DROP SCHEMA

Applies to VAST Cluster 5.5.

Drops a schema.

Syntax

DROP SCHEMA [IF EXISTS] "<bucket>/<schema>"

Parameters and rules

• Drop the schema's tables (and child schemas) first. What DROP SCHEMA does to a schema that still has tables isn't covered here, so don't rely on it.
• You can't drop a schema in a snapshot: snapshots are read-only.

What happens on the cluster

DROP SCHEMA is a change to the database's catalog. Nothing is computed in the Query Engine.

Example

The examples use the run() helper and the environment variables described in the SQL reference. This block connects, creates the schema docs_sql_drop_schema with a table, drops the table and then the schema, and lists what's left:

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_drop_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 TABLE IF NOT EXISTS {SCHEMA}.t (id BIGINT)")
run(f"DROP TABLE {SCHEMA}.t")
run(f"DROP SCHEMA {SCHEMA}")

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

[]
[]
[]
[]
[]

IF EXISTS on a schema that doesn't exist does nothing. Then close the connection:

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

[]

Errors

No DROP SCHEMA errors are listed here. If the schema may not exist, write IF EXISTS.

Trino and Spark

Trino and Spark drop schemas with their own statements, through their own catalogs; see Trino and Spark.

See also: CREATE SCHEMA, DROP TABLE.