CREATE TABLE LIKE

Syntax

CREATE TABLE [IF NOT EXISTS] table_name ({coulumn_name data_type [COMMENT comment] [WITH (property_name = expression [, ...])] | LIKE existing_table_name [{INCLUDING| EXCLUDING} PROPERTIES]}) [, ...] [COMMENT table_comment] [WITH (property_name = expression [, ...])]

Description

The LIKE clause allows you to include all column definitions of an existing table in a new table. You can use multiple LIKE statements to copy the columns of multiple tables.

If the INCLUDING PROPERTIES option is used, all attributes of the table are copied to the new table. This option takes effect for only one table.

You can use the WITH clause to modify the name of the attribute copied from the table.

The EXCLUDING PROPERTIES attribute is used by default.

For a table with partitions, if the LIKE clause is enclosed in parentheses, the copied column definition does not contain information about the partition key.

Example

• Create basic tables order01 and order02.

  CREATE TABLE order01(id int,name string,tel string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'STORED AS TEXTFILE;
  CREATE TABLE order02(sku int, sku_name string, sku_describe string);

• Create the orders_like01 table that contains the columns defined by the order01 table and the table properties.

  CREATE TABLE orders_like01 like order01 INCLUDING PROPERTIES;

• Create the orders_like02 table that contains the columns defined by the order02 table, and set the storage format of the table to TEXTFILE.

  CREATE TABLE orders_like02 like order02 STORED AS TEXTFILE;

• Create the orders_like03 table that contains the columns defined by the order01 table and the table properties, the columns defined by the order02 table, and additional columns c1 and c2.

  CREATE TABLE orders_like03 (c1 int,c2 float,LIKE order01 INCLUDING PROPERTIES,LIKE order02);

• Create the orders_like04 and orders_like05 tables. Both tables contain the definition of the same order_partition. The orders_like04 table does not contain but the orders_like05 table contains the partition key information.

  CREATE TABLE order_partition(id int,name string,tel string) PARTITIONED BY (sku int);
  CREATE TABLE orders_like04 (like order_partition);
  CREATE TABLE orders_like05 like order_partition;
  DESC orders_like04;
   Column |  Type   | Extra | Comment 
  --------|---------|-------|---------
   id     | integer |       |         
   name   | varchar |       |         
   tel    | varchar |       |         
   sku    | integer |       |         
  (4 rows)
  
  DESC orders_like05;
  
   Column |  Type   |     Extra     | Comment 
  --------|---------|---------------|---------
   id     | integer |               |         
   name   | varchar |               |         
   tel    | varchar |               |         
   sku    | integer | partition key |         
  (4 rows)