VAST Query Engine (ADBC)

Applies to VAST Cluster 5.5. Examples use the VAST DataBase Python SDK 2.1.0.

The VAST Query Engine runs SQL on VAST DataBase tables inside the cluster's CNodes. Client applications reach it through VAST's ADBC driver, which returns results as Apache Arrow data. You can use the driver on its own from any language with an ADBC driver manager, or through the Python SDK, where it shares your SDK transaction.

What you can do with it

The Query Engine supports these features (see VAST Query Engine):

• SQL DDL: create and drop schemas and tables, including sorted tables, and alter tables.
• SQL DML: INSERT, INSERT ... SELECT, UPDATE and DELETE.
• Queries with filter pushdown, nested data types and aggregation functions such as sum, avg and approx_count_distinct.
• Vector search. See the VAST Vector Search.
• Row and column security, applied to the user running the query.

The supported SQL is listed in the SQL reference.

Use the Query Engine when you want SQL, or aggregations computed on the cluster, from your own application without running Trino or Spark. For plain reads and writes from Python, the Python SDK doesn't need the Query Engine.

Before you start

You need these from your platform team:

Admin task: send to your platform team. See Accessing VAST DataBases using the Query Engine, and the checklist in setup prerequisites.

No driver version or cluster pairing is published; see versions.

Install the driver

The driver is open source: github.com/vast-data/vastdb-adbc-driver. For Python, the adbc-driver-vastdb package ships it, and the SDK's adbc extra installs it for you:

pip install "vastdb[adbc]==2.1.0"

SDK 2.1.0 pins adbc-driver-vastdb~=0.0.26. The SDK finds the driver library with adbc_driver_vastdb.get_driver_path().

Use the driver on its own

With the ADBC driver manager for Python, you pass the endpoint and keys as database options:

import os
import adbc_driver_vastdb
from adbc_driver_manager import dbapi

conn = dbapi.connect(
    driver=adbc_driver_vastdb.get_driver_path(),
    db_kwargs={
        "vast.db.endpoint": os.environ["VAST_QE_ENDPOINT"],
        "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
        "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
    },
)
cur = conn.cursor()
cur.execute('SELECT avg(temperature) FROM "my-db/weather".readings WHERE city = \'Paris\'')
print(cur.fetch_arrow_table())
cur.close()
conn.close()

VAST_QE_ENDPOINT here is just an environment variable for this example; set it to your Query Engine endpoint.

Table names have the form "<bucket>/<schema>".<table>: the bucket and schema path in double quotes, then the table name. For a nested schema, the quoted part is the full path, such as "my-db/sales/eu". That's the form the SDK builds for its own Query Engine SQL (TableRef.query_engine_full_path).

The connection options the driver takes:

Use it through the Python SDK

When the driver is installed, the SDK opens an ADBC connection for every transaction, as the transaction starts, and closes it when the transaction ends. The connection joins your SDK transaction (vast.db.external_txid), so SQL you run through it sees your uncommitted changes. The SDK's own tests check that a query on tx.adbc_conn sees a table created and filled earlier in the same transaction:

import os
import pyarrow as pa
import vastdb

session = vastdb.connect()
BUCKET = os.environ["VASTDB_BUCKET"]

with session.transaction() as tx:
    table = tx.bucket(BUCKET).create_schema("guide_adbc", fail_if_exists=False) \
        .create_table("numbers", pa.schema([("n", pa.int32())]), fail_if_exists=False)
    table.insert(pa.table({"n": pa.array([1, 2, 3, 4, 5], pa.int32())}))

    cur = tx.adbc_conn.cursor
    cur.execute(f"SELECT sum(n) FROM {table.ref.query_engine_full_path}")
    print(cur.fetchall())

tx.adbc_conn.cursor is a standard DB-API cursor from adbc_driver_manager, so execute(), fetchall() and fetch_arrow_table() work as usual. Don't close it yourself: the transaction closes it.

SQL writes on it commit and roll back with the SDK transaction; see transactions in practice.

To use a driver library from somewhere else, pass its path:

session = vastdb.connect(adbc_driver_path="/path/to/vast_adbc_driver.so")

Release notes of older SDK versions mention an AdbcDriver object. In SDK 2.1.0 there's no such class: you pass the path as a string, or nothing.

Which endpoint the SDK uses: the SDK gives the driver the same endpoint as the session, the one you passed to vastdb.connect() or set in AWS_S3_ENDPOINT_URL. The Query Engine needs a dedicated virtual IP pool.

If the driver isn't there

If the driver isn't installed and you didn't pass adbc_driver_path, transactions still open, without ADBC. Nothing warns you. The first use of tx.adbc_conn, or of table.vector_search(), raises:

vastdb.transaction.NoAdbcConnectionError: Adbc Driver may not have been supplied

(message quoted from the SDK 2.1.0 source)

The same error comes from using tx.adbc_conn after the transaction has ended.

Run queries as an end user

A service that runs queries for many users can run each query as the end user, instead of as the service's own account. Row and column security then applies to the end user (ADBC User Impersonation).

1. The service account needs s3:EndUserImpersonation in its identity or bucket policy. Admin task: send to your platform team.
2. Pass the end user's name:
   • On its own, in the connection options: conn_kwargs={"vast.db.end_user": "<user name>"} in dbapi.connect().
   • Through the SDK, when you connect: vastdb.connect(end_user="<user name>"). The SDK passes it as vast.db.end_user on every transaction's ADBC connection.

session = vastdb.connect(end_user="alice")

with session.transaction() as tx:
    cur = tx.adbc_conn.cursor
    cur.execute('SELECT * FROM "my-db/hr".salaries')
    rows = cur.fetch_arrow_table()   # row and column security applies as alice

end_user only affects ADBC operations: SQL on tx.adbc_conn and vector_search(). Other SDK calls, such as select() and insert(), still run as the key's owner. For how row and column security is set up, see security.

Vector search

table.vector_search() in the Python SDK runs through the Query Engine, so it needs the driver and s3:QueryEngine*. The SDK sends the query to the endpoint your session uses, so connect the session to the dedicated Query Engine VIP pool. How to create vector indexes and query them is covered in the VAST Vector Search.

See also

• SQL reference: the Query Engine's SQL.
• Transactions in practice: how SDK transactions behave.
• Python SDK reference: adbc_driver_path and end_user.