自增列

SinceVersion 2.1

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

功能说明

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

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

唯一性

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

聚集性

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

语法

要使用自增列，需要在建表CREATE-TABLE时为对应的列添加AUTO_INCREMENT属性。 若要手动指定自增列起始值，可以通过建表时AUTO_INCREMENT(start_value)语句指定，如果未指定，则默认起始值为1。

示例

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

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

  2. 创建一个Dupliciate模型表，其中一个key列是自增列，并设置起始值为100

    1. CREATE TABLE `demo`.`tbl` (
    2.       `id` BIGINT NOT NULL AUTO_INCREMENT(100),
    3.       `value` BIGINT NOT NULL
    4. ) ENGINE=OLAP
    5. DUPLICATE KEY(`id`)
    6. DISTRIBUTED BY HASH(`id`) BUCKETS 10
    7. PROPERTIES (
    8. "replication_allocation" = "tag.location.default: 3"
    9. );

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

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

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

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

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

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

约束和限制

  1. 仅Duplicate模型表和Unique模型表可以包含自增列。
  2. 一张表最多只能包含一个自增列。
  3. 自增列的类型必须是BIGINT类型，且必须为NOT NULL。
  4. 自增列手动指定的起始值必须大于等于0。

使用方式

普通导入

以下表为例：

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

使用insert into语句导入并且不指定自增列id时，id列会被自动填充生成的值。

  1. mysql> insert into tbl(name, value) values("Bob", 10), ("Alice", 20), ("Jack", 30);
  2. Query OK, 3 rows affected (0.09 sec)
  3. {'label':'label_183babcb84ad4023_a2d6266ab73fb5aa', 'status':'VISIBLE', 'txnId':'7'}
  5. mysql> select * from tbl order by id;
  6. +------+-------+-------+
  7. | id   | name  | value |
  8. +------+-------+-------+
  9. |    1 | Bob   |    10 |
  10. |    2 | Alice |    20 |
  11. |    3 | Jack  |    30 |
  12. +------+-------+-------+
  13. 3 rows in set (0.05 sec)

类似地，使用stream load导入文件test.csv且不指定自增列id，id列会被自动填充生成的值。

test.csv:

  1. Tom, 40
  2. John, 50
  1. curl --location-trusted -u user:passwd -H "columns:name,value" -H "column_separator:," -T ./test1.csv http://{host}:{port}/api/{db}/tbl/_stream_load
  1. mysql> select * from tbl order by id;
  2. +------+-------+-------+
  3. | id   | name  | value |
  4. +------+-------+-------+
  5. |    1 | Bob   |    10 |
  6. |    2 | Alice |    20 |
  7. |    3 | Jack  |    30 |
  8. |    4 | Tom   |    40 |
  9. |    5 | John  |    50 |
  10. +------+-------+-------+
  11. 5 rows in set (0.04 sec)

使用insert into导入时指定自增列id，则该列数据中的null值会被生成的值替换。

  1. mysql> insert into tbl(id, name, value) values(null, "Doris", 60), (null, "Nereids", 70);
  2. Query OK, 2 rows affected (0.07 sec)
  3. {'label':'label_9cb0c01db1a0402c_a2b8b44c11ce4703', 'status':'VISIBLE', 'txnId':'10'}
  5. mysql> select * from tbl order by id;
  6. +------+---------+-------+
  7. | id   | name    | value |
  8. +------+---------+-------+
  9. |    1 | Bob     |    10 |
  10. |    2 | Alice   |    20 |
  11. |    3 | Jack    |    30 |
  12. |    4 | Tom     |    40 |
  13. |    5 | John    |    50 |
  14. |    6 | Doris   |    60 |
  15. |    7 | Nereids |    70 |
  16. +------+---------+-------+
  17. 7 rows in set (0.04 sec)

部分列更新

在对一张包含自增列的merge-on-write Unique表进行部分列更新时，如果自增列是key列，由于部分列更新时用户必须显示指定key列，部分列更新的目标列必须包含自增列。此时的导入行为和普通的部分列更新相同。

  1. mysql> CREATE TABLE `demo`.`tbl2` (
  2.     ->     `id` BIGINT NOT NULL AUTO_INCREMENT,
  3.     ->     `name` varchar(65533) NOT NULL,
  4.     ->     `value` int(11) NOT NULL DEFAULT "0"
  5.     -> ) ENGINE=OLAP
  6.     -> UNIQUE KEY(`id`)
  7.     -> DISTRIBUTED BY HASH(`id`) BUCKETS 10
  8.     -> PROPERTIES (
  9.     -> "replication_allocation" = "tag.location.default: 3",
  10.     -> "enable_unique_key_merge_on_write" = "true"
  11.     -> );
  12. Query OK, 0 rows affected (0.03 sec)
  14. mysql> insert into tbl2(id, name, value) values(1, "Bob", 10), (2, "Alice", 20), (3, "Jack", 30);
  15. Query OK, 3 rows affected (0.14 sec)
  16. {'label':'label_5538549c866240b6_bce75ef323ac22a0', 'status':'VISIBLE', 'txnId':'1004'}
  18. mysql> select * from tbl2 order by id;
  19. +------+-------+-------+
  20. | id   | name  | value |
  21. +------+-------+-------+
  22. |    1 | Bob   |    10 |
  23. |    2 | Alice |    20 |
  24. |    3 | Jack  |    30 |
  25. +------+-------+-------+
  26. 3 rows in set (0.08 sec)
  28. mysql> set enable_unique_key_partial_update=true;
  29. Query OK, 0 rows affected (0.01 sec)
  31. mysql> set enable_insert_strict=false;
  32. Query OK, 0 rows affected (0.00 sec)
  34. mysql> insert into tbl2(id, name) values(1, "modified"), (4, "added");
  35. Query OK, 2 rows affected (0.06 sec)
  36. {'label':'label_3e68324cfd87457d_a6166cc0a878cfdc', 'status':'VISIBLE', 'txnId':'1005'}
  38. mysql> select * from tbl2 order by id;
  39. +------+----------+-------+
  40. | id   | name     | value |
  41. +------+----------+-------+
  42. |    1 | modified |    10 |
  43. |    2 | Alice    |    20 |
  44. |    3 | Jack     |    30 |
  45. |    4 | added    |     0 |
  46. +------+----------+-------+
  47. 4 rows in set (0.04 sec)

当自增列是非key列时，如果用户没有指定自增列的值，其值会从表中原有的数据行中进行补齐。如果用户指定了自增列，则该列数据中的null值会被替换为生成出的值，非null值则保持不表，然后以部分列更新的语义插入该表。

  1. mysql> CREATE TABLE `demo`.`tbl3` (
  2.     ->     `id` BIGINT NOT NULL,
  3.     ->     `name` varchar(100) NOT NULL,
  4.     ->     `score` BIGINT NOT NULL,
  5.     ->     `aid` BIGINT NOT NULL AUTO_INCREMENT
  6.     -> ) ENGINE=OLAP
  7.     -> UNIQUE KEY(`id`)
  8.     -> DISTRIBUTED BY HASH(`id`) BUCKETS 1
  9.     -> PROPERTIES (
  10.     -> "replication_allocation" = "tag.location.default: 3",
  11.     -> "enable_unique_key_merge_on_write" = "true"
  12.     -> );
  13. Query OK, 0 rows affected (0.16 sec)
  15. mysql> insert into tbl3(id, name, score) values(1, "Doris", 100), (2, "Nereids", 200), (3, "Bob", 300);
  16. Query OK, 3 rows affected (0.28 sec)
  17. {'label':'label_c52b2c246e244dda_9b91ee5e27a31f9b', 'status':'VISIBLE', 'txnId':'2003'}
  19. mysql> select * from tbl3 order by id;
  20. +------+---------+-------+------+
  21. | id   | name    | score | aid  |
  22. +------+---------+-------+------+
  23. |    1 | Doris   |   100 |    0 |
  24. |    2 | Nereids |   200 |    1 |
  25. |    3 | Bob     |   300 |    2 |
  26. +------+---------+-------+------+
  27. 3 rows in set (0.13 sec)
  29. mysql> set enable_unique_key_partial_update=true;
  30. Query OK, 0 rows affected (0.00 sec)
  32. mysql> set enable_insert_strict=false;
  33. Query OK, 0 rows affected (0.00 sec)
  35. mysql> insert into tbl3(id, score) values(1, 999), (2, 888);
  36. Query OK, 2 rows affected (0.07 sec)
  37. {'label':'label_dfec927d7a4343ca_9f9ade581391de97', 'status':'VISIBLE', 'txnId':'2004'}
  39. mysql> select * from tbl3 order by id;
  40. +------+---------+-------+------+
  41. | id   | name    | score | aid  |
  42. +------+---------+-------+------+
  43. |    1 | Doris   |   999 |    0 |
  44. |    2 | Nereids |   888 |    1 |
  45. |    3 | Bob     |   300 |    2 |
  46. +------+---------+-------+------+
  47. 3 rows in set (0.06 sec)
  49. mysql> insert into tbl3(id, aid) values(1, 1000), (3, 500);
  50. Query OK, 2 rows affected (0.07 sec)
  51. {'label':'label_b26012959f714f60_abe23c87a06aa0bf', 'status':'VISIBLE', 'txnId':'2005'}
  53. mysql> select * from tbl3 order by id;
  54. +------+---------+-------+------+
  55. | id   | name    | score | aid  |
  56. +------+---------+-------+------+
  57. |    1 | Doris   |   999 | 1000 |
  58. |    2 | Nereids |   888 |    1 |
  59. |    3 | Bob     |   300 |  500 |
  60. +------+---------+-------+------+
  61. 3 rows in set (0.06 sec)

使用场景

字典编码

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

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

  1. CREATE TABLE `demo`.`dwd_dup_tbl` (
  2.     `user_id` varchar(50) NOT NULL,
  3.     `dim1` varchar(50) NOT NULL,
  4.     `dim2` varchar(50) NOT NULL,
  5.     `dim3` varchar(50) NOT NULL,
  6.     `dim4` varchar(50) NOT NULL,
  7.     `dim5` varchar(50) NOT NULL,
  8.     `visit_time` DATE NOT NULL
  9. ) ENGINE=OLAP
  10. DUPLICATE KEY(`user_id`)
  11. DISTRIBUTED BY HASH(`user_id`) BUCKETS 32
  12. PROPERTIES (
  13. "replication_allocation" = "tag.location.default: 3"
  14. );

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

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

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

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

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

  1. insert into dit_tbl(user_id)
  2. select dwd_dup_tbl.user_id from dwd_dup_tbl left join dictionary_tbl
  3. 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是我们关心的统计维度，建立如下聚合表存放聚合结果

  1. CREATE TABLE `demo`.`dws_agg_tbl` (
  2.     `dim1` varchar(50) NOT NULL,
  3.     `dim3` varchar(50) NOT NULL,
  4.     `dim5` varchar(50) NOT NULL,
  5.     `user_id_bitmap` BITMAP BITMAP_UNION NOT NULL,
  6.     `pv` BIGINT SUM NOT NULL 
  7. ) ENGINE=OLAP
  8. AGGREGATE KEY(`dim1`,`dim3`,`dim5`)
  9. DISTRIBUTED BY HASH(`user_id`) BUCKETS 32
  10. PROPERTIES (
  11. "replication_allocation" = "tag.location.default: 3"
  12. );

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

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

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

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

高效分页

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

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

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

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

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

  1. 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 的方式通过提下推谓词提前过滤大量数据，从而更高效地实现分页。

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

  1. CREATE TABLE `demo`.`records_tbl2` (
  2.     `key` int(11) NOT NULL COMMENT "",
  3.     `name` varchar(26) NOT NULL COMMENT "",
  4.     `address` varchar(41) NOT NULL COMMENT "",
  5.     `city` varchar(11) NOT NULL COMMENT "",
  6.     `nation` varchar(16) NOT NULL COMMENT "",
  7.     `region` varchar(13) NOT NULL COMMENT "",
  8.     `phone` varchar(16) NOT NULL COMMENT "",
  9.     `mktsegment` varchar(11) NOT NULL COMMENT "",
  10.     `unique_value` BIGINT NOT NULL AUTO_INCREMENT
  11. ) DUPLICATE KEY (`key`, `name`)
  12. DISTRIBUTED BY HASH(`key`) BUCKETS 10
  13. PROPERTIES (
  14.     "replication_num" = "3"
  15. );

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

  1. select * from records_tbl2 order by unique_value limit 100;

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

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

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

  1. select key, name, address, city, nation, region, phone, mktsegment
  2. from records_tbl2, (select uniuqe_value as max_value from records_tbl2 order by uniuqe_value limit 1 offset 9999) as previous_data
  3. where records_tbl2.uniuqe_value > previous_data.max_value
  4. order by unique_value limit 100;