PROJECTION

PROJECTION 是 Relation Projection ( RP )的简写，也就是关系投影的概念。

CREATE PROJECTION

创建投影，投影更新配置需要在表上设置，未设置时为默认选项。

AIR 1.0版本后支持

• 语法说明

创建明细 RP

ALTER (VIEW |TABLE) <tablePath> 
CREATE RAW PROJECTION [IF NOT EXISTS] <projectionName> 
USING
DISPLAY (field1, field2, ...)
[PARTITION BY (field1, field2, ...)]
[SORT BY (field1, field2, ...)]
[DISTRIBUTE BY (field1, field2, ...)]

• 使用案例

-- 创建【表】的投影
ALTER TABLE catalogName.schemName.tableName
CREATE RAW PROJECTION projectName
USING
DISPLAY ("cc_call_center_sk", "cc_call_center_id");

-- 创建【视图】的投影
ALTER VIEW catalogName.schemName.tableName
CREATE RAW PROJECTION mv1
USING
DISPLAY ("cc_call_center_sk", "cc_call_center_id");

创建聚合 RP

ALTER VIEW|TABLE <name> 
CREATE AGGREGATE PROJECTION [IF NOT EXISTS] <PROJECTION_NAME> 
USING 
DIMENSIONS (x by day, y) 
MEASURES (b (COUNT, MIN, MAX, SUM, APPROXIMATE COUNT DISTINCT)) [PARTITION BY (field1, field2, ...)]
[SORT BY (field1, field2, ...)]
[DISTRIBUTE BY (field1, field2, ...)]

• 使用案例

ALTER TABLE "hive1"."tpcds_1_text"."catalog_sales" CREATE AGGREGATE PROJECTION table_agg_rp
USING DIMENSIONS (cs_bill_customer_sk)
MEASURES (cs_wholesale_cost(MAX, MIN, COUNT, SUM))

ALTER PROJECTION

修改投影，投影更新配置需要在表上设置，未设置时为默认选项。

• 语法说明

ALTER RAW PROJECTION 'prpjectionId'
USING
DISPLAY (field1, field2, field3...)
[ PARTITION BY (field1, field2, field3...) ]
[ SORT BY (field1, field2, field...) ]
[ DISTRIBUTE BY (field1, field2, field3...) ]
ALTER AGGREGATE PROJECTION 'prpjectionId'
USING 
DIMENSIONS (x by day, y) 
MEASURES (b (COUNT, MIN, MAX, SUM, APPROXIMATE COUNT DISTINCT)) 
[ PARTITION BY (field1, field2, field3...) ]
[ SORT BY (field1, field2, field...) ]
[ DISTRIBUTE BY (field1, field2, field3...) ]

REFRESH PROJECTION

刷新投影数据。

• 语法说明

REFRESH PROJECTION 'rpId' [ AS 'materializationId' ]

AIR 1.0版本仅支持以下语法

REFRESH PROJECTION 'rpId'

SHOW PROJECTION

查看表的投影配置

• 语法说明

SHOW PROJECTION PROPERTIES IN <tableName>

• 返回结果

refresh_mode = 'On_Demand'
refresh_type = 'Complete'
active_interval = 'Infinite'

PROJECTIONS

查看表或视图下面的投影名称

• 语法说明

SHOW PROJECTIONS IN <projectionName>

• 使用示例

SHOW PROJECTIONS IN <projectionName>

• 返回结果

显示指定表或视图下面的投影列表。

Projection name | Projection Id | Projection Type | Status | Last Update Time
----------------+---------------+-----------------+--------+-------------------

DESC PROJECTION

查看RP详情

• 语法说明

DESC PROJECTION 'rpId';

• 返回结果

返回投影定义+投影状态+最近更新时间

对返回结果字段的说明如下。

字段名	说明
Projection Type	RawAggregate
DIMENSIONS	x by day, y
MEASURES	b (COUNT, MIN, MAX, SUM, APPROXIMATE COUNT DISTINCT)
PARTITION BY	field1
SORT BY	field2
DISTRIBUTE BY	field2
Status	Enabled，开启状态Disabled，关闭状态Error，数据集编辑了之后，Schema Failed的状态
Last Update Time	2022-09-28 12:00:00

ENABLE PROJECTION

开启投影。

• 语法说明

ENABLE PROJECTION 'rpId'
ALTER (TABLE | VIEW ) <name> ENABLE PROJECTION <projectionName>

DISABLE PROJECTION

关闭投影。

• 语法说明

DISABLE PROJECTION 'rpId'
ALTER (TABLE | VIEW ) <name> DISABLE PROJECTION <projectionName>

DROP PROJECTION

删除投影。

• 语法说明

DROP PROJECTION [IF EXISTS] 'rpId'
ALTER (TABLE | VIEW ) <name> DROP PROJECTION [IF EXISTS] <projectionName>