大数据_Sqoop

简述

sqoop是apache旗下一款“Hadoop和关系数据库服务器之间传送数据”的工具。

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

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

sqoop1与sqoop2区别

两代之间是两个完全不同的版本，不兼容
sqoop1：1.4.x
sqoop2：1.99.x

sqoop1优点架构部署简单：
sqoop1的缺点命令行方式容易出错，格式紧耦合，无法支持所有数据类型，安全机制不够完善，例如密码暴漏， 安装需要root权限，connector必须符合JDBC模型 。

sqoop2的缺点，架构稍复杂，配置部署更繁琐：
sqoop2的优点多种交互方式，命令行，web UI，rest API，conncetor集中化管理，所有的链接安装在sqoop server上，完善权限管理机制，connector规范化，仅仅负责数据的读写。

架构

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

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

CLI方式访问，会通过交互过程界面，输入的密码信息丌被看到，同时Sqoop2引入基亍角色的安全机制，Sqoop2比Sqoop多了一个Server端。

Sqoop安装

安装sqoop的前提是已经具备java和hadoop的环境。

1、下载
http://archive.apache.org/dist/sqoop/1.4.7
sqoop1版本详细下载地址
http://archive.apache.org/dist/sqoop/1.4.7/sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz
sqoop2版本详细下载地址
http://archive.apache.org/dist/sqoop/1.99.6/sqoop-1.99.6-bin-hadoop200.tar.gz 我们这里使用sqoop1的版本，下载之后上传到/export/softwares目录下，然后进行解压。

cd /export/softwares
tar -zxvf  sqoop-1.4.6-cdh5.14.0.tar.gz -C ../servers/ 

2、修改配置文件

cd /export/servers/hadoop‐3.1.1/conf/ 
cp sqoop‐env‐template.sh  sqoop‐env.sh 
vim sqoop‐env.sh
export HADOOP_COMMON_HOME/export/servers/hadoop‐3.1.1
export HADOOP_MAPRED_HOME=/export/servers/hadoop‐3.1.1
export HIVE_HOME=/export/servers/apache‐hive‐3.1.1‐bin

3、加入依赖包
依赖包添加到sqoop的lib目录下，常用有：

mysql-connector-java-5.1.40.jar
java-json.jar
hiveexec-3.1.1.jar

4、启动验证

cd /export/servers/sqoop-1.4.7.bin__hadoop-2.6.0
bin/sqoop-version

Sqoop的数据导入

1、命令行查看帮助

bin/sqoop list‐databases ‐‐help

2、列出windows主机所有的数据库

bin/sqoop list‐databases ‐‐connect jdbc:mysql://192.168.1.111:3306/ --username root ‐‐password root

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

bin/sqoop list‐tables ‐‐connect jdbc:mysql://192.168.1.111:3306/userdb --username root ‐‐password root

4、开启windows的远程连接权限

GRANT ALL  PRIVILEGES ON .  TO 'root'@'%'  IDENTIFIED BY 'yourpassword'  WITH  GRANT  OPTION; 
FLUSH  PRIVILEGES;

MySQL表导入HDFS

1、简单导入：
下面的命令用于从MySQL数据库服务器中的emp表导入HDFS：

 bin/sqoop import ‐‐connect jdbc:mysql://192.168.1.111:3306/userdb ‐password root ‐‐username root ‐‐table emp ‐‐m 1

查看：

hdfs  dfs  ‐ls  /user/root/emp

2、导入到HDFS指定目录
使用参数–target-dir来指定导出目的地；
使用参数–delete-target-dir来判断导出目录是否存在，如果存在就删掉；
使用参数–fields‐terminated‐by ‘\t’，字段之间的分隔符（默认是逗号分隔）；

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

查看：

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

MySQL表导入HIVE

1、创建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';

2、导入

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

3、查看

hive (sqooptohive)> select * from emp_hive;

4、导入并自动创建表
通过这个命令，我们可以直接将我们mysql表当中的数据以及表结构一起倒入到hive当中去

bin/sqoop import ‐‐connect jdbc:mysql://192.168.1.111:3306/userdb --
username root ‐‐password root ‐‐
table emp_conn ‐‐hive‐import ‐m 1 ‐‐hivedatabase sqooptohive

指定数据导入

1、where

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

2、sql

bin/sqoop import \ 
‐‐connect jdbc:mysql://192.168.1.111:3306/userdb ‐‐username root ‐‐password  root\ 
‐‐delete‐target‐dir ‐m 1 \ 
‐‐query 'select email from emp_conn where 1=1 and  $CONDITIONS' \ 
‐‐target‐dir /sqoop/emp_con

增量导入

方式：它需要添加‘incremental’, ‘check-column’, 和 ‘last-value’选项来执行增量导入
1、导入emp表当中id大于1202的所有数据

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

查看：

hdfs dfs -text /sqoop/increment/part*

2、导入指定时间范围数据

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

Sqoop的数据导出

hdfs导出到mysql

1、数据是在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

2、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;

3、执行导出命令

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