MAP

name

SinceVersion 2.0.0

MAP

description

MAP<K, V>

A Map of K, V items, it cannot be used as a key column. Now MAP can only used in Duplicate and Unique Model Tables.

Need to manually enable the support, it is disabled by default.

1. admin set frontend config("enable_map_type" = "true");

K,V could be any of:

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

example

Create table 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. );

Insert data example:

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

stream_load examples： See STREAM TABLE for syntax details.

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. }

Select all data example:

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. +------+-----------------------------+

Select map column example:

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. +-----------------------------+

Select map value according given key example:

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

map functions examples:

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