This is the documentation for Cloudera Impala 2.1.x.
Documentation for other versions is available at

SELECT Statement

The SELECT statement performs queries, retrieving data from one or more tables and producing result sets consisting of rows and columns.

The Impala INSERT statement also typically ends with a SELECT statement, to define data to copy from one table to another.


Impala SELECT queries support:

  • An optional WITH clause before the SELECT keyword, to define a subquery whose name or column names can be referenced from later in the main query. This clause lets you abstract repeated clauses, such as aggregation functions, that are referenced multiple times in the same query.
  • By default, one DISTINCT clause per query. See DISTINCT Operator for details. See APPX_COUNT_DISTINCT for a query option to allow multiple COUNT(DISTINCT) impressions in the same query.
  • Subqueries in a FROM clause.
  • WHERE, GROUP BY, HAVING clauses.
  • ORDER BY. Prior to Impala 1.4.0, Impala required that queries using an ORDER BY clause also include a LIMIT clause. In Impala 1.4.0 and higher, this restriction is lifted; sort operations that would exceed the Impala memory limit automatically use a temporary disk work area to perform the sort.
  • Impala supports a wide variety of JOIN clauses. Left, right, semi, full, and outer joins are supported in all Impala versions. The CROSS JOIN operator is available in Impala 1.2.2 and higher. During performance tuning, you can override the reordering of join clauses that Impala does internally by including the keyword STRAIGHT_JOIN immediately after the SELECT keyword

    See Joins for details and examples of join queries.

  • LIMIT.
  • External tables.
  • Relational operators such as greater than, less than, or equal to.
  • Arithmetic operators such as addition or subtraction.
  • Logical/Boolean operators AND, OR, and NOT. Impala does not support the corresponding symbols &&, ||, and !.
  • Common SQL built-in functions such as COUNT, SUM, CAST, LIKE, IN, BETWEEN, and COALESCE. Impala specifically supports built-ins described in Built-in Functions.

Cancellation: Can be cancelled. To cancel this statement, use Ctrl-C from the impala-shell interpreter, the Cancel button from the Watch page in Hue, Actions > Cancel from the Queries list in Cloudera Manager, or Cancel from the list of in-flight queries (for a particular node) on the Queries tab in the Impala web UI (port 25000).

Continue reading:

The SELECT syntax is so extensive that it forms its own category of statements: queries. The other major classifications of SQL statements are data definition language (see DDL Statements) and data manipulation language (see DML Statements).

Because the focus of Impala is on fast queries with interactive response times over huge data sets, query performance and scalability are important considerations. See Tuning Impala for Performance and Scalability Considerations for Impala for details.