malloy

--- ## What is Malloy? Malloy is an open source language for describing data relationships and transformations. It is both a semantic modeling language and a query language that uses an existing SQL engine to execute queries. **Supported SQL engines:** BigQuery · Snowflake · DuckDB · MotherDuck · PostgreSQL · MySQL · Trino · Presto · Databricks ## Why Malloy? Malloy is composable: complex analytics built from small, readable pieces. It handles nested data natively — arrays of records, query pipelines — as first-class syntax, not workarounds. And every query you write leaves behind a semantic model, so the work you do to answer one question is already there for the next. Malloy compiles to SQL and embeds SQL, so none of this asks you to leave the database — or the language — you already have. --- ## How Malloy Works --- ## Quick Start There are four ways to install Malloy: ### Install the VSCode Extension The easiest way to try Malloy is the [VSCode Extension](https://docs.malloydata.dev/documentation/setup/extension.html#installation), which facilitates building Malloy data moels, querying and transforming data, and creating simple visualizations and dashboard. You can do this locally, or [try it in the browser (no install)](https://github.dev/malloydata/try-malloy/airports.malloy), which opens a live Malloy notebook in github.dev (GitHub's in-browser VSCode; requires a GitHub sign-in). Follow the instructions for [connecting Malloy to your database](https://docs.malloydata.dev/documentation/setup/extension.html#database-specific-setup). > Note: The Malloy VSCode Extension collects a small amount of anonymous usage data. You can opt out in the extension settings. [Learn more](https://policies.google.com/technologies/cookies). ### Use the npm packages To use Malloy in Node.js, install the compiler and a database connector. For example: ```bash npm install @malloydata/malloy @malloydata/malloy-connections ``` Importing @malloydata/malloy-connections registers all supported database backends. Run a query from Node.js: ``` … ``` This defines `total_revenue` once in the semantic model, then uses it by name in the query. Other databases can be configured through MalloyConfig. ### Run Malloy from the command line For scripting, pipelines, or CI, install the standalone Malloy CLI: ```bash npm install -g malloy-cli malloy-cli run my_query.malloy ``` It can `run` queries, `compile` to SQL, and `build` persistent tables from sources marked `#@ persist`.
Connections are configured in `~/.config/malloy/malloy-config.json` (BigQuery, Snowflake, DuckDB, PostgreSQL, MySQL, Trino, Presto, Databricks; MotherDuck via DuckDB).
See the [Malloy CLI docs](https://docs.malloydata.dev/documentation/malloy_cli/index) and [malloy-cli repo](https://github.com/malloydata/malloy-cli) for more details about using the CLI ### Serve models with Publisher [Publisher](https://github.com/malloydata/publisher) is the open-source semantic model server for Malloy. It serves your `.malloy` models through REST and MCP APIs so apps, BI tools, and AI agents can query them through one interface: ```bash npx @malloy-publisher/server --port 4000 --server_root path/to/your/models ``` Open `http://localhost:4000` to browse models, run queries, and grab MCP endpoints. See the [Publisher repo](https://github.com/malloydata/publisher) for setup, sample models, and deployment options. --- ## The Malloy Language at a Glance A bare Malloy query reads like an outline of what you want: **Malloy** ```malloy run: duckdb.table('airports.parquet') -> { group_by: state aggregate: airport_count is count() avg_elevation is elevation.avg() } ``` **Equivalent SQL** ```sql SELECT state, COUNT(*) AS airport_count, AVG(elevation) AS avg_elevation FROM 'airports.parquet' GROUP BY state ORDER BY airport_count DESC -- Malloy orders by first aggregate automatically ``` Malloy is most useful for pinning down measures whose definitions aren't obvious. Take "on-time arrival" - the US DOT defines it as `arr_delay < 15` *with cancelled and diverted flights excluded*. A naive `count() { where: arr_delay < 15 } / count()` silently treats cancellations as on-time. Encode the rule once, and every dashboard, report, and ad-hoc query agrees: ```malloy source: flights is duckdb.table('flights.parquet') extend { measure: on_time_rate is count() { where: cancelled = 'N' and diverted = 'N' and arr_delay < 15 } / count() { where: cancelled = 'N' and diverted = 'N' } } ``` These are two simple examples. The [10-minute quickstart](https://docs.malloydata.dev/documentation/user_guides/basic.html) and [Malloy by Example](https://docs.malloydata.dev/documentation/user_guides/malloy_by_example) cover additional topics like joins, nested results, symmetric aggregates, and the pipe operator. --- ## Documentation | Resource | Description | |---|---| | [Language Reference](https://docs.malloydata.dev/documentation/) | Full language guide | | [Quickstart](https://docs.malloydata.dev/documentation/user_guides/basic.html) | 10-minute tour | | [Malloy by Example](https://docs.malloydata.dev/documentation/user_guides/malloy_by_example) | Advanced modeling patterns and idioms | | [Malloy CLI](https://docs.malloydata.dev/documentation/malloy_cli/index) | Command-line reference for `run`, `compile`, `build` | | [YouTube Channel](https://www.youtube.com/channel/UCfN2td1dzf-fKmVtaDjacsg) | Video demos and walkthroughs | --- ## Community - **[Slack](https://malloydata.github.io/slack)**: ask questions, share models, meet other users - **[GitHub Issues](https://github.com/malloydata/malloy/issues)**: bug reports and feature requests - **[YouTube](https://www.youtube.com/channel/UCfN2td1dzf-fKmVtaDjacsg)**: demos and tutorials --- ## Contributing If you would like to contribute to Malloy, please read [`CONTRIBUTING.md`](CONTRIBUTING.md) and [`developing.md`](developing.md), and feel free to contact us on slack. --- ## Security To report a security vulnerability, please follow our [Security Policy](SECURITY.md) rather than opening a public issue.