如何访问Lindorm列存数据-阿里云帮助中心

本文介绍如何访问Lindorm列存数据。

背景信息

Lindorm列存是面向海量半结构化、结构化数据设计的列格式分布式存储服务，适用于车联网、物联网、订单、日志等大规模存储场景，核心能力包括：

计算分析
Lindorm计算引擎可以访问列存数据，完成海量数据的交互式分析和离线计算。列存提供丰富的索引能力和数据分布特征，可以有效加速计算过程中的数据定位与排布，通过SQL即可完成海量主键数据的增删改查。
高吞吐
列存引擎吞吐能力支持水平扩展，提供每分钟TB级数据的读写能力。适用于车联网数据快速导入、模型训练数据集存取和大规模报表分析生产等高吞吐数据场景。
低成本
通过列格式高压缩比算法、高密度低成本介质、冷热分离、多压缩编码和数据冷归档等技术，Lindorm列存相比自建系统存储成本显著降低，满足海量数据归档留存等低成本存储需求。
高可用
通过纠删码等技术，Lindorm列存保证了分布式数据集的高可用性，同时保证了数据访问无单点。

注意事项

访问Lindorm列存数据功能目前处于邀测阶段，如有使用需求，请联系Lindorm技术支持（钉钉号：s0s3eg3）。

前提条件

已阅读使用须知。
根据不同的作业形态，请确保已经完成了以下操作：
- JDBC开发实践：JDBC开发实践。
- JAR作业开发实践：JAR作业开发实践。
- Python作业开发实践：Python作业开发实践。

功能说明

DDL

命名空间

创建Namespace（Database）

USE lindorm_columnar;
CREATE NAMESPACE mydb;

删除Namespace（Database）

USE lindorm_columnar;
DROP NAMESPACE mydb;

警告 DROP NAMESPACE操作会将指定Namespace及该Namespace下的表一起删除，因此在执行DROP NAMESPACE操作前，请确保Namespace下的数据已有备份，避免数据丢失。

表

创建表

USE lindorm_columnar;
CREATE TABLE mydb.mytable (
  id INT,
  name STRING,
  score INT,
  city STRING)
PARTITIONED BY (city, bucket(128,id))
TBLPROPERTIES(
  'lce.primaryKey' = 'id,city');

参数说明

主键字段

列存表支持的字段类型：BOOLEAN、 BYTE、SHORT、 INT、 LONG、 FLOAT、DOUBLE、 STRING和BINARY。创建表时您可以指定任意数据字段。

主键

您必须创建表时通过设置TBLPROPERTIES中的lce.primaryKey参数的值，指定表的主键字段。

主键遵循以下规则：

多个主键字段需用英文逗号分隔。
列存表主键具备唯一性。
当相同主键数据多次写入，新数据将覆盖旧数据。

数据分区方式

您可以在创建表时通过PARTITIONED BY([普通分区表达式],{bucket(bucketNum,bucketCol(,...))}指定数据分区方式。

bucket分区表达式
- bucketNum为分片数量，直接影响数据写入和扫描的并发度。
  说明不同的bucket分区有不同的分区号（bucket_index）。bucketNum决定了一个普通分区下的bucket分区数量。
  - bucket分区号的计算方式是基于分区字段求Hash值，然后对bucketNum取余得出。以示例表mydb.mytable中的数据为例，bucket_index=hash(id)%128。
  - 对于每个不同的bucket_index，底层存储将进行物理划分。建议您在创建表前评估数据总量，并合理设置bucketNum，保证单bucket分区的数据量在50M~512M之间。
- bucketColN（bucketCol(,...)）为具体的bucket分区字段。
  重要
  bucket分区字段需遵循以下规则：
  - bucket分区字段必须来自主键字段。
  - 为避免数据倾斜，需确保bucket分区字段具有足够的离散特征。
示例
创建表时仅指定bucket分区。
- 示例一：
```
USE lindorm_columnar;
CREATE TABLE mydb.mytable (
  id0 INT, id1 STRING, name STRING, score DOUBLE)
PARTITIONED BY (bucket(1024,id1))
TBLPROPERTIES(
  'lce.primaryKey' = 'id0, id1')
```
- 示例二：
```
USE lindorm_columnar;
CREATE TABLE mydb.mytable (
  id INT, timestamp LONG, name STRING, sore DOUBLE)
PARTITIONED BY (bucket(512,timestamp))
TBLPROPERTIES(
  'lce.primaryKey' = 'id,timestamp')
```
普通分区表达式
对于普通分区表达式每个不同的值，底层存储将进行物理划分，保证数据扫描的裁剪能力。
重要
- 不同普通分区表达式中的字段必须来自主键字段。
- 请您确保普通分区表达式的取值相对集中，常见普通分区字段包括：日期、城市、性别等等。如果您的普通分区表达式取值过于离散，例如时间戳，将导致列存元数据压力过大。
示例
创建表时同时指定普通分区和bucket分区。
- 示例一：
```
USE lindorm_columnar;
CREATE TABLE mydb.mytable (
  id INT, year STRING, month STRING, day STRING, name STRING, score DOUBLE)
PARTITIONED BY (year, month, day, bucket(1024,id))
TBLPROPERTIES(
  'lce.primaryKey' = 'id, year, month, day')
```
- 示例二：
```
USE lindorm_columnar;
CREATE TABLE mydb.mytable (
  id INT, date STRING, city STRING, name STRING, score DOUBLE)
PARTITIONED BY (date, city, bucket(1024,id))
TBLPROPERTIES(
  'lce.primaryKey' = 'id,date,city')
```

查看当前Namespace下的表

USE lindorm_columnar;
USE mydb;
SHOW TABLES;

查看已存在的表

您可以执行以下SQL语句查看表结构。

USE lindorm_columnar;
SHOW CREATE TABLE mydb.mytable;
DESC mydb.mytable;

删除指定表

USE lindorm_columnar;
DROP TABLE mydb.mytable;

删除表中内容，但保留表的结构

USE lindorm_columnar;
TRUNCATE TABLE mydb.mytable;

DML

表

在表中插入数据

示例一：

USE lindorm_columnar;
INSERT INTO mydb.mytable VALUES (0, 'zhang3', 99, 'beijing');

示例二：

USE lindorm_columnar;
INSERT INTO mydb.mytable SELECT id, name, score, city FROM another_table;

查询表中的数据

示例一：

USE lindorm_columnar;
SELECT * from mydb.mytable where id=0;

示例二：

USE lindorm_columnar;
SELECT count(1), sum(score) from mydb.mytable where city = 'beijing';

分区

删除分区

USE lindorm_columnar;
ALTER TABLE mydb.mytable DROP PARTITION (city = 'beijing', _bucketIndex = '*')

重要删除分区时需指定全部的普通分区字段，并设置_bucketIndex="*", 即删除某一个普通分区下面的全部bucket分区。

分区整理

在列存分区写入数据，经过一段时间后，您可以执行COMPACT命令，整理分区数据，减少数据冗余，提升数据查询性能。

示例一：

USE lindorm_columnar;
COMPACT mydb.mytable PARTITION (city = 'beijing') FORCE MAJOR

示例二：

USE lindorm_columnar;
COMPACT mydb.mytable FORCE MAJOR

最佳实践

您可以通过以下方案，加速数据查询或计算。

主键数据查询

如果表中存储了海量数据集，查询时可以指定通过主键过滤条件，实现加速效果。查询时，主键的数据范围设置得越小，加速效果越好。

假设表结构如下：

USE lindorm_columnar;
CREATE TABLE orders
(
o_orderkey       int,
o_custkey        int,
o_orderstatus    string,
o_totalprice     double ,
o_orderdate      string ,
o_orderpriority  string,
o_clerk          string,
o_shippriority   int,
o_comment        string
)
PARTITIONED BY (bucket(1024,o_orderkey,o_custkey))
TBLPROPERTIES(
'lce.primaryKey' = 'o_orderkey,o_custkey'
);

示例一：

USE lindorm_columnar;
SELECT * FROM orders WHERE o_orderkey=18394 AND o_custkey=81772;

示例二：

USE lindorm_columnar;
SELECT count(*) FROM orders WHERE o_orderkey>100000 AND o_orderkey<200000;

示例三：

USE lindorm_columnar;
SELECT count(*) FROM orders WHERE o_orderkey>100000 AND o_custkey<99;

示例四：

USE lindorm_columnar;
SELECT count(*) FROM orders WHERE o_orderkey>100000;

添加分区过滤

Lindorm列存引擎中不同分区之间彼此物理隔离，因此，通过添加分区过滤条件，可以加速数据查询。

假设表结构如下：

USE lindorm_columnar;
CREATE TABLE orders
(
o_orderkey       int,
o_custkey        int,
o_orderstatus    string,
o_totalprice     double ,
o_orderdate      string ,
o_orderpriority  string,
o_clerk          string,
o_shippriority   int,
o_comment        string
)
PARTITIONED BY (o_orderdate, bucket(1024,o_orderkey,o_custkey))
TBLPROPERTIES(
'lce.primaryKey' = 'o_orderdate,o_orderkey,o_custkey'
);

示例一：

USE lindorm_columnar;
SELECT o_orderdate, count(*) FROM orders WHERE o_orderdate='2022-01-01' GROUP BY o_orderdate;

示例二：

USE lindorm_columnar;
SELECT o_orderdate, count(*) FROM orders WHERE o_orderdate>='2022-01-01' AND o_orderdate<='2022-01-07' GROUP BY o_orderdate;

非主键条件查询

针对分区整理过程，可以在建表时配置参数lce.compact.major.splitKey，指定二级排序键。在主键的基础上，基于其他若干列进行二次排序，从而加速非主键条件查询。

假设表结构如下：

USE lindorm_columnar;
CREATE TABLE orders
(
o_orderkey       int,
o_custkey        int,
o_orderstatus    string,
o_totalprice     double ,
o_orderdate      string ,
o_orderpriority  string,
o_clerk          string,
o_shippriority   int,
o_comment        string
)
PARTITIONED BY (bucket(1024,o_orderkey,o_custkey))
TBLPROPERTIES(
'lce.primaryKey' = 'o_orderkey,o_custkey',
'lce.compact.major.splitKey' = 'o_shippriority,o_totalprice'
);

执行以下语句整理分区：

COMPACT orders FORCE MAJOR;

您可以使用以下SQL语句查询已完成分区整理的表中的数据。

示例一：

USE lindorm_columnar;
SELECT count(*) FROM orders WHERE o_shippriority=0;

示例二：

USE lindorm_columnar;
SELECT count(*) FROM orders WHERE o_shippriority=0 AND o_totalprice>999.9;

数据关联

列存表中的bucket分区方式影响了数据分布特征，对于两个海量数据集，您可以基于关联条件定义相同的bucket分区方式，从而加速关联计算过程。

假设表结构如下：

USE lindorm_columnar;
# 表orders0结构：
CREATE TABLE orders0
(
o_orderkey       int,
o_custkey        int,
o_orderstatus    string,
o_totalprice     double ,
o_orderdate      string ,
o_orderpriority  string,
)
PARTITIONED BY (o_orderdate, bucket(1024,o_orderkey))
TBLPROPERTIES(
'lce.primaryKey' = 'o_orderdate,o_orderkey'
);

# 表orders1结构：
CREATE TABLE orders1
(
o_orderkey       int,
o_custkey        int,
o_orderdate      string ,
o_clerk          string,
o_shippriority   int,
o_comment        string
)
PARTITIONED BY (o_orderdate, bucket(1024,o_orderkey))
TBLPROPERTIES(
'lce.primaryKey' = 'o_orderdate,o_orderkey'
);

表orders0和表orders1中，每天（o_orderdate）的bucket分区方式都相同：bucket(1024,o_orderkey)。因此，将两张表单天的数据按照bucket分区字段（o_orderkey）关联，可以得到更好的加速效果。

关联查询示例如下：

USE lindorm_columnar;
SELECT * FROM orders0 a
JOIN
 orders1 b
ON a.o_orderkey=b.o_orderkey AND a.o_orderdate=b.o_orderdate
WHERE o_orderdate='2022-01-01';

查询加速

对指定表或者表中的指定分区进行数据整理（Compaction），可以增强数据的有序性或紧凑性，从而提升数据扫描性能。

假设表结构如下：

CREATE TABLE hello_tbl
(id int, city string, name string, score int)
partitioned by (city, hash(4, id))
tblproperties('lce.primaryKey' = 'id,city');

示例一：对hello_tbl全表进行数据整理。

COMPACT hello_tbl FORCE MAJOR;

示例二：对指定分区进行数据整理。

COMPACT hello_tbl PARTITION (city='beijing') FORCE MAJOR;

完成数据整理后，如果想要进一步提升后续查询的效率，可以执行以下语句设置表的相关参数来加速后续查询：

示例一：

ALTER TABLE hello_tbl SET TBLPROPERTIES ('lce.scan.majorCompactFilesOnly' = true);

参数说明

lce.scan.majorCompactFilesOnly：指定数据查询范围。数据类型为BOOLEAN。取值如下：

true：只查询已完成数据整理的数据，忽略增量写入且未完成数据整理的数据。
false：默认值。查询所有数据。

示例二：

ALTER TABLE hello_tbl SET TBLPROPERTIES ('lce.scan.enableVectorizedReader' = true);

参数说明

lce.scan.enableVectorizedReader：是否以向量化方式加速数据查询。数据类型为BOOLEAN。取值如下：

true：以向量化方式加速数据查询。仅在lce.scan.majorCompactFilesOnly配置为true时生效。
false：默认值。不以向量化方式加速数据查询。