---
title: "Overview"
slug: "dev-database-reference-sql-index"
updated: 2026-10-30T18:19:11Z
published: 2026-10-30T18:19:11Z
canonical: "docs.vastdata.com/dev-database-reference-sql-index"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 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](/develop/docs/dev-database-integrations-trino) or [Spark](/develop/docs/dev-database-integrations-spark), which reach the same tables through their VAST connectors. [Engine comparison](/develop/docs/dev-database-reference-sql-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](/develop/docs/dev-database-operations-setup-prerequisites#checklist) has the checklist to send your platform team, and [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-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](/docs-5-5/docs/vast-query-engine#accessing-vast-databases-using-the-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](/develop/docs/dev-database-guides-transactions-in-practice#vast-query-engine-in-an-sdk-transaction).
- **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](/develop/docs/dev-vector-search-reference-configuration)).
- **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](/develop/docs/dev-database-guides-profile-a-query#set-up).

## 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](/develop/docs/dev-database-reference-sql-select#snapshots).

`vector` is a reserved word in the VAST Query Engine, so don't use it as a column name ([Vector index](/docs-5-5/docs/vector-database-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](/develop/docs/dev-database-reference-sql-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](/develop/docs/dev-database-reference-sql-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: &quot;Plan query: &apos;&lt;statement&gt;&apos; failed with: Catalog Error: ...&quot;`.
- **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))
```

`&lt;req&gt;` is the request ID, a hexadecimal number. Give it to support with the time of the query; see [record the query ID](/develop/docs/dev-database-guides-profile-a-query#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: &apos;&lt;statement&gt;&apos; failed with:` prefix.

## Statements

| Statement | What it does |
| --- | --- |
| [CREATE SCHEMA](/develop/docs/dev-database-reference-sql-create-schema) | Creates a schema, including nested schemas |
| [DROP SCHEMA](/develop/docs/dev-database-reference-sql-drop-schema) | Drops a schema |
| [SHOW SCHEMAS, SHOW TABLES](/develop/docs/dev-database-reference-sql-show) | Lists the schemas in a bucket, or the tables in a schema |
| [CREATE TABLE](/develop/docs/dev-database-reference-sql-create-table) | Creates a table, a sorted table (`SORT KEY`) or a vector-indexed table (`VECTOR CLUSTER BY`) |
| [ALTER TABLE](/develop/docs/dev-database-reference-sql-alter-table) | Renames a table; adds, renames and drops columns; adds a sort key |
| [DROP TABLE](/develop/docs/dev-database-reference-sql-drop-table) | Drops a table |
| [DESCRIBE](/develop/docs/dev-database-reference-sql-describe) | Shows a table's columns |
| [SELECT](/develop/docs/dev-database-reference-sql-select) | Queries a table, a snapshot or a topic |
| [INSERT](/develop/docs/dev-database-reference-sql-insert) | Inserts rows, or the result of a `SELECT` |
| [Import Parquet files](/develop/docs/dev-database-reference-sql-import-parquet) | Imports Parquet files from a bucket, with `INSERT` |
| [UPDATE](/develop/docs/dev-database-reference-sql-update) | Changes columns in the rows that match a condition |
| [DELETE and TRUNCATE](/develop/docs/dev-database-reference-sql-delete) | Deletes rows |
| [EXPLAIN](/develop/docs/dev-database-reference-sql-explain) | Shows the plan of a statement |

Also in this reference: [data types](/develop/docs/dev-database-reference-sql-data-types), [functions](/develop/docs/dev-database-reference-sql-functions-index), [vector search SQL](/develop/docs/dev-database-reference-sql-vector-search), [not supported](/develop/docs/dev-database-reference-sql-not-supported) and the [engine comparison](/develop/docs/dev-database-reference-sql-engine-comparison).

See [VAST Query Engine](/docs-5-5/docs/vast-query-engine#supported-sql-commands) and the [5.5.0 release notes](/docs-5-5/docs/vast-cluster-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)](/develop/docs/dev-database-integrations-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](/develop/docs/dev-database-integrations-trino) and [Spark](/develop/docs/dev-database-integrations-spark), and the [engine comparison](/develop/docs/dev-database-reference-sql-engine-comparison).