executing HIVE query in background

Question

how to execute a HIVE query in background when the query looks like below

Select count(1) from table1 where column1='value1';

I am trying to write it using a script like below

 #!/usr/bin/ksh
exec 1> /home/koushik/Logs/`basename $0 | cut -d"." -f1 | sed 's/\.sh//g'`_$(date +"%Y%m%d_%H%M%S").log 2>&1

ST_TIME=`date +%s`

cd $HIVE_HOME/bin

./hive -e 'SELECT COUNT(1) FROM TABLE1 WHERE COLUMN1 = ''value1'';'

END_TIME=`date +%s`
TT_SECS=$(( END_TIME - ST_TIME))
TT_HRS=$(( TT_SECS / 3600 ))
TT_REM_MS=$(( TT_SECS % 3600 ))
TT_MINS=$(( TT_REM_MS / 60 ))
TT_REM_SECS=$(( TT_REM_MS % 60 ))
printf "\n"
printf "Total time taken to execute the script="$TT_HRS:$TT_MINS:$TT_REM_SECS HH:MM:SS
printf "\n"

but getting error like

FAILED: SemanticException [Error 10004]: Line 1:77 Invalid table alias or column reference 'value1'

let me know exactly where I am doing mistake.

Answer 1

I am able to resolve it replacing single quote with double quote. Now the modified statement looks like ./hive -e 'SELECT COUNT(1) FROM Table1 WHERE Column1 = "Value1";'

Answer 2

True @Koushik ! Glad that you found the issue. In the query, bash was unable to form the hive query due to ambiguous single quotes.

Though SELECT COUNT(1) FROM Table1 WHERE Column1 = 'Value1' is valid in hive,

$hive -e 'SELECT COUNT(1) FROM Table1 WHERE Column1 = 'Value1';' is not valid.

The best solution would be to use double quotes for the Value1 as

hive -e 'SELECT COUNT(1) FROM Table1 WHERE Column1 = "Value1";'

or use a quick and dirty solution by including the single quotes within double quotes.

hive -e 'SELECT COUNT(1) FROM Table1 WHERE Column1 = "'"Value1"'";'

This would make sure that the hive query is properly formed and then executed accordingly. I'd not suggest this approach unless you've a desperate ask for a single quote ;)

Answer 3

Create a document named example

vi example

Enter the query in the document and save it.

create table sample as
Select count(1) from table1 where column1='value1';

Now run the document using the following command:

hive -f example 1>example.error 2>example.output &

You will get the result as

[1]

Now disown the process :

disown

Now the process will run in the background. If you want to know the status of the output, you may use

tail -f example.output