CODEWITHSUNDEEP
sqlhivehiveql
someguy
someguy

Reputation: 47

SQL in Hive SemanticException [Error 10004]: Line 3:5 Invalid table alias or column reference

trying o figure out hive sql, not having much luck with what appears to be basics, but I'm just not getting!!

I have a query;

select
from_unixtime(unix_timestamp(unixTimeStampField)+43200) as MyLocalTime,
cast(MyLocalTime as timestamp) as EventTime,
*
from mart.table
where names in ('abc','xyz')

What I am trying to do is, first convert the unixtime to my local time using from_unixtime then from this convert, using cast the column into a date/time field so my graphs can read it as a date/time vs a string value.

Am getting this error;

Error

Error while compiling statement: FAILED: SemanticException [Error 10004]: Line 3:5 Invalid table alias or column reference

Tried some suggested fixes in the chats, but none I seem to get a result with. Thanks in advance

Upvotes: 1

Views: 1384

Answers (2)

someguy
someguy

Reputation: 47

Thanks for that, I did try and no luck unfortunately. I did though modify the unix conversion to then cast it as a timestamp, that seemed to work instead.

cast(from_unixtime(unix_timestamp(tfield)+43200)as TIMESTAMP)

so it looks like this

`select 
    cast(from_unixtime(unix_timestamp(tfield)+43200)as TIMESTAMP) as MyLocalTime, 
    * 
from 
    mart.table 
where 
    names in ('abc','xyz')`

Upvotes: 0

Koushik Roy
Koushik Roy

Reputation: 7387

Can you please try this ?

If you select all columns along with something else, you need to alias the table and use it to fetch all columns.

select
from_unixtime(unix_timestamp(unixTimeStampField)+43200) as MyLocalTime,
cast(MyLocalTime as timestamp) as EventTime,
t.*  -- You need to call the table by alias.
from mart.table t -- alias the table.
where names in ('abc','xyz')

Upvotes: 1

Related Questions