动态表

动态表

SinceVersion 2.0.0

动态schema表是一种特殊的表，其schema随着导入自动进行扩展。目前该功能，主要用于半结构数据，例如JSON等的导入、自动列生成。因为JSON是类型自描述的，所以我们可以从原始文档中提取schema信息，推断最终类型信息。这种特殊的表可以减少人工schema change的操作，并轻松导入半结构数据并自动扩展其schema。

名词解释

schema change, 改变表的结构，例如增加列、减少列，修改列类型
静态列，在建表时指定的列，例如分区列、主键列
动态列，随着导入自动识别并增加的列

建表

1. CREATE DATABASE test_dynamic_table;

3. -- 建表， 并指定静态列类型， 导入遇到对应列会自动转换成静态列的类型
4. -- 选择随机分桶方式
5. CREATE TABLE IF NOT EXISTS test_dynamic_table (
6. qid bigint,
7. `answers.date` array<datetime>,
8. `title` string,
9. ...   -- ...标识该表是动态表， 是动态表的语法
10. )
11. DUPLICATE KEY(`qid`)
12. DISTRIBUTED BY RANDOM BUCKETS 5
13. properties("replication_num" = "1");

15. -- 可以看到三列Column在表中默认添加， 类型是指定类型
16. mysql> DESC test_dynamic_table;
17. +--------------+-----------------+------+-------+---------+-------+
18. | Field        | Type            | Null | Key   | Default | Extra |
19. +--------------+-----------------+------+-------+---------+-------+
20. | qid          | BIGINT          | Yes  | true  | NULL    |       |
21. | answers.date | ARRAY<DATETIME> | Yes  | false | NULL    | NONE  |
22. | user         | TEXT            | Yes  | false | NULL    | NONE  |
23. +--------------+-----------------+------+-------+---------+-------+
24. 3 rows in set (0.00 sec)

导入数据

1. -- example1.json
2. '{
3. "title": "Display Progress Bar at the Time of Processing",
4. "qid": "1000000",
5. "answers": [
6. {"date": "2009-06-16T09:55:57.320", "user": "Micha\u0142 Niklas (22595)"},
7. {"date": "2009-06-17T12:34:22.643", "user": "Jack Njiri (77153)"}
8. ],
9. "tag": ["vb6", "progress-bar"],
10. "user": "Jash",
11. "creationdate": "2009-06-16T07:28:42.770"
12. }'

14. curl -X PUT -T example1.json --location-trusted -u root: -H "read_json_by_line:false" -H "format:json"   http://127.0.0.1:8147/api/regression_test_dynamic_table/test_dynamic_table/_stream_load

16. -- 新增 title，answers.user， tag， title， creationdate 五列
17. -- 且 qid，answers.date，user三列类型与建表时保持一致
18. -- 新增数组类型默认Default值为空数组[]
19. mysql> DESC test_dynamic_table;
20. +--------------+-----------------+------+-------+---------+-------+
21. | Field        | Type            | Null | Key   | Default | Extra |
22. +--------------+-----------------+------+-------+---------+-------+
23. | qid          | BIGINT          | Yes  | true  | NULL    |       |
24. | answers.date | ARRAY<DATETIME> | Yes  | false | NULL    | NONE  |
25. | title        | TEXT            | Yes  | false | NULL    | NONE  |
26. | answers.user | ARRAY<TEXT>     | No   | false | []      | NONE  |
27. | tag          | ARRAY<TEXT>     | No   | false | []      | NONE  |
28. | user         | TEXT            | Yes  | false | NULL    | NONE  |
29. | creationdate | TEXT            | Yes  | false | NULL    | NONE  |
30. | date         | TEXT            | Yes  | false | NULL    | NONE  |
31. +--------------+-----------------+------+-------+---------+-------+

33. -- 批量导入数据

35. -- 指定 -H "read_json_by_line:true"， 逐行解析JSON
36. curl -X PUT -T example_batch.json --location-trusted -u root: -H "read_json_by_line:true" -H "format:json"   http://127.0.0.1:8147/api/regression_test_dynamic_table/test_dynamic_table/_stream_load

38. -- 指定 -H "strip_outer_array:true"， 整个文件当做一个JSON array解析， array中的每个元素是一行， 解析效率更高效
39. curl -X PUT -T example_batch_array.json --location-trusted -u root: -H "strip_outer_array:true" -H "format:json"   http://127.0.0.1:8147/api/regression_test_dynamic_table/test_dynamic_table/_stream_load

对于dynamic table，你也可以使用S3load或者Routine load, 使用方式类似

对动态列增加索引

1. -- 将在titile列上新建倒排索引， 并按照english分词
2. CREATE INDEX title_idx ON test_dynamic_table (`title`) using inverted PROPERTIES("parser"="english")

类型冲突

在第一批导入会自动推断出统一的类型，并以此作为最终的Column类型，所以建议保持Column类型的一致, 例如

1. {"id" : 123}
2. {"id" : "123"}
3. -- 类型会被最终推断为Text类型， 如果在后续导入{"id" : 123}则类型会被自动转成String类型

5. 对于无法统一的类型， 例如
6. {"id" : [123]}
7. {"id" : 123}
8. -- 导入将会报错