Adding Structure with Hive - Microsoft Big Data Solutions - page 148

take a single row of input and produce multiple rows of output. These
are useful when dealing with complex types that need to be flattened out.
However, they must be used by themselves in SELECT column lists. Table
6.3 describes the table-generating functions, along with other functions that
work with complex types.

Table 6.3 Functions Related to Complex Types

Name Description

Returns the number of elements in the MAP
or ARRAY passed to the function

size(MAP | ARRAY)

Returns the key values from a MAP as an
ARRAY

map_keys(MAP)

Returns the values from a MAP as an ARRAY

map_values(MAP)

Returns true if the array contains the value,
false if it does not

array_contains(ARRAY,
value)

sort_array(ARRAY) Sorts and returns the ARRAY by the natural
order of the elements
explode(MAP | ARRAY) Returns a row for each item in the MAP or
ARRAY
inline(ARRAY<STRUCT>) Explodes an array of STRUCTs into a table

NOTE

There are also functions for parsing URLs and JSON objects into tables
of information that can prove extremely useful if you need to deal with
this type of data. For a complete, current list of Hive operators and
functions, a good resource is the Hive wiki:
https://cwiki.apache.org/confluence/display/Hive/
LanguageManual+UDF .

Hive supports joining tables, but only using equi-join logic. This restriction
isduetothedistributednatureofthedata,andbecauseHivehastotranslate
many queries to MapReduce jobs. Performing non-equi-joins across