0

I am finding number of days in a month as below.

set ndays=datediff(CONCAT(y, '-', (m + 1), '-', '01'), CONCAT(y, '-', m, '-', '01')) FROM (SELECT month(current_date) as m, year(current_date) as y, day(current_date)) tabl1;

I am checking the value.

select ${hiveconf:ndays}; --O/P 31

I am using this variable in a query and getting error.

select
sum(price)/ ${hiveconf:ndays}
from  sales_aly
GROUP BY sales_month;

FAILED: ParseException line 3:0 missing EOF at 'from' near 'tabl1'

Please help me.

Thanks in Advance.

Gynteniuxas
  • 7,035
  • 18
  • 38
  • 54
Rock
  • 65
  • 8

1 Answers1

0

set command will not calculate value of this expression: datediff(CONCAT(y, '-', (m + 1), '-', '01'), CONCAT(y, '-', m, '-', '01')) FROM (SELECT month(current_date) as m, year(current_date) as y, day(current_date)) tabl1 but assigns expression itself as a value. Then when you use variable in the code, expression is substituted instead of variable.

You can calculate your expression in shell and pass it to hive like this:

var=$(hive -e "set hive.cli.print.header=false; select datediff(CONCAT(y, '-', (m + 1), '-', '01'), CONCAT(y, '-', m, '-', '01')) FROM (SELECT month(current_date) as m, year(current_date) as y, from tab1 limit 1) tabl1;")
# Then call your script:

hive -hiveconf ndays=$var -f your_script_file_name

Address variable inside your script: ${hiveconf:ndays}

Alternatively you can use macro. See this: http://svn.apache.org/repos/asf/hive/trunk/ql/src/test/queries/clientpositive/macro.q

leftjoin
  • 36,950
  • 8
  • 57
  • 116
  • Hi, I have tried this but not working.cat nds.ksh var=$(hive -e "datediff(CONCAT(y, '-', (m + 1), '-', '01'), CONCAT(y, '-', m, '-', '01')) FROM (SELECT month(current_date) as m, year(current_date) as y, day(current_date)) tabl1;") select sum(price)/ ${hiveconf:ndays} from sales_aly GROUP BY sales_month; hive -hiveconf ndays=$var -f tt.hql please help me – Rock Sep 07 '16 at 13:24
  • 1. Prepare your script file: select sum(price)/ ${hiveconf:ndays} from sales_aly GROUP BY sales_month; --file tt.hql end 2. then in your shell script: var=$(hive -e " set hive.cli.print.header=false; select datediff(CONCAT(y, '-', (m + 1), '-', '01'), CONCAT(y, '-', m, '-', '01')) FROM (SELECT month(current_date) as m, year(current_date) as y, from tab1 limit 1) tabl1;") ; #use variable in the script; hive -hiveconf ndays=$var -f tt.hql – leftjoin Sep 07 '16 at 18:17
  • See also this link: https://netezzaadmin.wordpress.com/2013/09/25/passing-parameters-to-hive-scripts/ – leftjoin Sep 07 '16 at 18:25
  • In your example: select ${hiveconf:ndays}; --O/P 31 the variable first substituted with initial value - this exactly string: `(datediff(CONCAT(y, '-', (m + 1), '-', '01'), CONCAT(y, '-', m, '-', '01')) FROM (SELECT month(current_date) as m, year(current_date) as y, day(current_date)) tabl1);` Then you adding `select ` at the beginning and executing the whole sentence with `select ' at the beginning. – leftjoin Sep 07 '16 at 18:30
  • Hi, I have tried the below procedure. step1 : cat >nds.ksh var=$(hive -e " set hive.cli.print.header=false; select datediff(CONCAT(y, '-', (m + 1), '-', '01'), CONCAT(y, '-', m, '-', '01')) FROM (SELECT month(current_date) as m, year(current_date) as y, day(current_date)) tabl1;"); --this is giving 30 step2: select sum(price)/ '${hiveconf:ndays}' from sales_aly GROUP BY sales_month; step3: hive -hiveconf "ndays"="$var" -f tt.hql This is sum(price)/ '${hiveconf:ndays}' is giving the result NULL. Please help me. Thanks – Rock Sep 08 '16 at 02:36
  • You do not need quote `${hiveconf:ndays}` variable inside script because it's integer, not string. Check the variable passed correctly inside hql script: just `select ${hiveconf:ndays} ;` or ` ! echo ${hiveconf:ndays}` – leftjoin Sep 08 '16 at 07:01
  • Hi, I have tried this. select ${hiveconf:ndays} ; got the error FAILED: ParseException line 1:8 cannot recognize input near '' '' '' in select clause Please help me. Thanks. – Rock Sep 08 '16 at 07:31
  • try this: ` ! echo ${hiveconf:ndays};` inside the script – leftjoin Sep 08 '16 at 07:36
  • Syntactically select ${hiveconf:ndays}; is correct when variable is set. I have just checked. Most probably the variable is not set. have you checked $var is not empty? – leftjoin Sep 08 '16 at 07:39
  • Hi, I have tried this. cat nds.ksh var=$(hive -e " set hive.cli.print.header=false; select datediff(CONCAT(y, '-', (m + 1), '-', '01'), CONCAT(y, '-', m, '-', '01')) FROM (SELECT month(current_date) as m, year(current_date) as y, day(current_date)) tabl1;") echo $var ksh nds.ksh I got value 30. Again I tried echo$var its not giving anything. Thanks – Rock Sep 08 '16 at 07:52
  • If it's possible could please provide the steps needs to be followed. – Rock Sep 08 '16 at 07:55
  • So the problem is var is not set. Maybe you are calling hive command from a subshell where var does not exist? – leftjoin Sep 08 '16 at 08:00
  • Hi, I am doing this. Please let me know am I missing anything. In UNIX prompt step1 : cat >nds.ksh var=$(hive -e " set hive.cli.print.header=false; select datediff(CONCAT(y, '-', (m + 1), '-', '01'), CONCAT(y, '-', m, '-', '01')) FROM (SELECT month(current_date) as m, year(current_date) as y, day(current_date)) tabl1;"); step2:cat >tt.hql select ${hiveconf:ndays}; step3: hive -hiveconf ndays=$var -f tt.hql Please help me. Thanks. – Rock Sep 08 '16 at 08:52
  • Ok, let's try one more time, I will make simplified example. 1) create HQL script file: vi tt.hql File content is: select ${hiveconf:ndays}; 2) create script file: vi nds.ksh File contents is three lines var=$(hive -e " set hive.cli.print.header=false; select 30;"); echo $var hive -hiveconf ndays=$var -f tt.hql #End of file – leftjoin Sep 08 '16 at 10:14
  • and now run it: chmod u+x nds.ksh - make this file executable first – leftjoin Sep 08 '16 at 10:15
  • ./nds.ksh -skipped some Hive output here OK Time taken: 0.724 seconds, Fetched: 1 row(s) 30 -----this is printed variable $var from second line of the script -skipped some Hive output here OK 30 --This is printed by select statement from the tt.hql – leftjoin Sep 08 '16 at 10:18
  • Once more the contents of the shell file (lines separated by \n): var=$(hive -e " set hive.cli.print.header=false; select 30;"); \n echo $var \n hive -hiveconf ndays=$var -f tt.hql – leftjoin Sep 08 '16 at 10:22
  • Hi, Thanks for your solution. It's working fine. In the below query instead of "current_date" I want to pass the date as parameter. var=$(hive -e " set hive.cli.print.header=false; select datediff(CONCAT(y, '-', (m + 1), '-', '01'), CONCAT(y, '-', m, '-', '01')) FROM (SELECT month(current_date) as m, year(current_date) as y, day(current_date)) tabl1;"); Please help me ASAP. Thanks – Rock Sep 11 '16 at 11:09
  • You can use shell variables inside queries: some_date='2016-09-11'; result=$(hive -e " set hive.cli.print.header=false; select $some_date"); – leftjoin Sep 11 '16 at 11:18
  • Hi , I have tried this it's priniting NULL. Please help me. cat > nds.ksh run_date=$1 var=$(hive -e " set hive.cli.print.header=false; select datediff(CONCAT(y, '-', (m + 1), '-', '01'), CONCAT(y, '-', m, '-', '01')) FROM (SELECT month(CAST('${hiveconf:run_date}') as m, year('${hiveconf:run_date}') as y, day('${hiveconf:run_date}')) tabl1;") echo $var ksh nds.ksh 2016-09-11 Thanks – Rock Sep 11 '16 at 12:10
  • hiveconf will not work inside shell with hive-e. hiveconf is for hive -f... For hive -e option use shell variables $variable like in my previous comment. – leftjoin Sep 11 '16 at 12:19
  • Hi, As you said I have use as $variable. Still it's printing as NULL. cat > nds.ksh run_date=$1 var=$(hive -e " set hive.cli.print.header=false; select datediff(CONCAT(y, '-', (m + 1), '-', '01'), CONCAT(y, '-', m, '-', '01')) FROM (SELECT month($run_date) as m, year($run_date) as y, day($run_date)) tabl1;") echo $var ksh nds.ksh '2016-09-11' Please help me. Thanks – Rock Sep 11 '16 at 12:34
  • Sorry man, I cannot debug your scripts here. How have you checked that the variable is passed correctly? BTW why are you doing this in such way? Why don't you just using date shell command to format your date then pass it to your hive script using hiveconf? – leftjoin Sep 11 '16 at 12:47
  • Hi, Date is not static. I want to find the number of days for a given date. I will be executing for different dates. SO that I cannot use shell command date. Please help me. Thanks, – Rock Sep 11 '16 at 12:54
  • It seems like you are trying to format some date, and maybe perform some calculation with date using Hive then save result and then again pass it to hive as a parameter to run some script. You can format any date and perform date calculations using shell date command, look at the documentation on date command, it's not just static date, it can perform complex date calculations. then pass the result to hive script using hiveconf. It is much more faster then running hive for date formatting/date math. Anyway I have demonstrated you how to pass variables to your hive scripts. – leftjoin Sep 11 '16 at 13:21
  • Hi,I have fixed it. Sorry to bother you.Thanks for your help – Rock Sep 11 '16 at 13:21
  • Hi , Finally we have to execute as hive script as per our requirement. I have tried this.It's not working. cat >exec_script.hql !ksh nds.ksh hive -f exec_script.hql 2016-09-11 Please help me. Thanks – Rock Sep 11 '16 at 13:51