shawn shawn - 7 months ago 66
SQL Question

How to Select count and literal value in hive

Why is this query returning an error. I am trying to load the code for table as a constant string, the flag for data again a constant string, the time of insertion and the counts for a table. I thought, let me try and run the secelct before writing the inserts.
But for some reason, it fails listing column names from tables from where I am trying to get a count. All i need is two constant values, one date and one count. Tried by removing the groupby as well, throws another error.

hive -e "select "WEB" as src_cd, "1Hr" as Load_Flag, from_unixtime((unix_timestamp(substr(sysDate, 0, 11), 'dd/MMM/yyyy')), 'MM/dd/yyyy') as time, count(*)
from weblog
where year=2015 and month=04 and day=17
group by src_cd, load_flag, time
;

"

OK
Time taken: 1.446 seconds
FAILED: SemanticException [Error 10004]: Line 4:9 Invalid table alias or column reference 'src_cd': (possible column names are: clientip, authuser, sysdate, clfrequest.........(and so on) year, month, day)

Answer

The double quotes on the literals is a problem. Here is a simpler version that I tested successfully:

hive -e "select 'WEB' , '1Hr' , from_unixtime((unix_timestamp(substr(sysDate, 0, 11), 'dd/MMM/yyyy')), 'MM/dd/yyyy') as time, count(*) from weblog where year=2015 and month=04 and day=17 group by 1,2 , from_unixtime((unix_timestamp(substr(sysDate, 0, 11), 'dd/MMM/yyyy')), 'MM/dd/yyyy') ; "
Comments