CREATE SCHEMA

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Creates a schema (database) 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 | MANAGED LOCATION location_path } |
     WITH DBPROPERTIES ( { property_name = property_value } [ , ... ] ) ] [...]

Parameters

  • schema_name

    The name of the schema to be created.

    Schemas created in the hive_metastore catalog can only contain alphanumeric ASCII characters and underscores (INVALID_SCHEMA_OR_RELATION_NAME).

  • 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.

  • LOCATION schema_directory

    LOCATION is not supported in Unity Catalog. If you want to specify a storage location for a schema in Unity Catalog, use MANAGED LOCATION.

    schema_directory is a STRING literal. The 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

    If a schema (database) is registered in your workspace-level Hive metastore, dropping that schema using the CASCADE option causes all files in that schema location to be deleted recursively, regardless of the table type (managed or external).

    If the schema is registered to a Unity Catalog metastore, the files for Unity Catalog managed tables are deleted recursively. However, the files for external tables are not deleted. You must manage those files using the cloud storage provider directly.

    Therefore, to avoid accidental data loss, you should never register a schema in a Hive metastore to a location with existing data. Nor should you create new external tables in a location managed by Hive metastore schemas or containing Unity Catalog managed tables.

  • COMMENT schema_comment

    A STRING literal. The description for the schema.

  • MANAGED LOCATION location_path

    MANAGED LOCATION is optional and requires Unity Catalog. If you want to specify a storage location for a schema registered in your workspace-level Hive or third-party metastore, use LOCATION instead.

    location_path must be a STRING literal. Specifies the path to a storage root location for the schema that is different from the catalog’s or metastore’s storage root location. This path must be defined in an external location configuration, and you must have the CREATE MANAGED STORAGE privilege on the external location configuration. You can use the path that is defined in the external location configuration or a subpath (in other words, 's3://depts/finance' or 's3://depts/finance/product'). Supported in Databricks SQL or on clusters running Databricks Runtime 11.3 LTS and above.

    See also Work with managed tables and Create a Unity Catalog metastore.

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

    The properties for the schema in key-value pairs.

  • OPTIONS

    Sets connection-type specific parameters needed to identify the catalog at the connection.

    • option

      The option key. The key can consist of one or more identifiers separated by a dot, or a STRING literal.

      Option keys must be unique and are case-sensitive.

    • value

      The value for the option. The value must be a BOOLEAN, STRING, INTEGER, or DECIMAL constant expression. The value may also be a call to the SECRET SQL function. For example, the value for password may comprise secret('secrets.r.us', 'postgresPassword') as opposed to entering the literal password.

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`. LOCATION is not supported in Unity Catalog.
> CREATE SCHEMA IF NOT EXISTS customer_sc COMMENT 'This is customer schema' LOCATION '/samplepath'
    WITH DBPROPERTIES (ID=001, Name='John');

-- Create schema with a different managed storage location than the metastore's. MANAGED LOCATION is supported only in Unity Catalog.
> CREATE SCHEMA customer_sc MANAGED LOCATION 's3://depts/finance';

-- 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/samplepath
                Properties    ((ID,001), (Name,John))