对于熟悉Scala开发的人来说,对于spark-sql的使用,直接jar包中写入代码处理就能轻松实现动态语句的执行。
但是对于我,不打算学习Scala和Java语言,但是又想定时执行时间推延的周期、定时任务,该肿么办?
spark-sql CLI几个参数,完全满足我等非专业人员。
CLI options:
-d,--define Variable subsitution to apply to hive
commands. e.g. -d A=B or --define A=B
--database Specify the database to use
-e SQL from command line
-f SQL from files
-H,--help Print help information
--hiveconf Use value for given property
--hivevar Variable subsitution to apply to hive
commands. e.g. --hivevar A=B
-i 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中加入需要的环境变量或者脚本即可实现自动化。