sqoop数据迁移

墨蓝 2022-11-21 09:59 235阅读 0赞

# **1 概述** #

sqoop是apache旗下一款**“Hadoop和关系数据库服务器之间传送数据”**的工具。

**导入数据**：MySQL，Oracle导入数据到Hadoop的HDFS、HIVE、HBASE等数据存储系统；

**导出数据：**从Hadoop的文件系统中导出数据到关系数据库

## 1.1 sqoop与hadoop的对应关系： ##

**由于sqoop2版本不打算开发使用，所以使用sqoop1！！！**

![20201031181622678.png][]

![20201031181649842.png][]

![20201031175857253.png][]

# **2 工作机制** #

将导入或导出命令翻译成mapreduce程序来实现

在翻译出的mapreduce中主要是对inputformat和outputformat进行定制

# **3 sqoop实战及原理** #

## **3.1 sqoop安装** ##

安装sqoop的前提是已经具备java和hadoop的环境

**1、下载并解压**

最新版下载地址[http://archive.apache.org/dist/sqoop/1.4.6/sqoop-1.4.6.bin\_\_hadoop-2.0.4-alpha.tar.gz][http_archive.apache.org_dist_sqoop_1.4.6_sqoop-1.4.6.bin_hadoop-2.0.4-alpha.tar.gz]

![20201031193158558.png][]

**2、修改配置文件**

mv sqoop-env-template.sh sqoop-env.sh

打开sqoop-env.sh并编辑下面几行：

vim sqoop-env.sh

# Licensed to the Apache Software Foundation (ASF) under one or more
    # contributor license agreements.  See the NOTICE file distributed with
    # this work for additional information regarding copyright ownership.
    # The ASF licenses this file to You under the Apache License, Version 2.0
    # (the "License"); you may not use this file except in compliance with
    # the License.  You may obtain a copy of the License at
    #
    #     http://www.apache.org/licenses/LICENSE-2.0
    #
    # Unless required by applicable law or agreed to in writing, software
    # distributed under the License is distributed on an "AS IS" BASIS,
    # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    # See the License for the specific language governing permissions and
    # limitations under the License.
    
    # included in all the hadoop scripts with source command
    # should not be executable directly
    # also should not be passed any arguments, since we need original $*
    
    # Set Hadoop-specific environment variables here.
    
    #Set path to where bin/hadoop is available
    export HADOOP_COMMON_HOME=/mine/hadoop-3.2.0
    
    #Set path to where hadoop-*-core.jar is available
    export HADOOP_MAPRED_HOME=/mine/hadoop-3.2.0
    
    #set the path to where bin/hbase is available
    #export HBASE_HOME=
    
    #Set the path to where bin/hive is available
    export HIVE_HOME=/mine/apache-hive-3.1.2-bin
    
    #Set the path for where zookeper config dir is
    #export ZOOCFGDIR=

export JAVA_HOME=/mine/jdk1.8.0_201
    export HIVE_HOME=/mine/apache-hive-3.1.2-bin
    export HADOOP_HOME=/mine/hadoop-3.2.0
    export FLUME_HOME=/mine/apache-flume-1.9.0-bin
    export SQOOP_HOME=/mine/sqoop-1.4.6.bin__hadoop-2.0.4-alpha
    export ZOOKEEPER_HOME=/mine/apache-zookeeper-3.6.2-bin
    export PATH=$PATH:$JAVA_HOME/bin:$HADOOP_HOME/bin:/$HADOOP_HOME/sbin:$ZOOKEEPER_HOME/bin:$HIVE_HOME/bin:$FLUME_HOME/bin:$SQOOP_HOME/bin

**3、 加入mysql的jdbc驱动包**

scp /mine/mysql-connector-java-8.0.11.jar lib/

**4、导入表表数据到HDFS**

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

sqoop import --connect jdbc:mysql://ljxwtl.cn:3306/bigdata?zeroDateTimeBehavior=round --username root --password XXXXXXXXXXXX --table boke --m 1

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d0bDE5OTI_size_16_color_FFFFFF_t_70][]

**5、 导入关系表到HIVE**

sqoop import --connect jdbc:mysql://ljxwtl.cn:3306/bigdata?zeroDateTimeBehavior=round --username root --password XXXXXXXXXXXX--table boke  --hive-import --m 1

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d0bDE5OTI_size_16_color_FFFFFF_t_70 1][]** 6、导入到HDFS指定目录**

sqoop import --connect jdbc:mysql://ljxwtl.cn:3306/bigdata?zeroDateTimeBehavior=round --username root --password XXXXXXXXXXXXX --table boke --m 1 --target-dir /boke

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d0bDE5OTI_size_16_color_FFFFFF_t_70 2][]

**7、导入表数据子集**

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

where子句的语法如下。

--where <condition>

sqoop import --connect jdbc:mysql://ljxwtl.cn:3306/bigdata?zeroDateTimeBehavior=round --username root --password XXXXXXXXXXXX --table iqiyi --m 1 --target-dir /iqiyi --where "stars like '%唐嫣%'"

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d0bDE5OTI_size_16_color_FFFFFF_t_70 3][]** 按需导入:**

带参数“$CONDITIONS”，且用双引号时，需要在$CONDITIONS前加\\：

这里注意，我加的参数是**`\$CONDITIONS`**而不是**`$CONDITIONS`**，多了一个**`\`**

sqoop import --connect jdbc:mysql://ljxwtl.cn:3306/bigdata?zeroDateTimeBehavior=round --username root --password wtl199201180271 --m 1 --target-dir /iqiyi2 --query "select id,title,href,stars from iqiyi  where stars like '%唐嫣%' and \$CONDITIONS" --split-by id --fields-terminated-by '\t'

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d0bDE5OTI_size_16_color_FFFFFF_t_70 4][]

# 4、 Sqoop的数据导出 #

### 4.1、将数据从HDFS导出到RDBMS数据库 ###

**（1）导出前，目标表必须存在于目标数据库中。  
（2）默认操作是从将文件中的数据使用INSERT语句插入到表中  
（3）更新模式下，是生成UPDATE语句更新表数据**

### **语法** ###

以下是export命令语法。

sqoop export (generic-args) (export-args)

**1、首先需要手动创建mysql中的目标表 **

CREATE TABLE `student` (
      `id` varchar(256) DEFAULT NULL,
      `name` varchar(256) DEFAULT NULL,
      `sex` varchar(32) DEFAULT NULL,
      `age` int DEFAULT NULL,
      `address` varchar(256) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

** 2、然后执行导出命令**

sqoop export --connect jdbc:mysql://ljxwtl.cn:3306/bigdata?zeroDateTimeBehavior=round --username root --password XXXXXXXXXXXX --table student  --export-dir /user/hive/warehouse/azkaban_student

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d0bDE5OTI_size_16_color_FFFFFF_t_70 5][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d0bDE5OTI_size_16_color_FFFFFF_t_70 6][]

[20201031181622678.png]: /images/20221120/9c2d78d8f93e46ca9585066c3da2e06c.png
[20201031181649842.png]: /images/20221120/a21dc033cd3e432a9479e1ce85b3ae24.png
[20201031175857253.png]: /images/20221120/0bf2edb9d6254f4dbbd35842f3a4d518.png
[http_archive.apache.org_dist_sqoop_1.4.6_sqoop-1.4.6.bin_hadoop-2.0.4-alpha.tar.gz]: http://archive.apache.org/dist/sqoop/1.4.6/sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz
[20201031193158558.png]: https://img-blog.csdnimg.cn/20201031193158558.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d0bDE5OTI_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20201031194351713.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d0bDE5OTI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d0bDE5OTI_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20201031200118373.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d0bDE5OTI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d0bDE5OTI_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20201031200422430.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d0bDE5OTI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d0bDE5OTI_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/2020110108101320.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d0bDE5OTI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d0bDE5OTI_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20201101082929242.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d0bDE5OTI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d0bDE5OTI_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20201101085151815.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d0bDE5OTI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d0bDE5OTI_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20201101085218795.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d0bDE5OTI=,size_16,color_FFFFFF,t_70