跳到主要内容
跳到主要内容

自增列

提示

自增列功能，从 Apache Doris 2.1 才开始支持，当前 2.0 版本不支持，这里只是给出功能和适用场景介绍，如果对此功能有需求，请升级到 Doris 2.1，并参考 2.1 相关文档。

在导入数据时，Doris 会为在自增列上没有指定值的数据行分配一个表内唯一的值。

功能说明​

对于含有自增列的表，用户在在导入数据时：

  • 如果导入的目标列中不包含自增列，则自增列将会被 Doris 自动生成的值填充。

  • 如果导入的目标列中包含自增列，则导入数据中该列中的 null 值将会被 Doris 自动生成的值替换，非 null 值则保持不变。需要注意非 null 值会破坏自增列值的唯一性。

唯一性​

Doris 保证了自增列上生成的值具有表内唯一性。但需要注意的是，自增列的唯一性仅保证由 Doris 自动填充的值具有唯一性，而不考虑由用户提供的值，如果用户同时对该表通过显示指定自增列的方式插入了用户提供的值，则不能保证这个唯一性。

聚集性​

Doris 保证自增列上自动生成的值是稠密的，但不能保证在一次导入中自动填充的自增列的值是完全连续的，因此可能会出现一次导入中自增列自动填充的值具有一定的跳跃性的现象。这是因为出于性能考虑，每个 BE 上都会缓存一部分预先分配的自增列的值，每个 BE 上缓存的值互不相交。此外，由于缓存的存在，Doris 不能保证在物理时间上后一次导入的数据在自增列上自动生成的值比前一次更大。因此，不能根据自增列分配出的值的大小来判断导入时间上的先后顺序。

使用示例​

1. 创建一个 Dupliciate 模型表，其中一个 Key 列是自增列

CREATE TABLE `demo`.`tbl` (
      `id` BIGINT NOT NULL AUTO_INCREMENT,
      `value` BIGINT NOT NULL
) ENGINE=OLAP
DUPLICATE KEY(`id`)
DISTRIBUTED BY HASH(`id`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 3"
);

2. 创建一个 Dupliciate 模型表，其中一个 Value 列是自增列

CREATE TABLE `demo`.`tbl` (
      `uid` BIGINT NOT NULL,
      `name` BIGINT NOT NULL,
      `id` BIGINT NOT NULL AUTO_INCREMENT,
      `value` BIGINT NOT NULL
) ENGINE=OLAP
DUPLICATE KEY(`uid`, `name`)
DISTRIBUTED BY HASH(`uid`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 3"
);

3. 创建一个 Unique 模型表，其中一个 Key 列是自增列

CREATE TABLE `demo`.`tbl` (
      `id` BIGINT NOT NULL AUTO_INCREMENT,
      `name` varchar(65533) NOT NULL,
      `value` int(11) NOT NULL
) ENGINE=OLAP
UNIQUE KEY(`id`)
DISTRIBUTED BY HASH(`id`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"enable_unique_key_merge_on_write" = "true"
);

4. 创建一个 Unique 模型表，其中一个 Value 列是自增列

CREATE TABLE `demo`.`tbl` (
      `text` varchar(65533) NOT NULL,
      `id` BIGINT NOT NULL AUTO_INCREMENT,
) ENGINE=OLAP
UNIQUE KEY(`text`)
DISTRIBUTED BY HASH(`text`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"enable_unique_key_merge_on_write" = "true"
);

使用场景​

字典编码​

在用户画像场景中使用 Bitmap 做人群分析时需要构建用户字典，每个用户对应一个唯一的整数字典值，聚集的字典值可以获得更好的 Bitmap 性能。

以离线 uv，pv 分析场景为例，假设有如下用户行为表存放明细数据：

CREATE TABLE `demo`.`dwd_dup_tbl` (
    `user_id` varchar(50) NOT NULL,
    `dim1` varchar(50) NOT NULL,
    `dim2` varchar(50) NOT NULL,
    `dim3` varchar(50) NOT NULL,
    `dim4` varchar(50) NOT NULL,
    `dim5` varchar(50) NOT NULL,
    `visit_time` DATE NOT NULL
) ENGINE=OLAP
DUPLICATE KEY(`user_id`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 32
PROPERTIES (
"replication_allocation" = "tag.location.default: 3"
);

利用自增列创建如下字典表

CREATE TABLE `demo`.`dictionary_tbl` (
    `user_id` varchar(50) NOT NULL,
    `aid` BIGINT NOT NULL AUTO_INCREMENT
) ENGINE=OLAP
UNIQUE KEY(`user_id`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 32
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"enable_unique_key_merge_on_write" = "true"
);

将存量数据中的 user_id 导入字典表，建立 user_id 到整数值的编码映射

insert into dit_tbl(user_id)
select user_id from dwd_dup_tbl group by user_id;

或者使用如下方式仅将增量数据中的 user_id 导入到字典表

insert into dit_tbl(user_id)
select dwd_dup_tbl.user_id from dwd_dup_tbl left join dictionary_tbl
on dwd_dup_tbl.user_id = dictionary_tbl.user_id where dwd_dup_tbl.visit_time  '2023-12-10' and dictionary_tbl.user_id is NULL;

实际场景中也可以使用 Flink Connector 把数据写入到 Doris。

假设 dim1, dim3, dim5 是我们关心的统计维度，建立如下聚合表存放聚合结果

CREATE TABLE `demo`.`dws_agg_tbl` (
    `dim1` varchar(50) NOT NULL,
    `dim3` varchar(50) NOT NULL,
    `dim5` varchar(50) NOT NULL,
    `user_id_bitmap` BITMAP BITMAP_UNION NOT NULL,
    `pv` BIGINT SUM NOT NULL 
) ENGINE=OLAP
AGGREGATE KEY(`dim1`,`dim3`,`dim5`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 32
PROPERTIES (
"replication_allocation" = "tag.location.default: 3"
);

将数据聚合运算后存放至聚合结果表

insert into dws_tbl
select dwd_dup_tbl.dim1, dwd_dup_tbl.dim3, dwd_dup_tbl.dim5, BITMAP_UNION(TO_BITMAP(dictionary_tbl.aid)), COUNT(1)
from dwd_dup_tbl INNER JOIN dictionary_tbl on dwd_dup_tbl.user_id = dictionary_tbl.user_id;

用如下语句进行 uv, pv 查询

select dim1, dim3, dim5, user_id_bitmap as uv, pv from dws_agg_tbl;

高效分页​

在页面展示数据时，往往需要做分页展示。传统的分页通常使用 SQL 中的 limit, offset + order by 进行查询。例如有如下业务表需要进行展示：

CREATE TABLE `demo`.`records_tbl` (
    `key` int(11) NOT NULL COMMENT "",
    `name` varchar(26) NOT NULL COMMENT "",
    `address` varchar(41) NOT NULL COMMENT "",
    `city` varchar(11) NOT NULL COMMENT "",
    `nation` varchar(16) NOT NULL COMMENT "",
    `region` varchar(13) NOT NULL COMMENT "",
    `phone` varchar(16) NOT NULL COMMENT "",
    `mktsegment` varchar(11) NOT NULL COMMENT ""
) DUPLICATE KEY (`key`, `name`)
DISTRIBUTED BY HASH(`key`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 3"
);

假设在分页展示中，每页展示 100 条数据。那么获取第 1 页的数据可以使用如下 sql 进行查询：

select * from records_tbl order by key, name limit 100;

获取第 2 页的数据可以使用如下 sql 进行查询：

select * from records_tbl order by key, name limit 100, offset 100;

然而，当进行深分页查询时 (offset 很大时)，即使实际需要需要的数据行很少，该方法依然会将全部数据读取到内存中进行全量排序后再进行后续处理，这种方法比较低效。可以通过自增列给每行数据一个唯一值，在查询时就可以通过记录之前页面unique_value列的最大值max_value，然后使用 where unique_value > max_value limit rows_per_page 的方式通过提下推谓词提前过滤大量数据，从而更高效地实现分页。

仍然以上述业务表为例，通过在表中添加一个自增列从而赋予每一行一个唯一标识：

CREATE TABLE `demo`.`records_tbl2` (
    `key` int(11) NOT NULL COMMENT "",
    `name` varchar(26) NOT NULL COMMENT "",
    `address` varchar(41) NOT NULL COMMENT "",
    `city` varchar(11) NOT NULL COMMENT "",
    `nation` varchar(16) NOT NULL COMMENT "",
    `region` varchar(13) NOT NULL COMMENT "",
    `phone` varchar(16) NOT NULL COMMENT "",
    `mktsegment` varchar(11) NOT NULL COMMENT "",
    `unique_value` BIGINT NOT NULL AUTO_INCREMENT
) DUPLICATE KEY (`key`, `name`)
DISTRIBUTED BY HASH(`key`) BUCKETS 10
PROPERTIES (
    "replication_num" = "3"
);

在分页展示中，每页展示 100 条数据，使用如下方式获取第一页的数据：

select * from records_tbl2 order by unique_value limit 100;

通过程序记录下返回结果中 unique_value 中的最大值，假设为 99，则可用如下方式查询第 2 页的数据：

select * from records_tbl2 where unique_value  99 order by unique_value limit 100;

如果要直接查询一个靠后页面的内容，此时不方便直接获取之前页面数据中unique_value的最大值时，例如要直接获取第 101 页的内容，则可以使用如下方式进行查询

select key, name, address, city, nation, region, phone, mktsegment
from records_tbl2, (select uniuqe_value as max_value from records_tbl2 order by uniuqe_value limit 1 offset 9999) as previous_data
where records_tbl2.uniuqe_value  previous_data.max_value
order by unique_value limit 100;
问答论坛本页导航
ASF
Resources
Community
Join the community
Copyright © 2024 The Apache Software Foundation,Licensed under the Apache License, Version 2.0. Apache, Doris, Apache Doris, the Apache feather logo and the Apache Doris logo are trademarks of The Apache Software Foundation.