Supported SQL Language Features

The Impala SQL dialect supports many standard SQL language features and some extensions for Hadoop related to data loading and data warehousing. The impala-shell interpreter supports multi-line commands. A semicolon at the end of each statement is required.

SQL Features

Impala supports the following SQL features:
  • Comments
  • External/Internal tables
  • Hints
  • Joins
  • Views

Statements

Impala supports the following SQL statements:

  • ALTER TABLE
  • CASCADE
  • CREATE DATABASE
  • CREATE TABLE
  • CREATE TABLE AS SELECT
  • CREATE VIEW
  • DESCRIBE
  • DROP DATABASE
  • DROP TABLE/ALTER TABLE DROP PARTITION
  • EXPLAIN
  • INSERT
  • INVALIDATE METADATA
  • LOAD DATA
  • PURGE
  • REFRESH
  • SELECT
  • SHOW
  • SHOW DATABASES
  • TRUNCATE TABLE
  • USE

Clauses

Impala supports the following SQL clauses:

  • IF EXISTS
  • WHRRE
  • VALUES
  • WITH
  • GROUP BY
  • HAVING
  • LIMIT
  • ORDER BY

Operators

Impala supports the following SQL operators:

  • BETWEEN
  • DISTINCT
  • ILIKE
  • IREGEXPR
  • IS [NOT] DISTINCT FROM
  • LIKE
  • REGEXP
  • RLIKE

Functions

Impala supports the following SQL functions:

  • AVG
  • BITAND
  • BITNOT
  • BITOR
  • BITXOR
  • BTRIM
  • CHR
  • COT
  • COUNT
  • COUNTSET
  • CUME_DIST
  • DCEIL
  • DEXP
  • DFLOOR
  • DLOG10
  • DPOW
  • DROUND
  • DSQRT
  • DTRUNC
  • FACTORIAL
  • FPOW
  • GETBIT
  • GROUP_CONCAT
  • INT_MONTHS_BETWEEN
  • MAX
  • MIN
  • MONTHS_BETWEEN
  • NDV
  • NTILE
  • PERCENT_RANK
  • REGEXP_LIKE
  • ROTATELEFT
  • ROTATERIGHT
  • SETBIT
  • SHIFTLEFT
  • SHIFTRIGHT
  • SPLIT_PART
  • SUM
  • TIMEOFDAY
  • TIMESTAMP_CMP
  • TYPEOF

Data Types

Impala supports the following SQL data types:

  • BIGINT
  • BOOLEAN
  • DECIMAL
  • DOUBLE
  • FLOAT
  • INT
  • SMALLINT
  • NULL
  • STRING
  • TIMESTAMP
  • TINYINT
Impala supports the following complex data types:
  • STRUCT
  • ARRAY
  • MAP