7 Oracle XML Extensions for Hive

Example 7-9 STRING Binding

This example parses and binds the input XML string to the initial context item of the query x/y:

Hive> SELECT xml_exists("x/y", "<x><y>123</y></x>") FROM src LIMIT 1;
     .
     .
     .
true

Example 7-10 STRUCT Binding

This example defines two query variables, $data and $value:

Hive> SELECT xml_exists(
      "parse-xml($data)/x/y[@id = $value]",
      struct(
         "data", "<x><y id='1'/><y id='2'/></x>",
         "value", 2
      )
   ) FROM src LIMIT 1;
     .
     .
     .
true

Example 7-11 Error Logging

In this example, an error is written to the log, because the input XML is invalid:

hive> SELECT xml_exists("x/y", "<x><y>123</invalid></x>") FROM src LIMIT 1;
     .
     .
     .
false

Example 7-12 Using a STRING Binding

This example parses and binds the input XML string to the initial context item of the query x/y:

hive> 
SELECT xml_query("x/y", "<x><y>hello</y><z/><y>world</y></x>") 
FROM src LIMIT 1;
     .
     .
     .
["hello","world"]

Example 7-13 Using a STRUCT Binding

In this example, the second argument is a STRUCT that defines two query variables, $data and $value. The values of the variables in the STRUCT are converted to XML schema types as described in "Data Type Conversions."

hive>
SELECT xml_query(
   "fn:parse-xml($data)/x/y[@id = $value]", 
   struct(
      "data", "<x><y id='1'>hello</y><z/><y id='2'>world</y></x>",
      "value", 1
   )
) FROM src LIMIT 1;
     .
     .
     .
["hello"]

Example 7-14 Obtaining Serialized XML

This example uses the fn:serialize function to return serialized XML:

hive> 
SELECT xml_query(
"for $y in x/y 
return fn:serialize($y)
",
"<x><y>hello</y><z/><y>world</y></x>"
) FROM src LIMIT 1;
     .
     .
     .
["<y>hello</y>","<y>world</y>"]

Example 7-15 Accessing the Hadoop Distributed Cache

This example adds a file named test.xml to the distributed cache, and then queries it using the fn:doc function. The file contains this value:

<x><y>hello</y><z/><y>world</y></x>

hive> ADD FILE test.xml;
Added resource: test.xml
hive> SELECT xml_query("fn:doc('test.xml')/x/y", NULL) FROM src LIMIT 1;
     .
     .
     .
["hello","world"]

Example 7-16 Results of a Failed Query

The next example returns an empty array because the input XML is invalid. The XML parsing error will be written to the log:

hive> SELECT xml_query("x/y", "<x><y>hello</y></invalid") FROM src LIMIT 1;
     .
     .
     .
[]

Example 7-17 Using a STRING Binding

This example parses and binds the input XML string to the initial context item of the query x/y:

hive> SELECT xml_query_as_string("x/y", "<x><y>hello</y></x>") FROM src LIMIT 1;
     .
     .
     .
"hello"

The following are string binding examples that use other primitive functions:

hive> SELECT xml_query_as_int("x/y", "<x><y>123</y></x>") FROM src LIMIT 1;
     .
     .
     .
123
 
hive> SELECT xml_query_as_double("x/y", "<x><y>12.3</y></x>") FROM src LIMIT 1;
     .
     .
     .
12.3
 
hive> SELECT xml_query_as_boolean("x/y", "<x><y>true</y></x>") FROM src LIMIT 1;
     .
     .
     .
true

Example 7-18 Using a STRUCT Binding

In this example, the second argument is a STRUCT that defines two query variables, $data and $value. The values of the variables in the STRUCT are converted to XML schema types as described in "Data Type Conversions."

hive>
SELECT xml_query_as_string(
   "fn:parse-xml($data)/x/y[@id = $value]", 
   struct(
      "data", "<x><y id='1'>hello</y><z/><y id='2'>world</y></x>",
      "value", 2
   )
) FROM src LIMIT 1;
     .
     .
     .
world

Example 7-19 Returning Multiple Query Results

This example returns only the first item (hello) from the query. The second item (world) is discarded.

hive> SELECT xml_query_as_string("x/y", "<x><y>hello</y><z/><y>world</y></x>") FROM src LIMIT 1;
     .
     .
     .
hello

Example 7-20 Returning Empty Query Results

This example returns NULL because the result of the query is empty:

hive> SELECT xml_query_as_string("x/foo", "<x><y>hello</y><z/><y>world</y></x>") FROM src LIMIT 1;
     .
     .
     .
NULL

Example 7-21 Obtaining Serialized XML

These examples use the fn:serialize function to return complex XML elements as a STRING value:

hive> SELECT xml_query_as_string("fn:serialize(x/y[1])", "<x><y>hello</y><z/><y>world</y></x>") FROM src LIMIT 1;
     .
     .
     .
"<y>hello</y>"

hive> SELECT xml_query_as_string(
   "fn:serialize(<html><head><title>{$desc}</title></head><body>Name: {$name}</body></html>)", 
   struct(
      "desc", "Employee Details",
      "name", "John Doe"
   )
) FROM src LIMIT 1;
...
<html><head><title>Employee Details</title></head><body>Name: John Doe</body></html>

Example 7-22 Accessing the Hadoop Distributed Cache

This example adds a file named test.xml to the distributed cache, and then queries it using the fn:doc function. The file contains this value:

<x><y>hello</y><z/><y>world</y></x>

Hive> ADD FILE test.xml;
Added resource: test.xml
Hive> SELECT xml_query_as_string("fn:doc('test.xml')/x/y[1]", NULL) FROM src LIMIT 1;
     .
     .
     .
hello

Example 7-23 Results of a Failed Query

This example returns NULL because </invalid is missing an angle bracket. An XML parsing error is written to the log:

Hive> SELECT xml_query_as_string("x/y", "<x><y>hello</invalid") FROM src LIMIT 1;
     .
     .
     .
NULL

This example returns NULL because foo cannot be cast as xs:float. A cast error is written to the log:

Hive> SELECT xml_query_as_float("x/y", "<x><y>foo</y></x>") FROM src LIMIT 1;
     .
     .
     .
NULL

Example 7-24 Using a STRING Binding

The query "x/y" returns two <y> elements, therefore two table rows are generated. Because there are two column expressions ("./z", "./w"), each row has two columns.

hive> SELECT xml_table(
   "x/y",
   "<x>
      <y>
         <z>a</z>
         <w>b</w>
      </y>
      <y>
         <z>c</z>
      </y>
   </x>
   ",
   struct("./z", "./w")
 ) AS (z, w)
 FROM src;
     .
     .
     .
a        b
c        NULL

Example 7-25 Using the Columns Argument

The following two queries are equivalent. The first query explicitly specifies the value of the columns argument:

hive> SELECT xml_table(
      "x/y",
      "<x><y>hello</y><y>world</y></x>",
      struct(".")
   ) AS (y)
   FROM src;
     .
     .
     .
hello
world

The second query omits the columns argument, which defaults to struct("."):

hive> SELECT xml_table(
      "x/y",
      "<x><y>hello</y><y>world</y></x>"
   ) AS (y)
   FROM src;
     .
     .
     .
hello
world

Example 7-26 Using the Namespaces Argument

This example specifies the optional namespaces argument, which identifies an ns prefix and a URI of http://example.org.

hive> SELECT xml_table(
     struct("ns", "http://example.org"),
     "ns:x/ns:y",
     "<x xmlns='http://example.org'><y><z/></y><y><z/><z/></y></x>",
     struct("count(./ns:z)")
  ) AS (y)
  FROM src;
     .
     .
     .
1
2

Example 7-27 Querying a Hive Table of XML Documents

This example queries a table named COMMENTS3, which has a single column named XML_STR of type STRING. It contains these three rows:

hive> SELECT xml_str FROM comments3;

<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>

The following query shows how to extract the user, text, and number of likes from the COMMENTS3 table.

hive> SELECT t.id, t.usr, t.likes
     FROM comments3 LATERAL VIEW xml_table(
        "comment",
        comments.xml_str, 
        struct("./@id", "./@user", "fn:count(./like)")
     ) t AS id, usr, likes;

12345   john    0
56789   kelly   2
54321   mike    1

Example 7-28 Mapping Nested XML Elements to Table Rows

This example shows how to use xml_table to flatten nested, repeating XML elements into table rows. See Example 7-27for the COMMENTS table.

> SELECT t.i, t.u, t.l
    FROM comments3 LATERAL VIEW xml_table (
       "let $comment := ./comment
        for $like in $comment/like
        return
           <r>
              <id>{$comment/@id/data()}</id>
              <user>{$comment/@user/data()}</user>
              <like>{$like/@user/data()}</like>
           </r>
       ",
       comments.xml_str,
       struct("./id", "./user", "./like")
    ) t AS i, u, l;

56789   kelly   john
56789   kelly   mike
54321   mike    laura

Example 7-29 Mapping Optional Nested XML Elements to Table Rows

This example is a slight modification of Example 7-28that produces a row even when a comment has no likes. See Example 7-27for the COMMENTS table.

> SELECT t.i, t.u, t.l
     FROM comments3 LATERAL VIEW xml_table (
        "let $comment := ./comment
         for $like allowing empty in $comment/like
         return 
            <r>
               <id>{$comment/@id/data()}</id>
               <user>{$comment/@user/data()}</user>
               <like>{$like/@user/data()}</like>
            </r>
        ",
        comments.xml_str, 
        struct("./id", "./user", "./like")
     ) t AS i, u, l;

12345   john
56789   kelly   john
56789   kelly   mike
54321   mike    laura

Example 7-30 Creating a New View

You can create views and new tables using xml_table, the same as any table-generating function. This example creates a new view named COMMENTS_LIKES from the COMMENTS table:

hive> CREATE VIEW comments_likes AS 
     SELECT xml_table(
        "comment", 
         comments.xml_str, 
         struct("./@id", "count(./like)")
     ) AS (id, likeCt)
     FROM comments;

This example queries the new view:

> SELECT * FROM comments_likes
     WHERE CAST(likeCt AS INT) != 0;

56789   2
54321   1

Example 7-31 Accessing the Hadoop Distributed Cache

You can access XML documents and text files added to the distributed cache by using the fn:doc and fn:unparsed-text functions.

This example queries a file named test.xml that contains this string:

<x><y>hello</y><z/><y>world</y></x>

hive> ADD FILE test.xml;
Added resource: test.xml
hive> SELECT xml_table("fn:doc('test.xml')/x/y", NULL) AS y FROM src;
     .
     .
     .
hello
world