I have the sample XML file, Sorry for editing the sample xml missed some tags
<?xml version="1.0"?>
<wd:File xmlns:wd="urn:com.workday/bsvc">
<wd:Report_Entry>
<wd:All_Candidate_Job_Profiles wd:Descriptor="Manager I">
<wd:ID wd:type="WID">e83ebdbd2a0a013fcbb5009a49e9d9d4</wd:ID>
<wd:ID wd:type="Job_Profile_ID">US-100017363</wd:ID>
</wd:All_Candidate_Job_Profiles>
<wd:All_Companies_as_Text>Xyz, Inc.</wd:All_Companies_as_Text>
<wd:All_Watched_Companies>0</wd:All_Watched_Companies>
</wd:Report_Entry>
<wd:Report_Entry>
<wd:All_Candidate_Job_Profiles wd:Descriptor="Manager II">
<wd:ID wd:type="WID">e83ebdbd2a0a013fcbb5009a49e9d9d4</wd:ID>
<wd:ID wd:type="Job_Profile_ID">US-100017363</wd:ID>
</wd:All_Candidate_Job_Profiles>
<wd:All_Companies_as_Text>abc, Inc.</wd:All_Companies_as_Text>
<wd:All_Watched_Companies>0</wd:All_Watched_Companies>
</wd:Report_Entry>
</wd:File>
我正在尝试使用以下查询加载数据:
CREATE external TABLE ww_hr_dl_staging_hiring.recruiting_candidates_serdie(
All_Candidate_Job_Profiles array<string>,
All_Companies_as_Text string,
All_Watched_Companies string
)
ROW FORMAT SERDE 'com.ibm.spss.hive.serde2.xml.XmlSerDe'
WITH SERDEPROPERTIES (
'column.xpath.All_Companies_as_Text'='wd:File/wd:Report_Entry/*
[local-name()=\"wd:All_Companies_as_Text\"]/text()',
'column.xpath.All_Watched_Companies'='wd:File/wd:Report_Entry/*
[local-name()=\"wd:All_Watched_Companies\"]/text()'
)
STORED AS
INPUTFORMAT 'com.ibm.spss.hive.serde2.xml.XmlInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
TBLPROPERTIES (
'xmlinput.start'='<wd:File xmlns',
'xmlinput.end'='</wd:File>'
);
使用load data inpath'path'将数据从hdfs加载到'tablename'
从查询中选择数据之后,我得到的是空值
从ww\u hr\u dl\u staging\u rilling.recruining\u candidates\u serdie中选择*;
确定空值空值
My output should retreive
Manager I Xyz, Inc 0
Manager II abc inc 0
1条答案
按热度按时间7cwmlq891#
这是因为您使用的xpath与任何元素都不匹配。正确的xpath应该是。
//[本地名称(.)=“所有公司作为\u文本”]/text()
//[本地名称(.)=“所有公司”]/text()
这是工作代码