VAST Query Engine

CREATE SCHEMA [IF NOT EXISTS] <schema-full-name>

Creates a schema, the full name includes the bucket and all nested schema directories

Options:

DROP SCHEMA <schema-full-name>

Deletes an existing schema

CREATE [OR REPLACE] TABLE [IF NOT EXISTS] <table-full-name> (<col-name> <col-type> …)

Creates a regular table; full name includes bucket, nested schema directories, and table name

Options:

CREATE [OR REPLACE] TABLE [IF NOT EXISTS] <table-full-name> (… CONSTRAINT <constraint-name> SORT KEY (col1, col2, …))

Creates a table with an Elysium sort key constraint (up to 4 sort columns)

Options:

DROP TABLE <table-full-name>

Drops an existing table

DESCRIBE [TABLE] <table-full-name>

Displays the column definitions of a table

ALTER TABLE <table-full-name> RENAME TO <new-table-full-name>

Renames a table

ALTER TABLE <table-full-name> RENAME COLUMN <col-name> TO <new-col-name>

Renames a column in a table

ALTER TABLE <table-full-name> ADD [COLUMN] <col-name> <col-type>

Adds a new column to a table

ALTER TABLE <table-full-name> DROP [COLUMN] <col-name>

Removes a column from a table

ALTER TABLE <table-full-name> ADD CONSTRAINT <constraint-name> SORT KEY (col1, col2, …)

Enables column sorting by adding a sort key constraint to an existing table. See Sorted Tables

INSERT INTO <table-full-name> VALUES (col1-value, col2-value, …)

Inserts a row; all columns must be provided in table order

INSERT INTO <table-full-name> (col1-name, col2-name, …) VALUES (col1-value, col2-value, …)

Inserts a row with values for a specified subset of columns

INSERT INTO <table-full-name> SELECT …

Inserts rows produced by a SELECT query into the target table

UPDATE <table-full-name> SET col1-name = col1-value, col2-name = col2-value WHERE condition

Updates one or more column values in rows that match the specified condition

DELETE FROM <table-full-name> WHERE condition

Deletes rows from a table that match the specified condition

INSERT INTO <table-full-name>

(part_colname_1, partition_colname_2, partition_colname_3, partition_colname_4, $parquet_file_path")

VALUES (value1_1, value2_1, value3_1, parquet_file_path_1),

(value1_2, value2_2, value3_2, parquet_file_path_2),...

Inserts data from parquet files into a partitioned table, according to the partition columns

any_value(x)

Note

The column, x, must be constant size.

SELECT any_value(country) FROM t;

Returns the first non‑NULL country encountered (order‑sensitive).

avg(x)

SELECT avg(salary) FROM t;

Average of non‑NULL salaries; NULLs are ignored; empty group → NULL.

mean(x)

SELECT mean(salary) FROM t;

Average of non‑NULL salaries; NULLs are ignored; empty group → NULL.

sum(x)

SELECT sum(amount) FROM t;

Sum of non‑NULL amounts. For BOOLEAN, counts TRUEs.

min(x)

Note

The column, x, must be constant size.

SELECT min(score) FROM t;

Minimum non‑NULL score.

max(x)

Note

The column, x, must be constant size.

SELECT max(score) FROM t;

Maximum non‑NULL score.

bool_and(x)

SELECT bool_and(ok) FROM t;

TRUE if all non‑NULL booleans are TRUE; FALSE otherwise.

bool_or(x)

SELECT bool_or(ok) FROM t;

TRUE if any non‑NULL boolean is TRUE; FALSE if none are TRUE.

count()

SELECT count() FROM t;

Row count (includes NULLs). Same as count(*).

count(x)

SELECT count(user_id) FROM t;

Count of non‑NULL user_id values.

countif(cond)

SELECT countif(score >= 90) FROM t;

Number of rows where condition is TRUE.

product(x)

SELECT product(multiplier) FROM t;

Product of all non‑NULL multipliers (empty group → NULL).

stddev_pop(x)

SELECT stddev_pop(v) FROM t;

Population standard deviation.

stddev_samp(x) / stddev(x)

SELECT stddev_samp(v) FROM t;

Sample standard deviation (Bessel correction).

var_pop(x)

SELECT var_pop(v) FROM t;

Population variance.

var_samp(x) / variance(x)

SELECT var_samp(v) FROM t;

Sample variance (Bessel correction).