Complex types in hive are used

How to use compliant data structures in hive maps,array,structs

1. Use of array

Create a database table with array as the data type

CREATE table person (name String,work_locations array<string>)

ROW FORMAT Delimited

Fields TERMINATED by ' \ t '

COLLECTION ITEMS TERMINATED by ', ';

Data

Biansutao Beijing,shanghai,tianjin,hangzhou

Linan Changchu,chengdu,wuhan

Inbound data

LOAD DATA LOCAL inpath '/home/hadoop/person.txt ' OVERWRITE to TABLE person;

Inquire

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. Use of Map

Create a database table

CREATE TABLE score (name string, score map<string,int>)

ROW FORMAT Delimited

Fields TERMINATED by ' \ t '

COLLECTION ITEMS TERMINATED by ', '

MAP KEYS TERMINATED by ': ';

Data to be in storage

Biansutao ' math ': 80, ' language ': 89, ' English ': 95

Jobs ' language ': 60, ' math ': 80, ' English ': 99

Inbound data

LOAD DATA LOCAL inpath '/home/hadoop/score.txt ' OVERWRITE into TABLE score;

Inquire

Hive> select * from score;

Biansutao {"Mathematics": 80, "Chinese": 89, "English": 95}

Jobs {"Chinese": 60, "math": 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;

{"Language": 60, "math": 80, "English": 99}

{"Math": 80, "Chinese": 89, "English": 95}

Time taken:19.353 seconds

Hive> Select t.score[' language '] 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

3 Use of structs

Create a data table

CREATE TABLE Test (ID int,course struct<course:string,score:int>)

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;

Inquire

Hive> select * from test;

Ok

1 {"Course": "中文版", "Score": 80}

2 {"Course": "Math", "Score": 89}

3 {"Course": "Chinese", "Score": 95}

Time taken:0.275 seconds

Hive> Select course from test;

{"Course": "中文版", "Score": 80}

{"Course": "Math", "Score": 89}

{"Course": "Chinese", "Score": 95}

Time taken:44.968 seconds

Select T.course.course from Test t;

中文版

Math

Chinese

Time taken:15.827 seconds

Hive> Select T.course.score from Test t;