projections(投影)
Vertica支持创建包含聚合数据的projections,以提高聚合查询的效率。
聚合投影
Vertica提供了三种类型的投影,用于存储从聚合函数或表达式返回的数据:
- 包含表达式的投影:投影中包含从锚表列计算结果的列。
- 实时聚合投影:投影中包含具有从其锚表中的列的聚合值的列。还可以定义包含用户定义的转换函数的实时聚合投影。
- Top-K投影:实时聚合投影的类型,它返回所选行的分区中的前k行。创建满足Top-K查询条件的Top-K投影。
推荐用途
- 聚合投影对于针对大型数据集的查询最有用。
- 为了获得最佳查询性能,实时聚合投影的大小应该是锚表的一小部分 - 理想情况下,在锚表的1%到10%之间,如果可能的话,更小。
- 您无法更新或删除具有聚合投影的表中的数据。要更改锚表内容,必须首先删除与其关联的所有实时聚合和Top-K投影。因此,您应该对数据累积且不需要经常更新和删除的表使用聚合投影。
要求
- 要使用聚合投影,必须设置适当的配置参数。
- 如果从不干净的数据库关闭中手动恢复,实时聚合投影可能需要一些时间才能刷新。
使用表达式启用实时聚合投影和投影
要创建实时聚合和Top-K投影或带表达式的投影,必须将以下配置参数设置为1(默认设置):
配置参数 | 描述 | 启用 | 禁用 |
---|---|---|---|
EnableGroupByProjections | 启用实时聚合投影 | 1 | 0 |
EnableTopKProjections | 启用Top-K投影 | 1 | 0 |
EnableExprsInProjections | 启用带表达式的投影 | 1 | 0 |
要检查当前设置,请使用SHOW CURRENT。例如:
=> SHOW CURRENT EnableGroupByProjections;
level | name | setting
---------+--------------------------+---------
DEFAULT | EnableGroupByProjections | 1
(1 row)
实时聚合投影
实时聚合投影包含具有从其锚表中的列聚合的值的列。将数据加载到表中时,Vertica会在将数据加载到实时聚合投影之前聚合数据。在后续加载时,例如,通过INSERT或COPY,Vertica将使用新数据重新计算聚合并更新投影。
创建实时聚合投影
可以使用以下语法定义实时聚合投影:
CREATE PROJECTION proj-name AS
SELECT select-expression FROM table
GROUP BY group-expression;
例如:
=> CREATE PROJECTION clicks_agg AS
SELECT page_id,click_time :: DATE click_date,COUNT(*)num_clicks FROM clicks
GROUP BY page_id,click_time :: DATE KSAFE 1;
要求
以下要求适用于实时汇总投影:
- 必须启用实时聚合投影:配置参数EnableGroupByProjections必须设置为1(默认值)。
- 投影必须分段。
- SELECT和GROUP BY列的顺序必须相同。
限制
以下限制适用于实时聚合投影:
-
Vertica不会将投影视为超级投影,即使是包含所有表格列的投影也是如此。
-
您无法修改投影中包含的列的锚表元数据。您也不能删除这些列。要进行这些更改,必须首先删除与表关联的所有实时聚合和Top-K投影。
您无法对锚表数据执行以下操作:- 删除
- 更新
- 合并
要修改现有锚表数据,必须首先删除与其关联的所有实时聚合和Top-K投影。
-
投影只能引用自一个表。
实时聚合投影支持的功能
Vertica可以从以下聚合函数聚合实时聚合投影中的结果:
- SUM
- MAX
- MIN
- COUNT
使用DISTINCT的聚合函数
实时聚合投影可以支持包含使用关键字限定的聚合函数的查询DISTINCT。以下要求适用:
- 聚合表达式必须求值为非常量。
- projection的GROUP BY子句必须指定聚合表达式。
例如,以下查询用于SUM(DISTINCT)计算给定区域中所有唯一工资的总和:
SELECT customer_region,SUM(DISTINCT annual_income):: INT
FROM customer_dimension GROUP BY customer_region;
此查询可以使用以下实时聚合投影,该投影annual_income在其GROUP BY 子句中指定聚合列:
CREATE PROJECTION public.TotalRegionalIncome
(
customer_region,
annual_income,
Count
)
AS
SELECT customer_dimension.customer_region,
customer_dimension.annual_income,
count(*) AS Count
FROM public.customer_dimension
GROUP BY customer_dimension.customer_region,
customer_dimension.annual_income;
创建 Top-K Projections
定义语法如下:
CREATE PROJECTION proj-name [(proj-column-spec)]
AS SELECT select-expression FROM table
LIMIT num-rows OVER (PARTITION BY expression ORDER BY column-expr);
例如:
=> CREATE PROJECTION readings_topk (meter_id, recent_date, recent_value)
AS SELECT meter_id, reading_date, reading_value FROM readings
LIMIT 5 OVER (PARTITION BY meter_id ORDER BY reading_date DESC);
要求
以下要求适用于Top-K投影:
- 必须启用Top-K投影:配置参数EnableTopKProjections必须设置为1(默认值)。
- 投影必须分段。
- 该窗口分区子句必须使用PARTITION BY。
- 列PARTITION BY指定按SELECT列表中的一个或多个列表达式进行窗口分区。第一个列表达式是SELECT 列表中的第一个列表达式,第二个列表达式是SELECT列表中的第二个列表达式,依此类推。
- ORDER BY子句默认为递增(ASC)顺序,其所有列表达式必须来自SELECT 列表。
- 您必须使用该LIMIT选项来创建Top-K投影,而不是子查询。
限制
以下限制适用于Top-K投影:
-
Vertica不会将投影视为超级投影,即使是包含所有表格列的投影也是如此。
-
您无法修改投影中包含的列的锚表元数据。您也不能删除这些列。要进行这些更改,必须首先删除与表关联的所有实时聚合和Top-K投影。
-
您无法对锚表数据执行以下操作:
- 删除
- 更新
- 合并
要修改现有锚表数据,必须首先删除与其关联的所有实时聚合和Top-K投影。
-
投影只能引用自一个表。
例子:
创建一个包含有关个别股票交易的信息的表格:
=> CREATE TABLE trades(
symbol CHAR(16) NOT NULL,
trade_time TIMESTAMP NOT NULL,
price NUMERIC(12,4),
volume INT )
PARTITION BY (EXTRACT(year from trade_time) * 100 +
EXTRACT(month from trade_time));
往表中导入一些数据:
INSERT INTO trades VALUES('AAPL','2010-10-10 10:10:10'::TIMESTAMP,100.00,100);
INSERT INTO trades VALUES('AAPL','2010-10-10 10:10:10.3'::TIMESTAMP,101.00,100);
INSERT INTO trades VALUES ('AAPL','2011-10-10 10:10:10.5'::TIMESTAMP,106.1,1000);
INSERT INTO trades VALUES ('AAPL','2011-10-10 10:10:10.2'::TIMESTAMP,105.2,500);
INSERT INTO trades VALUES ('HPQ','2012-10-10 10:10:10.2'::TIMESTAMP,42.01,400);
INSERT INTO trades VALUES ('HPQ','2012-10-10 10:10:10.3'::TIMESTAMP,42.02,1000);
INSERT INTO trades VALUES ('HPQ','2012-10-10 10:10:10.4'::TIMESTAMP,42.05,100);
COMMIT;
返回每个股票代码的最新交易
=> CREATE PROJECTION trades_topk_a AS SELECT symbol, trade_time last_trade, price last_price
FROM trades LIMIT 1 OVER(PARTITION BY symbol ORDER BY trade_time DESC);
=> SELECT symbol, trade_time last_trade, price last_price FROM trades
LIMIT 1 OVER(PARTITION BY symbol ORDER BY trade_time DESC);
symbol | last_trade | last_price
------------------+-----------------------+------------
HPQ | 2012-10-10 10:10:10.4 | 42.0500
AAPL | 2011-10-10 10:10:10.5 | 106.1000
(2 rows)
在每个交易日返回最后一笔交易
=> CREATE PROJECTION trades_topk_b
AS SELECT symbol, trade_time::DATE trade_date, trade_time, price close_price, volume
FROM trades LIMIT 1 OVER(PARTITION BY symbol, trade_time::DATE ORDER BY trade_time DESC);
=> SELECT symbol, trade_time::DATE trade_date, trade_time, price close_price, volume
FROM trades LIMIT 1 OVER(PARTITION BY symbol, trade_time::DATE ORDER BY trade_time DESC);
symbol | trade_date | trade_time | close_price | volume
------------------+------------+-----------------------+-------------+--------
HPQ | 2012-10-10 | 2012-10-10 10:10:10.4 | 42.0500 | 100
AAPL | 2011-10-10 | 2011-10-10 10:10:10.5 | 106.1000 | 1000
AAPL | 2010-10-10 | 2010-10-10 10:10:10.3 | 101.0000 | 100
(3 rows)
在每个方案中,Vertica将trades表上的查询重定向到相应的Top-K投影,并从中返回聚合数据。随着附加数据加载到此表中,Vertica预先聚合新数据并更新Top-K投影,因此查询始终返回最新数据。
表达式投影
您可以创建一个投影,其中一个或多个列由表达式定义。表达式可以引用一个或多个锚表列。
例如,创建一个表,包含a和b两列。
=> CREATE TABLE values (a INT, b INT);
可以创建以a和b计算列的值c作为产物的表达的投影:
=> CREATE PROJECTION values_product (a, b, c)
AS SELECT a, b, a*b FROM values SEGMENTED BY HASH(a) ALL NODES KSAFE;
将数据加载到此投影中时,Vertica会解析a*b列c中的表达式。然后,您可以查询投影而不是锚表。Vertica返回预先计算的数据,并避免资源密集型计算产生的开销。
使用投影中的表达式还可以对表达式的计算结果进行排序或分段,而不是对单个列值进行排序。
注意:如果带表达式的投影还包括SUM或COUNT等聚合函数,则Vertica会将其视为实时聚合投影。
预聚合UDTF结果
CREATE PROJECTION可以定义调用用户定义的转换函数(UDTF)的实时聚合投影。为了在查询这些投影时最大限度地减少开销,Vertica会在后台处理这些函数并将其结果存储在磁盘上。
目前,实时聚合投影只能引用用C ++开发的UDTF。
关于这一点的使用,可以参考vertica官方文档。
参考:
vertica官方文档9.1.x—>Analyzing Data—>Data Aggregation–>Pre-Aggregating Data in Projections
链接如下:
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AnalyzingData/AggregatedData/AggregateDataRetrieve.htm%3FTocPath%3DAnalyzing%20Data|Data%20Aggregation|Pre-Aggregating%20Data%20in%20Projections|_____0