MAPREDUCE服务 MRS-CREATE TABLE AS:示例

时间：2024-07-02 16:39:46

示例

用指定列的查询结果创建新表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;

上一篇：MAPREDUCE服务 MRS-CREATE TABLE AS:语法

下一篇：MAPREDUCE服务 MRS-CREATE TABLE AS:语法