CREATE TABLE LIKE

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

Defines a table using the definition and metadata of an existing table or view.

Delta Lake does support CREATE TABLE LIKE in Databricks SQL and Databricks Runtime 13.3 LTS and above. In Databricks Runtime 12.2 LTS and below, use CREATE TABLE AS.

Syntax 

CREATE TABLE [ IF NOT EXISTS ] table_name LIKE source_table_name [table_clauses]

table_clauses
   { USING data_source |
     LOCATION path |
     TBLPROPERTIES clause |
     ROW FORMAT row_format |
     STORED AS file_format } [...]

row_format
   { SERDE serde_class [ WITH SERDEPROPERTIES (serde_key = serde_val [, ...] ) ] |
     { DELIMITED [ FIELDS TERMINATED BY fields_terminated_char [ ESCAPED BY escaped_char ] ]
       [ COLLECTION ITEMS TERMINATED BY collection_items_terminated_char ]
       [ MAP KEYS TERMINATED BY map_key_terminated_char ]
       [ LINES TERMINATED BY row_terminated_char ]
       [ NULL DEFINED AS null_char ] } }

property_key
  { identifier [. ...] | string_literal }

Parameters

  • IF NOT EXISTS

    If specified ignores the statement if the table_name already exists.

  • table_name

    The name of the table to create. The name must not include a temporal specification. If the name is not qualified the table is created in the current schema. A table_name must not exist already.

  • source_table_name

    The name of the table whose definition is copied. source_table_name and table_name must both be on Hive metastore, or both be on Unity Catalog.

  • table_clauses

    Optionally specify a data source format, location, and user defined properties for the new table. Each sub clause may only be specified once.

    • LOCATION path

      Path to the directory where table data is stored, which could be a path on distributed storage. If you specify a location, the new table becomes an external table. If you do not specify a location, the table is a managed table.

      You cannot create external tables in locations that overlap with the location of managed tables.

    • TBLPROPERTIES

      Optionally sets one or more user defined properties.

    • USING data_source

      The file format to use for the table. data_source must be one of:

      • TEXT

      • CSV

      • JSON

      • JDBC

      • PARQUET

      • DELTA

      The following additional file formats to use for the table are supported in Databricks Runtime:

      • ORC

      • HIVE

      • LIBSVM

      • a fully-qualified class name of a custom implementation of org.apache.spark.sql.sources.DataSourceRegister.

      If you do not specify USING the format of the source table will be inherited.

      The following applies to: Databricks Runtime

      HIVE is supported to create a Hive SerDe table in Databricks Runtime. You can specify the Hive-specific file_format and row_format using the OPTIONS clause, which is a case-insensitive string map. The option_keys are:

      • FILEFORMAT

      • INPUTFORMAT

      • OUTPUTFORMAT

      • SERDE

      • FIELDDELIM

      • ESCAPEDELIM

      • MAPKEYDELIM

      • LINEDELIM

    • ROW FORMAT row_format

      Applies to: check marked yes Databricks Runtime

      To specify a custom SerDe, set to SERDE and specify the fully-qualified class name of a custom SerDe and optional SerDe properties. To use the native SerDe, set to DELIMITED and specify the delimiter, escape character, null character and so on.

      • SERDEPROPERTIES

        A list of key-value pairs used to tag the SerDe definition.

      • FIELDS TERMINATED BY

        Define a column separator.

      • ESCAPED BY

        Define the escape mechanism.

      • COLLECTION ITEMS TERMINATED BY

        Define a collection item separator.

      • MAP KEYS TERMINATED BY

        Define a map key separator.

      • LINES TERMINATED BY

        Define a row separator.

      • NULL DEFINED AS

        Define the specific value for NULL.

      • STORED AS

        The file format for the table. Available formats include TEXTFILE, SEQUENCEFILE, RCFILE, ORC, PARQUET, and AVRO. Alternatively, you can specify your own input and output formats through INPUTFORMAT and OUTPUTFORMAT. Only formats TEXTFILE, SEQUENCEFILE, and RCFILE can be used with ROW FORMAT SERDE and only TEXTFILE can be used with ROW FORMAT DELIMITED.

Notes

Depending on the data source and target not all properties of tables may be transferred.

CREATE TABLE LIKE when the source is a Delta Lake Table:

Feature/Property

Target is non-Delta Table

Target is a Delta Table

COMMENT

Yes

Yes

Columns

Yes

Yes

Partition columns

Yes

Yes

Configuration

No

Yes

Table Constraint

Not Applicable

Yes

Delta Protocol

Not Applicable

Yes

CREATE TABLE LIKE when the source is not a Delta Lake Table:

Feature/Property

Target is non-Delta Table

Target is a Delta Table

COMMENT

Yes

Yes

Columns

Yes

Yes

Partition columns

Yes

Yes

Configuration

Yes

Yes

Table Constraint

No

Yes

Delta Protocol

No (Current Default Protocol for that session)

Yes

Examples 

-- Create table using a new location
> CREATE TABLE Student_Dupli LIKE Student LOCATION '/mnt/data_files';

-- Create table like using a data source
> CREATE TABLE Student_Dupli LIKE Student USING CSV LOCATION '/mnt/csv_files';