dataengref errors

Home / Hive / Query compilation

Hive: SemanticException [Error 10004]: Invalid table alias or column reference

org.apache.hadoop.hive.ql.parse.SemanticExceptionFAILED: SemanticException [Error 10004]: Line 1:7 Invalid table alias or column reference 'usr_id': (possible column names are: user_id, amount, dt)

Hive could not resolve a column or alias in the query. The message names the reference and lists the columns that were available, which is usually enough to spot the typo or scoping mistake.

Causes · Fixes · Source

Common causes

  1. A misspelled column, or a column that does not exist in that table.How to confirm: Compare the name with the "possible column names are" list in the message.
  2. A column alias defined in SELECT is used in WHERE or GROUP BY of the same query level.How to confirm: The alias appears only in the SELECT list of the same query.
  3. A column of a subquery is referenced with the inner table alias instead of the subquery alias.How to confirm: The reference uses an alias that is only visible inside the subquery.

How to fix it

Use the column name from the list in the message, and qualify it with the alias that is visible at that level.

Repeat the expression instead of the alias in WHERE or GROUP BY, or wrap the query in a subquery and filter outside.

SELECT * FROM (
  SELECT user_id, amount * 0.9 AS net FROM orders
) t
WHERE net > 100;

Where the message comes from

Message template in the source: Invalid table alias or column reference

References

Related errors

Last checked: