Usage

Cubes can be managed using any of the supported clients, such as hetu-cli located under the bin directory in the installation.

CREATE CUBE

Synopsis

CREATE CUBE [ IF NOT EXISTS ]
cube_name ON table_name WITH (
   AGGREGATIONS = ( expression [, ...] ),
   GROUP = ( column_name [, ...])
   [, FILTER = (expression)]
   [, ( property_name = expression [, ...] ) ] 
)
[WHERE predicate]

Description

Create a new, empty Cube with the specified group and aggregations. Use INSERT INTO CUBE (see below) to insert into data.

The optional IF NOT EXISTS clause causes the error to be suppressed if the Cube already exists. The optional property_name section can be used to set properties on the newly created Cube.

To list all available table properties, run the following query:

SELECT * FROM system.metadata.table_properties

Note: These properties are limited to the Connector which the Cube is being created for.

Examples

Create a new Cube orders_cube on orders:

CREATE CUBE orders_cube ON orders WITH (
  AGGREGATIONS = ( SUM(totalprice), AVG(totalprice) ),
  GROUP = ( orderstatus, orderdate ),
  format = 'ORC'
)

Create a new partitioned Cube orders_cube:

CREATE CUBE orders_cube ON orders WITH (
  AGGREGATIONS = ( SUM(totalprice), AVG(totalprice) ),
  GROUP = ( orderstatus, orderdate ),
  format = 'ORC',
  partitioned_by = ARRAY['orderdate']
)

Create a new Cube orders_cube with some source data filter:

CREATE CUBE orders_cube ON orders WITH (
  AGGREGATIONS = ( SUM(totalprice), COUNT DISTINCT(orderid) ),
  GROUP = ( orderstatus ),
  FILTER = (orderdate BETWEEN 2512450 AND 2512460)
)

Create a new Cube orders_cube with some additional predicate on Cube columns:

CREATE CUBE orders_cube ON orders WITH (
  AGGREGATIONS = ( SUM(totalprice), COUNT DISTINCT(orderid) ),
  GROUP = ( orderstatus ),
  FILTER = (orderdate BETWEEN 2512450 AND 2512460)
) WHERE orderstatus = 'PENDING';

This is same as following:

CREATE CUBE orders_cube ON orders WITH (
  AGGREGATIONS = ( SUM(totalprice), COUNT DISTINCT(orderid) ),
  GROUP = ( orderstatus ),
  FILTER = (orderdate BETWEEN 2512450 AND 2512460)
);
INSERT INTO CUBE orders_cube WHERE orderstatus = 'PENDING';

The FILTER property can be used to filter out data from the source table while building the Cube. Cube is built on the data after applying the orderdate BETWEEN 2512450 AND 2512460 predicate on the source table. The columns used in the filter predicate must not be part the Cube.

Limitations

  • Cubes can be created with only following aggregation functions.
    In other words, Queries using the following functions can only be optimized using Cubes. COUNT, COUNT DISTINCT, MIN, MAX, SUM, AVG
  • Different connector might support different data type, and different table/column properties.

INSERT INTO CUBE

Synopsis

INSERT INTO CUBE cube_name [WHERE condition]

Description

CREATE CUBE statement creates Cube without any data. To insert data into Cube, use INSERT INTO CUBE SQL. The WHERE clause is optional. If predicate is provided, only data matching the given predicate are processed from the source table and inserted into the Cube. Otherwise, entire data from the source table is processed and inserted into Cube.

Examples

Insert data into the orders_cube Cube:

INSERT INTO CUBE orders_cube WHERE orderdate > date '1999-01-01';
INSERT INTO CUBE order_all_cube;

Limitations

  1. Subsequent inserts to the same Cube need to use same set of columns
   CREATE CUBE orders_cube ON orders WITH (AGGREGATIONS = (count(*)), GROUP = (orderdate));
   
   INSERT INTO CUBE orders_cube WHERE orderdate BETWEEN date '1999-01-01' AND date '1999-01-05';
   
   -- This statement would fail because its possible the Cube already contain rows matching the given predicate.
   INSERT INTO CUBE orders_cube WHERE location = 'Canada';

Note: This means that columns used in the first insert must be used in every insert predicate following the first to avoid inserting duplicate data.

INSERT OVERWRITE CUBE

Synopsis

INSERT OVERWRITE CUBE cube_name [WHERE condition]

Description

Similar to INSERT INTO CUBE statement but with this statement the existing data is overwritten. Predicates are optional.INSERT OVERWRITE CUBE is not supported on partitioned cubes. Cubes are essentially stored as tables and so INSERT OVERWRITE only replaces the matching partitions and does not overwrite the entire table. So this operation is blocked on partitioned cube. Drop and recreate cube if needed.

Examples

Insert data based on condition into the orders_cube Cube:

INSERT OVERWRITE CUBE orders_cube WHERE orderdate > date '1999-01-01';
INSERT OVERWRITE CUBE orders_cube;

SHOW CUBES

Synopsis

SHOW CUBES [ FOR table_name ];

Description

SHOW CUBES lists all Cubes. Adding the optional table_name lists only the Cubes for that table.

Examples

Show all Cubes:

    SHOW CUBES;

Show Cubes for orders table:

    SHOW CUBES FOR orders;

RELOAD CUBE

Synopsis

RELOAD CUBE cube_name

Description

Reloads the Cube if the source table has been updated.

Examples

If the source table orders of the cube orders_cube gets updated then the status of the cube orders_cube gets EXPIRED. Use the command RELOAD CUBE cube_name to overcome this issue as follows:

    RELOAD CUBE orders_cube

DROP CUBE

Synopsis

DROP CUBE  [ IF EXISTS ] cube_name

Description

Drop an existing Cube.

The optional IF EXISTS clause causes the error to be suppressed if the Cube does not exist.

Examples

Drop the Cube orders_cube:

DROP CUBE orders_cube

Drop the Cube orders_cube if it exists:

DROP CUBE IF EXISTS orders_cube

有奖捉虫

“有虫”文档片段

0/500

存在的问题

文档存在风险与错误

● 拼写,格式,无效链接等错误;

● 技术原理、功能、规格等描述和软件不一致,存在错误;

● 原理图、架构图等存在错误;

● 版本号不匹配:文档版本或内容描述和实际软件不一致;

● 对重要数据或系统存在风险的操作,缺少安全提示;

● 排版不美观,影响阅读;

内容描述不清晰

● 描述存在歧义;

● 图形、表格、文字等晦涩难懂;

● 逻辑不清晰,该分类、分项、分步骤的没有给出;

内容获取有困难

● 很难通过搜索引擎,openLooKeng官网,相关博客找到所需内容;

示例代码有错误

● 命令、命令参数等错误;

● 命令无法执行或无法完成对应功能;

内容有缺失

● 关键步骤错误或缺失,无法指导用户完成任务,比如安装、配置、部署等;

● 逻辑不清晰,该分类、分项、分步骤的没有给出

● 图形、表格、文字等晦涩难懂

● 缺少必要的前提条件、注意事项等;

● 描述存在歧义

0/500

您对文档的总体满意度

非常不满意
非常满意

请问是什么原因让您参与到这个问题中

您的邮箱

创Issue赢奖品
根据您的反馈,会自动生成issue模板。您只需点击按钮,创建issue即可。
有奖捉虫