CREATE SCHEMA

Applies to VAST Cluster 5.5.

Creates a schema in a database (bucket), or inside another schema.

Syntax

CREATE SCHEMA [IF NOT EXISTS] "<bucket>/<schema>"
CREATE SCHEMA [IF NOT EXISTS] "<bucket>/<schema>/<child>"

Parameters and rules

• The full name includes every nested schema, so a schema inside another one is "<bucket>/<parent>/<child>".
• Schemas in a snapshot ("<bucket>/.snapshot/...") are read-only.

What happens on the cluster

CREATE SCHEMA is a change to the database's catalog on the cluster. No table data is read, and nothing is computed in the Query Engine. On a connection with autocommit=True the new schema is committed when the statement returns.

Example

The examples use the run() helper and the environment variables described in the SQL reference. Run the blocks in order, in one Python session. This block connects and creates the schema docs_sql_create_schema, then creates it again with IF NOT EXISTS, which does nothing:

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

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 SCHEMA IF NOT EXISTS {SCHEMA}")

with conn.cursor() as cur:
    cur.execute(f'SHOW SCHEMAS "{BUCKET}"')
    print([name for (name,) in cur.fetchall() if name.startswith("docs_sql_create_schema")])

[]
[]
['docs_sql_create_schema']

Create a schema inside it, and use a table in it:

CHILD = f'"{BUCKET}/docs_sql_create_schema/nested"'
run(f"CREATE SCHEMA IF NOT EXISTS {CHILD}")
run(f"CREATE TABLE IF NOT EXISTS {CHILD}.t (id BIGINT)")
run(f"INSERT INTO {CHILD}.t VALUES (1)")
run(f"SELECT id FROM {CHILD}.t")

[]
[]
[(1,)]
[(1,)]

Clean up:

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

[]
[]
[]

Errors

No CREATE SCHEMA errors are listed here. If the schema may already exist, write IF NOT EXISTS.

Trino and Spark

Trino and Spark create schemas with their own statements, and name them through their own catalogs (vast in Trino, ndb in Spark); see Trino and Spark.

See also: DROP SCHEMA, SHOW SCHEMAS.