SQOOP 数据导入与导出

SQOOP 数据导入与导出

简介

Apache Sqoop是在Hadoop生态体系和RDBMS体系之间传送数据的一种工具，来自于Apache软件基金会提供

主要功能：

1. 导入数据(import)：从RDBMS的MySQL，Oracle 导入数据到 Hadoop 的 HDFS、Hive、Hbase 等数据存储系统
2. 导出数据(export)：从 Hadoop 的文件系统中导出数据到关系数据库 Mysql等

Export(导出)

默认模式是将HDFS上的数据导入到Mysql中
HDFS --> Mysql

sqoop export \
--connect jdbc:mysql://192.168.64.180:3306/test \
--username root \
--password ****** \
--table sqp_order \
-m 1 \
--export-dir /test/kb12 \
--fields-terminated-by '\t'

选项	含义
–connect	指定jdbc连接，♥♥注意需要指定到库名
–username	用户名
–passname	密码
–table	指定mysql中的表
-m	指定map task 的数目
–export-dir	指定HDFS上的文件路径

Import(导入)

Mysql --> HDFS

1. 全量导入 Mysql上的数据到HDFS中

sqoop import \
--connect jdbc:mysql://192.168.64.180:3306/test \
--username root \
--password ******** \
--table sqp_order \
-m 1 \
--delete-target-dir \
--target-dir /test/kb12/sqoop/m2h_all \
--fields-terminated-by '\t' \
--lines-terminated-by '\n'

选项	含义
–connect	指定jdbc连接，♥♥注意需要指定到库名
–username	用户名
–passname	密码
–table	指定mysql中的表
-m	指定map task 的数目
–delete-target-dir	如果存在目录则删除
–target-dir	指定HDFS上的路径
–fields-terminated-by	指定字段分割符
–lines-terminated-by	指定行分隔符

2. 导入部分Mysql数据到HDFS上（列裁剪、行裁剪、多个reducer）

列裁剪与全量导入只有一处不同

选项	含义
–columns	指定mysql表中需要导入的列

sqoop import \
--connect jdbc:mysql://192.168.64.180:3306/test \
--username root \
--password ******* \
--table sqp_order \
--columns user_name,total_volume \
-m 1 \
--delete-target-dir \
--target-dir /test/kb12/sqoop/m2h_colcut \
--fields-terminated-by '\t' \
--lines-terminated-by '\n'
	

行裁剪与全量导入也只有一处不同

选项	含义
–where	where条件用于筛选满足条件的行

sqoop import \
--connect jdbc:mysql://192.168.64.180:3306/test \
--username root \
--password ******** \
--table sqp_order \
--where "total_volume >= 200" \
--delete-target-dir \
--target-dir /test/kb12/sqoop/m2h_rowcut \
--fields-terminated-by '\t' \
--lines-terminated-by '\n'

query查询

选项	含义
–query	query查询筛选需要导入的数据，可同时实现行、列裁剪
–split-by	配合 -m 使用，用于指定根据哪个字段进行划分并启动多少个maptask。

注意事项：
  ♥使用query sql语句来进行查找不能加参数--table，需要指定 –target-dir 路径
 ♥♥并且必须要添加where条件
♥♥♥并且where条件后面必须带一个$CONDITIONS 这个字符串
 
	
sqoop import \
--connect jdbc:mysql://192.168.64.180:3306/test \
--username root \
--password ********* \
--query "select user_name,total_volume from sqp_order where total_volume >=300 and \$CONDITIONS" \
-m 2 \
--split-by user_name \
--delete-target-dir \
--target-dir /test/kb12/sqoop/m2h_rowcut2 \
--fields-terminated-by '\t' \
--lines-terminated-by '\n'

3. ♥♥♥增量导入(append、lastmodified)

选项	含义
–check-column	用来指定一些列，这些列在增量导入时用来检查这些数据是否作为增量数据进行导入
–incremental	模式选择：append和lastmodified
–last-value	指定自从上次导入后列的值

注意：
--check-column (col) 
这些被指定的列的类型不能使任意字符类型，如char、varchar等类型都是不可以的，同时-- check-column可以去指定多个列

--incremental (mode)	
append：追加，比如对大于last-value指定的值之后的记录进行追加导入；
lastmodified：最后的修改时间，追加last-value指定的日期之后的记录

--last-value (value)
指定自从上次导入后列的最大值（大于该指定的值），也可以自己设定某一值

使用append:
sqoop import \
--connect jdbc:mysql://192.168.64.180:3306/test \
--username root \
--password ********* \
--query "select * from studentinfo where \$CONDITIONS" \
-m 1 \
--target-dir /test/kb12/sqoop/m2h_incr_append \
--fields-terminated-by '\t' \
--check-column stuId \
--incremental append \
--last-value 48

使用lastmodified:
sqoop import \
--connect jdbc:mysql://192.168.64.180:3306/test \
--username root \
--password *********** \
--query "select * from sqp_incr_time where \$CONDITIONS" \
-m 1 \
--target-dir /test/kb12/sqoop/m2h_incr_lastmodified\
--fields-terminated-by '\t' \
--lines-terminated-by '\n' \
--check-column incrTime \
--incremental lastmodified \
--append
--last-value '2021-6-30 05:20:30'

Mysql --> Hive

1. 全量导入 Mysql上的数据到Hive表中

sqoop import \
--connect jdbc:mysql://192.168.64.180:3306/school \
--username root \
--password ********* \
--table courseinfo \
-m 1 \
--hive-import \
--hive-database school \
--create-hive-table \
--hive-table courseinfo \

选项	含义
–hive-import	import:追加，overwrite:覆盖
–hive-database	指定hive数据库
–hive-table	指定库中的表
–create-hive-table	若指定的表不存在，则自动创建

2. 分区表单分区导入

sqoop import \
--connect jdbc:mysql://192.168.64.188:3306/sales_source \
--username root \
--password-file /sqoop/pwd/sqoopPWD.pwd \
--query "select * from sales_order where \$CONDITIONS" \
--hive-import \
--hive-database sales_rds \
--hive-table sales_order \
--split-by order_number \
--fields-terminated-by '\t' \
--lines-terminated-by '\n' \
--hive-delims-replacement ' ' \
--target-dir /hive/warehouse/sales_rds.db/sales_order \
--incremental append \
--check-column entry_date \
--last-value '1900-1-1'

3. 分区表单分区导入

方式一：

sqoop import \
--connect jdbc:mysql://192.168.64.180:3306/test \
--username root \
--password *********** \
--table studentinfo \
--columns stuName,stuAge \
--where "stuId between 0001 and 0015" \
-m 1 \
--hive-import \
--hive-table test.studentinfo \
--create-hive-table \
--hive-partition-key stuId \
--hive-partition-value 'stuId =0001-0015' \
--fields-terminated-by ',' \
--lines-terminated-by '\n'

#自己使用的时候数据写入到hive报错，搜索原因是sqoop版本与hive版本问题

选项	含义
–hive-partition-key	指定分区字段
–hive-partition-value	指定分区字段的值

方式二：

sqoop import \
--connect jdbc:mysql://192.168.64.180:3306/test \
--username root \
--password ********* \
--table sqp_partition \
--where "cast(dotime as date)='2021-06-01'" \
-m 1 \
--delete-target-dir \
--target-dir /user/hive/warehouse/kb12.db/sqp_partition/dodate=2021-06-01 \
--fields-terminated-by ',' \
--lines-terminated-by '\n'

♥♥♥♥♥注意，需要在hive中输入该命令，表中才会有数据。	
alter table sqp_partition add partition(dodate='2021-06-01');

方式二的脚本用于自动获取前一天的数据，并分区

#!/bin/bash
DATE=`date -d '-1 day' +%F`
sqoop import \
--connect jdbc:mysql://192.168.64.180:3306/test \
--username root \
--password ********** \
--table sqp_partition \
--where "cast(dotime as date)='$DATE'" \
-m 1 \
--delete-target-dir \
--target-dir /user/hive/warehouse/kb12.db/sqp_partition/dodate=$DATE \
--fields-terminated-by ',' \
--lines-terminated-by '\n'
		
hive -e "alter table kb12.sqp_partition add partition(dodate='$DATE')"