# CREATE TABLE AS
#### 语法
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 \]^②^
#### 限制
① 和 ②的语法不能组合使用。
当使用了avro_schema_url属性时，以下操作是不支持的：
- 不支持CREATE TABLE AS操作
- 使用CREATE TABLE时不支持partitioned_by和bucketed_by
- 不支持使用alter table修改column
 
#### 描述
创建包含SELECT查询结果的新表。
使用CREATE TABLE创建空表。
使用IF NOT EXISTS子句时，如果表已经存在则不会报错。
可选WITH子句可用于设置新创建的表的属性，如表的存储位置（location）、是不是外表（external）等。
#### 示例
- 用指定列的查询结果创建新表orders_column_aliased：
  ```
  CREATE TABLE orders_column_aliased (order_date, total_price) 
  AS
  SELECT orderdate, totalprice FROM orders;
  ```
  
- 用表orders的汇总结果新建一个表orders_by_data：
  ```
  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;
  ```
  
- 如果表orders_by_date不存在，则创建表orders_by_date：
  ```
  CREATE TABLE IF NOT EXISTS orders_by_date AS
  SELECT orderdate, sum(totalprice) AS price
  FROM orders
  GROUP BY orderdate;
  ```
  
- 用和表orders具有相同schema创建新表empty_orders table，但是没数据：
  ```
  CREATE TABLE empty_orders AS
  SELECT *
  FROM orders
  WITH NO DATA;
  ```
  
- 使用VALUES创建表，参考 [VALUES](https://support.huaweicloud.com/cmpntguide-lts-mrs/mrs_01_300149.html)。
- 分区表示例:
  ```
  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;
  ```