CREATE SCHEMA (Databricks SQL)

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

Syntax

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.

Warning

To avoid accidental data loss, do not register a schema (database) to a location with existing data or create new external tables in a location managed by a schema. Dropping a schema will recursively delete all data files in the managed location.

  • schema_comment

    The description for the schema.

  • WITH DBPROPERTIES ( property_name = property_value [ , … ] )

    The properties for the schema in key-value pairs.

Examples

-- Create schema `customer_sc`. This throws exception if schema with name customer_sc
-- already exists.
> CREATE SCHEMA customer_sc;

-- Create schema `customer_sc` only if schema with same name doesn't exist.
> CREATE SCHEMA IF NOT EXISTS customer_sc;

-- Create schema `customer_sc` only if schema with same name doesn't exist with
-- `Comments`,`Specific Location` and `Database properties`.
> CREATE SCHEMA IF NOT EXISTS customer_sc COMMENT 'This is customer schema' LOCATION '/user'
    WITH DBPROPERTIES (ID=001, Name='John');

-- Verify that properties are set.
> DESCRIBE SCHEMA EXTENDED customer_sc;
 database_description_item database_description_value
 ------------------------- --------------------------
             Database Name                customer_sc
               Description  This is customer schema
                  Location      hdfs://hacluster/user
                Properties    ((ID,001), (Name,John))