一、前言
最近在使用Sqoop+Jenkins实现mysql与hive数据库数据互传。
主要是用sqoop的import命令把mysql数据导入hive，以及使用export命令把hive数据导出到mysql。
Jenkins起定时作用，定时执行sh脚本，每天同步一次；
Jenkins还起到了让sh脚本按顺序执行的功能，确保上一个脚本执行完毕后再开始执行下一个脚本。
相关笔记记录如下。
感觉Sqoop与kettle类似；不过听说Sqoop处理大数据迁移比kettle性能好
/*
kettle数据的具体流向可以指定，可以是各种数据的存储工具；
sqoop只是完成hdfs到关系型数据库 或者 关系型数据库到hdfs的数据传输，在传输的过程中保证传输数据的类型。
因为sqoop底层调用的是mapreduce，所以小数据量性能会受到限制。
十万，百万。千万级别kettle有优势。
亿级别的数据sqoop优势明显。
*/
二、Sqoop部分
1.linux登录hive数据库的方法
生产环境linux使用了一个keytab秘钥文件，使用这个文件登录hive数据库。
命令样例如下：
#!/bin/bash
kinit -kt /home/admin/keytab/myuser.keytab myuser
beeline -u "jdbc:hive2://xxx.abc.com:10001/;principal=hive/xxx.abc.com@BIGDATA.XXX.COM"
登录后，大部分操作命令与mysql类似，下方记录些常用命令：
//展示数据库
show databases;
//展示有哪些表
show tables;
//选择数据库abcDB
use abcDB;
//显示user表详情
desc user;
//显示user表的建表语句
show create table user;
2.准备工作
目标：把mysql的数据迁移到Hive，以及把Hive的数据迁移到Mysql。
软件安装过程略。
(1)首先，Mysql创建一个表mytest123
create table 'mytest123' {
'id' int(11) not null,
'name' varchar(255) character set utf8 collate utf8_general_ci NULL default null,
'create_time' datetime Null default Null,
Primary Key ('id')
}
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
ROW_FORMAT=DYNAMIC
;
创建表完成后，随便写几条数据，准备把这个表的数据迁移到Hive。
(2)Hive数据库创建一个表mytest123
登录hive数据库(例如使用keytab)，然后执行建表语句。
如果执行失败，可能是空格或换行符问题，尽量写成一行再试试。
drop table if exists test.mytest123;
create EXTERNAL table test.mytest123 (
id int comment 'id',
name string comment '姓名',
create_time decimal(8,0) comment '创建时间'
)comment '姓名表(自测用)'
PARTITIONED BY ('import_time' string)
STORED AS ORC;
PARTITIONED BY相当于多了一列import_time，可以标注表中的每一行属于哪个分区，select时可以选分区用来提高查找效率；
STORED AS是表格式。
orcfile：
存储方式：数据按行分块 每块按照列存储；
压缩快 快速列存取；
效率比rcfile高,是rcfile的改良版本。
3.Mysql到Hive
表创建完毕后，先试试Mysql同步数据导Hive。
主要是使用sqoop import语句，给出mysql数据库的连接url、账号、密码、查询sql，以及hive数据库的库名、表名、分区用的key与value（如果有分区列的话）等参数，就可以迁移数据了。
注意hive数据库的连接、账号、密码则不需要给出。
样例如下：
#接收传入的参数
MY_DATE=$1
MY_SQL="select t1.id as id, t1.name as name, date_format(t1.create_time, '%Y%m%d') as create_time from mytest123 t1 where create_time >="$MY_DATE" and \$CONDITIONS"
echo $MY_SQL
#mysql
JDBC_MYSQL_URL="jdbc:mysql://10.123.123.123:3306/test"
JDBC_MYSQL_USERNAME="root"
JDBC_MYSQL_PASSWORD="root"
#hive
DATABASE_NAME="test"
TABLE_NAME="mytest123"
#实际执行的方法
function execute_sync {
sqoop import -D org.apache.sqoop.splitter.allow_text_splitter=true \
--connect $JDBC_MYSQL_URL \
--username $JDBC_MYSQL_USERNAME \
--password $JDBC_MYSQL_PASSWORD \
--query "$MY_SQL" \
--hcatalog-database $DATABASE_NAME \
--hcatalog-table $TABLE_NAME \
--hive-partition-key "import_time" \
--hive-partition-value "MY_DATE" \
--null-string '\\N' \
--null-non-string '\\N' \
-m 1
}
#key是分区列import_time,对应的value是$MY_DATE
#每插入一行,列import_time的值就会设置为$MY_DATE
#执行
execute_sync
然后，存入日期参数并执行这个sh文件，就可以把mysql中的test.mytest123表中的数据同步到hive库中的test.mytest123表。
./mytest.sh 20211018
4.Hive到Mysql
目标：从Hive的test.mytest123表同步数据导Mysql的test.mytest456表。
更换了mysql表，改成了使用export语句，并且不能写sql了：
sh样例如下：
#mysql
JDBC_MYSQL_URL="jdbc:mysql://10.123.123.123:3306/test"
JDBC_MYSQL_USERNAME="root"
JDBC_MYSQL_PASSWORD="root"
JDBC_MYSQL_TABLE="mytest456"
#hive
DATABASE_NAME="test"
TABLE_NAME="mytest123"
#实际执行的方法
function execute_sync {
sqoop import -D org.apache.sqoop.splitter.allow_text_splitter=true \
--connect $JDBC_MYSQL_URL \
--username $JDBC_MYSQL_USERNAME \
--password $JDBC_MYSQL_PASSWORD \
--table $JDBC_MYSQL_TABLE \
--update-key id \
--update-mode allowinsert \
--hcatalog-database $DATABASE_NAME \
--hcatalog-table $TABLE_NAME \
--colunms="id,name,create_time" \
--null-string '\\N' \
--null-non-string '\\N' \
-m 1
}
#执行
execute_sync
注意：
mysql的表换成了mytest456，create_time字段从datetime换成了varchar；
因为hive中的create_time是decimal类型(例如20211018)，如果还用datetime接收，不会报错，但是会为null，所以要换成varchar接收。
从mysql到hive时，可以写sql，所以类型转换容易处理些；
从hive到mysql，使用hcatalog参数的话，不能写sql，所以类型转换较难。
关于hive到mysql类型转换问题，有一种方法：将 Hive 表数据【用SQL查询出来】放到 HDFS 临时目录，然后 get 到 本地，使用 MySQL load file 方式，这种方式 显然比较麻烦，但SQL 对表字段内容处理支持很好，更灵活。
启动命令样例如下：
./mytest2.sh
三、Jenkins部分
1.功能
在这里，Jenkins主要用来定时执行sh任务。
安装步骤省略，使用步骤见下方。
2.使用步骤
(1)登录Jenkins
(2)点击左侧"新建任务"
(3.1)输入任务名称，例如mytest，下方可以选择"根据一个已存在的任务创建"，意思就是复制一个已存在的任务，这里不演示这种方法。
(3.2)输入任务名称，点击"构建一个多配置项目"，勾选"添加到当前视图"，单击"ok"
(4)进入General标签，可以写描述；选中"参数化构建过程"->“添加参数”->“Date Parameter”，配置一个DATE参数，用来最后给sh传递参数，如下：
Date Parameter
Name: DATE
Date Format: yyyyMMdd
Default Value: LocalDate.now().minusDays(1)
Description: date_param
配置了DATE参数的默认值为"当前时间-1"天。
点击问号可以看详情。
(5)AdvancedProject Project Options，一些高级配置，这里不设置
(6)源码管理，当自动化发布jar/war包时，可以配置git代码路径，这里不设置
(7)构建触发器，选中"定时构建"，可以配置定时执行的时间，例如每天3点执行一次:
H 3 * * *
常用的配置还有“其他工程构建后触发”。
(8)Configuration Matrix，可以配置混合参数，这里不设置。
(9)构建环境，这里不设置。
(10)构建，点击"增加构建步骤"->“执行shell”，然后输入shell命令，例如：
cd /home/admin/shell
sh mytest.sh $DATE
意思是，执行mytest.sh时，会传参$DATE。
(11)构建后操作，这里不设置。
(12)点击保存。
(13)在Jenkins任务列表中找到刚创建的mytest任务，因为配置了定时时间，所以它会在每天3点执行一次，日期参数为当前时间-1天。
如果想让它立即执行，可以点击该任务，点击"Build with Parameters"，然后可以修改本次执行的DATE参数，点击"开始构建"，任务则会立即开始执行。
四、其它笔记
1.sql的where语句一定要加and \$CONDITIONS
2.sql参数需要双引号；换行后参数末尾要加 \ ：--query "$MY_SQL" \
3.对于 hive Parquet+Snappy 格式的数据导出 ，使用 hcatalog 参数， 如果不使用 ，可以采用 另外一种方式 解决，将 Hive 表数据查询出来放到 HDFS 临时目录，然后 get 到 本地，使用 MySQL load file 方式，这种方式 显然比较麻烦，但SQL 对表字段内容处理支持很好，更灵活。
|