大数据-sqoop（一）

最新推荐文章于 2022-07-06 10:38:05 发布

海恋北斗星

最新推荐文章于 2022-07-06 10:38:05 发布

阅读量221

点赞数

分类专栏：大数据文章标签： sqoop

本文链接：https://blog.csdn.net/zy12306/article/details/105245550

版权

大数据专栏收录该内容

70 篇文章 9 订阅

订阅专栏

大数据-Sqoop（一）

Sqoop工具初识

Sqoop是apache旗下的一款 ”Hadoop和关系数据库之间传输数据”的工具。

导入数据：将MySQL，Oracle导入数据到Hadoop的HDFS、HIVE、HBASE等数据存储系统。
导出数据：从Hadoop的文件系统中导出数据到关系数据库。

Sqoop工作机制

将导入和导出的命令翻译成mapreduce程序实现。在翻译出的mapreduce中主要是对inputformat和outputformat进行定制。

Sqoop1与2对比

sqoop在发展中的过程中演进出来了两种不同的架构。

sqoop1架构

版本号为1.4.x为sqoop1

在架构上：sqoop1使用sqoop客户端直接提交的方式
访问方式：CLI控制台方式进行访问
安全性：命令或脚本中指定用户数据库名及密码

sqoop2架构

版本号为1.99x为sqoop2

在架构上：sqoop2引入了sqoop server，对connector实现了集中的管理。
访问方式：REST API、 JAVA API、 WEB UI以及CLI控制台方式进行访问。

Sqoop安装部署

sqoop安装很简单，解压好进行简单的修改就可以使用。

第一步：下载安装包

http://archive.cloudera.com/cdh5/cdh/5/sqoop-1.4.6-cdh5.14.2.tar.gz

第二步：上传并解压

将我们下载好的安装包上传到node03服务器的/hlbdx/soft路径下，然后进行解压。

cd /hlbdx/soft/
tar -zxf sqoop-1.4.6-cdh5.14.2.tar.gz  -C /hlbdx/install/

第三步：修改配置文件

更改sqoop的配置文件

cd /hlbdx/install/sqoop-1.4.6-cdh5.14.2/conf
mv sqoop-env-template.sh  sqoop-env.sh
vim sqoop-env.sh

#Set path to where bin/hadoop is available
export HADOOP_COMMON_HOME=/hlbdx/install/hadoop-2.6.0-cdh5.14.2
#Set path to where hadoop-*-core.jar is available
export HADOOP_MAPRED_HOME=/hlbdx/install/hadoop-2.6.0-cdh5.14.2
#set the path to where bin/hbase is available
export HBASE_HOME=/hlbdx/install/hbase-1.2.0-cdh5.14.2
#Set the path to where bin/hive is available
export HIVE_HOME=/hlbdx/install/hive-1.1.0-cdh5.14.2

第四步：添加两个必要的jar包

sqoop需要两个额外依赖的jar包，将以下两个jar包添加到sqoop的lib目录下

第五步：配置sqoop的环境变量

sudo vim /etc/profile

export SQOOP_HOME=/hlbdx/install/sqoop-1.4.6-cdh5.14.2
export PATH=:$SQOOP_HOME/bin:$PATH

Sqoop数据导入

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

列举出所有的数据库

命令行查看帮助

bin/sqoop list-databases --help

列出node03主机所有的数据库

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

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

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

其它导入示例

1、准备表数据

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

建表语句如下：

CREATE DATABASE /*!32312 IF NOT EXISTS*/`userdb` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `userdb`;

DROP TABLE IF EXISTS `emp`;

CREATE TABLE `emp` (
  `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,
  `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `is_delete` BIGINT(20) DEFAULT '1'
) ENGINE=INNODB DEFAULT CHARSET=latin1;

INSERT  INTO `emp`(`id`,`name`,`deg`,`salary`,`dept`) VALUES (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');

DROP TABLE IF EXISTS `emp_add`;

CREATE TABLE `emp_add` (
  `id` INT(11) DEFAULT NULL,
  `hno` VARCHAR(100) DEFAULT NULL,
  `street` VARCHAR(100) DEFAULT NULL,
  `city` VARCHAR(100) DEFAULT NULL,
  `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `is_delete` BIGINT(20) DEFAULT '1'
) ENGINE=INNODB DEFAULT CHARSET=latin1;

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

DROP TABLE IF EXISTS `emp_conn`;
CREATE TABLE `emp_conn` (
  `id` INT(100) DEFAULT NULL,
  `phno` VARCHAR(100) DEFAULT NULL,
  `email` VARCHAR(100) DEFAULT NULL,
  `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `is_delete` BIGINT(20) DEFAULT '1'
) ENGINE=INNODB DEFAULT CHARSET=latin1;

INSERT  INTO `emp_conn`(`id`,`phno`,`email`) VALUES (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');

2、导入数据库表数据到HDFS

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

bin/sqoop import --connect jdbc:mysql://node03:3306/userdb --password 123456 --username root --table emp --m 1

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

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

hdfs  dfs  -ls  /user/root/emp

3、导入到HDFS指定目录

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

使用参数 --target-dir来指定导出目的地，

使用参数—delete-target-dir来判断导出目录是否存在，如果存在就删掉。

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

查看导出的数据

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

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

bin/sqoop import  --connect jdbc:mysql://node03:3306/userdb --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

5、导入关系表到HIVE

第一步：拷贝jar包

将我们mysql表当中的数据直接导入到hive表中的话，我们需要将hive的一个叫做hive-exec-1.1.0-cdh5.14.0.jar的jar包拷贝到sqoop的lib目录下。

cp /hlbdx/install/hive-1.1.0-cdh5.14.0/lib/hive-exec-1.1.0-cdh5.14.2.jar /hlbdx/install/sqoop-1.4.6-cdh5.14.2/lib/

第二步：准备hive数据库与表

将我们mysql当中的数据导入到hive表当中来

hive (default)> create database sqooptohive;
hive (default)> use sqooptohive;
hive (sqooptohive)> create external table emp_hive(id int,name string,deg string,salary int ,dept string) row format delimited fields terminated by '\001';

第三步：开始导入

bin/sqoop import --connect jdbc:mysql://node03:3306/userdb --username root --password 123456 --table emp --fields-terminated-by '\001' --hive-import --hive-table sqooptohive.emp_hive --hive-overwrite --delete-target-dir --m 1

第四步：hive表数据查看

select * from emp_hive;

6、导入关系表到hive并自动创建hive表

我们也可以通过命令来将我们的mysql的表直接导入到hive表当中去

bin/sqoop import --connect jdbc:mysql://node03:3306/userdb --username root --password 123456 --table emp_conn --hive-import -m 1 --hive-database sqooptohive;

通过这个命令，我们可以直接将我们mysql表当中的数据以及表结构一起倒入到hive当中去。

7、将mysql表数据导入到hbase当中去

第一步：修改sqoop配置文件

sqoop导入导出HBase的数据，需要修改sqoop的配置文件sqoop-env.sh

cd /hlbdx/install/sqoop-1.4.6-cdh5.14.2/conf
vim sqoop-env.sh


#Set path to where bin/hadoop is available
export HADOOP_COMMON_HOME=/hlbdx/install/hadoop-2.6.0-cdh5.14.2

#Set path to where hadoop-*-core.jar is available
export HADOOP_MAPRED_HOME=/hlbdx/install/hadoop-2.6.0-cdh5.14.2

#set the path to where bin/hbase is available
export HBASE_HOME=/hlbdx/install/hbase-1.2.0-cdh5.14.2

#Set the path to where bin/hive is available
export HIVE_HOME=/hlbdx/install/hive-1.1.0-cdh5.14.2

第二步：在mysql当中创建数据库以及数据库表并插入数据

创建数据库表

CREATE DATABASE  IF NOT EXISTS library;
USE library;
CREATE TABLE book(
id INT(4) PRIMARY KEY NOT NULL AUTO_INCREMENT, 
NAME VARCHAR(255) NOT NULL, 
price VARCHAR(255) NOT NULL);

插入数据

INSERT INTO book(NAME, price) VALUES('Lie Sporting', '30');  
INSERT INTO book (NAME, price) VALUES('Pride & Prejudice', '70');  
INSERT INTO book (NAME, price) VALUES('Fall of Giants', '50');

第三步：将mysql表当中的数据导入HBase表当中去

执行以下命令，将mysql表当中的数据导入到HBase当中去

bin/sqoop import \
--connect jdbc:mysql://node03:3306/library \
--username root \
--password 123456 \
--table book \
--columns "id,name,price" \
--column-family "info" \
--hbase-create-table \
--hbase-row-key "id" \
--hbase-table "hbase_book" \
--num-mappers 1  \
--split-by id

第四步：HBase当中查看表数据

进入hbase的shell客户端，通过scan查看数据

hbase(main):057:0> scan 'hbase_book'

8、导入表数据子集

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

where子句的语法如下

--where <condition>

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

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

9、sql语句查找导入hdfs

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

bin/sqoop import \
--connect jdbc:mysql://node03:3306/userdb --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*

10、增量导入

在实际工作当中，数据的导入，很多时候都是只需要导入增量数据即可，并不需要将表中的数据全部导入到hive或者hdfs当中去，肯定会出现重复的数据的状况，所以我们一般都是选用一些字段进行增量的导入，为了支持增量的导入，sqoop也给我们考虑到了这种情况并且支持增量的导入数据

增量导入是仅导入新添加的表中的行的技术。

它需要添加‘incremental’, ‘check-column’, 和 ‘last-value’选项来执行增量导入。

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

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

第一种增量导入使用上面的选项来实现

导入emp表当中id大于1202的所有数据

注意：增量导入的时候，一定不能加参数--delete-target-dir否则会报错

bin/sqoop import \
--connect jdbc:mysql://node03:3306/userdb \
--username root \
--password 123456 \
--table emp \
--incremental append \
--check-column id \
--last-value 1202  \
-m 1 \
--target-dir /sqoop/increment

查看数据内容

hdfs dfs -text /sqoop/increment/part*

第二种增量导入通过--where条件来实现

或者我们使用--where来进行控制数据的选取会更加精准

bin/sqoop import \
--connect jdbc:mysql://node03:3306/userdb \
--username root \
--password 123456  \
--table emp \
--incremental append  \
--where "create_time > '2018-06-17 00:00:00' and is_delete='1' and create_time < '2018-06-17 23:59:59'" \
--target-dir /sqoop/incement2 \
--check-column id  \
--m 1

Sqoop数据导出

将数据从HDFS把文件导出到RDBMS数据库

默认操作是从将文件中的数据使用INSERT语句插入到表中

更新模式下，是生成UPDATE语句更新表数据

数据是在HDFS当中的如下目录/sqoop/emp，数据内容如下：

1201,gopal,manager,50000,TP,2018-06-17 18:54:32.0,2018-06-17 18:54:32.0,1
1202,manisha,Proof reader,50000,TP,2018-06-15 18:54:32.0,2018-06-17 20:26:08.0,1
1203,khalil,php dev,30000,AC,2018-06-17 18:54:32.0,2018-06-17 18:54:32.0,1
1204,prasanth,php dev,30000,AC,2018-06-17 18:54:32.0,2018-06-17 21:05:52.0,0
1205,kranthi,admin,20000,TP,2018-06-17 18:54:32.0,2018-06-17 18:54:32.0,1

第一步：创建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,
  `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `is_delete` BIGINT(20) DEFAULT '1'
) ENGINE=INNODB DEFAULT CHARSET=utf8;

第二步：执行导出命令

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

bin/sqoop export \
--connect jdbc:mysql://node03:3306/userdb \
--username root --password 123456 \
--table emp_out \
--export-dir /sqoop/emp \
--input-fields-terminated-by ","

第三步：验证mysql表数据

将数据从Hbase导出到mysql

将hbase_book这张表当中的数据导出到mysql当中来

注意：sqoop不支持我们直接将HBase当中的数据导出，所以我们可以通过以下的转换进行导出

Hbase→hive外部表→hive内部表→通过sqoop→mysql

第一步：创建hive外部表

进入hive客户端，创建hive外部表，映射hbase当中的hbase_book表

CREATE EXTERNAL TABLE course.hbase2mysql (id int,name string,price int)
   STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
   WITH SERDEPROPERTIES (
    "hbase.columns.mapping" =
     ":key,info:name, info:price"
   )
   TBLPROPERTIES( "hbase.table.name" = "hbase_book",
    "hbase.mapred.output.outputtable" = "hbase2mysql");

第二步：创建hive内部表并将外部表数据插入到内部表当中来

进入hive客户端，执行以下命令，创建hive内部表，并将外部表的数据插入到hive的内部表当中来

CREATE TABLE course.hbase2mysqlin(id int,name string,price int);

第三步：外部表数据插入内部表

进入hive客户端执行以下命令，将hive外部表数据插入到hive内部表当中来

insert overwrite table course.hbase2mysqlin select * from course.hbase2mysql;

第四步：清空mysql表数据

进入mysql客户端，执行以下命令，将mysql表数据清空。

TRUNCATE TABLE book;

第五步：执行sqoop导出hive内部表数据到mysql

bin/sqoop export -connect jdbc:mysql://node03:3306/library \
-username root -password 123456  \
-table book --export-dir /user/hive/warehouse/course.db/hbase2mysqlin \
--input-fields-terminated-by '\001' \
--input-null-string '\\N' \
--input-null-non-string '\\N';

此博文仅供学习参考，如有错误欢迎指正。

上一篇《大数据-Flume（九）》

下一篇《大数据-sqoop（二）》

希望对大数据相关技术感兴趣的友友们关注一下，大家可以一起交流学习哦~