XmlSerDe
========

Need to download jar "com.ibm.spss.hive.serde2.xml.XmlSerDe" and add to hive.

Download jar from https://repo1.maven.org/maven2/com/ibm/spss/hive/serde2/xml/hivexmlserde/1.0.0.0/hivexmlserde-1.0.0.0.jar

hive> add jar hivexmlserde-1.0.0.0.jar

drop table book_details;

CREATE EXTERNAL TABLE book_details
(  TITLE STRING
 , AUTHOR STRING
 , COUNTRY STRING
 , COMPANY STRING
 , PRICE FLOAT
 , YEAR INT
)
ROW FORMAT SERDE 'com.ibm.spss.hive.serde2.xml.XmlSerDe'
WITH SERDEPROPERTIES 
( "column.xpath.TITLE"="/BOOK/TITLE/text()",
  "column.xpath.AUTHOR"="/BOOK/AUTHOR/text()",
  "column.xpath.COUNTRY"="/BOOK/COUNTRY/text()",
  "column.xpath.COMPANY"="/BOOK/COMPANY/text()",
  "column.xpath.PRICE"="/BOOK/PRICE/text()",
  "column.xpath.YEAR"="/BOOK/YEAR/text()"
)
STORED AS INPUTFORMAT 'com.ibm.spss.hive.serde2.xml.XmlInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
TBLPROPERTIES ("xmlinput.start"="<BOOK","xmlinput.end"= "</BOOK>");

MultiDelimitSerDe
=================

DROP TABLE movie_rating;

CREATE EXTERNAL TABLE IF NOT EXISTS  movie_rating
(  userId        VARCHAR(10)
 , movieId       VARCHAR(20)
 , rating        VARCHAR(5)
 , movietime     timestamp
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe'
WITH SERDEPROPERTIES ("field.delim"="::")
TBLPROPERTIES ("skip.header.line.count"="1");