xml到配置单元表Map阵列

q35jwt9p  于 2021-06-26  发布在  Hive
关注(0)|答案(1)|浏览(320)

我的xml看起来像这样

<TAG>
  <REQUEST_ID>1</REQUEST_ID>
  <APPLICATION_ID>2</APPLICATION_ID>
  <EXTERNAL_SYSTEM_CODE>RB</EXTERNAL_SYSTEM_CODE>
  <CCM_CHECK>
    <CCM_CHECK_ID>101</CCM_CHECK_ID>
    <CCM_CHECK_RESULT>10</CCM_CHECK_RESULT>
  </CCM_CHECK>
  <VERIF_ANSWERS>
    <CHECK_CODE>101</CHECK_CODE>
    <QUESTION_CODE>1</QUESTION_CODE>
    <BOOKMARK_NUMBER>1</BOOKMARK_NUMBER>
    <ANSWER_VALUE>NN</ANSWER_VALUE>
  </VERIF_ANSWERS>
  <VERIF_ANSWERS>
    <CHECK_CODE>101</CHECK_CODE>
    <QUESTION_CODE>2</QUESTION_CODE>
    <BOOKMARK_NUMBER>1</BOOKMARK_NUMBER>
    <ANSWER_VALUE>NN</ANSWER_VALUE>
  </VERIF_ANSWERS>
</TAG>

这就是我如何从中创建表的方法

CREATE EXTERNAL TABLE s_sourcedata.evkuzmin_test_xml(
  request_id string
  , application_id string
  , external_system_code string
  , ccm_check map<string, string>
  , verif_answers array<struct<verif_answer:array<map<string, string>>>>
)
ROW FORMAT SERDE 'com.ibm.spss.hive.serde2.xml.XmlSerDe'
WITH SERDEPROPERTIES (
  "column.xpath.request_id"="/TAG/REQUEST_ID/text()",
  "column.xpath.application_id"="/TAG/APPLICATION_ID/text()",
  "column.xpath.external_system_code"="/TAG/EXTERNAL_SYSTEM_CODE/text()",
  "column.xpath.ccm_check"="/TAG/CCM_CHECK/*",
  "column.xpath.verif_answers"="/TAG")
STORED AS
INPUTFORMAT 'com.ibm.spss.hive.serde2.xml.XmlInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
LOCATION '/storage/s_sourcedata/db/evkuzmin_test_xml'
TBLPROPERTIES (
  "xmlinput.start"="<TAG",
  "xmlinput.end"="</TAG>"
);

结果如下

1,2,RB,"{""CCM_CHECK_ID"":""101"",""CCM_CHECK_RESULT"":""10""}","[{""verif_answer"":null}]"

我怎样才能转身 verif_answers 像我为 ccm_check ?
我试着像以前那样做 ccm_check ? 但只有第一个 VERIF_ANSWERS .
数量 VERIF_ANSWERS 可能会有所不同。在这种情况下有两个?但是可以是0或10。

ubof19bj

ubof19bj1#

CREATE EXTERNAL TABLE myxml(
  request_id string
  , application_id string
  , external_system_code string
  , ccm_check map<string, string>
  , verif_answers array<map<string, string>>
)
ROW FORMAT SERDE 'com.ibm.spss.hive.serde2.xml.XmlSerDe'
WITH SERDEPROPERTIES (
  "column.xpath.request_id"="/TAG/REQUEST_ID/text()",
  "column.xpath.application_id"="/TAG/APPLICATION_ID/text()",
  "column.xpath.external_system_code"="/TAG/EXTERNAL_SYSTEM_CODE/text()",
  "column.xpath.ccm_check"="/TAG/CCM_CHECK/*",
  "column.xpath.verif_answers"="/TAG/VERIF_ANSWERS/*")
STORED AS
INPUTFORMAT 'com.ibm.spss.hive.serde2.xml.XmlInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
LOCATION 'file:///home/cloudera/xmlfiles'
TBLPROPERTIES (
  "xmlinput.start"="<TAG",
  "xmlinput.end"="</TAG>"
);

select myxml.verif_answers from myxml;

INFO  : OK
+----------------------------------------------------+--+
|                myxml.verif_answers                 |
+----------------------------------------------------+--+
| [{"CHECK_CODE":"101"},{"QUESTION_CODE":"1"},{"BOOKMARK_NUMBER":"1"},{"ANSWER_VALUE":"NN"},{"CHECK_CODE":"101"},{"QUESTION_CODE":"2"},{"BOOKMARK_NUMBER":"1"},{"ANSWER_VALUE":"NN"}] |
+----------------------------------------------------+--+
1 row selected (0.306 seconds)

相关问题