CREATE TABLE LIKE

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

Delta Lake does not support CREATE TABLE LIKE. Instead use CREATE TABLE AS.

Syntax 

CREATE TABLE [ IF NOT EXISTS ] table_name LIKE source_table_name
    USING data_source
    [ ROW FORMAT row_format ]
    [ STORED AS file_format ]
    [ TBLPROPERTIES ( property_key [ = ] property_val [, ...] ) ]
    [ LOCATION path ]


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 ]

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.

  • source_table_name

    The name of the table whose definition is copied. The table must not be a Delta Lake table.

  • USING data_source

    The file format to use for the table. data_source must be one of TEXT, CSV, JSON, JDBC, PARQUET, ORC, HIVE, or LIBSVM, or a fully-qualified class name of a custom implementation of org.apache.spark.sql.sources.DataSourceRegister.

    HIVE is supported to create a Hive SerDe table. 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, and LINEDELIM.

  • ROW FORMAT row_format

    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.

  • TBLPROPERTIES

    Sets one or more user defined properties of an existing view. Properties are key value pairs.

    • property_key

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

      Property keys must be unique.

    • property_val

      The new value for the property. The value must be a BOOLEAN, STRING, INTEGER, or DECIMAL literal.

  • LOCATION

    Path to the directory where table data is stored, which could be a path on distributed storage.

Examples 

-- Create table using an existing table
> CREATE TABLE Student_Dupli LIKE Student;

-- Create table like using a data source
> CREATE TABLE Student_Dupli LIKE Student USING CSV;

-- Table is created as external table at the location specified
> CREATE TABLE Student_Dupli LIKE Student LOCATION  '/root1/home';

-- Create table like using a rowformat
> CREATE TABLE Student_Dupli LIKE Student
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
    STORED AS TEXTFILE
    TBLPROPERTIES ('owner'='xxxx');