Database Reference
In-Depth Information
Table 17-2. A high-level comparison of SQL and HiveQL
Feature
SQL
HiveQL
References
Updates
UPDATE
,
INSERT
,
DELETE
UPDATE
,
INSERT
,
DELETE
Transactions Supported
Limited support
Indexes
Supported
Supported
Data types
Integral, floating-point, fixed-
point, text and binary strings,
temporal
Boolean, integral, floating-point,
fixed-point, text and binary strings,
temporal, array, map, struct
Functions
Hundreds of built-in functions
Hundreds of built-in functions
Multitable
inserts
Not supported
Supported
Not valid SQL-92, but found in
some databases
Supported
CREATE
TABLE...AS
SELECT
SQL-92
SQL-92.
SORT BY
for partial order-
ing,
LIMIT
to limit number of rows
returned
SELECT
Joins
SQL-92, or variants (join tables
in the
FROM
clause, join condition
in the
WHERE
clause)
Inner joins, outer joins, semi joins,
map joins, cross joins
Joins
Subqueries In any clause (correlated or non-
correlated)
In the
FROM
,
WHERE
, or
HAVING
clauses (uncorrelated subqueries not
supported)
Views
Updatable (materialized or non-
materialized)
Read-only (materialized views not
supported)
Extension
points
User-defined functions, stored
procedures
User-defined functions, MapReduce
scripts
Data Types
Hive supports both primitive and complex data types. Primitives include numeric,
Boolean, string, and timestamp types. The complex data types include arrays, maps, and
structs. Hive's data types are listed in
Table 17-3
. Note that the literals shown are those