大数据_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 ","