kater scratch

Lists your scratch files, and runs a Sandbox action on a statement of one.

A scratch file is your own worksheet of SQL statements: any .sql file in connections/<connection>/users/<email>/brainstorm/, at any depth. The Sandbox runs its statements on shared models, a workspace or the warehouse. kater new scratch makes a file, and kater show shows a statement’s rows.

Commands

Command	What it does
`kater scratch list`	List your scratch files and ones shared with you.

Actions

Write the action after kater scratch, such as kater scratch add. add, add.options and chart.preview take the options below. Every other action takes its whole request as JSON with --request-file. kater scratch ACTION --help lists an action’s options.

Action	What it does
`files`	Lists your whole brainstorm folder: its topics, folders and files, newest first. It also lists the scratch files others shared with you.
`append`	Adds one statement with its title as a new scratch file, or with `path` after the last statement of one of yours.
`access`	Says what you may do in the connection’s Sandbox: `scratchpad`, `native` for warehouse SQL, `testSuite` and `connectionWorkspace`. It also gives your own `testsFolder`.
`catalog`	Lists the shared semantic models and Kater datasets a statement may read. Each comes with its fields’ names, labels, types, formats and descriptions.
`statement.read`	Reads one statement as the chart builder reads it: what it reads, its fields, and the fields and menus a chart of it offers.
`statement.write`	Writes the fields you select as a statement’s SQL. It saves nothing.
`add.options`	Says what adding a statement to a workspace would make. It takes options.
`add`	Adds a statement to a workspace as a query. It takes options.
`chart.preview`	Runs a statement and gives its rows for its chart. It takes options.
`explore.add`	Adds a copy of a statement and its chart to its shared model’s Explore page.

files, access and catalog take the connection alone, such as {"connection": "snowflake_prod_connection"}.

Action options

Action option	What it does
`--request-file FILE`	The action’s whole request as JSON, from a file. `-` reads stdin. Every action but `add`, `add.options` and `chart.preview` needs it. With it, the other options have no effect.
`--path FILE`	The scratch file that holds the statement, by its path. `add`, `add.options` and `chart.preview` need it.
`--statement N`	The statement, by its number in the file, counting from 0. `add` and `add.options` take it, `--at` or `--selection`. `chart.preview` takes only `--statement`.
`--at LINE:CHARACTER`	Or the statement at this position, counting lines and characters from 0, in UTF-16 units. A position just after a semicolon is in the next statement.
`--selection RANGE`	Or only a part of the file, added as one statement. Write it as `LINE:CHARACTER-LINE:CHARACTER`, counting from 0, in UTF-16 units.
`--text-file FILE`	The file’s text, in place of what is saved, such as unsaved changes in an editor. `-` reads stdin.
`--workspace PATH`	For `add`: the workspace to add the statement to, by its folder. It must read the model that the statement reads.
`--new-workspace NAME`	For `add`: makes a new workspace with this name, and adds the statement to it. Give `--workspace` or `--new-workspace`, not both.
`--location LOCATION`	For `add`, where a new workspace goes: `personal`, in your own folder, or `connection`, in the connection’s `workspaces` folder.
`--workspace-label TEXT`	For `add`: the name people see for the new workspace.
`--model-name NAME`	For `add`, for a join or warehouse SQL only: the name of the new shared model it becomes. Its new workspace gets a query of every field.
`--model-label TEXT`	For `add`: the name people see for the model that `--model-name` names.
`--query-name NAME`	For `add`: the new query’s name, in place of the name Kater gives it.
`--query-label TEXT`	For `add`: the name people see for the new query, in place of the statement’s title.
`--include-chart`	For `add`: carries the statement’s chart, from the file’s `charts:`, over as a chart of the new query.
`--dry-run`	For `add`: prints the paths it would write, and writes nothing.
`--chart JSON`	For `chart.preview`: the chart to draw, as JSON. Left out, the rows come back as a table of every output.

What a scratch file is

Known by its path. Two topics can each hold a checks.sql. Files in a topic’s workspaces/ and apps/ folders are not scratch files.
Never versioned. A scratch file never reaches a branch or anyone else’s local copy.
Private. Only you, admins and the people you share it with can read it.
Nothing reads it. No model, picker or Explore card reads from a scratch file.
Statements. A file holds statements separated by semicolons. The -- comment lines right above a statement are its title. Statements are numbered from 0.

How a statement runs

What a statement reads selects how it runs:

It reads	It runs as
One shared model	A query in a new workspace over that model
One workspace	A query in that workspace
Several shared models	A draft semantic model that joins them
Warehouse tables	One read-only query, as written. It needs edit access to the shared models.

kater show --select with a scratch file and --statement runs one statement. kater run --select with a scratch file runs every statement in it.

What add.options says

It says what adding the statement makes, and changes nothing:

source. The one shared model the statement reads. For a statement that reads a workspace, workspace names that workspace.
newWorkspace. The folders a new workspace can go in, each with the name Kater would give it.
model. For a join or warehouse SQL, the shared model it would become.
query. The query’s default name and label.
chart. Whether the statement has a chart in charts: to carry over.
unavailable. Why the statement cannot be added as it stands.

Add a statement to a workspace

Where it goes. With --workspace, to that workspace. With --new-workspace, to a new one. With neither, to the first workspace over its model that you can change, or else to a new workspace in --location.
One shared model. The query reads the workspace, so FROM semantic.ad_reporting is saved as FROM workspace.ad_reporting.
A workspace it reads. It goes into that workspace only.
A join or warehouse SQL. It first becomes a new shared model. A join becomes a semantic model, and warehouse SQL a custom SQL Kater dataset. A new workspace then opens on it. Both need edit access to the shared models.
Conditions stay behind. In a workspace, the statement’s own conditions on the fields that the workspace’s filters are on stay behind. The scratch file keeps them.
Names. Kater names what you leave unnamed. A query is named by what it shows, with _2 or _3 when another query has that name. A name you give that is taken is refused.
Try again. A later try of the same add takes over what an earlier try made.

Draw a statement’s chart

chart.preview runs one statement and gives its rows for its chart, as the Sandbox draws it. The rows hold every output of the statement, planned for --chart. Without --chart, or when the chart no longer fits the statement, the rows come back as a table. Only a statement that reads one model has a chart. It prints the chart’s definition and rows, or says what to change.

Examples

# Go to the snowflake_prod_connection connection
cd connections/snowflake_prod_connection
# Make a scratch file in the sandbox folder of your brainstorm folder
kater new scratch --connection snowflake_prod_connection --folder sandbox
# List your scratch files and the ones shared with you
kater scratch list --connection snowflake_prod_connection
# Show the rows of statement 2 of a scratch file
kater show --select users/you@example.com/brainstorm/sandbox/live_session_check.sql --statement 2
# See what adding statement 2 to a workspace would make
kater scratch add.options --path users/you@example.com/brainstorm/sandbox/live_session_check.sql --statement 2
# Add statement 2 to the ad_reporting workspace as a query
kater scratch add --path users/you@example.com/brainstorm/sandbox/live_session_check.sql --statement 2 --workspace workspaces/ad_reporting
# Print the paths that add would write, and write nothing
kater scratch add --path users/you@example.com/brainstorm/sandbox/live_session_check.sql --statement 2 --workspace workspaces/ad_reporting --dry-run
# Run statement 2 and draw it as a bar chart of spend by platform
kater scratch chart.preview --path users/you@example.com/brainstorm/sandbox/live_session_check.sql --statement 2 \
  --chart '{"version": 2, "kind": "bar", "category": "platform", "values": ["sum__spend"]}'
# List the shared models a statement can read, with their fields
echo '{"connection": "snowflake_prod_connection"}' | kater scratch catalog --request-file -