Controlling Onehouse with SQL

Every resource in Onehouse is created, changed, inspected, and removed through one SQL-style grammar. A developer who knows CREATE, ALTER, DESCRIBE, and SHOW can read the whole product surface from the table below.

How a call works. POST one statement as JSON to https://api.onehouse.ai/v1/resource/, authenticated with a service principal’s token. The response carries a request ID. A status endpoint returns the outcome for that ID, so you can follow long-running operations such as cluster creation. The CLI, the Terraform provider, and the Python SDK all generate these same statements, so anything you can do in one you can do in the others (see Automation and APIs).

Resources and verbs

Resource	Create and change	Inspect	Operate
Lake	CREATE LAKE, ALTER LAKE, DELETE LAKE	SHOW LAKES
Database	CREATE DATABASE, DELETE DATABASE	SHOW DATABASES
Table	REGISTER TABLE, UNREGISTER TABLE	SHOW TABLES, SHOW UNREGISTERED TABLES	ALTER SERVICE IN TABLE, ALTER SERVICES_CLUSTER IN TABLE, RUN SERVICE IN TABLE
Source	CREATE SOURCE, DELETE SOURCE	DESCRIBE SOURCE, SHOW SOURCES
Flow	CREATE FLOW, ALTER FLOW, DELETE FLOW	DESCRIBE FLOW, SHOW FLOWS	pause, resume, clean-and-restart via ALTER FLOW
Transformation	CREATE TRANSFORMATION, DELETE TRANSFORMATION	DESCRIBE TRANSFORMATION, SHOW TRANSFORMATIONS
Validation	CREATE VALIDATION, DELETE VALIDATION	DESCRIBE VALIDATION, SHOW VALIDATIONS
Cluster	CREATE CLUSTER, ALTER CLUSTER, DELETE CLUSTER	DESCRIBE CLUSTER, SHOW CLUSTERS	start and stop, and resize OCU, via ALTER CLUSTER
Job	CREATE JOB, CREATE AND RUN JOB, ALTER JOB, DELETE JOB	DESCRIBE JOB, DESCRIBE JOB RUN, SHOW JOBS, SHOW RUNS IN JOB	RUN JOB, CANCEL JOB RUN
Table service	CREATE TABLE_SERVICE, DELETE TABLE_SERVICE	DESCRIBE TABLE_SERVICES, SHOW TABLE_SERVICES, SHOW RUNS IN TABLE_SERVICE	PAUSE, RESUME, CANCEL TABLE_SERVICE, RUN SERVICE IN TABLE
Catalog	CREATE CATALOG, DELETE CATALOG	DESCRIBE CATALOG, SHOW CATALOGS
JAR	UPLOAD JAR, REPLACE JAR, DELETE JAR	DESCRIBE JAR, SHOW JARS
Group	CREATE GROUP, ALTER GROUP, DELETE GROUP	SHOW GROUPS
Service principal	CREATE SERVICE_PRINCIPAL, ALTER SERVICE_PRINCIPAL, DELETE SERVICE_PRINCIPAL	SHOW SERVICE_PRINCIPALS	CREATE API_TOKEN, DELETE API_TOKEN
Access control	GRANT PRIVILEGE, REVOKE PRIVILEGE, GRANT ROLE, REVOKE ROLE, RESET POLICIES	SHOW GRANTS

The shape of a statement

Resource-specific settings go in a WITH clause of key-value pairs, which keeps the grammar stable as connectors and options are added.

Create a Spark cluster with a spend ceiling:

CREATE CLUSTER `etl` TYPE = 'Spark' MIN_OCU = 0 MAX_OCU = 32

Stop it overnight, then resize it:

ALTER CLUSTER `etl` SET STATE = 'STOP'
ALTER CLUSTER `etl` SET MAX_OCU = 64

Create an ingestion Flow from a Kafka source into a mutable table, syncing to a catalog:

CREATE FLOW `orders`
  SOURCE = 'kafka-prod' LAKE = 'lake' DATABASE = 'sales' TABLE_NAME = 'orders'
  WRITE_MODE = 'MUTABLE' CLUSTER = 'ingest'
  CATALOGS = ('glue') RECORD_KEY_FIELDS = ('order_id') PRECOMBINE_KEY_FIELD = 'updated_at'
  MIN_SYNC_FREQUENCY_MINS = 1 QUARANTINE_ENABLED = 'true'
  WITH 'kafka.topic.name' = 'orders'

Add on-demand clustering to a table, then trigger it:

CREATE TABLE_SERVICE LAKE = 'lake' DATABASE = 'sales' TABLE = 'orders'
  SERVICE = 'CLUSTER' TRIGGER_MODE = 'ON_DEMAND'
  WITH 'clustering.sort.columns' = 'customer_id'

RUN SERVICE IN TABLE `orders` LAKE = 'lake' DATABASE = 'sales' SERVICE = 'CLUSTER'

Take a savepoint and later restore to it, the same way:

RUN SERVICE IN TABLE `orders` LAKE = 'lake' DATABASE = 'sales' SERVICE = 'AUTOSAVEPOINT'
RUN SERVICE IN TABLE `orders` LAKE = 'lake' DATABASE = 'sales' SERVICE = 'RESTORE'

Register a table another pipeline wrote, so Onehouse can observe and optimize it:

REGISTER TABLE LAKE = 'observed' TABLE_BASE_PATH = 's3://bucket/warehouse/events'

Run a job, and grant a group the right to read a database:

RUN JOB `nightly_rollup`
GRANT PRIVILEGE SELECT ON DATABASE sales TO GROUP analysts

Field order within a statement is fixed. The console shows the equivalent statement for anything you do in the UI, so the fastest way to learn the grammar is to build a resource once by hand and read the statement it produced. The full reference is in the product documentation.