2.7.3 hadoop体系之离线计算-sqoop数据迁移工具-sqoop实战（导入+导出）

小灰灰 2022-12-24 07:58 149阅读 0赞

**目录**

1.Sqoop的数据导入

1.1 查看关系型数据数据库的信息

1.2 导入数据库表中数据到HDFS

1.3 导入数据库表中数据到hdfs指定目录

1.4 导入数据库表中数据到hdfs指定目录，并且指定字段之间的分割符

1.5 导入数据库表中关系表到HIVE

1.6 导入数据库关系表到HIVE，并且自动创建hive表

1.7 导入表数据子集

1.8 增量导入

2.sqoop的数据导出

2.1 hdfs导出到mysql

--------------------

# 1.Sqoop的数据导入 #

导入单个表从RDBMS（关系型数据库）到HDFS。表中的每一行被视为HDFS的记录。所有记录都存储为文本文件的文本数据（或者Avro、sequence文件等二进制数据）

## 1.1 查看关系型数据数据库的信息 ##

命令行查看帮助

bin/sqoop list‐databases ‐‐help

列出windows主机***所有的数据库***

bin/sqoop list‐databases ‐‐connect jdbc:mysql://192.168.1.7:3306/ ‐‐
    username root ‐‐password root

查看某一个数据库下面的***所有数据表***

bin/sqoop list‐tables ‐‐connect jdbc:mysql://192.168.1.7:3306/userdb ‐‐
    username root ‐‐password root

如果出现连接拒绝，则在windows的mysql的数据库中执行以下命令: 开启windows的远程连接权限

GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY 'yourpassword'
    WITH GRANT OPTION;
    FLUSH PRIVILEGES;

## 1.2 导入数据库表中数据到HDFS ##

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

bin/sqoop import ‐‐connect jdbc:mysql://192.168.1.7:3306/userdb ‐‐password root ‐‐username root ‐‐table emp ‐‐m 1

如果成功执行，那么会得到下面的输出。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70][]

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

hdfs dfs ‐ls /user/root/emp

## 1.3 导入数据库表中数据到hdfs指定目录 ##

在导入表数据到HDFS使用Sqoop导入工具，我们可以指定目标目录。

使用参数  **--target-dir  **来指定导出目的地，使用参数  **--delete-target-dir  **来判断导出目录是否存在，如果存在就删掉。

bin/sqoop import ‐‐connect jdbc:mysql://192.168.1.7:3306/userdb ‐‐
    username root ‐‐password root ‐‐delete‐target‐dir ‐‐table emp ‐‐target‐
    dir /sqoop/emp ‐‐m 1

查看导出的数据，它会用逗号（，）分隔emp\_add表的数据和字段。

hdfs dfs ‐text /sqoop/emp/part‐m‐00000

## 1.4 导入数据库表中数据到hdfs指定目录，并且指定字段之间的分割符 ##

bin/sqoop import ‐‐connect jdbc:mysql://192.168.1.7:3306/userdb ‐‐
    username root ‐‐password root ‐‐delete‐target‐dir ‐‐table emp ‐‐
    target‐dir /sqoop/emp2 ‐‐m 1 ‐‐fields‐terminated‐by '\t'

查看文件内容

hdfs dfs ‐text /sqoop/emp2/part‐m‐00000

## 1.5 导入数据库表中关系表到HIVE ##

第一步：拷贝jar包

将我们mysql表当中的数据直接导入到hive表中的话，我们需要将hive的一个叫做hiveexec-3.1.1.jar 的jar包拷贝到sqoop的lib目录下

cp /export/servers/apache‐hive‐3.1.1‐bin/lib/hive‐exec‐3.1.1.jar /export/servers/sqoop‐1.4.7.bin__hadoop‐2.6.0/lib

第二步：准备hive数据库与表

hive (default)> create database sqooptohive;
    hive (default)> use sqooptohive;
    hive (sqooptohive)> create external table emp_hive(id int,name string,deg
    string,salary int ,dept string) row format delimited fields terminated by
    '\001';

第三步：开始导入

bin/sqoop import ‐‐connect jdbc:mysql://192.168.1.7:3306/userdb ‐‐
    username root ‐‐password root ‐‐table emp ‐‐fields‐terminated‐by '\001' ‐
    ‐hive‐import ‐‐hive‐table sqooptohive.emp_hive ‐‐hive‐overwrite ‐‐delete‐
    target‐dir ‐‐m 1

第四步：hive表数据查看

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70 1][]

## 1.6 导入数据库关系表到HIVE，并且自动创建hive表 ##

我们也可以通过命令来将我们的mysql的表直接导入到hive表当中去

bin/sqoop import ‐‐connect jdbc:mysql://192.168.1.7:3306/userdb ‐‐
    username root ‐‐password root ‐‐table emp_conn ‐‐hive‐import ‐m 1 ‐‐hive‐
    database sqooptohive

通过这个命令，我们可以直接将我们mysql表当中的数据以及表结构一起倒入到hive当中 去

## 1.7 导入表数据子集 ##

我们可以导入表的使用Sqoop导入工具，"where"子句的一个子集。它执行在各自的数据库服务器相应的SQL查询，并将结果存储在HDFS的目标目录。

where子句的语法如下：

按照条件进行查找，通过—where参数来查找表emp\_add当中city字段的值为sec-bad的 所有数据导入到hdfs上面去

bin/sqoop import \
    ‐‐connect jdbc:mysql://192.168.1.7:3306/userdb \
    ‐‐username root ‐‐password root ‐‐table emp_add \
    ‐‐target‐dir /sqoop/emp_add ‐m 1 ‐‐delete‐target‐dir \
    ‐‐where "city = 'sec‐bad'"

sql语句查找导入hdfs

我们还可以通过 –query参数来指定我们的sql语句，通过sql语句来过滤我们的数据进行导入

bin/sqoop import \
    ‐‐connect jdbc:mysql://192.168.1.7:3306/userdb ‐‐username root ‐‐password
    root\
    ‐‐delete‐target‐dir ‐m 1 \
    ‐‐query 'select email from emp_conn where 1=1 and $CONDITIONS' \
    ‐‐target‐dir /sqoop/emp_conn

查看hdfs数据内容

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70 2][]

## 1.8 增量导入 ##

在实际工作当中，数据的导入，很多时候都是只需要导入增量数据即可，并不需要将表 中的数据全部导入到hive或者hdfs当中去，肯定会出现重复的数据的状况，所以我们一 般都是选用一些字段进行增量的导入，为了支持增量的导入，sqoop也给我们考虑到了这 种情况并且支持增量的导入数据。增量导入是仅导入新添加的表中的行的技术。

**它需要添加‘incremental’, ‘check-column’, 和 ‘last-value’选项来执行增量导入。**

下面的语法用于Sqoop导入命令增量选项。 第一种增量导入使用上面的选项来实现 导入emp表当中id大于1202的所有数据

**注意：增量导入的时候，一定不能加参数--delete-target-dir否则会报错**

bin/sqoop import \
    ‐‐connect jdbc:mysql://192.168.1.7:3306/userdb \
    ‐‐username root \
    ‐‐password root \
    ‐‐table emp \
    ‐‐incremental append \
    ‐‐check‐column id \
    ‐‐last‐value 1202 \
    ‐m 1 \
    ‐‐target‐dir /sqoop/increment

查看数据内容

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70 3][]

第二种增量导入通过--where条件来实现，或者我们使用--where来进行控制数据的选取会更加精准

bin/sqoop import \
    ‐‐connect jdbc:mysql://192.168.1.7:3306/userdb \
    ‐‐username root \
    ‐‐password admin \
    ‐‐table emp \
    ‐‐incremental append \
    ‐‐where "create_time > '2018‐06‐17 00:00:00' and create_time < '2018‐06‐
    17 23:59:59'" \
    ‐‐target‐dir /sqoop/incement2 \
    ‐‐check‐column id \
    ‐‐m 1

# 2.sqoop的数据导出 #

## 2.1 hdfs导出到mysql ##

导出前，目标表必须存在于目标数据库中。

*  默认操作是从将文件中的数据使用INSERT语句插入到表中
 *  更新模式下，是生成UPDATE语句更新表数据

数据是在HDFS当中的如下目录/sqoop/emp。

第一步：创建mysql表

CREATE TABLE emp_out (
    id INT(11) DEFAULT NULL,
    name VARCHAR(100) DEFAULT NULL,
    deg VARCHAR(100) DEFAULT NULL,
    salary INT(11) DEFAULT NULL,
    dept VARCHAR(10) DEFAULT NULL,
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
    CURRENT_TIMESTAMP,
    is_delete BIGINT(20) DEFAULT '1'
    ) ENGINE=INNODB DEFAULT CHARSET=utf8;

第二步：执行导出命令

通过export来实现数据的导出，将hdfs的数据导出到mysql当中去

bin/sqoop export \
    ‐‐connect jdbc:mysql://192.168.1.7:3306/userdb \
    ‐‐username root ‐‐password root \
    ‐‐table emp_out \
    ‐‐export‐dir /sqoop/emp \
    ‐‐input‐fields‐terminated‐by ","

第三步：验证mysql表数据

![20201127145147783.png][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70]: /images/20221120/0a6d5876f4f24be6a29ca5e11fe116b9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70 1]: /images/20221120/a1cecdab0bf843ffb3d624c9bb7dcd45.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20201127144038503.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20201127144155162.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU=,size_16,color_FFFFFF,t_70
[20201127145147783.png]: https://img-blog.csdnimg.cn/20201127145147783.png