sqoop增量导入到_sqoop的使用之import导入到HDFS

淩亂°似流年 2022-10-28 10:23 210阅读 0赞

首先我们已经安装好sqoop了，如果没有安装好参考文档《基于CentOS6.5-Hadoop2.7.3-hive-2.1.1安装sqoop1.4.7》

准备一些基本的认识

1、sqoop分为了sqoop1和sqoop2

2、sqoop2拆分server和client，类似于hiveserver2和beeline

3、sqoop早期是一些封装MR程序，以jar文件的形式，最后才演变成了框架

4、用于在hadoop和结构化数据库之间的高效传输批量数据的工具

下面我们开始做一些简单是使用，熟悉sqoop的使用方式

查看帮助信息：bin/sqoop help

![5ad9d9c9a342a071af6f27181b04783c.png][]

连接mysql

bin/sqoop list-databases --connect jdbc:mysql://mastercdh:3306/ --username root --password password

可以将数据库罗列出来，显示mysql数据库说明安装成功

![374f156b236f59185fae7218b8b8b112.png][]

从关系型数据库中导入到HDFS(rdbms -> hdfs)

在mysql中创建一个库和表，方便我们练习

![9c6151a32796ed93261cfa23714a32c2.png][]

create table importhdfs(

id int primary key not null,

name varchar(20) not null

);

![3b7524a82116976e26af05a3cc26454a.png][]

插入数据

insert into importhdfs values(1,'x1');

insert into importhdfs values(2,'x2');

insert into importhdfs values(3,'x3');

insert into importhdfs values(4,'x4');

insert into importhdfs values(5,'x5');

![fe14ea2ee97555cdb523a9e12a9ed1fd.png][]

使用命令导入数据(注意反斜杠，反斜杠后面不能有空格等)

bin/sqoop import

\--connect jdbc:mysql://mastercdh:3306/sqoop\_test

\--username root

\--password password

\--table importhdfs

![d6517a10ebad99d5cd6f46933d03b0c0.png][]

![bcaee47ae4faadaf30ca09115b2ac8c4.png][]

我们可以看reduce数量，sqoop不运行reduce

![a7fa1d9e8d1b98cfe13bc64c53efb445.png][]

或者

![8c567f2ddd7a65f53a10414c880669d9.png][]

那么我们的数据在哪里？

![b2df98608b1f126856361e99438ca08c.png][]

![d05a90df966cd5f70da54314169846b3.png][]

搜索outputdir，就可以看到目录

hdfs://mastercdh:8020/user/root/importhdfs

![2d398b4391c8b1a7821bbf683ff5ccf8.png][]

我们就可以看到数据了

![9815c33a7fa33863179bf62a86499774.png][]

我们查看下数据

bin/hdfs dfs -text /user/root/importhdfs/part-m-00001

![5b0b6307f1864400e9e84c903cf17979.png][]

我们也可以

控制map任务的个数 -m,--num-mappers

输出目录路径如果存在则删除--delete-target-dir

指定输出目录 --target-dir

运行下

bin/sqoop import

\--connect jdbc:mysql://mastercdh:3306/sqoop\_test

\--username root

\--password password

\--delete-target-dir

\--target-dir /sqoop\_test

\--table importhdfs

\-m 1

![9ef5ca5088b65b84244b568c08c441ab.png][]

我们查看数据

![d4d7db3254d9f9c72dba3cb1e3bd2b36.png][]

bin/hdfs dfs -text /sqoop\_test/part-m-00000

![5a184cabbeafc152f3a347e943c23962.png][]

这个数据分隔符是逗号

1,x1

2,x2

3,x3

4,x4

5,x5

我们可以指定数据输出分隔符(mysql默认是 ',')

分隔符参数fields-terminated-by

命令

bin/sqoop import

\--connect jdbc:mysql://mastercdh:3306/sqoop\_test

\--username root

\--password password

\--delete-target-dir

\--target-dir /sqoop\_test

\--table importhdfs

\-m 1

\--fields-terminated-by ""

![bff3d02e85abf2d3e7f7404a376138c4.png][]

查看下数据

bin/hdfs dfs -text /sqoop\_test/part-m-00000

![a1d1a6e5df17c32c80e1edd778a8186a.png][]

其中有个日志信息

![a3ea27785da27c3b4d9231e69d166fd6.png][]

指定快速模式 --direct

命令

bin/sqoop import

\--connect jdbc:mysql://mastercdh:3306/sqoop\_test

\--username root

\--password password

\--direct

\--delete-target-dir

\--target-dir /sqoop\_test

\--table importhdfs

\-m 1

\--fields-terminated-by ""

![f4be68e0580bc7cb5f3b315a862c9efe.png][]

bin/hdfs dfs -text /sqoop\_test/part-m-00000

![9196a7c41f541d0fbdb436d7971aefdb.png][]

增量导入，从原有的数据上进行追加数据。比如mysql中表数据变化了，那么我重新执行sqoop，会把HDFS上的数据删除，这不符合我们的要求，我们只希望在原来的基础上添加数据。

首先了解三个参数

\--check-column (col)：指定要导入时检查的列，行的检测列，一般都是拿主键作为检测列。注意：char、varchar类型不可以使用

\--incremental (mode)：有两种模式：append 和 lastmodifuied

append ：追加 lastmodifuied：时间戳

\--last-value (value)：指定上次导入检测列最后字段的最大值，也就是最后一个值

在append模式下

![155afcb5a1690fea4ed859175118aea7.png][]

增加几条数据

insert into importhdfs values(5,'x5');

insert into importhdfs values(6,'x6');

insert into importhdfs values(7,'x7');

insert into importhdfs values(8,'x8');

insert into importhdfs values(9,'x9');

![a36b9d780b3c7e453b3d77a79edf7c20.png][]

注意：append和--delete-target-dir不能同时使用

bin/sqoop import

\--connect jdbc:mysql://mastercdh:3306/sqoop\_test

\--username root

\--password password

\--direct

\--target-dir /sqoop\_test

\--table importhdfs

\-m 1

\--fields-terminated-by ""

\--check-column id

\--incremental append

\--last-value 5

![3cb79cc35014636a83e5df1f9ae4975a.png][]

会有很多信息

![a88af56afa98906baac1704a4f46b9ff.png][]

![5a024045843e309e279609dc959d04aa.png][]

查看

![422c7b4d6fc487935a94f31bf46f3bee.png][]

查看

bin/hdfs dfs -text /sqoop\_test/part-m-00000

bin/hdfs dfs -text /sqoop\_test/part-m-00001

![f15cda6b2f467f4e4428ea3a48c5ac05.png][]

在lastmodifuied模式下

如果check-column是时间列time的话

\--check-column time time列下面的数据，指定的就是你添加数据时最后的时间戳

\--last-value"2019-12-19 19:04:07" 最后一行的时间戳

创建表

create table inc\_tab(

id int,

name varchar(20),

last\_mod timestamp default current\_timestamp on update current\_timestamp

);

添加数据

insert into inc\_tab(id,name) values(1,'inc1');

insert into inc\_tab(id,name) values(2,'inc2');

insert into inc\_tab(id,name) values(3,'inc3');

insert into inc\_tab(id,name) values(4,'inc4');

insert into inc\_tab(id,name) values(5,'inc5');

![e776986feb3015e1864f6ff333ed1256.png][]

执行sqoop命令

bin/sqoop import

\--connect jdbc:mysql://mastercdh:3306/sqoop\_test

\--username root

\--password password

\--table inc\_tab

\--target-dir /sqoop\_test\_inc

\--fields-terminated-by ''

\--num-mappers 1

![e68eaa0468686223336049c82f6e7d81.png][]

查看数据

bin/hdfs dfs -text /sqoop\_test\_inc/part-m-00000

![1af4ac4ea29806e5f66481d87e4fe080.png][]

再添加几条数据

insert into inc\_tab(id,name) values(6,'inc6');

insert into inc\_tab(id,name) values(7,'inc7');

insert into inc\_tab(id,name) values(8,'inc8');

![376fd8114c6f52519b815cc51c5a675f.png][]

使用lastmodifuied模式进行增量导入指定增量数据是以--append(附加)还是 --merge-key(合并)模式进行添加

bin/sqoop import

\--connect jdbc:mysql://mastercdh:3306/sqoop\_test

\--username root

\--password password

\--table inc\_tab

\--target-dir /sqoop\_test\_inc

\--check-column last\_mod

\--fields-terminated-by ''

\--incremental lastmodified

\--last-value "2019-12-20 03:13:51"

\--num-mappers 1

\--merge-key id

![8e8db989636622a8f05334c85c02caba.png][]

bin/hdfs dfs -text /sqoop\_test\_inc/part-r-00000

![77b48d9386b13a362ed4b7fb4945bef6.png][]

这个地方是有reduce的

![40b7fd64e7700e90375abae2541ad07e.png][]

这些命令我们可以使用job执行：sqoop job

bin/sqoop job --create job01 创建

bin/sqoop job --delete 删除任务

bin/sqoop job --exec 执行job

bin/sqoop job --show 显示job的详细信息

bin/sqoop job --list 显示可执行的job

示例语句：

sqoop job --create myjob -- import --connect jdbc:mysql://example.com/db

\--table mytable

我们做个练习

先导入数据

insert into importhdfs values(11,'x11');

insert into importhdfs values(12,'x12');

insert into importhdfs values(13,'x13');

insert into importhdfs values(14,'x14');

insert into importhdfs values(15,'x15');

![f0b7eeba2ddd00a694bde8fcbd00ebe9.png][]

执行命令

bin/sqoop job

\--create job\_importhdfs

\--

import

\--connect jdbc:mysql://mastercdh:3306/sqoop\_test

\--username root

\--password password

\--direct

\--target-dir /sqoop\_test

\--table importhdfs

\-m 1

\--fields-terminated-by ""

\--check-column id

\--incremental append

\--last-value 10

![baa860b41a85e7c00faeae75b3d9e788.png][]

我们查看是否成功

![3144f76565054ae42ffae85cbaee8558.png][]

我们执行一下

bin/sqoop job --exec job\_importhdfs

执行job的时候会确认密码，指定成mysql的登录密码

![d5fc630fa284a01c8e8e7173c6ebe924.png][]

我们查看数据

![909b7e46c345aaf7089c596b3a2787dc.png][]

可以选择行列

导出某几列数据或者几行数据

bin/sqoop import

\--connect jdbc:mysql://mastercdh:3306/sqoop\_test

\--username root

\--password password

\--direct

\--delete-target-dir

\--target-dir /where\_test\_importhdfs

\--table importhdfs

\-m 1

\--fields-terminated-by ""

\--where 'id<6'

![09d4f6b12690f9c6a0502dc9ab82f3e1.png][]

查看数据

bin/hdfs dfs -text /where\_test\_importhdfs/part-m-00000

![e0179a9332223d5f97f9095fd491fda3.png][]

导出某几列

\--query使用要点：

不能使用--table

如果--query的sql语句带着where字句，

必须要加上标识符$CONDITIONS

![c08146bc071a1cf4f6d321fde3b132b6.png][]

查看数据

bin/hdfs dfs -text /where\_test\_importhdfs\_sql/part-m-00000

![d5eab8881e81b8c83ef73fd79120cb7e.png][]

[5ad9d9c9a342a071af6f27181b04783c.png]: /images/20221024/edcacc4b73e34abeb0453cc897fcf7d1.png
[374f156b236f59185fae7218b8b8b112.png]: /images/20221024/83adfde1ee3342fab5cb4c653a4e9860.png
[9c6151a32796ed93261cfa23714a32c2.png]: /images/20221024/c436a7e7f6164678bf54f22aa5072682.png
[3b7524a82116976e26af05a3cc26454a.png]: /images/20221024/3f7fba56b0d44ff0a6901bd46e2a2f5b.png
[fe14ea2ee97555cdb523a9e12a9ed1fd.png]: /images/20221024/2c2e77f98abe453aa3ae68fcd5af4ccf.png
[d6517a10ebad99d5cd6f46933d03b0c0.png]: /images/20221024/639e78d7e6af416fb6b9b7e96e816aa0.png
[bcaee47ae4faadaf30ca09115b2ac8c4.png]: /images/20221024/a685501d231b4611a280d869e43db527.png
[a7fa1d9e8d1b98cfe13bc64c53efb445.png]: /images/20221024/0035220a3641424d99dd71d49d4c953d.png
[8c567f2ddd7a65f53a10414c880669d9.png]: /images/20221024/144e136caf194dbca0182b036e8406a9.png
[b2df98608b1f126856361e99438ca08c.png]: /images/20221024/d85126106c4148e7996a964e151d3d4d.png
[d05a90df966cd5f70da54314169846b3.png]: /images/20221024/ab571300dd3b48a39cc1e5330369322e.png
[2d398b4391c8b1a7821bbf683ff5ccf8.png]: /images/20221024/a57b96f4f3bb457e96ddd77bc639f12b.png
[9815c33a7fa33863179bf62a86499774.png]: /images/20221024/ee11fbdda5b34115b863ba106fe6963a.png
[5b0b6307f1864400e9e84c903cf17979.png]: /images/20221024/cd9f80bc71a34fbe991c1206060a2ca3.png
[9ef5ca5088b65b84244b568c08c441ab.png]: /images/20221024/2d05f4fe96d042f9b0a953841b184349.png
[d4d7db3254d9f9c72dba3cb1e3bd2b36.png]: /images/20221024/cd07dbe4b469413c8cd7cd62ae4e2cef.png
[5a184cabbeafc152f3a347e943c23962.png]: /images/20221024/54e370b546c9432787a4b8703cd2792a.png
[bff3d02e85abf2d3e7f7404a376138c4.png]: /images/20221024/c056969e57d44b9db8b201ab240b10ef.png
[a1d1a6e5df17c32c80e1edd778a8186a.png]: /images/20221024/0403ec1237c1452eb8cae7c03fe85334.png
[a3ea27785da27c3b4d9231e69d166fd6.png]: /images/20221024/53bfef8567b84472b0461d6c3f58b7c1.png
[f4be68e0580bc7cb5f3b315a862c9efe.png]: /images/20221024/5e4273215cb0419cbad4e389541f7adf.png
[9196a7c41f541d0fbdb436d7971aefdb.png]: /images/20221024/db8ef4cdf8f24bc4aabb9e07a69eadb0.png
[155afcb5a1690fea4ed859175118aea7.png]: /images/20221024/270637dff0a444e2bacacf45013e601b.png
[a36b9d780b3c7e453b3d77a79edf7c20.png]: /images/20221024/b0f46acfea0e4ae5aa79ce8cf2090a25.png
[3cb79cc35014636a83e5df1f9ae4975a.png]: /images/20221024/86a1906ea6e94458af6346923e1ca10c.png
[a88af56afa98906baac1704a4f46b9ff.png]: /images/20221024/1de0217f60964fbdb3758e5d18118ea3.png
[5a024045843e309e279609dc959d04aa.png]: /images/20221024/561a91d8be5344858c3cae00ed71791a.png
[422c7b4d6fc487935a94f31bf46f3bee.png]: /images/20221024/0a805e298f0a49dbbec11b814b354a13.png
[f15cda6b2f467f4e4428ea3a48c5ac05.png]: /images/20221024/4b31d24139af4e75878c7fee932d608a.png
[e776986feb3015e1864f6ff333ed1256.png]: /images/20221024/56f2174ea20547b5a2eb392426c7d86f.png
[e68eaa0468686223336049c82f6e7d81.png]: /images/20221024/7d9fc31a3f4e4ded82d2031e7f2bb1c1.png
[1af4ac4ea29806e5f66481d87e4fe080.png]: /images/20221024/e2739ece9375403fad2cce66d31c1da2.png
[376fd8114c6f52519b815cc51c5a675f.png]: /images/20221024/3a75c55af3174ed2b5b377175464db13.png
[8e8db989636622a8f05334c85c02caba.png]: /images/20221024/a6643dc222934c928c14ee6d6728d36b.png
[77b48d9386b13a362ed4b7fb4945bef6.png]: /images/20221024/79b9eb94d6844fa3bc33069c36ca61cc.png
[40b7fd64e7700e90375abae2541ad07e.png]: /images/20221024/7e759ae66cd94214bc9da5acd3fd6733.png
[f0b7eeba2ddd00a694bde8fcbd00ebe9.png]: /images/20221024/39496cdab18745479ffcef4694e91db7.png
[baa860b41a85e7c00faeae75b3d9e788.png]: /images/20221024/30c21e55da704145abb71396dc44dd4b.png
[3144f76565054ae42ffae85cbaee8558.png]: /images/20221024/c31ca4d65bf54c29819d3d4d74f83a59.png
[d5fc630fa284a01c8e8e7173c6ebe924.png]: /images/20221024/5177ca7c31ce45ca84a40172bde75fd0.png
[909b7e46c345aaf7089c596b3a2787dc.png]: /images/20221024/7b85c50fd90142179d68f7398c8cdab9.png
[09d4f6b12690f9c6a0502dc9ab82f3e1.png]: /images/20221024/3e7f07018c4e4f6faf8ef674f72ef741.png
[e0179a9332223d5f97f9095fd491fda3.png]: /images/20221024/8e195830efb749eca38ff1eafeaa26a1.png
[c08146bc071a1cf4f6d321fde3b132b6.png]: /images/20221024/c10ef0ac636b42f7bf6ad3198f21ef9e.png
[d5eab8881e81b8c83ef73fd79120cb7e.png]: /images/20221024/1dad292ad91a4420858cf77901e84ebe.png