Database Reference
In-Depth Information
SELECT c.name, c.city, c.state, c.postalCode,
c.purchases[1]
And if purchases were a STRUCT , you would use the field name:
SELECT c.name, c.city, c.state, c.postalCode,
c.purchases.Lodging
You can use this syntax in any location where you would use a regular
column.
Calculations and functions are used in the same way as you would in most
SQL dialects. For example, this SELECT statement returns the sum of
lodging purchases for any customer who purchased over 100 in food:
SELECT SUM(c.purchases['Lodging'])
FROM MsBigData.customer c
WHERE c.purchases['Food'] > 100;
NOTE
One interesting feature of Hive is that you can use regular expressions
in the column list of the SELECT . For example, this query returns the
name column and all columns that start with “address” from the
specified table:
SELECT name, 'address.*' FROM shipments;
You can also use the functions RLIKE and REGEXP , which function in
the same way as LIKE but allow the use of regular expressions for
matching.
Some functions that are of particular interest are those that deal with
complex types, because these don't have equivalent versions in many
relational systems. Forexample, there are functions fordetermining thesize
ofacollection. There are alsofunctions thatgenerate tables asoutput. These
are the opposite of aggregating functions, such as SUM , which take multiple
rows and aggregate them into a single result. Table generating functions
Search WWH ::




Custom Search