大数据存储-数据传输工具Sqoop

数据传输工具——Sqoop目前很多使用Hadoop技术的企业，仍然有大量的数据存储在传统关系型数据库中。在开发的过程中总是需要将数据从传统关系型数据库传输到Hadoop中，顺利的传输过程能够确保数据成功存储。因此数据传输工具的选择是一个非常重要的环节。项目描述项目背景某网站的数据库中储存了约30万条用户日志数据，已被导出为CSV格式的文件（law_visit_log_all.csv），文件记录的是用户在网站上的浏览行为信息，包括用户IP、浏览时间、浏览网址类型等信息，具体的字段说明如表所示。现通过对用户日志数据进行处理，根据浏览器类型进行用户信息分类，实现用户群分，以便研究不同用户群的兴趣特征。筛选出浏览器类型为“iOS”的用户信息保存至Hive中的表new_law中。项目描述项目背景属性名称属性说明realIP真实IPrealAreacode地区编号userOS用户浏览器类型fullURL网址现寻求一个方式或工具实现传统关系型数据库与Hadoop之间的数据传输，而Sqoop就是为了满足这一需求而诞生的。本项目将介绍Sqoop的概念、安装配置和基本使用，结合用户日志数据实例，使用Sqoop实现在Hadoop和关系型数据库之间进行高效的数据传输，先将CSV格式的用户日志数据导入MySQL，再将MySQL数据库中的数据增量导入Hive，最后将Hive中的用户日志信息筛选结果导出至MySQL中。项目描述项目目标学习Sqoop的工作流程、核心理念和集群搭建过程，根据网站的用户日志数据存储需求安装配置Sqoop集群。将用户日志数据保存至MySQL数据库中。学习Sqoopeval基本操作，在Sqoop中通过sqoopeval命令对MySQL中的用户日志数据记录数进行查询。学习Sqoopimport基本操作，使用Sqoop将MySQL中的用户日志数据增量导入至Hive中。学习Sqoopexport基本操作，在Hive中筛选出浏览器类型为“iOS”的用户信息并保存为new_law表，使用Sqoop将Hive中的new_law表数据传输至MySQL数据库中。项目描述项目分析1

查询MySQL用户日志数据表记录数

目录认识Sqoop2增量导入MySQL用户日志数据至Hive3导出Hive中的筛选结果到MySQL4Sqoop是一种用于在Hadoop和关系数据库之间进行高效传输数据的工具，它的出现使得Hadoop和关系数据库之间的数据传输变得非常方便高效。本任务将详细介绍Sqoop工具的基础知识，包括内容如下。基础概念安装配置过程一些基本的传输命令任务描述Sqoop是一个用于在Hadoop和传统关系型数据库（MySQL、Oracle、Postgres等）之间传输数据的工具。Sqoop的工作流程如图所示，使用Sqoopimport命令可以将传统关系型数据库中的数据导入Hadoop中（如HDFS、Hive、HBase），而使用Sqoopexport命令可以将集群中的数据导出至关系型数据库。了解SqoopSqoop的核心理念包括如下内容。将数据导入或导出的命令转换成MapReduce程序。转换成的MapReduce程序会通过自定义InputFormat（数据输入格式）和OutputFormat（数据输出格式）实现数据的导入导出。通过运行MapReduce程序实现Hadoop和关系型数据库中的数据相互转移。了解SqoopSqoop在Linux中的安装步骤如下。在虚拟机上创建Sqoop安装目录，将压缩包解压缩至Sqoop安装目录。Sqoop的配置文件存放在Sqoop根目录下的conf目录中。将配置文件sqoop-env-template.sh重命名为sqoop-env.sh。修改配置文件sqoop-env.sh。配置环境变量，打开/etc/profile文件，配置完成后运行“source/etc/profile”命令使配置的环境变量生效。进入MySQL官网的MySQL连接器下载页面下载mysql-connector-java-5.1.42-bin.jar的MySQL依赖包，并将依赖包移动到Sqoop安装目录的lib文件夹下。解决Sqoop依赖，将安装目录下的sqoop-1.4.6.jar复制到Hadoop安装目录下的/share/Hadoop/yarn/目录。安装配置Sqoop测试是否安装成功。执行命令“sqoopversion”，若出现如图所示结果，说明安装成功。安装配置SqoopSqoop提供了大量的传输命令以满足HDFS、Hive、HBase和关系型数据库中数据导入导出的需求。Sqoop的基本传输命令，如表所示。了解Sqoop基本传输命令传输命令解释help返回Sqoop帮助信息，包括传输命令的详情version返回Sqoop的版本信息list-databases列出所有数据库名list-tables列出某个数据库下所有的表import-all-tables导入某个数据库下所有的表到HDFS中import将数据导入Hadoop集群export将Hadoop集群的数据导出关系型数据库中eval使用SQL语句对关系型数据库进行操作job用于生成一个Sqoop的任务。生成后，该任务并不执行，除非使用命令执行该任务merge将HDFS中不同目录下面的数据合在一起，并存放在指定的目录中使用Sqoop时，不仅需要提供传输命令，还要提供传输命令对应的参数才真正构成一个完整的Sqoop命令。Sqoop大多数命令都支持的公共参数设置，如表所示。了解Sqoop基本传输命令参数解释--connect连接关系型数据库的URL--connection-manager指定要使用的连接管理类--username连接数据库的用户名--password连接数据库的密码--table指定关系数据库的表名--fields-terminated-by<char>设定每个字段以什么符号作为结束，默认为逗号--lines-terminated-by<char>设定每行记录之间的分隔符，默认是“\n”--verbose在控制台打印出详细信息以list-databases命令为例，列出MySQL中所有数据库名，基本操作步骤如下。指定连接MySQL数据库的URL。连接数据库的用户名及密码。运行查看结果，如图所示。了解Sqoop基本传输命令1

查询MySQL用户日志数据表记录数

目录认识Sqoop2增量MySQL导入用户日志数据至Hive3导出Hive中的筛选结果到MySQL4在开发过程中，常常需要先查询关系数据库中将要被导入的数据，以确保数据的正确性，而来回切换的过程相当麻烦。Sqoop提供了eval命令解决这一问题，Sqoopeval命令允许用户使用SQL语句对关系型数据库进行简单的SQL查询操作。本任务将实现使用eval命令查询MySQL用户日志数据表的记录数。任务描述eval命令允许用户使用SQL语句对关系型数据库进行查询操作，并将结果输出到控制台。eval命令参数如下表所示。下面以使用eval命令查询student数据表的数据为例，介绍eval命令的基础用法。

在test数据库创建student数据表，并插入数据。调用eval命令，利用“--query”参数查询student表的数据。查看Hive数据表的结构和数据，结果如右图所示。掌握Sqoopeval基本操作参数解释--e\--query<SQLstatement>运行简单的SQL查询语句根据law_visit_log_all.csv表的数据字段说明，具体的实现步骤如下。在MySQL数据库中创建存储用户日志数据表law_log，并将law_visit_log_all.csv文件数据导入law_log表中。使用eval命令查询MySQL用户日志数据表的记录数。最后得到用户日志数据表记录数为295349，完成结果如图所示。任务实现1

查询MySQL用户日志数据表记录数

目录认识Sqoop2增量导入MySQL用户日志数据至Hive3导出Hive中的筛选结果到MySQL4Sqoop核心功能是在Hadoop集群与传统的关系型数据库之间传输数据，因此，导入数据无疑是至关重要的一部分。本任务将使用Sqoopimport命令将MySQL数据库中的用户日志数据增量导入Hive中。任务描述Sqoopimport命令可将关系型数据库中的数据导入Hadoop集群中，包括HDFS、Hive、Hbase。import命令可通过设置特定的参数满足各种数据导入需求，其参数设置如表所示。掌握Sqoopimport基本操作参数解释--append将数据追加到HDFS中已经存在的DataSet中，如果使用该参数，Sqoop会将数据先导入临时文件目录，再合并--as-avrodatafile将数据导入一个Avro数据文件中--as-sequencefile将数据导入一个sequence文件中--as-textfile将数据导入一个普通文本文件中--columns<col1,col2,col3>指定要导入的字段--num-mappers<n>启动n个map来并行导入数据，默认4个--target-dir<dir>指定导入的HDFS路径续接上表。掌握Sqoopimport基本操作参数解释--incremental<mode>数据导入模式，mode可选append或lastmodified--null-string<null-string>string类型的列如果null，替换为指定字符串--null-non-string<null-string>非string类型的列如果null，替换为指定字符串--where从关系数据库导入数据时的查询条件--query导入查询SQL查询语句的结果，使用时必须伴随参--target-dir，--hive-table，如果查询中有where条件，则条件后必须加上$CONDITIONS关键字--check-column<col>作为增量导入的判断列名--last-value<value>指定某一个值，用于标记增量导入的位置以使用import命令将student表中“id”小于3的数据导入HDFS的/user/sqoop目录下为例，实现步骤如下。指定连接MySQL数据库的URL。连接数据库的用户名及密码。选择数据表student，设置保存目录为/user/sqoop。设置判断条件，筛选“id”小于3的数据。运行程序结束后，在HDFS的web端口中查看从MySQL数据库导入的数据，如图所示。了解Sqoop基本传输命令在现实生产环境中，系统会定期从与业务相关的关系型数据库将数据导入至Hadoop集群中，这时可以选择增量导入数据。增量导入需要提供一个字段作为增量导入的标识，即需要调用“--check-column”参数。以增量导入student数据表的数据到HDFS为例，增量导入的操作从“id”等于3的数据开始。程序运行结束，增量导入的操作会新建一个文件存放数据，不会与之前导入的数据合并，结果如图所示。了解Sqoop基本传输命令Sqoopjob允许用户创建并使用已保存的Sqoop任务（job）。已创建保存的job并不执行，除非使用参数执行该任务。若已创建保存的job被配置为执行增量导入，则只需在已保存的job中更新关于增量导入的行的状态，设置job只增量导入最新的行。job命令参数设置如表所示。掌握Sqoopjob基本操作参数解释--create<job-name>创建一个job，创建完成后会自动保存job--delete<job-name>删除一个job--exec<job-name>执行一个job--list显示job列表--show<job-name>显示一个job的信息以创建并运行一个list-databases命令的job为例，介绍job的基础用法，实现步骤如下。创建项目myjob。指定连接MySQL数据库的URL。连接数据库的用户名。运行程序，结果如图所示。掌握Sqoopimport基本操作执行job文件，实现免密操作步骤如下。调用“--password-file”参数。在HDFS创建/user/password目录。密码保存在一个文件mysql.pwd中，并将该文件上传至HDFS的/user/password目录下。将/user/password/mysql.pwd文件的权限设置为只读。修改Sqoop安装目录下的conf文件夹中的sqoop-site.xml文件内容。最后使用Sqoopjob命令，调用“--password-file”参数声明密码文件的路径，创建免手动输入密码的job。掌握Sqoopimport基本操作Sqoop支持使用import传输命令设置特殊的参数，实现直接将MySQL中的数据导入Hive。相关参数设置如表所示。使用Sqoop从MySQL导入数据至Hive参数解释--hive-import将数据从关系数据库中导入Hive表--hive-overwrite覆盖掉在Hive表中已经存在的数据--hive-table后面接要创建的Hive表，默认使用MySQL的表名--table指定关系数据库的表名--hive-partition-value<v>导入数据时，指定某个分区的值--hive-drop-import-delims在导入数据至Hive时，去掉数据中的\r、\n、\013、\010这样的字符--hive-delims-replacement<val>用自定义的字符串替换掉数据中的\r、\n和\013、\010等字符以导入MySQL数据库的student表数据到Hive为例，使用Sqoopimport命令将数据导入Hive中。实现步骤如下。在Hive中创建一个表结构与MySQL的student表结构一致的表。使用Sqoopimport命令，并调用对应的参数将student表的数据导入Hive中。查看Hive数据表的结构和数据，表结构与数据应与MySQL中的一致。结果如图所示。

掌握Sqoopimport基本操作用户日志数据是一份静态离线数据，实现将用户日志数据增量导入Hive中，将日志数据分成两部分导入。具体操作步骤如下。导入第一部分数据至Hive，导入“system”是“iOS”的数据至Hive中的表law_log导入“system”是“iOS”的数据至Hive中的表law_log。在Hive中查询导入的前5条数据，结果如图所示。任务实现1

查询MySQL用户日志数据表记录数

目录认识Sqoop2增量导入MySQL用户日志数据至Hive3导出Hive中的筛选结果到MySQL4在开发过程中，经常需要在Hadoop集群中进行数据处理，再将处理之后的筛选结果保存到MySQL中，以便后续使用。本任务要求实现将存储在Hive中的筛选结果导出至MySQL。任务描述Sqoopexport命令可将Hadoop集群中的数据导出至关系型数据库中，export命令参数设置如表所示。掌握Sqoopexport基本操作参数解释--direct利用数据库自带的导入导出工具，以便于提高效率--export-dir<dir>存放数据的HDFS的源目录--table<table-name>指定导出至哪个RDBMS中的表--update-key<col-name>用于更新的锚列，当此列（或多列）的值相同时则判定为重复记录--columns<col1,col2,col3>指定要导出的字段--update-mode<mode>当插入的数据重复时，可选updateonly、allowinsert两个模式--staging-table<staging-table-name>创建一张临时表，用于存放所有事务的结果，然后将所有事务结果一次性导入目标表中，防止错误。--clear-staging-table如果staging-table参数非空，则可以在导出操作执行前，清空临时事务结果表Sqoopexport导出命令可以将数据导出至关系型数据库的数据表中，其本质是将Sqoopexport导出命令转换成INSERT的SQL语句，并将对应的数据记录插入到目标数据库表中。Sqoop提供了“--update-key”参数解决目标数据库表中已存在该条数据记录的问题。Sqoop提供了“--update-mode<mode>”参数设定插入数据的模式，支持updateonly和allowinsert两种模式。设置为updateonly模式，插入数据时，若遇到重复的数据，则会直接修改原来的数据。设置为al