SHOW SCHEMAS and SHOW TABLES

Applies to VAST Cluster 5.5.

Lists the schemas in a database (bucket), or the tables in a schema. SHOW <table> lists a table's columns, like DESCRIBE.

Syntax

SHOW SCHEMAS "<bucket>"
SHOW TABLES "<bucket>/<schema>"
SHOW "<bucket>/<schema>".<table>

Parameters and rules

• Always name the bucket or schema. A bare SHOW TABLES fails with an internal error message, and SHOW ALL TABLES fails too.
• Sort the names in your code if you need an order.

What happens on the cluster

SHOW reads the database's catalog. It reads no table data.

Example

The examples use the run() helper and the environment variables described in the SQL reference. This block connects, and creates the schema docs_sql_show with two tables:

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

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)")
run(f"CREATE TABLE IF NOT EXISTS {SCHEMA}.customers (id BIGINT, name VARCHAR)")

[]
[]
[]

List the schema, its tables, and the columns of one table:

def names(sql):
    with conn.cursor() as cur:
        cur.execute(sql)
        return sorted(row[0] for row in cur.fetchall())

print([s for s in names(f'SHOW SCHEMAS "{BUCKET}"') if s == "docs_sql_show"])
print(names(f'SHOW TABLES "{BUCKET}/docs_sql_show"'))
run(f"SHOW {SCHEMA}.orders")

['docs_sql_show']
['customers', 'orders']
[('id', 'BIGINT', 'YES', None, None, None), ('status', 'VARCHAR', 'YES', None, None, None)]

SHOW ALL TABLES fails:

run("SHOW ALL TABLES")

InternalError: The operator 'ORDER_BY' type=1 is currently not supported for this usage.

Clean up:

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

[]
[]
[]

Errors

Trino and Spark

Trino and Spark list schemas and tables with their own SHOW statements, through their own catalogs; see Trino and Spark.

See also: DESCRIBE, CREATE SCHEMA.