Restrictions

• The CSV format does not support complex data types.
• When creating a table of the MAP data type, you must specify the schema and do not support the date, short, and timestamp data types.
• For the OBS table in JSON format, the key type of the MAP supports only the STRING type.
• The key of the MAP type cannot be NULL. Therefore, the MAP key does not support implicit conversion between inserted data formats where NULL values are allowed. For example, the STRING type cannot be converted to other native types, the FLOAT type cannot be converted to the TIMESTAMP type, and other native types cannot be converted to the DECIMAL type.
• Values of the double or boolean data type cannot be included in the STRUCT data type does not support the.

Example of ARRAY

Create an array_test table, set id to ARRAY<INT>, and name to STRING. Import the existing text file array_test.txt to array_test. The procedure is as follows:

1. Create a table.

   CREATE TABLE array_test(name STRING, id ARRAY<INT>)

   ROW FORMAT DELIMITED

   FIELDS TERMINATED BY ','

   COLLECTION ITEMS TERMINATED BY ':';

2. Import data.

   The path of the array_test.txt file is /opt/array_test.txt. The content of the file is as follows:

   100,1:2:3:4
   101,5:6
   102,7:8:9:10

   Run the following statement to import data.

   LOAD DATA LOCAL INPATH '/opt/array_test.txt' INTO TABLE array_test;

3. Query the result.

   To query all data in the array_test table, run the following statement:

   SELECT * FROM array_test;

   100 [1,2,3,4]
   101 [5,6]
   102 [7,8,9,10]

   To query the data of element 0 in the id array in the array_test table, run the following statement:

   SELECT id[0] FROM array_test;

   1
   5
   7

Example of Map

Create a map_test table, set the score parameter to the map<STRING,INT>) data type (the key is STRING, and the value is INT), and import the existing text map_test.txt to the map_test. The procedure is as follows:

1. Create a table.

   CREATE TABLE map_test(id STRING, score map<STRING,INT>)

   ROW FORMAT DELIMITED

   FIELDS TERMINATED BY '|'

   COLLECTION ITEMS TERMINATED BY ','

   MAP KEYS TERMINATED BY ':';

2. Import data.

   The path of the map_test.txt file is /opt/map_test.txt. The content of the file is as follows:

   1|Math:90,English:89,Physics:86
   2|Math:88,English:90,Physics:92
   3|Math:93,English:93,Physics:83

   Run the following statement to import data.

   LOAD DATA LOCAL INPATH '/opt/map_test.txt' INTO TABLE map_test;

3. Query the result.

   To query all data in the map_test table, run the following statement:

   SELECT * FROM map_test;

   1 {"English":89,"Math":90,"Physics":86}
   2 {"English":90,"Math":88,"Physics":92}
   3 {"English":93,"Math":93,"Physics":83}

   To query the math score in the map_test table, run the following statement:

   SELECT id, score['Math'] FROM map_test;

   190
   288
   393

Example of STRUCT

Create a struct_test table and set info to the STRUCT<name:STRING, age:INT> data type (the field consists of name and age, where the type of name is STRING and age is INT). Import the existing text struct_test.txt to the struct_test table. The procedure is as follows:

1. Create a table.

   CREATE TABLE struct_test(id INT, info STRUCT<name:STRING,age:INT>)

   ROW FORMAT DELIMITED

   FIELDS TERMINATED BY ','

   COLLECTION ITEMS TERMINATED BY ':';

2. Import data.

   The path of the struct_test.txt file is /opt/struct_test.txt. The content of the file is as follows:

   1,Lily:26
   2,Sam:28
   3,Mike:31
   4,Jack:29

   Run the following statement to import data.

   LOAD DATA LOCAL INPATH '/opt/struct_test.txt' INTO TABLE struct_test;

3. Query the result.

   To query all data in the struct_test table, run the following statement:

   SELECT * FROM struct_test;

   1 {"name":"Lily","age":26}
   2 {"name":"Sam","age":28}
   3 {"name":"Mike","age":31}
   4 {"name":"Jack","age":29}

   To query the name and age in the struct_test table, run the following statement:

   SELECT id,info.name,info.age FROM struct_test;

   1 Lily 26
   2 Sam 28
   3 Mike 31
   4 Jack 29