hive error: specifying a string in a hive query

Question

When I run the following hive command

hive -e 'select msg, count(*) as cnt from table where msg like “%abcd%” group by msg order by cnt desc ;' | sed 's/[\t]/,/g' > table.csv

I get the following error.

FAILED: ParseException line 1:89 cannot recognize input near 'like' '%' 'password' in expression specification

I am aware that there is a problem with specifying the string “%abcd%”. The command works fine in a hive environemnt, but here i was trying to save the result to a csv file. How do i rectify this error?

The thing is Hive uses single quotes for string so you need to write like '%abcd%' and use double quotes for bash string — serge_k
@serge_k Tried that as well hive -e 'select msg, count(*) as cnt from table where msg like '%password%' group by msg order by cnt desc ;' | sed 's/[\t]/,/g' > table.csv. But getting the same error. — prashanth
However, this worked fine hive -e "select msg, count(*) as cnt from table where msg like '%password%' group by msg order by cnt desc ;" | sed "s/[\t]/,/g" > table.csv — prashanth
Actually that's what I meant when wrote "use double quotes for bash string". — serge_k

leftjoin leftjoin · Accepted Answer · 2018-09-27T16:06:01

Hive script should be double-quoted and template is single-quoted:

hive -e "select msg, count(*) as cnt from table where msg like '%abcd%' group by msg order by cnt desc ;" | sed 's/[\t]/,/g' > table.csv

hive error: specifying a string in a hive query

1 Answers