文档

列存索引

更新时间:

索引是加速数据查询的重要方法。列存索引可以增强宽表中海量数据的分析计算能力,主要适用于车联网与物联网的设备信息统计、电商领域的数据分析、物流行业的订单统计等场景。本文介绍列存索引的基础用法和高阶用法,帮助您快速上手并进一步掌握列存索引。

前提条件

  • 已开通列存索引功能。

    重要

    列存索引功能需联系Lindorm技术支持(钉钉号:s0s3eg3)开通。

  • 已开通计算引擎。具体操作,请参见开通与变配

  • 已开通文件引擎,且文件引擎的版本为4.0.0及以上版本。

  • 已开通宽表引擎,且宽表引擎的版本为2.5.0及以上版本。

注意事项

  • 列存索引不支持同步构建方式

  • 列存索引的构建耗时为15分钟左右。如果后台索引构建任务的数量较多,业务数据量较大,那么构建列存索引的用时可能更长。

快速入门

假设要对海量数据表my_tbl进行高效并行数据分析,您需要为该表创建列存索引。

示例表my_tbl的结构如下:

+------------+-------------+---------+----------------+
| TABLE_NAME | COLUMN_NAME |  TYPE   | IS_PRIMARY_KEY |
+------------+-------------+---------+----------------+
| my_tbl     | pk0         | INT     | true           |
| my_tbl     | pk1         | VARCHAR | true           |
| my_tbl     | pt_d        | VARCHAR | true           |
| my_tbl     | col0        | INT     | false          |
| my_tbl     | col1        | VARCHAR | false          |
| my_tbl     | json_col0   | JSON    | false          |
+------------+-------------+---------+----------------+

主键pk0代表该行数据的标识,拥有较大的基数。主键pt_d表示该行数据产生的日期,通常会按照天级别进行数据分析。

  1. 创建列存索引。列存索引能自动为您展开JSON类型字段中存储的数据。

    • 如果my_tbl的表结构比较稳定,不会频繁发生变化。请执行以下语句:

      CREATE INDEX my_tbl_idx USING COLUMNAR
      ON my_tbl(*) 
      PARTITION BY ENUMERABLE (pt_d, bucket(128, pk0))
      WITH (
        `lindorm_columnar.user.index.database` = 'my_index_db',
        `lindorm_columnar.user.index.table` = 'my_index_tbl',
        `lindorm_columnar.user.syncer.lci.dynamicJsonColumns` = 'json_col0'
        );

      列存索引会根据您当前表结构以及json_col0的结构,来创建索引表。

    • 如果my_tbl的表结构可能会频繁变化,请执行以下语句:

      CREATE INDEX my_tbl_idx USING COLUMNAR
      ON my_tbl(*) 
      PARTITION BY ENUMERABLE (pt_d, bucket(128, pk0))
      WITH (
        `lindorm_columnar.user.index.database` = 'my_index_db',
        `lindorm_columnar.user.index.table` = 'my_index_tbl',
        `lindorm_columnar.user.syncer.lci.dynamicJsonColumns` = 'json_col0',
        `lindorm_columnar.user.syncer.lci.dynamicSchema` = 'true');

      列存索引会根据您当前的表结构以及json_col0的结构,来创建索引表,并根据您后续的表结构、JSON字段内容变化来动态扩展索引表。

  2. 查看索引状态。

    SHOW INDEX FROM my_tbl;

    SHOW INDEX的使用方法及返回结果集说明,请参见SHOW INDEX

  3. 使用列存索引进行数据查询分析。具体操作,请参见使用列存索引

基础用法

假设示例表my_tbl的结构如下:

+------------+-------------+---------+----------------+
| TABLE_NAME | COLUMN_NAME |  TYPE   | IS_PRIMARY_KEY |
+------------+-------------+---------+----------------+
| my_tbl     | pk0         | INT     | true           |
| my_tbl     | pk1         | VARCHAR | true           |
| my_tbl     | pk2         | VARCHAR | true           |
| my_tbl     | col0        | INT     | false          |
| my_tbl     | col1        | VARCHAR | false          |
+------------+-------------+---------+----------------+

创建列存索引

语法

CREATE INDEX index_name USING COLUMNAR
ON table_name(column_name(,..))
PARTITION BY ENUMERABLE (column_name(,...), bucket(bucket_num, column_name))
WITH (`lindorm_columnar.user.index.database` = 'columnar_db_name',
      `lindorm_columnar.user.index.table` = 'columnar_tbl_name');
参数说明

参数

说明

index_name

列存索引的名称,由大写字母、小写字母、数字、下划线(_)其中的一种或多种组成。

table_name

宽表名称。

column_name(,...))

需要创建列存索引的字段列表,多个字段用英文逗号(,)分隔。目前支持创建列存索引的字段类型包括:TINYINT、SMALLINT、INTEGER、BIGINT、LONG、FLOAT、DOUBLE、VARCHAR、BINARY、VARBINARY、BOOLEAN。

说明

该字段列表必须包含对应宽表的全部主键字段,若您需要为全部字段(主键和非主键)创建列存索引,可简写为(*)

PARTITION BY ENUMERABLE(column_name(,...), bucket(bucket_num, column_name))

指定索引数据按照枚举算法进行分区,从而提升查询过程中的检索能力。分区表达式包括普通分区表达式和bucket分区表达式,且普通分区表达式和bucket分区表达式中的字段均为宽表主键字段。

  • 普通分区表达式

    • 可以指定0个或多个普通分区表达式,多个表达式用英文逗号(,)分隔。

    • 普通分区表达式为宽表主键字段(例如城市、日期等),索引数据会按照不同的分区值构建,并在查询时按照分区过滤条件高效定位数据。

  • bucket分区表达式

    • 至少指定1个bucket分区表达式。

    • bucket_num为bucket分区数目,column_name为bucket分区字段,用于计算bucket分区号(bucket_index)。bucket_index的计算方法为基于表达式中的bucket分区字段计算hash值,然后对bucket_num取余得出。在下面的示例中,bucket_index的计算方法为hash(pk0)%128

    • bucket分区字段需为宽表中的主键字段,并确保bucket分区字段具有足够的离散特征,避免不同分区间的数据倾斜。

    普通分区表达式和bucket分区表达式共同决定了索引数据的分区数量,建议将每个分区的数据量设置在50 MB到512 MB之间。例如:普通分区表达式为日期字段dt,宽表单日数据量为50 GB,宽表主键字段为(id, dt),可以将分区表达式配置为PARTITION BY ENUMERABLE (dt, bucket(200, id))

WITH(`key` = 'value')

使用WITH关键字为列存索引指定以下参数:

  • lindorm_columnar.user.index.database:指定列存索引表所在Database名称。

  • lindorm_columnar.user.index.table:指定列存索引表名称。

示例

以表my_tbl为例创建列存索引:

CREATE INDEX my_tbl_idx USING COLUMNAR
ON my_tbl(pk0, pk1, pk2, col0, col1)
PARTITION BY ENUMERABLE (pk1, pk2, bucket(128, pk0))
WITH (`lindorm_columnar.user.index.database` = 'my_index_db',
      `lindorm_columnar.user.index.table` = 'my_index_tbl');

查询列存索引

列存索引创建成功后,索引数据会持续构建,宽表作为主表会持续将表中的数据同步至列存索引表中。数据同步包括存量数据同步和增量数据同步,增量数据同步过程中,索引数据与主表的数据会存在延迟,延迟时间小于30分钟。

您可以通过SHOW INDEX语句查看列存索引的状态。SHOW INDEX的使用方法及返回结果集说明,请参见SHOW INDEX

使用列存索引

创建列存索引可以增强宽表海量数据的分析计算能力,您可以在SELECT查询语句中指定Hint /*+ _use_ldps_ */,将查询请求路由至计算引擎执行并使用列存索引加速查询,从而提升大数据计算的效率。

示例一:大数据统计

SELECT /*+ _use_ldps_ */ COUNT(*), SUM(col0), MIN(col0), MAX(col0)
FROM lindorm_columnar.my_index_db.my_index_tbl
WHERE col0 > 100 AND col0 < 200 OR col0 > 500
GROUP BY pk1;

示例二:大数据排序

SELECT /*+ _use_ldps_ */ pk0 + col0, pk1
FROM lindorm_columnar.my_index_db.my_index_tbl
WHERE col0 > 100 AND col0 < 200 OR col0 > 500
ORDER BY pk1
LIMIT 100;

示例三:大数据关联

如果您为多个宽表创建了列存索引,也可以将宽表间的数据进行关联。

SELECT /*+ _use_ldps_ */ *
FROM lindorm_columnar.my_index_db.my_index_tbl0 as t0
  JOIN lindorm_columnar.my_index_db.my_index_tbl1 as t1 
ON t0.pk0 = t1.pk0
AND t0.pk1 = t1.pk1
LIMIT 100;
说明

通过select /*+ _use_ldps_ */ webui()获取WebUI地址,可以查看SELECT语句的执行信息。

删除列存索引

您可以通过DROP INDEX语句删除指定的列存索引。DROP INDEX的使用方法及示例,请参见DROP INDEX

进阶用法

复杂分区表达式

假设示例表my_ts_tbl的表结构如下:

+------------+-------------+---------+----------------+
| TABLE_NAME | COLUMN_NAME |  TYPE   | IS_PRIMARY_KEY |
+------------+-------------+---------+----------------+
| my_ts_tbl  | id          | INT     | true           |
| my_ts_tbl  | ts          | LONG    | true           |
| my_ts_tbl  | col0        | VARCHAR | false          |
| my_ts_tbl  | col1        | INT     | false          |
+------------+-------------+---------+----------------+

在创建列存索引时,若宽表数据的主键字段不能直接作为列存索引的普通分区表达式,可以在普通分区表达式中包含计算逻辑,示例如下。

  • 对宽表所有字段创建列存索引,将列存索引数据按时间戳字段ts按天分区:

    CREATE INDEX my_ts_idx USING COLUMNAR ON my_ts_tbl(*)
    PARTITION BY ENUMERABLE (ifnull(substring(from_unixtime(ts), 0, 10), 'unknown') AS dt, bucket(128, id))
    WITH (`lindorm_columnar.user.index.database` = 'my_ts_index_db',
          `lindorm_columnar.user.index.table` = 'my_ts_index_tbl');
  • 列存索引创建完成后,您可以在查询语句中指定过滤条件并查询列存索引中的数据。

    SELECT /*+ _use_ldps_ */ COUNT(1)
    FROM lindorm_columnar.my_ts_index_db.my_ts_index_tbl
    WHERE dt = '2020-06-06';

仅为增量数据构建列存索引

如果您需要跳过宽表中的存量数据,只为增量数据构建列存索引,可以指定参数lindorm_columnar.user.syncer.skip.fullsync = 'true',示例如下:

CREATE INDEX my_tbl_idx USING COLUMNAR ON my_tbl(*)
PARTITION BY ENUMERABLE (pk1, pk2, bucket(128, pk0))
WITH (`lindorm_columnar.user.index.database` = 'my_index_db',
      `lindorm_columnar.user.index.table` = 'my_index_tbl',
      `lindorm_columnar.user.syncer.skip.fullsync` = 'true');

配置列存索引查询参数

列存索引支持在连接串中添加计算引擎配置项。具体配置项,请参见作业配置说明

./lindorm-cli -url <jdbc url>?hiveconf:key=value?hiveconf:key=value -username <用户名> -password <密码>

其中,jdbc url为控制台获取的Lindorm 宽表SQL地址。如何获取,请参见查看连接地址

重要

配置项会在首次启动计算引擎节点时生效。计算引擎节点启动后需要变更配置,请在SparkUI界面的Kyuubi Query Engine页签中,单击kill关闭原有计算引擎节点。

JSON字段展开映射

列存索引支持在数据同步后,将表中JSON类型的字段展开存储。在创建列存索引时,定义JSON字段到列存字段之间的映射关系。

  1. 创建包含JSON字段的表。

    CREATE TABLE IF NOT EXISTS my_json_tbl(id BIGINT, col1 INTEGER, col2 DOUBLE, json_col JSON, PRIMARY KEY(id));
  2. 写入数据。

    假设json_col的结构如下:

    {
      "a": {
        "b": {
          "c": "hello,world",
          "d": 123
        },
        "e": false
      },
      "f": 3.14
    }
  3. 创建列存索引时,基于WITH关键字,指定展开JSON字段的映射规则。

    CREATE INDEX columnar_idx USING COLUMNAR ON my_json_tbl(*) 
    PARTITION BY ENUMERABLE (ifnull(id%16, 0) as dt, bucket(16,id)) 
    WITH (
      `lindorm_columnar.user.syncer.lci.jsonMapping.json_col` = 'a.b.c VARCHAR, a.e BOOLEAN ,f DOUBLE',
      `lindorm_columnar.user.index.database` = 'my_index_db',
      `lindorm_columnar.user.index.table` = 'my_index_tbl');

    参数说明

    • lindorm_columnar.user.syncer.lci.jsonMapping.json_col:指定表中需要映射的JSON列,此处指定列json_col。

    • a.b.c VARCHAR, a.e BOOLEAN ,f DOUBLE:指定每一个展开字段,使用英文逗号(,)隔开。

      • 字段名:展开字段对应的JSON路径,使用半角句号(.)隔开。例如a.b.c

      • 字段类型:支持的数据类型为BOOLEAN、BYTE、SHORT、INTEGER、LONG、FLOAT、DOUBLE和VARCHAR。

    说明

    您可以通过WITH关键字指定多个lindorm_columnar.user.syncer.lci.jsonMapping,为多个JSON字段创建映射。

常见问题

  • Q:创建列存索引后,是否会产生额外费用?

    A:会。主要包括列存索引数据的存储费用,以及主表和列存索引之间数据同步实际使用的CU费用。

  • Q:分区表达式中是否可以包含非主键字段?

    A:不可以。分区表达式中的字段必须全部为主键字段。

  • Q:bucket分区表达式中,是否可以包含复杂分区表达式?

    A:不可以。bucket分区表达式中仅包括bucket_num和bucket分区字段。

  • Q:分区数目过大或过小会有什么影响?

    A:分区数目过大,会导致元数据膨胀,从而影响查询效率,因此建议单分区数据量大于50 MB,bucket分区表达式中的bucket_num小于1024。分区数目过小,会影响数据读写吞吐或造成数据倾斜,建议单分区数据量小于512 MB。

  • Q:是否可以通过Lindorm计算引擎直接访问列存索引数据?

    A:可以。您需要先自定义索引表的名称再通过计算引擎访问列存索引数据。具体操作,请参见访问Lindorm列存

    重要

    请谨慎执行列存索引表的修改操作,如需修改列存索引表,请联系Lindorm技术支持(钉钉号:s0s3eg3)。

  • Q:能否为同一个宽表创建多个列存索引?

    A:不能。一张宽表仅支持创建一个列存索引。

  • Q:若已经对部分宽表字段创建列存索引,能否新增更多字段?

    A:不能。

  • Q:宽表中数据因为TTL过期被清除后,列存索引数据是否会被自动清除?

    A:不会。

  • Q:列存索引创建失败了,再次创建为什么会报错?

  • A:一张宽表仅允许创建一个列存索引,无论该索引的状态是否为失败。您需要先删除构建失败的列存索引,再去创建新的索引。删除列存索引的语法,请参见DROP INDEX

  • 本页导读 (1)
文档反馈