CREATE TABLE LIKE
Defines a table using the definition and metadata of an existing table or view.
The statement does not inherit primary key or foreign key constraints from the source table.
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 [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.-
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.
-
The name of the table whose definition is copied. The table must not be a Delta Lake table.
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 amanaged table
.-
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
ORC
HIVE
LIBSVM
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-specificfile_format
androw_format
using theOPTIONS
clause, which is a case-insensitive string map. The option keys areFILEFORMAT
,INPUTFORMAT
,OUTPUTFORMAT
,SERDE
,FIELDDELIM
,ESCAPEDELIM
,MAPKEYDELIM
, andLINEDELIM
.If you do not specify
USING
the format of the source table will be inherited.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 toDELIMITED
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
, andAVRO
. Alternatively, you can specify your own input and output formats throughINPUTFORMAT
andOUTPUTFORMAT
. Only formatsTEXTFILE
,SEQUENCEFILE
, andRCFILE
can be used withROW FORMAT SERDE
and onlyTEXTFILE
can be used withROW FORMAT DELIMITED
.
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';
-- 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');