Hive - Hadoop: The Definitive Guide - page 584

Table 17-2. A high-level comparison of SQL and HiveQL

Feature SQL HiveQL References

Updates UPDATE , INSERT , DELETE UPDATE , INSERT , DELETE Inserts ; Updates,

Transactions,
and Indexes

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

Data Types

Functions Hundreds of built-in functions Hundreds of built-in functions Operators and

Functions

Multitable
inserts

Not supported Supported Multitable insert

Not valid SQL-92, but found in
some databases

Supported CREATE

TABLE...AS
SELECT

CREATE
TABLE...AS
SELECT

SQL-92 SQL-92. SORT BY for partial order-

ing, LIMIT to limit number of rows
returned

Querying Data 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)

Subqueries

Views Updatable (materialized or non-

materialized)

Read-only (materialized views not
supported)

Views

Extension
points

User-defined functions, stored
procedures

User-defined functions, MapReduce
scripts

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