Dynamic Table

SinceVersion 2.0.0

A dynamic schema table is a special kind of table which schema expands automatically with the import procedure. Currently, this feature is mainly used for importing semi-structured data such as JSON. Because JSON is self-describing, we can extract the schema information from the original document and infer the final type information. This special table can reduce manual schema change operations and easily import semi-structured data and automatically expand its schema.

Terminology

  • Schema change, changing the structure of the table, such as adding columns, reducing columns, changing column types
  • Static column, column specified during table creation, such as partition columns, primary key columns
  • Dynamic column, columns automatically recognized and added during import

Create dynamic table

  1. CREATE DATABASE test_dynamic_table;
  3. -- Create table and specify static column types, import will automatically convert to the type of static column
  4. -- Choose random bucketing
  5. CREATE TABLE IF NOT EXISTS test_dynamic_table (
  6.                 qid bigint,
  7.                 `answers.date` array<datetime>,
  8.                 `title` string,
  9.                 ...   -- ... Identifying a table as a dynamic table and its syntax for dynamic tables.
  10.         )
  11. DUPLICATE KEY(`qid`)
  12. DISTRIBUTED BY RANDOM BUCKETS 5 
  13. properties("replication_num" = "1");
  15. -- Three Columns are added to the table by default, and their types are specified
  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)

Importing data

  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. -- Added five new columns: `title`, `answers.user`, `tag`, `title`, `creationdate`
  17. -- The types of the three columns: `qid`, `answers.date`, `user` remain the same as with the table was created
  18. -- The default value of the new array type is an empty array []
  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. -- Batch import data
  34. -- Specifying -H "read_json_by_line:true", parsing JSON line by line
  35. 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
  37. -- Specifying -H "strip_outer_array:true", parsing the entire file as a JSON array, each element in the array is the same, more efficient parsing way
  38. 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

For a dynamic table, you can also use S3load or Routine load, with similar usage.

Adding Index to Dynamic Columns

  1. -- Create an inverted index on the title column, using English parsing.
  2. CREATE INDEX title_idx ON test_dynamic_table (`title`) using inverted PROPERTIES("parser"="english")

Type conflict resolution

In the first batch import, the unified type will be automatically inferred and used as the final Column type, so it is recommended to keep the Column type consistent, for example:

  1. {"id" : 123}
  2. {"id" : "123"}
  3. -- The type will finally be inferred as Text type, and if {"id" : 123} is imported later, the type will automatically be converted to String type
  5. For types that cannot be unified, such as:
  6. {"id" : [123]}
  7. {"id" : 123}
  8. -- Importing will result in an error."