Skip to content

Latest commit

 

History

History
56 lines (42 loc) · 2.89 KB

sql.md

File metadata and controls

56 lines (42 loc) · 2.89 KB

SQL

The SQL console runs DuckDB over the data files of the datasets you can read. It does not reach Postgres. Terms are defined in Concepts.

The console

Tools · SQL (open to everyone who is signed in). Every dataset in your projects, and every dataset shared into them, is a table named ds_<id>. It also has an alias, the dataset's name in lowercase with each run of other characters turned into _ (t_ first when it starts with a digit), when that alias is unique among your datasets. The table datasets lists them: view, alias, dataset_id, name, project, borrowed and rows. GET /api/sql/tables returns the same list with each dataset's columns and types.

SELECT * FROM ds_12 LIMIT 100;
FROM (SUMMARIZE ds_12);
SELECT a.*, b.label FROM ds_12 AS a JOIN ds_14 AS b USING (customer_id);

Console rules

  • The console runs one query: SELECT, WITH … SELECT, VALUES or FROM …. DuckDB's own parser checks it, so a second statement is refused however it is written.
  • It sees only your datasets. Another project's ds_<id>, a file path (read_parquet('/data/…'), FROM '…'), COPY … TO, ATTACH, INSTALL, LOAD and SET are all errors, and the catalogs (duckdb_views(), information_schema.tables, SHOW TABLES) list only your tables. Each query runs in its own in-memory DuckDB connection with external access off and its configuration locked.
  • PIVOT is not available (DuckDB runs it as several statements). Use count(*) FILTER (WHERE …) or CASE inside an aggregate.
  • A query stops after SQL_TIMEOUT_MS, may use SQL_MEMORY_MB of memory, and returns at most SQL_MAX_ROWS rows. SQL_POOL_MAX queries run at once on one API worker; another is told to try again.
  • :dataset in an example is replaced with the current dataset's id.
  • Field profiles, decisions, correlations, jobs and run metrics are not tables here. They are on the pages, through the API, and through rf in a notebook (rf.dataset(id).fields(), rf.jobs()).

Example queries

-- share of missing values per column
SELECT column_name, column_type, null_percentage FROM (SUMMARIZE ds_1) ORDER BY null_percentage DESC;

-- value counts of a column
SELECT some_column, count(*) AS n FROM ds_1 GROUP BY some_column ORDER BY n DESC LIMIT 50;

Any other SQL client

Postgres holds the application's metadata (meta) and the raw tables (raw.ds_<id>) behind row-level security for the app's role. A client connected to localhost:5432 (DB_PORT) as the database user from .env owns those tables. It sees everything; keep it to administrators. make psql opens psql as that user.

Migrations and views

Schema changes go in api/app/migrations/NNN_name.sql. The API applies new migrations once on startup, under an advisory lock, and records them in meta.schema_migrations.

Views live in api/app/views.sql. The API recreates them on every start.