Sqoop系列之2、Sqoop的详细介绍及操作

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

大数据系列文章目录
Sqoop的官网

目录

概述sqoop1与sqoop2架构对比

sqoop1架构sqoop2架构 工作机制sqoop的安装

验证启动 sqoop抽取的两种方式区别

数据格式区别数据覆盖字段名 Sqoop的方式HCatalog方式

针对不同字段名，想要使用HCatalog方式将数据插入，可以使用下面的方式 Sqoop的数据导入

列举出所有的数据库表数据导入数据库表数据到HDFS导入到HDFS指定目录导入到hdfs指定目录并指定字段之间的分隔符导入关系表到Hive

第一步：准备hive数据库与表第二步：开始导入第三步：hive表数据查看 条件部分导入

where导入到HDFSsql语句查找导入hdfs增量导入数据到Hive表 Sqoop的数据导出

第一步：创建mysql表第二步：执行导出命令第三步：验证mysql表数据 Sqoop一些常用参数结束 概述

sqoop是apache旗下一款“Hadoop和关系数据库服务器之间传送数据”的工具。
导入数据：MySQL，Oracle导入数据到Hadoop的HDFS、HIVE、Hbase等数据存储系统；
导出数据：从Hadoop的HDFS、HIVE中导出数据到关系数据库mysql等

sqoop1与sqoop2架构对比 sqoop1架构

Sqoop1以Client客户端的形式存在和运行。没有任务时是没有进程存在的

sqoop2架构

sqoop2是以B/S服务器的形式去运行的，始终会有Server服务端进程在运行。

工作机制

将导入或导出命令翻译成mapreduce程序来实现。

sqoop的安装

这里不说，可能会出一篇文章来专门介绍

验证启动

sqoop-version

sqoop抽取的两种方式

对于Mysql数据的采集，通常使用Sqoop来进行。

通过Sqoop将关系型数据库数据到Hive有两种方式，一种是原生Sqoop API，一种是使用HCatalog API。两种方式略有不同。
HCatalog方式与Sqoop方式的参数基本都是相同，只是个别不一样，都是可以实现Sqoop将数据抽取到Hive。

区别 数据格式区别

Sqoop方式支持的数据格式较少，HCatalog支持的数据格式多，包括RCFile, ORCFile, CSV, JSON和SequenceFile等格式。

数据覆盖

Sqoop方式允许数据覆盖，HCatalog不允许数据覆盖，每次都只是追加。

字段名

Sqoop方式比较随意，不要求源表和目标表字段相同(字段名称和个数都可以不相同)，它抽取的方式是将字段按顺序插入，比如目标表有3个字段，源表有一个字段，它会将数据插入到Hive表的第一个字段，其余字段为NULL。但是HCatalog不同，源表和目标表字段名需要相同，字段个数可以不相等，如果字段名不同，抽取数据的时候会报NullPointerException错误。HCatalog抽取数据时，会将字段对应到相同字段名的字段上，哪怕字段个数不相等。

Sqoop的方式

sqoop import --hive-import --connect 'jdbc:mysql://localhost:3306/test' --username 'root' --password '123456789' --query " select order_no from driver_action where $CONDITIONS" --hive-database test --hive-table driver_action --hive-partition-key pt --hive-partition-value 20190901 --null-string '' --null-non-string '' --num-mappers 1 --target-dir /tmp/test --delete-target-dir

HCatalog方式

sqoop import --connect jdbc:mysql://localhost:3306/test--username 'root' --password 'root' --query "SELECT order_no FROM driver_action WHERe $CONDITIONS" --hcatalog-database test --hcatalog-table driver_action --hcatalog-partition-keys pt --hcatalog-partition-values 20200104 --hcatalog-storage-stanza 'stored as orcfile tblproperties ("orc.compress"="SNAPPY")' --num-mappers 1

针对不同字段名，想要使用HCatalog方式将数据插入，可以使用下面的方式

sqoop import --connect jdbc:mysql://localhost:3306/test--username 'root' --password 'root' --query "SELECt order_no_src as order_no_target FROM driver_action WHERe $CONDITIONS" --hcatalog-database test --hcatalog-table driver_action --hcatalog-partition-keys pt --hcatalog-partition-values 20200104 --hcatalog-storage-stanza 'stored as orc tblproperties ("orc.compress"="SNAPPY")' --num-mappers 1

Sqoop的数据导入

因为项目采用的是ORC File文件格式，sqoop原始方式并不支持，因此使用HCatalog方式来进行数据的导入导出。

“导入工具”导入单个表从RDBMS到HDFS。表中的每一行被视为HDFS的记录。所有记录都存储为文本文件的文本数据（或者Avro、sequence文件等二进制数据）

列举出所有的数据库

/usr/bin/sqoop help

命令行查看帮助

/usr/bin/sqoop list-databases --help

列出主机所有的数据库

/usr/bin/sqoop list-databases --connect jdbc:mysql://192.168.52.150:3306/ --username root --password 123456

查看某一个数据库下面的所有数据表

/usr/bin/sqoop list-tables --connect jdbc:mysql://192.168.52.150:3306/hive --username root --password 123456

表数据

在mysql中有一个库test中三个表：emp, emp_add和emp_conn。

测试数据sql，可以使用SQLyog等mysql客户端进行导入。

建库SQL

create database test default character set utf8mb4 collate utf8mb4_unicode_ci;use test;

emp

CREATE TABLE emp (id INT NOT NULL PRIMARY KEY,NAME VARCHAR ( 32 ) NULL,deg VARCHAR ( 32 ) NULL,salary INT NULL,dept VARCHAR ( 32 ) NULL );INSERT INTO emp (id, name, deg, salary, dept) VALUES (1201, 'gopal', 'manager', 50000, 'TP');INSERT INTO emp (id, name, deg, salary, dept) VALUES (1202, 'manisha', 'Proof reader', 50000, 'TP');INSERT INTO emp (id, name, deg, salary, dept) VALUES (1203, 'khalil', 'php dev', 30000, 'AC');INSERT INTO emp (id, name, deg, salary, dept) VALUES (1204, 'prasanth', 'php dev', 30000, 'AC');INSERT INTO emp (id, name, deg, salary, dept) VALUES (1205, 'kranthi', 'admin', 20000, 'TP');

emp_add

CREATE TABLE emp_add (id INT NOT NULL PRIMARY KEY,hno VARCHAR ( 32 ) NULL,street VARCHAR ( 32 ) NULL,city VARCHAR ( 32 ) NULL );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_conn

CREATE TABLE emp_conn ( id INT NOT NULL PRIMARY KEY, phno VARCHAR ( 32 ) NULL, email VARCHAR ( 32 ) NULL );INSERT INTO emp_conn (id, phno, email) VALUES (1201, '2356742', 'gopal@tp.com');INSERT INTO emp_conn (id, phno, email) VALUES (1202, '1661663', 'manisha@tp.com');INSERT INTO emp_conn (id, phno, email) VALUES (1203, '8887776', 'khalil@ac.com');INSERT INTO emp_conn (id, phno, email) VALUES (1204, '9988774', 'prasanth@ac.com');INSERT INTO emp_conn (id, phno, email) VALUES (1205, '1231231', 'kranthi@tp.com');

导入数据库表数据到HDFS

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

/usr/bin/sqoop import --connect jdbc:mysql://192.168.52.150:3306/test --password 123456 --username root --table emp --m 1

注意，mysql地址必须为服务器IP，不能是localhost或者机器名。

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

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

hdfs dfs -ls /user/root/emp

导入到HDFS指定目录

在导入表数据到HDFS时，使用Sqoop导入工具，我们可以指定目标目录。
使用参数–target-dir 来指定导出目的地，
使用参数–delete-target-dir来判断导出目录是否已存在，如果存在就删掉

/usr/bin/sqoop import --connect jdbc:mysql://192.168.52.150:3306/test --username root --password 123456 --delete-target-dir --table emp --target-dir /sqoop/emp --m 1

查看导出的数据

hdfs dfs -text /sqoop/emp/part-m-00000

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

1201,gopal,manager,50000,TP
1202,manisha,Proof reader,50000,TP
1203,khalil,php dev,30000,AC
1204,prasanth,php dev,30000,AC
1205,kranthi,admin,20000,TP

导入到hdfs指定目录并指定字段之间的分隔符

/usr/bin/sqoop import --connect jdbc:mysql://192.168.52.150:3306/test --username root --password 123456 --delete-target-dir --table emp --target-dir /sqoop/emp2 --m 1 –fields-terminated-by ‘t’

查看文件内容

hdfs dfs -text /sqoop/emp2/part-m-00000

导入关系表到Hive 第一步：准备hive数据库与表

将我们mysql当中的数据导入到hive表当中来
hive (default)> create database sqooptohive;
hive (default)> use sqooptohive;
hive (sqooptohive)> create table sqooptohive.emp_hive(id int,name string,deg string,salary int ,dept string) row format delimited fields terminated by 't' stored as orc;

第二步：开始导入

/usr/bin/sqoop import --connect jdbc:mysql://192.168.52.150:3306/test --username root --password 123456 --table emp --fields-terminated-by 't' --hcatalog-database sqooptohive --hcatalog-table emp_hive -m 1

第三步：hive表数据查看

select * from sqooptohive.emp_hive;

条件部分导入 where导入到HDFS

我们可以导入表时使用Sqoop导入工具，"where"子句的一个子集。它执行在各自的数据库服务器相应的SQL查询，并将结果存储在HDFS的目标目录。
where子句的语法如下。

--where

按照条件进行查找，通过—where参数来查找表emp_add当中city字段的值为sec-bad的所有数据导入到hdfs上面去

/usr/bin/sqoop import --connect jdbc:mysql://192.168.52.150:3306/test --username root --password 123456 --table emp_add --target-dir /sqoop/emp_add -m 1 --delete-target-dir --where "city = 'sec-bad'"

sql语句查找导入hdfs

我们还可以通过 –query参数来指定我们的sql语句，通过sql语句来过滤我们的数据进行导入

/usr/bin/sqoop import --connect jdbc:mysql://192.168.52.150:3306/test --username root --password 123456 --delete-target-dir -m 1 --query 'select phno from emp_conn where 1=1 and $CONDITIONS' --target-dir /sqoop/emp_conn

查看hdfs数据内容

hdfs dfs -text /sqoop/emp_conn/part*

增量导入数据到Hive表

/usr/bin/sqoop import --connect jdbc:mysql://192.168.52.150:3306/test --username root --password 123456 --query "select * from emp where id>1203 and $CONDITIONS" --fields-terminated-by 't' --hcatalog-database sqooptohive --hcatalog-table emp_hive -m 1

Sqoop的数据导出 第一步：创建mysql表

CREATE TABLE `emp_out` ( `id` INT(11) DEFAULT NULL, `name` VARCHAR(100) DEFAULT NULL, `deg` VARCHAR(100) DEFAULT NULL, `salary` INT(11) DEFAULT NULL, `dept` VARCHAR(10) DEFAULT NULL) ENGINE=INNODB DEFAULT CHARSET=utf8;

第二步：执行导出命令

通过export来实现数据的导出，将hive的数据导出到mysql当中去

/usr/bin/sqoop export --connect jdbc:mysql://192.168.52.150:3306/test --username root --password 123456 --table emp_out --hcatalog-database sqooptohive --hcatalog-table emp_hive -m 1

第三步：验证mysql表数据 Sqoop一些常用参数 参数说明–connect连接关系型数据库的URL–username连接数据库的用户名–password连接数据库的密码–driverJDBC的driver class–query或–e <>将查询结果的数据导入，使用时必须伴随参–target-dir，–hcatalog-table，如果查询中有where条件，则条件后必须加上 C O N D I T I O N S 关 键 字 。 如 果 使 用 双 引 号 包 含 s q l ， 则 CONDITIONS关键字。如果使用双引号包含sql，则 CONDITIONS关键字。如果使用双引号包含sql，则CONDITIONS前要加上以完成转义：$CONDITIONS–hcatalog-database指定HCatalog表的数据库名称。如果未指定，default则使用默认数据库名称。提供 --hcatalog-database不带选项–hcatalog-table是错误的。–hcatalog-table此选项的参数值为HCatalog表名。该–hcatalog-table选项的存在表示导入或导出作业是使用HCatalog表完成的，并且是HCatalog作业的必需选项。–create-hcatalog-table此选项指定在导入数据时是否应自动创建HCatalog表。表名将与转换为小写的数据库表名相同。–hcatalog-storage-stanza ‘stored as orc tblproperties (“orc.compress”=“SNAPPY”)’ 建表时追加存储格式到建表语句中，tblproperties修改表的属性，这里设置orc的压缩格式为SNAPPY-m指定并行处理的MapReduce任务数量。-m不为1时，需要用split-by指定分片字段进行并行导入，尽量指定int型。–split-by id如果指定-split by, 必须使用$CONDITIONS关键字, 双引号的查询语句还要加–hcatalog-partition-keys 或 --hcatalog-partition-valueskeys和values必须同时存在，相当于指定静态分区。允许将多个键和值提供为静态分区键。多个选项值之间用，（逗号）分隔。比如：–hcatalog-partition-keys year,month,day 和 --hcatalog-partition-values 1999,12,31–null-string ‘N’ 或 --null-non-string ‘N’指定mysql数据为空值时用什么符号存储，null-string针对string类型的NULL值处理，–null-non-string针对非string类型的NULL值处理–hive-drop-import-delims设置无视字符串中的分割符（hcatalog默认开启）–fields-terminated-by ‘t’设置字段分隔符结束

Sqoop到此就介绍完了，有交流的及时留言，我看到会第一时间回复。