Shulou(Shulou.com)06/02 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.