Overview of Impala Aliases

When you write the names of tables, columns, or column expressions in a query, you can assign an alias at the same time. Then you can specify the alias rather than the original name when making other references to the table or column in the same statement. You typically specify aliases that are shorter, easier to remember, or both than the original names. The aliases are printed in the query header, making them useful for self-documenting output.

To set up an alias, add the AS alias clause immediately after any table, column, or expression name in the SELECT list or FROM list of a query. The AS keyword is optional; you can also specify the alias immediately after the original name.

  1. -- Make the column headers of the result set easier to understand.
  2. SELECT c1 AS name, c2 AS address, c3 AS phone FROM table_with_terse_columns;
  3. SELECT SUM(ss_xyz_dollars_net) AS total_sales FROM table_with_cryptic_columns;
  4. -- The alias can be a quoted string for extra readability.
  5. SELECT c1 AS "Employee ID", c2 AS "Date of hire" FROM t1;
  6. -- The AS keyword is optional.
  7. SELECT c1 "Employee ID", c2 "Date of hire" FROM t1;
  9. -- The table aliases assigned in the FROM clause can be used both earlier
  10. -- in the query (the SELECT list) and later (the WHERE clause).
  11. SELECT one.name, two.address, three.phone
  12.   FROM census one, building_directory two, phonebook three
  13. WHERE one.id = two.id and two.id = three.id;
  15. -- The aliases c1 and c2 let the query handle columns with the same names from 2 joined tables.
  16. -- The aliases t1 and t2 let the query abbreviate references to long or cryptically named tables.
  17. SELECT t1.column_n AS c1, t2.column_n AS c2 FROM long_name_table AS t1, very_long_name_table2 AS t2
  18.   WHERE c1 = c2;
  19. SELECT t1.column_n c1, t2.column_n c2 FROM table1 t1, table2 t2
  20.   WHERE c1 = c2;

From Impala 3.0, the alias substitution logic has changed.

You can specify column aliases with or without the AS keyword, and with no quotation marks, single quotation marks, or double quotation marks. Some kind of quotation marks are required if the column alias contains any spaces or other problematic characters. The alias text is displayed in the impala-shell output as all-lowercase. For example:

  1. [localhost:21000] > select c1 First_Column from t;
  2. [localhost:21000] > select c1 as First_Column from t;
  3. +--------------+
  4. | first_column |
  5. +--------------+
  6. ...
  8. [localhost:21000] > select c1 'First Column' from t;
  9. [localhost:21000] > select c1 as 'First Column' from t;
  10. +--------------+
  11. | first column |
  12. +--------------+
  13. ...
  15. [localhost:21000] > select c1 "First Column" from t;
  16. [localhost:21000] > select c1 as "First Column" from t;
  17. +--------------+
  18. | first column |
  19. +--------------+
  20. ...

From Impala 3.0, the alias substitution logic in the GROUP BY, HAVING, and ORDER BY clauses has become more consistent with standard SQL behavior, as follows. Aliases are now only legal at the top level, and not in subexpressions. The following statements are allowed:

  2.   SELECT int_col / 2 AS x
  3.   FROM t
  4.   GROUP BY x;
  6.   SELECT int_col / 2 AS x
  7.   FROM t
  8.   ORDER BY x;
  10.   SELECT NOT bool_col AS nb
  11.   FROM t
  12.   GROUP BY nb
  13.   HAVING nb;

And the following statements are NOT allowed:

  2.   SELECT int_col / 2 AS x
  3.   FROM t
  4.   GROUP BY x / 2;
  6.   SELECT int_col / 2 AS x
  7.   FROM t
  8.   ORDER BY -x;
  10.   SELECT int_col / 2 AS x
  11.   FROM t
  12.   GROUP BY x
  13.   HAVING x > 3;

To use an alias name that matches one of the Impala reserved keywords (listed in Impala Reserved Words), surround the identifier with either single or double quotation marks, or `` characters (backticks).

Aliases follow the same rules as identifiers when it comes to case insensitivity. Aliases can be longer than identifiers (up to the maximum length of a Java string) and can include additional characters such as spaces and dashes when they are quoted using backtick characters.

Complex type considerations:

Queries involving the complex types (ARRAY, STRUCT, and MAP), typically make extensive use of table aliases. These queries involve join clauses where the complex type column is treated as a joined table. To construct two-part or three-part qualified names for the complex column elements in the FROM list, sometimes it is syntactically required to construct a table alias for the complex column where it is referenced in the join clause. See Complex Types (Impala 2.3 or higher only) for details and examples.

Alternatives:

Another way to define different names for the same tables or columns is to create views. See Overview of Impala Views for details.

Parent topic: Impala Schema Objects and Object Names