Malloy Documentation
search
previousImports
Tagsnext

Connections

VSCode document

In Malloy, named connection objects are used to interact with a database, including fetching schemas for SQL queries and tables, as well as actually running queries.

Connection objects are defined implicitly on their first use when referencing a table or SQL source:

document
// This creates a model-level connection definition, `duckdb`
source: users is duckdb.table('../data/users.parquet')

run: users -> { group_by: first_name; limit: 5 }
QUERY RESULTS 
[
  {
    "first_name": "AARON"
  },
  {
    "first_name": "ABBEY"
  },
  {
    "first_name": "ABBIE"
  },
  {
    "first_name": "ABBY"
  },
  {
    "first_name": "ABDUL"
  }
]
SELECT 
   base."first_name" as "first_name"
FROM '../data/users.parquet' as base
GROUP BY 1
ORDER BY 1 asc NULLS LAST
LIMIT 5

Connection Methods

VSCode document

There are currently two connection methods, .table() and .sql().

Table Connection Method

The .table() connection method is used to reference a table or view in a database. It accepts a single string representing a table. The exact semantics of how that string is resolved depend on the database and application.

In the official Malloy connection implementations, the behavior is as follows:

BigQuery

In BigQuery, the string passed to the .table() connection method can be a two- or three-segment path including the (optional) project ID, dataset ID, and table name, e.g. bigquery.table('project-id.dataset-id.table-name') or bigquery.table('dataset-id.table-name'). If the project ID is left off, the default project ID for the connection will be used, or else the system default if none is set on the connection.

To use a BigQuery wildcard table, the segment containing the wildcard must be backtick-quoted inside the string, e.g. bigquery.table('`my-project.analytics.events_*`').

Databricks

In Databricks, the string passed to the .table() connection method can be a one-, two-, or three-segment path: table, schema.table, or catalog.schema.table. If the catalog or schema is omitted, the configured defaults (or workspace defaults) are used.

DuckDB

In DuckDB, the .table() method accepts the path (relative to the Malloy file) of a CSV, JSON, or Parquet file containing the table data, e.g. duckdb.table('data/users.csv') or duckdb.table('../../users.parquet'). URLs to such files (or APIs) and glob patterns (duckdb.table('data/*.parquet')) are also allowed; see an example here. DuckDB also accepts dotted identifier paths (schema.table) for tables defined inside the database.

If Malloy doesn't recognize the argument as a file path, wrap it in literal single quotes — duckdb.table("'/actual/path'") — to pass the quoted string straight through to DuckDB.

MySQL

In MySQL, the string passed to the .table() connection method can be a one- or two-segment path: table or database.table. If the database is omitted, the connection's default database is used.

Postgres

In Postgres, the string passed to the .table() connection method can be a two- or three-segment path including the (optional) database ID, schema name, and table name, e.g. postgres.table('database-id.schema-name.table-name') or postgres.table('schema-name.table-name'). If the database ID is left off, the default database for the connection will be used, or else the system default if none is set on the connection.

Snowflake

In Snowflake, the string passed to the .table() connection method can be a one-, two-, or three-segment path: table, schema.table, or database.schema.table. Omitted segments fall back to the connection's configured database and schema.

Trino / Presto

In Trino and Presto, the string passed to the .table() connection method can be a one-, two-, or three-segment path: table, schema.table, or catalog.schema.table. Omitted segments fall back to the connection's configured catalog and schema.

Quoting and reserved words

To use a reserved word or a name containing special characters as a path segment, quote it with the dialect's identifier quote character: "…" for Postgres, Snowflake, and Trino/Presto; `…` for MySQL, Databricks, and BigQuery. For example:

source: orders is postgres.table('public."order"')

SQL Connection Method

The .sql() connection method is used to define a source or query based on a SQL query. See the SQL Sources section for more information.

Virtual Connection Method (experimental)

The .virtual() connection method defines a source with no underlying table. The string argument is a logical name that is resolved to an actual table at query time through a virtual map. Virtual sources require a type: declaration applied with the :: operator to define their fields.

##! experimental.virtual_source

type: order_fields is { order_id :: string, order_date :: date, total :: number }
source: orders is duckdb.virtual('orders')::order_fields

See Types and Virtual Sources for details. Enable with ##! experimental.virtual_source.

Configuring Connections

For information on configuring database connections for your environment, see: