【大数据学习】之 SQOOP

Sqoop （官网sqoop.apache.org）

 

一．Sqoop简介

1.产生背景

MapReduce、Hive===>数据都是存放在HDFS上的

 

insert into xxx as select ...

 

Web前端如何与使用MapReduce或者Hive处理后的数据进行对接？

 

HDFS ===> RDBMS  (如果是HDFS导到关系型数据库，HDFS输入应该写url,就是导入的路径，关系型数据库要写关系型数据库的URL，账号，库，表，密码，驱动)

RDBMS ==> HDFS

这两种方式以前是用MapReduce来实现的，如果有好多个操作，那就非常烦非常慢。

 

这时就会想到 封装一个框架：指定输入和输出。这时的Sqoop诞生了。

==> Sqoop : SQL to Hadoop

这里的SQL可以理解为关系型数据库(RDBMS)，那Hadoop是指包括 HDFS/Hive/HBase (因为这些文件数据都存放在Hadoop之上的)，所以使用sqoop导这些都是没有问题的。

 

Apache Sqoop(TM) is a tool

designed for efficiently transferring bulk data

between Apache Hadoop and structured datastores such as relational databases

翻译后就是：Apache Sqoop（TM）是一种用于在apchde hadooop和结构化数据存储（如关系数据库）之间高效传输批量数据的工具 。

 

2.Sqoop架构

Sqoop1.x和Sqoop2.x ，1和2两个版本区别非常大，架构完全不一样，2的使用比1麻烦得多，2在现在的公司很少用，1在公司用得多。2.x并不兼容1.x

 

Sqoop其实就是一个桥梁，两端就是关系型数据库跟hadoop，在这两者之间架起来的一座数据交换的桥梁。Sqoop其实就是一个mr的jar包，底层实现(运行)就是mr

 

再来理解导入和导出，有一个出发点的：Hadoop，即以hadoop作为参照物

导入：RDBMS ==> Hadoop  (从RDBMS导入到Hadoop，所以就叫导入)

导出：Hadoop ==> RDBMS  (从Hadoop导出到RDBMS，所以就叫导出)

 

 

Sqoop1架构

客户端提交一个command到sqoop，会提交到yarn运行，而MapTask相当于从上面箭头的关系型数据库读进来，顺便就写到HADOOP（hdfs/hbase/hive），这里面并没有

reduce task,或不需要reduce task，为什么Sqoop1.x中不需要ReduceTask，仅仅MapTask就ok了? 因为map相当于从RDBMS读进来，然后做一次归并，然后丢到reduce,最终再合并，而该架构根本就没有计算，reduce一般用来做聚合的，所以只需要MaoTask就可以了。

 

二．Sqoop安装

1）下载  cdh 下载

wget http://archive-primary.cloudera.com/cdh5/cdh/5/sqoop-1.4.6-cdh5.7.0.tar.gz

2）解压到~/app下

3）SQOOP_HOME

export SQOOP_HOME=/home/hadoop/app/sqoop-1.4.6-cdh5.7.0

export PATH=$SQOOP_HOME/bin:$PATH

4）conf： $SQOOP_HOME/conf/sqoop-env.sh

export HADOOP_COMMON_HOME=/home/hadoop/app/hadoop-2.6.0-cdh5.7.0

export HADOOP_MAPRED_HOME=/home/hadoop/app/hadoop-2.6.0-cdh5.7.0

export HIVE_HOME=/home/hadoop/app/hive-1.1.0-cdh5.7.0

 

1.列出mysql数据库中的所有数据库

sqoop list-databases \

--connect jdbc:mysql://localhost:3306 \

--username root \

--password 123456

执行后有报错，原因是少了mysql的驱动包

需要添加mysql驱动包到/home/hadoop/app/sqoop-1.4.6-cdh5.7.0/lib

再执行一下，查出来的结果跟在mysql查的结果一样。

 

2.连接mysql并列出数据库中的表

sqoop list-tables \

--connect jdbc:mysql://localhost:3306/ruoze_d5 \

--username root \

--password 123456

查出来的结果跟在mysql查的结果一样。

 

三. 把数据从mysql导入到hdfs中

1.MySQL==>HDFS

 

sqoop import \

--connect jdbc:mysql://localhost:3306/ruoze_d5 \

--username root \

--password 123456 \

--table emp

执行时有两个错误

第一个是：ERROR tool.ImportTool: Error during import: No primary key could be found for table stu. Please specify one with --split-by or perform a sequential import with '-m 1'.

提示是指mysql中导出的表没有设定主键，本人就把mysql里面的emp表删了，再重新建表，建表时添加了主键 primary key(empno)。后面有介绍另外两个方法解决，不用删表重建的。

第二个错误，缺少json包（下载http://www.java2s.com/Code/Jar/j/Downloadjavajsonjar.htm）

解压java-json.jar包，把java-json.jar添加到${SQOOP_HOME}/sqoop/lib目录 (安装sqoop的目录)

重新执行import命令。

看看执行的日志，有个地方要记住：number of splits:4 表示执行了4个map task

那数据默认写在哪里呢，默认的是在 hadoop fs -ls  (跟hadoop fs -ls /user/hadoop写法是一样的)

现在看看emp里面有什么，hadoop fs -ls emp ，就会看到之前说的4个map task

再看看4个map task具体里面的内容, hadoop fs -text emp/part*

 

另外，如果想改mapreduce-job的名称可以在后面加mapreduce-job-name

有很多参数，可以参考help来查看了解。

sqoop import \

--connect jdbc:mysql://localhost:3306/ ruoze_d5 \

--username root \

--password 123456 \

--table emp \

--mapreduce-job-name FromMySQLToHDFS \    #这个是MP的名称

-m 1 \                                       #这个是修改map task的数量。

--delete-target-dir \                                            #如果目标目录存在，就先删除掉

--columns “EMPNO,ENAME,JOB,SAL,COMM“ \            #假如只导入几列

--target-dir EMP_COLUMN_WHERE \             #指定写到哪个路径

--fields-terminated-by '\t' \                            #修改字段与字段之间的分隔符用tab分隔

--null-non-string '0' \                     #非字符串的null用0表示

--null-string '' \                                            #字符串的null 用‘’表示

--where 'SAL>2000'

 

另一个情况是如果用sql时，就不能用table xxx了，where也不用了，要把这些删掉，另外是select语句里面要添加 $conditions

sqoop import \

--connect jdbc:mysql://localhost:3306/ruoze_d5 \

--username root \

--password 123456 \

--mapreduce-job-name FromMySQLToHDFS \

-m 1 \

--delete-target-dir \

--target-dir EMP_COLUMN_QUERY \

--fields-terminated-by '\t' \

--null-non-string '0' \

--null-string '' \

-e 'select * from emp where empno>7900 and $CONDITIONS';

在hdfs上查的数据跟在mysql上查是一样的结果。要注意一点，如果select是用双引号的,就要在conditions前面加\, 即"select * from emp where empno>7900 and \$CONDITIONS"

如果表没有设置主键，刚好map task的数量又不是1个，那这时就会报前面所说过的”ERROR tool.ImportTool: Error during import: No primary key could be found for table stu.”，这时看提示“Please specify one with --split-by or perform a sequential import with '-m 1'”，即有两个解决方法，一个是用--solit-by指定字段拆分，另一个方法是修改map task为1。

sqoop import \

--connect jdbc:mysql://localhost:3306/sqoop \

--username root \

--password root \

--table salgrage \

--split-by GRADE \

-m 2

 

1

2

3

4

5

6

7

8

9

10

 

3map

1: 1 2 3

2: 3 4 5

3: 4 5 6

5: 7 8 9 10

 

因为这段语句太长了，复制不方便，所以可以封装起来，

所以可以先建一个文档，把语句写到文档里面，注意格式，然后       再执行调用。

import

--connect

jdbc:mysql://localhost:3306/ruoze_d5

--username

root

--password

123456

--table

emp

--delete-target-dir

执行：sqoop --options-file /home/hadoop/tmp/emp.opt

 

以上便是mysql导入到hdfs的大概内容，还有很多参数可以查看帮助。

 

四、从hdfs导出数据到mysql

HDFS => MySQL

 

先在mysql创建一个表，复制表结构即可

create table emp_d5 as select * from emp where 1=8;

把hdfs的emp表的所有数据导出到mysql的emp_d5表中

sqoop export \

--connect jdbc:mysql://localhost:3306/ruoze_d5 \

--username root \

--password 123456 \

--table emp_d5 \

--export-dir emp

 

如果是导出部分字段呢？

sqoop export \

--connect jdbc:mysql://localhost:3306/ruoze_d5 \

--username root \

--password 123456 \

--table emp_d5 \

--export-dir EMP_COLUMN_SPLIT \

--columns 'EMPNO,ENAME,JOB,SAL,COMM' \

--fields-terminated-by '\t'

 

 

五、把关系型数据库的数据导入到hive里面

RDBMS==>Hive

 

sqoop import \

--connect jdbc:mysql://localhost:3306/ruoze_d5 \

--username root \

--password 123456 \

--table emp \

--delete-target-dir \

--hive-import  \

--hive-table d5_emp_test_p \    #这里写入的位置是hive默认的路径，即default

--fields-terminated-by '\t' \

--columns 'EMPNO,ENAME,JOB,SAL,COMM' \

--hive-overwrite \

--hive-partition-key 'pt' \           #分区表时要用到

--hive-partition-value 'ruoze'    #分区表时要用到

 

注意：如果导入时提示下面这个提示

需要从$HIVE_HOME/lib，将hive-common-1.1.0-cdh5.7.0.jar和hive-shims-*开始的所有模糊匹配的包，复制到$SQOOP_HOME/lib目录下 

 

 

在实际生产中，一般先在hive里面创建一张表，然后再导入，否则数据类型就会很容易出错的。

CREATE TABLE d5_emp_test (

empno int,

ename string,

job string,

mgr int,

hiredate string,

sal double,

comm double,

deptno int

)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

 

如果要指定写入哪个表，就在hive-table那里设置

sqoop import \

--connect jdbc:mysql://localhost:3306/ruoze_d5 \

--username root \

--password 123456 \

--table emp \

--delete-target-dir \

--hive-import \

--hive-table d5_hive.d5_emp_test \

--fields-terminated-by '\t' \

--columns 'empno,ename,job,sal,comm' \   #可以导入部分字段的数据

--hive-overwrite

#指定导入到d5_hive数据库的d5_emp_test表中，即导入到上面创建的表里面。

另columns 'empno,ename,job,sal,comm'，如果用大写就会失败，暂时不知道是什么原因。

 

试试分区表，先创建分区表

CREATE TABLE d5_emp_test_p (

empno int,

ename string,

job string,

mgr int,

hiredate string,

sal double,

comm double,

deptno int

)partitioned by (pt string)

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

 

导入部分字段的数据到分区表d5_hive.d5_emp_test_p

sqoop import \

--connect jdbc:mysql://localhost:3306/ruoze_d5 \

--username root \

--password 123456 \

--table emp \

--delete-target-dir \

--hive-import \

--hive-table d5_hive.d5_emp_test_p \

--fields-terminated-by '\t' \

--columns 'empno,ename,job,sal,comm' \

--hive-overwrite \

--hive-partition-key 'pt' \

--hive-partition-value 'ruoze'

在hive查查是否有数据，已经有数据了

在hdfs看看数据，也同样有的。