Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
736 views
in Technique[技术] by (71.8m points)

shell - passing multiple dates as a paramters to Hive query

I am trying to pass a list of dates as parameter to my hive query.

#!/bin/bash
echo "Executing the hive query - Get distinct dates"
var=`hive -S -e "select distinct  substr(Transaction_date,0,10) from test_dev_db.TransactionUpdateTable;"`
echo $var
echo "Executing the hive query - Get the parition data"
hive -hiveconf paritionvalue=$var -e 'SELECT Product FROM test_dev_db.TransactionMainHistoryTable where tran_date in("${hiveconf:paritionvalue}");'
echo "Hive query - ends"

Output as:

Executing the hive query - Get distinct dates
2009-02-01 2009-04-01
Executing the hive query - Get the parition data

Logging initialized using configuration in file:/hive/conf/hive-log4j.properties
OK
Product1
Product1
Product1
Product1
Product1
Product1
Time taken: 0.523 seconds, Fetched: 6 row(s)
Hive query - ends

It's only taking only first date as input. I would like to pass my dates as ('2009-02-01','2009-04-01') Note:TransactionMainHistoryTable is partitioned on tran_date column with string type.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Collect array of distinct values using collect_set and concatenate it with delimiter ','. This will produce list without outer quotes 2009-02-01','2009-04-01 and in the second script add outer quotes ' also, or you can add them in the first query. And when executing in inline sql (-e option) you do not need to pass hiveconf variable, direct shell variable substitution will work. Use hiveconf when you are executing script from file (-f option)

Working example (use your table instead of stack):

date_list=$(hive -S -e "select concat_ws('\',\'',collect_set(substr(dt,0,10))) from (select stack (2,'2017-01', '2017-02')as dt)s ;")

hive -e "select * from (select stack (2,'2017-01', '2017-02')as dt)s where dt in ('${date_list}');"

Returns:

OK

2017-01
2017-02
Time taken: 1.221 seconds, Fetched: 2 row(s)

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...