CREATE SCHEMA
Applies to: Databricks SQL 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
-
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, useMANAGED LOCATION
.schema_directory
is aSTRING
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 configurationspark.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.
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, useLOCATION
instead.location_path
must be aSTRING
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 theCREATE 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,'abfss://container@storageaccount.dfs.core.chinacloudapi.cn/finance'
or'abfss://container@storageaccount.dfs.core.chinacloudapi.cn/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
, orDECIMAL
constant expression. The value may also be a call to theSECRET
SQL function. For example, thevalue
forpassword
may comprisesecret('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 'abfss://container@storageaccount.dfs.core.chinacloudapi.cn/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))