Sqoop+Jenkins实现Mysql与Hive数据库互传

Sqoop+Jenkins实现Mysql与Hive数据库互传 一、前言

最近在使用Sqoop+Jenkins实现mysql与hive数据库数据互传。

主要是用sqoop的import命令把mysql数据导入hive，以及使用export命令把hive数据导出到mysql。

Jenkins起定时作用，定时执行sh脚本，每天同步一次；
Jenkins还起到了让sh脚本按顺序执行的功能，确保上一个脚本执行完毕后再开始执行下一个脚本。

相关笔记记录如下。

感觉Sqoop与kettle类似；不过听说Sqoop处理大数据迁移比kettle性能好

二、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/[email protected]"

登录后，大部分 *** 作命令与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 对表字段内容处理支持很好，更灵活。