Hive从JSON错误 [英] Hive from JSON Error
本文介绍了Hive从JSON错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我无法以某种方式将此json转换为配置表，无论是变为空数据还是无法选中。我只需要与我的DDL所有相同的字段，如果它是内部结构的，我想让它作为一个字符串，而不是尝试解析。
唯一的一个几乎实现了： hive-hcatalog-core-1.1.0-cdh5.10.0.jar 因为有些数据是空白的，
我可以查询LIMIT，但是当我删除限制时，它返回给我这种错误 org.apache.hadoop.hive.serde2.SerDeException：java.io.IOException：期望的字段名称
创建表：
添加JAR hive-hcatalog-core -1.1.0-cdh5.10.0.jar;
CREATE EXTERNAL TABLE tabless（`dt` STRING，`hGeoLocation` STRING，`loginId` STRING，`hSearchFunnel` STRING，`timeseries` STRING，`locale` STRING，`fetcherResult` STRING，`searchType `STRING，`isBackDate` STRING，`hId` STRING，`hFrequency` STRING，`currency` STRING，`userType` STRING，`isSNA` STRING，`isBinding` STRING，`nodeId` STRING，`_id` STRING，`adjustedResult `STRING，`selectedProviderSell` STRING，`ChosenInventoryBeforeAdjusted` STRING，`PricingRules` STRING，`cInDate` STRING，`cOutDate` STRING，`machineId` STRING，`interface` STRING，`pricingSpec` STRING，`elapsedTime` STRING，`ChosenInventoryAfterAdjusted `STRING，`selectedProviderBase` STRING，`fFrequency` STRING，`kafkaPT` STRING，`kafkaST` STRING，`cookieId` STRING，`sessionId` STRING，`pricingSpecAbPriceAdjustment` STRING，`searchId` STRING，`prevSearchId` STRING`` competitorRequest `STRING，`CPricingRule` STRING，`CStatisticChosenMethod` STRING，`ChosenCId` STRING，`ChosenCPricingRule` STRING，`selectedCPriceType` STRI NG，`CPriceDiff` STRING，`competitorResponse` STRING，`searchRateType` STRING）COMMENT'somecomment'
ROW FORMAT SERDE
'org.apache.hive.hcatalog.data.JsonSerDe'
LOCATION 'someremotelocation';
如果需要，请使用在线json解析器，
我的JSON看起来像海量数据：
{ChosenCId：null，ChosenCPricingRule：null，ChosenInventoryAfterAdjusted：[{hRoomId：1086174， BASEFARE ：22150，SELLFARE ：25000}，{ hRoomId ：103270，BASEFARE ：249， SELLFARE ：2800}，{ hRoomId ：103272，BASEFARE ：2470， SELLFARE：200 }，{ hRoomId：100273 BASEFARE：3050， SELLFARE：3500}，{ hRoomId：10376 BASEFARE：3050， SELLFARE：3500}，{ hRoomId：10375， BASEFARE ：3050， SELLFARE ：3500}，{ hRoomId ：10374，BASEFARE ：367， SELLFARE ：4250}，{ hRoomId ：1069， BASEFARE ：430， SELLFARE：500 }，{ hRoomId：108634 BASEFARE：44700 SELLFARE：5000}，{ hRoomId：10270 BASEFARE：400， SELLFARE：570}，{ hRoomId：102， BASEFARE ：400， SELLFARE ：5700}，{ hRoomId ：1026， BASEFARE ：610， SELLFARE ：70}，{ hRoomId ：1033， BASEFARE ：610， SELLFARE：70 }，{ hRoomId：1075， BASEFARE：60， SELLFARE：0}，{ hRoomId：1074， BASEFARE：730， SELLFARE：80}，{ hRoomId：1039， BASEFARE ：870， SELLF ARE ：10}，{ hRoomId ：1269， BASEFARE ：800， SELLFARE ：10000}，{ hRoomId ：10271，BASEFARE ：9500， SELLFARE ：1100}，{ hRoomId ：1039， BASEFARE：17000 SELLFARE：2000}，{ hRoomId：1271， BASEFARE：1900， SELLFARE：200}]， ChosenInventoryBeforeAdjusted：[{ hRoomId：1084， BASEFARE ：220， SELLFARE ：2000}，{ hRoomId ：10320，BASEFARE ：250， SELLFARE ：280}，{ hRoomId ：10372，BASEFARE ：240， SELLFARE：200 }，{ hRoomId：103273 BASEFARE：3850， SELLFARE：300}，{ hRoomId：1076， BASEFARE：350， SELLFARE：300}，{ hRoomId：10275， BASEFARE ：380， SELLFARE ：350}，{ hRoomId ：1074， BASEFARE ：360， SELLFARE ：420}，{ hRoomId ：1069， BASEFARE ：430， SELLFARE：500 }，{ hRoomId：1084， BASEFARE：440， SELLFARE：50}，{ hRoomId：10370 BASEFARE：490， SELLFARE：500}，{ hRoomId：1032， BASEFARE ：400， SELLFARE ：500}，{ hRoomId ：1036， BASEFARE ：610， SELLFARE ：710}，{ hRoomId ：1073， BASEFARE ：610， SELLFARE：710 }，{ hRoomId：1035， BASEFARE：61， SELLFARE：710}，{ hRoomId：1034， BASEFARE：730， SELLFARE：80}，{ hRoomId：1029， BASEFARE ：800， SELLFARE ：100}，{ H RoomId ：10269，BASEFARE ：800， SELLFARE ：100}，{ hRoomId ：101， BASEFARE ：9500， SELLFARE ：100}，{ hRoomId ：109， BASEFARE：1700 SELLFARE：200}，{ hRoomId：1071， BASEFARE：1900， SELLFARE：20}]， CPriceDiff：0.0 CPricingRule：{}， CStatisticChosenMethod： 无， Cookie编号： 1547597， FTA：[{ hRoomId：1074， BASEFARE：220， SELLFARE：20}，{ hRoomId：10370 BASEFARE：2450， SELLFARE： 200}，{ hRoomId：1072， BASEFARE：240， SELLFARE：28}，{ hRoomId：1033， BASEFARE：37， SELLFARE：35}，{ hRoomId：1036， BASEFARE：300， SELLFARE：350}，{ hRoomId：105， BASEFARE：30， SELLFARE：350}，{ hRoomId：1074， BASEFARE：30， SELLFARE： 420}，{ hRoomId：109， BASEFARE：430， SELLFARE：00}，{ hRoomId：10874 BASEFARE：440， SELLFARE：500}，{ hRoomId：10370， BASEFARE：4900， SELLFARE：570}，{ hRoomId：103， BASEFARE：490， SELLFARE：5700}，{ hRoomId：10376 BASEFARE：6100， SELLFARE： 70}，{ hRoomId：10273 BASEFARE：600， SELLFARE：700}，{ hRoomId：175， BASEFARE：60， SELLFARE：70}，{ hRoomId：104， BASEFARE：730， SELLFARE：80}，{ hRoomId：1069，BAS EFARE ：80， SELLFARE ：100}，{ hRoomId ：109， BASEFARE ：80， SELLFARE ：10}，{ hRoomId ：171， BASEFARE ：950， SELLFARE：110 }，{ hRoomId：10 BASEFARE：170， SELLFARE：20}，{ hRoomId：101， BASEFARE：100， SELLFARE：200}]， PricingRules：{T_L ：22000900002， hbeds ：2200000002， T_P ：2200000002， t_m ：22000000002， e_private ：22000900002， T ：22000000002， E ：222， hbeds_ratebinding ：220000002 t_budgetrooms： 22000} 会话ID： d586280d34， _编码：154766 adjustedResult：{ CheapestBase：{ 吨：{ BASEFARE：22， SELLFARE：25}， E：{ BASEFARE：26， SELLFARE：28}}， CheapestSell：{ 吨：{ BASEFARE：22， SELLFARE：25}， E：{ BASEFARE：26，SELLFARE ：28}}}， cInDate ： 2012\" 年1月1日， cOutDate： 2017年12月12日， chosenProviderBase： T， chosenProviderSell： T， 货币 ： SGD， DT：147591430 elapsedTime：5 fetcherResult：{ CheapestBase：{ 吨：{ BASEFARE：20， SELLFARE：25}， E：{ BASEFARE：20， SELLFARE：28}}， CheapestSell：{ 吨：{ BASEFARE：22， SELLFARE：25}， E：{ BASEFARE：20，SELLFARE ：20}}}， fFrequency ：[ NONE ]， hFrequency ：[]， hGeoL ocation ： 新加坡 HID：200344， 接口：[ MOBILE_APPS_ANDROID]， isBackDate：假 isBinding：假 isSNA：假， 场所： id_ID，登录ID ：， 设备ID： 416， 节点ID： hivv2， pricingSpec：{ ISB：假的， searchDate：14700， hTransactionFrequencyStatus： ， userLocale： id_ID， isBackDate：假， 货币： VND， hTransactionFrequency：0 roomCount： 1， hUserType： NON_LOGGED_IN_USER， lengthOfStay： 1，fTransactionRecency ：0， userGeoCountry ： 澳大利亚 abPriceAdjustment： treatmentGroup， searchTime：530， BookingWindowInDays的：1， roomNight： 1， hSearchFunnel： LOWER_FUNNEL， cInDate ：147000，cOutDate ：1476032400000 searchDay ： 6\" ， fTransactionFrequency：0 fTransactionFrequencyStatus： NONE， cInDay： 7， hGrouping：1,93,41,122 ， HIDS： 2000000369344， hTransactionRecency：0 clientType： MOBILE}， 搜索类型： hRoomSearch， 时间序列：1475919104430， 时间序列：1475919104430， 用户类型：[ NON]，kafkaPT：1475919104430，kafkaST：1475919656986}
你们知道为什么吗？解决方案
外部表tabless（json_doc字符串）
行格式分隔
tblproperties（'serialization.last.column.takes.rest'='true'）
;
select json_tuple
json_doc
$ b'dt'hGeoLocation'loginId'hSearchFunnel
'timeseries''locale'fetcherResult '，'searchType'
，'isBackDate'，'hId'，'hFrequency'，'currency'
，'userType'，'isSNA'，'isBinding'，'nodeId'
， '_id'，'adjustedResult'，'selectedProviderSell'，'ChosenInventoryBeforeAdjusted'
，'PricingRules'，'cInDate'，'cOutDate'，'machineId'
，'interface'，'pricingSpec'，'elapsedTime '，'ChosenInventoryAfterAdjusted'
，'selectedProviderBase'，'fFrequency'，'kafkaPT'，'kafkaST'
，'cookieId'，'sessionId'，'pricingSpecAbPriceAdjustment'，'searchId'
， 'prevSearchId'， 'competitorRequest'， 'CPricingRule'，'C StatisticChosenMethod'
，'ChosenCId'，'ChosenCPricingRule'，'selectedCPriceType'，'CPriceDiff'
，'competitorResponse'，'searchRateType'
）as（
` dt，hGeoLocation，loginId，hSearchFunnel
，timeseries，locale，fetcherResult，searchType
，isBackDate，hId，hFrequency ，`currency`
，`userType`，`isSNA`，`isBinding`，`nodeId`
，`_id`，`adjustedResult`，`selectedProviderSell`，`ChosenInventoryBeforeAdjusted`
，`` PricingRules，cInDate，cOutDate，machineId，
，interface，pricingSpec，elapsedTime，ChosenInventoryAfterAdjusted，
，chosenProviderBase， ，'kafkaST'
，`cookieId`，`sessionId`，`pricingSpecAbPriceAdjustment`，`searchId`
，`prevSearchId`，`competitorRequest`，`CPricingRule`，`CStatisticChosenMethod`
，`ChosenCId`，`ChosenCPricingRule`，`selectedCPriceType`，`CPriceDiff`
，`competitorResponse`，`searchRateType`
）
from table
;
+ ----------- -------------- + + --------- + ------------- - + --------------- + -------- + ---------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- - + ------------- + ------------ + -------- + ------------ + ---------- + ---------- + ------- + ----------- + ------- - + -------- + --------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------------------------- + --------------- ----- + -------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------- + ---------------------------------- -------------------------------------------------- -------------------------------------------------- ----------------------------------------- + -------- ---- + ------------ + ----------- + -------------------- ----- + -------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------------------------------- + ------------- + -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------- + -------------------- + - ----------- + --------------- + --------------- + ------ ---- + ----------- + ------------------------------ + - -------- + -------------- + ------------------- + ------ -------- + ------------------------ + ----------- + ---- ---------------- + ------------------ + ------------ + - ------------------- + ---------------- +
| dt | hgeolocation | loginid | hsearchfunnel |时间序列| locale | fetcherresult | searchtype | isbackdate |隐藏| hfrequency |货币| usertype | issna | isbinding | nodeid | _id |调整结果| selectedprovidersell |选择存货前调整| pricingrules | cindate | coutdate | machineid |界面| pricingspec | elapsedtime | selectedinventoryafteradjusted | selectedproviderbase | ffrequency | kafkapt | kafkast | cookieid | sessionid | pricingspecabpriceadjustment | searchid | prevsearchid |竞争者请求| cpricingrule | cstatisticchosenmethod | selectedcid | selectedcpricingrule | selectedcpricetype | cpricediff |竞争对手| searchratetype |
+ ----------- + -------------- + --------- + -------- ------- + -------- + -------- + ----------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------ ------------- + + ------------ + -------- + ------- ----- + ---------- + ---------- + ------- + ----------- + - ------ + -------- + ---------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------- + ---------- ---------- + --------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------- + -------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------------------- + ------------ + ------------ + ----------- + ------------------------ - + ------------------------------------------------ -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------------- + ------------- + ---- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------------------- + -------------------- + ----- ------- + --------------- + --------------- + ---------- + ----------- + ------------------------------ + ------ ---- + -------------- + ------------------- + ---------- ---- + ------------------------ + ----------- + -------- ------------ + ------------------ + ------------ + ----- --------------- + ---------------- +
| 147591430 |新加坡| | （null）| 1475919104430 | id_ID | { CheapestBase：{ 吨：{ BASEFARE：20， SELLFARE：25}， E：{ BASEFARE：20， SELLFARE：28}}， CheapestSell：{吨：{BASEFARE：22，SELLFARE：25}，e：{BASEFARE：20，SELLFARE：20}}} hRoomSearch | false | 200344 | [] | SGD | [NON] | false | false | hivv2 | 154766 | { CheapestBase：{ 吨：{ BASEFARE：22， SELLFARE：25}， E：{ BASEFARE：26， SELLFARE：28}}， CheapestSell：{吨：{BASEFARE：22，SELLFARE：25}，e：{BASEFARE：26，SELLFARE：28}}} t | [{ hRoomId：1084， BASEFARE：220， SELLFARE：2000}，{ hRoomId：10320 BASEFARE：250， SELLFARE：280}，{ hRoomId：10372，BASEFARE ：240， SELLFARE ：200}，{ hRoomId ：103273，BASEFARE ：3850， SELLFARE ：300}，{ hRoomId ：1076， BASEFARE ：350， SELLFARE：300} ，{ hRoomId：10275 BASEFARE：380， SELLFARE：350}，{ hRoomId：1074， BASEFARE：360， SELLFARE：420}，{ hRoomId：1069，BASEFARE ：430， SELLFARE ：500}，{ hRoomId ：1084， BASEFARE ：440， SELLFARE ：50}，{ hRoomId ：10370，BASEFARE ：490， SELLFARE：500} ，{ hRoomId：1032， BASEFARE：400， SELLFARE：500}，{ hRoomId：1036， BASEFARE：610， SELLFARE：710}，{ hRoomId：1073，BASEFARE ：610， SELLFARE ：710}，{ hRoomId ：1035， BASEFARE ：61， SELLFARE ：710}，{ hRoomId ：1034， BASEFARE ：730， SELLFARE：80} ，{ hRoomId：1029， BASEFARE：800， SELLFARE：100}，{ hRoomId：10269 BASEFARE：800， SELLFARE：100}，{ hRoomId：101，BASEFARE ：9500， SELLFARE ：100}，{ hRoomId ：109， BASEFARE ：1700， SELLFARE ：200}，{ hRoomId ：1071， BASEFARE ：1900， SELLFARE：20} ] | { T_L：22000900002， hbeds：2200000002， T_P：2200000002， t_m：22000000002， e_private：22000900002， T：22000000002， E：222， hbeds_ratebinding：220000002 t_budgetrooms：22000} | 01-01-2012 | 12-12-2017 | 416 | [MOBILE_APPS_ANDROID] | { ISB：假 searchDate：14700 hTransactionFrequencyStatus： ， userLocale： id_ID， isBackDate：假， 货币： VND， hTransactionFrequency：10，roomCount ： 1\" ， hUserType： NON_LOGGED_IN_USER， lengthOfStay： 1， fTransactionRecency：10 userGeoCountry： 澳大利亚， abPriceAdjustment： treatmentGroup， searchTime：530， BookingWindowInDays的：1， roomNight： 1， hSearchFunnel： LOWER_FUNNEL， cInDate：147000 cOutDate：1476032400000 searchDay： 6， fTransactionFrequency：10，fTransactionFrequencyStatus ：NONE，cInDay：7，hGrouping：1,93,41,122，hIds：2000000369344，hTransactionRecency：10，clientType：MOBILE} | 5 | [{ hRoomId：1086174， BASEFARE：22150 SELLFARE：25000}，{ hRoomId：103270 BASEFARE：249， SELLFARE：2800}，{ hRoomId：103272，BASEFARE ：2470， SELLFARE ：200}，{ hRoomId ：100273，BASEFARE ：3050， SELLFARE ：3500}，{ hRoomId ：10376，BASEFARE ：3050， SELLFARE：3500} ，{ hRoomId：10375 BASEFARE：3050， SELLFARE：3500}，{ hRoomId：10374 BASEFARE：367， SELLFARE：4250}，{ hRoomId：1069，BASEFARE ：430， SELLFARE ：500}，{ hRoomId ：108634，BASEFARE ：44700，SELLFARE ：5000}，{ hRoomId ：10270，BASEFARE ：400， SELLFARE：570} ，{ hRoomId：102， BASEFARE：400， SELLFARE：5700}，{ hRoomId：1026， BASEFARE：610， SELLFARE：70}，{ hRoomId：1033，BASEFARE ：610， SELLFARE ：70}，{ hRoomId ：1075， BASEFARE ：60， SELLFARE ：10}，{ hRoomId ：1074， BASEFARE ：730， SELLFARE：80} ，{ hRoomId：1039， BASEFARE：870， SELLFARE：10}，{ hRoomId：1269， BASEFARE：800， SELLFARE：10000}，{ hRoomId：10271，BASEFARE ：9500， SELLFARE ：1100}，{ hRoomId ：1039， BASEFARE ：17000，SELLFARE ：2000}，{ hRoomId ：1271， BASEFARE ：1900， SELLFARE：200} ] | t | [无] | 1475919104430 | 1475919656986 | （null）| （null）| （null）| （null）| （null）| （null）| {} | none | （null）| （null）| （null）| 10.0 | （null）| （null）|
+ ----------- + -------------- + --------- + -------- ------- + -------- + -------- + ----------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------ ------------- + + ------------ + -------- + ------- ----- + ---------- + ---------- + ------- + ----------- + - ------ + -------- + ---------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------- + ---------- ---------- + --------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------- + -------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------------------- + ------------ + ------------ + ----------- + ------------------------ - + ------------------------------------------------ -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------------- + ------------- + ---- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------------------- + -------------------- + ----- ------- + --------------- + --------------- + ---------- + ----------- + ------------------------------ + ------ ---- + -------------- + ------------------- + ---------- ---- + ------------------------ + ----------- + -------- ------------ + ------------------ + ------------ + ----- --------------- + ---------------- +
I can't make this json into hive table somehow, either become all null data or not able being selected. i just need all the same fields with my DDL, and if it's structured inside it, i want to let it as a string instead try to parse that.
The only one almost achieved only by : hive-hcatalog-core-1.1.0-cdh5.10.0.jar since some data are blank,
i'm able to query with LIMIT but when i remove the limit, it was returning me this kind of error org.apache.hadoop.hive.serde2.SerDeException: java.io.IOException: Field name expected
My table creation :
ADD JAR hive-hcatalog-core-1.1.0-cdh5.10.0.jar;
CREATE EXTERNAL TABLE tabless (`dt` STRING, `hGeoLocation` STRING, `loginId` STRING, `hSearchFunnel` STRING, `timeseries` STRING, `locale` STRING, `fetcherResult` STRING, `searchType` STRING, `isBackDate` STRING, `hId` STRING, `hFrequency` STRING, `currency` STRING, `userType` STRING, `isSNA` STRING, `isBinding` STRING, `nodeId` STRING, `_id` STRING, `adjustedResult` STRING, `chosenProviderSell` STRING, `ChosenInventoryBeforeAdjusted` STRING, `PricingRules` STRING, `cInDate` STRING, `cOutDate` STRING, `machineId` STRING, `interface` STRING, `pricingSpec` STRING, `elapsedTime` STRING, `ChosenInventoryAfterAdjusted` STRING, `chosenProviderBase` STRING, `fFrequency` STRING, `kafkaPT` STRING, `kafkaST` STRING, `cookieId` STRING, `sessionId` STRING,`pricingSpecAbPriceAdjustment` STRING,`searchId` STRING,`prevSearchId` STRING, `competitorRequest` STRING, `CPricingRule` STRING, `CStatisticChosenMethod` STRING, `ChosenCId` STRING, `ChosenCPricingRule` STRING, `chosenCPriceType` STRING, `CPriceDiff` STRING, `competitorResponse` STRING, `searchRateType` STRING) COMMENT 'somecomment'
ROW FORMAT SERDE
'org.apache.hive.hcatalog.data.JsonSerDe'
LOCATION 'someremotelocation';
Please use online json parser if needed, My JSON looks like this in massive quantity :
{"ChosenCId":null,"ChosenCPricingRule":null,"ChosenInventoryAfterAdjusted":[{"hRoomId":1086174,"BASEFARE":22150,"SELLFARE":25000},{"hRoomId":103270,"BASEFARE":249,"SELLFARE":2800},{"hRoomId":103272,"BASEFARE":2470,"SELLFARE":200},{"hRoomId":100273,"BASEFARE":3050,"SELLFARE":3500},{"hRoomId":10376,"BASEFARE":3050,"SELLFARE":3500},{"hRoomId":10375,"BASEFARE":3050,"SELLFARE":3500},{"hRoomId":10374,"BASEFARE":367,"SELLFARE":4250},{"hRoomId":1069,"BASEFARE":430,"SELLFARE":500},{"hRoomId":108634,"BASEFARE":44700,"SELLFARE":5000},{"hRoomId":10270,"BASEFARE":400,"SELLFARE":570},{"hRoomId":102,"BASEFARE":400,"SELLFARE":5700},{"hRoomId":1026,"BASEFARE":610,"SELLFARE":70},{"hRoomId":1033,"BASEFARE":610,"SELLFARE":70},{"hRoomId":1075,"BASEFARE":60,"SELLFARE":0},{"hRoomId":1074,"BASEFARE":730,"SELLFARE":80},{"hRoomId":1039,"BASEFARE":870,"SELLFARE":10},{"hRoomId":1269,"BASEFARE":800,"SELLFARE":10000},{"hRoomId":10271,"BASEFARE":9500,"SELLFARE":1100},{"hRoomId":1039,"BASEFARE":17000,"SELLFARE":2000},{"hRoomId":1271,"BASEFARE":1900,"SELLFARE":200}],"ChosenInventoryBeforeAdjusted":[{"hRoomId":1084,"BASEFARE":220,"SELLFARE":2000},{"hRoomId":10320,"BASEFARE":250,"SELLFARE":280},{"hRoomId":10372,"BASEFARE":240,"SELLFARE":200},{"hRoomId":103273,"BASEFARE":3850,"SELLFARE":300},{"hRoomId":1076,"BASEFARE":350,"SELLFARE":300},{"hRoomId":10275,"BASEFARE":380,"SELLFARE":350},{"hRoomId":1074,"BASEFARE":360,"SELLFARE":420},{"hRoomId":1069,"BASEFARE":430,"SELLFARE":500},{"hRoomId":1084,"BASEFARE":440,"SELLFARE":50},{"hRoomId":10370,"BASEFARE":490,"SELLFARE":500},{"hRoomId":1032,"BASEFARE":400,"SELLFARE":500},{"hRoomId":1036,"BASEFARE":610,"SELLFARE":710},{"hRoomId":1073,"BASEFARE":610,"SELLFARE":710},{"hRoomId":1035,"BASEFARE":61,"SELLFARE":710},{"hRoomId":1034,"BASEFARE":730,"SELLFARE":80},{"hRoomId":1029,"BASEFARE":800,"SELLFARE":100},{"hRoomId":10269,"BASEFARE":800,"SELLFARE":100},{"hRoomId":101,"BASEFARE":9500,"SELLFARE":100},{"hRoomId":109,"BASEFARE":1700,"SELLFARE":200},{"hRoomId":1071,"BASEFARE":1900,"SELLFARE":20}],"CPriceDiff":0.0,"CPricingRule":{},"CStatisticChosenMethod":"none","CookieID":"1547597","FTA":[{"hRoomId":1074,"BASEFARE":220,"SELLFARE":20},{"hRoomId":10370,"BASEFARE":2450,"SELLFARE":200},{"hRoomId":1072,"BASEFARE":240,"SELLFARE":28},{"hRoomId":1033,"BASEFARE":37,"SELLFARE":35},{"hRoomId":1036,"BASEFARE":300,"SELLFARE":350},{"hRoomId":105,"BASEFARE":30,"SELLFARE":350},{"hRoomId":1074,"BASEFARE":30,"SELLFARE":420},{"hRoomId":109,"BASEFARE":430,"SELLFARE":00},{"hRoomId":10874,"BASEFARE":440,"SELLFARE":500},{"hRoomId":10370,"BASEFARE":4900,"SELLFARE":570},{"hRoomId":103,"BASEFARE":490,"SELLFARE":5700},{"hRoomId":10376,"BASEFARE":6100,"SELLFARE":70},{"hRoomId":10273,"BASEFARE":600,"SELLFARE":700},{"hRoomId":175,"BASEFARE":60,"SELLFARE":70},{"hRoomId":104,"BASEFARE":730,"SELLFARE":80},{"hRoomId":1069,"BASEFARE":80,"SELLFARE":100},{"hRoomId":109,"BASEFARE":80,"SELLFARE":10},{"hRoomId":171,"BASEFARE":950,"SELLFARE":110},{"hRoomId":10,"BASEFARE":170,"SELLFARE":20},{"hRoomId":101,"BASEFARE":100,"SELLFARE":200}],"PricingRules":{"t_l":22000900002,"hbeds":2200000002,"t_p":2200000002,"t_m":22000000002,"e_private":22000900002,"t":22000000002,"e":222,"hbeds_ratebinding":220000002,"t_budgetrooms":22000},"SessionID":"d586280d34","_id":154766,"adjustedResult":{"CheapestBase":{"t":{"BASEFARE":22,"SELLFARE":25},"e":{"BASEFARE":26,"SELLFARE":28}},"CheapestSell":{"t":{"BASEFARE":22,"SELLFARE":25},"e":{"BASEFARE":26,"SELLFARE":28}}},"cInDate":"01-01-2012","cOutDate":"12-12-2017","chosenProviderBase":"t","chosenProviderSell":"t","currency":"SGD","dt":147591430,"elapsedTime":5,"fetcherResult":{"CheapestBase":{"t":{"BASEFARE":20,"SELLFARE":25},"e":{"BASEFARE":20,"SELLFARE":28}},"CheapestSell":{"t":{"BASEFARE":22,"SELLFARE":25},"e":{"BASEFARE":20,"SELLFARE":20}}},"fFrequency":["NONE"],"hFrequency":[],"hGeoLocation":"SINGAPORE","hId":200344,"interface":["MOBILE_APPS_ANDROID"],"isBackDate":false,"isBinding":false,"isSNA":false,"locale":"id_ID","loginId":"","machineId":"416","nodeId":"hivv2","pricingSpec":{"isB":false,"searchDate":14700,"hTransactionFrequencyStatus":"","userLocale":"id_ID","isBackDate":false,"currency":"VND","hTransactionFrequency":0,"roomCount":"1","hUserType":"NON_LOGGED_IN_USER","lengthOfStay":"1","fTransactionRecency":0,"userGeoCountry":"Australia","abPriceAdjustment":"treatmentGroup","searchTime":530,"bookingWindowInDays":1,"roomNight":"1","hSearchFunnel":"LOWER_FUNNEL","cInDate":147000,"cOutDate":1476032400000,"searchDay":"6","fTransactionFrequency":0,"fTransactionFrequencyStatus":"NONE","cInDay":"7","hGrouping":"1,93,41,122","hIds":"2000000369344","hTransactionRecency":0,"clientType":"MOBILE"},"searchType":"hRoomSearch","timeSeries":1475919104430,"timeseries":1475919104430,"userType":["NON"],"kafkaPT":1475919104430,"kafkaST":1475919656986}
do you guys know why / the solution ?
解决方案
create external table tabless (json_doc string)
row format delimited
tblproperties ('serialization.last.column.takes.rest'='true')
;
select json_tuple
(
json_doc
,'dt','hGeoLocation','loginId','hSearchFunnel'
,'timeseries','locale','fetcherResult','searchType'
,'isBackDate','hId','hFrequency','currency'
,'userType','isSNA','isBinding','nodeId'
,'_id','adjustedResult','chosenProviderSell','ChosenInventoryBeforeAdjusted'
,'PricingRules','cInDate','cOutDate','machineId'
,'interface','pricingSpec','elapsedTime','ChosenInventoryAfterAdjusted'
,'chosenProviderBase','fFrequency','kafkaPT','kafkaST'
,'cookieId','sessionId','pricingSpecAbPriceAdjustment','searchId'
,'prevSearchId','competitorRequest','CPricingRule','CStatisticChosenMethod'
,'ChosenCId','ChosenCPricingRule','chosenCPriceType','CPriceDiff'
,'competitorResponse','searchRateType'
) as (
`dt`,`hGeoLocation`,`loginId`,`hSearchFunnel`
,`timeseries`,`locale`,`fetcherResult`,`searchType`
,`isBackDate`,`hId`,`hFrequency`,`currency`
,`userType`,`isSNA`,`isBinding`,`nodeId`
,`_id`,`adjustedResult`,`chosenProviderSell`,`ChosenInventoryBeforeAdjusted`
,`PricingRules`,`cInDate`,`cOutDate`,`machineId`
,`interface`,`pricingSpec`,`elapsedTime`,`ChosenInventoryAfterAdjusted`
,`chosenProviderBase`,`fFrequency`,`kafkaPT`,`kafkaST`
,`cookieId`,`sessionId`,`pricingSpecAbPriceAdjustment`,`searchId`
,`prevSearchId`,`competitorRequest`,`CPricingRule`,`CStatisticChosenMethod`
,`ChosenCId`,`ChosenCPricingRule`,`chosenCPriceType`,`CPriceDiff`
,`competitorResponse`,`searchRateType`
)
from tabless
;
+-----------+--------------+---------+---------------+---------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+------------+--------+------------+----------+----------+-------+-----------+--------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------------+-----------+-------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+------------+---------------+---------------+----------+-----------+------------------------------+----------+--------------+-------------------+--------------+------------------------+-----------+--------------------+------------------+------------+--------------------+----------------+
| dt | hgeolocation | loginid | hsearchfunnel | timeseries | locale | fetcherresult | searchtype | isbackdate | hid | hfrequency | currency | usertype | issna | isbinding | nodeid | _id | adjustedresult | chosenprovidersell | choseninventorybeforeadjusted | pricingrules | cindate | coutdate | machineid | interface | pricingspec | elapsedtime | choseninventoryafteradjusted | chosenproviderbase | ffrequency | kafkapt | kafkast | cookieid | sessionid | pricingspecabpriceadjustment | searchid | prevsearchid | competitorrequest | cpricingrule | cstatisticchosenmethod | chosencid | chosencpricingrule | chosencpricetype | cpricediff | competitorresponse | searchratetype |
+-----------+--------------+---------+---------------+---------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+------------+--------+------------+----------+----------+-------+-----------+--------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------------+-----------+-------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+------------+---------------+---------------+----------+-----------+------------------------------+----------+--------------+-------------------+--------------+------------------------+-----------+--------------------+------------------+------------+--------------------+----------------+
| 147591430 | SINGAPORE | | (null) | 1475919104430 | id_ID | {"CheapestBase":{"t":{"BASEFARE":20,"SELLFARE":25},"e":{"BASEFARE":20,"SELLFARE":28}},"CheapestSell":{"t":{"BASEFARE":22,"SELLFARE":25},"e":{"BASEFARE":20,"SELLFARE":20}}} | hRoomSearch | false | 200344 | [] | SGD | ["NON"] | false | false | hivv2 | 154766 | {"CheapestBase":{"t":{"BASEFARE":22,"SELLFARE":25},"e":{"BASEFARE":26,"SELLFARE":28}},"CheapestSell":{"t":{"BASEFARE":22,"SELLFARE":25},"e":{"BASEFARE":26,"SELLFARE":28}}} | t | [{"hRoomId":1084,"BASEFARE":220,"SELLFARE":2000},{"hRoomId":10320,"BASEFARE":250,"SELLFARE":280},{"hRoomId":10372,"BASEFARE":240,"SELLFARE":200},{"hRoomId":103273,"BASEFARE":3850,"SELLFARE":300},{"hRoomId":1076,"BASEFARE":350,"SELLFARE":300},{"hRoomId":10275,"BASEFARE":380,"SELLFARE":350},{"hRoomId":1074,"BASEFARE":360,"SELLFARE":420},{"hRoomId":1069,"BASEFARE":430,"SELLFARE":500},{"hRoomId":1084,"BASEFARE":440,"SELLFARE":50},{"hRoomId":10370,"BASEFARE":490,"SELLFARE":500},{"hRoomId":1032,"BASEFARE":400,"SELLFARE":500},{"hRoomId":1036,"BASEFARE":610,"SELLFARE":710},{"hRoomId":1073,"BASEFARE":610,"SELLFARE":710},{"hRoomId":1035,"BASEFARE":61,"SELLFARE":710},{"hRoomId":1034,"BASEFARE":730,"SELLFARE":80},{"hRoomId":1029,"BASEFARE":800,"SELLFARE":100},{"hRoomId":10269,"BASEFARE":800,"SELLFARE":100},{"hRoomId":101,"BASEFARE":9500,"SELLFARE":100},{"hRoomId":109,"BASEFARE":1700,"SELLFARE":200},{"hRoomId":1071,"BASEFARE":1900,"SELLFARE":20}] | {"t_l":22000900002,"hbeds":2200000002,"t_p":2200000002,"t_m":22000000002,"e_private":22000900002,"t":22000000002,"e":222,"hbeds_ratebinding":220000002,"t_budgetrooms":22000} | 01-01-2012 | 12-12-2017 | 416 | ["MOBILE_APPS_ANDROID"] | {"isB":false,"searchDate":14700,"hTransactionFrequencyStatus":"","userLocale":"id_ID","isBackDate":false,"currency":"VND","hTransactionFrequency":10,"roomCount":"1","hUserType":"NON_LOGGED_IN_USER","lengthOfStay":"1","fTransactionRecency":10,"userGeoCountry":"Australia","abPriceAdjustment":"treatmentGroup","searchTime":530,"bookingWindowInDays":1,"roomNight":"1","hSearchFunnel":"LOWER_FUNNEL","cInDate":147000,"cOutDate":1476032400000,"searchDay":"6","fTransactionFrequency":10,"fTransactionFrequencyStatus":"NONE","cInDay":"7","hGrouping":"1,93,41,122","hIds":"2000000369344","hTransactionRecency":10,"clientType":"MOBILE"} | 5 | [{"hRoomId":1086174,"BASEFARE":22150,"SELLFARE":25000},{"hRoomId":103270,"BASEFARE":249,"SELLFARE":2800},{"hRoomId":103272,"BASEFARE":2470,"SELLFARE":200},{"hRoomId":100273,"BASEFARE":3050,"SELLFARE":3500},{"hRoomId":10376,"BASEFARE":3050,"SELLFARE":3500},{"hRoomId":10375,"BASEFARE":3050,"SELLFARE":3500},{"hRoomId":10374,"BASEFARE":367,"SELLFARE":4250},{"hRoomId":1069,"BASEFARE":430,"SELLFARE":500},{"hRoomId":108634,"BASEFARE":44700,"SELLFARE":5000},{"hRoomId":10270,"BASEFARE":400,"SELLFARE":570},{"hRoomId":102,"BASEFARE":400,"SELLFARE":5700},{"hRoomId":1026,"BASEFARE":610,"SELLFARE":70},{"hRoomId":1033,"BASEFARE":610,"SELLFARE":70},{"hRoomId":1075,"BASEFARE":60,"SELLFARE":10},{"hRoomId":1074,"BASEFARE":730,"SELLFARE":80},{"hRoomId":1039,"BASEFARE":870,"SELLFARE":10},{"hRoomId":1269,"BASEFARE":800,"SELLFARE":10000},{"hRoomId":10271,"BASEFARE":9500,"SELLFARE":1100},{"hRoomId":1039,"BASEFARE":17000,"SELLFARE":2000},{"hRoomId":1271,"BASEFARE":1900,"SELLFARE":200}] | t | ["NONE"] | 1475919104430 | 1475919656986 | (null) | (null) | (null) | (null) | (null) | (null) | {} | none | (null) | (null) | (null) | 10.0 | (null) | (null) |
+-----------+--------------+---------+---------------+---------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+------------+--------+------------+----------+----------+-------+-----------+--------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------------+-----------+-------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+------------+---------------+---------------+----------+-----------+------------------------------+----------+--------------+-------------------+--------------+------------------------+-----------+--------------------+------------------+------------+--------------------+----------------+
这篇关于Hive从JSON错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
