What Is Malloy
Installing the Malloy Extension
Currently, BigQuery, PostgreSQL, and DuckDB are supported. These instructions assume you have already installed the Malloy Extension in VSCode.
Adding the Connection in VS Code
NOTE: DuckDB is natively supported, allowing you to skip these initial steps.
Open the VS Code Command Palette (cmd+shift+p on Mac, or ctrl+shift+p on Windows), and type "malloy connections". Click on the "Malloy: Edit Connections" option that appears. This will take you to the Connection Manager page.
Click "New Connection" and fill out the relevant details. See below for database-specific instructions.
Press "Test" on the connection to confirm that you have successfully connected to the database
Hit "Save," then dive into writing Malloy! We recommend starting with one of our Samples, which can be found here
If you've given your connection a name, that name must be used when referencing a table in your database. If you have not named your connection, the default name is either bigquery, duckdb, or postgres, depending on which database you're connecting to; e.g., bigquery.table('project-id.dataset-id.tablename').
BigQuery
Authenticating to BigQuery can be done either via OAuth (using your Google Cloud Account) or with a Service Account Key downloaded from Google Cloud
Option 1: OAuth using gcloud
To access BigQuery with the Malloy Extension, you will need to have a Google Cloud Account, access to BigQuery, and the gcloud CLI installed. Once the gcloud CLI is installed, open a terminal and type the following:
gcloud auth login --update-adc gcloud config set project {my_project_id} --installation
Replace {my_project_id} with the ID of the BigQuery project you want to use & bill to. If you're not sure what this ID is, open Cloud Console, and click on the dropdown at the top (just to the right of the "Google Cloud Platform" text) to view projects you have access to. If you don't already have a project, create one.
When creating the connection in the VS Code Plugin, you can leave the optional fields blank as it will connect using your gcloud project configuration.
NOTE: The Malloy Extension used the BigQuery Node SDK, which does its best guess at finding credentials stored on your device - it looks in environment variables, and also in places that gcloud is known to store application credentials. There is not necessarily a 1:1 mapping of how gcloud is authenticated and how the BigQuery SDK will authenticate.
Option 2: Service Account
Add the relevant account information to the new connection, and include the path to the service account key.
The default name of a BigQuery connection is bigquery.
PostgreSQL
Add the relevant database connection information. Once you click save, the password (if you have entered one) will be stored in your system keychain.
The default name of a PostgreSQL connection is postgres
Parquet and CSV Files (via DuckDB)
Parquet and CSV files are queryable via DuckDB is available without needing to explicitly configure a connection. Local files can be referenced in a source. This example has the CSV in the same directory as the .malloy model file: source: baby_names is duckdb.table('babynames.csv') The default name of a DuckDB connection is duckdb.
There are a number of examples on public data using DuckDB available here.