kater query new

Writes a new query in a workspace from the fields you select, as the field picker in Kater does.

Kater writes the SQL, compiles it and saves the file. Set dry_run to see the file first.

Examples

# Write the query that new-query.json describes into the ad_reporting workspace
kater query new --request-file new-query.json
# See the file that the same request writes, and save nothing
jq '.dry_run = true' new-query.json | kater query new --request-file -

Arguments

Argument	Description
`<path>`	Optional. In place of `--request-file`: the workspace, the query file or the chart file the request names.

Options

Option	Description
`--request-file <file>`	The JSON request. `-` reads it from standard input. See The request.

It also takes the global options --json, --kater-root, --scope and --help.

The request

This request writes clicks_by_platform: the clicks of each platform in the last 3 months, the most clicks first.

{"namespace": {"path": "connections/snowflake_prod_connection/workspaces/ad_reporting"}, "name": "clicks_by_platform", "fields": [{"field": "platform"}, {"field": "sum__clicks"}], "conditions": [{"field": "union__date_day", "operation": "is_within_last", "parameters": {"amount": 3, "unit": "month"}}], "order": [{"field": "sum__clicks", "descending": true}]}

Key	What it holds
`namespace`	The workspace, by its path from the content root.
`fields`	The fields the query shows, from 1 to 24. Each names a `field` of the workspace, with at most one `operation` and its `parameters`. `of` applies an operation to an earlier field’s result, by its place. `expression` makes a field from SQL. `lock_in` locks in a measure’s number at this query’s grouping.
`conditions`	The conditions the rows must meet. Each names a row `field`, an `operation` such as `is_one_of` or `is_within_last`, and its `parameters`.
`group`	`true` lists each combination of the row fields one time.
`order`	The sort order. Each names a `field`, or a field’s place with `of`, and `descending`.
`limit`	The most rows, from 1 to 10,000.
`name`	The query’s name. Without it, Kater names and labels the query by what it shows, such as `spend_by_platform` and “Spend by Platform”.
`label`	The query’s label.
`source`	Another query of the workspace to read in place of the workspace, as `query.<name>`, such as `query.spend_by_platform`.
`dry_run`	`true` writes and compiles the query and returns its file, but does not save it.

kater query fields lists the fields and operations that a request can use.

How Kater writes it

Conditions are filters. Kater writes each condition as an optional filter over a parameter, whose default is the condition’s value. So a reader can change the value.
Every reader turns them on. kater run, kater show, the query’s chart and its app’s tags all turn the query’s own filters on. Only a chart’s switch turns one off.
A required condition ("required": true) is written so that no reader can turn it off or change its value.
The end of a window. For is_within_last, "end": "latest" ends the window at the latest reported date before the current period. Use it for data that runs behind today.
Measures bring their dimensions. A measure brings in the dimensions that its NOT_ADDITIVE_ACROSS rule holds. For example, sum__spend brings in union__currency.
A field that cannot be written is refused with the field picker’s reason.

The answer names the query’s filters, and gives the run command that runs it.