xml - 如何从 XPath 返回的数组中将数据插入到配置单元表中

Question

我有一个配置单元查询，它使用 XPath 从 XML 返回一组数组。 我想将数组的那些元素插入到配置单元表中。

hivexml表中的xml内容为:

<tag><row Id="1" TagName=".net" Count="244006" ExcerptPostId="3624959" WikiPostId="3607476" /><row Id="2" TagName="html" Count="602809" ExcerptPostId="3673183" WikiPostId="3673182" /><row Id="3" TagName="javascript" Count="1274350" ExcerptPostId="3624960" WikiPostId="3607052" /><row Id="4" TagName="css" Count="434937" ExcerptPostId="3644670" WikiPostId="3644669" /><row Id="5" TagName="php" Count="1009113" ExcerptPostId="3624936" WikiPostId="3607050" /><row Id="8" TagName="c" Count="236386" ExcerptPostId="3624961" WikiPostId="3607013" /></tag>

返回数组集的查询是:

select xpath(str,'/tag/row/@Id'), xpath(str,'/tag/row/@TagName'), xpath(str,'/tag/row/@Count'), xpath(str,'/tag/row/@ExcerptPostId'), xpath(str,'/tag/row/@WikiPostId') from hivexml;"

上述查询的输出(数组集)是:

["1","2","3","4","5"] [".net","html","css","php","c"]   ["244006","602809","434937","1009113","236386"] ["3624959","3673183","3644670","3624936","3624961"] ["3607476","36
73182","3644669","3607050","3607013"]

我想以这种格式将这些值插入到配置单元表中:

1    .net    244006     3624959    3607476
2    html    602809     3673183    3673182
3    css     434937     3644670    3644669
4    php     1009113    3624936    3607050
5    c       236386     3624961    3607013

如果我对上面的选择查询进行插入:

insert into newhivexml select xpath(str,'/tags/row/@Id'), xpath(str,'/tag/row/@TagName'), xpath(str,'/tag/row/@Count'), xpath(str,'/tag/row/@ExcerptPostId'), xpath(str,'/tag/row/@WikiPostId') from hivexml;"

然后我得到一个错误:

NoMatchingMethodException No matching method for class org.apache.hadoop.hive.ql.udf.UDFToInteger with (array). Possible choices: FUNC(bigint) FUNC(boolean) FU NC(decimal(38,18)) FUNC(double) FUNC(float) FUNC(smallint) FUNC(string) FUNC(struct) FUNC(timestamp) FUNC(tinyin t) FUNC(void)

我认为我们不能像这样直接插入，我在这里缺少一些东西。谁能告诉我该怎么做？也就是说，将数组中的这些值插入到表中。

Answer 1

xpath_... (str,concat('/tag/row[',pe.pos+1,']/@...))

create table hivexml (str string);

insert into hivexml values ('<tag><row Id="1" TagName=".net" Count="244006" ExcerptPostId="3624959" WikiPostId="3607476" /><row Id="2" TagName="html" Count="602809" ExcerptPostId="3673183" WikiPostId="3673182" /><row Id="3" TagName="javascript" Count="1274350" ExcerptPostId="3624960" WikiPostId="3607052" /><row Id="4" TagName="css" Count="434937" ExcerptPostId="3644670" WikiPostId="3644669" /><row Id="5" TagName="php" Count="1009113" ExcerptPostId="3624936" WikiPostId="3607050" /><row Id="8" TagName="c" Count="236386" ExcerptPostId="3624961" WikiPostId="3607013" /></tag>');

---

select  xpath_int    (str,concat('/tag/row[',pe.pos+1,']/@Id'           )) as Id  
       ,xpath_string (str,concat('/tag/row[',pe.pos+1,']/@TagName'      )) as TagName
       ,xpath_int    (str,concat('/tag/row[',pe.pos+1,']/@Count'        )) as Count
       ,xpath_int    (str,concat('/tag/row[',pe.pos+1,']/@ExcerptPostId')) as ExcerptPostId
       ,xpath_int    (str,concat('/tag/row[',pe.pos+1,']/@WikiPostId'   )) as WikiPostId

from    hivexml
        lateral view  posexplode (xpath(str,'/tag/row/@Id')) pe
;

---

+----+------------+---------+---------------+------------+
| id |  tagname   |  count  | excerptpostid | wikipostid |
+----+------------+---------+---------------+------------+
|  1 | .net       |  244006 |       3624959 |    3607476 |
|  2 | html       |  602809 |       3673183 |    3673182 |
|  3 | javascript | 1274350 |       3624960 |    3607052 |
|  4 | css        |  434937 |       3644670 |    3644669 |
|  5 | php        | 1009113 |       3624936 |    3607050 |
|  8 | c          |  236386 |       3624961 |    3607013 |
+----+------------+---------+---------------+------------+