Connecting to a warehouse

Two different programs are described by datasources.yml, and keeping them apart is the whole of this page.

The server never connects to your warehouse. It has no database driver linked into it: it takes a query spec and gives back SQL text. The only keys it reads are adapter, which picks the dialect to generate, and tier, which ranks datasources when more than one could answer a query.

zsql on your own machine does connect, read-only, when you ask it to — zsql catalog to read what the warehouse holds, zsql sample to collect a dimension’s values, zsql run to run a planned query. Everything else in datasources.yml is for that, and for your own tools.

The two keys the planner reads

warehouse:
  name: Warehouse
  adapter: postgres        # the dialect to generate
  tier: hot                # hot | warm | cold; the planner prefers hotter tiers

Those three travel in the deployed model. See Datasources for what tier does to routing and Adapters for the dialects.

The keys zsql connects with

A deploy uploads datasources.yml with the secrets stripped, but the server keeps none of these: they are read here, on this machine.

keyadaptersmeaning
host, portthe wire and HTTP adapterswhere the server is
databasemostthe database (ClickHouse and MySQL call it the database that --schema names)
schemamostthe default schema; a catalog read without --schema reads every one it can see. BigQuery spells it dataset, and either key is read
usernamethe wire adapters, Snowflake key pairwho connects
urlClickHouse, Trinothe base URL when host and port cannot say it — a scheme, or TLS on a port that does not imply it
fileSQLite, DuckDBthe file to open
query_timeoutallseconds; also the statement timeout
sslmode, ca_filethe wire and HTTP adapterssee TLS
account, warehouse, role, private_key_fileSnowflakethe account identifier, the compute to run on, the role to assume, a PKCS#8 PEM
warehouse, catalog, client_idDatabricksthe SQL warehouse id, the Unity catalog, a service principal’s application id
project, location, key_fileBigQuerythe project, the region, a service-account or ADC key file
region, catalog, workgroup, output_location, profileAthenathe AWS region, the Glue data catalog, the workgroup, the S3 result location, a named profile
cache_ttlallseconds a stored zsql run result answers for; 0 keeps nothing

A path is not a secret, so private_key_file, key_file and ca_file may live in the committed file.

Secrets

Secrets never leave this machine. They go in .zsql, which zsql init gitignores and no deploy ever sends, or in the environment. Nothing reads them but the connection, and a resolved secret lives no longer than the connection does.

# .zsql — gitignored
warehouse:
  password: hunter2
snow:
  personal_access_token: ...

A flat warehouse_password: hunter2 works too, and ~/.zsql/config is read after the project’s file. The environment is checked first, so a shell can override a checked-out project without editing it:

export ZSQL_DS_WAREHOUSE_PASSWORD=hunter2
export ZSQL_DS_SNOW_PERSONAL_ACCESS_TOKEN=...

The variable is ZSQL_DS_<KEY>_<FIELD>, the datasource key upper-cased with anything that is not a letter or digit becoming an underscore. The eight fields are password, personal_access_token, private_key, oauth_client_id, oauth_client_secret, access_key_id, secret_access_key and session_token.

A missing secret is named rather than guessed at:

datasource warehouse needs a password: put it in .zsql under warehouse, or in ZSQL_DS_WAREHOUSE_PASSWORD

zsql datasource auth

zsql datasource auth -d warehouse                      # prompts, hidden
zsql datasource auth -d snow --field personal_access_token
echo "$SECRET" | zsql datasource auth -d warehouse --secret-stdin

Writes the secret into .zsql under the datasource’s key. --field names which one; without it, the adapter’s usual secret. --secret-stdin is the form for a script.

TLS

sslmode is libpq’s five rungs with libpq’s meanings, and the default is verify-full:

sslmodemeans
disableplaintext, for a container on loopback
preferTLS if the server offers it, plaintext if not, never verified; a connection that ends up plaintext says so
requireTLS or nothing, certificate not checked — the rung a self-signed development server needs
verify-caTLS, the chain checked against the trust store, the host name not
verify-fullTLS, chain and host name both checked. The default.

MySQL’s spellings of the same five are accepted. ca_file is a PEM of certificates to trust besides the platform’s, for a private CA — libpq’s sslrootcert, MySQL’s --ssl-ca. An HTTP adapter whose url is plain http while sslmode insists on TLS is refused, naming both.

Which adapters zsql can reach

Twelve adapter names in eleven implementations, over four transports. Redshift rides the Postgres wire with its own catalog queries.

transportadapters
native wirePostgres, Redshift, MySQL, SQL Server
HTTPClickHouse, Trino
in-processSQLite, DuckDB
cloud RESTSnowflake, Databricks, BigQuery, Athena

Postgres, MySQL, SQL Server, ClickHouse and Trino are tested against real servers in containers on every push, and Redshift through the Postgres one. The four cloud adapters are tested against a fake of each endpoint, because none can be reached without an account. Druid is a dialect with no reader: 0sql generates Druid SQL, but zsql catalog and zsql run cannot open it.

Credentials, by adapter:

adaptertakes
Postgres, Redshift, MySQL, SQL Server, ClickHouse, Trinousername + password
SQLite, DuckDBnothing; a file
Snowflakepersonal_access_token, or username + an RS256 key pair (private_key_file, or private_key in .zsql)
Databrickspersonal_access_token, or client_id + oauth_client_secret
BigQuerykey_file, or GOOGLE_APPLICATION_CREDENTIALS, or gcloud auth application-default login
Athenaaccess_key_id + secret_access_key (+ session_token), or AWS_ACCESS_KEY_ID and friends, or a profile in ~/.aws/credentials

datasource list, add and test

$ zsql datasource list
key              adapter      tier   where                            secret
warehouse        postgres     hot    db.internal:5432/analytics       yes
snow             snowflake    warm   myorg-myaccount                  missing: .zsql or ZSQL_DS_*
cs               duckdb       hot    /data/cs_warehouse.duckdb        none needed

The last column says whether the secret is at hand — yes, missing: .zsql or ZSQL_DS_*, or none needed for an adapter that takes none — never what it is.

zsql datasource add                      # asks on a terminal
zsql datasource add --adapter postgres --host db.internal --database analytics --username zsql

add writes the entry into datasources.yml and the secret into .zsql. Every prompt has a flag form, so it works unattended; --no-input turns a missing answer into a refusal that names the flag.

$ zsql datasource test -d cs
cs: ok, duckdb (512 ms); 2 schemas: main (20), sim_base (20)
next: zsql catalog read --schema <name>

test opens the connection, runs a one-row probe and lists the schemas the credential can see, which is the list catalog read --schema takes. It is the one command whose job is the connection, so a wrong host, a missing grant or a TLS mismatch is diagnosed here rather than halfway through a catalog read. A driver’s own message is reported with its cause chained onto it, because the outer message is often “error performing TLS handshake” while the reason underneath is “server does not support TLS”.

zsql check connects to nothing. It validates the model against the committed catalog instead.

Next steps