一、sqoop--基本使用

发布时间:2020-03-11 08:54:05 作者:隔壁小白
来源:网络 阅读:265

一、sqoop概述

1.1 简介

​ sqoop用于在hadoop(hdfs、hive)和关系型数据库等结构化数据存储之间相关导数据的场景。Sqoop于2012年3月孵化出来,现在是一个顶级的Apache项目。
请注意,1.99.7与1.4.6不兼容,且特征不完整,它并不打算用于生产部署。

1.2 基本原理

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

二、部署sqoop

首先得准备好hadoop和java环境,这里不重复说。
这里使用 sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz。
解压程序:

tar -zxvf sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz -C /opt/modules/

修改配置文件:

进入sqoop的解压路径,进入conf目录
mv sqoop-env-template.sh sqoop-env.sh

修改内容:
#export HADOOP_COMMON_HOME=
export HADOOP_COMMON_HOME=/opt/modules/hadoop-2.8.4

#Set path to where hadoop-*-core.jar is available
#export HADOOP_MAPRED_HOME=
export HADOOP_MAPRED_HOME=/opt/modules/hadoop-2.8.4

#Set the path to where bin/hive is available
#export HIVE_HOME=
export HIVE_HOME=/opt/modules/hive-1.2.1-bin

#Set the path for where zookeper config dir is
#export ZOOCFGDIR=
export ZOOCFGDIR=/opt/modules/zookeeper-3.4.10/conf

准备需要连接的关系型数据库的连接驱动,这里使用mysql,所以下载 mysql-connector-java-5.1.27-bin.jar,然后

cp -a mysql-connector-java-5.1.27-bin.jar /opt/module/sqoop-1.4.6.bin__hadoop-2.0.4-alpha/lib

接着配置环境变量:

vim /etc/profile.d/sqoop.sh
#!/bin/bash
export SQOOP_HOME=/opt/modules/sqoop-1.4.6.bin__hadoop-2.0.4-alpha
export PATH=$PATH:${SQOOP_HOME}/bin

验证sqoop:

sqoop help

三、sqoop简单使用案例

在详细说明sqoop的用法之前,先来点简单案例熟悉下

3.1 导入数据

​ 在Sqoop中,“导入”概念指:从非大数据集群(RDBMS)向大数据集群(HDFS,HIVE,HBASE等)中传输数据,叫做:导入,即使用import关键字。

3.1.1 RDBMS到HDFS

先在mysql中准备些数据:

$ mysql -uroot -p000000
mysql> create database company;
mysql> create table company.staff(id int(4) primary key not null auto_increment, name varchar(255), sex varchar(255));
mysql> insert into company.staff(name, sex) values('Thomas', 'Male');
mysql> insert into company.staff(name, sex) values('Catalina', 'FeMale');

导入全部数据:

sqoop import \
--connect jdbc:mysql://bigdata121:3306/company \   jdbc的连接串
--username root \                                  用户名
--password 000000 \                                密码
--table staff \                                    数据源的表
--target-dir /user/company \                       hdfs指定目录
--delete-target-dir \                              目标目录存在就删除
--num-mappers 1 \                                  map数目
--fields-terminated-by "\t"                        输出到hdfs的分隔符

查询导入:

sqoop import \
--connect jdbc:mysql://bigdata121:3306/company \
--username root \
--password 000000 \
--target-dir /user/company \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t" \     写入到hdfs文件中的字段分隔符
--query 'select name,sex from staff where id <=3 and $CONDITIONS;' 执行的sql查询语句

尖叫提示1:must contain '$CONDITIONS' in WHERE clause.即where语句的最后面一定要加上 and $CONDITIONS
尖叫提示2:如果query后使用的是双引号,则$CONDITIONS前必须加转义符,防止shell识别为自己的变量。

导入指定列:

$ bin/sqoop import \
--connect jdbc:mysql://bigdata113:3306/company \
--username root \
--password 000000 \
--target-dir /user/company \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t" \
--columns id,sex \                       导入指定列
--table staff

尖叫提示:columns中如果涉及到多列,用逗号分隔,分隔时不要添加空格
像这种只是简单对列进行筛选的,用上面的方式就好,如果需要进行where之类的条件筛选,那么还是使用查询导入

使用sqoop关键字筛选查询导入数据:

sqoop import \
--connect jdbc:mysql://bigdata113:3306/company \
--username root \
--password 000000 \
--target-dir /user/company \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t" \
--table staff \
--where "id=2"                        这个其实相当于select中的where语句

这个方式其实就相当于--query方式进行查询导入

尖叫提示:在Sqoop中可以使用sqoop import -D property.name=property.value这样的方式加入执行任务的参数,多个参数用空格隔开。

3.1.2 RDBMS到hive

sqoop import \
--connect jdbc:mysql://bigdata113:3306/Andy \
--username root \
--password 000000 \
--table aca \
--num-mappers 1 \
--hive-import \                           指定导入到hive中
--fields-terminated-by "\t" \             指定字段分隔符
--hive-overwrite \                        覆盖导入
--hive-table staff_hive                   指定导入到hive中的表

尖叫提示1:从MYSQL到Hive,本质是从MYSQL => HDFS => load To Hive
尖叫提示2:hive表不存在时,自动创建。表存在默认就会自动覆盖数据

3.2 导出数据

​ 在Sqoop中,“导出”概念指:从大数据集群(HDFS,HIVE,HBASE等)向非大数据集群(RDBMS)中传输数据,叫做:导出,即使用export关键字。

mysql创建aca表
create table abc(id int,name VARCHAR(5));

hive导入mysql
sqoop export \
--connect jdbc:mysql://bigdata113:3306/test\
--username root \
--password 000000 \
--export-dir /user/hive/warehouse/staff_hive \
--table abc \
--num-mappers 1 \
--input-fields-terminated-by "\t"

尖叫提示1:Mysql中如果表不存在,不会自动创建。表不存在会报错 
尖叫提示2:数据是追加的,不是覆盖

3.3 脚本打包

使用opt格式文件打包sqoop命令,文件后缀必须是.opt

vi ./job_HDFS2RDBMS.opt
#以下命令是从staff_hive中追加导入到mysql的aca表中
# 格式基本上是 option /n value

export
--connect
jdbc:mysql://bigdata113:3306/Andy
--username
root
--password
000000
--table
aca
--num-mappers
1
--export-dir
/user/hive/warehouse/staff_hive
--input-fields-terminated-by
"\t"

格式必须验证按照上面的写,选项和参数各自独立一行

执行脚本:

sqoop --options-file job_HDFS2RDBMS.opt

四、sqoop常用参数

sqoop的用法其实是以 sqoop subcommand options 的形式的,有很多子命令,选项也有公有选项以及独有选项,下面看看每个子命令的用法

4.1 数据库连接参数

参数 说明
--connect 连接关系型数据库的URL
--driver Hadoop根目录
--connection-manager 指定要使用的连接管理类
--password 连接数据库的密码
--username 连接数据库的用户名

4.2 import公用参数

参数 说明
--enclosed-by <char> 给字段值前加上指定的字符
--escaped-by <char> 对字段中的双引号加转义符
--fields-terminated-by <char> 设定每个字段是以什么符号作为结束,默认为逗号
--lines-terminated-by <char> 设定每行记录之间的分隔符,默认是\n

4.3 export公用参数

参数 说明
--input-enclosed-by <char> 对字段值前后加上指定字符
--input-escaped-by <char> 对含有转移符的字段做转义处理
--input-fields-terminated-by <char> 字段之间的分隔符
--input-lines-terminated-by <char> 行之间的分隔符

4.4hive的公用参数

参数 说明
--hive-delims-replacement <arg> 用自定义的字符串替换掉数据中的\r\n和\013 \010等字符
--hive-drop-import-delims 在导入数据到hive时,去掉数据中的\r\n\013\010这样的字符
--map-column-hive <arg> 生成hive表时,可以更改生成字段的数据类型
--hive-partition-key 创建分区,后面直接跟分区名,分区字段的默认类型为string
--hive-partition-value <v> 导入数据时,指定某个分区的值
--hive-import 将数据从关系数据库中导入到hive表中
--hive-overwrite 覆盖掉在hive表中已经存在的数据
--create-hive-table 默认是false,即,如果目标表已经存在了,那么创建任务失败。
--hive-table 后面接要创建的hive表,默认使用MySQL的表名
--table 指定关系数据库的表名

4.5 import--导入到hadoop

​ 将关系型数据库中的数据导入到HDFS(包括Hive,HBase)中,如果导入的是Hive,那么当Hive中没有对应表时,则自动创建。导入数据时,默认是追加的方式。

导入到hive中
sqoop import \
--connect jdbc:mysql://bigdata113:3306/Andy \
--username root \
--password 000000 \
--table access \
--hive-import \
--fields-terminated-by "\t"

增量导入:
有几个关键参数

参数 说明
--check-column 字段名 检查增量的字段名
--incremental append | lastmodified 两种增加方式,后面有讲区别
--last-value value 指定增量的最后一个值的界限

append模式:

sqoop import \
--connect jdbc:mysql://bigdata113:3306/Andy \
--username root \
--password 000000 \
--table aca \
--num-mappers 1 \
--fields-terminated-by "\t" \
--target-dir /user/hive/warehouse/staff_hive \
--check-column id \
--incremental append \
--last-value 10     在check-column指定的字段中,下一次导入的开始的行的id的值

尖叫提示1:append不能与--hive-等参数同时使用
尖叫提示2:如果 --last-value N , N > MYSQL中最大行数,则HDFS会创建一个空文件。如果N<=0 , 那么就是所有数据

lastmodified模式:

先在mysql中建表并插入几条数据:
mysql> create table company.staff_timestamp(id int(4), name varchar(255), sex varchar(255), last_modified timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
mysql> insert into company.staff_timestamp (id, name, sex) values(1, 'AAA', 'female');
mysql> insert into company.staff_timestamp (id, name, sex) values(2, 'BBB', 'female');
先导入一部分数据:
$ bin/sqoop import \
--connect jdbc:mysql://bigdata113:3306/company \
--username root \
--password 000000 \
--table staff_timestamp \
--delete-target-dir \
--hive-import \
--fields-terminated-by "\t" \
--m 1
再增量导入一部分数据:
mysql> insert into company.staff_timestamp (id, name, sex) values(3, 'CCC', 'female');
$ bin/sqoop import \
--connect jdbc:mysql://bigdata113:3306/company \
--username root \
--password 000000 \
--table staff_timestamp \
--check-column last_modified \
--incremental lastmodified \   指定为lastmodified模式
--m 1 \
--last-value "2019-05-17 09:50:12" \
--append    指定为追加模式

尖叫提示1:使用lastmodified方式导入数据要指定增量数据是要--append(追加)还是要--merge-key COLUMN(合并)
尖叫提示2:在Hive中,如果不指定输出路径,可以去看以下两个目录
1.  /user/root(此为用户名)追加模式中会将文件输出到这里
如果需要导入到hive中的表时,需要—target-dir 指定输出目录到hive的目录
2.  /user/hive/warehouse  个人配置的目录,普通模式下会输出到这里
尖叫提示3:last-value指定的值是会包含于增量导入的数据中
注意:lastmodified模式下,如果不指定输出hdfs路径,就是输出到/user/root/TABLE_NAME/ 下,不会再 /user/hive/warehouse/ 下,这点要注意

Last-modified和append的区别:

1、append,在导入的新数据ID值是连续时采用,对数据进行附加 加不加–last-value的区别在于:数据是否冗余,如果不加,则会导入源表中的所有数据导致数据冗余。加了就表示将大于 last-value值的行导入,小于的就不导入。而且作为 –check-column的字段必须是可比较的类型,字符是不行的,通常是数字,比如表的主键id

2、lastmodified,在源表中有数据更新的时候使用,检查列就必须是一个时间戳或日期类型的字段,更新完之后,last-value会被设置为执行增量导入时的当前系统时间,当使用–incremental lastmodified模式进行导入且导入目录已存在时,需要使用–merge-key或–append ,--merge-key会将所有的行根据指定的key进行合并
导入>=last-value的值。--incremental lastmodified --check-column created --last-value '2012-02-01 11:0:00'
就是只导入修改时间 比'2012-02-01 11:0:00'更大的数据,如果存在就覆盖。
--append  就是将指定时间之后的数据追加,不会做覆盖

4.6 export导出数据到mysql

从HDFS(包括Hive和HBase)中将数据导出到关系型数据库中。

从hdfs导出到mysql
sqoop export \
--connect jdbc:mysql://bigdata113:3306/Andy \
--username root \
--password 000000 \
--export-dir /user/hive/warehouse/staff_hive \    hdfs导出路径
--table aca \
--num-mappers 1 \
--input-fields-terminated-by "\t"

4.7 codegen--数据打包成jar

将关系型数据库中的表映射为一个Java类,在该类中有各列对应的各个字段。

sqoop codegen \
--connect jdbc:mysql://bigdata113:3306/company \
--username root \
--password 000000 \
--table staff \
--bindir /opt/Desktop/staff \    
--class-name Staff \
--fields-terminated-by "\t"

用法和import类似,只是把import换成codegen,打包成jar,但是不执行。
默认会在/tmp/sqoop-root/compile/[JOB_ID]/ 生成对应的jar包

常用参数:

参数 说明
--bindir <dir> 指定生成的Java文件、编译成的class文件及将生成文件打包为jar的文件输出路径
--class-name <name> 设定生成的Java文件指定的名称

4.8 create-hive-table创建hive表

用于单独创建hive表

sqoop create-hive-table \
--connect jdbc:mysql://bigdata113:3306/company \
--username root \
--password 000000 \
--table staff \
--hive-table hive_staff1     指定创建的表名

4.9 eval--在命令行下执行sql语句

​ 可以快速的使用SQL语句对关系型数据库进行操作,经常用于在import数据之前,了解一下SQL语句是否正确,数据是否正常,并可以将结果显示在控制台。

sqoop eval \
--connect jdbc:mysql://bigdata113:3306/company \
--username root \
--password 000000 \
--query "SELECT * FROM staff"             查询语句

4.10 import-all-tables导入所有表

可以将RDBMS中的所有表导入到HDFS中,每一个表都对应一个HDFS目录

bin/sqoop import-all-tables \
--connect jdbc:mysql://bigdata113:3306/company \
--username root \
--password 000000 \
--hive-import \
--fields-terminated-by "\t"

4.11 job生成sqoop任务

用来生成一个sqoop任务,生成后不会立即执行,需要手动执行。

生成job
$ bin/sqoop job \
 --create myjob -- import-all-tables \
 --connect jdbc:mysql://bigdata113:3306/company \
 --username root \
 --password 000000

显示job
$ bin/sqoop job \
--list

执行job
$ bin/sqoop job \
--exec myjob

尖叫提示:注意import-all-tables和它左边的--之间有一个空格
尖叫提示:如果需要连接metastore,则--meta-connect 
执行的结果在HDFS:/user/root/ 目录中,即导出所有表到/user/root中

4.12 list-databases和list-tables

显示mysql库:

sqoop list-databases \
--connect jdbc:mysql://bigdata113:3306/ \
--username root \
--password 000000

显示mysql表:

sqoop list-tables \
--connect jdbc:mysql://bigdata113:3306/company \
--username root \
--password 000000

4.13 merge合并hdfs文件

将HDFS中不同目录下面的数据合并在一起并放入指定目录中
数据环境:注意:以下数据自己手动改成\t
new_staff
1 AAA male
2 BBB male
3 CCC male
4 DDD male

old_staff
1 AAA female
2 CCC female
3 BBB female
6 DDD female

尖叫提示:上边数据的列之间的分隔符应该为\t,行与行之间的分割符为\n,如果直接复制,请检查。

开始合并

创建JavaBean:将数据以及任务打包成jar包
$ bin/sqoop codegen \
--connect jdbc:mysql://bigdata113:3306/company \
--username root \
--password 000000 \
--table staff \
--bindir /opt/Desktop/staff \
--class-name Staff \
--fields-terminated-by "\t"

开始合并:注:是hdfs路径,运行上面的jar包.记得将上面的数据文件上传到hdfs
$ bin/sqoop merge \
--new-data /test/new/ \
--onto /test/old/ \
--target-dir /test/merged \
--jar-file /opt/Desktop/staff/Staff.jar \
--class-name Staff \
--merge-key id
结果:
1 AAA MALE
2 BBB MALE
3 CCC MALE
4 DDD MALE
6 DDD FEMALE

参数:

参数 说明
--new-data <path> HDFS 待合并的数据目录,合并后在新的数据集中保留(重复的话这里的数据保留)
--onto <path> HDFS合并后,重复的部分在新的数据集中被覆盖(重复的话这里的数据被覆盖)
--merge-key <col> 合并键,一般是主键ID
--jar-file <file> 合并时引入的jar包,该jar包是通过Codegen工具生成的jar包
--class-name <class> 对应的表名或对象名,该class类是包含在jar包中的
--target-dir <path> 合并后的数据在HDFS里存放的目录
推荐阅读:
  1. 一、Elasticsearch原理与基本使用
  2. Gitlab的安装及基本使用(一)

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

sqoop基本使用 --

上一篇:从零手动实现 java json 序列化框架

下一篇:linux和windows下实现日志写入文件功能

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》