crontab shell调用spark-sql，实现周期性动态SQL批量自动执行

对于熟悉Scala开发的人来说，对于spark-sql的使用，直接jar包中写入代码处理就能轻松实现动态语句的执行。
但是对于我，不打算学习Scala和Java语言，但是又想定时执行时间推延的周期、定时任务，该肿么办？

spark-sql CLI几个参数，完全满足我等非专业人员。

CLI options:
 -d,--define <key=value>          Variable subsitution to apply to hive
                                  commands. e.g. -d A=B or --define A=B
    --database <databasename>     Specify the database to use
 -e <quoted-query-string>         SQL from command line
 -f <filename>                    SQL from files
 -H,--help                        Print help information
    --hiveconf <property=value>   Use value for given property
    --hivevar <key=value>         Variable subsitution to apply to hive
                                  commands. e.g. --hivevar A=B
 -i <filename>                    Initialization SQL file
 -S,--silent                      Silent mode in interactive shell
 -v,--verbose                     Verbose mode (echo executed SQL to the
                                  console)
###########################
-e 可以在命令中带sql语句，但是明显不适合大量的sql。小量语句可以考虑。
-f sql文件，只要把sql写入文件，直接批量提交，全部语句一次性任务，如果周期变化了，怎么办？
--hiveconf 主角登场，可以把参数带入sql语句中替换，在shell中赋值，实现动态sql。

测试前提：预先pcp2表是存在的，这里不错复杂判断表格是否存在后再查询。
###########################################################
测试一：
#简单替换SQL中的参数 -hiveconf 把2个参数替代sql文件里面的动态参数
spark-sql -S -hiveconf table_name='pcp2' -hiveconf row_limit='10' -f /opt/hadoop/sql/spark-test.sql

#########################
--这个SQL文件中，查询的表名字，返回的条目数，由外部输入。
spark-test.sql  内容如下：

show tables;
select * from ${hiveconf:table_name} limit ${hiveconf:row_limit};

插图1

#########################
[root@snn sql]# spark-sql -S -hiveconf table_name='pcp2' -hiveconf row_limit='10' -f /opt/hadoop/sql/spark-test.sql
15/12/18 11:41:59 WARN Utils: Service 'SparkUI' could not bind on port 4040. Attempting port 4041.
15/12/18 11:42:09 WARN MetricsSystem: Using default name DAGScheduler for source because spark.app.id is not set.
SET hive.support.sql11.reserved.keywords=false
15/12/18 11:43:23 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
SET spark.sql.hive.version=1.2.1
SET spark.sql.hive.version=1.2.1
15/12/18 11:43:39 WARN YarnScheduler: Initial job has not accepted any resources; check your cluster UI to ensure that workers are registered and have sufficient resources
external_2000w  false
external_2000w_new      false
external_bz2_tbl_6005   false
external_tar_gz_tbl_6005        false
external_tbl_6005       false
hive_user_info  false
pcp     false
pcp2    false
sqoop_test      false
tbl_6005        false
test_table_16538        false
test_table_16539        false
浙江    杭州    300
浙江    宁波    150
浙江    温州    200
浙江    嘉兴    100
江苏    南京    270
江苏    苏州    299
江苏    某市    200
江苏    某某市  100
[root@snn sql]#

###########################################################
测试二：
#shell外部传入参数SQL执行。
/bin/bash ./spark-parameter.sh pcp2 10

#################
spark-parameter.sh  内容如下：

#!/bin/bash
sh_table_name=$1
sh_row_limit=$2
$SPARK_HOME/bin/spark-sql -S -hiveconf table_name=$sh_table_name -hiveconf row_limit=$sh_row_limit -f /opt/hadoop/sql/spark-test.sql

插图2

#########################
[root@snn sql]# /bin/bash ./spark-parameter.sh pcp2 10
15/12/18 11:52:32 WARN Utils: Service 'SparkUI' could not bind on port 4040. Attempting port 4041.
15/12/18 11:52:43 WARN MetricsSystem: Using default name DAGScheduler for source because spark.app.id is not set.
SET hive.support.sql11.reserved.keywords=false
15/12/18 11:53:52 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
SET spark.sql.hive.version=1.2.1
SET spark.sql.hive.version=1.2.1
15/12/18 11:54:09 WARN YarnScheduler: Initial job has not accepted any resources; check your cluster UI to ensure that workers are registered and have sufficient resources
external_2000w  false
external_2000w_new      false
external_bz2_tbl_6005   false
external_tar_gz_tbl_6005        false
external_tbl_6005       false
hive_user_info  false
pcp     false
pcp2    false
sqoop_test      false
tbl_6005        false
test_table_16538        false
test_table_16539        false
浙江    杭州    300
浙江    宁波    150
浙江    温州    200
浙江    嘉兴    100
江苏    南京    270
江苏    苏州    299
江苏    某市    200
江苏    某某市  100
[root@snn sql]#

###########################################################
测试三：
#小量SQL测试需要，固定2个参数，可以根据需要调整一下。
#方便crontab自动触发shell，shell可以浮动生成日期后缀。
/bin/bash ./spark-autoparameter.sh pcp2 10

##############
spark-autoparameter.sh 内容如下：(注意shell文件中*星号这个通配符,后面引用需要引号，否则做文件匹配)

#!/bin/bash
sh_table_name=$1
sh_row_limit=$2
#seconds from 1970-01-01
date_seconds=`date +%s`
echo $date_seconds
sh_table_date=`expr $date_seconds / 86400`
echo $sh_table_date
#Statment_create
creat_sql="create table ${sh_table_name}_$sh_table_date as select * from ${sh_table_name} limit ${sh_row_limit};"
echo "${creat_sql}"
$SPARK_HOME/bin/spark-sql -e "${creat_sql}"

插图3

#########################
[root@snn sql]# /bin/bash ./spark-autoparameter.sh pcp2 10
1450413019
16787
create table pcp2_16787 as select * from pcp2 limit 10;
15/12/18 12:30:45 WARN MetricsSystem: Using default name DAGScheduler for source because spark.app.id is not set.
SET hive.support.sql11.reserved.keywords=false
15/12/18 12:31:54 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
SET spark.sql.hive.version=1.2.1
SET spark.sql.hive.version=1.2.1
Time taken: 21.701 seconds
[root@snn sql]#

##############  检查新表是否生成？
[root@snn sql]# spark-sql
15/12/18 12:33:06 WARN MetricsSystem: Using default name DAGScheduler for source because spark.app.id is not set.
SET hive.support.sql11.reserved.keywords=false
15/12/18 12:34:13 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
SET spark.sql.hive.version=1.2.1
SET spark.sql.hive.version=1.2.1
spark-sql> select * from pcp2_16787;
浙江    杭州    300
浙江    宁波    150
浙江    温州    200
浙江    嘉兴    100
江苏    南京    270
江苏    苏州    299
江苏    某市    200
江苏    某某市  100
Time taken: 19.368 seconds, Fetched 8 row(s)
spark-sql>

###########################################################
测试四：
#方便crontab自动触发shell，shell可以浮动生成日期后缀。
#代码量很大，-e 不适合处理时，可以shell调用sql文件
/bin/bash ./spark-autoparameter_sql_file.sh pcp2

## spark-autoparameter_sql_file.sh ，如下：

#!/bin/bash
sh_table_name=$1
#seconds from 1970-01-01
date_seconds=`date +%s`
echo $date_seconds
sh_table_date=`expr $date_seconds / 86400`
echo $sh_table_date
#Statment_create
$SPARK_HOME/bin/spark-sql -S -hiveconf table_name="another_${sh_table_name}_${sh_table_date}" -hiveconf org_table_name="${sh_table_name}" -f /opt/hadoop/sql/spark-create.sql

##需要执行的sql语句集合，如下：
spark-create.sql

create table ${hiveconf:table_name} as select * from ${hiveconf:org_table_name};
show tables;

插图4

#########################
[root@snn sql]# vi spark-autoparameter_sql_file.sh
[root@snn sql]# /bin/bash ./spark-autoparameter_sql_file.sh pcp2
1450414909
16787
15/12/18 13:02:15 WARN MetricsSystem: Using default name DAGScheduler for source because spark.app.id is not set.
SET hive.support.sql11.reserved.keywords=false
15/12/18 13:03:23 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
SET spark.sql.hive.version=1.2.1
SET spark.sql.hive.version=1.2.1
another_pcp2_16787      false             -------------生成的表
external_2000w  false
external_2000w_new      false
external_bz2_tbl_6005   false
external_tar_gz_tbl_6005        false
external_tbl_6005       false
hive_user_info  false
pcp     false
pcp2    false
pcp2_16787      false
sqoop_test      false
tbl_6005        false
test_table_16538        false
test_table_16539        false
[root@snn sql]#

##############  检查新表

[root@snn sql]# spark-sql
15/12/18 13:07:03 WARN MetricsSystem: Using default name DAGScheduler for source because spark.app.id is not set.
SET hive.support.sql11.reserved.keywords=false
15/12/18 13:08:12 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
SET spark.sql.hive.version=1.2.1
SET spark.sql.hive.version=1.2.1
spark-sql> select * from another_pcp2_16787;
浙江    杭州    300
浙江    宁波    150
浙江    温州    200
浙江    嘉兴    100
江苏    南京    270
江苏    苏州    299
江苏    某市    200
江苏    某某市  100
Time taken: 18.441 seconds, Fetched 8 row(s)

spark-sql>

至此，圆满完成简单的shell和spark-sql结合，只要根据需要修改sql文件里面的内容，在crontab -e中加入需要的环境变量或者脚本即可实现自动化。