bash脚本中单元格( .hql)文件中的命令行参数

Question

我有一个主bash脚本，运行其他几个bash脚本和hql文件。hql文件具有单元查询。单元查询有一个where子句，它位于date字段上。我正在尝试自动化一个过程，我需要where子句根据今天的日期进行更改(这是从主bash脚本获得的)。

例如，.hql文件如下所示:这是selectrows.hql

DROP TABLE IF EXISTS tv.events_tmp;
CREATE TABLE tv.events_tmp
(   origintime STRING,
 deviceid STRING,
 clienttype STRING,
 loaddate STRING)
 ROW FORMAT DELIMITED FIELDS TERMINATED BY '\u0001'
 LINES TERMINATED BY '\n'
 STORED AS TEXTFILE LOCATION 'hdfs://nameservice1/data/full/events_tmp';

INSERT INTO TABLE tv.events_tmp SELECT origintime, deviceid, clienttype, loaddate FROM tv.events_tmp WHERE origintime >= '2015-11-02 00:00:00' AND origintime < '2015-11-03 00:00:00';

由于今天是2015-11-11，我希望能够将date - 9 days和date-8 days从bash脚本传递给.hql脚本。有没有办法将这两个变量从bash脚本传递到.hql文件。

因此，主要bash脚本如下所示：

#!/bin/bash
# today's date
prodate=`date +%Y-%m-%d`
echo $prodate
dateneeded=`date -d "$prodate - 8 days" +%Y-%m-%d`
echo $dateneeded

# CREATE temp table
beeline -u 'jdbc:hive2://datanode:10000/;principal=hive/datanode@HADOOP.INT.BELL.CA' -d org.apache.hive.jdbc.HiveDriver -f /home/automation/tv/selectrows.hql
echo "created table"

提前谢谢。

Answer 1

可以使用beeline -e选项使用字符串执行查询。然后将日期参数传递给字符串。

#!/bin/bash
# today's date
prodate=`date +%Y-%m-%d`
echo $prodate
dateneeded8=`date -d "$prodate - 8 days" +%Y-%m-%d`
dateneeded9=`date -d "$prodate - 9 days" +%Y-%m-%d`
echo $dateneeded8
echo $dateneeded9

hql="
DROP TABLE IF EXISTS tv.events_tmp;
CREATE TABLE tv.events_tmp
(   origintime STRING,
 deviceid STRING,
 clienttype STRING,
 loaddate STRING)
 ROW FORMAT DELIMITED FIELDS TERMINATED BY '\u0001'
 LINES TERMINATED BY '\n'
 STORED AS TEXTFILE LOCATION 'hdfs://nameservice1/data/full/events_tmp';

INSERT INTO TABLE tv.events_tmp SELECT origintime, deviceid, clienttype, loaddate FROM tv.events_tmp WHERE origintime >= '"

echo "$hql""$dateneeded9""' AND origintime < '""$dateneeded8""';"

# CREATE temp table
beeline -u 'jdbc:hive2://datanode:10000/;principal=hive/datanode@HADOOP.INT.BELL.CA' -d org.apache.hive.jdbc.HiveDriver -e "$hql""$dateneeded9""' AND origintime < '""$dateneeded8""';"
echo "created table"

Answer 2

传递论点的另一种方式

创建带有定义变量的单元格.hql文件

vi multi_var_file.hql 
SELECT * FROM TEST_DB.TEST_TB WHERE TEST1='${var_1}' AND TEST2='${var_2}';

将相同的变量传递到Hive脚本以运行

hive -hivevar var_1='TEST1' -hivevar var_2='TEST2' -f multi_var_file.hql