carbondate mysql_Apache CarbonData 1.3.1 中文文档

CarbonData 时间序列 DataMap

时间序列 DataMap 介绍 (Alpha feature in 1.3.0)

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')

注意 (限制):

仅支持层次级别为 1。 未来的 CarbonData 将支持其他层级。

需要一个接着一个地创建所需级别的时间序列 datamap

每个级别时间序列 datamap 需要分别进行删除

压缩时间序列 datamp

请参见预聚合 datamap 的压缩章节。同样适用于时间序列 datamap。

时间序列 datamap 上的数据管理

请参见预聚合 datamap 的数据管理章节。同样适用于时间序列 datamap。