Apache Sqoop 数据导入

Apache Sqoop 数据导入

数据

链接：https://pan.baidu.com/s/17DgifY7j-iLsGQW91N_zJw
提取码：psrd

步骤

“导入工具”导入单个表从RDBMS到HDFS。
表中的每一行被视为HDFS的记录。所有记录都存储为文本文件的文本数据

下面的语法用于将数据导入HDFS。
$ sqoop import (generic-args) (import-args)

Sqoop测试表数据
在mysql中创建数据库userdb，然后执行参考资料中的sql脚本：
创建三张表: emp雇员表、 emp_add雇员地址表、emp_conn雇员联系表。

mysql> 
mysql> show tables;
+------------------+
| Tables_in_userdb |
+------------------+
| emp              |
| emp_add          |
| emp_conn         |
+------------------+
3 rows in set (0.00 sec)

mysql> 

全量导入mysql表数据到HDFS

下面的命令用于从MySQL数据库服务器中的emp表导入HDFS。

bin/sqoop import \
--connect jdbc:mysql://hadoop01/userdb \
--username root \
--password 123456 \
--delete-target-dir \
--target-dir /sqoopresult \
--table emp --m 1

其中–target-dir可以用来指定导出数据存放至HDFS的目录；
mysql jdbc url 请使用 ip 地址。

为了验证在HDFS导入的数据，请使用以下命令查看导入的数据：

hdfs dfs -cat /sqoopresult/part-m-00000

可以看出它会在HDFS上默认用逗号,分隔emp表的数据和字段。可以通过
–fields-terminated-by '\t’来指定分隔符。

运行结果：

[root@hadoop01 /]# hdfs dfs -ls /sqoopresult 
Found 2 items
-rw-r--r--   2 root supergroup          0 2019-11-29 10:11 /sqoopresult/_SUCCESS
-rw-r--r--   2 root supergroup        151 2019-11-29 10:11 /sqoopresult/part-m-00000
[root@hadoop01 /]# hdfs dfs -cat /sqoopresult/part-m-00000
1201,gopal,manager,50000,TP
1202,manisha,Proof reader,50000,TP
1203,khalil,php dev,30000,AC
1204,prasanth,php dev,30000,AC
1205,kranthi,admin,20000,TP
[root@hadoop01 /]# 

全量导入mysql表数据到HIVE

方式一：先复制表结构到hive中再导入数据

将关系型数据的表结构复制到hive中

bin/sqoop create-hive-table \
--connect jdbc:mysql://hadoop01/userdb \
--table emp_add \
--username root \
--password 123456 \
--hive-table test.emp_add_sp

如果你报了这个错(+_+)?：
Caused by: java.lang.ClassNotFoundException: org.apache.hadoop.hive.conf.HiveConf
就把hive的lib目录下的hive-common-版本号.jar拷贝到sqoop的lib目录下：

cp /export/servers/hive-1.1.0-cdh5.14.0/lib/hive-common-1.1.0-cdh5.14.0.jar /export/servers/sqoop-1.4.7.bin__hadoop-2.6.0/lib/

再执行一遍！
如果你又报了这个错╰（‵□′）╯：
Caused by: java.lang.ClassNotFoundException: org.apache.hadoop.hive.shims.ShimLoader

cp /export/servers/hive-1.1.0-cdh5.14.0/lib/hive-shims-*.jar /export/servers/sqoop-1.4.7.bin__hadoop-2.6.0/lib/

再执行一遍！！
然后你又报了这个错(ノ｀Д)ノ！
Database does not exist: test
原来是没创建整个数据库！！创建它！

hive (default)> create database test;
OK
Time taken: 0.134 seconds

再执行一遍！！！
终于出来了，下面是结果

19/11/29 16:39:44 INFO hive.HiveImport: Loading uploaded data into Hive
19/11/29 16:39:45 INFO hive.HiveImport: which: no hbase in (:/export/servers/sqoop-1.4.7.bin__hadoop-2.6.0/bin:/usr/lib64/qt-3.3/bin::/export/servers/hive-1.1.0-cdh5.14.0//bin::/export/servers/hadoop-2.6.0-cdh5.14.0/bin:/export/servers/hadoop-2.6.0-cdh5.14.0/sbin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/export/servers/jdk1.8.0_144/bin:/root/bin)
19/11/29 16:39:47 INFO hive.HiveImport: 
19/11/29 16:39:47 INFO hive.HiveImport: Logging initialized using configuration in jar:file:/export/servers/sqoop-1.4.7.bin__hadoop-2.6.0/lib/hive-common-1.1.0-cdh5.14.0.jar!/hive-log4j.properties
19/11/29 16:39:51 INFO hive.HiveImport: OK
19/11/29 16:39:51 INFO hive.HiveImport: Time taken: 3.403 seconds
19/11/29 16:39:51 INFO hive.HiveImport: Hive import complete.

然后去hive里检查一下这个表是否创建成功。

hive (default)> use test;
OK
Time taken: 0.051 seconds

hive (test)> show tables;
OK
tab_name
emp_add_sp
Time taken: 0.017 seconds, Fetched: 1 row(s)


hive (test)> desc emp_add_sp;
OK
col_name        data_type       comment
id                      int                                         
hno                     string                                      
street                  string                                      
city                    string                                      
Time taken: 0.049 seconds, Fetched: 4 row(s)

可以看到，mysql中的表结构成功的复制到hive中了。

其中：
–table emp_add为mysql中的数据库sqoopdb中的表。
–hive-table emp_add_sp 为hive中新建的表名称。

从关系数据库导入文件到hive中

bin/sqoop import \
--connect jdbc:mysql://hadoop01/userdb \
--username root \
--password 123456 \
--table emp_add \
--hive-table test.emp_add_sp \
--hive-import \
--m 1

运行结果：

hive (test)> select * from emp_add_sp;
OK
emp_add_sp.id   emp_add_sp.hno  emp_add_sp.street       emp_add_sp.city
1201    288A    vgiri   jublee
1202    108I    aoc     sec-bad
1203    144Z    pgutta  hyd
1204    78B     old city        sec-bad
1205    720X    hitec   sec-bad
Time taken: 0.263 seconds, Fetched: 5 row(s)
hive (test)> 

方式二：直接复制表结构数据到hive中

bin/sqoop import \
--connect jdbc:mysql://hadoop01/userdb \
--username root \
--password 123456 \
--table emp_conn \
--hive-import \
--m 1 \
--hive-database test;

运行结果：

hive (default)> use test;
OK
Time taken: 2.157 seconds
hive (test)> show tables;
OK
tab_name
emp_add_sp
emp_conn
Time taken: 0.133 seconds, Fetched: 2 row(s)
hive (test)> select * from emp_conn;
OK
emp_conn.id     emp_conn.phno   emp_conn.email
1201    2356742 gopal@tp.com
1202    1661663 manisha@tp.com
1203    8887776 khalil@ac.com
1204    9988774 prasanth@ac.com
1205    1231231 kranthi@tp.com
Time taken: 0.256 seconds, Fetched: 5 row(s)
hive (test)> 

导入表数据子集(where过滤)

where可以指定从关系数据库导入数据时的查询条件。它执行在数据库服务器相应的SQL查询，并将结果存储在HDFS的目标目录。

bin/sqoop import \
--connect jdbc:mysql://hadoop01/userdb \
--username root \
--password 123456 \
--where "city ='sec-bad'" \
--target-dir /wherequery \
--table emp_add --m 1

用Sqoop将查询结果写到了HDFS文件中的结果：

[root@hadoop01 sbin]# hadoop fs -ls /wherequery
Found 2 items
-rw-r--r--   2 root supergroup          0 2019-11-29 17:06 /wherequery/_SUCCESS
-rw-r--r--   2 root supergroup         72 2019-11-29 17:06 /wherequery/part-m-00000
[root@hadoop01 sbin]# hadoop fs -cat /wherequery/part-m-00000
1202,108I,aoc,sec-bad
1204,78B,old city,sec-bad
1205,720X,hitec,sec-bad
[root@hadoop01 sbin]# 

导入表数据子集(query查询)

使用query sql语句来进行查找的注意事项：

• 不能加参数–table ;
• 必须要添加where条件;
• where条件后面必须带一个$CONDITIONS 这个字符串;
• 这个sql语句必须用单引号，不能用双引号;

bin/sqoop import \
--connect jdbc:mysql://hadoop01:3306/userdb \
--username root \
--password 123456 \
--target-dir /wherequery12 \
--query 'select id,name,deg from emp WHERE  id>1203 and $CONDITIONS' \
--split-by id \
--fields-terminated-by '\t' \
--m 2

运行结果

[root@hadoop01 sbin]# hadoop fs -ls /wherequery12
Found 3 items
-rw-r--r--   2 root supergroup          0 2019-12-02 08:26 /wherequery12/_SUCCESS
-rw-r--r--   2 root supergroup         22 2019-12-02 08:26 /wherequery12/part-m-00000
-rw-r--r--   2 root supergroup         19 2019-12-02 08:26 /wherequery12/part-m-00001
[root@hadoop01 sbin]# hadoop fs -cat /wherequery12/part-m-00000
1204    prasanth        php dev
[root@hadoop01 sbin]# hadoop fs -cat /wherequery12/part-m-00001
1205    kranthi admin

sqoop命令中，–split-by id通常配合-m 10参数使用。用于指定根据哪个字段进行划分并启动多少个maptask。

增量导入

在实际工作当中，数据的导入，很多时候都是只需要导入增量数据即可，并不需要将表中的数据每次都全部导入到hive或者hdfs当中去，这样会造成数据重复的问题。

因此一般都是选用一些字段进行增量的导入， sqoop支持增量的导入数据。
增量导入是仅导入新添加的表中的行的技术。

–check-column (col)
用来指定一些列，这些列在增量导入时用来检查这些数据是否作为增量数据进行导入，和关系型数据库中的自增字段及时间戳类似。

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

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

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

Append模式增量导入

执行以下指令先将我们之前的数据导入：

bin/sqoop import \
--connect jdbc:mysql://hadoop01:3306/userdb \
--username root \
--password 123456 \
--target-dir /appendresult \
--table emp --m 1

使用hadoop fs -cat查看生成的数据文件，发现数据已经导入到hdfs中。

[root@hadoop01 sbin]# hadoop fs -ls /appendresult
Found 2 items
-rw-r--r--   2 root supergroup          0 2019-12-02 08:48 /appendresult/_SUCCESS
-rw-r--r--   2 root supergroup        151 2019-12-02 08:48 /appendresult/part-m-00000
[root@hadoop01 sbin]# hadoop fs -cat /appendresult/part-m-00000
1201,gopal,manager,50000,TP
1202,manisha,Proof reader,50000,TP
1203,khalil,php dev,30000,AC
1204,prasanth,php dev,30000,AC
1205,kranthi,admin,20000,TP
[root@hadoop01 sbin]# 

然后在mysql的emp中插入2条增量数据:

insert into `userdb`.`emp` (`id`, `name`, `deg`, `salary`, `dept`) values ('1206', 'allen', 'admin', '30000', 'tp');
insert into `userdb`.`emp` (`id`, `name`, `deg`, `salary`, `dept`) values ('1207', 'woon', 'admin', '40000', 'tp');

运行效果如下，可以看到新增了两条数据：

mysql> select * from emp;
+------+----------+--------------+--------+------+
| id   | name     | deg          | salary | dept |
+------+----------+--------------+--------+------+
| 1201 | gopal    | manager      |  50000 | TP   |
| 1202 | manisha  | Proof reader |  50000 | TP   |
| 1203 | khalil   | php dev      |  30000 | AC   |
| 1204 | prasanth | php dev      |  30000 | AC   |
| 1205 | kranthi  | admin        |  20000 | TP   |
| 1206 | allen    | admin        |  30000 | tp   |
| 1207 | woon     | admin        |  40000 | tp   |
+------+----------+--------------+--------+------+
7 rows in set (0.00 sec)

执行如下的指令，实现增量（增量就是新增的那两条数据）的导入:

bin/sqoop import \
--connect jdbc:mysql://hadoop01:3306/userdb \
--username root  --password 123456 \
--table emp --m 1 \
--target-dir /appendresult \
--incremental append \
--check-column id \
--last-value  1205

注意看运行结果，接收到两条数据，并且追加到目录中了。

最后验证导入数据目录 可以发现多了一个文件 里面就是增量数据

[root@hadoop01 sbin]# hadoop fs -ls /appendresult
Found 3 items
-rw-r--r--   2 root supergroup          0 2019-12-02 08:48 /appendresult/_SUCCESS
-rw-r--r--   2 root supergroup        151 2019-12-02 08:48 /appendresult/part-m-00000
-rw-r--r--   2 root supergroup         51 2019-12-02 08:59 /appendresult/part-m-00001
[root@hadoop01 sbin]# hadoop fs -cat /appendresult/part-m-00001
1206,allen,admin,30000,tp
1207,woon,admin,40000,tp

Lastmodified模式增量导入

首先在MySQL创建一个customer表，指定一个时间戳字段：

use userdb;
create table customertest(id int,name varchar(20),last_mod timestamp default current_timestamp on update current_timestamp);

运行结果

mysql> show tables;
+------------------+
| Tables_in_userdb |
+------------------+
| customertest     |
| emp              |
| emp_add          |
| emp_conn         |
+------------------+
4 rows in set (0.00 sec)

此处的时间戳设置为在数据的产生和更新时都会发生改变.
分别插入如下记录:

insert into customertest(id,name) values(1,'neil');
insert into customertest(id,name) values(2,'jack');
insert into customertest(id,name) values(3,'martin');
insert into customertest(id,name) values(4,'tony');
insert into customertest(id,name) values(5,'eric');

运行结果（确保你的last_mod每一次都不一样）：

mysql> select * from customertest;                          
+------+--------+---------------------+
| id   | name   | last_mod            |
+------+--------+---------------------+
|    1 | neil   | 2019-12-02 09:08:39 |
|    2 | jack   | 2019-12-02 09:08:44 |
|    3 | martin | 2019-12-02 09:08:49 |
|    4 | tony   | 2019-12-02 09:08:54 |
|    5 | eric   | 2019-12-02 09:09:00 |
+------+--------+---------------------+
5 rows in set (0.00 sec)

执行sqoop指令将数据全部导入hdfs:

bin/sqoop import \
--connect jdbc:mysql://hadoop01/userdb \
--username root \
--password 123456 \
--target-dir /lastmodifiedresult \
--table customertest --m 1

查看此时导出的结果数据：

[root@hadoop01 sbin]# hadoop fs -ls /lastmodifiedresult
Found 2 items
-rw-r--r--   2 root supergroup          0 2019-12-02 09:10 /lastmodifiedresult/_SUCCESS
-rw-r--r--   2 root supergroup        147 2019-12-02 09:10 /lastmodifiedresult/part-m-00000
[root@hadoop01 sbin]# hadoop fs -cat /lastmodifiedresult/part-m-00000
1,neil,2019-12-02 09:08:39.0
2,jack,2019-12-02 09:08:44.0
3,martin,2019-12-02 09:08:49.0
4,tony,2019-12-02 09:08:54.0
5,eric,2019-12-02 09:09:00.0

再次插入一条数据进入customertest表

insert into customertest(id,name) values(6,'james')

使用incremental的方式进行增量的导入:

bin/sqoop import \
--connect jdbc:mysql://hadoop01:3306/userdb \
--username root \
--password 123456 \
--table customertest \
--target-dir /lastmodifiedresult \
--check-column last_mod \
--incremental lastmodified \
--last-value "2019-12-02 09:08:54" \
--m 1 \
--append

[root@hadoop01 sbin]# hadoop fs -ls /lastmodifiedresult
Found 3 items
-rw-r--r--   2 root supergroup          0 2019-12-02 09:10 /lastmodifiedresult/_SUCCESS
-rw-r--r--   2 root supergroup        147 2019-12-02 09:10 /lastmodifiedresult/part-m-00000
-rw-r--r--   2 root supergroup         88 2019-12-02 09:16 /lastmodifiedresult/part-m-00001
[root@hadoop01 sbin]# hadoop fs -cat /lastmodifiedresult/part-m-00001
4,tony,2019-12-02 09:08:54.0
5,eric,2019-12-02 09:09:00.0
6,james,2019-12-02 09:12:57.0
[root@hadoop01 sbin]# 

此处已经会导入我们最后插入的一条记录,但是我们却发现此处插入了2条数据，这是为什么呢？
这是因为采用lastmodified模式去处理增量时，会将大于等于last-value值的数据当做增量插入。

Lastmodified模式:append、merge-key

使用lastmodified模式进行增量处理要指定增量数据是以append模式(附加)还是merge-key(合并)模式添加。
下面演示使用merge-by的模式进行增量更新,我们去更新 id为1的name字段。
(原来小写的neil改为现在大写的Neil以看出区别)

update customertest set name = 'Neil' where id = 1;

更新之后，这条数据的时间戳会更新为更新数据时的系统时间.
执行如下指令，把id字段作为merge-key:

bin/sqoop import \
--connect jdbc:mysql://hadoop01:3306/userdb \
--username root \
--password 123456 \
--table customertest \
--target-dir /lastmodifiedresult \
--check-column last_mod \
--incremental lastmodified \
--last-value "2019-12-02 09:08:54" \
--m 1 \
--merge-key id

由于merge-key模式是进行了一次完整的mapreduce操作，
因此最终我们在lastmodifiedresult文件夹下可以看到生成的为part-r-00000这样的文件，会发现id=1的name已经得到修改，同时新增了id=6的数据。

[root@hadoop01 sbin]# hadoop fs -ls /lastmodifiedresult
Found 2 items
-rw-r--r--   2 root supergroup          0 2019-12-02 09:26 /lastmodifiedresult/_SUCCESS
-rw-r--r--   2 root supergroup        177 2019-12-02 09:26 /lastmodifiedresult/part-r-00000
[root@hadoop01 sbin]# hadoop fs -cat  /lastmodifiedresult/part-r-00000
1,Neil,2019-12-02 09:30:10.0
2,jack,2019-12-02 09:08:44.0
3,martin,2019-12-02 09:08:49.0
4,tony,2019-12-02 09:08:54.0
5,eric,2019-12-02 09:09:00.0
6,james,2019-12-02 09:12:57.0
[root@hadoop01 sbin]#