索引是加速数据查询的重要方法。列存索引可以增强宽表中海量数据的分析计算能力,主要适用于车联网与物联网的设备信息统计、电商领域的数据分析、物流行业的订单统计等场景。本文介绍列存索引的基础用法和高阶用法,帮助您快速上手并进一步掌握列存索引。
前提条件
已开通列存索引功能。
重要列存索引功能需联系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
表示该行数据产生的日期,通常会按照天级别进行数据分析。
创建列存索引。列存索引能自动为您展开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字段内容变化来动态扩展索引表。
查看索引状态。
SHOW INDEX FROM my_tbl;
SHOW INDEX
的使用方法及返回结果集说明,请参见SHOW INDEX。使用列存索引进行数据查询分析。具体操作,请参见使用列存索引。
基础用法
假设示例表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分区表达式中的字段均为宽表主键字段。
|
WITH(`key` = 'value') | 使用
|
示例
以表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字段到列存字段之间的映射关系。
创建包含JSON字段的表。
CREATE TABLE IF NOT EXISTS my_json_tbl(id BIGINT, col1 INTEGER, col2 DOUBLE, json_col JSON, PRIMARY KEY(id));
写入数据。
假设json_col的结构如下:
{ "a": { "b": { "c": "hello,world", "d": 123 }, "e": false }, "f": 3.14 }
创建列存索引时,基于
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)