MAPREDUCE服务 MRS-CREATE TABLE AS:示例

时间:2024-11-28 20:08:15

示例

  • 用指定列的查询结果创建新表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
  • 分区表示例:
    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;
support.huaweicloud.com/cmpntguide-lts-mrs/mrs_01_300131.html