大数据技术原理与操作应用 第9章 Sqoop 数据迁移

第9章Sqoop数据迁移1Sqoop基本概念Sqoop安装配置Sqoop常用的相关命令使用Sqoop进行导入导出2✎学习目标了解掌握掌握熟悉了解Sqoop基本概念12掌握Sqoop安装配置4掌握使用Sqoop进行导入导出3熟悉Sqoop常用的相关命令章节概要越来越多的企业使用Hadoop作为处理大数据的分布式平台,但许多企业仍有大量的数据存储在关系型数据库中,需要频繁地将数据集在Hadoop和传统数据库之间转移,这并非易事。因此,一款能够帮助数据进行传输的工具就变得尤为重要。ApacheSqoop就是这样的一款工具,可以在Hadoop和传统关系型数据库之间高效地转移大量数据。

9.1 Sqoop介绍9.1.1 Sqoop概述ApacheSqoop(SQLtoHadoop)是Apache的一个顶级开源项目,主要用于有效地在传统关系型数据库和Hadoop之间进行大量的数据传递。Sqoop可以轻松地将关系型数据库(如MySQL、Oracle等)中的数据导入Hadoop的HDFS或其他相关系统(如Hive和HBase)中,也可方便地将Hadoop或其他相关系统中的数据抽取出来并导出到关系型数据库。Sqoop是关系型数据库与Hadoop之间的数据传递工具,其工作流程如图9.1所示。关系型数据库是有类型的,Sqoop可以自动地根据数据库中的类型将数据转换到Hadoop中,实现数据映射和转换的自动完成。Sqoop支持MySQL、Oracle等多种数据库与Hadoop之间传递数据,并且能高效、可控地利用资源,可以通过调整任务数来控制任务的并发度。

9.1 Sqoop介绍9.1.2 Sqoop导入导出工作机制Sqoop的底层工作机制是

MapReduce任务,也就是将Sqoop导入或导出命令翻译成MapReduce作业来实现。Sqoop工具接收到用户提交的Shell命令或“JavaApi”命令后,通过Sqoop中的任务翻译器将命令转换为一个只有Map任务的MapReduceJob。这个Job会访问数据库的元数据信息,通过并行的Map任务将关系型数据库表中的数据一行一行地读取出来,然后写入Hadoop中。也可以将Hadoop中的数据导出到关系型数据库。Sqoop的架构如图9.2所示。9.2Sqoop架构图

9.1 Sqoop介绍9.1.2 Sqoop导入导出工作机制Sqoop导入(SqoopImport)就是从关系型数据库中提取出数据并导入HDFS,操作流程如图9.3所示。图9.3

Sqoop导入流程(1)Sqoop导入机制

9.1 Sqoop介绍9.1.2 Sqoop导入导出工作机制在导入的过程中,Sqoop会将输入命令转化为基于Map任务的MapReduce作业,而MapReduce作业会通过InputFormat以JDBC的方式从关系型数据库中读取数据。多个Map任务并发执行,将查询到的数据复制到HDFS上。(1)Sqoop导入机制

9.1 Sqoop介绍9.1.2 Sqoop导入导出工作机制Sqoop导出(SqoopExport)通常是将数据从HDFS导出到关系型数据库中,其操作流程如图9.4所示。(2)Sqoop导出机制图9.4Sqoop导出流程

9.1 Sqoop介绍9.1.2 Sqoop导入导出工作机制在进行数据导出前,需要先在关系型数据库中创建好表。SqoopExport通常选择JDBC完成数据导出。首先Sqoop会获取目标表的元数据;然后根据目标表的结构生成一个Java类,这个类用于从导出的文本文件中解析记录,并将合适的值插入到目标表中。Sqoop根据输入命令,启动一个基于Map任务的MapReduce作业。多个Map任务并行执行,从HDFS中读取数据,使用生成的类解析记录,并使用JDBC方法将数据插入目标表中。(2)Sqoop导出机制

9.1 Sqoop介绍9.1.3Sqoop安装配置下载Sqoop安装包sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz，将其上传到master节点的/home/apache/package目录中，然后在该目录中执行命令将安装包解压到/home/apache/soft目录，并重命名为sqoop-1.4.6。(1)下载并解压Sqoop[apache@masterpackage]$tar-zvxfsqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz-C/home/apache/soft[apache@masterpackage]$mvsqoop-1.4.6.bin__hadoop-2.0.4-alphasqoop-1.4.6

9.1 Sqoop介绍9.1.3Sqoop安装配置默认情况下，并不存在sqoop-env.sh文件，需要先创建一个。进入Sqoop安装目录的conf目录，将sqoop-env-template.sh重命名为sqoop-env.sh：mvsqoop-env-template.shsqoop-env.sh。修改sqoop-env.sh的内容，设置Hadoop和Hive的安装路径。HADOOP_COMMON_HOME是Hadoop的环境信息，HADOOP_MAPRED_HOME用于配置Hadoop的MapReduce存放目录，这两者必须有。因为本章需要使用Hive数据仓库，所以还需要配置Hive的路径。如果需要用到HBase，则还要添加HBase的路径。Sqoop有内置的zookeeper，可以直接使用，不用再进行配置。修改如下：(2)修改sqoop-env.sh文件#Setpathtowherebin/hadoopisavailableexportHADOOP_COMMON_HOME=/home/apache/soft/hadoop-2.7.7#Setpathtowherehadoop-*-core.jarisavailableexportHADOOP_MAPRED_HOME=/home/apache/soft/hadoop-2.7.8#Setthepathtowherebin/hiveisavailableexportHIVE_HOME=/home/apache/soft/hive-1.2.2

9.1 Sqoop介绍9.1.3Sqoop安装配置将bin/configure-sqoop文件中关于HCAT_HOME、ACCUMULO_HOME和ZOOKEEPER_HOME的检查注释掉。如图9.5所示。(3)修改bin/configure-sqoop文件图9.5修改configure-sqoop文件

9.1 Sqoop介绍9.1.3Sqoop安装配置Sqoop是Hadoop和传统关系型数据库之间进行数据传递的桥梁,使用JDBC来访问关系型数据库,需要添加数据库驱动包。本章选择的是MySQL数据库,将“mysql-connector-java-5.1.38.jar”包拷贝到Sqoop的lib目录下。(4)添加数据库驱动包

9.1 Sqoop介绍9.1.3Sqoop安装配置在Sqoop安装目录的bin目录下,输入version命令可查看Sqoop的版本,如图9.6所示。(5)进行测试图9.6Sqoopversion命令

9.1 Sqoop介绍9.1.3Sqoop安装配置连接MySQL测试。Sqoop通过jdbc连接MySQL，输出数据库信息，命令为：bin/sqooplist-databases--connectjdbc:mysql://master:3306/--usernamehive--passwordHive@123，输出结果如图9.7所示。(5)进行测试图9.7Sqoop连接MySQL测试

9.1 Sqoop介绍9.1.4 Sqoop常用命令对于不同的命令,有不同的参数,而有些公用参数是大多数命令都支持的,表9.2列举了用于数据库连接的公用参数,表9.3列举了import命令的公用参数,表9.4列举了export命令的公用参数,表9.5列举了Hive相关的公用参数。表9.2

数据库连接的公用参数参数描述

-connect连接关系型数据库的url-connection-manager指定要使用的连接管理类-driver驱动器-username连接数据库的用户名-password连接数据库的密码-help打印帮助信息-verbose在控制台打印出详细信息

9.1 Sqoop介绍9.1.4 Sqoop常用命令表9.3import命令的公用参数参数描述

-fields-terminated-bychar设定字段分隔符,默认为逗号-lines-terminated-bychar设定每行记录之间的分隔符,默认是“\n”-enclosed-bychar给字段值前加上指定的字符-escaped-bychar对字段中的双引号加转义符-mysql-delimiters设置MySQL默认的分隔符,字段之间以逗号分隔,行之间以“\n”分隔,默认转义符是“\”,字段值使用单引号

-optionally-enclosed-bychar给带有双引号或单引号字段值前后加上指定字符

9.1 Sqoop介绍9.1.4 Sqoop常用命令表9.4export命令的公用参数参数描述

-input-fields-terminated-bychar定义字段之间的分隔符-input-lines-terminated-bychar定义行之间的分隔符-input-enclosed-bychar给字段值前后加上指定字符-input-escaped-bychar对含有转义符的字段做转义处理-input-optionally-enclosed-bychar给带有双引号或单引号的字段前后加上指定字符

9.1 Sqoop介绍9.1.4 Sqoop常用命令表9.5Hive相关的公用参数参数描述

-hive-import将数据从关系型数据库中导入Hive表中-hive-overwrite覆盖Hive表中已经存在的数据-create-hive-table创建Hive表,如果目标表存在,则创建失败-hive-table要创建的Hive表名-table指定关系型数据库的表名-map-column-hivemap生成Hive表时,可以更改生成字段的数据类型-hive-partition-key创建分区,后面跟分区名称,分区字段的默认类型是string-hive-partition-valuev导入数据时,指定某个分区的值

9.1 Sqoop介绍9.1.4 Sqoop常用命令如果向Hive中导入数据,那么当Hive中没有对应表时会进行自动创建。如果进行增量导入数据到Hive,就需要设定“mode=append”或“mode=lastmodified”。append模式主要针对insert新增数据的增量导入,lastmodified模式主要针对update修改数据的增量导入。如果将查询结果导入Hive,就需要使用参数“-query”。使用时必须伴随参数“-target-dir、-hive-table”,如果查询中有where条件,则条件后必须加上“﹩CONDITIONS”关键字。

9.2 Sqoop数据导入9.2.1MySQL表数据导入HDFS下载MySQL的Windows安装包“mysql-es-sential-5.1.51-win32.msi”,双击进行安装,注意字符集选择“utf8”,然后安装“navicat8_mysql_cs.exe”。2) 打开navicat,建立localhost连接,如图9.8所示。单击“连接测试”,弹出“连接成功”对话框,表示连接没有问题。

(1)创建MySQL表图9.8建立localhost连接

9.2 Sqoop数据导入9.2.1MySQL表数据导入HDFS

(1)创建MySQL表图9.9创建sqoop_test数据库3)创建数据库。在localhost连接上右击鼠标,选择“创建数据库”。输入数据库名“sqoop_test”,选择字符集“utf8--UTF-8Unicode”,如图9.9所示。

9.2 Sqoop数据导入9.2.1MySQL表数据导入HDFS

(1)创建MySQL表图9.10创建MySQL表mysql_employee4)创建表。双击数据库sqoop_test，在“表”上面点鼠标右键，创建表。设置属性id、name、sex、age、department，如图9.10所示。其中id设置为主键并且自动递增，varchar类型的属性的字符集都选择utf-8。保存并键入表名mysql_employee。

9.2 Sqoop数据导入9.2.1MySQL表数据导入HDFS(2)MySQL表数据导入HDFS在 HDFS中创建目录“/sqoop”,用于存放从MySQL导入的数据,命令为“hdfsdfs-mkdir/sqoop”。打开master节点终端,切换到Sqoop的安装目录,在bin目录下执行SqoopImport命令将表“mysql_employee”的数据导入HDFS。执行命令为:[apache@mastersqoop-1.4.6]$bin/sqoopimport--connect'jdbc:mysql://172.20.10.2/sqoop_test?useUnicode=true&characterEncoding=utf-8'--usernameroot--passwordroot--tablemysql_employee--target-dir/sqoop/mysql_employee--m2--fields-terminated-by'\t'--lines-terminated-by'\n'

9.2 Sqoop数据导入9.2.1MySQL表数据导入HDFS(2)MySQL表数据导入HDFS在 HDFS中创建目录“/sqoop”,用于存放从MySQL导入的数据,命令为“hdfsdfs-mkdir/sqoop”。打开master节点终端,切换到Sqoop的安装目录,在bin目录下执行SqoopImport命令将表“mysql_employee”的数据导入HDFS。执行命令为:[apache@mastersqoop-1.4.6]$bin/sqoopimport--connect'jdbc:mysql://172.20.10.2/sqoop_test?useUnicode=true&characterEncoding=utf-8'--usernameroot--passwordroot--tablemysql_employee--target-dir/sqoop/mysql_employee--m2--fields-terminated-by'\t'--lines-terminated-by'\n'

9.2 Sqoop数据导入9.2.1MySQL表数据导入HDFS(2)MySQL表数据导入HDFS--connect:后面是连接MySQL的url。这里使用JDBC连接Windows系统的MySQL数据库sqoop_test,所以,IP地址为Windows系统的IP地址。--usernameroot:连接MySQL的用户名为“root”。--passwordroot:连接MySQL的密码为“root”。--tablemysql_employee:指定MySQL的表为mysql_employee。--target-dir/sqoop/mysql_employee:指定表数据导入HDFS的“/sqoop/mysql_employee”目录。--m2:设置Map任务的并行度为“2”。--fields-terminated-by'\t':设置导入数据在HDFS文件中的字段分隔符为“\t”。

9.2 Sqoop数据导入9.2.1MySQL表数据导入HDFS

(3)查看导入结果使用HDFS命令查看“/sqoop/mysql_employee”目录,里面的两个文件“part-m-00000”和“part-m-00001”就是导入后生成的文件。因为设置了Map的并行度为“2”,所以生成了两个HDFS文件。可以进一步查看这两个文件的内容,其结果如图9.12所示。图9.12MySQL表数据导入HDFS结果查看

9.2 Sqoop数据导入9.2.2MySQL表数据导入Hive[apache@mastersqoop-1.4.6]$bin/sqoopimport\>--connect'jdbc:mysql://172.20.10.2/sqoop_test?useUnicode=true&characterEncoding=utf-8'\>--usernameroot\>--passwordroot\>--tablemysql_employee\>--hive-import\>--hive-overwrite\>--hive-databaseapachedb\>--create-hive-table\>--hive-tablemysql_employee2\>--m2\>--fields-terminated-by','\>--lines-terminated-by'\n'

9.2 Sqoop数据导入9.2.1MySQL表数据导入HDFS

(3)查看导入结果通过HDFSWeb查看Hive数据表文件,如图9.13所示,在“/user/hive/warehouse/apachedb/”目录下新增了一个“mysql_employee2”目录,里面包括了两个文件,这个就是导入的Hive表数据文件。图9.13导入的Hive表数据文件图9.14将MySQL表数据导入到Hive中

9.3Sqoop数据导出9.3.1 HDFS数据导出到MySQL(1)创建MySQL表在MySQL的sqoop_test数据库中,创建表“employee_out”,结构与“mysql_employee”一样,也可以在navicat中直接通过复制“mysql_employee”的方式创建新表。确保“employee_out”表中没有数据。

9.3Sqoop数据导出9.3.1 HDFS数据导出到MySQL(2)将HDFS数据导出到MySQL[apache@mastersqoop-1.4.6]$bin/sqoopexport\>--connect'jdbc:mysql://172.20.10.2/sqoop_test?useUnicode=true&characterEncoding=utf-8'\>--usernameroot\>--passwordroot\>--tableemployee_out\>--export-dir/sqoop/mysql_employee\>--input-fields-terminated-by'\t'\>--input-lines-terminated-by'\n'\>--m2--export-dir/sqoop/mysql_employee：指定待导出的HDFS数据文件的目录。--input-fields-terminated-by'\t'：指定HDFS文件的字段分隔符为“\t”。--input-lines-terminated-by'\n'：指定HDFS文件的行分隔符为“\n”。

9.3Sqoop数据导出9.3.1 HDFS数据导出到MySQL(3)查看导出结果

9.3Sqoop数据导出9.3.2 Hive数据导出到MySQL(1)创建MySQL