Overview

Applies to VAST Cluster 5.5.

The VAST Query Engine runs SQL on VAST DataBase tables inside the cluster's CNodes. Your application sends it one statement at a time through the VAST ADBC driver, and gets the result back as Apache Arrow data. This reference covers the SQL it accepts in VAST Cluster 5.5: one page per statement, the data types, the functions, and what it doesn't support.

The Query Engine supports a defined subset of SQL. If you need something it doesn't have, such as joins or GROUP BY, use Trino or Spark, which reach the same tables through their VAST connectors. Engine comparison shows what each engine can do.

Before you start

To send SQL to the VAST Query Engine you need:

A virtual IP pool dedicated to the Query Engine, created by your platform team.
An access key pair for a VAST user.
An identity policy that grants s3:QueryEngine* for Query Engine actions and s3:Tabular* for DDL and DML.
The VAST ADBC driver in your client (pip install adbc-driver-vastdb, or the Python SDK's adbc extra). It runs on Linux x86_64.

Setup prerequisites has the checklist to send your platform team, and VAST Query Engine (ADBC) shows how to connect.

Admin task: send to your platform team. The VIP pool and policy setup is in the KB: VAST Query Engine.

How SQL reaches the engine

One statement per request. Each execute() call takes one statement. A request with two statements, such as SELECT 1; SELECT 2, fails with query: 'SELECT 1; SELECT 2' contains 2 statements, only 1 statement is supported..
Transactions belong to the connection. On a connection opened with autocommit=True, each statement commits on its own. With autocommit=False, statements run in one transaction until you call conn.commit() or conn.rollback(). The SQL statements BEGIN TRANSACTION, COMMIT and ROLLBACK fail with The operator 'TRANSACTION' type=58 is currently not supported.. Through the Python SDK, SQL on tx.adbc_conn joins the SDK transaction; see transactions in practice.
Settings are connection options, not SQL. SET, RESET and PRAGMA statements fail. Session settings, such as the vector search settings, are passed as ADBC options when you connect (search session settings).
No bound parameters. With adbc-driver-vastdb 0.0.32, cur.execute(sql, params) fails on the client with INVALID_STATE: Cannot configure ingestion options on a statement configured for query. Put the values in the SQL text, and quote string values yourself. To load many rows, use ADBC bulk ingest (cur.adbc_ingest()), as in profile a query.

Object names

A full schema name is the bucket (your database) and the schema path, in double quotes: "<bucket>/<schema>", or "<bucket>/<schema>/<child>" for a nested schema. A full table name adds the table: "<bucket>/<schema>".<table>. Quote the table name too if it needs quoting: "<bucket>/<schema>"."<table>".

A table in a snapshot of the bucket is "<bucket>/.snapshot/<snapshot>/<schema>".<table>, and you can only read it; see SELECT.

vector is a reserved word in the VAST Query Engine, so don't use it as a column name (Vector index).

How a statement runs

The Query Engine plans each statement, then checks every step (operator) of the plan against the operators it supports. If a step isn't supported, the statement fails before it runs, and the error names the step: The operator 'HASH_JOIN' type=32 is currently not supported. Some steps are supported only in some forms, and fail with ... is currently not supported for this usage. For example, ORDER BY is supported only together with LIMIT. Not supported lists these errors with their workarounds.

When a statement reads a table, the scan (VAST_SCAN in the plan) runs in VAST DataBase: it returns only the columns the statement needs, and evaluates the filters it can, next to the data. Everything else, such as functions, aggregates and sorting, runs in the Query Engine on the rows that come back. EXPLAIN shows you which is which.

What errors look like

The driver raises a DB-API exception, mostly InternalError. The server's own message is inside the exception text, in one of two shapes:

Refused before it runs (planning and validation errors):
```
ServerErrorResponse { error_code: Other("NotImplemented"), message: "[req=<req>]: The operator 'HASH_JOIN' type=32 is currently not supported.", request_id: "<req>" }
```
Errors from planning carry the statement too: message: "Plan query: '<statement>' failed with: Catalog Error: ...".

Failed while it runs:

Io error: Query stopped prematurely: Query canceled: Server error (status: 58, description: [req=<req>]: internal error: This functionality is not implemented yet (specific message not set))

<req> is the request ID, a hexadecimal number. Give it to support with the time of the query; see record the query ID. A few failures come back with a different class: bound parameters raise ProgrammingError (from the driver), and some errors found while the result is read, such as a failed CAST, raise OSError.

The pages in this reference quote each error as the examples' run() helper prints it: the class, then the server's message, without the request ID and without the Plan query: '<statement>' failed with: prefix.

Statements

Statement	What it does
CREATE SCHEMA	Creates a schema, including nested schemas
DROP SCHEMA	Drops a schema
SHOW SCHEMAS, SHOW TABLES	Lists the schemas in a bucket, or the tables in a schema
CREATE TABLE	Creates a table, a sorted table (`SORT KEY`) or a vector-indexed table (`VECTOR CLUSTER BY`)
ALTER TABLE	Renames a table; adds, renames and drops columns; adds a sort key
DROP TABLE	Drops a table
DESCRIBE	Shows a table's columns
SELECT	Queries a table, a snapshot or a topic
INSERT	Inserts rows, or the result of a `SELECT`
Import Parquet files	Imports Parquet files from a bucket, with `INSERT`
UPDATE	Changes columns in the rows that match a condition
DELETE and TRUNCATE	Deletes rows
EXPLAIN	Shows the plan of a statement

Also in this reference: data types, functions, vector search SQL, not supported and the engine comparison.

See VAST Query Engine and the 5.5.0 release notes.

Example: connect, and use a transaction

The examples in this reference connect with the ADBC driver, as in VAST Query Engine (ADBC), and each page works in a schema of its own. Set these environment variables:

Variable	Value
`QE_ENDPOINT`	The Query Engine endpoint from your platform team
`AWS_ACCESS_KEY_ID`, `AWS_SECRET_ACCESS_KEY`	Your access key pair
`VASTDB_BUCKET`	Your database (bucket)

Run the blocks in order, in one Python session. Below each block is the output you should see.

This block connects, defines the run() helper that the pages use, and creates the schema docs_sql_index. run() prints the rows a statement returns, or the error. DDL statements return no rows, so it prints [] for them.

import os
import re

import adbc_driver_manager
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/docs_sql_index"'

def connect(autocommit=True):
    return dbapi.connect(
        driver=adbc_driver_vastdb.get_driver_path(),
        db_kwargs={
            "vast.db.endpoint": os.environ["QE_ENDPOINT"],
            "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
            "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
        },
        autocommit=autocommit,
    )

def server_message(e):
    """The server's message in a driver error, without the request ID or the echoed statement."""
    text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e))
    found = (re.search(r'message: "(.*?)", request_id', text, re.S)
             or re.search(r"description: (.*)\)", text, re.S))
    message = found.group(1) if found else text.strip().splitlines()[-1]
    message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S)
    message = re.split(r"\\n|\n", message)[0].replace('\\"', '"')
    return message.replace(BUCKET, "<bucket>")

def run(sql):
    """Run one statement. Print the rows it returns, or the error."""
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall() if cur.description else []
    except (adbc_driver_manager.Error, OSError) as e:
        print(f"{type(e).__name__}: {server_message(e)}")
    else:
        print(rows)

conn = connect()
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {SCHEMA}.orders (id BIGINT, status VARCHAR)")

[]
[]

Two statements in one request fail; send them one at a time:

run(f"INSERT INTO {SCHEMA}.orders VALUES (1, 'new'); INSERT INTO {SCHEMA}.orders VALUES (2, 'new')")
run(f"INSERT INTO {SCHEMA}.orders VALUES (1, 'new')")
run(f"INSERT INTO {SCHEMA}.orders VALUES (2, 'new')")

InternalError: query: 'INSERT INTO "<bucket>/docs_sql_index".orders VALUES (1, 'new'); INSERT INTO "<bucket>/docs_sql_index".orders VALUES (2, 'new')' contains 2 statements, only 1 statement is supported.
[(1,)]
[(1,)]

To group statements into one transaction, open a connection with autocommit=False, and end the transaction with commit() or rollback(). The BEGIN statement fails:

run("BEGIN TRANSACTION")

tx = connect(autocommit=False)
with tx.cursor() as cur:
    cur.execute(f"INSERT INTO {SCHEMA}.orders VALUES (3, 'rolled back')")
    cur.fetchall()
tx.rollback()
tx.close()

tx = connect(autocommit=False)
with tx.cursor() as cur:
    cur.execute(f"UPDATE {SCHEMA}.orders SET status = 'paid' WHERE id = 1")
    cur.fetchall()
    cur.execute(f"DELETE FROM {SCHEMA}.orders WHERE id = 2")
    cur.fetchall()
tx.commit()
tx.close()

run(f"SELECT id, status FROM {SCHEMA}.orders ORDER BY id LIMIT 10")

InternalError: The operator 'TRANSACTION' type=58 is currently not supported.
[(1, 'paid')]

Clean up:

run(f"DROP TABLE IF EXISTS {SCHEMA}.orders")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

Trino and Spark

Trino and Spark have their own SQL dialects, with joins, grouping and views, and their own transaction statements; see Trino and Spark, and the engine comparison.