示例

• 创建一个新表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 new_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');