示例

• 创建一个新表orders，使用子句with指定创建表的存储格式、存储位置、以及是否为外表。

  通过“auto.purge”参数可以指定涉及到数据移除操作（如DROP、DELETE、INSERT OVERWRITE、TRUNCATE TABLE）时是否清除相关数据：

  • "auto.purge"='true'时，清除元数据和数据文件。
  • "auto.purge"='false'时，仅清除元数据，数据文件会移入HDFS回收站。默认值为“false”，且不建议用户修改此属性，避免数据删除后无法恢复。

  CREATE TABLE orders (
  orderkey bigint,
  orderstatus varchar,
  totalprice double,
  orderdate date
  )
  WITH (format = 'ORC', location='/user',orc_compress='ZLIB',external=true, "auto.purge"=false);
  
  -- 通过DESC FORMATTED 语句，可以查看建表的详细信息
  desc formatted  orders ;
                             Describe Formatted Table                           
  ------------------------------------------------------------------------------
   # col_name      data_type      comment                                       
   orderkey      bigint                                                         
   orderstatus      varchar                                                     
   totalprice      double                                                       
   orderdate      date                                                          
  
   # Detailed Table Information                                                 
   Database:                   default                                          
   Owner:                      admintest                                        
   LastAccessTime:             0                                                
   Location:                   hdfs://hacluster/user                            
   Table Type:                 EXTERNAL_TABLE                                   
  
   # Table Parameters:                                                          
          EXTERNAL                TRUE                                                     
          auto.purge              false                                                     
          orc.compress.size       262144                                                   
          orc.compression.codec   ZLIB                                                    
          orc.row.index.stride    10000                                                    
          orc.stripe.size         67108864                                                 
          presto_query_id         20220812_084110_00050_srknk@default@HetuEngine           
          presto_version          1.2.0-h0.cbu.mrs.320.r1-SNAPSHOT                         
          transient_lastDdlTime   1660293670                                              
  
   # Storage Information                                                        
   SerDe Library:              org.apache.hadoop.hive.ql.io.orc.OrcSerde        
   InputFormat:                org.apache.hadoop.hive.ql.io.orc.OrcInputFormat  
   OutputFormat:               org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat 
   Compressed:                 No                                               
   Num Buckets:                -1                                               
   Bucket Columns:             []                                               
   Sort Columns:               []                                               
   Storage Desc Params:                                                         
          serialization.format    1                                                        
  (1 row)

• 创建一个新表，指定Row format：

  --建表时，指定表的字段分隔符为‘,’号（如果创建外表，要求数据文件中的每条记录的字段是以逗号进行分隔）
  CREATE TABLE student(
  id string,birthday string,
  grade int,
  memo string) 
  ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
  
  --建表时，指定字段分隔符为'\t'，换行符为'\n'
  CREATE TABLE test(
  id int, 
  name string ,
  tel string)
  ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
  LINES TERMINATED BY '\n'
  STORED AS TEXTFILE;

• 如果表orders不存在，则创建表orders，并且增加表注释和列注释：

  CREATE TABLE IF NOT EXISTS orders (
  orderkey bigint,
  orderstatus varchar,
  totalprice double COMMENT 'Price in cents.',
  orderdate date
  )
  COMMENT 'A table to keep track of orders.';
  insert into orders values
  (202011181113,'online',9527,date '2020-11-11'),
  (202011181114,'online',666,date '2020-11-11'),
  (202011181115,'online',443,date '2020-11-11'),
  (202011181115,'offline',2896,date '2020-11-11');

• 使用表orders的列定义创建表bigger_orders：

  CREATE TABLE bigger_orders (
  another_orderkey bigint,
  LIKE orders,
  another_orderdate date
  );
  
  SHOW CREATE TABLE bigger_orders ;
                              Create Table                             
  ---------------------------------------------------------------------
   CREATE TABLE hive.default.bigger_orders (                           
      another_orderkey bigint,
      orderkey bigint,
      orderstatus varchar,
      totalprice double,
      ordersdate date,                                             
      another_orderdate date                                           
   )                                                                   
   WITH (                                                              
      external = false,                                                
      format = 'ORC',                                                  
      location = 'hdfs://hacluster/user/hive/warehouse/bigger_orders', 
      orc_compress = 'GZIP',                                           
      orc_compress_size = 262144,                                      
      orc_row_index_stride = 10000,                                    
      orc_stripe_size = 67108864                                       
   )                                                                   
  (1 row)

• 标号^① 建表示例：

  CREATE EXTERNAL TABLE hetu_test (orderkey bigint, orderstatus varchar, totalprice double, orderdate date) PARTITIONED BY(ds int) SORT BY (orderkey, orderstatus) COMMENT 'test' STORED AS ORC LOCATION '/user' TBLPROPERTIES (orc_compress = 'SNAPPY', orc_compress_size = 6710422, orc_bloom_filter_columns = 'orderstatus,totalprice');

• 标号^② 建表示例：

  CREATE EXTERNAL TABLE hetu_test1 (orderkey bigint, orderstatus varchar, totalprice double, orderdate date) COMMENT 'test' PARTITIONED BY(ds int) CLUSTERED BY (orderkey, orderstatus) SORTED BY (orderkey, orderstatus) INTO 16 BUCKETS STORED AS ORC LOCATION '/user'  TBLPROPERTIES (orc_compress = 'SNAPPY', orc_compress_size = 6710422, orc_bloom_filter_columns = 'orderstatus,totalprice');

• 标号^③ 建表示例：

  CREATE TABLE hetu_test2 (orderkey bigint, orderstatus varchar, totalprice double, orderdate date, ds int) COMMENT 'This table is in Hetu syntax' WITH (partitioned_by = ARRAY['ds'], bucketed_by = ARRAY['orderkey', 'orderstatus'], sorted_by = ARRAY['orderkey', 'orderstatus'], bucket_count = 16, orc_compress = 'SNAPPY', orc_compress_size = 6710422, orc_bloom_filter_columns = ARRAY['orderstatus', 'totalprice'], external = true, format = 'orc', location = '/user');