Databases Reference
In-Depth Information
SELECT COUNT(1)
FROM page_view
WHERE country='US';
Like SQL, the
GROUP BY
clause allows one to do aggregate queries on groups. This
query will list the number of page views from each country:
SELECT country, COUNT(1)
FROM page_view
GROUP BY country;
And this query will list the number of unique users from each country:
SELECT country, COUNT(DISTINCT userid)
FROM page_view
GROUP BY country;
Table 11.2
shows all the operators supported in HiveQL. These are quite stan-
dard in SQL and programming languages and we won't explain them in detail.
The main exception is in regular expression matching. HiveQL provides two
commands for regular expression matching—
LIKE
and
REGEXP
. (
RLIKE
is equiv-
alent to
REGEXP
.)
LIKE
only performs simple SQL regular expression matching,
where an underscore (_) character in
B
matches any single character in
A
and the
percent (%) character matches any number of characters in
A
.
REGEXP
treats
functions.
Table 11.2
Standard operators in HiveQL
Operator type
Operators
Comparison
A = B , A <> B , A < B , A <= B , A > B , A >= B ,
A IS NULL , A IS NOT NULL , A LIKE B , NOT A LIKE B ,
A RLIKE B , A REGEXP B
Arithmetic
A + B , A - B , A * B , A / B , A % B
Bit-wise
A & B , A | B , A ^ B, ~A
Logical
A AND B, A && B, A OR B, A || B, NOT A, !A
One of the main motivators for users to seek a higher-level language, such as Pig Latin
and HiveQL, is the support of joins. Currently HiveQL
only supports equijoins (joins
on equality). An example join query is
INSERT OVERWRITE TABLE query_result
SELECT pv.*, u.gender, u.age
FROM page_view pv JOIN user u ON (pv.userid = u.id);
7
The format of Java regular expression is fully explained in the Javadoc
http://java.sun.com/j2se/1.4.2/
