Impala aliases

You must be aware of certain guidelines and rules about assigning an alias to the names of tables, columns, or column expressions in SQL statements and using the alias when referencing the table or column in the same statement.

Aliases rules

The following rules apply to aliases:

  • 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 specify the alias immediately after the original name.

  • You can specify column aliases with or without the AS keyword, and with no quotation marks, single quotation marks, or double quotation marks.
  • Aliases are not case sensitive.
  • Aliases can be up to the maximum length of a Java string, 2147483647.
  • Aliases can include additional characters such as spaces and dashes when they are quoted using backtick characters (``).
  • To use an alias name that matches one of the Impala reserved keywords, surround the identifier with either single or double quotation marks, or backtick characters.
  • Aliases are allowed at the top level of the GROUP BY, HAVING, and ORDER BY clauses, e.g. GROUP BY alias.
  • Aliases are not allowed in subexpressions of the GROUP BY, HAVING, and ORDER BY clauses, e,g. GROUP BY alias/5.
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:

  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;
And the following statements are NOT allowed:

  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;