Reading XML files using Hive

Scenario 1:
Consider an XML file as below

<row Id="1" Reputation="100" creationDate="2008-07-31T14:22:31.317" DisplayName="Test 1" LastAccessDate="2016-12-10T22:12:46.367" WebsiteUrl="http://www.joelonsoftware.com/" Location="New York, NY" />
<row Id="2" Reputation="250" creationDate="2008-07-31T14:22:31.317" DisplayName="Test User2" LastAccessDate="2016-12-10T22:12:46.367" WebsiteUrl="http://www.test.com/" Location="Phoenix, AZ"  Age="25"/>

Download Jar:

wget http://search.maven.org/remotecontent?filepath=com/ibm/spss/hive/serde2/xml/hivexmlserde/1.0.5.3/hivexmlserde-1.0.5.3.jar

From Hive CLI/Beeline:

add jar <above_jar_location>;

CREATE EXTERNAL TABLE test_xml(
Id int,
Reputation int,
creationDate timestamp,
displayName string,
location string,
age int
)
ROW FORMAT SERDE 'com.ibm.spss.hive.serde2.xml.XmlSerDe'
WITH SERDEPROPERTIES (
"column.xpath.id"="/row/@Id",
"column.xpath.Reputation"="/row/@Reputation",
"column.xpath.creationDate"="/row/@CreationDate",
"column.xpath.displayName"="/row/@DisplayName",
"column.xpath.location"="/row/@Location",
"column.xpath.age"="/row/@Age"
)
STORED AS
INPUTFORMAT 'com.ibm.spss.hive.serde2.xml.XmlInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
LOCATION '/path/to/xml/file'
TBLPROPERTIES (
"xmlinput.start"="<row ",
"xmlinput.end"="/>"
);

Scenario 2:

Consider Below XML:

<CATALOG>

<BOOK>

<TITLE>Hadoop Defnitive Guide</TITLE>

<AUTHOR>Tom White</AUTHOR>

<CURRENCY>USD</CURRENCY>

<PRICE>34</PRICE>

<YEAR>2017</YEAR>

</BOOK>

<BOOK>

<TITLE>Programming with Spark</TITLE>

<AUTHOR>I don't know</AUTHOR>

<CURRENCY>USA</CURRENCY>

<PRICE>29</PRICE>

<YEAR>2018</YEAR>

</BOOK>

</CATALOG>

Create table as below:

CREATE EXTERNAL TABLE books (title string, price float,currency string)

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

WITH SERDEPROPERTIES (

"column.xpath.title"="/BOOK/TITLE/text()",

"column.xpath.currency"="/BOOK/CURRENCY/text()",

"column.xpath.price"="/BOOK/PRICE/text()")

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

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

LOCATION '/path/to/xml'

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

**** Do not specify the Root node i.e <CATALOG> in TBLPROPERTIES****

Calculating Cumulative totals/Running totals in Hive

Hi All,

Many of us might have come across a requirement to implement running totals. Below are a few of the implementations.

Consider a bank maintains account and balance details as below.

Running total should give the sum of balance from beginning to current month.

SELECT acc_no, acc_type,Yr_Mnth, bal, SUM(bal) OVER (PARTITION BY (acc_no,acc_type) ORDER BY Yr_Mnth)  as running_total FROM bnk_bal;

If rolling has to happen to a fixed number of rows, we can use RANGE BETWEEN.

ex: Below query will give the running total for every 3 months.

SELECT acc_no, acc_type,Yr_Mnth, bal, SUM(bal) OVER (PARTITION BY (acc_no,acc_type) ORDER BY Yr_Mnth RANGE BETWEEN PRECEDING 2 ROWS AND CURRENT ROW)  as running_total FROM bnk_bal;

Handling skew data in Hive

Method 1: 
Identify the skew value and run the job for that value separately.
ex: If cust_id=100 has skew problem, then divide the records into cust_id=100 and cust_id!=100. then run the individual jobs.
Method 2:
Identify the column creating skew. If it is used for join, try to reduce the skew using multiple columns and use it in join.
Method 3:
Modify the join key(Salting). A simple approach that i follow some times is appending (key%3) at the end of key. ex: key1 can be divided into multiple values like key1_1,key1_2 etc,. This will help distribute the keys.
Method 4:
Divide the data into chunks and execute.

SCD Type1 implementation in Hive

Consider below tables with partitions.

Stage table Final Table
p1 p1
p2 p2
p5 p3
p4

stage_partition left join final_partition on partition columns
Set1: Consider the data from stage table where there is a match - Updates are available for the data in these partitions
Set2: Consider the data from stage table where there is no match - New records (Inserts)

create table final_partition_updates as select * from final_table where partition_col in (set1);
create table stage_partition_updates as select * from stage_table where partition_col in (set1);

WITH pkeyJoin as select a.*,b.* from final_partition_updates a full join stage_partition_updates b
on a.pkey = b.pkey

create table upserts as
select b.* from pkeyJoin where a.pkey is not null  --Updates
UNION ALL
select b.* from pkeyJoin where a.pkey is null; --Inserts

Insert into table upserts select * from stage_table where partition_col in (set2);

Insert overwrite final_table PARTITION(partition column) SELECT * from upserts;

Hive SHOW commands

List only column names in table:
show columns from <tableName>;
List partitions:
show partitions <tableName>;