CREATE CATALOG | Databricks on AWS (original) (raw)

Applies to: Databricks SQL Databricks Runtime 10.4 LTS and above Unity Catalog only

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

When you create a FOREIGN catalog it will be populated with all the schemas and their tables visible to the authenticating user.

CREATE CATALOG [ IF NOT EXISTS ] catalog_name
    [ USING SHARE provider_name . share_name |
      MANAGED LOCATION location_path |
      RETAIN DROPPED FOR number { HOUR | HOURS | DAY | DAYS | WEEK | WEEKS } |
      COMMENT comment |
      DEFAULT COLLATION default_collation_name |
      OPTIONS ( { option_name = option_value } [ , ... ] ) ] [...]

CREATE FOREIGN CATALOG [ IF NOT EXISTS ] catalog_name
    USING CONNECTION connection_name
    [ COMMENT comment ]
    OPTIONS ( { option_name = option_value } [ , ... ] )

• FOREIGN
  Applies to: Databricks SQL Databricks Runtime 13.3 LTS and above
  Specifies that the catalog is imported from a CONNECTION.
• IF NOT EXISTS
  Creates a catalog with the given name if it does not exist. If a catalog with the same name already exists, nothing will happen.
• catalog_name
  The name of the catalog to be created.
• USING SHARE provider_name . share_name
  Optionally specifies that the catalog is based on a OpenSharing share.
• provider_name
  The name of the OpenSharing provider who supplied the share.
• share_name
  The name of the share provided by provider_name.
• MANAGED LOCATION location_path
  Optionally specifies the path to a managed storage location for the catalog that is different than the metastore's root storage 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 Unity Catalog managed tables in Databricks for Delta Lake and Apache Iceberg and Create a Unity Catalog metastore.
• RETAIN DROPPED FOR number { HOUR | HOURS | DAY | DAYS | WEEK | WEEKS }
  Applies to: Databricks Runtime 17.3 and above Unity Catalog only
  Optionally sets the recovery period for dropped managed tables in the catalog — the period during which dropped tables can be recovered using the UNDROP TABLE command. If not specified, the default is 7 days. The value must be 0 hours (to disable recovery) or between 7–30 days, inclusive.
  Setting the recovery period to 0 means dropped tables are not recoverable using UNDROP and data files are deleted from cloud storage within 48 hours.
  If a recovery period is also set on a child schema, the schema-level setting takes precedence for tables in that schema. This setting only applies to tables dropped after the recovery period is configured. After the recovery period ends, data files for managed tables are deleted from your cloud storage within 48 hours.
  Not supported for foreign or shared catalogs. See also Drop a managed table.
• USING CONNECTION connection_name
  Specifies the connection where the source catalog resides.
• comment
  An optional STRING literal. The description for the catalog.
• DEFAULT COLLATION default_collation_name
  Applies to: : Databricks SQL Databricks Runtime 17.1 and above
  Optionally defines the default collation for objects defined within the catalog. If not specified the default collation is UTF8_BINARY.
• OPTIONS
  Sets connection-type specific parameters needed to identify the catalog at the connection.
• option_name
  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.
• option_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') instead of entering the literal password.