Data discovery SQL

Simple queries

Query a table in the current database:

SELECT id, name, dt
FROM orders
WHERE dt = '<partition date>'
LIMIT 100;

Query a table in a specific database:

SELECT id, name
FROM sales.orders
WHERE id = 1001
LIMIT 100;

Column selection

SELECT name
FROM users
LIMIT 100;

Filtering, sorting, and pagination

SELECT id, name, amount
FROM orders
WHERE dt = '<partition date>'
  AND amount > 100
ORDER BY amount DESC
LIMIT 100;

Aggregation

SELECT category, COUNT(*) AS cnt
FROM goods
GROUP BY category
ORDER BY cnt DESC
LIMIT 100;

Joins

SELECT u.id, u.name, s.score
FROM users u
JOIN scores s
  ON u.id = s.id
WHERE s.score >= 80
LIMIT 100;

Subqueries

SELECT id, name
FROM users
WHERE id IN (
  SELECT user_id
  FROM orders
  WHERE dt = '<partition date>'
)
LIMIT 100;

Common table expressions (CTEs)

WITH filtered AS (
  SELECT *
  FROM orders
  WHERE dt = '<partition date>'
)
SELECT COUNT(*) AS cnt
FROM filtered;

Vector search

vector_search(...) performs similarity search on a vector column. Parameters (in order): table name, vector column name, query vector, and number of results to return.

Syntax:

SELECT *
FROM vector_search('<database name>.<table name>', '<vector column>', '<query vector JSON array>', <number of results>);

Example -- before running this query, select the target Catalog at the top of the page:

SELECT *
FROM vector_search(
  'multimodal_samples.berkeley_deepdrive_100k_images',
  'image_embedding',
  '<query vector JSON array>',
  3
);

Full-text search

full_text_search(...) performs full-text search on a text column. Parameters (in order): table name, text column name, query text, and number of results to return.

Syntax:

SELECT *
FROM full_text_search('<database name>.<table name>', '<text column>', '<query text>', <number of results>);

Search by keyword -- before running this query, select the target Catalog at the top of the page:

SELECT *
FROM full_text_search(
  'multimodal_samples.berkeley_deepdrive_100k_images',
  'tags',
  'traffic',
  5
);

Search by phrase:

SELECT *
FROM full_text_search(
  'multimodal_samples.berkeley_deepdrive_100k_images',
  'tags',
  '"traffic light"',
  5
);

Create and drop a database

In SQL exploration, the SCHEMA keyword maps to a DLF Database.

CREATE SCHEMA IF NOT EXISTS demo_db;

DROP SCHEMA IF EXISTS demo_db CASCADE;

Create an append table

CREATE TABLE IF NOT EXISTS demo_db.orders (
  order_id BIGINT,
  price BIGINT,
  customer STRING
);

Create a primary key table

CREATE TABLE IF NOT EXISTS demo_db.orders_pk (
  order_id BIGINT,
  price BIGINT,
  customer STRING,
  PRIMARY KEY (order_id) NOT ENFORCED
);

Create a partitioned table

Define the partition column as a regular table column, then specify it in PARTITIONED BY.

CREATE TABLE IF NOT EXISTS demo_db.events (
  event_id BIGINT,
  event_name STRING,
  dt STRING
)
PARTITIONED BY (dt);

Alter a table

ALTER TABLE demo_db.orders
ADD COLUMN remark STRING;

ALTER TABLE demo_db.orders
RENAME COLUMN remark TO comment;

Drop a table

DROP TABLE IF EXISTS demo_db.orders;

Temporary tables and views

Temporary objects are visible only within the current SQL submission. They are useful for organizing intermediate results in multi-statement submissions.

CREATE TEMPORARY TABLE tmp_users AS
SELECT * FROM (VALUES (1, 'alice'), (2, 'bob')) AS t(id, name);

SELECT COUNT(*) FROM tmp_users;

DROP TEMPORARY TABLE tmp_users;

CREATE TEMPORARY VIEW tmp_view AS
SELECT * FROM orders WHERE dt = '<partition date>';

SELECT COUNT(*) FROM tmp_view;

DROP TEMPORARY VIEW tmp_view;

Drop a partition

ALTER TABLE demo_db.events
DROP PARTITION (dt = '<partition date>');

Query table options

SELECT key, value
FROM demo_db.orders$options;

Query schema history

SELECT *
FROM demo_db.orders$schemas;

Query snapshots

SELECT *
FROM demo_db.orders$snapshots
ORDER BY snapshot_id DESC
LIMIT 10;

Query tags

SELECT *
FROM demo_db.orders$tags;

Query manifests

SELECT *
FROM demo_db.orders$manifests
LIMIT 10;

Query partition statistics

SELECT *
FROM demo_db.orders$partitions;

Query file size statistics

SELECT *
FROM demo_db.orders$physical_files_size;

SELECT *
FROM demo_db.orders$referenced_files_size;

Table storage overview

SELECT dt,
       COUNT(DISTINCT table_id) AS table_cnt,
       SUM(obj_size) + SUM(meta_obj_size) AS total_size,
       SUM(obj_size) AS total_data_obj_size,
       SUM(meta_obj_size) AS total_meta_obj_size
FROM system.table_summary
WHERE dt BETWEEN '<start date>' AND '<end date>'
GROUP BY dt
ORDER BY dt;

Partition storage statistics

SELECT database_name, table_name, partition_name,
       obj_cnt AS data_obj_cnt,
       obj_size AS data_obj_size
FROM system.partition_summary
WHERE dt = '<partition date>'
ORDER BY data_obj_size DESC
LIMIT 100;

Compute usage trends

SELECT dt,
       SUM(cu) AS daily_cu,
       MIN(start_time) AS min_start_time,
       MAX(start_time) AS max_start_time
FROM system.compute_usage
WHERE dt BETWEEN '<start date>' AND '<end date>'
GROUP BY dt
ORDER BY dt;

Permission grants

SELECT principal, principal_kind, principal_name,
       database_name, object_name, resource_type, access
FROM system.auth_privilege_grants
WHERE dt = '<partition date>'
  AND access = 'SELECT'
ORDER BY database_name, object_name, principal;