query_table

Applies to VAST Cluster 5.5.

Functions › Table functions › Queries and tables named in a string. Reads a table whose name is given as a string, or several tables combined into one result, so it goes in the FROM clause. Use it when your code chooses the table at run time.

Syntax

query_table(name)
query_table([name, ...])
query_table([name, ...], by_name)

Returns: a table with the columns and rows of the named table, or of the named tables combined.

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (ADBC), and work in a schema called docs_sql_fn_table_functions_query_table. Set QE_ENDPOINT, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY and VASTDB_BUCKET (SQL reference), and run the blocks in order in one Python session. Below each block is the output you should see.

This block connects, defines the run() and plan() helpers, and creates three tables: articles (four rows; the last has no title), archive with the same columns, and notes with two columns in a different order:

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_fn_table_functions_query_table"'

conn = 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=True,
)

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)

def plan(sql):
    """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert."""
    with conn.cursor() as cur:
        cur.execute("EXPLAIN " + sql)
        steps = cur.fetchall()[0][1]
    print("\n".join(line for line in steps.splitlines() if "Rows" not in line))

TABLE = f"{SCHEMA}.articles"
ARCHIVE = f"{SCHEMA}.archive"
NOTES = f"{SCHEMA}.notes"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, title VARCHAR, views BIGINT)")
run(f"CREATE TABLE IF NOT EXISTS {ARCHIVE} (id BIGINT, title VARCHAR, views BIGINT)")
run(f"CREATE TABLE IF NOT EXISTS {NOTES} (body VARCHAR, id BIGINT)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'Getting started', 120), "
    "(2, 'Vector search', 45), "
    "(3, 'Tuning queries', 300), "
    "(4, NULL, NULL)")
run(f"INSERT INTO {ARCHIVE} VALUES (10, 'Old release notes', 7), (11, 'Retired guide', 2)")
run(f"INSERT INTO {NOTES} VALUES ('Check the plan', 20), ('Add an index', 21)")

[]
[]
[]
[]
[(4,)]
[(2,)]
[(2,)]

What it does

query_table(name) returns the rows of the table, with its column names and types, as SELECT * FROM name would. Write the name as you would in a statement, with the bucket and schema: a bare 'articles' isn't found.

With a list of names, the tables' rows are combined into one result, as with UNION ALL: duplicates are kept. Without by_name, columns are matched by position, so the tables need the same number of columns. With by_name set to true, columns are matched by name, and tables with different columns can be combined.

NULLs and edge cases

The same cases, as the cluster returns them. They use the tables and helpers from Set up the examples:

run("SELECT * FROM query_table(NULL)")
run("SELECT * FROM query_table('')")
run("SELECT * FROM query_table('articles')")
run("SELECT * FROM query_table(1)")
run(f"SELECT id FROM query_table(['{TABLE}', '{TABLE}']) ORDER BY id LIMIT 20")

InternalError: Binder Error: Cannot use NULL as function argument
InternalError: Parse query: 'SELECT * FROM query_table('')' failed with: Parser Error: syntax error at end of input
InternalError: Catalog Error: Table with name articles does not exist!
InternalError: Binder Error: No function matches the given name and argument types 'query_table(INTEGER)'. You might need to add explicit type casts.
[(1,), (1,), (2,), (2,), (3,), (3,), (4,), (4,)]

Examples

The articles, from a table name held in a Python variable. ORDER BY needs a LIMIT (SELECT):

run(f"SELECT id, title FROM query_table('{TABLE}') ORDER BY id LIMIT 10")

[(1, 'Getting started'), (2, 'Vector search'), (3, 'Tuning queries'), (4, None)]

Current and archived articles in one result, matched by position:

run(f"SELECT id, title FROM query_table(['{TABLE}', '{ARCHIVE}']) ORDER BY id LIMIT 20")

[(1, 'Getting started'), (2, 'Vector search'), (3, 'Tuning queries'), (4, None), (10, 'Old release notes'), (11, 'Retired guide')]

The IDs of articles and notes, whose columns differ, matched by name:

run(f"SELECT id FROM query_table(['{TABLE}', '{NOTES}'], true) ORDER BY id LIMIT 20")

[(1,), (2,), (3,), (4,), (20,), (21,)]

How it runs in VAST

The Query Engine runs on the cluster's CNodes. query_table('name') is planned as a scan of that table in VAST DataBase (VAST_SCAN), like SELECT ... FROM name, so conditions on it are handled as they are on the table:

• A comparison of a column with a constant is evaluated in the scan. The plan of WHERE id = 2 is a single VAST_SCAN step, with no FILTER step.
• A function of a column, such as length(title) = 5, is computed by the Query Engine in a FILTER step above the scan, which sends the column for every row.

plan(f"SELECT id FROM query_table('{TABLE}') WHERE id = 2")
plan(f"SELECT id FROM query_table('{TABLE}') WHERE length(title) = 5")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #0            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│    (length(title) = 5)    │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             id            │
│           title           │
│                           │
└───────────────────────────┘

Make it faster

• Filter on raw columns. WHERE id = 2 pushes down to VAST DataBase, so rows that don't match never leave storage; WHERE length(title) = 5 doesn't.
• Select only the columns you need. The scan reads only the columns the query uses, as the Projections in the plans show.
• Use a plain FROM when the table is known. query_table() helps only when the name is chosen at run time.

Limitations and errors

• Same number of columns, unless by_name is true. Combining tables with different column counts by position fails with Binder Error: Set operations can only apply to expressions with the same number of result columns. Pass true as the second argument to match by name.
• A constant only. A column can't be the argument: it fails with Table function "query_table" does not support lateral join column parameters.
• Strings or a list of strings only. Other types fail with No function matches the given name and argument types; the error lists query_table(VARCHAR), query_table(VARCHAR[]) and query_table(VARCHAR[], BOOLEAN).
• A missing table fails with Catalog Error: Table with name ... does not exist!.

run(f"SELECT * FROM query_table(['{TABLE}', '{NOTES}']) LIMIT 20")
run(f"SELECT * FROM {TABLE}, query_table(title)")
run(f"SELECT * FROM query_table('{SCHEMA}.nope')")

InternalError: Binder Error: Set operations can only apply to expressions with the same number of result columns
InternalError: Binder Error: Table function "query_table" does not support lateral join column parameters - cannot use column "title" in this context.
InternalError: Catalog Error: Table with name nope does not exist!

Similar functions

The same row three ways:

run(f"SELECT id, title FROM query_table('{TABLE}') WHERE id = 2")
run(f"SELECT * FROM query('SELECT id, title FROM {TABLE} WHERE id = 2')")
run(f"SELECT id, title FROM {TABLE} WHERE id = 2")

[(2, 'Vector search')]
[(2, 'Vector search')]
[(2, 'Vector search')]

Clean up:

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

[]
[]
[]
[]

See also

• Table functions: all the table functions.
• query: run a statement given as a string.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.