Sqoop：连接MySQL和Hadoop的桥梁

来源：互联网发布：摄像头照相软件编辑：程序博客网时间：2024/05/18 01:43

1 Sqoop1简介

Apache Sqoop项目旨在协助RDBMS与Hadoop之间进行高效的大数据交流。用户可以在Sqoop的帮助下，轻松地把关系型数据库的数据导入到Hadoop与其相关的系统(如：HBase和Hive)中；同时也可以把数据从Hadoop系统里抽取并导出到关系型数据库里。除了这些主要的功能外，Sqoop也提供了一些诸如查看数据库表等实用的小工具。
Sqoop支持的数据库
理论上，Sqoop支持任何一款支持JDBC规范的数据库，如MySQL、DB2等。在使用Sqoop连接关系型数据库前，首先需要把相关的JDBC驱动拷贝到$SQOOP_HOME/lib文件夹下，然后在“connect”参数后指定好数据库连接的url，如:
--connect jdbc:mysql://localhost:3306/userdb。对于MySql数据库来说，Sqoop目前支持MySQL的绝大多数数据类型，而且Sqoop的大多数工具也能在MySQL上较好地运行。

2 Sqoop安装

JDK和Hadoop集群（或伪分布式）是必须的，Hive和Hbase可选。具体教程参见以下：

JDK安装
搭建Hadoop分布式集群
Hbase集群安装
Hive安装
CentOs6.5 x64安装MySQL数据库
Sqoop安装

本文使用的sqoop版本为：sqoop-1.4.4.bin__hadoop-2.0.4-alpha.tar.gz
1. 上传sqoop安装包并解压到/iwisdom目录下
tar -xvzf sqoop-1.4.4.bin__hadoop-2.0.4-alpha.tar.gz -C /iwisdom
2. 修改配置
cd /iwisdom/sqoop-1.4.4.bin__hadoop-2.0.4-alpha/conf mv sqoop-env.template.sh sqoop-env.sh vi sqoop-env.sh
编辑sqoop-env.sh设置以下变量的值

    #Set path to where bin/hadoop is available    export HADOOP_COMMON_HOME=/iwisdom/hadoop-2.5.2    #Set path to where hadoop-*-core.jar is available    export HADOOP_MAPRED_HOME=/iwisdom/hadoop-2.5.2    #set the path to where bin/hbase is available    export HBASE_HOME=/iwisdom/hbase-0.96.2-hadoop2    #Set the path to where bin/hive is available    export HIVE_HOME=/iwisdom/hive-0.12.0-bin    #Set the path for where zookeper config dir is    export ZOOCFGDIR=/iwisdom/zookeeper-3.4.5/conf

在添加sqoop到环境变量

    vi /etc/profile

在文件的结尾追加如下内容：

    export SQOOP_HOME=/iwisdom/sqoop-1.4.4.bin__hadoop-2.0.4-alpha    export PATH=$PATH:$SQOOP_HOME/bin

更新配置文件，使其生效：

    source /etc/profile

验证Sqoop安装：

    sqoop-version

输出：

    Sqoop 1.4.4    git commit id 050a2015514533bc25f3134a33401470ee9353ad

表示安装成功。

3 Sqoop使用

3.1 准备数据

创建MySQL表

数据库userdb包含表的列表如下:

Tables emp emp_add emp_contact

表emp

id name deg salary dept 1201 gopal manager 50,000 TP 1202 manisha Proof reader 50,000 TP 1203 khalil php dev 30,000 AC 1204 prasanth php dev 30,000 AC 1204 kranthi admin 20,000 TP

SQL语句：

    insert into emp(id,name,deg,salary,dept) values (1201,'gopal','manager','50,000','TP');    insert into emp(id,name,deg,salary,dept) values(1202,'manisha','Proof reader','50,000','TP');    insert into emp(id,name,deg,salary,dept) values(1203,'khalil','php dev','30,000','AC');    insert into emp(id,name,deg,salary,dept) values(1204,'prasanth','php dev','30,000','AC');    insert into emp(id,name,deg,salary,dept) values(1205,'kranthi','admin','20,000','TP');

表emp_add

id hno street city 1201 288A vgiri jublee 1202 108I aoc sec-bad 1203 144Z pgutta hyd 1204 78B old city sec-bad 1205 720X hitec sec-bad

SQL语句：

    insert into emp_add(id,hno,street,city) values(1201,'288A','vgiri','jublee');    insert into emp_add(id,hno,street,city) values(1202,'108I','aoc','sec-bad');    insert into emp_add(id,hno,street,city) values(1203,'144Z','pgutta','hyd');    insert into emp_add(id,hno,street,city) values(1204,'78B','old city','sec-bad');    insert into emp_add(id,hno,street,city) values(1205 ,'720X','hitec','sec-bad');

表emp_contact

id phno email 1201 2356742 gopal@tp.com 1202 1661663 manisha@tp.com 1203 8887776 khalil@ac.com 1204 9988774 prasanth@ac.com 1205 1231231 kranthi@tp.com

SQL语句：

    insert into emp_contact(id,phno,email) values(1201,'2356742','gopal@tp.com');    insert into emp_contact(id,phno,email) values(1202,'1661663','manisha@tp.com');    insert into emp_contact(id,phno,email) values(1203,'8887776','khalil@ac.com');    insert into emp_contact(id,phno,email) values(1204,'9988774','prasanth@ac.com');    insert into emp_contact(id,phno,email) values(1205,'1231231','kranthi@tp.com');

上传HDFS文件
在HDFS文件/userinfo目录的emp_data文件中保存员工数据，如下:
1201, gopal, manager, 50000, TP
1202, manisha, preader, 50000, TP
1203, kalil, php dev, 30000, AC
1204, prasanth, php dev, 30000, AC
1205, kranthi, admin, 20000, TP
1206, satish p, grp des, 20000, GR

3.2 Sqoop测试

3.2.1 使用Sqoop列出数据库

Sqoop列表数据库工具解析并执行对数据库服务器的“`SHOW DATABASES“`查询。它列出了在服务器上的所有数据库。**语法**以下语法用于Sqoop列表数据库命令。

    sqoop list-databases (generic-args) (list-databases-args)    sqoop-list-databases (generic-args) (list-databases-args)

示例查询下面的命令用于列出MySQL数据库服务器的所有数据库。

      sqoop list-databases \    --connect jdbc:mysql://192.168.0.100/ \    --username root \    --password 123

如果命令成功执行，那么它会显示MySQL数据库服务器的数据库列表，如下所示。

    information_schema    aresult    hivedb    mysql    test    urlenhance    userdb    wfbhive

3.2.2 使用Sqoop列出的某数据库中的所有表

Sqoop的list-tables工具解析并执行针对特定数据库的“`SHOW TABLES“`查询。它列出了在数据库中存在的表。**语法**以下是使用 Sqoop 的 list-tables 命令的语法。

    sqoop list-tables (generic-args) (list-tables-args)    sqoop-list-tables (generic-args) (list-tables-args)

示例查询下面的命令用于列出MySQL数据库服务器的userdb数据库下的所有的表。

    sqoop list-tables \    --connect jdbc:mysql://192.168.0.100/userdb \    --username root \    --password 123

如果该指令执行成功，那么将显示USERDB数据库中所有表，如下。

    emp    emp_add    emp_contact

经过以上验证可知，Sqoop可以正常使用。

3.3 Sqoop导入

将MySQL数据库连接驱动拷贝到$SQOOP_HOME/lib里

3.3.1 MySQL数据导入到HDFS

从RDBMS到HDFS，“导入工具”导入单个表的数据。表中的每一行被视为HDFS的一行记录。所有记录都存储为文本文件的文本数据或者Avro格式和序列化的二进制数据。**语法**下面的语法用于将数据导入HDFS。

    sqoop import (generic-args) (import-args)    sqoop-import (generic-args) (import-args)

示例[3.1 准备数据](xxxx)的userdb数据库中的emp, emp_add和emp_contact的三张表。

导入表
Sqoop工具import是用来将表中的数据导入到Hadoop的文件系统中作为文本文件或二进制文件。

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

      sqoop import \    --connect jdbc:mysql://192.168.0.100/userdb \    --username root \    --password 123 \    --table emp --m 1

如果成功执行，那么会得到下面的输出。

    ... ...    16/08/18 19:22:45 INFO mapreduce.Job: Job job_1469756466595_0015 running in uber mode : false    16/08/18 19:22:45 INFO mapreduce.Job:  map 0% reduce 0%    16/08/18 19:23:06 INFO mapreduce.Job:  map 100% reduce 0%    16/08/18 19:23:08 INFO mapreduce.Job: Job job_1469756466595_0015 completed successfully    16/08/18 19:23:09 INFO mapreduce.Job: Counters: 30    ... ...    16/08/18 19:23:09 INFO mapreduce.ImportJobBase: Transferred 127 bytes in 61.5066 seconds (2.0648 bytes/sec)    16/08/18 19:23:09 INFO mapreduce.ImportJobBase: Retrieved 4 records.

为了验证在HDFS导入的数据，请使用以下命令。

    hdfs dfs -cat /user/root/emp/part-m-*

emp表的数据和字段之间用逗号(,)表示。

    1201, gopal,    manager, 50000, TP    1202, manisha,  preader, 50000, TP    1203, kalil,    php dev, 30000, AC    1204, prasanth, php dev, 30000, AC    1205, kranthi,  admin,   20000, TP

导入到目标目录
在导入表数据到HDFS使用Sqoop导入工具，我们可以指定目标目录。

以下是指定目标目录选项的Sqoop导入命令的语法。

    --target-dir <new or exist directory in HDFS>

下面的命令是用来导入emp_add表数据到/queryresult目录。

    sqoop import \    --connect jdbc:mysql://192.168.0.100/userdb \    --username root \    --password 123 \    --table emp_add \    --m 1 \    --target-dir /queryresult

下面的命令是用来验证 /queryresult 目录中 emp_add表导入的数据形式。

    hdfs dfs -cat /queryresult/part-m-*

它会用逗号（，）分隔emp_add表的数据和字段。

    1201, 288A, vgiri,   jublee    1202, 108I, aoc,     sec-bad    1203, 144Z, pgutta,  hyd    1204, 78B,  oldcity, sec-bad    1205, 720C, hitech,  sec-bad

导入表数据子集

我们可以添加--where xxx参数得到一个子集。它执行在各自的数据库服务器相应的SQL查询，并将结果存储在HDFS的目标目录。

where子句的语法如下。

    --where <condition>

下面的命令用来导入emp_add表数据的子集。子集查询检索员工ID和地址，居住城市为：Secunderabad

    sqoop import \    --connect jdbc:mysql://192.168.0.100/userdb \    --username root \    --password 123 \    --table emp_add \    --m 1 \    --where "city=’sec-bad’" \    --target-dir /wherequery

下面的命令用来验证数据从emp_add表导入/wherequery目录

    hdfs dfs -cat /wherequery/part-m-*

它用逗号（,）分隔 emp_add表数据和字段。

    1202, 108I, aoc,     sec-bad    1204, 78B,  oldcity, sec-bad    1205, 720C, hitech,  sec-bad

增量导入

特别注意：所指定的check-column 列必须是自增int或者是时间戳
增量导入是仅导入新添加的表中的行的技术。它需要添加三个关键参数incremental,check-column和last-value选项来执行增量导入。

Argument Description –check-column (col) 指定一个“标志列”用于判断增量导入的数据范围，该列不能是字符型，最好是数字或者日期型（这个很好理解吧）。 –incremental (mode) 指定增量模式，包含“追加模式” append 和“最后修改模式” lastmodified （该模式更满足常见需求）。 –last-value (value) 指定“标志列”上次导入的上界。如果“标志列”是最后修改时间，则–last-value为上次执行导入脚本的时间。

下面的语法用于Sqoop导入命令增量选项。

    --incremental <mode>    --check-column <column name>    --last value <last check column value>

让我们假设新添加的数据转换成emp表如下：

    1206, satish p, grp des, 20000, GR

下面的命令用于在EMP表执行增量导入。

    sqoop import \    --connect jdbc:mysql://192.168.0.100/userdb \    --username root \    --password 123 \    --table emp \    --m 1 \    --incremental append \    --check-column id \    -last-value 1205

以下命令用于从emp表导入HDFS emp/ 目录的数据验证。

    hdfs dfs -cat /user/root/emp/part-m-*

它用逗号（，）分隔 emp_add表数据和字段。

    1201, gopal,    manager, 50000, TP    1202, manisha,  preader, 50000, TP    1203, kalil,    php dev, 30000, AC    1204, prasanth, php dev, 30000, AC    1205, kranthi,  admin,   20000, TP    1201, gopal,    manager, 50000, TP    1202, manisha,  preader, 50000, TP    1203, kalil,    php dev, 30000, AC    1204, prasanth, php dev, 30000, AC    1205, kranthi,  admin,   20000, TP    1206, satish p, grp des, 20000, GR

下面的命令是从表emp 用来查看修改或新添加的行。

    hdfs dfs -cat /user/root/emp/part-m-*1

这表示新添加的行用逗号（，）分隔emp表的字段。

    1201, gopal,    manager, 50000, TP    1202, manisha,  preader, 50000, TP    1203, kalil,    php dev, 30000, AC    1204, prasanth, php dev, 30000, AC    1205, kranthi,  admin,   20000, TP    1206, satish p, grp des, 20000, GR

Sqoop导出
将数据从HDFS导出到RDBMS数据库。目标表必须存在于目标数据库中。这是作为输入到Sqoop的文件包含记录，这被称为在表中的行。那些被读取并解析成一组记录和分隔与用户指定的分隔符。

默认的操作是从输入文件到数据库表，使用INSERT语句插入所有记录。在更新模式，Sqoop生成替换现有记录到数据库的UPDATE语句。

语法

以下是export命令语法。

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

示例
在HDFS文件中的员工数据的一个例子。员工数据是在HDFS/userinfo目录的emp_data文件中。所述emp_data如下。

1201, gopal, manager, 50000, TP
1202, manisha, preader, 50000, TP
1203, kalil, php dev, 30000, AC
1204, prasanth, php dev, 30000, AC
1205, kranthi, admin, 20000, TP
1206, satish p, grp des, 20000, GR

首先需要创建被用来接收导出数据的表：’employee’。

    CREATE TABLE employee (    id INT NOT NULL PRIMARY KEY,    name VARCHAR(20),    deg VARCHAR(20),    salary INT,    dept VARCHAR(10));

下面的命令是用来导出表数据（这是在HDFS emp_data文件）到MySQL数据库服务器DB数据库的employee表中。

    sqoop export \    --connect jdbc:mysql://192.168.0.100/userdb \    --username root \    --password 123 \    --table employee \    --export-dir /userinfo/emp_data

下面的命令是用来验证表mysql命令行。

    select * from employee;

如果给定的数据存储成功，那么可以找到数据在如下的employee表。

Id Name Designation Salary Dept 1201 gopal manager 50000 TP 1202 manisha preader 50000 TP 1203 kalil php dev 30000 AC 1204 prasanth php dev 30000 AC 1205 kranthi admin 20000 TP 1206 satish p grp des 20000 GR

* Sqoop Job
Sqoop Job创建并保存import和export命令。它指定参数来识别并调用已保存的Job。这种重新调用或重新执行用在增量导入，可以从RDBMS表到HDFS导入更新的行。

语法

以下是创建Sqoop Job的语法。

    sqoop job (generic-args) (job-args)        [-- [subtool-name] (subtool-args)]    sqoop-job (generic-args) (job-args)        [-- [subtool-name] (subtool-args)]

创建 Job(–create)
在这里，我们创建一个名为myjob，这可以从RDBMS表的数据导入到HDFS作业。下面的命令用于创建一个从DB数据库的employee表导入到HDFS文件的Job。

– import 两个杠和import中间必须有一个空格

    sqoop job --create myjob \    -- import \    --connect jdbc:mysql://192.168.0.100/userdb \    --username root \    --password 123 \    --table employee \    --m 1

验证 JOb (–list)
‘–list’ 参数是用来验证保存的Job。下面的命令用来验证保存Sqoop Job的列表。

sqoop job --list

它显示了现有的Job列表。

    Available jobs:       myjob

检查 Job(–show)
‘–show’ 参数用于检查或验证特定的Job，及其详细信息。以下命令和样本输出用来验证一个名为myjob的Job。

    sqoop job --show myjob

它显示了工具和它们的选择，这是使用在myjob中Job情况。

    Job: myjob    Tool: import Options:    ----------------------------    direct.import = true    codegen.input.delimiters.record = 0    hdfs.append.dir = false    db.table = employee    ...    incremental.last.value = 1206    ...

执行 Job (–exec)
‘–exec’ 选项用于执行保存的作业。下面的命令用于执行保存的作业称为myjob。

    sqoop job --exec myjob

首先程序会要求输入所要连接数据的密码，才会继续执行下去，它会显示下面的输出。

    16/08/18 21:39:10 INFO impl.YarnClientImpl: Submitted application application_1469756466595_0023    16/08/18 21:39:10 INFO mapreduce.Job: The url to track the job: http://hadoopcswfb:8088/proxy/application_1469756466595_0023/    16/08/18 21:39:10 INFO mapreduce.Job: Running job: job_1469756466595_0023    16/08/18 21:39:37 INFO mapreduce.Job: Job job_1469756466595_0023 running in uber mode : false    16/08/18 21:39:37 INFO mapreduce.Job:  map 0% reduce 0%    16/08/18 21:39:56 INFO mapreduce.Job:  map 100% reduce 0%    16/08/18 21:39:57 INFO mapreduce.Job: Job job_1469756466595_0023 completed successfully    16/08/18 21:39:58 INFO mapreduce.Job: Counters: 30            ... ...    16/08/18 21:39:58 INFO mapreduce.ImportJobBase: Transferred 176 bytes in 56.2899 seconds (3.1267 bytes/sec)    16/08/18 21:39:58 INFO mapreduce.ImportJobBase: Retrieved 6 records.

关于Sqoop2

架构上，Sqoop1使用MapOnly作业进行Hadoop（HDFS/HBase/Hive）同关系数据库进行数据的导入导出，用户使用命令行方式与之交互，数据传输和数据格式紧密耦合；易用性欠佳，Connector数据格式支持有限，安全性不好，对Connector的限制过死。Sqoop2则建立了集中化的服务，负责管理完整的MapReduce作业，提供多种用户交互方式（CLI/WebUI/RESTAPI），具有权限管理机制，具有规范化的Connector，使得它更加易用，更加安全，更加专注。

0 0