Create a map column

You can use the CREATE TABLE statement to create a map column.

Considerations

• Default values cannot be set.
• For information about supported map types, their descriptions, and limitations, see Overview of map data types.

Examples

Use the CREATE TABLE statement to create a table with a map column. Here is the syntax:

CREATE TABLE t1 (
  id INT,
  c1 MAP(INT, FLOAT),
  c2 MAP(VARCHAR(10), VARCHAR(2)),
  c3 MAP(FLOAT UNSIGNED, ARRAY(INT)),
  c4 MAP(VARCHAR(3), ARRAY(ARRAY(VARCHAR(20))))
);

Here is an example of defining a map type with the Value type as a nested array:

CREATE TABLE t2(c1 MAP(INT, ARRAY(INT)));
CREATE TABLE t3(c1 MAP(INT, INT[][]));

The Value type can be nested up to six levels (including the MAP itself). Here is an example:

CREATE TABLE t4 (c1 MAP(INT, INT[][][][][]));

DESC t4;
+-------+-------------------------------------------------+------+------+---------+-------+
| Field | Type                                            | Null | Key  | Default | Extra |
+-------+-------------------------------------------------+------+------+---------+-------+
| c1    | MAP(INT,ARRAY(ARRAY(ARRAY(ARRAY(ARRAY(INT)))))) | YES  |      | NULL    |       |
+-------+-------------------------------------------------+------+------+---------+-------+
1 row in set (0.002 sec)

Here are examples of writing and querying map data.

Create a test table named t5:

CREATE TABLE t5(
    c1 MAP(INT, INT),
    c2 MAP(VARCHAR(256), VARCHAR(256)),
    c3 MAP(BIGINT, BIGINT)
);

The result is as follows:

DESC t5;
+-------+--------------------------------+------+------+---------+-------+
| Field | Type                           | Null | Key  | Default | Extra |
+-------+--------------------------------+------+------+---------+-------+
| c1    | MAP(INT,INT)                   | YES  |      | NULL    |       |
| c2    | MAP(VARCHAR(256),VARCHAR(256)) | YES  |      | NULL    |       |
| c3    | MAP(BIGINT,BIGINT)             | YES  |      | NULL    |       |
+-------+--------------------------------+------+------+---------+-------+
3 rows in set (0.002 sec)

Insert data:

INSERT INTO t5 VALUES (MAP(1,2), MAP(1,2), MAP(1,2));

Query the test table. The result is as follows:

SELECT * FROM t5;
+-------+-----------+-------+
| c1    | c2        | c3    |
+-------+-----------+-------+
| {1:2} | {"1":"2"} | {1:2} |
+-------+-----------+-------+
1 row in set (0.003 sec)