加载json文件到hive表

struct类型应用

准备json文件

simple.json

{"foo":"abc","bar":"200901011000000","quux":{"quuxid":1234,"quuxname":"sam"}}

添加hive-hcatalog-core.jar包

add jar /home/hadoop/hive/lib/hive-hcatalog-core.jar

建立测试表

create database if not exists mytest;
use mytest;
create table if not exists  my_table(
   foo string,
   bar string,
   quux struct<quuxid:int,quuxname:string>
) 
row format serde 'org.apache.hive.hcatlog.data.JsonSerde'
stored as textfile;

装载数据

load data local inpath '/home/hadoop/data/simple.json' into table my_table;

查询

select foo, bar,quux.quuxid,quux.quuname from my_table;

sstruct结合array类型应用

准备json文件

complex.json

{"docid":"abc","user":{"id":123,"username":"saml1234","name":"sam","shippingaddress":{"address1":"123mainst","address2:""","city":"durham","state":"nc"},"orders":[{"itemid":6789,"orderdate":"11/11/2012"},{"itemid":4352,"orderdate"："12/12/2012"}]}}

添加hive-hcatalog-core.jar包

add jar /home/hadoop/hive/lib/hive-hcatalog-core.jar

建立测试表

use mytest;
create table  if not exists complex_json(
 docid string,
 user struct<id: int,
      username: string,
      shippingaddress:struct<address1:string
           address2: string,
           city: string,
           state: string>,
           orders:array<struct<itemid:int,orderdate:String>>>
)
row format serde 'org.apache.hive.hcatlog.data.JsonSerde'
stored as textfile;

装载数据

load data local inpath '/home/hadoop/data/complex.json' overwrite table complex_json;

查询

select docid,user.id,user.shippingaddress.city as city ,user.orders[0].itemid as order0id,user.orders[1].itemid as order1ib    
from complex_json;

动态map类型应用

json文件

a.json

{"conflict":{"liveid":123,"zhuboid":"456","media":789,"proxy":"ac","result":10000}}
{"conflict":{"liveid":123,"zhuboid":"456","media":789,"proxy":"ac"}}
{"conflict":{"liveid":123,"zhuboid":"456","media":789}}
{"conflict":{"liveid":123,"zhuboid":"456"}}
{"conflict":{"liveid":123}}

添加hive-hcatalog-core.jar包

add jar /home/hadoop/hive/lib/hive-hcatalog-core.jar

建立测试表

use mytest;
create table if not exists json_table(
    conflict map<string,string>
)
row format serde 'org.apache.hive.hcatlog.data.JsonSerde'
stored as textfile;

查询

select * from json_table;   
select conflict['media'] from json_table;