Databricks SQL – Schema

Posted on by

The code runs three statements. The first drops the schema just in case we are running this twice. The second creates the schema.

Note that there is no CATALOG provided. Databricks is set up to use a default catalog, and this is set up by your Databricks Administrator.

The third statement runs a DESCRIBE SCHEMA EXTENDED, which gives us information about the schema, including the location where managed table data will be stored.

DROP SCHEMA IF EXISTS demo_schema CASCADE;
CREATE SCHEMA IF NOT EXISTS demo_schema;
DESCRIBE SCHEMA EXTENDED demo_schema;

DROP SCHEMA (Databricks SQL)

Drops a schema and deletes the directory associated with the schema from the file system. An exception is thrown if the schema does not exist in the system.

While usage of SCHEMA and DATABASE is interchangeable, SCHEMA is preferred.

DROP SCHEMA [ IF EXISTS ] schema_name [ RESTRICT | CASCADE ]

Parameters

  • IF EXISTS – If specified, no exception is thrown when the schema does not exist.
  • schema_name – The name of an existing schemas in the system. If the name does not exist, an exception is thrown.
  • RESTRICT – If specified, will restrict dropping a non-empty schema and is enabled by default.
  • CASCADE – If specified, will drop all the associated tables and functions.

CREATE SCHEMA (Databricks SQL)

Creates a schema with the specified name. If a schema with the same name already exists, an exception is thrown.

CREATE SCHEMA [ IF NOT EXISTS ] schema_name
    [ COMMENT schema_comment ]
    [ LOCATION schema_directory ]
    [ WITH DBPROPERTIES ( property_name = property_value [ , ... ] ) ]

Parameters

  • schema_name – The name of the schema to be created.
  • IF NOT EXISTS – Creates a schema with the given name if it does not exist. If a schema with the same name already exists, nothing will happen.
  • schema_directory – Path of the file system in which the specified schema is to be created. If the specified path does not exist in the underlying file system, creates a directory with the path. If the location is not specified, the schema is created in the default warehouse directory, whose path is configured by the static configuration spark.sql.warehouse.dir.
  • schema_comment – The description for the schema.
  • WITH DBPROPERTIES ( property_name = property_value [ , … ] ) – The properties for the schema in key-value pairs.

DESCRIBE SCHEMA (Databricks SQL)

Returns the metadata of an existing schema. The metadata information includes the schema’s name, comment, and location on the filesystem. If the optional EXTENDED option is specified, schema properties are also returned.

While usage of SCHEMA and DATABASE is interchangeable, SCHEMA is preferred.

{ DESC | DESCRIBE } SCHEMA [ EXTENDED ] schema_name

Parameters

  • schema_name – The name of an existing schema (schema) in the system. If the name does not exist, an exception is thrown.