Timeseries DataMap 是基于 'preaggregate' DataMap 实现的预聚合表。区别在于时间序列 DataMap 内置了对时间层次(time hierarchy)和级别的理解:年、月、日、时、分,以便它支持将查询中时间维度进行自动 roll-up。
数据加载、查询、压缩命令以及行为和预聚合 DataMap 一致,请参考 预聚合 DataMap 了解更多的信息。
要使用这个 datamap,用户可以在主表上创建多个时间序列 datamap,其中主表上有一个 event_time 的列,一个 datamap 对应一个时间粒度。然后 Carbondata 可以为主表上的查询进行自动汇总(automatic roll-up)。
例如,以下语句在名为 timeseries 的主表上有效地创建了多个预聚合表
CREATE DATAMAP agg_year
ON TABLE sales
USING "timeseries"
DMPROPERTIES (
'event_time'='order_time',
'year_granularity'='1',
) AS
SELECT order_time, country, sex, sum(quantity), max(quantity), count(user_id), sum(price),
avg(price) FROM sales GROUP BY order_time, country, sex
CREATE DATAMAP agg_month
ON TABLE sales
USING "timeseries"
DMPROPERTIES (
'event_time'='order_time',
'month_granularity'='1',
) AS
SELECT order_time, country, sex, sum(quantity), max(quantity), count(user_id), sum(price),
avg(price) FROM sales GROUP BY order_time, country, sex
CREATE DATAMAP agg_day
ON TABLE sales
USING "timeseries"
DMPROPERTIES (
'event_time'='order_time',
'day_granularity'='1',
) AS
SELECT order_time, country, sex, sum(quantity), max(quantity), count(user_id), sum(price),
avg(price) FROM sales GROUP BY order_time, country, sex
CREATE DATAMAP agg_sales_hour
ON TABLE sales
USING "timeseries"
DMPROPERTIES (
'event_time'='order_time',
'hour_granularity'='1',
) AS
SELECT order_time, country, sex, sum(quantity), max(quantity), count(user_id), sum(price),
avg(price) FROM sales GROUP BY order_time, country, sex
CREATE DATAMAP agg_minute
ON TABLE sales
USING "timeseries"
DMPROPERTIES (
'event_time'='order_time',
'minute_granularity'='1',
) AS
SELECT order_time, country, sex, sum(quantity), max(quantity), count(user_id), sum(price),
avg(price) FROM sales GROUP BY order_time, country, sex
CREATE DATAMAP agg_minute
ON TABLE sales
USING "timeseries"
DMPROPERTIES (
'event_time'='order_time',
'minute_granularity'='1',
) AS
SELECT order_time, country, sex, sum(quantity), max(quantity), count(user_id), sum(price),
avg(price) FROM sales GROUP BY order_time, country, sex
为了查询时间序列数据,Carbondata 内置支持时间相关的 UDF,从而能够自动滚动到所需的聚合级别
timeseries(timeseries column name, 'aggregation level')
SELECT timeseries(order_time, 'hour'), sum(quantity) FROM sales GROUP BY timeseries(order_time,
'hour')
除非查询需要,否则不必为每个粒度创建预聚合表。 Carbondata 可以汇总数据并获取它。
比如: 对于主表 sales , 如果以下时间序列 datamaps 是日级和小时级的预先聚合表
CREATE DATAMAP agg_day
ON TABLE sales
USING "timeseries"
DMPROPERTIES (
'event_time'='order_time',
'day_granularity'='1',
) AS
SELECT order_time, country, sex, sum(quantity), max(quantity), count(user_id), sum(price),
avg(price) FROM sales GROUP BY order_time, country, sex
CREATE DATAMAP agg_sales_hour
ON TABLE sales
USING "timeseries"
DMPROPERTIES (
'event_time'='order_time',
'hour_granularity'='1',
) AS
SELECT order_time, country, sex, sum(quantity), max(quantity), count(user_id), sum(price),
avg(price) FROM sales GROUP BY order_time, country, sex
下面这样的查询将被汇总并命中时间序列 datamaps
Select timeseries(order_time, 'month'), sum(quantity) from sales group by timeseries(order_time,
'month')
Select timeseries(order_time, 'year'), sum(quantity) from sales group by timeseries(order_time,
'year')
注意 (限制):
请参见预聚合 datamap 的压缩章节。同样适用于时间序列 datamap。
请参见预聚合 datamap 的数据管理章节。同样适用于时间序列 datamap。