CREATE TABLE AS

Syntax

CREATE [EXTERNAL]¹ TABLE [IF NOT EXISTS] [catalog_name.][db_name.]table_name [ ( column_alias, ... ) ]

[[PARTITIONED BY ¹(col_name, ....)] [SORT BY¹ ([column [, column ...]])] ]¹

[COMMENT 'table_comment']

[ WITH ( property_name = expression [, ...] ) ]²

[[STORED AS file_format]¹

[LOCATION 'hdfs_path']¹

[TBLPROPERTIES (orc_table_property = value [, ...] ) ] ]¹

AS query

[ WITH [ NO ] DATA ]²

Remarks

The syntax of ¹ and ² cannot be used together.

When the avro_schema_url attribute is used:

• CREATE TABLE AS is not supported.
• When CREATE TABLE is used, partitioned_by and bucketed_by are not supported.
• Columns cannot be modified by using alter table.

Description

It is used to create a table that contains the SELECT query result.

Use the CREATE TABLE statement to create an empty table.

When the IF NOT EXISTS clause is used, no error is reported if the table already exists.

The WITH clause can be used to set the properties of a newly created table, such as the storage location of the table and whether the table is an external table.

Example

• Run the following statement to create the orders_column_aliased table based on the query result of a specified column:

  CREATE TABLE orders_column_aliased (order_date, total_price) 
  AS
  SELECT orderdate, totalprice FROM orders;

• Create the orders_by_data table based on the summary result of the orders table.

  CREATE TABLE orders_by_date
  COMMENT 'Summary of orders by date'
  WITH (format = 'ORC')
  AS
  SELECT orderdate, sum(totalprice) AS price
  FROM orders
  GROUP BY orderdate;

• If the orders_by_date table does not exist, create the orders_by_date table:

  CREATE TABLE IF NOT EXISTS orders_by_date AS
  SELECT orderdate, sum(totalprice) AS price
  FROM orders
  GROUP BY orderdate;

• Create the empty_orders table using the schema that is the same as that of the orders table but does not contain data:

  CREATE TABLE empty_orders AS
  SELECT *
  FROM orders
  WITH NO DATA;

• Use VALUES to create a table. For details, see VALUES.
• Partitioned table example:

  CREATE EXTERNAL TABLE hetu_copy(corderkey, corderstatus, ctotalprice, corderdate, cds) 
   PARTITIONED BY(cds)  
   SORT BY (corderkey, corderstatus) 
   COMMENT 'test'  
   STORED AS orc  
   LOCATION '/user/hetuserver/tmp'  
   TBLPROPERTIES (orc_bloom_filter_fpp = 0.3, orc_compress = 'SNAPPY', orc_compress_size = 6710422, orc_bloom_filter_columns = 'corderstatus,ctotalprice')  
   as select * from hetu_test; 
    
   CREATE TABLE hetu_copy1(corderkey, corderstatus, ctotalprice, corderdate, cds)  
   WITH (partitioned_by = ARRAY['cds'], bucketed_by = ARRAY['corderkey', 'corderstatus'],  
   sorted_by = ARRAY['corderkey', 'corderstatus'],  
   bucket_count = 16,  
   orc_compress = 'SNAPPY',  
   orc_compress_size = 6710422,  
   orc_bloom_filter_columns = ARRAY['corderstatus', 'ctotalprice'],  
   external = true,  
   format = 'orc',  
   location = '/user/hetuserver/tmp ') 
    as select * from hetu_test;