动态表

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. -- 导入将会报错