MAP
类型描述
MAP<key_type, value_type>类型用于表示键值对集合的复合类型,每个键(key)唯一地对应一个值(value)。key_type表征键的类型,支持的类型为BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, DECIMAL, DATE, DATETIME, TIMESTAMP_NS, TIMESTAMPTZ, CHAR, VARCHAR, STRING,IPTV4, IPV6,UUID,key值是Nullable的,不支持指定NOT NULL。
value_type表征值的类型,支持BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, DECIMAL, DATE, DATETIME, TIMESTAMP_NS, TIMESTAMPTZ, CHAR, VARCHAR, STRING,IPV4, IPV6,UUID, ARRAY, MAP, STRUCT,值是 Nullable 的,不支持指定 NOT NULL。
语法
MAP<K, V>
类型约束
MAP<key_type, value_type>类型允许的最大嵌套深度是9。MAP<key_type, value_type>的Key可以是 NULL,并且允许相同的Key(NULL和NULL也被认为是相同的Key)。MAP<key_type, value_type>类型之间的转换取决于key_type之间以及value_type之间是否能转换,MAP<key_type, value_type>类型不能转成其他类型。- 例如:
MAP<INT,INT>可以转换为MAP<BIGINT,BIGINT>,因为INT和BIGINT可以转换。 - 字符串类型可以转换成
MAP<key_type, value_type>类型(通过解析的形式,解析失败返回 NULL)。
- 例如:
MAP<key_type, value_type>类型在AGGREGATE表模型中只支持REPLACE和REPLACE_IF_NOT_NULL,在任何表模型中都无法作为Key列,无法作为分区分桶列。MAP<key_type, value_type>类型的列不支持比较或者算数运算,包括IN和NOT IN,不支持ORDER BY和GROUP BY操作,不支持作为JOIN KEY,不支持在DELETE语句中使用。MAP<key_type, value_type>类型的列不支持建立任何索引。
类型构造
-
MAP()函数可以返回一个MAP类型的值。SELECT MAP('Alice', 21, 'Bob', 23); +-----------------------------+ | map('Alice', 21, 'Bob', 23) | +-----------------------------+ | {"Alice":21, "Bob":23} | +-----------------------------+ -
{}可以构造一个MAP类型的值。SELECT {'Alice': 20}; +---------------+ | {'Alice': 20} | +---------------+ | {"Alice":20} | +---------------+
修改类型
-
当
MAP<key_type, value_type>的key_type或value_type为VARCHAR时,才允许进行修改。- 只允许将
VARCHAR的参数从小改到大。反之不行。
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)>; - 只允许将
-
MAP<key_type, value_type>类型的列默认值只能指定为NULL,如果指定后不能修改。
元素访问
-
使用
[key]的方式访问MAP的Key对应的Value。SELECT {'Alice': 20}['Alice']; +------------------------+ | {'Alice': 20}['Alice'] | +------------------------+ | 20 | +------------------------+` -
使用
ELEMENT_AT(MAP, Key)的方式访问MAP的Key对应的Value。SELECT ELEMENT_AT({'Alice': 20}, 'Alice'); +------------------------------------+ | ELEMENT_AT({'Alice': 20}, 'Alice') | +------------------------------------+ | 20 | +------------------------------------+
示例
-
多层
MAP嵌套-- 建表 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 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))); -- 查询 SELECT map_nested['key1']['key2'] FROM map_table order by id; +----------------------------+ | map_nested['key1']['key2'] | +----------------------------+ | 1 | | 3 | +----------------------------+ -
复杂类型嵌套
-- 建表 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 INTO map_table VALUES (1, MAP('key1', [1, 2, 3])), (2, MAP('key1', [4, 5, 6])); -- 查询 SELECT map_array['key1'][1] FROM map_table order by id; +----------------------+ | map_array['key1'][1] | +----------------------+ | 1 | | 4 | +----------------------+ -- 建表 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 INTO map_table VALUES (1, MAP('key1', STRUCT(1, 'John'), 'key2', STRUCT(3, 'Jane'))); -- 查询 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 | +---------------------------------------+--------------------------------------------+ -
修改类型
-- 建表 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" ); -- 修改 KEY ALTER TABLE map_table MODIFY COLUMN map_varchar_int MAP<VARCHAR(20), INT>; -- 修改 VALUE ALTER TABLE map_table MODIFY COLUMN map_int_varchar MAP<INT, VARCHAR(20)>; -- 修改 KEY VALUE ALTER TABLE map_table MODIFY COLUMN map_varchar_varchar MAP<VARCHAR(20), VARCHAR(20)>; -- 查看列类型 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 | +---------------------+------------------------------+------+-------+---------+-------+
本文来自 Apache Doris 文档贡献者,按 Apache 2.0 整理版式与相对链接,保留正文与代码。图示请参阅原文。作者与贡献记录。
