Sqoop(sqoop是什么？关系型数据库导入数据到HDFS,Hive，hbase；HDFS导出数据到RDB，脚本导入)_sqoop是啥

一.Sqoop

1.Sqoop是什么

将导入或导出命令翻译成 MapReduce 程序来实现。
Sqoop是一个用于在Hadoop和关系数据库之间传输数据的工具

• 将数据从RDBMS导入到HDFS
  HDFS、Hive、HBase
• 从HDFS导出数据到RDBMS
• 使用MapReduce导入和导出数据，提供并行操作和容错

RDB指关系数据库(Relational Database,RDB)

2.RDB导入数据到HDFS

2.1导入表到HDFS

因为没有配置环境变量，先进入sqoop文件夹cd /opt/sqoop/
将mysql数据库中的hive数据库中的BUCKETING_COLS表导到HDFS的路径/data/bucketing_cols中
（需要转义换行符）

bin/sqoop-import \
--connect jdbc:mysql://hadoop1:3306/hive \
--driver com.mysql.jdbc.Driver \
--table BUCKETING_COLS \
--username root \
--password ok \
--target-dir /data/bucketing_cols \
--m 3
1
2
3
4
5
6
7
8

说明：
（1）sqoop-import是sqoop import的别名
（2）connect jdbc:mysql://hadoop1:3306/hive \配置数据库连接为MySQL中数据库
（3）target-dir /data/bucketing_cols \导入指定目录
（4）–m 表示Mapper数量

可能出现的报错

报错原因：
缺少 java-json.jar
解决方法：

获得java-json.jar的文件，并将该 jar 包放到$SQOOP_HOME/lib 目录下

[root@hadoop1 sqoop]#wget http://www.java2s.com/Code/JarDownload/java-json/java-json.jar.zip
[root@hadoop1 sqoop]# yum -y install unzip
[root@hadoop1 sqoop]# unzip java-json.jar.zip
[root@hadoop1 sqoop]# mv java-json.jar lib/
1
2
3
4

（2）也有可能出现连接不上的报错

可以检查一下之前自己的mysql是否有过赋权
a.登录mysql数据库：[root@hadoop1 ~]# mysql -uroot -pok

b.进入mysql数据库中查看host和pasword

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select host,password from user
    -> ;
+-----------------------+-------------------------------------------+
| host                  | password                                  |
+-----------------------+-------------------------------------------+
| localhost             | *31330A9B24799CC9566A39CBD78CEF60E26C906F |
| localhost.localdomain | *31330A9B24799CC9566A39CBD78CEF60E26C906F |
| 127.0.0.1             | *31330A9B24799CC9566A39CBD78CEF60E26C906F |
| ::1                   | *31330A9B24799CC9566A39CBD78CEF60E26C906F |
| %                     | *31330A9B24799CC9566A39CBD78CEF60E26C906F |
+-----------------------+-------------------------------------------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

c.如果没有赋权的话password前面没有*

那就通过以下方法进行赋权

grant all privileges on *.* to 'root'@'%'  identified by 'ok' with grant option;
flush privileges;   --刷新权限
1
2

2.2通过Where语句过滤导入表

bin/sqoop-import \
--connect jdbc:mysql://hadoop1:3306/hive \
--driver com.mysql.jdbc.Driver \
--table TBLS \
--where "owner='root'" \       #指定where条件
--username root \
--password ok \
--delete-target-dir \          #如果目标目录存在，则删除
--target-dir /data/tbls \
--m 3
1
2
3
4
5
6
7
8
9
10

2.3通过COLUMNS过滤导入表

bin/sqoop-import \
--connect jdbc:mysql://hadoop1:3306/hive \
--driver com.mysql.jdbc.Driver \
--table TBLS \
--columns "TBL_ID,OWNER,TBL_NAME,TBL_TYPE" \   #导入指定列
--where "owner='root'" \
--username root \
--password ok \
--delete-target-dir \
--target-dir /data/tbls \
--m 3
1
2
3
4
5
6
7
8
9
10
11

2.4使用query方式导入数据

bin/sqoop-import \
--connect jdbc:mysql://hadoop1:3306/hive \
--driver com.mysql.jdbc.Driver \
--query "select * from TBLS where owner='root' and \$CONDITIONS" \       #所有查询都应该\$CONDITIONS结束;sqoop内部使用该条件将记录范围分发给所有Mapper
--username root \
--password ok \
--split-by tbl_id \         #指定用于分割数据的列为tbl_id
--delete-target-dir \
--target-dir /data/tbls/ \
--m 3
1
2
3
4
5
6
7
8
9
10

2.5使用Sqoop增量导入数据

使用Sqoop增量导入数据
Incremental指定增量导入的模式
append：追加数据记录
lastmodified：可追加更新的数据

bin/sqoop-import \
--connect jdbc:mysql://hadoop1:3306/test \
--table student \
--username root \
--password ok \
--incremental append \
--check-column sid \
--last-value 5 \     #将表的sid列的值为5的后面的数据进行增量导入进入
--delete-target-dir \
--target-dir /data/stu \
--m 3
1
2
3
4
5
6
7
8
9
10
11

说明：

• incremental append 增量导入方式为append追加数据至已经存在的HDFS数据集
• check-column sid指定递增的列
• last-value 5指定上一次导入的最大值

2.6导入时指定文件格式参数

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

bin/sqoop-import \
--connect jdbc:mysql://hadoop1:3306/test \
--table student \
--username root \
--password ok \
--incremental append \
--check-column sid \
--last-value 5 \
--target-dir /data/stu \
--m 3 \
--as-sequencefile
1
2
3
4
5
6
7
8
9
10
11

3.sqoop job

3.1sqoop作用

Sqoop可以定义保存job，从而简化此过程
保存的job记录执行Sqoop命令所需的配置信息

3.2相关命令

3.3示例

#创建sqoop job
sqoop job \
--create order2HDFS -- import \  #import前面需要一个空格
--connect jdbc:mysql://hadoop00:3306/retail_db \
--driver com.mysql.jdbc.Driver \
--table orders \
--username root \
--password ok \
--target-dir /data/retail_db/orders_job \
--delete-target-dir \
-m 5
#执行，需要输入数据库的密码
sqoop job --exec order2HDFS
1
2
3
4
5
6
7
8
9
10
11
12
13

注意： 在创建job时，命令"-- import"中间需要一个空格，否则会报错

4.从RDB导入数据到Hive

从关系型数据库导到hive中，中间会先导到HDFS的临时路径下，再导入hive中。
所以如果第一次因为jar包没导入成功的话，第二次可能会出现表已经存在的情况，可以删除HDFS中的临时表再进行导入，目录为"user/root/…"

4.1直接导入数据到Hive

• –create-hive-table：自动创建表，生产中一般不使用
• –hive-overwrite：覆盖原有表数据

bin/sqoop-import \
--connect jdbc:mysql://hadoop1:3306/test \
--table student \
--username root \
--password ok \
--hive-import \ #通过hive-import指定导入到Hive
--create-hive-table \ #指定创建一个新的hive表，如果表已经存在则报错
--hive-database sqooptest \
--hive-table student \
--hive-overwrite \
--m 3 
1
2
3
4
5
6
7
8
9
10
11

1.jar包导入：从hive中的lib中复制到sqoop的lib目录下

 cp /opt/hive/lib/hive-common-1.1.0-cdh5.14.2.jar /opt/sqoop/lib/
 cp /opt/hive/lib/hive-exec-1.1.0-cdh5.14.2.jar /opt/sqoop/lib/
1
2

2.命令：

bin/sqoop-import \
--connect jdbc:mysql://hadoop1:3306/test \
--table student \
--username root \
--password ok \
--hive-import \
[--create-hive-table \]
--hive-database sqooptest \
--hive-table student \
[--hive-overwrite]
--m 3 
1
2
3
4
5
6
7
8
9
10
11

4.2导入数据到Hive分区

sqoop-import \
--connect jdbc:mysql://hadoop00:3306/retail_db \
--driver com.mysql.jdbc.Driver \
--query "select order_id,order_status from orders where order_date >='2014-07-24' and order_date <'2014-07-25' and \$CONDITIONS" \
--username root \
--password ok \
--split-by 'order_id' \
--target-dir /data/retail_db/order_partition \ #这个是hdfs上的临时路径，最终导入的是hive的默认路径下
--hive-import \
--hive-database retails \
--hive-table order_partition \
--hive-partition-key "order_date" \ #指定分区字段
--hive-partition-value "2014-07-24" \ #指定分区值
--m 1
1
2
3
4
5
6
7
8
9
10
11
12
13
14

5.从RDB导入数据到HBase

5.1导入MySQL数据到HBase

bin/sqoop-import \
--connect jdbc:mysql://hadoop1:3306/test \
--table products \
--username root \
--password ok \
--columns "product_name,product_price" \
--hbase-create-table \
--hbase-table product \
--column-family prod \
--hbase-row-key product_id \
--m 1
1
2
3
4
5
6
7
8
9
10
11

6.导出HDFS数据到MySQL

使用sqoop export导出数据到MySQL
步骤：

• 在MySQL中创建表
• 导出数据到MySQL

--在mysql中创建表
CREATE TABLE customerdemo LIKE customers
1
2

#hdfs导出到mysql
sqoop export \
--connect jdbc:mysql://hadoop00:3306/retail_db \
--username root \
--password ok \
--table customerdemo \
--export-dir /data/retail_db/customers \
-m 1
1
2
3
4
5
6
7
8

7.脚本打包

脚本打包比用命令行操作要方便，而且更容易与 Shell 脚本结合使用。
opt 格式的文件打包 sqoop 命令，然后执行

(1).创建一个.opt 文件

[hadoop@hadoop00 ~]$ mkdir opt
[hadoop@hadoop00 ~]$ touch opt/job_HDFS2RDBMS.opt
1
2

(2).编写 sqoop 脚本

[hadoop@hadoop00 ~]$ vi opt/job_HDFS2RDBMS.opt
添加如下内容
import
--connect
jdbc:mysql://hadoop00:3306/retail_db
--driver
com.mysql.jdbc.Driver
--table
customers
--username
root
--password
ok
--target-dir
/data/retail_db/customers2
--delete-target-dir
-m3
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

(3).执行该脚本

[hadoop@hadoop00 ~]$ sqoop --options-file opt/job_HDFS2RDBMS.opt
1