Querying from the CLI

Every query command plans a spec, with an optional security context, and prints the SQL. By default the model is loaded from the project directory at hand, so nothing needs to be running; with -r the same command asks the server for its copy of the branch instead. Write the query as a line of the expression language or as a JSON file (--spec). The line becomes a spec before it is planned, so the planner always sees a spec.

The expression language in one minute

A line of comma-separated items (a newline separates items too). Each is read from its shape:

item category, month(date), store net paid desc, item category in (Books, Music)
yoy(store net paid), date between 2024-01-01 and 2024-12-31
calc(sum([Store Net Paid]@m) / sum([Catalog Net Paid]@m)) as Share
include (customer id, catalog net paid > 0) as Buyers, limit 100
  • A projection is a field, optionally inside decorator functions (month(...), yoy_pct(...), share(x by region), running_sum(x by a order by b), moving_avg(x, 7)), with an optional as Alias and asc/desc.
  • A filter is a field, a predicate and a value: =, !=, >, >=, <, <=, in (...), not in (...), between a and b, contains, starts with, ends with (each with not), is null, is not null, top N [by measure], keyword (...). and, or and parentheses build a tree.
  • A calculation is calc(sql) as Alias, over bracket references ([Name]@m, [Name]@d, [Alias]).
  • A segment is include (...), exclude (...) for measure or expand (...) with (...).
  • Settings are limit N and hints TABLE; {json} merges spec JSON into the query, or into the item it follows.
  • [Name] or "Name" is always a field, 'text' is always a value. Names match without regard to case; add @d or @m when a name is both a dimension and a measure. Fourteen words are reserved everywhere: and or not in is between top contains starts ends keyword as asc desc.

zsql spec --help prints every form. The full grammar is on Expressions; the spec it produces is on Query spec.

Security context

A branch with policies in security.yml refuses to plan without a context (ContextRequired: this branch has security policies; a context is required to plan). Put the context in a file and pass --context:

{"email": "ana@example.com", "groups": [{"name": "Men", "tags": ["cat:Men"]}]}

The shape is on Security context. The transcripts below use the TPC-DS example, which has one policy, so they all pass --context user.json.

zsql sql

zsql sql (LINE... | --spec FILE) [--context FILE] [--explain] [--json]
$ zsql sql "item category, store net paid" --context user.json
projection   item category
projection   store net paid
-- datasource: tpcds (TPC-DS, postgres)
SELECT
	T1."i_category" AS "Item Category",
	sum(T0."ss_net_paid") AS "Store Net Paid"
FROM
	store_sales T0
	JOIN item T1
		ON T0.ss_item_sk = T1.i_item_sk
GROUP BY
	T1."i_category"

The projection / filter / calculation / segment / setting lines say how each item was read; they go to stderr, so zsql sql ... > query.sql captures only the datasource comment and the SQL.

With a spec file (a top-level spec key is unwrapped if present):

{
  "projections": [
    {"field": "Item Category"},
    {"field": "Date", "decorators": [{"type": "truncate", "grain": "month"}]},
    {"field": "Store Net Paid", "order_by": "desc"}
  ],
  "filters": [{"field": "Item Category", "predicate": "in_list", "value": "Books, Music"}]
}
$ zsql sql --spec monthly.json --context user.json
-- datasource: tpcds (TPC-DS, postgres)
SELECT
	T2."i_category" AS "Item Category",
	DATE_TRUNC('month', T1."d_date")::DATE AS "Month(Date)",
	sum(T0."ss_net_paid") AS "Store Net Paid"
FROM
	store_sales T0
	JOIN date_dim T1
		ON T0.ss_sold_date_sk = T1.d_date_sk
	JOIN item T2
		ON T0.ss_item_sk = T2.i_item_sk
WHERE
	LOWER(T2."i_category") IN ('books', 'music')
GROUP BY
	T2."i_category",
	DATE_TRUNC('month', T1."d_date")::DATE
ORDER BY
	sum(T0."ss_net_paid") desc

Fuzzy corrections

A field name that is close but not exact is corrected, and the correction is printed before the SQL (to stderr):

$ zsql sql "departmnt, store net paid" --context user.json
departmnt → Department (~0.8)
-- datasource: tpcds (TPC-DS, postgres)
SELECT ...

The format is term → Field Name (~score). Exact and synonym matches print nothing.

—json

--json prints the answer unchanged: sql, datasource, datasource_uid, adapter, corrections when any, and for an expression line the spec the CLI sent.

$ zsql sql "year, store quantity" --context user.json --json
{
  "sql": "SELECT\n\tT1.\"d_year\" AS \"Year\",\n\tsum(T0.\"ss_quantity\") AS \"Store Quantity\"\nFROM\n\tstore_sales T0\n\tJOIN date_dim T1\n\t\tON T0.ss_sold_date_sk = T1.d_date_sk\nGROUP BY\n\tT1.\"d_year\"",
  "datasource": "TPC-DS (Postgres)",
  "datasource_uid": "tpcds",
  "adapter": "postgres"
}

zsql explain

zsql explain (LINE... | --spec FILE) [--context FILE] [--json]

The SQL, then the plan: the node tree the planner built and where the microseconds went. zsql sql --explain is the same.

$ zsql explain "item category, store net paid, catalog net paid" --context user.json
projection   item category
projection   store net paid
projection   catalog net paid
-- datasource: tpcds (TPC-DS, postgres)
WITH ag... AS (...), ag... AS (...)
SELECT ... FULL OUTER JOIN ...

-- 3 nodes
merge final (root) join full
    projections: Item Category, Store Net Paid, Catalog Net Paid
  aggregation ag... on store_sales (tpcds) group by
      paths: store-sales -> item
      projections: Item Category, Store Net Paid
      security: Item Category in (men)
  aggregation ag... on catalog_sales (tpcds) group by
      paths: catalog-sales -> item
      projections: Item Category, Catalog Net Paid

-- local 214 us: parser 11 us, plan 203 us
-- phases (us): ...

Each node is kind alias on table (datasource), with group by, the join type, [segment] and a transform or purpose when present, then indented lists of paths, projections, filters, security filters and masks. The SQL is abbreviated above; each ag... is one aggregation CTE. Two facts sharing Item Category become two aggregation nodes under one merge. --json returns the raw nodes, timings and phases. See Explain for the node fields.

zsql explore

zsql explore (LINE... | --spec FILE) [--context FILE] [-q TERM] [--json]

Given a query, what can it take on without breaking: the dimensions it can drill into and the measures it can add. -q narrows by name, uid or synonym, exact containing matches first, then close ones with a score.

$ zsql explore "item category, store net paid" --context user.json -q date
date                                     dim Date                             date
year                                     dim Year                             date
month-of-year                            dim Month of Year                    date
date-id                                  dim Date ID                          date
-- can add 4 dimensions, 0 measures · local 96 us

Without -q, every reachable dimension and measure is listed. Hidden fields are excluded. The last column names the tables a field lives on.

zsql spec

zsql spec LINE...

Several arguments are joined with spaces, so quoting the line is optional.

Prints the spec JSON an expression line makes, with the item descriptions, and needs no model. Use it to learn the spec shape or to generate a file for --spec. zsql spec --help is the language’s own summary: what a line is, and every form it takes.

$ zsql spec "item category, store net paid desc, item category in (Books, Music)"
projection   item category
projection   store net paid desc
filter       item category in (Books, Music)
{
  "filters": [
    {"field": "item category", "predicate": "in_list", "value": "Books, Music"}
  ],
  "projections": [
    {"field": "item category"},
    {"field": "store net paid", "order_by": "desc"}
  ]
}

zsql fields and zsql tables

zsql fields [Q] [--hidden]
zsql tables
$ zsql fields net
store-net-paid                           msr Store Net Paid
store-net-profit                         msr Store Net Profit
store-net-including-returns              msr Store Net Including Returns
catalog-net-paid                         msr Catalog Net Paid
$ zsql fields catgory
item-category                            dim Item Category ~0.86
$ zsql fields --hidden | grep hidden
store-ticket-number                      dim Store Ticket number (hidden)

Each line is uid dim|msr name, with (hidden) and a ~score for fuzzy matches. Without Q every field is listed; hidden ones only with --hidden.

$ zsql tables
date                             date_dim                         tpcds cost 10
item                             item                             tpcds cost 10
store-sales                      store_sales                      tpcds cost 100
catalog-sales                    catalog_sales                    tpcds cost 100
inventory                        inventory                        tpcds cost 100

uid physical_name datasource cost N. These are the two discovery routes, GET .../fields and GET .../tables; see Discovery.

The repl

zsql repl [--context FILE]

zsql with no subcommand inside a project also starts it. The prompt is zsql> . A plain line is an expression line; a line starting with { is a spec; both go to the sql route and print the SQL with the round trip time. The context given at start applies to every query.

$ zsql repl --context user.json
tpcds from /home/you/tpcds: 6 tables, 42 fields, 5 joins, 1 policies
type a line: projections, filters, calculations, segments; tab completes, .help for the forms, .quit to leave
zsql> item category, store net paid, item category in (Books, Music)
projection   item category
projection   store net paid
filter       item category in (Books, Music)
-- datasource: tpcds (TPC-DS, postgres)
SELECT
	T1."i_category" AS "Item Category",
	sum(T0."ss_net_paid") AS "Store Net Paid"
FROM
	store_sales T0
	JOIN item T1
		ON T0.ss_item_sk = T1.i_item_sk
WHERE
	LOWER(T1."i_category") IN ('books', 'music')
GROUP BY
	T1."i_category"
-- round trip 38.41 ms
zsql> .explore item category, store net paid ? quantity
store-quantity                           msr Store Quantity                   store-sales
catalog-quantity                         msr Catalog Quantity                 catalog-sales
inventory-quantity-on-hand               msr Inventory Quantity On Hand       inventory
-- can add 0 dimensions, 3 measures · local 102 us
zsql> .branch feature/returns
tpcds/feature/returns on http://127.0.0.1:3699: 7 tables, 48 fields, 6 joins, 1 policies (with -r)
zsql> .quit

Dot-commands, as in sqlite:

CommandDoes
.help, .?The forms of the language and this list
.tablesAs zsql tables
.fields [q]As zsql fields
.branch [name]Print the branch, or switch to another deployed branch
.spec <line>The spec a line makes, without planning
.json {...}Plan a spec written inline
.explain <line or {...}>As zsql explain
.explore <line or {...}> [? term]As zsql explore; the term after ? narrows
.quit, .exit, .qLeave

Completion and hints

The line editor knows the grammar, so what it offers depends on where the cursor stands:

  • Tab completes field names (only dimensions where the grammar wants a dimension, only measures where it wants a measure), decorator function names with their opening parenthesis, the keywords and predicate words that can come next, the projected dimensions after by and order by, table uids after hints, the parts inside extract( and the result types inside calc(. Names match on a prefix, then on any substring, so net finds Store Net Paid.
  • A dimmed hint trails the cursor with whatever every candidate agrees on beyond what you have typed; the right arrow accepts it.
  • Keywords are bold and quoted strings dim, so a value that the grammar read as a name is visible before you press return.
  • An unclosed parenthesis, bracket, brace or quote turns red from where it opened and keeps the line open, so a long in (...), a segment or a formula can be written over several lines.

Dot-commands are not completed. The editor re-reads the model after .reload and .branch, so completion follows the branch you are on.

help, quit and the like also work without the dot unless a field has that name. The repl holds the model it loaded and, before each line, checks whether any of the files it read has changed; when one has it reloads and says so (reloaded in 6 ms), and when the new files will not load it says that and keeps answering from the model it had. .reload forces it. Refusals print as error: <class>: <message> and the session continues. History is kept in ~/.zsql/history across sessions.

Next steps