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]($f1c3126d187707ee.md#insert) statement also typically ends with a SELECT statement, to define data to copy from one table to another.

Syntax:

  1. [WITH name AS (select_expression) [, ...] ]
  2. SELECT
  3.   [ALL | DISTINCT]
  4.   [STRAIGHT_JOIN]
  5.   expression [, expression ...]
  6. FROM table_reference [, table_reference ...]
  7. [[FULL | [LEFT | RIGHT] INNER | [LEFT | RIGHT] OUTER | [LEFT | RIGHT] SEMI | [LEFT | RIGHT] ANTI | CROSS]
  8.   JOIN table_reference
  9.   [ON join_equality_clauses | USING (col1[, col2 ...]] ...
  10. WHERE conditions
  11. GROUP BY { column | expression [, ...] }
  12. HAVING conditions
  13. ORDER BY { column | expression [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...] }
  14. LIMIT expression [OFFSET expression]
  15. [UNION [ALL] select_statement] ...]
  17. table_reference := { table_name | (subquery) }
  18.   [ TABLESAMPLE SYSTEM(percentage) [REPEATABLE(seed)] ]

Impala SELECT queries support:

  • SQL scalar data types: [BOOLEAN]($da337d61295bd4e6.md#boolean), [TINYINT]($5858db9294f81690.md#tinyint), [SMALLINT]($359cf5dc4a0ee5a9.md#smallint), [INT]($348e9f6ca05f4ca5.md#int), [BIGINT]($227dec91901bdbfe.md#bigint), [DECIMAL]($d8b465c31c33e4ac.md#decimal) [FLOAT]($4b1a0f9404942635.md#float), [DOUBLE]($1c8ec619bae1e4bf.md#double), [TIMESTAMP]($ca77622fde39d380.md#timestamp), [STRING]($9bae843d3fcf1dcb.md#string), [VARCHAR]($8a087b4d201b4d79.md#varchar), [CHAR]($8bce1cee0c9a7c9a.md#char).
  • The complex data types ARRAY, STRUCT, and MAP, are available in Impala 2.3 and higher. Queries involving these types typically involve special qualified names using dot notation for referring to the complex column fields, and join clauses for bringing the complex columns into the result set. See Complex Types (Impala 2.3 or higher only) for details.
  • 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.
  • Subqueries in a FROM clause. In Impala 2.0 and higher, subqueries can also go in the WHERE clause, for example with the IN(), EXISTS, and NOT EXISTS operators.
  • WHERE, GROUP BY, HAVING clauses.
  • [ORDER BY]($41f0e5f6601e9e60.md#order_by). Prior to Impala 1.4.0, Impala required that queries using an ORDER BY clause also include a [LIMIT]($bcd629c2564e18b4.md#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.

  • You can refer to SELECT-list items by their ordinal position. Impala supports ordinals in the GROUP BY, HAVING, and ORDER BY clauses. From Impala 3.0, ordinals can only be used at the top level. For example, the following statements are allowed:

    2.   SELECT int_col / 2, sum(x)
    3.   FROM t
    4.   GROUP BY 1;
    6.   SELECT int_col / 2
    7.   FROM t
    8.   ORDER BY 1;
    10.   SELECT NOT bool_col
    11.   FROM t
    12.   GROUP BY 1
    13.   HAVING 1;

    Numbers in subexpressions are not interpreted as ordinals:

    2.   SELECT int_col / 2, sum(x)
    3.   FROM t
    4.   GROUP BY 1 * 2;
    5. The above parses OK, however GROUP BY 1 * 2 has no effect.
    7.   SELECT int_col / 2
    8.   FROM t
    9.   ORDER BY 1 + 2;
    10. The above parses OK, however ORDER BY 1 + 2 has no effect.
    12.   SELECT NOT bool_col
    13.   FROM t
    14.   GROUP BY 1
    15.   HAVING not 1;
    16. The above raises an error at parse-time.

  • 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 and any DISTINCT or ALL keywords.

    See Joins in Impala SELECT Statements for details and examples of join queries.

  • UNION ALL.

  • 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 Impala Built-In Functions.
  • In Impala 2.9 and higher, an optional TABLESAMPLE clause immediately after a table reference, to specify that the query only processes a specified percentage of the table data. See TABLESAMPLE Clause for details.

Impala queries ignore files with extensions commonly used for temporary work files by Hadoop tools. Any files with extensions .tmp or .copying are not considered part of the Impala table. The suffix matching is case-insensitive, so for example Impala ignores both .copying and .COPYING suffixes.

Security considerations:

If these statements in your environment contain sensitive literal values such as credit card numbers or tax identifiers, Impala can redact this sensitive information when displaying the statements in log files and other administrative contexts. See the documentation for your Apache Hadoop distribution for details.

Amazon S3 considerations:

In Impala 2.6 and higher, Impala queries are optimized for files stored in Amazon S3. For Impala tables that use the file formats Parquet, ORC, RCFile, SequenceFile, Avro, and uncompressed text, the setting fs.s3a.block.size in the core-site.xml configuration file determines how Impala divides the I/O work of reading the data files. This configuration setting is specified in bytes. By default, this value is 33554432 (32 MB), meaning that Impala parallelizes S3 read operations on the files as if they were made up of 32 MB blocks. For example, if your S3 queries primarily access Parquet files written by MapReduce or Hive, increase fs.s3a.block.size to 134217728 (128 MB) to match the row group size of those files. If most S3 queries involve Parquet files written by Impala, increase fs.s3a.block.size to 268435456 (256 MB) to match the row group size produced by Impala.

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, or Cancel from the list of in-flight queries (for a particular node) on the Queries tab in the Impala web UI (port 25000).

HDFS permissions:

The user ID that the impalad daemon runs under, typically the impala user, must have read permissions for the files in all applicable directories in all source tables, and read and execute permissions for the relevant data directories. (A SELECT operation could read files from multiple different HDFS directories if the source table is partitioned.) If a query attempts to read a data file and is unable to because of an HDFS permission error, the query halts and does not return any further results.

Related information:

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.

Parent topic: Impala SQL Statements