hive复杂格式array,map,struct使用

-- 创建数据库表，以array作为数据类型

drop table if exists person;

create table person(

     name string

    ,work_locations array<string>

)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ' '

COLLECTION ITEMS TERMINATED BY ','

stored as textfile

;

-- 数据

biansutao beijing,shanghai,tianjin,hangzhou

linan changchu,chengdu,wuhan

-- 入库数据

LOAD DATA LOCAL INPATH 'tmpa' OVERWRITE INTO TABLE person;

-- 查询

select * from person;

select name from person;

select work_locations[] from person;

select work_locations from person;

select work_locations[] from person;

select work_locations[] from person;

-- 创建数据库表

create table score(

     name string

    ,score map<string,int>

)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ' '

COLLECTION ITEMS TERMINATED BY ','

MAP KEYS TERMINATED BY ':'

stored as textfile

;

-- 数据

biansutao 数学:80,语文:89,英语:95

jobs 语文:60,数学:80,英语:99

-- 入库数据

LOAD DATA LOCAL INPATH 'tmpb' OVERWRITE INTO TABLE score;

-- 查询

select * from score;

select name from score;

select t.score from score t;

select t.score['语文'] from score t;

select t.score['英语'] from score t;

-- 创建数据表

CREATE TABLE test(

     id int

    ,course struct<course:string,score:int>

)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ' '

COLLECTION ITEMS TERMINATED BY ','

stored as textfile

;

-- 数据

1 english,80

2 math,89

3 chinese,95

-- 入库

LOAD DATA LOCAL INPATH 'tmpc' OVERWRITE INTO TABLE test;

-- 查询

select * from test;

select course from test;

select t.course.course from test t;

select t.course.score from test t;

-- 创建数据表 (异常)

create table test1(

     id int

    ,a MAP<STRING,ARRAY<STRING>>

)

row format delimited fields terminated by ' '

collection items terminated by ','

MAP KEYS TERMINATED BY ':'

stored as textfile

;

-- 数据

1 english:80,90,70

2 math:89,78,86

3 chinese:99,100,82

-- 入库

LOAD DATA LOCAL INPATH 'tmpd' OVERWRITE INTO TABLE test1;

-- 查询