Impala-shell is it possible to pass a where clause ?

[#1]

Hi guys,

This is basic question but not able to solve it...

I created a scheduled workflow in Oozie to run a Shell script witch runs some impala-shell cmd. Everything worked fine since I added a where clause for one of them. I'm wondering if it's possible to pass a where clause on a query impala-shell ?

Here is my basic cmd in shell script :

impala-shell -B -q 'select col1,col2,Col3 from MyDB.MyTable where col1 IN ('argument1','argument2','argument3','argument4','argument5') order by col1,col2' --delimited --output_delimiter='|' -o ${DIR_OUT}/MyTable_$(date +"%Y%m%d-%H%M%S").csv --print_header 2>${DIR_LOG}/MyTable_$(date +"%Y%m%d-%H%M%S").log;

It works fine without "where col1 IN ('argument1','argument2','argument3','argument4','argument5')"

Is there a way to do it without using a file ? (with -f option ???)

Thanks for your advise

Alex

[#2 · Accepted solution]

Fixed by using double quote for the query "the query with where clause" (vs 'the query with where clause')

Full cmd :

impala-shell -B -q "select col1,col2,Col3 from MyDB.MyTable where col1 IN ('argument1','argument2','argument3','argument4','argument5') order by col1,col2" --delimited --output_delimiter='|' -o ${DIR_OUT}/MyTable_$(date +"%Y%m%d-%H%M%S").csv --print_header 2>${DIR_LOG}/MyTable_$(date +"%Y%m%d-%H%M%S").log;

That's all folks !

[#3]

Hi Alex,

Try escaping the single quotes in your WHERE clause by putting backslashes before them, like this:

WHERE col1 IN (\'argument1\',\'argument2\',\'argument3\',\'argument4\',\'argument5\')

This is necessary, because if you don't use the escape characters, then the first single quote mark inside the SELECT statement will be interpreted as the end of the quoted SELECT statement. 

For more details, see our Coursera courses. In particular: this video covers this specific topic.

Ian

[#4]

Hi Ian,

Thanks for taking time to have a look on it.

I actually tried to add backslash ( \' ) but did not worked.

I also tried to add 2 quotes (/!\ not double quotes) same, it did not worked.

The only way it worked is to put the query behind double quotes : -q "my_query where col1 IN ('arg1','arg2')"

PS: As a reminder my script is on a Shell script file .sh

Thanks again

Alex