Hadoop大数据平台构建与应用教程 课件 项目九 Sqoop数据迁移操作

项目九Sqoop数据迁移操作CONTENTS目录01

项目导读02

知识目标03

技能目标04

素质（思政）目标05

任务一Sqoop安装CONTENTS目录06

任务二Mysql数据导出到HDFS07

任务三Mysql与Hive数据互导08

项目总结09

项目考核项目导读01项目导读Sqoop工具的安装和使用详解在实际开发中，有时候需要将HDFS或Hive上的数据导出到传统数据库中（如MySQL、Oracle等），或者将传统数据库中的数据导入到HDFS或Hive上，如果通过人工手动进行数据迁移的话，就会显得非常麻烦。为此，可以使用Apache提供的Sqoop工具进行数据迁移，本章就对Sqoop工具的安装和使用进行详细讲解知识目标02知识目标了解Sqoop基本概念掌握Sqoop安装配置熟悉Sqoop常用的相关指令技能目标03技能目标

掌握Sqoop的安装和使用

掌握使用Sqoop从MySQL表导入HDFS

掌握使用Sqoop从MySQL表导入Hive

掌握使用Sqoop从HDFS导出到MySQL

掌握使用Sqoop从Hive导出到MySQL素质（思政）目标04素质（思政）目标

培养严谨细致的工匠精神

增强自学能力，能够应对MySQL安装和配置过程中遇到的各种挑战

培养不骄不躁的工匠心态

培养保密意识，良好的数据管理习惯，确保数据的完整性和一致性

培养网络安全意识任务一Sqoop安装05任务工单

任务描述在安装Sqoop之前，须先做一些铺垫，检查系统的CPU、内存和存储空间是否满足Sqoop的最低要求，配置Linux环境、选择合适的Sqoop版本（如社区版或企业版）

任务准备全班学生以4人左右为一组，各组选出组长。请组长组织组员查找相关资料，并组织讨论和汇总问题1：Sqoop有什么用问题2：Sqoop的安装步骤是什么必备知识技能：一、Sqoop概述

Sqoop发展历程Sqoop是Apache旗下的一款开源工具，该项目开始于2009年，最早是作为Hadoop的一个第三方模块存在，后来为了让使用者能够快速部署，也为了让开发人员能够更快速地迭代开发，在2013年，独立成为Apache的一个顶级开源项目

Sqoop数据传输工具Sqoop主要用于在Hadoop和关系数据库或大型机之间传输数据，可以使用Sqoop工具将数据从关系数据库管理系统导入（import）到Hadoop分布式文件系统中，或者将Hadoop中的数据转换导出（export）到关系数据库管理系统

Sqoop版本差异目前Sqoop主要分为Sqoop1和Sqoop2两个版本，其中，版本号为1.4.x属于Sqoop1，而版本号为1.99.x的属于Sqoop2。这两个版本开发时的定位方向不同，体系结构具有很大的差异，因此它们之间互不兼容

Sqoop1与Sqoop2对比Sqoop1功能结构简单，部署方便，提供命令行操作方式，主要适用于系统服务管理人员进行简单的数据迁移操作；Sqoop2功能完善、操作简便，同时支持多种访问模式（命令行操作、Web访问和RestAPI），引入角色安全机制增加安全性等多种优点，但是结构复杂，配置部署更加繁琐。由于本书只用到Sqoop解决数据迁移问题，因此使用Sqoop1就可以完成基本的需求必备知识技能：Sqoop原理Sqoop原理Sqoop是传统关系数据库服务器与Hadoop间进行数据同步的工具，其底层利用MapReduce并行计算模型以批处理方式加快了数据传输速度，并且具有较好的容错性功能通过客户端CLI（命令行界面）方式或JavaAPI方式调用Sqoop工具Sqoop可以将指令转换为对应的MapReduce作业（通常只涉及Map任务，每个Map任务从数据库中读取一片数据，这样多个Map任务实现并发地复制，可以快速地将整个数据复制到HDFS上），然后将关系数据库和Hadoop中的数据进行相互转换，从而完成数据的迁移可以说，Sqoop是关系数据库与Hadoop之间的数据桥梁，这个桥梁的重要组件是Sqoop连接器，它用于实现与各种关系数据库的连接，从而实现数据的导入和导出操作。Sqoop连接器能够支持大多数常用的关系数据库，如MySQL、Oracle、DB2和SQLServer等，同时它还有一个通用的JDBC连接器，用于连接支持JDBC协议的数据库导入原理在导入数据之前，Sqoop使用JDBC检查导入的数据表，检索出表中的所有列以及列的SQL数据类型，并将这些SQL类型映射为Java数据类型，在转换后的MapReduce应用中使用这些对应的Java类型来保存字段的值，Sqoop的代码生成器使用这些信息来创建对应表的类，用于保存从表中抽取的记录必备知识技能：Sqoop原理

导出原理在导出数据之前，Sqoop会根据数据库连接字符串来选择一个导出方法，对于大部分系统来说，Sqoop会选择JDBC。Sqoop会根据目标表的定义生成一个Java类，这个生成的类能够从文本中解析出记录数据，并能够向表中插入类型合适的值，然后启动一个MapReduce作业，从HDFS中读取源数据文件，使用生成的类解析出记录，并且执行选定的导出方法任务实施：一、安装Sqoop

步骤1将下载好的安装包sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz拷贝到master节点的/usr/local/src目录中，在终端命令窗口执行如下命令解压sqoop压缩包tar-zxvfsqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz步骤2进入Sqoop主目录，在conf目录下，将sqoop-env-template.sh文件复制并重命名为sqoop-env.sh，在终端命令窗口执行如下命令cpsqoop-env-template.shsqoop-env.sh步骤3：编辑sqoop-env.sh文件，在sqoop-env.sh末尾添加以下代码HadoopEnvironmentSetupexportHADOOP_COMMON_HOME=/usr/local/src/hadoop-2.7.3任务实施：一、安装Sqoop

HadoopMapReduceEnvironmentSetupexportHADOOP_MAPRED_HOME=/usr/local/src/hadoop-2.7.3/share/hadoop/mapreduceexportHBASE_HOME=/usr/local/src/hbase-1.3.1HIVE_HOMEExportexportHIVE_HOME=/usr/local/src/apache-hive-1.2.1-binZookeeperConfigurationExportexportZOOCFGDIR=/usr/local/src/zookeeper-3.4.10/conf步骤4安装MySQL连接jar包。将mysql-connector-java-5.1.27-bin.jar这个jar包，拷贝到Sqoop的lib目录下步骤5：配置环境变量，在终端命令窗口执行如下命令任务实施：一、安装Sqoop单击此处添加正文

vim/etc/profile单击此处添加项正文编辑profile文件，在文件末尾添加如下内容单击此处添加项正文#sqoop单击此处添加项正文SettingSQOOPEnvironmentVariableexportSQOOP_HOME=/usr/local/src/sqoop-1.4.6.bin__hadoop-2.0.4-alphaexportPATH=$PATH:${SQOOP_HOME}/bin单击此处添加项正文任务实施：一、安装Sqoop

01exportCLASSPATH=$CLASSPATH:${SQOOP_HOME}/lib

02步骤6：使环境变量立刻生效，在终端命令窗口执行如下命令

03source/etc/profile任务实施：二、测试Sqoop

步骤7：测试与MySQL连接，进入sqoop主目录，在终端命令窗口执行如下命令单击此处添加项正文

SQOOPListDatabasessqooplist-databases--connectjdbc:mysql://127.0.0.1:3306/--usernameroot--passwordJscfa2022$

如果成功进入sqoop命令行界面，则表示连接成功单击此处添加项正文任务二Mysql数据导出到HDFS06任务工单任务描述

Sqoop工具操作简单，它提供了一系列的工具指令，用来进行数据的导入、导出操作等使用Sqoop解压包中bin目录下的“sqoophelp"指令可以查看Sqoop支持的所有工具指令任务准备

全班学生以4人左右为一组，各组选出组长。请组长组织组员查找相关资料，并组织讨论和汇总问题1：Sqoop如何导入到HDFS问题2：Sqoop如何增量导入到HDFS必备知识技能：一、MySQL表数据导入到HDFSSqoop数据导入概述Sqoop数据导入（import）是将关系数据库中的单个表数据导入到HDFS和Hive等具有Hadoop分布式存储结构的文件系统中，表中的每一行都被视为一条记录，所有记录默认以文本文件格式进行逐行存储，还可以以二进制形式存储，如Avro文件格式和序列文件格式（SequenceFile）Sqoop数据导入导出演示为了演示Sqoop数据导入、导出的相关操作，首先在hadoop1机器上安装的MySQL数据库中创建userdb数据库，字符集设置为UTF-8，接下来创建3张表：emp、emp_add和emp_conn，并导入相关初始化数据，具体userdb.sql语句如文件10-1所示。将MySQL表数据导入到HDFS中，具体指令，请看任务实施必备知识技能：二、HBase增量导入

Sqoop增量导入模式当MySQL表中的数据发生了新增或修改变化，需要更新HDFS上对应的数据时，就可以使用Sqoop的增量导入功能。Sqoop目前支持两种增量导入模式：append模式和lastmodified模式。其中，append模式主要针对INSERT新增数据的增量导入；lastmodified模式主要针对UPDATE修改数据的增量导入

增量导入操作指南在进行增量导入操作时，首先必须指定“－-check-column”参数，用来检查数据表列字段，从而确定哪些数据需要执行增量导入。例如，在执行append模式增量导入时，通常会将“check-column”参数指定为具有连续自增功能的列（如主键id）；而执行lastmodified模式增量导入时，通常会将“－-check-column”参数指定为日期时间类型的列（如date或timestamp类型的列）。同时，还可以为增量导入操作指定“－-last-value”参数，只用于增量导入last-value值以后的记录数据，然后存储到之前HDFS上相应目录下的一个单独文件中。否则，会导入原表中所有数据到HDFS上相应目录下的一个单独文件中任务实施：一、普通导入01步骤1：在MySQL里面创建数据库mydb，在mysql命令窗口执行如下命令02CREATEDATABASEmydb03步骤2：创建表格emp，切换到数据库mydb，在mysql命令窗口执行如下命令04usemydb05CREATETABLEemp(06idVARCHAR(50)任务实施：一、普通导入nameVARCHAR(50)lanVARCHAR(50)salVARCHAR(50)depVARCHAR(50))步骤3：插入数据到表emp，在mysql命令窗口执行如下命令任务实施：一、普通导入

SQLEmployeeDataInsertionINSERTINTOempVALUES('1201','lily','cdev','60000','sales')SQLEmployeeInsertionINSERTINTOempVALUES('1202','bob','godev','70000','dev')SQLCommandExampleINSERTINTOempVALUES('1203','tim','sparkdev','80000','dev')SQLCommandExampleINSERTINTOempVALUES('1204','john','phpdev','20000','dev')SQLCommandExampleINSERTINTOempVALUES('1205','nana','C++dev','50000','dev')步骤4：将MySQL表数据导入到HDFS中。切换到sqoop主目录，在终端命令窗口执行如下命令任务实施：一、普通导入sqoopimport\--connectjdbc:mysql://hadoop1:3306/mydb--usernameroot\--passwordJscfa2022$\--target-dir/sqoopresult\--tableemp任务实施：一、普通导入

--num-mappers1MySQL数据导入HDFS指南上述指令演示了将MySQL表数据导入到HDFS中的基本使用，其中包含了多个参数，“”符号用于单个指令换行，下面对其中的参数进行具体说明--connect指定连接的关系数据库，包括JDBC驱动名、主机名、端口号和数据库名称。应注意的是，Sqoop数据导入导出操作需要启动Hadoop集群的MapReduce程序，所以这里连接的主机名不能是localhost，必须是MySQL数据库所在主机名或IP地址--username：用于指定连接数据库的用户名--password用于指定连接数据库的密码。这种方式直接暴露了数据库连接密码，不太安全，所以可以使用-P指令代替，这个指令会以交互方式提示用户输入密码--target-dir指定导入到HDFS的目录，代表MySQL数据表要导入HDFS的目标地址。这里需要注意该选项所指定的目录的最后一个子目录不能存在，否则Sqoop会执行失败任务实施：一、普通导入

--table：代表要进行数据导入操作的MySQL源数据库表名单击此处添加项正文

--num-mappers指定map任务个数（默认为4个，并且会产生4个结果文件），可简写为-m。这里指定map任务个数为1，那么只会启动一个Map程序执行相关操作，并只会生成一个结果文件

Sqoop操作与MapReduce集成执行上述指令后，Sqoop操作会转换为MapReduce任务在整个集群中并行执行，作业执行成功后可以通过HDFSweb界面查看数据结果文件任务实施：二、增量导入

步骤1：向emp表添加新数据，在mysql命令窗口执行如下命令InsertIntoempValues示例INSERTINTO`emp`VALUES('1206','zhangsan','javadev','50000','AC')步骤2：增量导入数据到表格emp。目录切换到sqoop主目录，在终端命令窗口执行如下命令sqoopimport\--connectjdbc:mysql://hadoop01:3306/mydb\--usernameroot\任务实施：二、增量导入--passwordJscfa2022$\--target-dir/sqoopresult2\--tableemp\--num-mappers1\--incrementalappend\任务实施：二、增量导入--check-columnid\单击此处添加项正文--last-value1205单击此处添加项正文增量导入功能及参数设置为了实现增量导入功能，新添加了3个参数。其中，“－-incrementalappend”指定了使用增量导入的模式为append；“--check-columnid”指定了针对表emp数据的id主键进行检查；“－-last-value1205"指定了针对id值为1205以后的数据执行增量导入。这里只演示了开发中常用的append模式的增量导入操作，读者也可以根据说明进行另一种lastmodified模式增量导入的操作。但需要注意的是，必须保证lastmodified模式增量导入的数据具有日期时间类型的列字段任务三Mysql与Hive数据互导07任务工单

任务描述如果Hadoop集群中部署了Hive服务，并且在Sqoop服务的sqoop-env.sh文件中配置了Hive的安装路径，那么也可以通过Sqoop工具将MySQL表数据导入Hive表中

任务准备全班学生以4人左右为一组，各组选出组长。请组长组织组员查找相关资料，并组织讨论和汇总问题1：MySQL表如何导入到Hive问题2：Hive如何导入到MySQL表必备知识技能

MySQL部分数据导入方法前面几节针对MySQL表数据的全表导入与增量导入进行了讲解，而在实际业务中，有时候开发人员可能只需要针对部分数据进行导入操作。针对上述需求，可以使用Sqoop提供的“--where”和“-query”参数，先进行数据过滤，然后再将满足条件的数据进行导入在使用Sqoop的“-query”参数进行数据导入时，要特别注意以下事项"-split-by"参数说明1.如果没有指定“－-num-mappers”（或-m1，即map任务个数为1），那么在指令中必须还要添加“-split-by"参数。“-split-by”参数的作用就是针对多副本map任务并行执行查询结果并进行数据导入，该参数的值要指定为表中唯一的字段（如主键id）SQL条件语句规则2.“-query"参数后的查询语句中（如示例中单引号中的SELECT语句），如果已经使用了WHERE关键字，那么在连接$CONDITIONS占位符前必须使用AND关键字；否则，就必须使用WHERE关键字连接"$CONDITIONS"使用规范3.“-query"参数后的查询语句中的$CONDITIONS占位符不可省略，并且如果查询语句使用双引号（"）进行包装，那么就必须使用\$CONDITIONS，这样可以避免Shell将其视为Shell变量任务实施：一、MySQL导出数据到Hive

步骤1：在master上启动Hadoophdfs，在终端命令窗口执行如下命令

sbin/start-dfs.sh

步骤2：在hadoop01上启动yarn，在终端命令窗口执行如下命令

sbin/start-yarn.sh

步骤3：登录MySQL，在终端命令窗口执行如下命令

mysql-uroot–pJscfa2022$任务实施：一、MySQL导出数据到Hive

步骤4：在MySQL创建数据库bob，在mysql命令窗口执行如下命令CREATEDATABASEbob步骤5：进入数据库bob，在mysql命令窗口执行如下命令usesbob步骤6：创建student表，在mysql命令窗口执行如下命令CreateTableStatementcreatetablestudent(namevarchar(30),ageint(10))任务实施：一、MySQL导出数据到Hive

步骤7：往student表格插入两条测试数据，在mysql命令窗口执行如下命令insertintostudentvalues("zhangsan",18)insertintostudentvalues("lisi",19)步骤8MySQL导出到Hive。打开另一个Linux终端，进入sqoop的主目录，在终端命令窗口执行如下命令sqoopimport\--connectjdbc:mysql://master:3306/bob\任务实施：一、MySQL导出数据到Hive

--usernameroot\

--passwordJscfa2022$\

--tablestudent\

--hive-tablehive.student\

--create-hive-table\

--hive-import\任务实施：一、MySQL导出数据到Hive

--num-mappers1

步骤9：回到Hive中查询结果，在hive命令窗口执行如下命令

usehive

showtables

select*fromstudent任务实施：二、Hive导出数据到MySQL

01步骤1：在Hive中建表student2，在hive命令窗口执行如下命令

02createtablestudent2(

03idint

04numberstring

05namestring

06sexstring任务实施：二、Hive导出数据到MySQL

)rowformatdelimitedfieldsterminatedby'\t步骤2：插入数据到Hive的student2表中，在hive命令窗口执行如下命令InsertintoStudentTableinsertintostudent(id,number,name,sex)values(1,'123456789','jack','man')步骤3打开另一个Linux终端，登录进MySQL，在MySQL的bob数据库里建表student2createtablestudent2(idint任务实施：二、Hive导出数据到MySQLnumbervarchar(30)namevarchar(30)sexvarchar(20))步骤4：进入sqoop的主目录，从hive导出数据到mysql，在终端命令窗口执行如下命令sqoopexport\任务实施：二、Hive导出数据到MySQL单击此处添加正文--connectjdbc:mysql://master:3306/bob\单击此处添加项正文--usernameroot\单击此处添加项正文--passwordJscfa2022$\单击此处添加项正文--tablestudent2\单击此处添加项正文HiveDataExport--export-dir'/user/hive/warehouse/hive.db/student2'\任务实施：二、Hive导出数据到MySQL

--fields-terminated-by'\t单击此处添加项正文

步骤5登录进MySQL，切换到mysql的bob数据库，查看表格s