What are the collection data types in Hive
This article introduces what collection data types there are in Hive, the content is very detailed, interested friends can use it for reference, I hope it can be helpful to you.
In addition to using the basic data types string, etc., the columns in Hive support the use of struct, map, array collection data types.
The data type description syntax example STRUCT is similar to struct or "objects" in the C language in that the element content can be accessed through the "dot" symbol. Struct {'John',' Doe'} MAPMAP is a collection of key-value pairs of elements that can be accessed using key. The map ('fisrt',' John', 'last',' Doe') ARRAY array is a collection of variables with the same data type and name. Array ('John',' Doe')
1. The use of Array
Create a database table with array as the data type
Create table person (name string,work_locations array) ROW FORMAT DELIMITED FIELDS TERMINATED BY'\ t 'COLLECTION ITEMS TERMINATED BY','
data
Biansutao beijing,shanghai,tianjin,hangzhou linan changchu,chengdu,wuhan
Incoming data
LOAD DATA LOCAL INPATH'/ home/hadoop/person.txt' OVERWRITE INTO TABLE person
Query
Hive > select * from person; biansutao ["beijing", "shanghai", "tianjin", "hangzhou"] linan ["changchu", "chengdu", "wuhan"] Time taken: 0.355 seconds hive > select name from person; linan biansutao Time taken: 12.397 seconds hive > select work_locations [0] from person; changchu beijing Time taken: 13.214 seconds hive > select work_locations from person ["changchu", "chengdu", "wuhan"] ["beijing", "shanghai", "tianjin", "hangzhou"] Time taken: 13.755 seconds hive > select work_locations [3] from person; NULL hangzhou Time taken: 12.722 seconds hive > select work_locations [4] from person; NULL NULL Time taken: 15.958 seconds
2. The use of Map
Create a database table
Create table score (name string, score map) ROW FORMAT DELIMITED FIELDS TERMINATED BY'\ t 'COLLECTION ITEMS TERMINATED BY', 'MAP KEYS TERMINATED BY':'
Data to be stored in the database
Biansutao 'Mathematics': 80 'Chinese': 89 'English': 95 jobs' Chinese': 60 'Mathematics': 80 'English': 99
Incoming data
LOAD DATA LOCAL INPATH'/ home/hadoop/score.txt' OVERWRITE INTO TABLE score
Query
Hive > select * from score; biansutao {Mathematics: 80, Chinese: 89, English: 95} jobs {Chinese: 60, Mathematics: 80, English: 99} Time taken: 0.665 seconds hive > select name from score; jobs biansutao Time taken: 19.778 seconds hive > select t.score from score t {"Chinese": 60, "Mathematics": 80, "English": 99} {"Mathematics": 80, "Chinese": 89, "English": 95} Time taken: 19.353 seconds hive > select t.score ['Chinese'] from score t; 60 89 Time taken: 13.054 seconds hive > select t.score ['English'] from score t; 99 95 Time taken: 13.769 seconds
Modify the delimiter of the map field
Storage Desc Params: colelction.delim # # field.delim\ t mapkey.delim = serialization.format\ t
You can view the properties of the table through desc formatted tableName.
In hive-2.1.1, you can see that colelction.delim, here is colelction, not collection,hive. The word is misspelled, so you still have to follow the error.
Alter table T8 set serdepropertyes ('colelction.delim'=',')
3. The use of Struct
Create a datasheet
CREATE TABLE test (id int,course struct) ROW FORMAT DELIMITED FIELDS TERMINATED BY'\ t 'COLLECTION ITEMS TERMINATED BY','
data
1 english,80 2 math,89 3 chinese,95
Storage
LOAD DATA LOCAL INPATH'/ home/hadoop/test.txt' OVERWRITE INTO TABLE test
Query
Hive > select * from test; OK 1 {"course": "english", "score": 80} 2 {"course": "math", "score": 89} 3 {"course": "chinese", "score": 95} Time taken: 0.275 seconds hive > select course from test {"course": "english", "score": 80} {"course": "math", "score": 89} {"course": "chinese", "score": 95} Time taken: 44.968 seconds select t.course.course from test t; english math chinese Time taken: 15.827 seconds hive > select t.course.score from test t; 80 89 95 Time taken: 13.235 seconds
4. Complex data types that do not support combination
We may sometimes want to build a complex data collection type, such as the following a field, which itself is a Map, whose key is of string type and value of Array collection type.
Build a table
Create table test1 (id int,a MAP) row format delimited fields terminated by'\ t 'collection items terminated by', 'MAP KEYS TERMINATED BY':'
Import data
1 english:80,90,70 2 math:89,78,86 3 chinese:99,100,82 LOAD DATA LOCAL INPATH'/ home/hadoop/test1.txt' OVERWRITE INTO TABLE test1
The data is queried here:
Hive > select * from test1; OK 1 {"english": ["80"], "90": null, "70": null} 2 {"math": ["89"], "78": null, "86": null} 3 {"chinese": ["99"], "100": null, "82": null}
As you can see, something has gone wrong. Our intention is to "english": ["80", "90", "70"]. In fact, 90 and 70 are also regarded as key of Map, and the value is empty. By analyzing our table-building statement, collection items terminated by', 'establishes that the separator between the data elements of the collection type (map, struct, array) is ",". In fact, map also belongs to the collection, so it will also be divided into three key-value pairs according to the comma; because MAP KEYS TERMINATED BY': 'defines the separator of key-value in map as ":", the first "english" can be accurately identified, and the latter directly sets value to "null".
About which collection data types in Hive are shared here, I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.
