CREATE TABLE LIKE

Description

The CREATE TABLE statement defines a new table using the definition/metadata of an existing table or view.

Syntax

  1. CREATE TABLE [IF NOT EXISTS] table_identifier LIKE source_table_identifier
  2.     USING data_source
  3.     [ ROW FORMAT row_format ]
  4.     [ STORED AS file_format ]
  5.     [ TBLPROPERTIES ( key1=val1, key2=val2, ... ) ]
  6.     [ LOCATION path ]

Parameters

  • table_identifier

    Specifies a table name, which may be optionally qualified with a database name.

    Syntax: [ database_name. ] table_name

  • USING data_source

    Data Source is the input format used to create the table. Data source can be CSV, TXT, ORC, JDBC, PARQUET, etc.

  • ROW FORMAT

    SERDE is used to specify a custom SerDe or the DELIMITED clause in order to use the native SerDe.

  • STORED AS

    File format for table storage, could be TEXTFILE, ORC, PARQUET, etc.

  • TBLPROPERTIES

    Table properties that have to be set are specified, such as created.by.user, owner, etc.

  • LOCATION

    Path to the directory where table data is stored, which could be a path on distributed storage like HDFS, etc. Location to create an external table.

Examples

  1. -- Create table using an existing table
  2. CREATE TABLE Student_Dupli like Student;
  4. -- Create table like using a data source
  5. CREATE TABLE Student_Dupli like Student USING CSV;
  7. -- Table is created as external table at the location specified
  8. CREATE TABLE Student_Dupli like Student location  '/root1/home';
  10. -- Create table like using a rowformat
  11. CREATE TABLE Student_Dupli like Student
  12.     ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
  13.     STORED AS TEXTFILE
  14.     TBLPROPERTIES ('owner'='xxxx');