Lightning Command Reference

Start with the supplied samples

Start with your first SQL commands, using the supplied NY_TAXI and TPCDS_DB samples.

Stage	Commands to learn	Prerequisites
Check execution	`SELECT 1`, `SHOW SERVER VERSION`	Login and query permission
Discover sources	`SHOW LIGHTNING DATABASES`, `SHOW DATASOURCES`	Login; results are limited to your access
Inspect sample tables	`SHOW LIGHTNING TABLES`, `DESCRIBE DATASOURCE TABLE`	Access to the supplied NY Taxi or TPCDS tables
Read sample data	`SELECT ... LIMIT 5`	Query permission on an existing table
Create views and connect more data	SchemaStore, file, REST and datasource commands	Creation permission, your own object names and the source-specific setup below
Build models and pipelines	USL, DQ, pipeline and data-mart commands	Accessible source data and the required namespaces or containers
Manage storage, access and cleanup	Export, cache, grant, revoke and drop commands	Writable storage or management permission, with a confirmed scope

Empty discovery lists are valid. Use SHOW DATASOURCES for external database connections and SHOW LIGHTNING DATABASES for file/REST sources.

Read each group's prerequisites and each command's privileges. The sections group commands by topic, not execution order.

Start with the supplied samples

Introduction
Conventions
General Rules
Data Sources
File Sources
REST Sources and Authentication
Streaming
Data Lakes
External Catalogs
Hive Metastore
AWS Glue Metastore
Schema Store Views
Unified Semantic Layer
Data Quality
Export and Materialization
Data Pipelines
Virtual Data Marts
Query Plans
Caching
Users and Roles
Access Control
Data Warehouses
Server Information

1. Introduction

Lightning commands connect sources, build models, check data quality and manage access.

Use the platform UI or run these commands in SQL Workspace alongside ordinary SQL queries.

This reference contains 144 Lightning commands.

2. Conventions

2.1 Syntax notation

Notation	Meaning
`KEYWORD`	A keyword, entered as shown. Keywords are not case-sensitive.
`<name>`	A value supplied by the user.
`'<text>'`	A value supplied as a quoted string. Single or double quotes may be used.
`[ … ]`	An optional element.
`{ A \| B }`	Choose one alternative.
`…`	The preceding element may be repeated.
`(key 'value', …)`	An option list of key and quoted value pairs, separated by commas.

2.2 Entry format

Each entry gives syntax, a description, privileges, any limitations or notes, and an example.

2.3 Example objects

Examples use the objects below. Create the required objects and replace names with your team names. Hosts, credentials and incomplete JSON are placeholders. The starter guide uses already-registered NY_TAXI and TPCDS_DB samples.

Object	Description
`crm_pg`	A PostgreSQL datasource with the tables `customers` and `orders`
`sales_files`	A file source containing the table `transactions`
`weather_api`	A REST source containing the table `daily_weather`; `weather_oauth` is its saved token request
`clickstream`	A Kafka streaming source
`analysis`	A schema store container
`lightning.metastore.datathon`	The namespace of the USL `retail`
`etl`	A pipeline container
`sales_mart`	A virtual data mart
`alice@example.com`, `analyst`	A user and a role

3. General Rules

One statement per execution. The SQL Workspace splits editor text on semicolons. Submit each command separately.
Qualified table names. Commands that operate on a table require the form <source>.<table>.
Identifiers and values. Object names are written without quotes; names containing special characters are enclosed in backquotes. Text values are enclosed in single or double quotes.
Privileges. Commands marked Administrators only are refused for other users. Other commands require the permission stated in the entry, and return only the objects assigned to the user.
Standard SQL. Registered tables are queried with standard Spark SQL, for example SELECT … FROM crm_pg.customers, and may be joined across sources.

4. Data Sources

A datasource is a registered connection to an external database. Registering a datasource stores the connection only; its tables become available after they are imported.

Prerequisites

Creating or inspecting an unregistered datasource needs a reachable database, driver and source credentials. Import, preview and metadata changes need a registered datasource. The client JDBC connection to Zetaris is separate. SHOW DATASOURCES needs no registration.

4.1 CREATE DATASOURCE

Syntax

CREATE DATASOURCE <name> [DESCRIBE BY '<desc>'] OPTIONS (key 'value', …)

Registers an external source (JDBC, Cassandra, MongoDB or DynamoDB, detected from the options) after testing the connection.

Privileges: Administrators only.

Example

CREATE DATASOURCE crm_pg DESCRIBE BY "PostgreSQL" OPTIONS (
  jdbcdriver "org.postgresql.Driver",
  jdbcurl "jdbc:postgresql://crm-db.example.com:5432/crm",
  username "datathon",
  password "changeme",
  fetchsize "1000"
)

4.2 REGISTER DATASOURCE TABLE

Syntax

REGISTER DATASOURCE TABLE '<table>' FROM <datasource>

Imports one source table's metadata into a datasource.

Privileges: Administrators only.

Example

REGISTER DATASOURCE TABLE "customers" FROM crm_pg

4.3 REGISTER DATASOURCE TABLES

Syntax

REGISTER DATASOURCE TABLES [('<table>', …)] FROM <datasource>

Imports the listed source tables, or every table when no list is given.

Privileges: Administrators only.

Example

REGISTER DATASOURCE TABLES FROM crm_pg

4.4 CREATE DATASOURCE VIEW

Syntax

CREATE DATASOURCE VIEW <view> FROM <datasource> AS <SELECT query …>

Creates or replaces a datasource view from native SQL that runs on the source.

Privileges: Administrators or Query Builder Write users.

Example

CREATE DATASOURCE VIEW au_customers FROM crm_pg AS SELECT * FROM customers WHERE country = 'AU'

4.5 SHOW DATASOURCES

Syntax

SHOW DATASOURCES

Lists accessible datasources: name, description and id.

Privileges: Signed-in users; assigned objects only.

Example

SHOW DATASOURCES

4.6 SHOW DATASOURCE TABLES

Syntax

SHOW DATASOURCE TABLES [<datasource>]
SHOW DATASOURCE TABLES <datasource> WITH STATISTICS

Lists a datasource's tables and views; WITH STATISTICS adds row counts, size and tags.

Privileges: Depends on the form: any user; Data Catalog Read, Data Catalog Write.

Limitations

WITH STATISTICS requires a datasource name.

Remarks

WITH STATISTICS adds row counts, size and tags.

Example

SHOW DATASOURCE TABLES crm_pg

4.7 PREVIEW DATASOURCE SQL

Syntax

PREVIEW DATASOURCE SQL <datasource> AS <SELECT query …>

Runs native SQL on a datasource and returns up to 10 rows.

Privileges: Administrators only.

Example

PREVIEW DATASOURCE SQL crm_pg AS SELECT id, full_name FROM customers WHERE country = 'AU'

4.8 LIST DATABASE TABLES

Syntax

LIST DATABASE TABLES OPTIONS (key 'value', …)

Connects with inline options and lists an unregistered source's tables, columns, types and keys.

Privileges: Administrators only.

Limitations

Only JDBC sources are supported.

Example

LIST DATABASE TABLES OPTIONS (
  jdbcdriver "org.postgresql.Driver",
  jdbcurl "jdbc:postgresql://crm-db.example.com:5432/crm",
  username "datathon",
  password "changeme"
)

4.9 DESCRIBE DATASOURCE TABLE

Syntax

DESCRIBE DATASOURCE TABLE <datasource>.<table>

Describes a table's columns with type, comment, description and tags (datasource, file, REST, Hive or Glue).

Privileges: Signed-in users; assigned objects only.

Limitations

The table must be qualified with its datasource name.

Example

DESCRIBE DATASOURCE TABLE crm_pg.customers

4.10 REFRESH DATASOURCE TABLES

Syntax

REFRESH DATASOURCE TABLES FROM <datasource>

Re-imports metadata for a datasource's registered tables.

Privileges: Administrators or Data Catalog Write users.

Example

REFRESH DATASOURCE TABLES FROM crm_pg

4.11 UNCACHE DATASOURCE

Syntax

UNCACHE DATASOURCE <name>

Clears a datasource's cached table metadata and statistics, then reloads Presto catalogs.

Privileges: Administrators only.

Example

UNCACHE DATASOURCE crm_pg

4.12 UPDATE DATASOURCE SCHEMA

Syntax

UPDATE DATASOURCE SCHEMA [<datasource>]

Re-imports table metadata for one datasource, or for every datasource you can access.

Privileges: Administrators only.

Example

UPDATE DATASOURCE SCHEMA crm_pg

4.13 DROP DATASOURCE VIEW

Syntax

DROP DATASOURCE VIEW <view> FROM <datasource>

Deletes a datasource view.

Privileges: Administrators only.

Example

DROP DATASOURCE VIEW au_customers FROM crm_pg

4.14 DROP DATASOURCE

Syntax

DROP DATASOURCE <name>

Deregisters a datasource with its tables, cache, search-index entries and privileges.

Privileges: Administrators only.

Example

DROP DATASOURCE crm_pg

5. File Sources

A file source is a Lightning database whose tables are defined over files in S3, Azure Blob Storage, Google Cloud Storage, HDFS or platform storage.

Prerequisites

Query the supplied samples with source access. New file tables need creation permission, a readable path and any storage credentials; persistent tables also need a Lightning database. Update, uncache and drop need an existing object.

5.1 CREATE FILESTORE SESSION TABLE

Syntax

CREATE FILESTORE SESSION TABLE <table> FORMAT <format> OPTIONS (key 'value', …)

Defines a temporary file-backed table, readable as session.<table> until restart or DROP TABLE.

Privileges: Administrators only.

Remarks

Session tables are visible within the organisation until they are dropped.
Read the table as session.<table> and remove it with DROP TABLE session.<table>.

Example

CREATE FILESTORE SESSION TABLE tx_preview FORMAT CSV OPTIONS (
  path "s3a://datathon-bucket/sales/transactions.csv",
  awsAccessKeyId "AKIAEXAMPLE",
  awsSecretAccessKey "example-secret",
  header "true",
  delimiter ","
)
SELECT * FROM session.tx_preview LIMIT 20

5.2 CREATE LIGHTNING DATABASE

Syntax

CREATE LIGHTNING DATABASE <name> [DESCRIBE BY '<desc>'] [OPTIONS (key 'value', …)]

Creates a Lightning database, the container for file-store and REST tables.

Privileges: Administrators only.

Remarks

The name must be unique within the organisation.

Example

CREATE LIGHTNING DATABASE sales_files DESCRIBE BY "Sales files"

5.3 CREATE LIGHTNING FILESTORE TABLE

Syntax

CREATE LIGHTNING FILESTORE TABLE <table> FROM <database> FORMAT <format> OPTIONS (key 'value', …)

Registers a file table (CSV, Parquet, JSON, Delta, PDF, images and more) with the schema inferred from the path.

Privileges: Administrators only.

Remarks

Option sourceTable also writes that table's data to the path.
Query the table as <database>.<table>. Supported formats: CSV, JSON, PARQUET, ORC, DELTA, XML, AVRO, PDF, IMAGE, VIDEO, AUDIO, DOC, TEXT, MARKDOWN, RTF.

Example

CREATE LIGHTNING FILESTORE TABLE transactions FROM sales_files FORMAT CSV OPTIONS (
  path "s3a://datathon-bucket/sales/transactions.csv",
  awsAccessKeyId "AKIAEXAMPLE",
  awsSecretAccessKey "example-secret",
  header "true",
  delimiter ","
)
SELECT * FROM sales_files.transactions LIMIT 20

5.4 SHOW LIGHTNING DATABASES

Syntax

SHOW LIGHTNING DATABASES

Lists accessible Lightning databases: name, description and id.

Privileges: Signed-in users; assigned objects only.

Example

SHOW LIGHTNING DATABASES

5.5 SHOW LIGHTNING TABLES

Syntax

SHOW LIGHTNING TABLES <database>

Lists a Lightning database's tables with type, path or endpoint, format and id.

Privileges: Signed-in users; assigned objects only.

Limitations

Only a single database name is accepted.

Example

SHOW LIGHTNING TABLES sales_files

5.6 LIST HDFS FILES

Syntax

LIST HDFS FILES '<dir>' OPTIONS (key 'value', …)

Lists files and folders under an HDFS, S3, Azure or local path, using the credentials you pass.

Privileges: Administrators only.

Example

LIST HDFS FILES "s3a://datathon-bucket/sales/" OPTIONS (
  awsAccessKeyId "AKIAEXAMPLE",
  awsSecretAccessKey "example-secret",
  isS3BucketPublic "false"
)

5.7 UNCACHE LIGHTNING FILESTORE TABLE

Syntax

UNCACHE LIGHTNING FILESTORE TABLE <table> FROM <database>

Clears a file-store table's cached metadata and dependent caches, then reloads Presto catalogs.

Privileges: Signed-in users; assigned objects only.

Example

UNCACHE LIGHTNING FILESTORE TABLE transactions FROM sales_files

5.8 UPDATE LIGHTNING FILESTORE TABLE

Syntax

UPDATE LIGHTNING FILESTORE TABLE <table> FROM <database> OPTIONS (key 'value', …)

Updates a file-store table's stored options; changing the path is rejected. Include the original path in OPTIONS even when updating other settings. On Zetaris 2.4.3.1, an update without it returned Path is not provided in the 12 October 2026 live check.

Privileges: Administrators only.

Example

UPDATE LIGHTNING FILESTORE TABLE transactions FROM sales_files OPTIONS (
  path "s3a://datathon-bucket/sales/transactions.csv",
  header "true",
  delimiter ";"
)

5.9 DROP LIGHTNING DATABASE TABLE

Syntax

DROP LIGHTNING DATABASE TABLE <database>.<table>

Removes a file-store or REST table from a Lightning database.

Privileges: Administrators only.

Limitations

The table must be qualified with its database name.

Example

DROP LIGHTNING DATABASE TABLE sales_files.transactions

5.10 DROP LIGHTNING DATABASE

Syntax

DROP LIGHTNING DATABASE <database>

Deletes a Lightning database with its tables and privileges.

Privileges: Administrators only.

Example

DROP LIGHTNING DATABASE sales_files

6. REST Sources and Authentication

A REST table exposes the JSON response of an HTTP endpoint as a table. Saved token requests (auth definitions) supply access tokens to REST tables.

Prerequisites

REST requests need an endpoint reachable from Zetaris and any required authentication. Persistent tables need a Lightning database and creation permission. Saved AUTH operations need a definition; token requests need the token endpoint and credentials. LIST AUTH may be empty.

6.1 CREATE REST SESSION TABLE

Syntax

CREATE REST SESSION TABLE <table> REQUEST (key 'value', …) HEADER (key 'value', …) BODY (key 'value', …) [AUTH BY <auth>[, …]]

Calls a REST endpoint and holds the result as temporary table session.<table>.

Privileges: Administrators only.

Remarks

Session tables are visible within the organisation until they are dropped.
Read the table as session.<table> and remove it with DROP TABLE session.<table>.

Example

CREATE REST SESSION TABLE weather_preview REQUEST (
  endpoint "https://api.example.com/v1/weather/daily",
  method "GET",
  http_encoding "URLENCODED",
  response_type "JSON",
  insecure_ssl "false"
) HEADER () BODY () AUTH BY weather_oauth
SELECT * FROM session.weather_preview

6.2 CREATE LIGHTNING REST TABLE

Syntax

CREATE LIGHTNING REST TABLE <table> FROM <database> REQUEST (key 'value', …) HEADER (key 'value', …) BODY (key 'value', …) [AUTH BY <auth>[, …]]

Registers a REST table by calling its endpoint and inferring the JSON schema. AUTH BY is optional.

Privileges: Administrators only.

Remarks

Query the table as <database>.<table>.

Example

CREATE LIGHTNING REST TABLE daily_weather FROM weather_api REQUEST (
  endpoint "https://api.example.com/v1/weather/daily",
  method "GET",
  http_encoding "URLENCODED",
  response_type "JSON",
  insecure_ssl "false"
) HEADER () BODY () AUTH BY weather_oauth

6.3 UPSERT AUTH

Syntax

UPSERT AUTH <auth> REQUEST (key 'value', …) HEADER (key 'value', …) BODY (key 'value', …)

Creates or updates a named token-request definition: endpoint, method, headers and body.

Privileges: Administrators only.

Example

UPSERT AUTH weather_oauth REQUEST (
  endpoint "https://auth.example.com/oauth/token",
  method "POST",
  http_encoding "URLENCODED",
  insecure_ssl "false"
) HEADER () BODY (
  grant_type "client_credentials",
  client_id "datathon-app",
  client_secret "example-secret"
)

6.4 TEST AUTH REQUEST

Syntax

TEST AUTH REQUEST (key 'value', …) HEADER (key 'value', …) BODY (key 'value', …)

Runs an ad-hoc token request and returns the response fields.

Privileges: Administrators only.

Example

TEST AUTH REQUEST (
  endpoint "https://auth.example.com/oauth/token",
  method "POST",
  http_encoding "URLENCODED",
  insecure_ssl "false"
) HEADER () BODY (
  grant_type "client_credentials",
  client_id "datathon-app",
  client_secret "example-secret"
)

6.5 TEST AUTH

Syntax

TEST AUTH <auth>

Runs a saved token-request definition and returns the response fields.

Privileges: Administrators only.

Example

TEST AUTH weather_oauth

6.6 TEST REST REQUEST

Syntax

TEST REST REQUEST (key 'value', …) HEADER (key 'value', …) BODY (key 'value', …) [AUTH BY <auth>[, …]]

Runs a REST request, optionally authorised by saved AUTH definitions, and returns the rows.

Privileges: Administrators only.

Example

TEST REST REQUEST (
  endpoint "https://api.example.com/v1/weather/daily",
  method "GET",
  http_encoding "URLENCODED",
  response_type "JSON",
  insecure_ssl "false"
) HEADER () BODY () AUTH BY weather_oauth

6.7 SHOW AUTH

Syntax

SHOW AUTH <auth>

Shows a saved token-request definition, including its header and body values.

Privileges: Administrators only.

Example

SHOW AUTH weather_oauth

6.8 LIST AUTH

Syntax

LIST AUTH

Lists saved token-request definitions: name, endpoint, method, encoding, content type.

Privileges: Administrators only.

Example

LIST AUTH

6.9 DROP AUTH

Syntax

DROP AUTH <auth>

Deletes a saved token-request definition.

Privileges: Administrators only.

Example

DROP AUTH weather_oauth

7. Streaming

Streaming sources read JSON messages from a Kafka topic. Aggregation tables run a continuous query over a streaming source and write the result to storage.

Prerequisites

Registering a stream needs Kafka broker/topic access, a sample JSON message and creation permission. An aggregation also needs writable output storage. Aggregation listing/removal needs its registered source.

7.1 REGISTER STREAMING DATASOURCE

Syntax

REGISTER STREAMING DATASOURCE <name>
INPUT_SOURCE KAFKA
INPUT_FORMAT JSON
INPUT_OPTIONS (key 'value', …)
INPUT_SAMPLE <sample message>

Registers a Kafka topic as a streaming source; the schema comes from INPUT_SAMPLE.

Privileges: Administrators only.

Limitations

Only Kafka input sources and JSON messages are supported.

Remarks

The Kafka broker must be reachable from the Lightning platform. A broker addressed as localhost on a participant's computer is not reachable.
The schema is derived from INPUT_SAMPLE; supply one representative message.
Requires an external service: Kafka.

Example

REGISTER STREAMING DATASOURCE clickstream
INPUT_SOURCE KAFKA
INPUT_FORMAT JSON
INPUT_OPTIONS(
  inferSchema "false",
  kafka.bootstrap.servers "kafka.example.com:9092",
  kafka.topic "clicks",
  security.protocol "PLAINTEXT"
)
INPUT_SAMPLE
{"user_id": 42, "page": "/pricing", "ts": 1727222400}

7.2 REGISTER STREAMING AGGREGATION

Syntax

REGISTER STREAMING AGGREGATION <table> FROM <source>
OUTPUT_MODE {APPEND | COMPLETE | UPDATE}
OUTPUT_FORMAT {PARQUET | ORC | CSV | JDBC}
OUTPUT_OPTIONS (key 'value', …)
AS <SELECT query>

Starts a streaming query over a source and writes the result to Parquet, ORC, CSV or JDBC.

Privileges: Administrators only.

Limitations

Supported output formats are PARQUET, ORC, CSV and JDBC.

Remarks

Requires an external service: Kafka.

Example

REGISTER STREAMING AGGREGATION pricing_clicks FROM clickstream
OUTPUT_MODE APPEND
OUTPUT_FORMAT PARQUET
OUTPUT_OPTIONS(path "s3a://datathon-bucket/streams/pricing_clicks")
AS SELECT user_id, page, from_unixtime(ts) AS clicked_at FROM clickstream WHERE page LIKE '/pricing%'

7.3 SHOW STREAMING DATASOURCE

Syntax

SHOW STREAMING DATASOURCE

Lists the streaming sources you can see: name, input, format, schema.

Privileges: Signed-in users; assigned objects only.

Example

SHOW STREAMING DATASOURCE

7.4 SHOW STREAMING AGGREGATION TABLES

Syntax

SHOW STREAMING AGGREGATION TABLES <source>

Lists a streaming source's aggregation tables: output mode, format, schema, query.

Privileges: Signed-in users; assigned objects only.

Example

SHOW STREAMING AGGREGATION TABLES clickstream

7.5 DROP STREAMING DATASOURCE

Syntax

DROP STREAMING DATASOURCE <source>

Stops and deletes a streaming source, and revokes its privileges.

Privileges: Administrators only.

Example

DROP STREAMING DATASOURCE clickstream

7.6 DROP STREAMING AGGREGATION TABLE

Syntax

DROP STREAMING AGGREGATION TABLE <source>.<table>

Stops and deletes aggregation table <source>.<table>.

Privileges: Administrators only.

Example

DROP STREAMING AGGREGATION TABLE clickstream.pricing_clicks

8. Data Lakes

A data lake is an Iceberg lakehouse registered on object or file storage.

Prerequisites

Creation needs accessible Iceberg storage, connection settings and creation permission. Table listing needs a registered lake. RELOAD DATALAKE affects all engine catalogs; confirm that scope first.

8.1 CREATE DATALAKE

Syntax

CREATE DATALAKE <name> OPTIONS (key 'value', …)

Registers an Iceberg data lake on S3, Azure, GCS, HDFS or local storage after checking the path.

Privileges: Administrators only.

Example

CREATE DATALAKE datathon_lake OPTIONS (
  storageType "S3",
  storagePath "s3a://datathon-bucket/lake/",
  awsAccessKeyId "AKIAEXAMPLE",
  awsSecretAccessKey "example-secret"
)

8.2 SHOW DATALAKES

Syntax

SHOW DATALAKES

Lists accessible data lakes.

Privileges: Signed-in users; assigned objects only.

Example

SHOW DATALAKES

8.3 SHOW DATALAKE TABLES

Syntax

SHOW DATALAKE TABLES <database>

Lists a data lake's Iceberg tables.

Privileges: Signed-in users; assigned objects only.

Remarks

Reads the lake storage directly; the command can take several minutes on a large lake.

Example

SHOW DATALAKE TABLES datathon_lake

8.4 RELOAD DATALAKE

Syntax

RELOAD DATALAKE

Tells every Presto engine to reload its catalogs and drops cached Spark catalogs.

Privileges: Administrators only.

Remarks

Requires an external service: Presto.

Example

RELOAD DATALAKE

9. External Catalogs

External catalogs connect a Unity Catalog server under a local alias.

Prerequisites

Registration needs Unity Catalog server access, authentication, an existing catalog and creation permission. Namespace/table commands need an accessible registered catalog. SHOW UNITY CATALOG needs no registration.

9.1 REGISTER UNITY CATALOG

Syntax

REGISTER UNITY CATALOG <alias> NAME <catalog> OPTIONS (key 'value', …)

Registers a Unity Catalog under an alias after checking the catalog exists on the server.

Privileges: Administrators only.

Remarks

The option unityCatalogUri is required.
Requires an external service: Unity Catalog server.

Example

REGISTER UNITY CATALOG uc_demo NAME main OPTIONS (
  useCatalogTemporaryCredentials "true",
  unityCatalogUri "https://uc.example.com",
  unityCatalogToken "dapi-example-token"
)

9.2 SHOW UNITY CATALOG

Syntax

SHOW UNITY CATALOG

Lists registered Unity Catalogs: alias, name, masked options, schema count.

Privileges: Signed-in users; assigned objects only.

Example

SHOW UNITY CATALOG

9.3 SHOW UC NAMESPACES

Syntax

SHOW UC NAMESPACES IN <alias>[.<namespace> …]

Lists a Unity Catalog's schemas with child counts, filtered to what you can access.

Privileges: Signed-in users; assigned objects only.

Remarks

Requires an external service: Unity Catalog server.

Example

SHOW UC NAMESPACES IN uc_demo

9.4 SHOW UC TABLES

Syntax

SHOW UC TABLES IN <alias>.<namespace>[.<namespace> …]

Lists the tables in a Unity Catalog schema.

Privileges: Signed-in users; assigned objects only.

Remarks

Users other than administrators require an assignment on the schema.
Requires an external service: Unity Catalog server.

Example

SHOW UC TABLES IN uc_demo.default

9.5 UNCACHE UNITY CATALOG

Syntax

UNCACHE UNITY CATALOG <name>

Evicts a Unity Catalog connection from the driver cache so it reloads.

Privileges: Administrators only.

Example

UNCACHE UNITY CATALOG uc_demo

9.6 DROP UNITY CATALOG

Syntax

DROP UNITY CATALOG <alias>

Removes a Unity Catalog registration and unloads it.

Privileges: Administrators only.

Example

DROP UNITY CATALOG uc_demo

10. Hive Metastore

A Hive metastore registration synchronises database and table metadata from an existing Hive metastore database.

Prerequisites

Registration/remote listing needs Hive metastore access, its JDBC driver and credentials. Named listing/removal needs a registered metastore or synced database. LIST HIVE METASTORES may be empty.

10.1 REGISTER HIVE METASTORE

Syntax

REGISTER HIVE METASTORE <name> OPTIONS (key 'value', …)

Registers a Hive metastore (JDBC to its database) and syncs the listed databases.

Privileges: Administrators only.

Remarks

Requires an external service: Hive metastore.

Example

REGISTER HIVE METASTORE hive_prod OPTIONS (
  hive_meta_jdbc_url "jdbc:mysql://hive-db.example.com:3306/metastore",
  hive_meta_user_name "hive",
  hive_meta_password "changeme",
  hive_meta_jdbc_driver "com.mysql.cj.jdbc.Driver",
  databases "sales"
)

10.2 LIST HIVE METASTORES

Syntax

LIST HIVE METASTORES

Lists accessible Hive metastores.

Privileges: Signed-in users; assigned objects only.

Example

LIST HIVE METASTORES

10.3 LIST HIVE METASTORE TABLES REMOTE

Syntax

LIST HIVE METASTORE TABLES REMOTE OPTIONS (key 'value', …)

Lists live tables for the databases named in OPTIONS, over an inline connection.

Privileges: Administrators only.

Remarks

The databases key is case-sensitive.
Requires an external service: Hive metastore.

Example

LIST HIVE METASTORE TABLES REMOTE OPTIONS (
  hive_meta_jdbc_url "jdbc:mysql://hive-db.example.com:3306/metastore",
  hive_meta_user_name "hive",
  hive_meta_password "changeme",
  hive_meta_jdbc_driver "com.mysql.cj.jdbc.Driver",
  databases "sales"
)

10.4 LIST HIVE METASTORE DATABASES

Syntax

LIST HIVE METASTORE DATABASES <metastore>

Lists the synced databases of a Hive metastore.

Privileges: Signed-in users; assigned objects only.

Example

LIST HIVE METASTORE DATABASES hive_prod

10.5 SHOW HIVE METASTORE DATABASES

Syntax

SHOW HIVE METASTORE DATABASES OPTIONS (key 'value', …)

Lists live Hive databases from inline connection options.

Privileges: Administrators only.

Remarks

Requires an external service: Hive metastore.

Example

SHOW HIVE METASTORE DATABASES OPTIONS (
  hive_meta_jdbc_url "jdbc:mysql://hive-db.example.com:3306/metastore",
  hive_meta_user_name "hive",
  hive_meta_password "changeme",
  hive_meta_jdbc_driver "com.mysql.cj.jdbc.Driver"
)

10.6 LIST HIVE METASTORE TABLES

Syntax

LIST HIVE METASTORE TABLES <source>.<table> [OPTIONS (key 'value', …)]

Lists synced tables of <metastore>.<db>; with OPTIONS it lists live tables instead.

Privileges: Depends on the form: any user; admins only.

Example

For a registered metastore:

LIST HIVE METASTORE TABLES hive_prod.sales

For a remote connection with inline options:

LIST HIVE METASTORE TABLES REMOTE OPTIONS (
  hive_meta_jdbc_url "jdbc:mysql://hive-db.example.com:3306/metastore",
  hive_meta_user_name "hive",
  hive_meta_password "changeme",
  hive_meta_jdbc_driver "com.mysql.cj.jdbc.Driver",
  databases "sales"
)

10.7 DROP HIVE METASTORE

Syntax

DROP HIVE METASTORE <source>.<table>

Deletes a registered Hive metastore (<metastore>) or one synced database (<metastore>.<db>).

Privileges: Administrators only.

Example

DROP HIVE METASTORE hive_prod

11. AWS Glue Metastore

An AWS Glue metastore registration synchronises database and table metadata from AWS Glue.

Prerequisites

Registration/remote listing needs a region, Glue catalog/database and authorised AWS authentication. Named listing/removal needs a registered metastore or synced database. LIST AWS GLUE METASTORES may be empty.

11.1 REGISTER AWS GLUE METASTORE

Syntax

REGISTER AWS GLUE METASTORE <name> OPTIONS (key 'value', …)

Registers an AWS Glue catalog (region, databases, optional keys and prefix) and syncs its metadata.

Privileges: Administrators only.

Remarks

Requires an external service: AWS Glue.

Example

REGISTER AWS GLUE METASTORE glue_prod OPTIONS (
  region "ap-southeast-2",
  databases "analytics",
  awsAccessKeyId "AKIAEXAMPLE",
  awsSecretAccessKey "example-secret"
)

11.2 LIST AWS GLUE METASTORES

Syntax

LIST AWS GLUE METASTORES

Lists accessible Glue metastores.

Privileges: Signed-in users; assigned objects only.

Example

LIST AWS GLUE METASTORES

11.3 LIST AWS GLUE METASTORE DATABASES REMOTE

Syntax

LIST AWS GLUE METASTORE DATABASES REMOTE OPTIONS (key 'value', …)

Lists live Glue databases from inline options.

Privileges: Administrators only.

Remarks

Requires an external service: AWS Glue.

Example

LIST AWS GLUE METASTORE DATABASES REMOTE OPTIONS (
  name "glue_prod",
  region "ap-southeast-2",
  awsAccessKeyId "AKIAEXAMPLE",
  awsSecretAccessKey "example-secret"
)

11.4 LIST AWS GLUE METASTORE DATABASES

Syntax

LIST AWS GLUE METASTORE DATABASES {OPTIONS (key 'value', …) | <metastore>}

With a name, lists synced databases; with OPTIONS, lists live ones.

Privileges: Depends on the form: admins only; any user.

Example

For a registered metastore:

LIST AWS GLUE METASTORE DATABASES glue_prod

For a remote connection with inline options:

LIST AWS GLUE METASTORE DATABASES REMOTE OPTIONS (
  name "glue_prod",
  region "ap-southeast-2",
  awsAccessKeyId "AKIAEXAMPLE",
  awsSecretAccessKey "example-secret"
)

11.5 LIST AWS GLUE METASTORE TABLES REMOTE

Syntax

LIST AWS GLUE METASTORE TABLES REMOTE OPTIONS (key 'value', …)

Lists live tables for the databases in OPTIONS original_names, over an inline connection.

Privileges: Administrators only.

Remarks

original_names is read case-sensitively.
Requires an external service: AWS Glue.

Example

LIST AWS GLUE METASTORE TABLES REMOTE OPTIONS (
  name "glue_prod",
  region "ap-southeast-2",
  original_names "analytics",
  awsAccessKeyId "AKIAEXAMPLE",
  awsSecretAccessKey "example-secret"
)

11.6 LIST AWS GLUE METASTORE TABLES

Syntax

LIST AWS GLUE METASTORE TABLES <source>.<table> [OPTIONS (key 'value', …)]

Lists synced tables of <metastore>.<db>; with OPTIONS it lists live tables instead.

Privileges: Depends on the form: any user; admins only.

Example

For a registered metastore:

LIST AWS GLUE METASTORE TABLES glue_prod.analytics

For a remote connection with inline options:

LIST AWS GLUE METASTORE TABLES REMOTE OPTIONS (
  name "glue_prod",
  region "ap-southeast-2",
  original_names "analytics",
  awsAccessKeyId "AKIAEXAMPLE",
  awsSecretAccessKey "example-secret"
)

11.7 DROP AWS GLUE METASTORE

Syntax

DROP AWS GLUE METASTORE <source>.<table>

Deletes a registered Glue metastore (<metastore>) or one synced database (<metastore>.<db>).

Privileges: Administrators only.

Example

DROP AWS GLUE METASTORE glue_prod

12. Schema Store Views

A schema store view is a stored Spark SQL view held in a container. Query Builder views are stored the same way.

Prerequisites

Views need an accessible source, an existing container and creation permission. First create your own container. Preview can use SELECT 1 without a container. Rename/removal needs an existing view.

12.1 CREATE [OR REPLACE] SCHEMASTORE VIEW

Syntax

CREATE [OR REPLACE] SCHEMASTORE VIEW <view> WITH CONTAINER <container> AS <SELECT query …>

Creates a Spark SQL view in a container; OR REPLACE overwrites an existing view.

Privileges: Administrators or Query Builder Write users.

Remarks

Query a view as <container>.<view>.

Example

CREATE SCHEMASTORE VIEW reasons WITH CONTAINER analysis AS
SELECT r_reason_sk, r_reason_desc FROM TPCDS_DB.reason

12.2 CREATE SCHEMASTORE CONTAINER

Syntax

CREATE SCHEMASTORE CONTAINER <name>

Creates a schema-store view container, the folder Query Builder views live in.

Privileges: Administrators only.

Example

CREATE SCHEMASTORE CONTAINER analysis

12.3 PREVIEW SCHEMASTORE SQL

Syntax

PREVIEW SCHEMASTORE SQL AS <SELECT query …>

Runs a Spark SQL query and returns up to 10 rows.

Privileges: Administrators only.

Remarks

Begin the statement with PREVIEW SCHEMASTORE SQL AS.

Example

PREVIEW SCHEMASTORE SQL AS SELECT country, COUNT(*) AS customers FROM crm_pg.customers GROUP BY country

12.4 SHOW DATASOURCE VIEWS

Syntax

SHOW DATASOURCE VIEWS

Lists accessible schema-store views: name, generator and cached table.

Privileges: Signed-in users; assigned objects only.

Example

SHOW DATASOURCE VIEWS

12.5 UPDATE SCHEMASTORE VIEW

Syntax

UPDATE SCHEMASTORE VIEW <source>.<table> SET NAME <new_name>

Renames a schema-store view and updates its grants and data-mart references.

Privileges: Administrators or Query Builder Write users.

Remarks

Pipelines that refer to the view by its previous name must be updated separately.
Needs <container>.<view>.

Example

UPDATE SCHEMASTORE VIEW analysis.top_customers SET NAME best_customers

12.6 DROP SCHEMASTORE VIEW

Syntax

DROP SCHEMASTORE VIEW <view> FROM <container>

Deletes a schema-store view unless a data mart uses it, and revokes its privileges.

Privileges: Administrators only.

Example

DROP SCHEMASTORE VIEW best_customers FROM analysis

13. Unified Semantic Layer

A Unified Semantic Layer (USL) is a semantic model of tables, keys and relationships. Each table is compiled from DDL, deployed to a namespace under lightning.metastore, and activated with a query over source data. An activated table is queried as lightning.metastore.<namespace>.<usl>.<table>.

Prerequisites

Deployment needs creation permission and a namespace under lightning.metastore. Activation needs an accessible SELECT matching the table schema. Load/update needs an existing USL. Compile without DEPLOY returns a model without saving it.

13.1 COMPILE USL

Syntax

COMPILE USL <usl> [DEPLOY] NAMESPACE <namespace> DDL
<CREATE TABLE statement>;
[<CREATE TABLE statement>; …]

Compiles USL table DDL into a semantic model; with DEPLOY it saves the model under the namespace.

Privileges: Administrators only.

Limitations

Columns must use scalar data types. ARRAY, MAP and STRUCT types are not supported.
Each CREATE TABLE statement must begin on a new line.

Remarks

Separate CREATE TABLE definitions with ;. SQL Workspace splits editor text at those separators. For multiple tables, use Unified Semantic Layer → New USL, or send the whole COMPILE statement over JDBC/HTTP with a runner that keeps it intact. Do not send each definition separately.
Without DEPLOY, the command returns the model definition as JSON and stores nothing.
The namespace must exist; create it with CREATE NAMESPACE IF NOT EXISTS lightning.metastore.<name>.

Example

COMPILE USL retail DEPLOY NAMESPACE lightning.metastore.datathon DDL
CREATE TABLE customer (
  id int NOT NULL PRIMARY KEY,
  full_name varchar(200),
  email varchar(200),
  country varchar(50)
);
CREATE TABLE orders (
  id int NOT NULL PRIMARY KEY,
  customer_id int FOREIGN KEY REFERENCES customer(id),
  amount decimal(10,2),
  status varchar(20)
)

13.2 LOAD USL

Syntax

LOAD USL <usl> NAMESPACE <namespace>

Returns a USL as JSON: tables, ordered columns, keys and materialization info.

Privileges: Signed-in users; assigned objects only.

Example

LOAD USL retail NAMESPACE lightning.metastore.datathon

13.3 SHOW NAMESPACES OR TABLES

Syntax

SHOW NAMESPACES OR TABLES IN <namespace>

Lists the namespaces, USLs and tables (with activation flag) under a namespace, filtered by access.

Privileges: Signed-in users; assigned objects only.

Remarks

Use this command in place of SHOW NAMESPACES and SHOW TABLES.

Example

SHOW NAMESPACES OR TABLES IN lightning.metastore

13.4 ACTIVATE USL TABLE

Syntax

ACTIVATE USL TABLE <namespace.usl.table> AS <SELECT query …>

Binds a USL table to a SELECT query after checking the query matches the declared schema.

Privileges: Administrators only.

Remarks

Any ANSI SQL or Spark built-in function can shape the columns; a query column may not be downcast to the declared type.
The query must return the declared columns with compatible types. A column declared NOT NULL requires a non-null expression, for example COALESCE(id, 0).

Example

ACTIVATE USL TABLE lightning.metastore.datathon.retail.customer AS
SELECT COALESCE(id, 0) AS id, full_name, email, country
FROM crm_pg.customers

13.5 UPDATE USL … AS (replace model)

Syntax

UPDATE USL <usl> NAMESPACE <namespace> AS <USL JSON …>

Replaces a whole USL from its JSON form.

Privileges: Administrators only.

Remarks

Start from the JSON returned by LOAD USL. The command replaces the entire model.
Re-run ACTIVATE USL TABLE after replacement before querying the table. On Zetaris 2.4.3.1, the 12 October 2026 live check accepted JSON containing activateQuery but the next SELECT returned table(...) is not activated. Reactivation restored query access.

Example

UPDATE USL retail NAMESPACE lightning.metastore.datathon AS {"namespace": ["metastore", "datathon"], "name": "retail", "tables": […]}

13.6 UPDATE USL … SET DESCRIPTION

Syntax

UPDATE USL <namespace.usl.table[.column]> SET [TABLE | COLUMN] DESCRIPTION <text …>

Sets the description of a USL, one of its tables, or one column.

Privileges: Administrators only.

Example

UPDATE USL lightning.metastore.datathon.retail.customer SET TABLE DESCRIPTION One row per customer

13.7 UPDATE USL … SET COLUMN ORDER

Syntax

UPDATE USL <namespace.usl.table> SET COLUMN ORDER <col1, col2, …>

Saves a table's column display order; list every column, comma-separated.

Privileges: Administrators only.

Example

UPDATE USL lightning.metastore.datathon.retail.customer SET COLUMN ORDER id, full_name, country, email

13.8 REMOVE USL

Syntax

REMOVE USL <usl> NAMESPACE <namespace>

Deletes a USL.

Privileges: Administrators only.

Example

REMOVE USL retail NAMESPACE lightning.metastore.datathon

14. Data Quality

Data-quality rules are boolean expressions evaluated against activated USL tables.

Prerequisites

Rules and record inspection need an activated USL table. Registering a rule needs model-change permission. Named operations need an existing rule; check names with LIST DQ USL.

14.1 REGISTER DQ

Syntax

REGISTER DQ <rule> TABLE <namespace.usl.table> AS <boolean expression …>

Adds a named data-quality rule, a boolean expression, to an activated USL table.

Privileges: Administrators only.

Example

REGISTER DQ valid_email TABLE lightning.metastore.datathon.retail.customer AS
email IS NOT NULL AND email LIKE '%@%'

14.2 LIST DQ USL

Syntax

LIST DQ USL <namespace.usl>

Lists the key constraints and data-quality rules for every table in a USL.

Privileges: Signed-in users; assigned objects only.

Example

LIST DQ USL lightning.metastore.datathon.retail

14.3 SHOW DQ VALID / INVALID RECORD

Syntax

SHOW DQ {VALID | INVALID} RECORD <rule> TABLE <namespace.usl.table>

Returns the records that pass (VALID) or fail (INVALID) one rule.

Privileges: Signed-in users; assigned objects only.

Remarks

Results are streamed.

Example

SHOW DQ INVALID RECORD valid_email TABLE lightning.metastore.datathon.retail.customer

14.4 SHOW DQ ALL VALID / INVALID

Syntax

SHOW DQ ALL {VALID | INVALID} TABLE <namespace.usl.table>

Returns the records that pass every rule, or fail any rule.

Privileges: Administrators only.

Example

SHOW DQ ALL INVALID TABLE lightning.metastore.datathon.retail.customer

14.5 UPDATE DQ

Syntax

UPDATE DQ <rule> TABLE <namespace.usl.table> SET NAME = <new_rule>, EXPRESSION = <boolean expression …>

Renames a data-quality rule and/or changes its expression.

Privileges: Administrators only.

Example

UPDATE DQ valid_email TABLE lightning.metastore.datathon.retail.customer
SET NAME = valid_email_v2, EXPRESSION = email LIKE '%@%.%'

14.6 RUN DQ

Syntax

RUN DQ <rule> TABLE <namespace>.<usl>.<table>

Runs one rule, or all rules and key constraints, and returns total, valid and invalid counts.

Privileges: Signed-in users; assigned objects only.

Limitations

A rule name must be specified.

Remarks

For a composite key, enclose the column list in backquotes, for example RUN DQ id,name TABLE ….

Example

RUN DQ valid_email TABLE lightning.metastore.datathon.retail.customer

14.7 REMOVE DQ

Syntax

REMOVE DQ <rule> TABLE <namespace.usl.table>

Deletes a data-quality rule.

Privileges: Administrators only.

Remarks

The rule is removed from every table in the USL.

Example

REMOVE DQ valid_email_v2 TABLE lightning.metastore.datathon.retail.customer

15. Export and Materialization

These commands write a query result or a USL table to storage.

Prerequisites

Export needs a writable destination, storage settings/credentials and query access. USL materialisation also needs an activated table. Confirm the path and overwrite/append behaviour before writing.

15.1 RUN EXPORT TO STORAGE

Syntax

RUN EXPORT TO STORAGE (key 'value', …)

Writes query results to cloud storage or an Iceberg table.

Privileges: Signed-in users; assigned objects only.

Remarks

optionsBase64 is a base64-encoded JSON object of write and storage options; the example encodes {"header":"true"}.
format is csv, json, parquet, orc or delta; saveMode is overwrite, append, ignore or errorifexists.
The Export action in the SQL Workspace builds this command, including the storage credentials.

Example

RUN EXPORT TO STORAGE (
  query = 'SELECT * FROM crm_pg.customers WHERE country = ''AU''',
  path = 's3a://datathon-bucket/exports/au_customers',
  format = 'csv',
  saveMode = 'overwrite',
  optionsBase64 = 'eyJoZWFkZXIiOiJ0cnVlIn0='
)

15.2 MATERIALIZE USL TABLE

Syntax

MATERIALIZE USL TABLE <namespace.usl.table> TO STORAGE (key 'value', …)

Runs a USL table's activation query and writes the result to storage.

Privileges: Administrators only.

Remarks

recordType is all or valid; valid writes only rows that pass the data-quality rules.
The Materialize action on a USL table builds this command, including the storage setting and credentials.

Example

MATERIALIZE USL TABLE lightning.metastore.datathon.retail.customer TO STORAGE (
  recordType = 'all',
  path = 's3a://datathon-bucket/usl/retail/customer',
  format = 'parquet',
  storageSettingId = '<storage setting id>',
  optionsBase64 = 'eyJoZWFkZXIiOiJ0cnVlIn0='
)

16. Data Pipelines

Pipelines are built in the Data Pipeline designer and stored in pipeline containers. Several commands in this chapter take the pipeline specification in JSON form.

Prerequisites

Saving needs your own container, write permission and a complete specification with accessible sources. Infer/verify needs a valid query or specification. Named describe/update/drop needs a saved relation.

16.1 CREATE PIPELINE CONTAINER

Syntax

CREATE PIPELINE CONTAINER <name> [DESCRIPTION '<desc>']

Creates a pipeline container.

Privileges: Administrators or Data Pipeline Write users.

Example

CREATE PIPELINE CONTAINER etl DESCRIPTION 'Daily ETL'

16.2 REGISTER PIPELINE RELATION CONTAINER

Syntax

REGISTER PIPELINE RELATION CONTAINER <container> SPEC <pipeline JSON …>

Saves a pipeline into a container.

Privileges: Administrators or Data Pipeline Write users.

Remarks

The specification is the JSON returned by DESCRIBE PIPELINE RELATION. Pipelines are normally built in the Data Pipeline designer.

Example

REGISTER PIPELINE RELATION CONTAINER etl SPEC
{"name": "daily_orders", "nodes": [
  {"nodeType": "DataSource", "name": "orders_source",
   "columns": [{"name": "customer_id", "dataType": "int"}, {"name": "amount", "dataType": "decimal(10,2)"}],
   "properties": [], "sources": ["crm_pg.orders"]},
  {"nodeType": "Projection", "name": "orders_projection",
   "columns": [{"name": "customer_id", "dataType": "int"}, {"name": "amount", "dataType": "decimal(10,2)"}],
   "properties": [], "sources": ["orders_source"]}
]}

16.3 LIST PIPELINE CONTAINER

Syntax

LIST PIPELINE CONTAINER

Lists accessible pipeline containers.

Privileges: Signed-in users; assigned objects only.

Example

LIST PIPELINE CONTAINER

16.4 LIST PIPELINE RELATION

Syntax

LIST PIPELINE RELATION <container>

Lists the pipelines in a container.

Privileges: Signed-in users; assigned objects only.

Example

LIST PIPELINE RELATION etl

16.5 DESCRIBE PIPELINE RELATION

Syntax

DESCRIBE PIPELINE RELATION <container>.<pipeline>

Returns a saved pipeline's JSON.

Privileges: Administrators or Data Pipeline Read or Write users.

Limitations

The pipeline must be qualified with its container name.

Remarks

Returns the pipeline specification in JSON form.

Example

DESCRIBE PIPELINE RELATION etl.daily_orders

16.6 VERIFY PIPELINE

Syntax

VERIFY PIPELINE SPEC <pipeline JSON …>

Validates a whole pipeline spec, source access included; returns validity, schema and error.

Privileges: Administrators or Data Pipeline Write users.

Remarks

Returns the validity, the output schema and any error.

Example

VERIFY PIPELINE SPEC {"name": "daily_orders", "nodes": […]}

16.7 INFER SQLTABLE SOURCES

Syntax

INFER SQLTABLE SOURCES SPEC <SQL query …>

Returns the source tables and aliases that a SQL query reads.

Privileges: Administrators or Data Pipeline Write users.

Example

INFER SQLTABLE SOURCES SPEC SELECT c.id, o.amount FROM crm_pg.customers c JOIN crm_pg.orders o ON o.customer_id = c.id

16.8 INFER SQLTABLE COLUMNS

Syntax

INFER SQLTABLE COLUMNS SPEC <SQL query …>

Returns a SQL query's output columns with their source table.column and type.

Privileges: Administrators or Data Pipeline Write users.

Example

INFER SQLTABLE COLUMNS SPEC SELECT c.id, o.amount FROM crm_pg.customers c JOIN crm_pg.orders o ON o.customer_id = c.id

16.9 UPDATE PIPELINE RELATION

Syntax

UPDATE PIPELINE RELATION <container>.<pipeline> SPEC <pipeline JSON>

Replaces a saved pipeline's JSON.

Privileges: Administrators or Data Pipeline Write users.

Limitations

The pipeline must be qualified with its container name.

Remarks

The specification is the JSON returned by DESCRIBE PIPELINE RELATION.

Example

UPDATE PIPELINE RELATION etl.daily_orders SPEC {"name": "daily_orders", "nodes": […]}

16.10 RUN PIPELINE

Syntax

RUN PIPELINE ON '<node>' [LIMIT <n>] SPEC <pipeline JSON …>

Runs an unsaved pipeline up to the named node and returns its rows.

Privileges: Administrators or Data Pipeline Read or Write users.

Remarks

Runs the unsaved specification up to the named node and returns its rows.

Example

RUN PIPELINE ON 'orders_projection' LIMIT 100 SPEC {"name": "daily_orders", "nodes": […]}

16.11 DROP PIPELINE CONTAINER

Syntax

DROP PIPELINE CONTAINER <name>

Deletes a pipeline container with its dependent views and privileges.

Privileges: Administrators or Data Pipeline Write users.

Example

DROP PIPELINE CONTAINER etl

16.12 DROP PIPELINE RELATION

Syntax

DROP PIPELINE RELATION <container>.<pipeline>

Deletes a saved pipeline.

Privileges: Administrators or Data Pipeline Write users.

Limitations

The pipeline must be qualified with its container name.

Example

DROP PIPELINE RELATION etl.daily_orders

17. Virtual Data Marts

A virtual data mart presents source tables under new table and column names without copying data.

Prerequisites

Creation needs write permission. Adding a table needs an existing mart and source access; map every source column. Other operations need the existing mart or table.

17.1 CREATE DATAMART

Syntax

CREATE DATAMART <name> [DESCRIBE BY '<description>']

Creates a virtual data mart.

Privileges: Administrators or Virtual Data Mart Write users.

Example

CREATE DATAMART sales_mart DESCRIBE BY 'Sales mart'

17.2 ADD DATAMART TABLE

Syntax

ADD DATAMART TABLE <table> INTO <datamart> FROM <source.path> VSCHEMA (<column> <virtual_name>, …)

Adds a virtual table over a source table; every source column must be given a virtual name.

Privileges: Administrators or Virtual Data Mart Write users.

Remarks

Query a data-mart table as <datamart>.<table>.

Example

ADD DATAMART TABLE customer_v INTO sales_mart FROM crm_pg.customers
VSCHEMA (id customer_id, full_name name, email email, country country, created_at signup_date)

17.3 SHOW DATAMARTS

Syntax

SHOW DATAMARTS

Lists accessible data marts.

Privileges: Signed-in users; assigned objects only.

Example

SHOW DATAMARTS

17.4 SHOW DATAMART TABLES

Syntax

SHOW DATAMART TABLES <datamart>

Lists a data mart's tables with their sources.

Privileges: Signed-in users; assigned objects only.

Example

SHOW DATAMART TABLES sales_mart

17.5 DESCRIBE DATAMART TABLE

Syntax

DESCRIBE DATAMART TABLE <source>.<table>

Returns a data-mart table's real-to-virtual column mapping.

Privileges: Administrators or Virtual Data Mart Read or Write users.

Example

DESCRIBE DATAMART TABLE sales_mart.customer_v

17.6 UPDATE DATAMART

Syntax

UPDATE DATAMART <name> OPTIONS (key 'value', …)

Renames a data mart and/or sets its description.

Privileges: Administrators or Virtual Data Mart Write users.

Remarks

Change the name and the description in separate statements.

Example

UPDATE DATAMART sales_mart OPTIONS (DESCRIPTION "Sales mart for the datathon")

17.7 UPDATE DATAMART TABLE … SET VSCHEMA

Syntax

UPDATE DATAMART TABLE <source>.<table> SET VSCHEMA (<column> <virtual_name>,…)

Rewrites a data-mart table's virtual column names.

Privileges: Administrators or Virtual Data Mart Write users.

Example

UPDATE DATAMART TABLE sales_mart.customer_v SET VSCHEMA (id customer_id, full_name customer_name, email email, country country, created_at signup_date)

17.8 UPDATE DATAMART TABLE … SET NAME

Syntax

UPDATE DATAMART TABLE <source>.<table> SET NAME <new_name>

Renames a data-mart table.

Privileges: Administrators or Virtual Data Mart Write users.

Example

UPDATE DATAMART TABLE sales_mart.customer_v SET NAME customers

17.9 DROP DATAMART

Syntax

DROP DATAMART <name>

Deletes a data mart with its dependent views and privileges.

Privileges: Administrators or Virtual Data Mart Write users.

Example

DROP DATAMART sales_mart

17.10 DROP DATAMART TABLE

Syntax

DROP DATAMART TABLE <source>.<table>

Deletes data-mart table <datamart>.<table>.

Privileges: Administrators or Virtual Data Mart Write users.

Example

DROP DATAMART TABLE sales_mart.customers

18. Query Plans

This chapter describes the query-plan command.

Prerequisites

Administrator permission and an accessible SELECT query. EXPLAIN QUERY AS SELECT 1 needs no source table.

18.1 EXPLAIN QUERY

Syntax

EXPLAIN QUERY AS <SELECT query>

Returns the query plan for a statement.

Privileges: Administrators only.

Limitations

Only SELECT statements are supported.

Remarks

Use this command only with SELECT statements.

Example

EXPLAIN QUERY AS SELECT country, COUNT(*) FROM crm_pg.customers GROUP BY country

19. Caching

Off-site caching stores a copy of a table in memory or in file storage to accelerate queries.

Prerequisites

Caching needs a table, source access and cache-management permission. Update/uncache needs an active cache. Policy/history lists may be empty. INVALIDATE CACHE affects every organisation; confirm that scope first.

19.1 SHOW CACHE TABLES

Syntax

SHOW CACHE TABLES

Lists cached tables with storage, size, partitions and hit counts.

Privileges: Administrators only.

Example

SHOW CACHE TABLES

19.2 SHOW CACHE POLICY

Syntax

SHOW CACHE POLICY

Lists stored organisation cache policies.

Privileges: Administrators only.

Example

SHOW CACHE POLICY

19.3 SHOW CACHE TABLE POLICY

Syntax

SHOW CACHE TABLE POLICY [<datasource>]

Lists per-table cache policies, optionally for one datasource.

Privileges: Administrators only.

Example

SHOW CACHE TABLE POLICY crm_pg

19.4 OFFSITE CACHE TABLE

Syntax

OFFSITE CACHE TABLE <source>.<table> TO {ONSITEMEMORY | MEMORY | FILE} WITH (key 'value', …)

Caches a table in Spark memory or a file cache, with expiry and partition options.

Privileges: Administrators only.

Remarks

expireInSec is -1 for no expiry or a number of seconds; partcount sets the number of partitions.
Storage is MEMORY or FILE.

Example

OFFSITE CACHE TABLE crm_pg.customers TO MEMORY WITH (expireInSec "-1", partcount "4")

19.5 OFFSITE UNCACHE TABLE

Syntax

OFFSITE UNCACHE TABLE <source>.<table>

Drops a table's cache and its refresh schedule.

Privileges: Administrators only.

Example

OFFSITE UNCACHE TABLE crm_pg.customers

19.6 UPDATE OFFSITE CACHE TABLE

Syntax

UPDATE OFFSITE CACHE TABLE <source>.<table> WITH (key 'value', …)

Changes a cached table's refresh schedule.

Privileges: Administrators only.

Example

UPDATE OFFSITE CACHE TABLE crm_pg.customers WITH (expireInSec "600")

19.7 OFFSITE CACHE TABLE DETAILS

Syntax

OFFSITE CACHE TABLE DETAILS <source>.<table>

Returns a table's cache settings; empty when the table isn't cached.

Privileges: Administrators only.

Example

OFFSITE CACHE TABLE DETAILS crm_pg.customers

19.8 OFFSITE CACHE HISTORY

Syntax

OFFSITE CACHE HISTORY <source>.<table>

Lists a table's cache and uncache history.

Privileges: Administrators only.

Example

OFFSITE CACHE HISTORY crm_pg.customers

19.9 INVALIDATE CACHE

Syntax

INVALIDATE CACHE

Flushes the driver's metadata caches for every organisation; table caches are kept.

Privileges: Signed-in users; assigned objects only.

Example

INVALIDATE CACHE

20. Users and Roles

User and role names are stored in upper case and matched case-insensitively.

Prerequisites

Check each command's user-management privileges. Assignment/revocation needs existing users and roles. Deletion examples require a disposable test user, not a participant account.

20.1 CREATE ROLE

Syntax

CREATE ROLE '<role>' [DESCRIBE BY '<desc>']

Creates a role; admin, none, all and default are reserved names.

Privileges: Administrators or User Management Write users.

Example

CREATE ROLE 'analyst' DESCRIBE BY 'Datathon analysts'

20.2 SHOW USERS

Syntax

SHOW USERS

Lists your organisation's users: email, name, level, organisation, LDAP flag.

Privileges: Administrators or User Management Read or Write users.

Example

SHOW USERS

20.3 SHOW ROLES

Syntax

SHOW ROLES

Lists roles: name, creator, created time, description, LDAP flag.

Privileges: Administrators or User Management Read or Write users.

Example

SHOW ROLES

20.4 SHOW ROLE ASSIGNED TO USER

Syntax

SHOW ROLE ASSIGNED TO USER '<user>'

Lists a user's roles with who assigned them and when.

Privileges: Administrators or User Management Read or Write users.

Example

SHOW ROLE ASSIGNED TO USER 'alice@example.com'

20.5 SHOW USER ASSIGNED TO ROLE

Syntax

SHOW USER ASSIGNED TO ROLE '<role>'

Lists a role's users with who assigned them and when.

Privileges: Administrators or User Management Read or Write users.

Example

SHOW USER ASSIGNED TO ROLE 'analyst'

20.6 DESCRIBE USER

Syntax

DESCRIBE USER '<user>'

Returns a user's name, email, level, organisation and LDAP flag.

Privileges: Signed-in users; assigned objects only.

Example

DESCRIBE USER 'alice@example.com'

20.7 ASSIGN USER … TO ROLE

Syntax

ASSIGN USER '<user>'[, …] TO ROLE '<role>'

Adds users to a role; LDAP-sourced roles are refused.

Privileges: Administrators or User Management Write users.

Example

ASSIGN USER 'alice@example.com', 'bob@example.com' TO ROLE 'analyst'

20.8 DROP USER

Syntax

DROP USER '<user>'

Deletes a user; you can't delete yourself.

Privileges: Administrators or User Management Write users.

Example

DROP USER 'bob@example.com'

20.9 DROP ROLE

Syntax

DROP ROLE '<role>'

Deletes a role and its user assignments.

Privileges: Administrators or User Management Write users.

Example

DROP ROLE 'analyst'

20.10 REVOKE USER … FROM ROLE

Syntax

REVOKE USER '<user>'[, …] FROM ROLE '<role>'

Removes users from a role; LDAP-sourced roles are refused.

Privileges: Administrators or User Management Write users.

Example

REVOKE USER 'bob@example.com' FROM ROLE 'analyst'

21. Access Control

Access is controlled at two levels. An assignment attaches a user or role to an object; a grant gives a privilege (SELECT or INSERT) on a table or namespace. Schema store views are protected by grants; view assignments are made per container in the platform interface.

Prerequisites

Assignments/grants need existing objects/principals and access-control permission. Metastore forms need a registered Hive or Glue database. Confirm the recipient, object and privilege before access changes.

21.1 SHOW GRANT … ON ALL

Syntax

SHOW GRANT [{ROLE '<role>' | USER '<user>'}[, …]] ON ALL

Lists the grants held by users or roles; with no principal, every grant in your organisation.

Privileges: Administrators or Access Control Read or Write users.

Example

SHOW GRANT USER 'alice@example.com' ON ALL

21.2 LIST USERS / LIST ROLES FOR object

Syntax

LIST {USERS | ROLES} FOR {DATASOURCE | DATAMART | [LIGHTNING] DATABASE | PIPELINE | STREAMING DATASOURCE} <name>

Lists the users or roles assigned to an object.

Privileges: Administrators or Access Control Read or Write users.

Limitations

Schema store views are not supported. Access to views is controlled with GRANT … ON VIEW.

Example

LIST USERS FOR DATASOURCE crm_pg

21.3 LIST USERS / LIST ROLES FOR metastore database

Syntax

LIST {USERS | ROLES} FOR {HIVE METASTORE DATABASE|AWS GLUE METASTORE DATABASE} <metastore.db>

Lists the users or roles assigned to a Hive or Glue metastore database.

Privileges: Administrators or Access Control Read or Write users.

Example

LIST ROLES FOR HIVE METASTORE DATABASE hive_prod.sales

21.4 GRANT privilege

Syntax

GRANT {SELECT | INSERT | CACHE} ON {ALL [<object_type>] | USL {NAMESPACE | TABLE} <name> | <object_type> {NAMESPACE | TABLE} <name>} TO {ROLE '<role>' | USER '<user>'}[, …] [WITH GRANT OPTION]

Grants SELECT, INSERT or CACHE on everything, one object type, or a namespace or table.

Privileges: Administrators, or users granted the Access Control Write; holders of the privilege WITH GRANT OPTION can also grant it permission.

Remarks

Holders of a privilege WITH GRANT OPTION can pass it on.
Object types: DATASOURCE, LIGHTNING DATABASE, DATAMART, PIPELINE, VIEW, DATALAKE, STREAMING DATASOURCE, HIVE, AWS GLUE, UNITY CATALOG, USL.
Grants on views take the form VIEW TABLE <container>.<view> or VIEW NAMESPACE <container>.

Example

GRANT SELECT ON DATASOURCE TABLE crm_pg.customers TO USER 'alice@example.com'

21.5 ASSIGN object TO ROLE

Syntax

ASSIGN {DATASOURCE | DATAMART | [LIGHTNING] DATABASE | PIPELINE | STREAMING DATASOURCE} <object>[, …] TO ROLE '<role>'[, …]

Assigns datasources, data marts, Lightning databases, pipelines or streaming sources to roles.

Privileges: Administrators or Access Control Write users.

Limitations

Schema store views cannot be assigned. Access to views is controlled with GRANT … ON VIEW.

Example

ASSIGN DATASOURCE crm_pg TO ROLE 'analyst'

21.6 ASSIGN metastore database TO ROLE

Syntax

ASSIGN {HIVE METASTORE DATABASE|AWS GLUE METASTORE DATABASE} <metastore.db>[, …] TO ROLE '<role>'[, …]

Assigns Hive or AWS Glue metastore databases to roles.

Privileges: Administrators or Access Control Write users.

Example

ASSIGN HIVE METASTORE DATABASE hive_prod.sales TO ROLE 'analyst'

21.7 ASSIGN object TO USER

Syntax

ASSIGN {DATASOURCE | DATAMART | [LIGHTNING] DATABASE | PIPELINE | STREAMING DATASOURCE} <object>[, …] TO USER '<user>'[, …]

Assigns datasources, data marts, Lightning databases, pipelines or streaming sources to users.

Privileges: Administrators or Access Control Write users.

Limitations

Schema store views cannot be assigned. Access to views is controlled with GRANT … ON VIEW.

Example

ASSIGN DATAMART sales_mart TO USER 'alice@example.com'

21.8 ASSIGN metastore database TO USER

Syntax

ASSIGN {HIVE METASTORE DATABASE|AWS GLUE METASTORE DATABASE} <metastore.db>[, …] TO USER '<user>'[, …]

Assigns Hive or AWS Glue metastore databases to users.

Privileges: Administrators or Access Control Write users.

Example

ASSIGN AWS GLUE METASTORE DATABASE glue_prod.analytics TO USER 'alice@example.com'

21.9 REVOKE privilege

Syntax

REVOKE {SELECT | INSERT | CACHE} ON {ALL [<object_type>] | USL {NAMESPACE | TABLE} <name> | <object_type> {NAMESPACE | TABLE} <name>} FROM {ROLE '<role>' | USER '<user>'}[, …]

Revokes a SELECT, INSERT or CACHE grant.

Privileges: Administrators, or users granted the Access Control Write; holders of the privilege WITH GRANT OPTION can also grant it permission.

Example

REVOKE SELECT ON DATASOURCE TABLE crm_pg.customers FROM USER 'alice@example.com'

21.10 REVOKE object FROM ROLE

Syntax

REVOKE {DATASOURCE | DATAMART | [LIGHTNING] DATABASE | PIPELINE | STREAMING DATASOURCE} <object>[, …] FROM ROLE '<role>'

Removes object assignments from a role.

Privileges: Administrators or Access Control Write users.

Limitations

Schema store views are not supported.

Remarks

There is no FROM USER form; use REVOKE USERS … FROM <object>.

Example

REVOKE DATASOURCE crm_pg FROM ROLE 'analyst'

21.11 REVOKE metastore database FROM ROLE

Syntax

REVOKE {HIVE METASTORE DATABASE|AWS GLUE METASTORE DATABASE} <metastore.db>[, …] FROM ROLE '<role>'

Removes metastore database assignments from a role.

Privileges: Administrators or Access Control Write users.

Example

REVOKE HIVE METASTORE DATABASE hive_prod.sales FROM ROLE 'analyst'

21.12 REVOKE USERS / ROLES FROM object

Syntax

REVOKE {USERS '<user>'[, …] | ROLES '<role>'[, …]} FROM {DATASOURCE | DATAMART | [LIGHTNING] DATABASE | PIPELINE | STREAMING DATASOURCE} <name>

Removes the named users' or roles' assignment to an object, with their privileges on it.

Privileges: Administrators or Access Control Write users.

Limitations

Schema store views are not supported.

Example

REVOKE USERS 'alice@example.com' FROM DATAMART sales_mart

21.13 REVOKE USERS / ROLES FROM metastore database

Syntax

REVOKE {USERS '<user>'[, …] | ROLES '<role>'[, …]} FROM {HIVE METASTORE DATABASE|AWS GLUE METASTORE DATABASE} <metastore.db>

Removes users' or roles' assignment to a Hive or Glue metastore database.

Privileges: Administrators or Access Control Write users.

Example

REVOKE ROLES 'analyst' FROM AWS GLUE METASTORE DATABASE glue_prod.analytics

22. Data Warehouses

Commands for the virtual data warehouse feature.

Prerequisites

Administrator permission. Virtual data warehouses are unused here, so the list is normally empty.

22.1 LIST DATAWAREHOUSE

Syntax

LIST DATAWAREHOUSE

Lists warehouses with their status and endpoints.

Privileges: Administrators only.

Remarks

Virtual data warehouses are not used on this platform; the list is normally empty.

Example

LIST DATAWAREHOUSE

23. Server Information

Commands that report on the platform.

Prerequisites

A signed-in account. No datasource or dataset setup is needed; results reflect your access.

23.1 SHOW SCHEMA

Syntax

SHOW SCHEMA

Lists accessible databases, datasources, streams, pipelines and data marts.

Privileges: Signed-in users; assigned objects only.

Example

SHOW SCHEMA

23.2 SHOW SERVER VERSION

Syntax

SHOW SERVER VERSION

Returns the driver's version, build time, tag and commit.

Privileges: Signed-in users; assigned objects only.

Example

SHOW SERVER VERSION

Lightning Command Reference

Start with the supplied samples

Contents

1. Introduction

2. Conventions

2.1 Syntax notation

2.2 Entry format

2.3 Example objects

3. General Rules

4. Data Sources

4.1 CREATE DATASOURCE

4.2 REGISTER DATASOURCE TABLE

4.3 REGISTER DATASOURCE TABLES

4.4 CREATE DATASOURCE VIEW

4.5 SHOW DATASOURCES

4.6 SHOW DATASOURCE TABLES

4.7 PREVIEW DATASOURCE SQL

4.8 LIST DATABASE TABLES

4.9 DESCRIBE DATASOURCE TABLE

4.10 REFRESH DATASOURCE TABLES

4.11 UNCACHE DATASOURCE

4.12 UPDATE DATASOURCE SCHEMA

4.13 DROP DATASOURCE VIEW

4.14 DROP DATASOURCE

5. File Sources

5.1 CREATE FILESTORE SESSION TABLE

5.2 CREATE LIGHTNING DATABASE

5.3 CREATE LIGHTNING FILESTORE TABLE

5.4 SHOW LIGHTNING DATABASES

5.5 SHOW LIGHTNING TABLES

5.6 LIST HDFS FILES

5.7 UNCACHE LIGHTNING FILESTORE TABLE

5.8 UPDATE LIGHTNING FILESTORE TABLE

5.9 DROP LIGHTNING DATABASE TABLE

5.10 DROP LIGHTNING DATABASE

6. REST Sources and Authentication

6.1 CREATE REST SESSION TABLE

6.2 CREATE LIGHTNING REST TABLE

6.3 UPSERT AUTH

6.4 TEST AUTH REQUEST

6.5 TEST AUTH

6.6 TEST REST REQUEST

6.7 SHOW AUTH

6.8 LIST AUTH

6.9 DROP AUTH

7. Streaming

7.1 REGISTER STREAMING DATASOURCE

7.2 REGISTER STREAMING AGGREGATION

7.3 SHOW STREAMING DATASOURCE

7.4 SHOW STREAMING AGGREGATION TABLES

7.5 DROP STREAMING DATASOURCE

7.6 DROP STREAMING AGGREGATION TABLE

8. Data Lakes

8.1 CREATE DATALAKE

8.2 SHOW DATALAKES

8.3 SHOW DATALAKE TABLES

8.4 RELOAD DATALAKE

9. External Catalogs

9.1 REGISTER UNITY CATALOG

9.2 SHOW UNITY CATALOG

9.3 SHOW UC NAMESPACES

9.4 SHOW UC TABLES

9.5 UNCACHE UNITY CATALOG

9.6 DROP UNITY CATALOG

10. Hive Metastore

10.1 REGISTER HIVE METASTORE

10.2 LIST HIVE METASTORES

10.3 LIST HIVE METASTORE TABLES REMOTE

10.4 LIST HIVE METASTORE DATABASES

10.5 SHOW HIVE METASTORE DATABASES

10.6 LIST HIVE METASTORE TABLES

10.7 DROP HIVE METASTORE

11. AWS Glue Metastore

11.1 REGISTER AWS GLUE METASTORE

11.2 LIST AWS GLUE METASTORES

11.3 LIST AWS GLUE METASTORE DATABASES REMOTE

11.4 LIST AWS GLUE METASTORE DATABASES

11.5 LIST AWS GLUE METASTORE TABLES REMOTE

11.6 LIST AWS GLUE METASTORE TABLES

11.7 DROP AWS GLUE METASTORE