The CREATE DATABASE SQL for an OTF data lake creates the database metadata information in the OTF catalog. The data lake is expected to be created using the existing CREATE DATALAKE statement. The database created in a data lake is specified using a 2-level dot notation: <datalake_name.otf_database_name>.
Syntax
CREATE DATABASE <datalake_name>.<OTFdatabase_name> [{DBPROPERTIES | PROPERTIES} ([<custom_clause_list>])]; <datalake_name> ::= !! name of the data lake object <OTFdatabase_name> ::= !! name of the database in the OTF catalog <custom_clause_list> ::= <custom_clause>[, ...] <custom_clause> ::= <quote><name><quote> = <quote><value><quote> <name> ::= !! Teradata identifier <value> ::= !! Teradata literal value
Example
CREATE DATABASE datalake_glue.MyDatabase DBPROPERTIES('create_date'='2023-10-20','owner'='Mark');
Usage Considerations
- The name of the database object must not exist within the OTF data lake catalog. Otherwise, an error is returned to the user:
For example:
*** Error 7825 in UDF/XSP/UDM TD_OTFDB.ICEBERG_EXPORT: SQLSTATE 38001: [TD-Iceberg]-[AllCols]:Cannot create namespace db1 because it already exists in Glue
- A given name in name/value pair must not occur multiple times in DBPROPERTIES/PROPERTIES.
- DBPROPERTIES/PROPERTIES
- The properties that can be specified for an Iceberg database are specified in the Iceberg specification.
- The properties that can be specified for a Delta Lake database are specified in the Delta Lake specification.
- No other CREATE DATABASE options or clauses are allowed and result in a syntax error if specified.
- CREATE DATABASE...[FROM database_name] - FROM clause is not allowed.
- CREATE DATABASE .. AS <database_attribute_list> - AS clause is not allowed.
Delta Lake
Databricks database creation goes through a REST API. The database’s name is appended to the REST API request’s URL. For this reason, a database name cannot be allowed to contain any characters that are illegal in a URL.