Hive XML Serde : Parsing error when loading xml content

0

I am trying to load the following XML content using SerDe with Hive :

<?xml version="1.0"?>

<RootTag xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.website.com/service">

<Code>123</Code>

<ParentElement>

   <Entity>

      <EntityId>A</EntityId>

      <EntityCode i:nil="true"/>

   </Entity>

   <Entity>

      <EntityId>M</EntityId>

      <EntityCode i:nil="true"/>

   </Entity>

</ParentElement>

</RootTag>

The hive table was created as follows:

CREATE EXTERNAL TABLE database.mytable(

code   String, 

Entity array<struct<Entity:struct<EntityId:String,EntityCode:String>>>

)

ROW FORMAT SERDE 'com.ibm.spss.hive.serde2.xml.XmlSerDe'

WITH SERDEPROPERTIES 

(

  "column.xpath.Code" = "/RootTag/Code/text()",

  "column.xpath.ParentElement" = "/RootTag/ParentElement"

)

STORED AS 

INPUTFORMAT  'com.ibm.spss.hive.serde2.xml.XmlInputFormat'

OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'

LOCATION     '/xml_content/'

TBLPROPERTIES ("xmlinput.start" = "<RootTag", "xmlinput.end" = "</RootTag>");

I have 2 issues:

1. when I specify the "xmlinput.start" as specified above, it doesn't work. I had to remove manually the content "xmlns:i = ... /service" next to the "RootTage" in order to start parsing the xml.


2. Even though, there is another issue with "EntityCode" attribute. I am getting the error:

Caused by: org.apache.hive.service.cli.HiveSQLException:

  java.io.IOException:

  org.apache.hadoop.hive.serde2.SerDeException: 

  java.lang.RuntimeException:

  org.xml.sax.SAXParseException;

  lineNumber: 41;

  columnNumber: 33;

  The prefix "i" for attribute "i:nil" associated with an element type "ParentCode" is not bound.

What am I doing wrong? I would appreciate your suggestions and comments regarding this.

xml parsing hadoop hive hive-serde

share|improve this question

edited Nov 19 '18 at 17:06

E_net4 wishes happy holidays

11.9k63468

asked Nov 19 '18 at 14:03

ilmar

11

add a comment | 

0

I am trying to load the following XML content using SerDe with Hive :

<?xml version="1.0"?>

<RootTag xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.website.com/service">

<Code>123</Code>

<ParentElement>

   <Entity>

      <EntityId>A</EntityId>

      <EntityCode i:nil="true"/>

   </Entity>

   <Entity>

      <EntityId>M</EntityId>

      <EntityCode i:nil="true"/>

   </Entity>

</ParentElement>

</RootTag>

The hive table was created as follows:

CREATE EXTERNAL TABLE database.mytable(

code   String, 

Entity array<struct<Entity:struct<EntityId:String,EntityCode:String>>>

)

ROW FORMAT SERDE 'com.ibm.spss.hive.serde2.xml.XmlSerDe'

WITH SERDEPROPERTIES 

(

  "column.xpath.Code" = "/RootTag/Code/text()",

  "column.xpath.ParentElement" = "/RootTag/ParentElement"

)

STORED AS 

INPUTFORMAT  'com.ibm.spss.hive.serde2.xml.XmlInputFormat'

OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'

LOCATION     '/xml_content/'

TBLPROPERTIES ("xmlinput.start" = "<RootTag", "xmlinput.end" = "</RootTag>");

I have 2 issues:

1. when I specify the "xmlinput.start" as specified above, it doesn't work. I had to remove manually the content "xmlns:i = ... /service" next to the "RootTage" in order to start parsing the xml.


2. Even though, there is another issue with "EntityCode" attribute. I am getting the error:

Caused by: org.apache.hive.service.cli.HiveSQLException:

  java.io.IOException:

  org.apache.hadoop.hive.serde2.SerDeException: 

  java.lang.RuntimeException:

  org.xml.sax.SAXParseException;

  lineNumber: 41;

  columnNumber: 33;

  The prefix "i" for attribute "i:nil" associated with an element type "ParentCode" is not bound.

What am I doing wrong? I would appreciate your suggestions and comments regarding this.

xml parsing hadoop hive hive-serde

share|improve this question

edited Nov 19 '18 at 17:06

E_net4 wishes happy holidays

11.9k63468

asked Nov 19 '18 at 14:03

ilmar

11

add a comment | 

0

0

0

I am trying to load the following XML content using SerDe with Hive :

<?xml version="1.0"?>

<RootTag xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.website.com/service">

<Code>123</Code>

<ParentElement>

   <Entity>

      <EntityId>A</EntityId>

      <EntityCode i:nil="true"/>

   </Entity>

   <Entity>

      <EntityId>M</EntityId>

      <EntityCode i:nil="true"/>

   </Entity>

</ParentElement>

</RootTag>

The hive table was created as follows:

CREATE EXTERNAL TABLE database.mytable(

code   String, 

Entity array<struct<Entity:struct<EntityId:String,EntityCode:String>>>

)

ROW FORMAT SERDE 'com.ibm.spss.hive.serde2.xml.XmlSerDe'

WITH SERDEPROPERTIES 

(

  "column.xpath.Code" = "/RootTag/Code/text()",

  "column.xpath.ParentElement" = "/RootTag/ParentElement"

)

STORED AS 

INPUTFORMAT  'com.ibm.spss.hive.serde2.xml.XmlInputFormat'

OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'

LOCATION     '/xml_content/'

TBLPROPERTIES ("xmlinput.start" = "<RootTag", "xmlinput.end" = "</RootTag>");

I have 2 issues:

1. when I specify the "xmlinput.start" as specified above, it doesn't work. I had to remove manually the content "xmlns:i = ... /service" next to the "RootTage" in order to start parsing the xml.


2. Even though, there is another issue with "EntityCode" attribute. I am getting the error:

Caused by: org.apache.hive.service.cli.HiveSQLException:

  java.io.IOException:

  org.apache.hadoop.hive.serde2.SerDeException: 

  java.lang.RuntimeException:

  org.xml.sax.SAXParseException;

  lineNumber: 41;

  columnNumber: 33;

  The prefix "i" for attribute "i:nil" associated with an element type "ParentCode" is not bound.

What am I doing wrong? I would appreciate your suggestions and comments regarding this.

xml parsing hadoop hive hive-serde

share|improve this question

edited Nov 19 '18 at 17:06

E_net4 wishes happy holidays

11.9k63468

asked Nov 19 '18 at 14:03

ilmar

11

I am trying to load the following XML content using SerDe with Hive :

<?xml version="1.0"?>

<RootTag xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.website.com/service">

<Code>123</Code>

<ParentElement>

   <Entity>

      <EntityId>A</EntityId>

      <EntityCode i:nil="true"/>

   </Entity>

   <Entity>

      <EntityId>M</EntityId>

      <EntityCode i:nil="true"/>

   </Entity>

</ParentElement>

</RootTag>

The hive table was created as follows:

CREATE EXTERNAL TABLE database.mytable(

code   String, 

Entity array<struct<Entity:struct<EntityId:String,EntityCode:String>>>

)

ROW FORMAT SERDE 'com.ibm.spss.hive.serde2.xml.XmlSerDe'

WITH SERDEPROPERTIES 

(

  "column.xpath.Code" = "/RootTag/Code/text()",

  "column.xpath.ParentElement" = "/RootTag/ParentElement"

)

STORED AS 

INPUTFORMAT  'com.ibm.spss.hive.serde2.xml.XmlInputFormat'

OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'

LOCATION     '/xml_content/'

TBLPROPERTIES ("xmlinput.start" = "<RootTag", "xmlinput.end" = "</RootTag>");

I have 2 issues:

1. when I specify the "xmlinput.start" as specified above, it doesn't work. I had to remove manually the content "xmlns:i = ... /service" next to the "RootTage" in order to start parsing the xml.


2. Even though, there is another issue with "EntityCode" attribute. I am getting the error:

Caused by: org.apache.hive.service.cli.HiveSQLException:

  java.io.IOException:

  org.apache.hadoop.hive.serde2.SerDeException: 

  java.lang.RuntimeException:

  org.xml.sax.SAXParseException;

  lineNumber: 41;

  columnNumber: 33;

  The prefix "i" for attribute "i:nil" associated with an element type "ParentCode" is not bound.

What am I doing wrong? I would appreciate your suggestions and comments regarding this.

xml parsing hadoop hive hive-serde

xml parsing hadoop hive hive-serde

share|improve this question

edited Nov 19 '18 at 17:06

E_net4 wishes happy holidays

11.9k63468

asked Nov 19 '18 at 14:03

ilmar

11

share|improve this question

edited Nov 19 '18 at 17:06

E_net4 wishes happy holidays

11.9k63468

asked Nov 19 '18 at 14:03

ilmar

11

share|improve this question

share|improve this question

edited Nov 19 '18 at 17:06

E_net4 wishes happy holidays

11.9k63468

edited Nov 19 '18 at 17:06

E_net4 wishes happy holidays

11.9k63468

edited Nov 19 '18 at 17:06

E_net4 wishes happy holidays

11.9k63468

11.9k63468

asked Nov 19 '18 at 14:03

ilmar

11

asked Nov 19 '18 at 14:03

ilmar

11

asked Nov 19 '18 at 14:03

ilmar

11

11

add a comment | 

add a comment | 

1 Answer
1

active

oldest

votes

0

There are few things.

• I think the root tag with namespace is only supported on version 1.5.0.3 or greater. check the following link https://github.com/dvasilen/Hive-XML-SerDe/issues/15


• I am don't think it supports the i:nil="true"


• Mapping of column should match the column name and it's case sensitive. column.xpath.Code and code won't work. Also there is no corrosponding column for column.xpath.ParentElement
  

So what you need to do?

• Upgrade your serde.


• replace <EntityCode i:nil="true"/> to <EntityCode/> in your xml


• Fix the code as mentioned below

Code

CREATE EXTERNAL TABLE temp.test_xml(

code   String,

Entity array<struct<Entity:struct<EntityId:String,EntityCode:String>>>

)

ROW FORMAT SERDE 'com.ibm.spss.hive.serde2.xml.XmlSerDe'

WITH SERDEPROPERTIES 

(

  "column.xpath.code" = "/RootTag/Code/text()",

  "column.xpath.Entity" = "/RootTag/ParentElement/Entity"



)

STORED AS 

INPUTFORMAT  'com.ibm.spss.hive.serde2.xml.XmlInputFormat'

OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'

LOCATION     '/tmp/test_xml1/table/'

TBLPROPERTIES (

                "xmlinput.start"="<RootTag",

                "xmlinput.end"="</RootTag>");

share|improve this answer

answered Nov 19 '18 at 20:18

Gaurang Shah

2,94511333

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53376332%2fhive-xml-serde-parsing-error-when-loading-xml-content%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

active

oldest

votes

active

oldest

votes

0

There are few things.

• I think the root tag with namespace is only supported on version 1.5.0.3 or greater. check the following link https://github.com/dvasilen/Hive-XML-SerDe/issues/15


• I am don't think it supports the i:nil="true"


• Mapping of column should match the column name and it's case sensitive. column.xpath.Code and code won't work. Also there is no corrosponding column for column.xpath.ParentElement
  

So what you need to do?

• Upgrade your serde.


• replace <EntityCode i:nil="true"/> to <EntityCode/> in your xml


• Fix the code as mentioned below

Code

CREATE EXTERNAL TABLE temp.test_xml(

code   String,

Entity array<struct<Entity:struct<EntityId:String,EntityCode:String>>>

)

ROW FORMAT SERDE 'com.ibm.spss.hive.serde2.xml.XmlSerDe'

WITH SERDEPROPERTIES 

(

  "column.xpath.code" = "/RootTag/Code/text()",

  "column.xpath.Entity" = "/RootTag/ParentElement/Entity"



)

STORED AS 

INPUTFORMAT  'com.ibm.spss.hive.serde2.xml.XmlInputFormat'

OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'

LOCATION     '/tmp/test_xml1/table/'

TBLPROPERTIES (

                "xmlinput.start"="<RootTag",

                "xmlinput.end"="</RootTag>");

share|improve this answer

answered Nov 19 '18 at 20:18

Gaurang Shah

2,94511333

add a comment | 

0

There are few things.

• I think the root tag with namespace is only supported on version 1.5.0.3 or greater. check the following link https://github.com/dvasilen/Hive-XML-SerDe/issues/15


• I am don't think it supports the i:nil="true"


• Mapping of column should match the column name and it's case sensitive. column.xpath.Code and code won't work. Also there is no corrosponding column for column.xpath.ParentElement
  

So what you need to do?

• Upgrade your serde.


• replace <EntityCode i:nil="true"/> to <EntityCode/> in your xml


• Fix the code as mentioned below

Code

CREATE EXTERNAL TABLE temp.test_xml(

code   String,

Entity array<struct<Entity:struct<EntityId:String,EntityCode:String>>>

)

ROW FORMAT SERDE 'com.ibm.spss.hive.serde2.xml.XmlSerDe'

WITH SERDEPROPERTIES 

(

  "column.xpath.code" = "/RootTag/Code/text()",

  "column.xpath.Entity" = "/RootTag/ParentElement/Entity"



)

STORED AS 

INPUTFORMAT  'com.ibm.spss.hive.serde2.xml.XmlInputFormat'

OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'

LOCATION     '/tmp/test_xml1/table/'

TBLPROPERTIES (

                "xmlinput.start"="<RootTag",

                "xmlinput.end"="</RootTag>");

share|improve this answer

answered Nov 19 '18 at 20:18

Gaurang Shah

2,94511333

add a comment | 

0

0

0

There are few things.

• I think the root tag with namespace is only supported on version 1.5.0.3 or greater. check the following link https://github.com/dvasilen/Hive-XML-SerDe/issues/15


• I am don't think it supports the i:nil="true"


• Mapping of column should match the column name and it's case sensitive. column.xpath.Code and code won't work. Also there is no corrosponding column for column.xpath.ParentElement
  

So what you need to do?

• Upgrade your serde.


• replace <EntityCode i:nil="true"/> to <EntityCode/> in your xml


• Fix the code as mentioned below

Code

CREATE EXTERNAL TABLE temp.test_xml(

code   String,

Entity array<struct<Entity:struct<EntityId:String,EntityCode:String>>>

)

ROW FORMAT SERDE 'com.ibm.spss.hive.serde2.xml.XmlSerDe'

WITH SERDEPROPERTIES 

(

  "column.xpath.code" = "/RootTag/Code/text()",

  "column.xpath.Entity" = "/RootTag/ParentElement/Entity"



)

STORED AS 

INPUTFORMAT  'com.ibm.spss.hive.serde2.xml.XmlInputFormat'

OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'

LOCATION     '/tmp/test_xml1/table/'

TBLPROPERTIES (

                "xmlinput.start"="<RootTag",

                "xmlinput.end"="</RootTag>");

share|improve this answer

answered Nov 19 '18 at 20:18

Gaurang Shah

2,94511333

There are few things.

• I think the root tag with namespace is only supported on version 1.5.0.3 or greater. check the following link https://github.com/dvasilen/Hive-XML-SerDe/issues/15


• I am don't think it supports the i:nil="true"


• Mapping of column should match the column name and it's case sensitive. column.xpath.Code and code won't work. Also there is no corrosponding column for column.xpath.ParentElement
  

So what you need to do?

• Upgrade your serde.


• replace <EntityCode i:nil="true"/> to <EntityCode/> in your xml


• Fix the code as mentioned below

Code

CREATE EXTERNAL TABLE temp.test_xml(

code   String,

Entity array<struct<Entity:struct<EntityId:String,EntityCode:String>>>

)

ROW FORMAT SERDE 'com.ibm.spss.hive.serde2.xml.XmlSerDe'

WITH SERDEPROPERTIES 

(

  "column.xpath.code" = "/RootTag/Code/text()",

  "column.xpath.Entity" = "/RootTag/ParentElement/Entity"



)

STORED AS 

INPUTFORMAT  'com.ibm.spss.hive.serde2.xml.XmlInputFormat'

OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'

LOCATION     '/tmp/test_xml1/table/'

TBLPROPERTIES (

                "xmlinput.start"="<RootTag",

                "xmlinput.end"="</RootTag>");

share|improve this answer

answered Nov 19 '18 at 20:18

Gaurang Shah

2,94511333

share|improve this answer

share|improve this answer

answered Nov 19 '18 at 20:18

Gaurang Shah

2,94511333

answered Nov 19 '18 at 20:18

Gaurang Shah

2,94511333

answered Nov 19 '18 at 20:18

Gaurang Shah

2,94511333

2,94511333

add a comment | 

add a comment | 

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53376332%2fhive-xml-serde-parsing-error-when-loading-xml-content%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

This page is only for reference, If you need detailed information, please check here