MAP

name

SinceVersion 2.0.0

MAP

description

MAP<K, V>

由K, V类型元素组成的map，不能作为key列使用。目前支持在Duplicate，Unique 模型的表中使用。

K,V 支持的类型有：

1. BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, DECIMAL, DECIMALV3, DATE,
2. DATEV2, DATETIME, DATETIMEV2, CHAR, VARCHAR, STRING

example

建表示例如下：

1. CREATE TABLE IF NOT EXISTS test.simple_map (
2. `id` INT(11) NULL COMMENT "",
3. `m` Map<STRING, INT> NULL COMMENT ""
4. ) ENGINE=OLAP
5. DUPLICATE KEY(`id`)
6. DISTRIBUTED BY HASH(`id`) BUCKETS 1
7. PROPERTIES (
8. "replication_allocation" = "tag.location.default: 1",
9. "storage_format" = "V2"
10. );

插入数据示例：

1. mysql> INSERT INTO simple_map VALUES(1, {'a': 100, 'b': 200});

stream_load示例： 更多详细 stream_load 用法见 STREAM TABLE

1. # load the map data from json file
2. curl --location-trusted -uroot: -T events.json -H "format: json" -H "read_json_by_line: true" http://fe_host:8030/api/test/simple_map/_stream_load
3. # 返回结果
4. {
5. "TxnId": 106134,
6. "Label": "5666e573-9a97-4dfc-ae61-2d6b61fdffd2",
7. "Comment": "",
8. "TwoPhaseCommit": "false",
9. "Status": "Success",
10. "Message": "OK",
11. "NumberTotalRows": 10293125,
12. "NumberLoadedRows": 10293125,
13. "NumberFilteredRows": 0,
14. "NumberUnselectedRows": 0,
15. "LoadBytes": 2297411459,
16. "LoadTimeMs": 66870,
17. "BeginTxnTimeMs": 1,
18. "StreamLoadPutTimeMs": 80,
19. "ReadDataTimeMs": 6415,
20. "WriteDataTimeMs": 10550,
21. "CommitAndPublishTimeMs": 38
22. }

查询数据示例：

1. mysql> SELECT * FROM simple_map;
2. +------+-----------------------------+
3. | id   | m                           |
4. +------+-----------------------------+
5. |    1 | {'a':100, 'b':200}          |
6. |    2 | {'b':100, 'c':200, 'd':300} |
7. |    3 | {'a':10, 'd':200}           |
8. +------+-----------------------------+

查询 map 列示例：

1. mysql> SELECT m FROM simple_map;
2. +-----------------------------+
3. | m                           |
4. +-----------------------------+
5. | {'a':100, 'b':200}          |
6. | {'b':100, 'c':200, 'd':300} |
7. | {'a':10, 'd':200}           |
8. +-----------------------------+

map 取值示例：

1. mysql> SELECT m['a'] FROM simple_map;
2. +-----------------------------+
3. | %element_extract%(`m`, 'a') |
4. +-----------------------------+
5. |                         100 |
6. |                        NULL |
7. |                          10 |
8. +-----------------------------+

map 支持的functions示例：

1. # map construct

3. mysql> SELECT map('k11', 1000, 'k22', 2000)['k11'];
4. +---------------------------------------------------------+
5. | %element_extract%(map('k11', 1000, 'k22', 2000), 'k11') |
6. +---------------------------------------------------------+
7. |                                                    1000 |
8. +---------------------------------------------------------+

10. mysql> SELECT map('k11', 1000, 'k22', 2000)['nokey'];
11. +-----------------------------------------------------------+
12. | %element_extract%(map('k11', 1000, 'k22', 2000), 'nokey') |
13. +-----------------------------------------------------------+
14. |                                                      NULL |
15. +-----------------------------------------------------------+
16. 1 row in set (0.06 sec)

18. # map size

20. mysql> SELECT map_size(map('k11', 1000, 'k22', 2000));
21. +-----------------------------------------+
22. | map_size(map('k11', 1000, 'k22', 2000)) |
23. +-----------------------------------------+
24. |                                       2 |
25. +-----------------------------------------+

27. mysql> SELECT id, m, map_size(m) FROM simple_map ORDER BY id;
28. +------+-----------------------------+---------------+
29. | id   | m                           | map_size(`m`) |
30. +------+-----------------------------+---------------+
31. |    1 | {"a":100, "b":200}          |             2 |
32. |    2 | {"b":100, "c":200, "d":300} |             3 |
33. |    2 | {"a":10, "d":200}           |             2 |
34. +------+-----------------------------+---------------+
35. 3 rows in set (0.04 sec)

37. # map_contains_key

39. mysql> SELECT map_contains_key(map('k11', 1000, 'k22', 2000), 'k11');
40. +--------------------------------------------------------+
41. | map_contains_key(map('k11', 1000, 'k22', 2000), 'k11') |
42. +--------------------------------------------------------+
43. |                                                      1 |
44. +--------------------------------------------------------+
45. 1 row in set (0.08 sec)

47. mysql> SELECT id, m, map_contains_key(m, 'k1') FROM simple_map ORDER BY id;
48. +------+-----------------------------+-----------------------------+
49. | id   | m                           | map_contains_key(`m`, 'k1') |
50. +------+-----------------------------+-----------------------------+
51. |    1 | {"a":100, "b":200}          |                           0 |
52. |    2 | {"b":100, "c":200, "d":300} |                           0 |
53. |    2 | {"a":10, "d":200}           |                           0 |
54. +------+-----------------------------+-----------------------------+
55. 3 rows in set (0.10 sec)

57. mysql> SELECT id, m, map_contains_key(m, 'a') FROM simple_map ORDER BY id;
58. +------+-----------------------------+----------------------------+
59. | id   | m                           | map_contains_key(`m`, 'a') |
60. +------+-----------------------------+----------------------------+
61. |    1 | {"a":100, "b":200}          |                          1 |
62. |    2 | {"b":100, "c":200, "d":300} |                          0 |
63. |    2 | {"a":10, "d":200}           |                          1 |
64. +------+-----------------------------+----------------------------+
65. 3 rows in set (0.17 sec)

67. # map_contains_value

69. mysql> SELECT map_contains_value(map('k11', 1000, 'k22', 2000), NULL);
70. +---------------------------------------------------------+
71. | map_contains_value(map('k11', 1000, 'k22', 2000), NULL) |
72. +---------------------------------------------------------+
73. |                                                       0 |
74. +---------------------------------------------------------+
75. 1 row in set (0.04 sec)

77. mysql> SELECT id, m, map_contains_value(m, '100') FROM simple_map ORDER BY id;
78. +------+-----------------------------+------------------------------+
79. | id   | m                           | map_contains_value(`m`, 100) |
80. +------+-----------------------------+------------------------------+
81. |    1 | {"a":100, "b":200}          |                            1 |
82. |    2 | {"b":100, "c":200, "d":300} |                            1 |
83. |    2 | {"a":10, "d":200}           |                            0 |
84. +------+-----------------------------+------------------------------+
85. 3 rows in set (0.11 sec)

87. # map_keys

89. mysql> SELECT map_keys(map('k11', 1000, 'k22', 2000));
90. +-----------------------------------------+
91. | map_keys(map('k11', 1000, 'k22', 2000)) |
92. +-----------------------------------------+
93. | ["k11", "k22"]                          |
94. +-----------------------------------------+
95. 1 row in set (0.04 sec)

97. mysql> SELECT id, map_keys(m) FROM simple_map ORDER BY id;
98. +------+-----------------+
99. | id   | map_keys(`m`)   |
100. +------+-----------------+
101. |    1 | ["a", "b"]      |
102. |    2 | ["b", "c", "d"] |
103. |    2 | ["a", "d"]      |
104. +------+-----------------+
105. 3 rows in set (0.19 sec)

107. # map_values

109. mysql> SELECT map_values(map('k11', 1000, 'k22', 2000));
110. +-------------------------------------------+
111. | map_values(map('k11', 1000, 'k22', 2000)) |
112. +-------------------------------------------+
113. | [1000, 2000]                              |
114. +-------------------------------------------+
115. 1 row in set (0.03 sec)

117. mysql> SELECT id, map_values(m) FROM simple_map ORDER BY id;
118. +------+-----------------+
119. | id   | map_values(`m`) |
120. +------+-----------------+
121. |    1 | [100, 200]      |
122. |    2 | [100, 200, 300] |
123. |    2 | [10, 200]       |
124. +------+-----------------+
125. 3 rows in set (0.18 sec)

keywords

1. MAP