MAP
Type Descriptionβ
- The
MAP<key_type, value_type>
type is used to represent a composite type of key-value pairs, where each key uniquely corresponds to a value.key_type
represents the type of the keys, supporting types such asBOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, DECIMAL, DATE, DATETIME, CHAR, VARCHAR, STRING, IPV4, IPV6
. Keys are nullable and cannot be specified as NOT NULL.value_type
represents the type of the values, supportingBOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, DECIMAL, DATE, DATETIME, CHAR, VARCHAR, STRING, IPV4, IPV6, ARRAY, MAP, STRUCT
. Values are nullable and cannot be specified as NOT NULL.
Type Constraintsβ
- The
MAP<key_type, value_type>
type allows a maximum nesting depth of 9. - In
MAP<key_type, value_type>
, keys can be NULL, and identical keys are allowed (NULL and NULL are considered the same key). - Conversion between
MAP<key_type, value_type>
types depends on whetherkey_type
andvalue_type
can be converted.MAP<key_type, value_type>
cannot be converted to other types.- For example:
MAP<INT, INT>
can be converted toMAP<BIGINT, BIGINT>
becauseINT
andBIGINT
can be converted. - String types can be converted to
MAP<key_type, value_type>
(through parsing, returning NULL if parsing fails).
- For example:
- In the
AGGREGATE
table model,MAP<key_type, value_type>
only supportsREPLACE
andREPLACE_IF_NOT_NULL
. In any table model, it cannot be used as a KEY column, nor as a partition or bucket column. - Columns of type
MAP<key_type, value_type>
do not support comparison or arithmetic operations, do not supportORDER BY
andGROUP BY
operations, cannot be used asJOIN KEY
, and cannot be used inDELETE
statements. - Columns of type
MAP<key_type, value_type>
do not support creating any indexes.
Type Constructionβ
-
The
MAP()
function can return a value of typeMAP
.SELECT MAP('Alice', 21, 'Bob', 23);
+-----------------------------+
| map('Alice', 21, 'Bob', 23) |
+-----------------------------+
| {"Alice":21, "Bob":23} |
+-----------------------------+ -
{}
can be used to construct a value of typeMAP
.SELECT {'Alice': 20};
+---------------+
| {'Alice': 20} |
+---------------+
| {"Alice":20} |
+---------------+
Modifying Typeβ
-
Modification is allowed only when
key_type
orvalue_type
ofMAP<key_type, value_type>
isVARCHAR
.-
Only allows changing the parameter of
VARCHAR
from smaller to larger, not the other way around.CREATE TABLE `map_table` (
`k` INT NOT NULL,
`map_varchar_int` MAP<VARCHAR(10), INT>,
`map_int_varchar` MAP<INT, VARCHAR(10)>,
`map_varchar_varchar` MAP<VARCHAR(10), VARCHAR(10)>
) ENGINE=OLAP
DUPLICATE KEY(`k`)
DISTRIBUTED BY HASH(`k`) BUCKETS 1
PROPERTIES (
"replication_num" = "1"
);
ALTER TABLE map_table MODIFY COLUMN map_varchar_int MAP<VARCHAR(20), INT>;
ALTER TABLE map_table MODIFY COLUMN map_int_varchar MAP<INT, VARCHAR(20)>;
ALTER TABLE map_table MODIFY COLUMN map_varchar_varchar MAP<VARCHAR(20), VARCHAR(20)>;
-
-
The default value for columns of type
MAP<key_type, value_type>
can only be specified as NULL, and once specified, it cannot be modified.
Element Accessβ
-
Use
[key]
to access the value corresponding to the key in theMAP
.SELECT {'Alice': 20}['Alice'];
+------------------------+
| {'Alice': 20}['Alice'] |
+------------------------+
| 20 |
+------------------------+ -
Use
ELEMENT_AT(MAP, Key)
to access the value corresponding to the key in theMAP
.SELECT ELEMENT_AT({'Alice': 20}, 'Alice');
+------------------------------------+
| ELEMENT_AT({'Alice': 20}, 'Alice') |
+------------------------------------+
| 20 |
+------------------------------------+
Examplesβ
-
Nested MAPs
-- Create table
CREATE TABLE IF NOT EXISTS map_table (
id INT,
map_nested MAP<STRING, MAP<STRING, INT>>
) ENGINE=OLAP
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);
-- Insert
INSERT INTO map_table VALUES (1, MAP('key1', MAP('key2', 1, 'key3', 2)));
INSERT INTO map_table VALUES (2, MAP('key1', MAP('key2', 3, 'key3', 4)));
-- Query
SELECT map_nested['key1']['key2'] FROM map_table ORDER BY id;
+----------------------------+
| map_nested['key1']['key2'] |
+----------------------------+
| 1 |
| 3 |
+----------------------------+ -
Nested Complex Types
-- Create table
CREATE TABLE IF NOT EXISTS map_table (
id INT,
map_array MAP<STRING, ARRAY<INT>>
) ENGINE=OLAP
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);
-- Insert
INSERT INTO map_table VALUES (1, MAP('key1', [1, 2, 3])), (2, MAP('key1', [4, 5, 6]));
-- Query
SELECT map_array['key1'][1] FROM map_table ORDER BY id;
+----------------------+
| map_array['key1'][1] |
+----------------------+
| 1 |
| 4 |
+----------------------+
-- Create table
CREATE TABLE IF NOT EXISTS map_table (
id INT,
map_struct MAP<STRING, STRUCT<id: INT, name: STRING>>
) ENGINE=OLAP
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);
-- Insert
INSERT INTO map_table VALUES (1, MAP('key1', STRUCT(1, 'John'), 'key2', STRUCT(3, 'Jane')));
-- Query
SELECT STRUCT_ELEMENT(map_struct['key1'], 1), STRUCT_ELEMENT(map_struct['key1'], 'name') FROM map_table ORDER BY id;
+---------------------------------------+--------------------------------------------+
| STRUCT_ELEMENT(map_struct['key1'], 1) | STRUCT_ELEMENT(map_struct['key1'], 'name') |
+---------------------------------------+--------------------------------------------+
| 1 | John |
+---------------------------------------+--------------------------------------------+ -
Modifying Type
-- Create table
CREATE TABLE `map_table` (
`k` INT NOT NULL,
`map_varchar_int` MAP<VARCHAR(10), INT>,
`map_int_varchar` MAP<INT, VARCHAR(10)>,
`map_varchar_varchar` MAP<VARCHAR(10), VARCHAR(10)>
) ENGINE=OLAP
DUPLICATE KEY(`k`)
DISTRIBUTED BY HASH(`k`) BUCKETS 1
PROPERTIES (
"replication_num" = "1"
);
-- Modify KEY
ALTER TABLE map_table MODIFY COLUMN map_varchar_int MAP<VARCHAR(20), INT>;
-- Modify VALUE
ALTER TABLE map_table MODIFY COLUMN map_int_varchar MAP<INT, VARCHAR(20)>;
-- Modify KEY and VALUE
ALTER TABLE map_table MODIFY COLUMN map_varchar_varchar MAP<VARCHAR(20), VARCHAR(20)>;
-- Check column types
DESC map_table;
+---------------------+------------------------------+------+-------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+------------------------------+------+-------+---------+-------+
| k | int | No | true | NULL | |
| map_varchar_int | map<varchar(20),int> | Yes | false | NULL | NONE |
| map_int_varchar | map<int,varchar(20)> | Yes | false | NULL | NONE |
| map_varchar_varchar | map<varchar(20),varchar(20)> | Yes | false | NULL | NONE |
+---------------------+------------------------------+------+-------+---------+-------+