＜Zhuuu_ZZ＞Sqoop数据迁移

一 从RDB导入数据到HDFS

1 导入表到HDFS

sqoop-import --connect jdbc:mysql://hadoopwei:3306/hive \
--driver com.mysql.jdbc.Driver \
--table PARTITIONS \
--username root \
--password ok \
--target-dir /data/partitions \
--m 3 

2 通过Where语句过滤导入表

## 第一行的末尾hive代表hive数据库
## \ 表示换行
## --delete-target-dir 表示如果目标路径存在则删除
## --m表示Mapper数量，不写默认为4片
sqoop-import --connect jdbc:mysql://hadoopwei:3306/hive \
--table PARTITIONS \
--where "PART_ID<50" \
--username root \
--password ok \
--delete-target-dir \
--target-dir /data/partitions \
--m 3

3 通过COLUMNS过滤导入表

sqoop import --connect jdbc:mysql://hadoopwei:3306/hive \
--table PARTITIONS \
--columns "PART_ID,PART_NAME" \
--where "PART_ID>50" \
--username root \
--password ok \
--delete-target-dir \
--target-dir /data/partitions \
--m 3

4 使用query方式导入数据

• 必须指定--target-dir hdfs路径；
• 所有查询都应该以and \$CONDITIONS结束;
• sqoop内部使用该条件将记录范围分发给所有Mapper
• 就不需要--table 需要导入数据的表名
• 当指定-m大于1时，hdfs实际生成文件个数为m+1
• 使用query方式当-m大于1时，需要指定--split-by。

sqoop-import --connect jdbc:mysql://hadoopwei:3306/hive \
--query "select * from PARTITIONS where PART_ID between 30 and 80 and \$CONDITIONS" \
--username root \
--password ok \
--split-by CREATE_TIME \
--delete-target-dir \
--target-dir /data/partitions \
--m 3

5 使用Sqoop增量导入数据

• Incremental指定增量导入的模式
  • append：追加数据记录
  • lastmodified：可追加更新的数据
• –password ok可替换为-P。隐藏输入密码增加保密性

sqoop import --connect jdbc:mysql://localhost:3306/test \
--table student --username root --password ok \
--incremental append --check-column sid  --last-value 9 \
--target-dir /data/stu \
--m 3

6 指定文件格式导入到hdfs

• as-textfile 导入数据为text文件（默认）
• as-avrodatafile 导入数据为avro文件
• as-sequencefile 导入数据为sequence文件
• as-parquetfile 导入数据为parquet文件

sqoop import --connect jdbc:mysql://localhost:3306/test \
--table student --username root --password hadoop \
--delete-target-dir --target-dir /data/orders \
--m 3 \
--as-sequencefile

7 自动运行程序Sqoop-job

• sqoop job作用
  • Sqoop可以定义保存job，从而简化此过程
  • 保存的job记录执行Sqoop命令所需的配置信息
    
• 通过create创建job，此时不运行导入程序。注意import前面需要空格

sqoop job \
--create orderHDFS \
-- import \
--connect jdbc:mysql://hadooptest1:3306/retail_db \
--driver com.mysql.jdbc.Driver \
--table orders \
--username root \
--password ok \
--target-dir /data/retail_db/orders \
--delete-target-dir
-m 3

• 通过exec执行job

sqoop job --exec orderHDFS

• 写个定时器脚本每天夜里12点半自动运行sqoop job --exec orderHDFS ，就可以实现数据的自动增量导入。

二 从RDB导入数据到Hive

• 首先从hive的lib目录下复制所需Jar包到sqoop的lib目录下
  • scp /opt/hive/lib/hive-common-1.1.0-cdh5.14.2.jar /opt/sqoop/lib
  • scp /opt/hive/lib/hive-exec-1.1.0-cdh5.14.2.jar /opt/sqoop/lib

1 直接导入数据到Hive

• 若有ERROR：Output directory hdfs://hadooptest1:9000/user/root/orders already exists是因为sqoop导入数据到hive会先在临时目录下储存数据，然后load data加载数据到表。
• 解决：直接删除已存在的目录即可。

opt/sqoop/bin/sqoop-import --connect jdbc:mysql://hadoopwei:3306/test \
--table student  --username root -P \
--hive-import [--create-hive-table] \
--hive-database sqooptest --hive-table student \
[--hive-overwrite] \
[--m 1]

注意：前两个中括号至少二选一，要么表不存在创表并导入数据，要么表存在覆盖数据

• 或者直接简便一点：

opt/sqoop/bin/sqoop-import --connect jdbc:mysql://hadoopwei:3306/test \
--table student  --username root -P \
--hive-import \
--hive-table sqooptest.student \
[--hive-overwrite] \
[--m 1]

2 导入数据到动态分区

• 不支持直接导入动态分区，即不支持把列名作为分区key，不指定分区value由表原有的数据导入作为分区值这个方式是不支持的。
• 可以采用先在hive里建立一个临时表，把数据导入到这个临时表，然后在hive里创建动态分区表，然后 insert into 表 partition(分区名) select *,分区字段 from 临时表

#在hdfs执行以下命令，即在hive里创建临时表
sqoop import --connect jdbc:mysql://hadoopwei:3306/retail \
--table orders --username root --password ok \
--hive-import --create-hive-table \
--hive-database shucang --hive-table ext_orders \
--m 1

--hive里开启动态分区：
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
--若分区数大于100需修改最大分区数：
set hive.exec.max.dynamic.partitions.pernode=600000;
set hive.exec.max.dynamic.partitions=6000000;
set hive.exec.max.created.files=6000000;

--在hive里建分区表
create table orders_p(
order_id int,
order_date string,
customer_id int,
order_status string)
partitioned by (date string);

--hive里执行：
insert into orders_p partition(date) select *,date(order_date) from ext_orders	

3 导入数据到静态分区

• 静态分区：在hdfs里执行
• 需要指定--target-dir作为临时数据存储目录，然后在hdfs上load data移动数据到hive表里。然后临时数据目录会被删除。

/opt/sqoop/bin/sqoop-import \
--connect jdbc:mysql://localhost:3306/test --table orders \
--where "order_date between '2013-07-24' and '2013-07-25'" \ --username root -P \
--hive-import  --hive-database sqooptest \
--hive-table orders_p \
--target-dir /data/retail_db/order_partition \
--hive-partition-key "date" \
--hive-partition-value "20200724" \
--hive-overwrite \
--m 1

追加到动态分区表

sqoop import \
--connect jdbc:mysql://localhost:3306/retail --table orders \
--where "order_id=68690" \
--username root -P \
--hive-import  --hive-database shucang \
--hive-table order \
--hive-partition-key "date" \
--hive-partition-value "20200724" \
--incremental append \
--check-column order_id \
--m 1

覆盖数据到分区表

/opt/sqoop/bin/sqoop-import \
--connect jdbc:mysql://localhost:3306/test --table orders \
--where "order_date between '2013-07-24' and '2013-07-25'" \
--username root -P \
--hive-import \
--hive-database sqooptest --hive-table orders_p \
--hive-partition-key "date" \
--hive-partition-value "20200726" \
--hive-overwrite \
--m 1

三 导入MySQL数据到HBase

## --hbase-row-key 字段名，这个字段名需要出现在--columns"字段名,....."里面
/opt/sqoop/bin/sqoop import \
--connect jdbc:mysql://localhost:3306/test --table products \
--username root --password ok \  
--columns "product_id,product_name,product_price" \ 
--hbase-create-table \
--hbase-table product \
--column-family prod \
--hbase-row-key product_id \
--m 1

四 使用Sqoop export导出hdfs数据到MySQL表里

• 该表需要事先存在。
• 需要指定分隔符为’\001’

sqoop export \
--connect jdbc:mysql://localhost:3306/sqoop \
--username root --password ok \
--table stu \
--export-dir /data/stu/ \
--input-fields-terminated-by '\001' \
--m 1

五 脚本打包sqoop命令

（1）创建一个.opt文件

[root@hadooptest1 ~]# touch /opt/job_RDBMS2HDFS.opt

（2）编写sqoop脚本vi /opt/job_RDBMS2HDFS.opt

import
--connect
jdbc:mysql://hadooptest1:3306/retail_db
--driver
com.mysql.jdbc.Driver
--table
customers
--username
root
--password
123456
--target-dir
/data/retail_db/customers2
--delete-target-dir
-m3

（3）执行该脚本

[root@hadooptest1 ~]# sqoop --options-file /opt/job_RDBMS2HDFS.opt