[HADOOP] 어떻게 측면 뷰는 XML 데이터 형식 하이브에서 폭발 사용 하는가?
HADOOP어떻게 측면 뷰는 XML 데이터 형식 하이브에서 폭발 사용 하는가?
나는 하이브 테이블에 XML 형식으로 판매 데이터를로드하려합니다. 아래는 데이터의 작은 샘플입니다.
난 내가 여러 테이블로 분리하는 경우 하이브에 아래의 데이터를로드 한 후 필요에 따라 참여할 수 있다는 것을 알고 있습니다. 하지만 그냥 하나의 테이블에로드 할 수 있습니다 및 예상 출력이 부착 된 스크린 샷과 같이해야하는지 알고 싶었다.
내가 사용해야하는 테이블 구조를 도와 내가 옵션을 폭발 측면보기를 사용할 수있는 방법을 효과적으로 달성하기 바랍니다.
샘플 데이터 :
<Store>
<Version>1.1</Version>
<StoreId>16695</StoreId>
<Bskt>
<TillNo>4</TillNo>
<BsktNo>1753</BsktNo>
<DateTime>2017-10-31T11:19:34.000+11:00</DateTime>
<OpID>50056</OpID>
<Itm>
<ItmSeq>1</ItmSeq>
<GTIN>29559</GTIN>
<ItmDsc>CHOCALATE</ItmDsc>
<ItmProm>
<PromCD>CM</PromCD>
</ItmProm>
</Itm>
<Itm>
<ItmSeq>2</ItmSeq>
<GTIN>59653</GTIN>
<ItmDsc>CORN FLAKES</ItmDsc>
</Itm>
<Itm>
<ItmSeq>3</ItmSeq>
<GTIN>42260</GTIN>
<ItmDsc> MILK CHOCOLATE 162GM</ItmDsc>
<ItmProm>
<PromCD>MTSRO</PromCD>
<OfferID>11766</OfferID>
</ItmProm>
</Itm>
</Bskt>
<Bskt>
<TillNo>5</TillNo>
<BsktNo>1947</BsktNo>
<DateTime>2017-10-31T16:24:59.000+11:00</DateTime>
<OpID>50063</OpID>
<Itm>
<ItmSeq>1</ItmSeq>
<GTIN>24064</GTIN>
<ItmDsc>TOMATOES 2KG</ItmDsc>
<ItmProm>
<PromCD>INSTORE</PromCD>
</ItmProm>
</Itm>
<Itm>
<ItmSeq>2</ItmSeq>
<GTIN>81287</GTIN>
<ItmDsc>ROTHMANS BLUE</ItmDsc>
<ItmProm>
<PromCD>TF</PromCD>
</ItmProm>
</Itm>
</Bskt>
</Store>
원하는 출력
여기에 이미지 설명을 입력
테이블 구조 :
CREATE EXTERNAL TABLE IF NOT EXISTS POC_BASKET_ITEM_PROMO (
`Version` string,
`StoreId` string,
`DateTime` array<string>,
`BsktNo` array<double>,
`TillNo` array<int>,
`Item_Seq_num` array<int>,
`GTIN` array<string>,
`ItmDsc` array<string>,
`Promo_CD` array<string>,
`Offer_ID` array<int>
)
ROW FORMAT SERDE 'com.ibm.spss.hive.serde2.xml.XmlSerDe'
WITH SERDEPROPERTIES (
"column.xpath.Version"="/Store/Version/text()",
"column.xpath.StoreId"="/Store/StoreId/text()",
"column.xpath.DateTime"="/Store/Bskt/DateTime/text()",
"column.xpath.BsktNo"="/Store/Bskt/BsktNo/text()",
"column.xpath.TillNo"="/Store/Bskt/TillNo/text()",
"column.xpath.Item_Seq_num"="/Store/Bskt/Itm/ItmSeq/text()",
"column.xpath.GTIN"="/Store/Bskt/Itm/GTIN/text()",
"column.xpath.ItmDsc"="/Store/Bskt/Itm/ItmDsc/text()",
"column.xpath.Promo_CD"="/Store/Bskt/Itm/ItmProm/PromCD/text()",
"column.xpath.Offer_ID"="/Store/Bskt/Itm/ItmProm/OfferID/text()"
)
STORED AS INPUTFORMAT 'com.ibm.spss.hive.serde2.xml.XmlInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION 'hdfs://namenode:8020/DEV/TEST/nanda_test'
TBLPROPERTIES (
"xmlinput.start"="<Store","xmlinput.end"="</Store>"
);
산출: 여기에 이미지 설명을 입력
쿼리 아래에 시도하는 것은 내가 원하는 방식으로 결과를 보여주는되지 않으며, 데이터를 읽을 수 있습니다.
select Version,StoreId,basket_dtm,basket_number,till_number from POC_BASKET_ITEM_PROMO
LATERAL VIEW explode(DateTime) table1 as basket_dtm
LATERAL VIEW explode(BsktNo) table2 as basket_number
LATERAL VIEW explode(TillNo) table3 as till_number;
결과 :
여기에 이미지 설명을 입력
해결법
-
==============================
1.크로스 조인과 같은 배열 객체의 작품 폭발. 당신이 8 개 행을 줄 것이다 모든 열 폭발 적용, 2 개 요소를 각각 포함하는 배열 3 열이있는 경우 그래서.
크로스 조인과 같은 배열 객체의 작품 폭발. 당신이 8 개 행을 줄 것이다 모든 열 폭발 적용, 2 개 요소를 각각 포함하는 배열 3 열이있는 경우 그래서.
당신은 다른 배열에서 하나 개의 객체를 매핑 할 수 없습니다.
실제로 각 요소에 대해 당신에게 인덱스를 제공 posexplode를 사용하여 할 수 있습니다. 어떤 당신은 조건에 따라 가입 할 수 있습니다. 그러나 까다로운 '고 여러 열을 가지고 배열의 크기는 각 열의 다른 경우.
해결책
따라서 귀하의 경우 가장 좋은 방법은 될 것입니다.
귀하의 XML에 대한 JSON
{"Version":"1.1","StoreId":"16695","Bskt":[{"TillNo":"4","BsktNo":"1753","DateTime":"2017-10-31T11:19:34.000+11:00","OpID":"50056","Itm":[{"ItmSeq":"1","GTIN":"29559","ItmDsc":"CHOCALATE","ItmProm":{"PromCD":"CM"}},{"ItmSeq":"2","GTIN":"59653","ItmDsc":"CORNFLAKES"},{"ItmSeq":"3","GTIN":"42260","ItmDsc":"MILKCHOCOLATE162GM","ItmProm":{"PromCD":"MTSRO","OfferID":"11766"}}]},{"TillNo":"5","BsktNo":"1947","DateTime":"2017-10-31T16:24:59.000+11:00","OpID":"50063","Itm":[{"ItmSeq":"1","GTIN":"24064","ItmDsc":"TOMATOES2KG","ItmProm":{"PromCD":"INSTORE"}},{"ItmSeq":"2","GTIN":"81287","ItmDsc":"ROTHMANSBLUE","ItmProm":{"PromCD":"TF"}}]}]}당신이 당신의 파일에 탭이나 공백이있는 경우 JsonSerde은 오류를 줄 수 있습니다. 그래서 그들을 제거하는 것이 가장 좋습니다.
하이브 표
create external table temp.test_json ( Version string, StoreId string, Bskt array<struct< BsktNo:string, DateTime:string, OpID:string, TillNo:string, Itm:array<struct< GTIN:string, ItmDsc:string, ItmSeq:string, ItmProm:struct< OfferID:string, PromCD:string > > > > > ) row format serde 'org.openx.data.jsonserde.JsonSerDe' location '/tmp/test_json/table/';보기 만들기
SELECT Version, StoreId, basket.bsktno, basket.tillno, basket.`datetime`, item.itmseq, item.itmdsc, item.gtin, item.itmprom.offerid, item.itmprom.promcd FROM temp.test_json lateral view explode(bskt) b AS basket lateral view explode(basket.itm) i AS item -
==============================
2.자세한 솔루션을 주셔서 감사합니다. 나는 그것을 테스트하고 완벽하게 잘 작동했다. 나는 XML의 serde와 직접 XML에서 데이터를 읽을 유사한 접근 방식을 시도했다.
자세한 솔루션을 주셔서 감사합니다. 나는 그것을 테스트하고 완벽하게 잘 작동했다. 나는 XML의 serde와 직접 XML에서 데이터를 읽을 유사한 접근 방식을 시도했다.
내 도전 과제 :
1)XML to JSON conversion takes additional development efforts and we don't have Apache Nifi installation parcels in Cloudera by default, we need to install it with custom parcels. 2) My data will definitely have spaces/tab spaces in it, especially in 'Item description' field.We need to load the data with the same names as we receive. So converting to JSON and use the 'org.openx.data.jsonserde.JsonSerDe' didn't help. Queries failed with errors as suggested by you.다음은 하이브 테이블 구조와 내가 데이터를 읽을 사용하고있는 쿼리입니다. 나는 어떤 문제없이 성공적으로 첫 번째 레벨 어레이 (Bskt)을 폭발 할 수 있어요.
내가 두 번째 수준의 배열을 폭발하려고 할 때 (ITM은)는 'ITM'의 모든 필드에 대한 NULL 결과를 반환합니다.
내 쿼리 또는 테이블 구조 자체에 어떤 문제가 있습니까?
create external table nanda_scan_xml ( Version string, StoreId string, Bskt array<struct< Bskt:struct< DateTime:string, TillNo:string, BsktNo:string, Itm:array<struct< Itm:struct< ItmSeq:string, GTIN:string, ItmDsc:string, DeptCD:string, ItmCD:string, SalesQTY:string, SalesExGST:string, Points:string, CostExGST:string, GSTRate:string, DiscAmtExGST:string, ItmProm:struct< PromCD:string, OfferID:string > > > > > > > ) row format serde 'com.ibm.spss.hive.serde2.xml.XmlSerDe' with serdeproperties ( "column.xpath.Version" = "/Store/Version/text()", "column.xpath.StoreId" = "/Store/StoreId/text()", "column.xpath.Bskt" = "/Store/Bskt" ) stored as inputformat 'com.ibm.spss.hive.serde2.xml.XmlInputFormat' outputformat 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' LOCATION 'hdfs://namenode/LandingArea/Sources/SCANP/IGA_SCAN/STAGING/' tblproperties ( "xmlinput.start" = "<Store>", "xmlinput.end" = "</Store>" );질문:
1) 잘 작동하는 Bskt의 경우 :
SELECT Version, StoreId, basket.Bskt.DateTime, basket.Bskt.bsktno, basket.Bskt.tillno FROM eim_stg.nanda_scan_xml LATERAL VIEW EXPLODE(Bskt) b AS basket;결과 :
여기에 이미지 설명을 입력 2) 두 가지 측면 뷰는 단일 쿼리에서 폭발하려고 할 때 :
SELECT Version, StoreId, basket.Bskt.DateTime, basket.Bskt.bsktno, basket.Bskt.tillno, item.Itm.ItmSeq, item.Itm.ItmDsc, item.Itm.GTIN, item.Itm.itmprom.OfferID, item.Itm.itmprom.PromCD FROM eim_stg.nanda_scan_xml LATERAL VIEW EXPLODE(Bskt) b AS basket LATERAL VIEW EXPLODE(basket.Bskt.Itm) i AS item limit 100;결과 :
여기에 이미지 설명을 입력
3) 검색어 :
SELECT Version, StoreId, basket.Bskt.DateTime, basket.Bskt.bsktno, basket.Bskt.tillno, item.Itm.ItmSeq, item.Itm.ItmDsc, item.Itm.GTIN, item.Itm.itmprom.OfferID, item.Itm.itmprom.PromCD FROM eim_stg.nanda_scan_xml LATERAL VIEW EXPLODE(Bskt) b AS basket LATERAL VIEW EXPLODE(basket.Itm) i AS item limit 100;오류:
여기에 이미지 설명을 입력
from https://stackoverflow.com/questions/53384728/how-to-use-lateral-view-explode-in-hive-for-xml-data-format by cc-by-sa and MIT license
'HADOOP' 카테고리의 다른 글
| [HADOOP] MySQL의와 하이브 JDBC 연결 설정 또는 매핑 (0) | 2019.10.06 |
|---|---|
| [HADOOP] 어떻게 작업이 완료되기 전에 하둡에 감소 / 전체지도를 다시 실행하려면? (0) | 2019.10.06 |
| [HADOOP] 하이브 - 버킷 팅 및 파티셔닝 (0) | 2019.10.06 |
| [HADOOP] 돼지에서 CASE 문 (0) | 2019.10.06 |
| [HADOOP] Oozie 작업 제출 실패 (0) | 2019.10.06 |