Can't subtract two columns involving an alias in Hive query

Question

I'm attempting to do the following query where I use a windowing to fetch the next log timestamp and then do a subtraction between it and the current timestamp.

SELECT
  LEAD(timestamp) OVER (PARTITION BY id ORDER BY timestamp) AS lead_timestamp,
  timestamp,
  (lead_timestamp - timestamp) as delta
FROM logs;

However, when I do this, I get the following error:

FAILED: SemanticException [Error 10004]: Line 4:1 Invalid table alias or column reference 'lead_timestamp': (possible column names are: logs.timestamp, logs.latitude, logs.longitude, logs.principal_id)

If I drop this subtraction, the rest of the query works, so I'm stumped - am I using the AS syntax wrong above for lead_timestamp?

Pradeep Gollakota Pradeep Gollakota · Accepted Answer · 2015-04-14T01:36:54

One of the limitations of Hive is that you can't refer to aliases you assigned in the same query (except for the HAVING clause). This is due to the way the code is structured around aliasing. You'll have to write this using a sub query.

SELECT lead_timestamp, timestamp, (lead_timestamp - timestamp) AS delta
FROM (
    SELECT LEAD(timestamp) OVER (PARTITION BY id ORDER BY timestamp) AS lead_timestamp,
           timestamp
    FROM logs
) a;

It's ugly, but works.

Can't subtract two columns involving an alias in Hive query

1 Answers