1、介绍
1、sqoop介绍
sqoop是Apache旗下的一款 Hadoop和关系型数据库服务器之间传输数据的工具。
- 导入数据(import):MySQL、Oracle导入数据到hadoop的HDFS、Hive、HBase等数据存储系统
- 导出数据(export):从Hadoop的文件系统中导出到关系型数据库中
2、sqoop架构
3、sqoop命令解析
-
常用命令
命令名称 对应类 命令说明 import ImportTool 将关系型数据库数据导入到HDFS、HIVE、HBASE export ExportTool 将HDFS上的数据导出到关系型数据库 codegen CodeGenTool 获取数据库中某张表生成java并打成jar包 create-hive-table CreateHiveTool 创建Hive表 eval EvalSqlTool 查看SQL执行结果 list-databases ListDatabasesTool 列出所有数据库 list-tables ListTablesTool 列出所有数据表 help HelpTool 打印sqoop帮助信息 version VersionTool 打印sqoop版本信息 -
常用参数
Argument Description --connect <jdbc-url>
指定JDBC连接字符串 --connect-manager <class-name>
指定要使用的连接管理器类 --driver <class-name>
指定要使用的JDBC驱动类 --hadoop-mapred-home <dir>
指定$HADOOP_MAPRED_HOME路径 --password-file
设置用于存放认证密码信息文件的路径 -P
从控制台读取输入密码 --password <password>
设置认证密码 --username <username>
设置认证用户名 --verbose
打印运行信息 --connection-param-file <filename>
指定存储数据库连接参数的属性文件 -
举个例子
# 查询指定数据库下所有表 sqoop list-tables --connect jdbc:mysql://localhost:3306/hive_meta --username root --password 12345
2、Sqoop的安装
-
解压并修改配置文件
# 上传压缩包,解压 tar -zxvf sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz -C /opt/module/ # 改名字 mv sqoop-1.4.6.bin__hadoop-2.0.4-alpha sqoop-1.4.6 # 修改配置文件,在sqoop的conf目录下,先将模板复制成sqoop-env.sh cp sqoop-env-template.sh sqoop-env.sh # 修改sqoop-env.sh vim sqoop-env.sh
-
添加mysql的驱动到sqoop的lib目录下
-
配置sqoop环境变量
# 打开配置文件 vim /etc/profile # 添加下面两行 export SQOOP_HOME=/opt/module/sqoop-1.4.6 export PATH=$PATH:$SQOOP_HOME/bin # 重新加载环境变量 source /etc/profile
-
测试sqoop能否接通MySQL
sqoop list-databases --connect jdbc:mysql://192.168.48.10:3306 --username root --password 123456
3、sqoop应用
1、eval查看sql语句查询结果
sqoop eval \
--connect jdbc:mysql://localhost:3306/sqoop_db \
--username root \
--password 12345 \
--query 'select * from mysql_gxm limit 20'
2、create-hive-table创建hive表
# 依据mysql的mysql_gxm表在hive的hivedatabase数据库中创建hive_gxm表,只是创建,没有数据
sqoop create-hive-table \
--connect jdbc:mysql://localhost:3306/sqoop_db \
--username root \
--password 12345 \
--table mysql_gxm
--hive-table hivedatabase.hive_gxm
3、import将数据导入HDFS
# 语法
sqoop import \
--connect 数据库连接字符串 \
--username 数据库用户名 \
--password 数据库密码 \
--target-dir HDFS位置 \
--delete-target-dir \
--fields-terminated-by 指定分割符 \
--num-mappers 指定MapReduce中map的数量 \
--split-by 切分数据分给各个map的依据 \
--query '查询语句'
注意: --query
或-e
将查询语句的结果导入,使用时必须伴随参数–target-dir
,--hive-table
,如果查询语句中有where
条件,则条件后必须加上$CONDITIONS
关键字
3.1import导入文本文件
sqoop import \
--connect jdbc:mysql://localhost:3306/sqoop_db"?useUnicode=true&characterEncoding=UTF-8" \
--username root \
--password 123456 \
--target-dir /sqoop_data/goods \
--delete-target-dir \
--fields-terminated-by "\t" \
--num-mappers 4 \
--split-by id \
--query 'select * from goods_table where id < 10 and $CONDITIONS'
# 注意:
# --split-by: 一般都是数值型
# -Dorg.apache.sqoop.splitter.allow_text_splitter=true: --split-by 指定字符串也可以
3.2导入其他类型文件
# 导入不同格式,支持格式as-avrodatafile、as-parquetfile、as-sequencefile、as-textfile(默认格式)
# 多次导入时会报jar包已存在错误,请忽略,因为sqoop读取数据源数据的schema文件创建的jar包在前几次任务已经创建了
sqoop import \
--connect jdbc:mysql://localhost:3306/sqoop_db"?useUnicode=true&characterEncoding=UTF-8" \
--username root \
--password 123456 \
--as-parquetfile \
--target-dir /sqoop_data/goods_2_parquet \
--delete-target-dir \
--fields-terminated-by "\t" \
--num-mappers 4 \
--split-by id \
--query 'select * from goods_table where id < 10 and $CONDITIONS'
4、将数据全量导入到HIVE
sqoop import \
--connect jdbc:mysql://localhost/sqoop_db"useUnicode=true&characterEncoding=utf-8" \
--username root \
--password 123456 \
--table goods_table \
--num-mappers 4 \
--delete-target-dir \
--hive-import \
--fields-terminated-by "\t" \
--hive-overwrite \
--hive-table sqoop.hive_goods_table
上面过程分为两步
- 将数据导入到HDFS,默认的临时目录是 /user/当前操作用户/mysql表名 ,通过查看job的configuration的outputdir属性可知
- 将导入到HDFS的数据迁移到Hive表,如果hive表不存在,sqoop会自动创建内部表。
5、将数据增量导入到HIVE
1、append方式
# 按id增量导入
sqoop import \
--connect jdbc:mysql://localhost:3306/sqoop_db"?useUnicode=true&characterEncoding=UTF-8" \
--username root \
--password 123456 \
--table mysql_gxm
--num-mapper 3 \
--target-dir /hive/warehouse/hive.db/hive_gxm \
--fields-terminated-by "\001" \
--incremental append \
--check-column id \
--last-value 1
# 参数解释
# incremental <mode> :append或lastmodified,使用lastmodified方式导入数据要指定增量数据是要--append(追加)还是--merge-key(合并)
# check-column <字段> :作为增量导入判断的列名
# last-value val :指定某一个值,用于标记增量导入的位置,这个值的数据不会被导入到表中,指的是mysql中id值
2、lastmodified方式
必须要加--append
(追加)或者--merge-key
(合并,一般填主键)
-
--append
追加sqoop import \ --connect jdbc:mysql://localhost:3306/sqoop_db"?useUnicode=true&characterEncoding=UTF-8" \ --username root \ --password 123456 \ --table good_update_tablde \ --target-dir /hive/warehouse/hive.db/good_update_table \ --fields-terminated-by "\001" \ --incremental lastmodified \ --check-column last_update_time \ --last-value '2020-10-10 13:00:00' \ --append # 注意: # --incremental lastmodified :lastmodified是上次修改的意思 # --last-value 的设置是把包括 2020-10-10 13:00:00 时间以及之后时间的数据做增量导入
-
--merge-key
合并sqoop import \ --connect jdbc:mysql://localhost:3306/sqoop_db"?useUnicode=true&characterEncoding=UTF-8" \ --username root \ --password 123456 \ --table mysql_gxm \ --num-mappers 3 \ --target-dir /hive/warehouse/hive.db/hive_gxm \ --fields-terminated-by "\001" \ --incremental lastmodified \ --check-column last_update_time \ --last-value '2020-10-10 13:00:00' \ --merge-key id # --incremental lastmodified --merge-key : 修改过的数据和新增的数据,(前提是满足last-value的条件)都会导入进来,并且重复的数据(不需要满足last-value的条件)都会进行合并
6、Sqoop的简单使用案例
6.1、在MySQL中创建库和表
create database mall;
use mall;
6.2、导入数据 ==> MySQL
在Sqoop中,“导入”概念指:从非大数据集群(RDBMS)向大数据集群(HDFS,HIVE,HBASE)中传输数据,叫做:导入,使用import关键字。
6.2.1、全部导入:--table
sqoop import \
--connect jdbc:mysql://192.168.48.10:3306/mall \
--username root \
--password 123456 \
--table order_info \
--target-dir hdfs://192.168.48.10:9000/sqoop_data/mall/db/order_info \
--delete-target-dir \
--m 1 \
--fields-terminated-by ',';
6.2.2、查询导入:--query
sqoop import \
--connect jdbc:mysql://192.168.48.10:3306/mall \
--username root \
--password 123456 \
--target-dir hdfs://192.168.48.10:9000/sqoop_data/mall/db/order_info/2019-06-10 \
--delete-target-dir \
--m 1 \
--fields-terminated-by ',' \
--query 'select * from order_info where date_format(create_time,"%Y-%m-%d")="2019-06-10" and $CONDITIONS';
注意:sql语句末尾必须加上$CONDITIONS
6.2.3、导入指定列:--columns
sqoop import \
--connect jdbc:mysql://192.168.48.10:3306/mall \
--username root \
--password 123456 \
--target-dir hdfs://192.168.48.10:9000/sqoop_data/mall/db/order_info/columns \
--delete-target-dir \
--m 1 \
--fields-terminated-by ',' \
--columns id,consignee,consignee_tel \
--table order_info;
注意:columns中如果涉及到多列,用逗号分隔,分隔时不要添加空格
6.2.4、筛选查询导入数据:--where
sqoop import \
--connect jdbc:mysql://192.168.48.10:3306/mall \
--username root \
--password 123456 \
--target-dir hdfs://192.168.48.10:9000/sqoop_data/mall/db/order_info/where \
--delete-target-dir \
--m 1 \
--fields-terminated-by ',' \
--table order_info \
--where id=2;
6.3、导入数据 ==> Hive
sqoop import \
--connect jdbc:mysql://192.168.48.10:3306/mall \
--username root \
--password 123456 \
--m 1 \
--table user \
--hive-import \
--hive-database db_order \
--fields-terminated-by ',' \
--hive-overwrite \
--hive-table t_user;
- 该过程分为两步,第一步将数据导入到HDFS,第二步将导入到HDFS的数据迁移到Hive仓库
- 第一步默认的临时目录是/user/用户名/表名,按上方来的话,临时目录路径为 /user/root/user
hive也可以直接从HDFS中加载数据
6.4、导出数据
在Sqoop中,“导出”概念指:从大数据集群(HDFS,HIVE,HBASE)向非大数据集群(RDBMS)中传输数据,叫做:导出,使用export关键字。
-
HIVE ==> MySQL
-
先将表中数据导入到HDFS中
sqoop import \ --connect jdbc:mysql://192.168.48.10:3306/mall \ --username root \ --password 123456 \ --table user \ --target-dir hdfs://192.168.48.10:9000/sqoop_data/mall/db/user \ --delete-target-dir \ --m 1 \ --fields-terminated-by ',';
-
再将数据库中的数据删除,由sqoop将hdfs上的数据导出到mysql数据库中
sqoop export \ --connect jdbc:mysql://192.168.48.10:3306/mall \ --username root \ --password 123456 \ --export-dir hdfs://192.168.48.10:9000/sqoop_data/mall/db/user \ --table user \ -m 1 \ --input-fields-terminated-by ',';
注意:MySQL中如果表不存在,不会自动创建,会报错;数据默认是追加,不是覆盖。
-
7、脚本编写
将mysql中的数据迁到hdfs中。
#!/bin/bash
App=mall
if [ -n $2 ]
then
date=$2
else
date=`date -d '-l day' +%F`
fi
# 导入数据函数
import_data(){
sqoop import \
--connect jdbc:mysql://192.168.48.10:3306/$App \
--username root \
--password 123456 \
--target-dir hdfs://192.168.48.10:9000/sqoop_data/mall/db/$1/$date \
--delete-target-dir \
--m 1 \
--fields-terminated-by ',' \
--query "$2 and \$CONDITIONS" \
--null-string '\\N' \
--null-non-string '\\N'
}
# 按表调用导入数据函数
# order_info表
order_info_data(){
import_data order_info "select * from order_info where date_format(create_time,'%Y-%m-%d')='$date'"
}
# payment_info表
payment_info_data(){
import_data payment_info "select * from payment_info where date_format(payment_time,'%Y-%m-%d')='$date'"
}
# 根据输入调用函数
case $1 in
"order_info")
order_info_data
;;
"payment_info")
payment_info_data
;;
"all")
order_info_data
payment_info_data
esac
# 执行脚本
mysql_to_hdfs.sh all 2019-06-09