Hive for SQL Queries: How to Easily Ingest XML

Team Zaloni August 6th, 2017

When recently working with a customer, I had the misfortune of brushing into a well-known trope within the world of data and programming: XML is HARD. As part of a set of exploratory requirements, the first thing this customer said was, “Let’s start with something simple- let’s bring some XML into Hive for SQL queries”. Although this exposes the dichotomy between business and technology as a whole, this is a topic for another day; today, instead, I’ll focus on something a little less controversial, namely, using standard Hadoop components to ingest common business-facing data sources as quickly as easily as possible. Specifically, we’ll look at ingesting XML into Hive for SQL queries.

For our sample data, let’s look at some publicly available US Treasury Auction data. These all have fairly simple schemas, but are good examples of a common source of data that many FinServ customers might use. This page alone consists of about 25,000 XML files, some containing single auction announcements, and others containing series of multiple records. To process these records in Hive, we have a couple of options; we could try to do some Python or Java parsing prior to ingesting the files, or we could write some UDFs to handle our schema. In the spirit of simplicity, we’ll opt to use the widely-available (and free!) XML SerDe. This allows as close to “plug and play” functionality as is possible with Hive, with no custom scripting or coding required; in exchange, you’ll have to accept a few caveats and limitations as to how you deal with your data. For most customers, this tradeoff is more than acceptable!

Once you’ve downloaded the SerDe JAR, there are a few ways to actually make it useable:

• In Beeline, execute add jar /path/to/jar/hivexmlserde-1.0.5.3.jar (replacing 1.0.5.3 with the version you downloaded). This will only apply to the current Hive session; if you disconnect or restart Hive, you’ll have to re-execute this command.
• Add the jar to $HIVE_HOME/lib/ or the path set in hive-site.xml under the hive.aux.jars.path property (note you will probably have to set this manually, as it may not be set by default). Then, restart HiveServer2 to include this jar in the path. To check if the SerDe is included, you can execute ps –ef | grep hivexmlserde and look for your hive process.

Now that your SerDe is loaded, you can create tables directly from XML files as if they were a flat file. There are a few caveats to consider when using the SerDe, but for the most part, it’s a simple process, even with highly nested files. For example, let’s pull in one of the files from the Treasury repository; we’ll look at one of the files with an “A_” prefix. For brevity, we’ll consider only a few of the fields, since the full file includes 50+ attributes; let’s say we want to pull in auction date, maturity date, and total maturity amount. The schema for these files will look like this:

<bpd:AuctionData xmlns:bpd=”https://www.treasurydirect.gov/” xmlns:xsi=”https://www.w3.org/2001/XMLSchema-instance”xsi:schemaLocation=”https://www.treasurydirect.gov/ https://www.treasurydirect.gov/xsd/Auction_v1_0_0.xsd”>

<AuctionAnnouncement>

<…/>

<AuctionDate>2008-04-28</AuctionDate>

<MaturityDate>2008-07-31</MaturityDate>

<MatureSecurityAmount>61989000000.0</MatureSecurityAmount>

<…/>

</AuctionAnnouncement>

</bpd:AuctionData> 

For this minimally-nested XML, we can write a simple table definition as follows: 

CREATE TABLE treasury.xml_auctions(

auctionDate string,

maturityDate string,

maturityAmt double 

 )

ROW FORMAT SERDE ‘com.ibm.spss.hive.serde2.xml.XmlSerDe’

WITH SERDEPROPERTIES (

“column.xpath.auctionDate”=”/AuctionAnnouncement/AuctionDate/text()”,

“column.xpath.maturityDate”=”/AuctionAnnouncement/MaturityDate/text()”

“column.xpath.maturityAmt”=”/AuctionAnnouncement/MatureSecurityAmount/text()”

)

STORED AS

INPUTFORMAT ‘com.ibm.spss.hive.serde2.xml.XmlInputFormat’

OUTPUTFORMAT ‘org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat’

TBLPROPERTIES (

“xmlinput.start”=”<AuctionAnnouncement”,

“xmlinput.end”=“</AuctionAnnouncement>”

);

Executing  SELECT * FROM treasury.xml_auctions; will return the auction date, maturity date, and maturity amount in a friendly, well-known  SQl  style, as if we were querying from a relational system.

+————–+—————+————–+–+

| auctionDate  | maturityDate  | maturityAmt  |

+————–+—————+————–+–+

|  2008-04-28  |   2008-07-31  |61989000000.0 |

+————–+—————+————–+–+

Now, we can create a script to download all of the 25,000 auction XML files, load them into a hive table, and create more advanced queries, joins, lateral views, and other Hive constructs quickly and easily. Compared to jumping through the hoops of parsing, formatting, maintaining and tracking these files manually, this process is straightforward, and offers business users the ability to query agnostically.

What about more complex XML files? The SerDe does have its limitations, specifically around custom namespaces, but it handles most reasonable schemas elegantly. If the XML tags contain attributes, for example, such as: 

<AuctionAnnouncement ID=”ABCD” Security=”EFGH” …/> 

We can still use XPath notation to pull out the ID attribute, simply by setting: 

“column.xpath.customer_id”=”/AuctionAnnouncement/@ID” 

The same goes for defining Map, Array and Struct datatypes; using a wildcard in your XPath can select an entire tag, along with any subtags, as a single field, which can then be mapped with native Hive types. 

Although XML processing is never truly easy, the open-source nature of Hadoop means that it does have plenty of support from committers around the world who deal with these types of problems every day. By incorporating these Hive for SQL queries recommendations, business can start to see real value in many of the stable features of the ecosystem; the ability to ingest, process, and query complex data types such as XML is only one example.

about the author

Team Zaloni

This team of authors from Team Zaloni provide their expertise, best practices, tips and tricks and use cases across varied topics incuding: data governance, data catalog, dataops, observability, and so much more.