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.
-- Make the column headers of the result set easier to understand.
SELECT c1 AS name, c2 AS address, c3 AS phone FROM table_with_terse_columns;
SELECT SUM(ss_xyz_dollars_net) AS total_sales FROM table_with_cryptic_columns;
-- The alias can be a quoted string for extra readability.
SELECT c1 AS "Employee ID", c2 AS "Date of hire" FROM t1;
-- The AS keyword is optional.
SELECT c1 "Employee ID", c2 "Date of hire" FROM t1;
-- The table aliases assigned in the FROM clause can be used both earlier
-- in the query (the SELECT list) and later (the WHERE clause).
SELECT one.name, two.address, three.phone
FROM census one, building_directory two, phonebook three
WHERE one.id = two.id and two.id = three.id;
-- The aliases c1 and c2 let the query handle columns with the same names from 2 joined tables.
-- The aliases t1 and t2 let the query abbreviate references to long or cryptically named tables.
SELECT t1.column_n AS c1, t2.column_n AS c2 FROM long_name_table AS t1, very_long_name_table2 AS t2
WHERE c1 = c2;
SELECT t1.column_n c1, t2.column_n c2 FROM table1 t1, table2 t2
WHERE c1 = c2;
From Impala 3.0, the alias substitution logic has changed.
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:
[localhost:21000] > select c1 First_Column from t;
[localhost:21000] > select c1 as First_Column from t;
+--------------+
| first_column |
+--------------+
...
[localhost:21000] > select c1 'First Column' from t;
[localhost:21000] > select c1 as 'First Column' from t;
+--------------+
| first column |
+--------------+
...
[localhost:21000] > select c1 "First Column" from t;
[localhost:21000] > select c1 as "First Column" from t;
+--------------+
| first column |
+--------------+
...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:
SELECT int_col / 2 AS x
FROM t
GROUP BY x;
SELECT int_col / 2 AS x
FROM t
ORDER BY x;
SELECT NOT bool_col AS nb
FROM t
GROUP BY nb
HAVING nb;
SELECT int_col / 2 AS x
FROM t
GROUP BY x / 2;
SELECT int_col / 2 AS x
FROM t
ORDER BY -x;
SELECT int_col / 2 AS x
FROM t
GROUP BY x
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.