文章目录
介绍
sqoop是RDBMS(mysql,oracle等)和Hadoop生态体系(hdfs,hive,hbase)间数据传输的工具,
通过将导入导出命令翻译为mapreduce命令来执行
安装及配置
① 解压
② 修改环境变量
cd到conf文件夹下,重命名sqoop-env-template.sh为sqoop-env.sh,并添加安装的hadoop,hive,hbase等安装路径
export HADOOP_COMMON_HOME= /export/servers/hadoop-2.7.5
export HADOOP_MAPRED_HOME= /export/servers/hadoop-2.7.5
export HIVE_HOME= /export/servers/hive
③ 测试
列出所有数据库
cd /export/servers/sqoop-1.4.6-cdh5.14.0/
bin/sqoop list-databases \
--connect jdbc:mysql://localhost:3306/ \
--username root --password ******
缺少三个依赖包,下载并复制到lib文件夹下
mysql连接
jason包
hive执行包 (hive依赖包下有hive-exec,复制到sqoop依赖文件夹下)
导入
全量导入
全量导入到HDFS
bin/sqoop import \ #导入命令
--connect jdbc:mysql://bigdata01.virtualgroup.com/sqooptest \ #db位置和数据库名称
--username root \ #db用户名
--password ******\ #db密码
--target-dir /sqoopresult1 \ #hdfs保存路径
--table emp \ #MySQL中源数据表名
--m 1 #启动map个数
命令解释
–fields-terminated-by ‘\t’ 可以指定分隔符,默认逗号
–m 1 指定启动map数量,若是多个map,需指定map分割字段,推荐数字型主键
–split-by id 指定分割字段
全量导入到HIVE
方式一 先导入表结构,再导入数据
导入表结构
bin/sqoop create-hive-table \ #导入表结构
--connect jdbc:mysql://bigdata01.virtualgroup.com/sqooptest \ #数据库
--table emp_add \ #数据库表名
--username root \
--password ******\
--hive-table default.emp_add_sp #导入目标的hive数据库.表名
导入表数据
bin/sqoop import \
--connect jdbc:mysql://bigdata01.virtualgroup.com:3306/sqooptest \
--username root \
--password ******\
--table emp_add \
--hive-table default.emp_add_sp \
--hive-import \
--m 1
默认分隔符\001
方式二 直接导入表
bin/sqoop import \
--connect jdbc:mysql://bigdata01.virtualgroup.com:3306/sqooptest \
--username root \
--password ******\
--table emp_conn \ #表名emp_conn
--hive-import \
--m 1 \
--hive-database default; #数据库名为default
子集导入
where过滤
bin/sqoop import \ #导入命令
--connect jdbc:mysql://bigdata01.virtualgroup.com/sqooptest \ #db位置和数据库名称
--username root \ #db用户名
--password ******\ #db密码
--target-dir /whereresult1 \ #hdfs保存路径
--where "dept='TP'"\ #字段过滤,仅导入符合要求的数据
--table emp \
--m 1 #启动map个数
query查询
bin/sqoop import \
--connect jdbc:mysql://bigdata01.virtualgroup.com/sqooptest \
--username root \
--password ******\
--target-dir /queryresult1 \
--query 'select id,street,city from emp_add where city="sec-bad"and $CONDITIONS' \
--m 1
规定
- 出现–query不能出现–table(与–query内的from重复)
- SQL语句不能以双引号包括,只能用单引号
- 单引号内似乎不能出现单引号,是以单引号内含双引号交叉出现的,否则报错(实测)
- query语句中必须有where条件,无需则添加where 1=1之类
- where语句最后必须添加$CONDITIONS
增量导入
–check-column
指定一些列作为增量检查的字段,只能是时间或数字格式,可以指定多列
–incremental
append:追加,指定值之后的数追加导入
–last-value
自上次导入列后的最大值,或者自己设定
append模式
准备数据
bin/sqoop import \
--connect jdbc:mysql://bigdata01.virtualgroup.com/sqooptest \
--username root \
--password ******\
--target-dir /appendresult1 \
--table emp_add \
--m 1
增加数据
bin/sqoop import \
--connect jdbc:mysql://bigdata01.virtualgroup.com/sqooptest \
--username root \
--password ******\
--target-dir /appendresult1 \
--table emp_add \
--incremental append \ #有append和lastmodified两个选项,lastmodified如下
--check-column id \ #以ID列作为增量检查的字段
--last-value 1205 \ #原文件最后一个ID是1205,对大于此值的新增
--m 1
会在之前文件夹下新增一个文件,即为增量数据
lastmodified模式
append选项
准备数据
create table customertest(id int,name varchar(20),last_mod timestamp default current_timestamp on update current_timestamp);
#以上语法对timestamp列按系统时间默认,更新则也会自动修改此时间
insert into customertest(id,name) values(1,'neil');
insert into customertest(id,name) values(2,'jack');
insert into customertest(id,name) values(3,'martin');
insert into customertest(id,name) values(4,'tony');
insert into customertest(id,name) values(5,'eric');
导入
bin/sqoop import \
--connect jdbc:mysql://bigdata01.virtualgroup.com/sqooptest \
--username root \
--password ******\
--target-dir /lastmodifiedresult1 \
--table customertest \
--m 1
增加
insert into customertest(id,name) values(6,'james');
导入增加数据
bin/sqoop import \
--connect jdbc:mysql://bigdata01.virtualgroup.com/sqooptest \
--username root \
--password ******\
--table customertest \
--target-dir /lastmodifiedresult1 \
--check-column last_mod \
--incremental lastmodified \
--last-value "2019-10-17 20:53:52" \
--m 1 \
--append #对大于等于last-value的新增,新建文件
merge-key选项
更新
update customertest set name = 'Neil' where id = 1;
导入增加数据
bin/sqoop import \
--connect jdbc:mysql://bigdata01.virtualgroup.com/sqooptest \
--username root \
--password ******\
--table customertest \
--target-dir /lastmodifiedresult1 \
--check-column last_mod \
--incremental lastmodified \
--last-value "2019-10-17 20:53:52" \
--m 1 \
--merge-key id #新增值新增,更新值更新,合并到一个新的-r-的文件中
导出
默认模式导出
HDFS->mysql 底层为insert一条条插入
myqsl需先手动建立对应的表
use sqooptest;
CREATE TABLE employee (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(20),
deg VARCHAR(20),
salary INT,
dept VARCHAR(10));
准备导出数据
vim empdata
1201,gopal,manager,50000,TP
1202,manisha,preader,50000,TP
1203,kalil,php dev,30000,AC
1204,prasanth,php dev,30000,AC
1205,kranthi,admin,20000,TP
1206,satishp,grpdes,20000,GR
hadoop fs -put empdata /sqoopoutput
数据导出
bin/sqoop export \
--connect jdbc:mysql://bigdata01.virtualgroup.com/sqooptest \
--username root \
--password ******\
--table employee \ #mysql中的表名
--export-dir /sqoopoutput/empdata #导出数据的存放位置
其他参数
–input-fields-terminated-by ‘\t’ 指定分隔符
–columns 选择列并控制他们的排序,当源文件和数据库表列顺序一致时不需要
更新导出
updateonly
修改源数据
导出更新数据
updateonly只更新原有记录有变化的部分,不会更新新增数据
bin/sqoop export \
--connect jdbc:mysql://bigdata01.virtualgroup.com/sqooptest \
--username root \
--password ******\
--table employee \
--export-dir /sqoopoutput/empdata2 \
--update-key id \
--update-mode updateonly #updateonly只更新更改,不会更新新增
allowinsert
修改源数据
导出更新数据
更新和新增的变化都会被导入
bin/sqoop export \
--connect jdbc:mysql://bigdata01.virtualgroup.com/sqooptest \
--username root \
--password ******\
--table employee \
--export-dir /sqoopoutput/empdata3 \
--update-key id \
--update-mode allowinsert #updateonly新增和更改都会更新
sqoop job作业
创建job
bin/sqoop job --create updatejob -- export \
#create后为job名称,inport/export前有空格
#其他语法同正常的导入导出操作
--connect jdbc:mysql://bigdata01.virtualgroup.com/sqooptest \
--username root \
--password ******\
--table employee \
--export-dir /sqoopoutput/empdata2 \
--update-key id \
--update-mode allowinsert
查看job
bin/sqoop job --list
查看job详细信息
bin/sqoop job --show jobname
执行job
bin/sqoop job --exec jobname
免密执行job
①新建文件保存密码,权限设置为400
②sqoop-site.xml设置
<property>
<name>sqoop.metastore.client.record.password</name>
<value>true</value>
<description>If true, allow saved passwords in the metastore.
</description>
</property>
③创建job时语法调整
--password ****** #不再使用--password
--password-file /密码地址 #使用--password-file指定密码地址