hive 导出json格式 文件_在HIVE表中加载JSON文件

I have a JSON file like below, which I want to load in a HIVE table with parsed format, what are possible options I can go for.

If it is AVRO then I could have used directly AvroSerDe. But the source file in this case is JSON.

{

"subscriberId":"vfd1234-07e1-4054-9b64-83a5a20744db",

"cartId":"1234edswe-6a9c-493c-bcd0-7fb71995beef",

"cartStatus":"default",

"salesChannel":"XYZ",

"accountId":"12345",

"channelNumber":"12",

"timestamp":"Dec 12, 2013 8:30:00 AM",

"promotions":[

{

"promotionId":"NEWID1234",

"promotionContent":{

"has_termsandconditions":[

"TC_NFLMAXDEFAULT16R103578"

],

"sequenceNumber":"305",

"quantity":"1",

"promotionLevel":"basic",

"promotionDuration":"1",

"endDate":"1283142400000",

"description":"Regular Season One Payment",

"active":"true",

"disableInOfferPanel":"true",

"displayInCart":"true",

"type":"promotion",

"frequencyOfCharge":"weekly",

"promotionId":"NEWID1234",

"promotionIndicator":"No",

"shoppingCartTitle":"Regular Season One Payment",

"discountedPrice":"0",

"preselectedInOfferPanel":"false",

"price":"9.99",

"name":"Regular Season One Payment",

"have":[

"CatNFLSundayMax"

],

"ID":"NEWID1234",

"startDate":"1451365600000",

"displayInOfferPanel":"true"

}

}

]

}

I did tried to create a table using org.openx.data.jsonserde.JsonSerDe, but it is not showing me the data.

CREATE EXTERNAL TABLE test1

(

SUBSCRIBER_ID string,

CART_ID string,

CART_STAT_NAME string,

SLS_CHAN_NAME string,

ACCOUNT_ID string,

CHAN_NBR string,

TX_TMSTMP string,

PROMOTION ARRAY

)

ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'

LOCATION '';

解决方案

when you are using hive 0.12 and later, use hive-hcatalog-core,

Note: For Hive releases prior to 0.12, Amazon provides a JSON SerDe available at s3://elasticmapreduce/samples/hive-ads/libs/jsonserde.jar.

you should first add the jar hive-hcatalog-core,

ADD JAR /path/to/jar/;

you can either download it from mvn repository or find it manually.

then the hive table should look like

CREATE EXTERNAL TABLE test1

(

SUBSCRIBER_ID string,

CART_ID string,

CART_STAT_NAME string,

SLS_CHAN_NAME string,

ACCOUNT_ID string,

CHAN_NBR string,

TX_TMSTMP string,

PROMOTION ARRAY

)

ROW FORMAT SERDE

'org.apache.hive.hcatalog.data.JsonSerDe'

LOCATION '';