数据迁移工具 Sqoop

介绍：

　　Sqoop是一款开源的工具，主要用于在Hadoop(Hive)与传统的数据库（mysql、 postgresql等）间进行数据的传递。可以将关系型数据库（MySQL ,Oracle ,Postgres等）中的数据导入到HDFS中，也可以将HDFS的数据导进到关系型数据库 中。

 

 

导入全部数据：

sqoop import \
--connect jdbc:mysql://linux:3306/sqoop \
--username hive \
--password 12345678 \
--table goodtbl \
--target-dir /root \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t"

备注：

• target-dir：将数据导入 HDFS 的路径；
• delete-target-dir：如果目标文件夹在 HDFS 上已经存在，那么再次运行就会报 错。可以使用--delete-target-dir来先删除目录。也可以使用 append 参数，表 示追加数据；
• num-mappers：启动多少个Map Task；默认启动4个Map Task；也可以写成 - m 1
• fields-terminated-by：HDFS文件中数据的分隔符；

导入查询数据：

sqoop import \
--connect jdbc:mysql://linux123:3306/sqoop \
--username hive \
--password 12345678 \
--target-dir /root\
--append \
-m 1 \
--fields-terminated-by "\t" \
--query 'select gname, serialNumber, price, stock_number,
create_time from goodtbl where price>88 and $CONDITIONS;'

备注：

• 查询语句的where子句中必须包含 '$CONDITIONS'
• 如果query后使用的是双引号，则$CONDITIONS前必须加转义符，防止shell识 别为自己的变量，单引号就不需要转义

导入指定的列：　

sqoop import \
--connect jdbc:mysql://linux123:3306/sqoop \
--username hive \
--password 12345678 \
--target-dir /root\
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t" \
--columns gname,serialNumber,price \
--table goodtbl

备注：columns中如果涉及到多列，用逗号分隔，不能添加空格

导入查询数据（使用关键字）：

sqoop import \
--connect jdbc:mysql://linux123:3306/sqoop \
--username hive \
--password 12345678 \
--target-dir /root\
--delete-target-dir \
-m 1 \
--fields-terminated-by "\t" \
--table goodtbl \
--where "price>=68"

 

启动多个Map Task导入数据：

sqoop import \
--connect jdbc:mysql://linux123:3306/sqoop \
--username hive \
--password 12345678 \
--target-dir /root \
--delete-target-dir \
--fields-terminated-by "\t" \
--table goodtbl \
--split-by gname

备注：

• 没有指定maptask数量，默认启动4个
• 如果 MySQL 中的表有主键，指定 Map Task 的个数就行
• 如果 MySQL 中的表有主键，要使用 split-by 指定分区字段
• 如果分区字段是字符类型，使用 sqoop 命令的时候要添加：- Dorg.apache.sqoop.splitter.allow_text_splitter=true。即：

sqoop import -
Dorg.apache.sqoop.splitter.allow_text_splitter=true \
--connect jdbc:mysql://liunx:3306/sqoop \
... ...

• 查询语句的where子句中的 '$CONDITIONS' ，也是为了做数据分区使用的，即 使只有1个Map Task

MySQL 到 Hive：

sqoop import \
--connect jdbc:mysql://linux123:3306/sqoop \
--username hive \
--password 12345678 \
--table goodtbl \
--hive-import \
--create-hive-table \
--fields-terminated-by "\t" \
--hive-overwrite \
--hive-table mydb.goodtbl \
-m 1

参数说明：

• hive-import。必须参数，指定导入hive
• hive-database。Hive库名(缺省值default)
• hive-table。Hive表名
• fields-terminated-by。Hive字段分隔符
• hive-overwrite。覆盖中已经存在的数据
• create-hive-table。创建好 hive 表，但是表可能存在错误。不建议使用这个参 数，建议提前建好表

导出数据：Hive/HDFS到RDBMS：

# 执行导出
sqoop export \
--connect jdbc:mysql://linux123:3306/sqoop \
--username hive \
--password 12345678 \
--table goodtbl2 \
--num-mappers 1 \
--export-dir /user/hive/warehouse/mydb.db/goodtbl \
--input-fields-terminated-by "\t"

增量数据导入：

　　sqoop 实现了基于时间戳的CDC。

• 时间戳：最好有两个列，一个插入时间戳，表示何时创建，一个更新时间 戳，表示最后一次更新的时间；
• 序列：大多数数据库都提供自增功能，表中的列定义成自增的，很容易地根 据该列识别新插入的数据；

时间戳的CDC是最简单且常用的，但是有如下缺点：

• 不能记录删除记录的操作
• 无法识别多次更新
• 不具有实时能力

sqoop import \
--connect jdbc:mysql://linux123:3306/sqoop \
--username hive --password 12345678 \
--table goodtbl \
--incremental append \
--hive-import \
--fields-terminated-by "\t" \
--hive-table mydb.goodtbl \
--check-column serialNumber \
--last-value 50 \
-m 1

参数说明：

• check-column 用来指定一些列（即可以指定多个列），这些列在增量导入时用 来检查这些数据是否作为增量数据进行导入，和关系型数据库中的自增字段及时 间戳类似。这些被指定的列的类型不能使任意字符类型，如char、varchar等类 型都不可以
• last-value 指定上一次导入中检查列指定字段最大值

执行 Sqoop  job：

　　执行数据增量导入有两种实现方式：

• 1. 每次手工配置last-value，手工调度
• 2. 使用job，给定初始last-value，定时任务每天定时调度

　　很明显方式2更简便。

创建口令文件：

echo -n "12345678" > sqoopPWD.pwd
hdfs dfs -mkdir -p /sqoop/pwd
hdfs dfs -put sqoopPWD.pwd /sqoop/pwd
hdfs dfs -chmod 400 /sqoop/pwd/sqoopPWD.pwd
# 可以在 sqoop 的 job 中增加：
--password-file /sqoop/pwd/sqoopPWD.pwd

创建 sqoop job：

# 创建 sqoop job
sqoop job --create myjob1 -- import \
--connect jdbc:mysql://linux123:3306/sqoop?useSSL=false \
--username hive \
--password-file /sqoop/pwd/sqoopPWD.pwd \
--table goodtbl \
--incremental append \
--hive-import \
--hive-table mydb.goodtbl \
--check-column serialNumber \
--last-value 0 \
-m 1
# 查看已创建的job
sqoop job --list

# 查看job详细运行是参数
sqoop job --show myjob1

# 执行job
sqoop job --exec myjob1

# 删除job
sqoop job --delete myjob1

实现原理：

　　因为job执行完成后，会把当前check-column的最大值记录到meta中，下次再调起 时把此值赋给last-value。 缺省情况下元数据保存在 ~/.sqoop/

其中，metastore.db.script 文件记录了对last-value的更新操作：

cat metastore.db.script |grep incremental.last.value