Hadoop技术与应用 课件 项目10 Sqoop实现数据传输

项目二：Sqoop数据传输应用点击添加文本点击添加文本点击添加文本点击添加文本

在当前大多数生产背景下，数据的采集、处理是在关系型数据库中进行的，要实现大数据的处理和分析就要实现关系型数据库和Hadoop平台之间所存储的数据的相互传输、转换。首先需要把数据从关系型数据库导入到Hadoop平台，利用Hadoop平台强大的数据处理能力进行大数据处理。数据处理结束后，再把数据从Hadoop平台导出到关系型数据库中。在Sqoop出现之前，由于缺乏相应工具的支持，对Hadoop和传统数据库系统中的数据进行相互传输是一件十分困难的事情；Sqoop软件的诞生使数据在关系型数据库系统和Hadoop平台之间的传输变得非常方便。Sqoop成立于2009年，刚开始是作为Hadoop的一个模块而存在的，后来为了更好的进行部署使用，成为apache基金会组织的一个项目，其主要解决Hadoop集群数据和关系数据库中数据的传输。项目概述点击添加文本点击添加文本点击添加文本点击添加文本使用Sqoop实现HDFS和关系数据库Mysql之间的数据传输，在传输过程中要保证数据的正确性、完整性。项目背景（需求）

点击添加文本点击添加文本点击添加文本点击添加文本项目演示（体验）

Sqoop将数据从Mysql导入到HDFSSqoop将数据从HDFS导出到MysqlSqoop将HDFS数据导出到Mysql数据表指定的列点击添加文本点击添加文本点击添加文本点击添加文本思维导图点击添加文本点击添加文本点击添加文本点击添加文本思政聚焦利用Sqoop软件实现数据在HDFS和mysql之间的传输、转换时，虽然数据以不同形式存储，但数据所表达的含义是不变的，必须确保信息传输过程的完整性、真实性，避免数据的丢失、损坏，这样才能实现不同形式下数据的客观和有效分析。这个过程和辩证唯物主义所主张的保持不同意识形态下事物实事求是的客观真实性观点完全一致，利用这一思想指导软件技术发展我们才能取得不断的进步，让科技改善我们的学习、工作、生活。点击添加文本点击添加文本点击添加文本点击添加文本本项目主要介绍Sqoop简介、工作原理Sqoop软件的下载、安装、配置Sqoop常用的命令及参数Sqoop将数据从Mysql导入到HDFSSqoop将数据从HDFS导出到Mysql关系数据库常用的数据操作语言DDL点击添加文本点击添加文本点击添加文本点击添加文本教学大纲

能力目标◎能成功完成在CentOS7系统中下载、安装、配置Sqoop软件。◎能掌握Sqoop常用的命令及参数◎能使用Sqoop将数据从Mysql导入到HDFS◎能使用Sqoop将数据从HDFS导出到Mysql◎掌握关系数据库常用的数据操作语言DDL◎掌握HDFS操作文件的命令知识目标◎熟悉Sqoop常用的命令及参数◎熟悉Sqoop将数据从Mysql导入到HDFS◎熟悉Sqoop将数据从HDFS导出到Mysql◎熟悉关系数据库常用的数据操作语言DDL◎熟悉HDFS操作文件的命令学习重点◎Sqoop常用的命令及参数

◎Sqoop将数据从Mysql导入到HDFS◎Sqoop将数据从HDFS导出到Mysql学习难点◎Sqoop将数据从Mysql导入到HDFS◎Sqoop将数据从HDFS导出到Mysql点击添加文本点击添加文本点击添加文本点击添加文本目录任务2Sqoop将数据从HDFS导出到Mysql任务1Sqoop将数据从Mysql导入到HDFS

点击添加文本点击添加文本点击添加文本点击添加文本任务1Sqoop将数据从Mysql导入到HDFS1.Sqoop软件压缩包的下载：在sqoop官网镜像地址下载sqoop软件压缩sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz，放置CentOs系统的/opt/software目录中Sqoop软件解压a）

在目录/opt/software下，解压sqoop软件压缩包。

运行命令：tar–zxvfsqoop-1.4.7.bin__hadoop-2.6.0.tar.gzb）

更改解压后的文件夹名为sqoop

运行命令：mvsqoop-1.4.7.bin__hadoop-2.6.0sqoop配置系统环境变量

运行命令：vim/etc/profile

在文件末尾添加环境变量SQOOP_HOME，并休改环境变量PATH,修改

内容如下：exportSQOOP_HOME=/opt/software/sqoopexportPATH=$SQOOP_HOME/bin:$PATH

使用source命令使环境变量配置生效

运行命令：source/etc/profile

1.Sqoop软件的下载、安装、配置点击添加文本点击添加文本点击添加文本点击添加文本任务1Sqoop将数据从Mysql导入到HDFSSqoop软件是一个在关系型数据库（Mysql、Oracle）和Hadoop之间进行批量数据传输的工具。Sqoop底层用MapReduce程序实现数据的抽取、转换、加载，MapReduce天生的特性保证了并行化和高容错率，MapReduce任务在Hadoop集群上运行，减少了对服务器资源的浪费情况，在性能方面有很大的优化。Sqoop架构图1.Sqoop简介点击添加文本点击添加文本点击添加文本点击添加文本任务1Sqoop将数据从Mysql导入到HDFSSqoop对于数据的导入、导出都是通过MapReduce作业实现的。Sqoop从关系型数据库导入数据时，在MapReduce作业中，会从表中读取一行行记录，然后将其写入HDFS。在开始导入之前，Sqoop会通过JDBC来获得所需要的数据库元数据，例如：导入表的列名，数据类型等；接着这些数据库的数据类型会被映射成java类型，根据这些信息，Sqoop会生成一个与表名同名的类用来完成反序列化的工作，并保存表中的每一行记录。Sqoop启动MapReduce作业，MapReduce中主要是对输入类InputFormat和输出类OutputFormat进行定制；启动的作业在数据导入的过程中，会通过JDBC读取数据库表中的内容，并使用Sqoop生成的类进行反序列化，再将这些记录写到HDFS中，在写入HDFS的过程中，同样会使用Sqoop生成的类进行序列化。Sqoop将数据从HDFS导出到关系数据库时，同样会使用MapReduce作业，首先需要在关系数据库中创建一个数据表，用来记录要传输的表的表名和字段，从HDFS相应的目录逐行读取数据，通过JDBC把数据批量导入到关系数据库的数据表中。相比于传统的数据收集工具，Sqoop的优势有以下几点：sqoop可以高效的可控的利用资源，比如它可以通过调整任务数，来控制任务的并发度，另外还可以配置数据库的访问时间等等sqoop能自动的完成数据类型的映射与转换Sqoop支持多种关系数据库和Hadoop集群之间的数据传输，比如mysqloracle，postgresql1.Sqoop简介点击添加文本点击添加文本点击添加文本点击添加文本任务1Sqoop将数据从Mysql导入到HDFS修改Sqoop配置文件中的变量a）进入sqoop安装目录下的文件加conf

运行命令：cd/opt/software/sqoop/confb）利用该目录下所提供的模板，生成sqoop配置文件sqoop-env.sh

运行命令：cpsqoop-env-template.shsqoop-env.shc）修改配置文件sqoop-env.sh，将HADOOP_COMMON_HOME和HADOOP_MAPRED_HOME变量修改为Hadoop安装目录，如下所示：

#Setpathtowherebin/hadoopisavailableexportHADOOP_COMMON_HOME=/opt/software/hadoop-2.7.6#Setpathtowherehadoop-*-core.jarisavailableexportHADOOP_MAPRED_HOME=/opt/software/hadoop-2.7.6d）修改配置文件sqoop-env.sh，将HIVE_HOME变量修改为Hive安装目录，如下所示：exportHIVE_HOME=/opt/software/hive1.Sqoop软件的下载、安装、配置点击添加文本点击添加文本点击添加文本点击添加文本任务1Sqoop将数据从Mysql导入到HDFS屏蔽Sqoop启动时默认检查的参数HBASE_HOME、HCAT_HOME、ACCUMULO_HOME、ZOOKEEPER_HOME，避免Sqoop启动时输出多余的警告信息。运行命令：cd/opt/software/sqoop/bin运行命令：viconfigure-sqoop

修改配置文件configure-sqoop，在如下内容加入注释。##Movedtobearuntimecheckinsqoop.##if[!-d"${HBASE_HOME}"];then##echo"Warning:$HBASE_HOMEdoesnotexist!HBaseimportswillfail."##echo'Pleaseset$HBASE_HOMEtotherootofyourHBaseinstallation.'##fi##Movedtobearuntimecheckinsqoop.##if[!-d"${HCAT_HOME}"];then##echo"Warning:$HCAT_HOMEdoesnotexist!HCatalogjobswillfail."##echo'Pleaseset$HCAT_HOMEtotherootofyourHCataloginstallation.'##fi##if[!-d"${ACCUMULO_HOME}"];then##echo"Warning:$ACCUMULO_HOMEdoesnotexist!Accumuloimportswillfail."##echo'Pleaseset$ACCUMULO_HOMEtotherootofyourAccumuloinstallation.'##fi##if[!-d"${ZOOKEEPER_HOME}"];then##echo"Warning:$ZOOKEEPER_HOMEdoesnotexist!Accumuloimportswillfail."##echo'Pleaseset$ZOOKEEPER_HOMEtotherootofyourZookeeperinstallation.'##fi2.Sqoop软件的下载、安装、配置点击添加文本点击添加文本点击添加文本点击添加文本任务1Sqoop将数据从Mysql导入到HDFS测试Sqoop安装是否成功。若安装成功，输入sqoopversion命令之后，系统会提示Sqoop版本相关信息。

运行命令：sqoopversion

系统提示Sqoop如下相关信息：[root@hadoopconf]#sqoopversion20/05/2119:11:02INFOsqoop.Sqoop:RunningSqoopversion:1.4.7Sqoop1.4.7gitcommitid2328971411f57f0cb683dfb79d19d4d19d185dd8CompiledbymauglionThuDec2115:59:58STD20172.Sqoop软件的下载、安装、配置点击添加文本点击添加文本点击添加文本点击添加文本任务1Sqoop将数据从Mysql导入到HDFS3.关系数据库常用的数据操作语言DDLMysql创建数据库`school`的命令。createdatabase`school`DEFAULTCHARACTERSETutf8COLLATEutf8_general_ci;Mysql在数据库`school`中创建数据表`student`的命令。CREATETABLE`student`(`id`int(32)NOTNULLAUTO_INCREMENT,`userName`varchar(32)CHARACTERSETutf8COLLATEutf8_general_ciNOTNULL,`passWord`varchar(50)CHARACTERSETutf8COLLATEutf8_general_ciNOTNULL,`realName`varchar(32)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULL,PRIMARYKEY(`id`)USINGBTREE)Mysql查询表`student`中所有数据的命令select*from`student`

点击添加文本点击添加文本点击添加文本点击添加文本任务1Sqoop将数据从Mysql导入到HDFS4.sqoop常用的命令及功能列表Sqoop常用命令及功能列表命令功能import将数据导入到集群export将集群数据导出codegen生成与数据库记录交互的代码create-hive-table创建Hive表eval查看SQL执行结果import-all-tables导人某个数据库下所有表到HDFS中job用来生成一个joblist-databases列出所有的数据库名list-tables列出某个数据库下所有的表merge将HDFS中不同目录下的数据合在一起,并存放在指定的目录中metastore记录sqoopjob的元数据信息,如果不启动Metastore实例,则默认的元数据存储目录为:～/.sqoophelp打印Sqoop帮助信息version打印Sqoop版本信息点击添加文本点击添加文本点击添加文本点击添加文本任务1Sqoop将数据从Mysql导入到HDFS5.Sqoopimport命令常用的参数列表Sqoopimport命令常用的参数列表选项含义说明–append

将数据追加到HDFS上一个已存在的数据集上–as-avrodatafile

将数据导入到Avro数据文件–as-sequencefile

将数据导入到SequenceFile–as-textfile

将数据导入到普通文本文件（默认）–boundary-query边界查询，用于创建分片（InputSplit）–columns<col,col,col…>从表中导出指定的一组列的数据–delete-target-dir

如果指定目录存在，则先删除掉–direct

使用直接导入模式（优化导入速度）–direct-split-size

分割输入stream的字节大小（在直接导入模式下）–fetch-size从数据库中批量读取记录数–inline-lob-limit

设置内联的LOB对象的大小-m,–num-mappers

使用n个map任务并行导入数据-e,–query

导入的查询语句–split-by

指定按照哪个列去分割数据–table

导入的源表表名–target-dir导入HDFS的目标路径–warehouse-dir

HDFS存放表的根路径–where

指定导出时所使用的查询条件-z,–compress启用压缩–compression-codec指定Hadoop的codec方式（默认gzip）–null-string

果指定列为字符串类型，使用指定字符串替换值为null的该类列的值–null-non-string<null-string如果指定列为非字符串类型，使用指定字符串替换值为null的该类列的值点击添加文本点击添加文本点击添加文本点击添加文本任务1Sqoop将数据从Mysql导入到HDFS任务实施1.在Mysql中创建数据库`school`。连接Mysql数据库

运行命令：mysql-uroot-p，输入正确的密码，连接Mysql，运行结果如下图所示：点击添加文本点击添加文本点击添加文本点击添加文本任务1Sqoop将数据从Mysql导入到HDFS任务实施在Mysql中创建数据库`school`。

运行命令：dropdatabaseifexists`school`;createdatabase`school`DEFAULTCHARACTERSETutf8COLLATEutf8_general_ci;showdatabase;

成功创建‘school’数据库之后，用showdatabases命令查看当前所有的数据库，会显示出‘school’数据库，运行结果如下图所示：点击添加文本点击添加文本点击添加文本点击添加文本任务1Sqoop将数据从Mysql导入到HDFS任务实施2.在Mysql数据库`school`中创建数据表`student`;，并写入数据。在Mysql中选中`school`数据库为当前使用的数据库

运行命令：useschool；,运行结果如下图所示：在数据库`school`中创建数据表`student`，并写入数据：

运行命令：SETNAMESutf8mb4;SETFOREIGN_KEY_CHECKS=0;--------------------------------Tablestructureforuser------------------------------DROPTABLEIFEXISTS`student`;CREATETABLE`student`(`id`int(32)NOTNULLAUTO_INCREMENT,`userName`varchar(32)CHARACTERSETutf8COLLATEutf8_general_ciNOTNULL,`passWord`varchar(50)CHARACTERSETutf8COLLATEutf8_general_ciNOTNULL,点击添加文本点击添加文本点击添加文本点击添加文本任务1Sqoop将数据从Mysql导入到HDFS任务实施2.在Mysql数据库`school`中创建数据表`student`;，并写入数据。`realName`varchar(32)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULL,PRIMARYKEY(`id`)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=12CHARACTERSET=utf8COLLATE=utf8_general_ciROW_FORMAT=Compact;--------------------------------Recordsofuser------------------------------INSERTINTO`user`VALUES(1,'huhu','123456','张明');INSERTINTO`user`VALUES(3,'haitang','123456','刘庆');INSERTINTO`user`VALUES(4,'anan','123456','李丽');INSERTINTO`user`VALUES(5,'wendy','123456','李海');INSERTINTO`user`VALUES(6,'Avenue','123456','胡明');INSERTINTO`user`VALUES(7,'huhu1','123456','张明明');INSERTINTO`user`VALUES(8,'haitang1','123456','刘庆清');INSERTINTO`user`VALUES(9,'anan1','123456','李文');INSERTINTO`user`VALUES(10,'wendy1','123456','李东');INSERTINTO`user`VALUES(11,'Avenue1','123456','赵倩');SETFOREIGN_KEY_CHECKS=1;点击添加文本点击添加文本点击添加文本点击添加文本任务1Sqoop将数据从Mysql导入到HDFS任务实施2.在Mysql数据库`school`中创建数据表`student`;，并写入数据。

运行结果如下图所示：

点击添加文本点击添加文本点击添加文本点击添加文本任务1Sqoop将数据从Mysql导入到HDFS任务实施

2.在Mysql数据库`school`中创建数据表`student`;，并写入数据。使用Mysql查询命令查看`school`数据库中所创建的数据表`student`中的数据是否正确插入。

运行命令：select*fromstudent;，运行结果如下图所示：

从上图可以看出，`student`数据表中的数据和所插入的数据源完全一致。点击添加文本点击添加文本点击添加文本点击添加文本任务1Sqoop将数据从Mysql导入到HDFS任务实施3.拷贝mysql数据库连接驱动到Sqoop

拷贝mysql-connector-java-5.1.38.jar驱动到/sqoop/lib目录下：

运行命令：cp/opt/software/mysql-connector-java-5.1.38-bin.jar/sqoop/libchmod-R777/sqoop/lib/mysql-connector-java-5.1.38-bin.jar4.用sqoop列出数据库`school`下的所有数据表：

运行命令：sqooplist-tables--usernameroot--password123456--connectjdbc:mysql://localhost:3306/school，运行结果如下图所示：点击添加文本点击添加文本点击添加文本点击添加文本任务1Sqoop将数据从Mysql导入到HDFS任务实施5.用sqoop的import命令将数据库`school`下的所有数据表传输至HDFS的目

录/tmp/sqoop_data/student中。

运行命令：sqoopimport--usernameroot--password123456--connectjdbc:mysql://localhost:3306/school-tablestudent--target-dir/tmp/sqoop_data/student--fields-terminated-by','--split-byid-m1

运行结果如下图所示:点击添加文本点击添加文本点击添加文本点击添加文本任务1Sqoop将数据从Mysql导入到HDFS任务实施6.使用HDFS文件命令查看Hadoop文件系统/tmp/sqoop_data/student目录下的文件是否成功接收到Mysql传输的数据。

运行命令：hadoopfs-ls/tmp/sqoop_data/student，运行结果如下图所示：hadoopfs-cat/tmp/sqoop_data/student/part-m-00000，运行结果如下图所示：可以看到数据被正确的从Mysql的数据表中传输到HDFS指定的文件中。点击添加文本点击添加文本点击添加文本点击添加文本任务2Sqoop将数据从HDFS导出到Mysql

1.Sqoopexport命令常用的参数列表Sqoopexport命令常用的参数列表选项含义说明–validate启用数据副本验证功能，仅支持单表拷贝，

可以指定验证使用的实现类–validation-threshold指定验证门限所使用的类–export-dir导出过程中HDFS源路径-m,–num-mappers使用n个map任务并行导出–table导出的目的表名称–call导出数据调用的指定存储过程名–update-key更新参考的列名称，多个列名使用逗号分隔–update-mode指定更新策略，包括：updateonly（默认）、

allowinsert–input-null-string使用指定字符串，替换字符串类型值为null的列–input-null-non-string使用指定字符串，替换非字符串类型值为null的列–staging-table在数据导出到数据库之前，数据临时存放的表名称–clear-staging-table清除工作区中临时存放的数据–batch使用批量模式导出-fields-terminated-by字段之间的分隔符-columns要导出的列点击添加文本点击添加文本点击添加文本点击添加文本任务2Sqoop将数据从HDFS导出到Mysql1.在Mysql中选中数据库`school`为当前操作数据库。

运行命令：useschool;,运行结果如下图所示：2.在Mysql数据库`school`下创建新的数据表`student_from_hdfs`,为接收从HDFS传输过来的数据做准备；

运行命令：

SETNAMESutf8mb4;SETFOREIGN_KEY_CHECKS=0;--------------------------------Tablestructureforstudent_from_hdfs------------------------------DROPTABLEIFEXISTS`student_from_hdfs`;CREATETABLE`student_from_hdfs`(`id`int(32)NOTNULLAUTO_INCREMENT,`NickName`varchar(32)CHARACTERSETutf8COLLATEutf8_general_ciNOTNULL,任务实施点击添加文本点击添加文本点击添加文本点击添加文本任务2Sqoop将数据从HDFS导出到Mysql2.`passWord`varchar(50)CHARACTERSETutf8COLLATEutf8_general_ciNOTNULL,`realName`varchar(32)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULL,PRIMARYKEY(`id`)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=12CHARACTERSET=utf8COLLATE=utf8_general_ciROW_FORMAT=Compact;

运行结果，如下图所示：任务实施点击添加文本点击添加文本点击添加文本点击添加文本任务2Sqoop将数据从HDFS导出到Mysql3.查看数据库`school`中所有的表，检查创建的数据表`student_from_hdfs`是否成功创建，并查看数据表是否为空表。

运行命令：showtables;select*fromstudent_from_hdfs;

运行结果如下图所示：任务实施点击添加文本点击添加文本点击添加文本点击添加文本任务2Sqoop将数据从HDFS导出到Mysql4.使用Sqoopexport命令实现将数据从HDFS传输到Mysql中

运行命令：sqoopexport–connectjdbc:mysql://localhost:3306/school?characterEncoding=UTF-8--usernameroot--password123456--tablestudent_from_hdfs--export-dir/tmp/sqoop_data/student/part-m-00000--input-fields-terminated-by','--m1

运行结果，如下图所示：任务实施点击添加文本点击添加文本点击添加文本点击添加文本任务2Sqoop将数据从HDFS导出到Mysql5.查看数据库`school`下的数据表`student_from_hdfs`中是否正确导入HDFStmp/sqoop_data/student/part-m-00000文件中的数据。

运行命名：select*from`student_from_hdfs`;

运行结果如下图所示：HDFStmp/sqoop_data/student/part-m-00000文件中的数据被Sqoop正确传输到数据库`school`下的数据表`student_from_hdfs`中。任务实施点击添加文本点击添加文本点击添加文本点击添加文本能力拓展Sqoop将HDFS数据导出到Mysql数据表指定的列

用Sqoop实现将HDFStmp/sqoop_data/student/part-m-00000文件中内容导出到数据库`school`下的数据表`student_from_hdfs`的id,userName，passWord的三列中。1.在连接Mysql，选中数据库`school`为当前操作数据库,并清除表其下数据表`student_from_hdfs`中的数据。

运行命令：mysql-uroot-p123456useschool;deletefrom`student_from_hdfs`;select*from`student_from_hdfs`;

,运行结果如下图所示：点击添加文本点击添加文本点击添加文本点击添加文本能力拓展Sqoop将HDFS数据导出到Mysql数据表指定的列2.使用Sqoopexport命令实现将数据从HDFS传输到Mysql中

运行命令：sqoopexport–connectjdbc:mysql://localhost:3306/school?characterEncoding=UTF-8--usernameroot--password123456--tablestudent_from_hdfsdfs--columnsid,userName,password--export-dir/tmp/sqoop_data/student/part-m-00000--input-fields-terminated-by','--m1

运行结果，如下图所示：点击添加文本点击添加文本点击添加文本点击添加文本能力拓展Sqoop将HDFS数据导出到Mysql数据表指定的列3.查看数据库`school`下的数据表`student_from_hdfs`中是否正确导入HDFS

tmp/sqoop_data/student/part-m-00000文件中的数据。

运行命名：mysql-uroot-p123456useschool;select*from`student_from_hdfs`;

运行结果如下图所示：HDFS

tmp/sqoop_data/student/part-m-00000文件中的数据被Sqoop正确传输到数据库`school`下的数据表`student_from_hdfs`中的id、NickName、Password列。点击添加文本点击添加文本点击添加文本点击添加文本小结

Sqoop软件是一个在关系型数据库（Mysql、Oracle）和Hadoop之间进行批量数据传输的工具，Sqoop底层用MapReduce程序实现数据的抽取、转换、加载，MapReduce天生的特性保证了并行化和高容错率，MapReduce任务在Hadoop集群上运行，减少了对服务器资源的