Download User`s Guide - Oracle Documentation

Creating XML Tables
<comments>
<comment id="12345" user="john" text="It is raining :( "/>
<comment id="56789" user="kelly" text="I won the lottery!">
<like user="john"/>
<like user="mike"/>
</comment>
<comment id="54321" user="mike" text="Happy New Year!">
<like user="laura"/>
</comment>
</comments>
In the CREATE TABLE examples, the comments.xml input file is in the current
working directory of the local file system.
Example 6-4
Creating a Table
The following Hive CREATE TABLE command creates a table named COMMENTS with
a row for each comment containing the user names, text, and number of likes:
hive>
CREATE TABLE comments (usr STRING, content STRING, likeCt INT)
ROW FORMAT
SERDE 'oracle.hadoop.xquery.hive.OXMLSerDe'
STORED AS
INPUTFORMAT 'oracle.hadoop.xquery.hive.OXMLInputFormat'
OUTPUTFORMAT 'oracle.hadoop.xquery.hive.OXMLOutputFormat'
TBLPROPERTIES(
"oxh-elements" = "comment",
"oxh-column.usr" = "./@user",
"oxh-column.content" = "./@text",
"oxh-column.likeCt" = "fn:count(./like)"
);
The Hive LOAD DATA command loads comments.xml into the COMMENTS table. See
“Simple Examples” for the contents of the file.
hive> LOAD DATA LOCAL INPATH 'comments.xml' OVERWRITE INTO TABLE comments;
]
The following query shows the content of the COMMENTS table.
hive> SELECT usr, content,
.
.
.
john It is raining :(
kelly I won the lottery!
mike Happy New Year!
Example 6-5
likeCt FROM comments;
0
2
1
Querying an XML Column
This CREATE TABLE command is like Example 6-4, except that the like elements are
produced as XML in a STRING column.
hive>
CREATE TABLE comments2 (usr STRING, content STRING, likes STRING)
ROW FORMAT
SERDE 'oracle.hadoop.xquery.hive.OXMLSerDe'
STORED AS
INPUTFORMAT 'oracle.hadoop.xquery.hive.OXMLInputFormat'
OUTPUTFORMAT 'oracle.hadoop.xquery.hive.OXMLOutputFormat'
TBLPROPERTIES(
"oxh-elements" = "comment",
Oracle XML Extensions for Hive 6-7