MalloySQL

VSCode

MalloySQL enables mixing dialect-specific SQL (including DDL) with Malloy. For example, MalloySQL can be used to create a new table in BigQuery, based on a model of existing data.

Usage

MalloySQL can be written in files with either the .malloynb or .malloysql file extensions. These files can be used by either the VS Code Extension or the Malloy CLI. In the VS Code Extension, MalloySQL files are rendered in notebook format, and you have the option to add cells that contain markdown, Malloy, or MalloySQL. To change the language of a cell, click the language button on the bottom right of an individual code cell:

You can specify a connection name in a MalloySQL cell by including the following line before the query:

-- connection:<connection_name>

In the example above, we are specifying bigquery as the connection name to use when executing the SQL query. Only the first MalloySQL cell in the notebook needs to specify a connection. All subsequent queries will use this same connection by default.

SQL statements can also contain embedded Malloy queries by wrapping the Malloy statement with %{ and }%. To use embedded Malloy, a source must first be imported to use with the Malloy query.

MalloySQL can contain multi (/*...*/) and single-line (// or --) comments.

Running a specific SQL statement in a Malloy file (by, for example, clicking the "Run" codelens in the VSCode extension) will execute all preceeding Malloy statements, but only the selected SQL statement.