Complex types in hive are used

Source: Internet
Author: User
Tags data structures

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;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.