管理信息系统网站建设,陕西网站建设开发,公交车广告,北京定制网站价格目录 一、sqoop基本原理
1.1、何为Sqoop#xff1f;
1.2、为什么需要用Sqoop#xff1f;
1.3、关系图 1.4、架构图
二、Sqoop可用命令
2.1、公用参数#xff1a;数据库连接
2.2、公用参数#xff1a;import
2.3、公用参数#xff1a;export 2.4、公用参数#xff…目录 一、sqoop基本原理
1.1、何为Sqoop
1.2、为什么需要用Sqoop
1.3、关系图 1.4、架构图
二、Sqoop可用命令
2.1、公用参数数据库连接
2.2、公用参数import
2.3、公用参数export 2.4、公用参数hive
2.3、其他命令
三、Sqoop常用命令
3.1、RDBMS HDFS (导入重点)
3.1.1、全表导入
3.1.2、查询导入--query
3.1.3、导入指定列 --columns
3.1.4、where语句过滤
3.1.5、①增量导入 append
3.1.5、②增量导入 lastmodified 3.2、RDBMS HBase
3.3、RDBMS Hive
3.3.1、导入普通表
3.3.2、导入分区表
3.4、Hive/Hdfs RDBMS
3.5、Sqoop Job 一、sqoop基本原理
1.1、何为Sqoop Sqoop(SQL-to-Hadoop)是一款开源的工具主要用于在Hadoop(Hive)与传统的数据库(mysql、postgresql…)间进行数据的传递可以将一个关系型数据库例如 MySQL ,Oracle ,Postgres等中的数据导入到Hadoop的HDFS中也可以将HDFS的数据导出到关系型数据库中。 1.2、为什么需要用Sqoop
我们通常把有价值的数据存储在关系型数据库系统中以行和列的形式存储数据以便于用户读取和查询。但是当遇到海量数据时我们需要把数据提取出来通过MapReduce对数据进行加工获得更符合我们需求的数据。数据的导入和导出本质上是Mapreduce程序充分利用了MR的并行化和容错性。为了能够和HDFS系统之外的数据库系统进行数据交互MapReduce程序需要使用外部API来访问数据因此我们需要用到Sqoop。
1.3、关系图 1.4、架构图 在 mapreduce 中主要是对 inputformat 和 outputformat 进行定制。 Sqoop工具接收到客户端的shell命令或者Java api命令后通过Sqoop中的任务翻译器(Task Translator)将命令转换为对应的MapReduce任务而后将关系型数据库和Hadoop中的数据进行相互转移进而完成数据的拷贝。
二、Sqoop可用命令
命令方法codegen生成与数据库记录交互的代码create-hive-table将表定义导入到Hive中eval评估SQL语句并显示结果export导出一个HDFS目录到一个数据库表help可用命令列表import将一个表从数据库导入到HDFSimport-all-tables从数据库导入表到HDFSimport-mainframe从大型机服务器导入数据集到HDFSjob使用已保存的工作list-databases列出服务器上可用的数据库list-tables列出数据库中可用的表merge合并增量导入的结果metastore运行一个独立的Sqoop转移version显示版本信息对于不同的命令有不同的参数这里给大家列出来了一部分Sqoop操作时的常用参数以供参考需要深入学习的可以参看对应类的源代码本文目前介绍常用的导入、导出的一些命令。
2.1、公用参数数据库连接
参数说明--connect连接关系型数据库的URL--connection-manager指定要使用的连接管理类--driverJDBC的driver class--help打印帮助信息--username连接数据库的用户名--password连接数据库的密码--verbose在控制台打印出详细信息
2.2、公用参数import
参数说明--enclosed-by给字段值前后加上指定的字符--escaped-by对字段中的双引号加转义符--fields-terminated-by设定每个字段是以什么符号作为结束默认为逗号--lines-terminated-by设定每行记录之间的分隔符默认是\n--mysql-delimitersMysql默认的分隔符设置字段之间以逗号分隔行之间以\n分隔默认转义符是\字段值以单引号包裹。--optionally-enclosed-by给带有双引号或单引号的字段值前后加上指定字符。
2.3、公用参数export
参数说明--input-enclosed-by对字段值前后加上指定字符--input-escaped-by对含有转移符的字段做转义处理--input-fields-terminated-by字段之间的分隔符--input-lines-terminated-by行之间的分隔符--input-optionally-enclosed-by给带有双引号或单引号的字段前后加上指定字符2.4、公用参数hive
参数说明--hive-delims-replacement用自定义的字符串替换掉数据中的\r\n和\013 \010等字符--hive-drop-import-delims在导入数据到hive时去掉数据中的\r\n\013\010这样的字符--map-column-hive map生成hive表时可以更改生成字段的数据类型--hive-partition-key创建分区后面直接跟分区名分区字段的默认类型为string--hive-partition-value导入数据时指定某个分区的值--hive-homehive的安装目录可以通过该参数覆盖之前默认配置的目录--hive-import将数据从关系数据库中导入到hive表中--hive-overwrite覆盖掉在hive表中已经存在的数据--create-hive-table默认是false即如果目标表已经存在了那么创建任务失败--hive-table后面接要创建的hive表,默认使用MySQL的表名--table指定关系数据库的表名
2.3、其他命令
命令含义-m N指定启动N个map进程--num-mappers N指定启动N个map进程--query后跟查询的SQL语句--incremental modemodeappend或lastmodified--check-column作为增量导入判断的列名--split-by按照某一列来切分表的工作单元不能与–autoreset-to-one-mapper连用--last-value指定某一个值用于标记增量导入的位置--target-dir指定HDFS路径--delete-target-dir若hdfs存放目录已存在则自动删除
三、Sqoop常用命令
先在mysql中建一张表来使用
create table student(sid int primary key,sname varchar(16) not null,gender enum(女,男) not null default 男,age int not null
);insert into student(sid,sname,gender,age) values
(1,孙尚香,女,15),
(2,貂蝉,女,16),
(3,刘备,男,17),
(4,孙二娘,女,16),
(5,张飞,男,15),
(6,关羽,男,18),3.1、RDBMS HDFS (导入重点)
3.1.1、全表导入
//single为自己虚拟机ip
sqoop import \
--connect jdbc:mysql://single:3306/sqoop_test \
--username root \
--password kb10 \
--table student \
--target-dir /sqooptest/table_all \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by ,登录web界面查看/sqooptest/table_all目录下生成了数据结果或者使用hdfs命令查看数据结果hdfs dfs -cat /sqooptest/table_all/part-m-00000数据结果如下
1,孙尚香,女,15
2,貂蝉,女,16
3,刘备,男,17
4,孙二娘,女,16
5,张飞,男,15
6,关羽,男,183.1.2、查询导入--query
//single为虚拟机ip地址
sqoop import \
--connect jdbc:mysql://single:3306/sqoop_test \
--username root \
--password kb10 \
--target-dir /sqooptest/select_test \
--num-mappers 1 \
--query select sname,gender from student where $CONDITIONSwhere语句中必须有 $CONDITIONS表示将查询结果带回。 如果query后使用的是双引号则 $CONDITIONS前必须加转移符即 \$CONDITIONS防止shell识别为自己的变量。 hdfs dfs -cat /sqooptest/select_test/part-m-00000数据结果如下
孙尚香,女
貂蝉,女
刘备,男
孙二娘,女
张飞,男
关羽,男3.1.3、导入指定列 --columns
sqoop import \
--connect jdbc:mysql://single:3306/sqoop_test \
--username root \
--password kb10 \
--table student \
--columns sid,sname,age \
--target-dir /sqooptest/column_test \
--num-mappers 1 \
--fields-terminated-by |数据结果如下
1|孙尚香|15
2|貂蝉|16
3|刘备|17
4|孙二娘|16
5|张飞|15
6|关羽|183.1.4、where语句过滤
源表数据 sqoop import \
--connect jdbc:mysql://single:3306/sqoop_test \
--username root \
--password kb10 \
--table student \
--where sid6 \
--target-dir /sqooptest/wheretest \
-m 2得到了如下 “sid6” 的数据
[rootsingle ~]# hdfs dfs -cat /sqooptest/wheretest/*
6,关羽,男,18
7,云中君,男,19
8,百里玄策,男,20
3.1.5、①增量导入 append
sqoop import \
--connect jdbc:mysql://single:3306/sqoop_test \
--username root \
--password kb10 \
--query select sid,sname,gender from student where \$CONDITIONS \
--target-dir /sqooptest/add1 \
--split-by sid \
-m 2 \
--incremental append \
--check-column sid \
--last-value 0–split-by 和 -m 结合实现numberReduceTasks并行
后面两句 –check-column sid –last-value 0 结合使用的效果类似于where sid0
MR过程中部分关键信息如下
--sid界限值是0-6
20/11/20 05:17:42 INFO tool.ImportTool: Incremental import based on column sid
20/11/20 05:17:42 INFO tool.ImportTool: Lower bound value: 0
20/11/20 05:17:42 INFO tool.ImportTool: Upper bound value: 6
--条件是where sid 0 AND sid 6
20/11/20 05:17:48 INFO db.DataDrivenDBInputFormat: BoundingValsQuery: SELECT MIN(sid), MAX(sid) FROM (select sid,sname,gender from student where sid 0 AND sid 6 AND (1 1) ) AS t1
--指定了两个maptask
20/11/20 05:17:48 INFO mapreduce.JobSubmitter: number of splits:2
--提示last-value即sid是6
20/11/20 05:18:06 INFO tool.ImportTool: --incremental append
20/11/20 05:18:06 INFO tool.ImportTool: --check-column sid
20/11/20 05:18:06 INFO tool.ImportTool: --last-value 6数据结果如下
1,孙尚香,女
2,貂蝉,女
3,刘备,男
4,孙二娘,女
5,张飞,男
6,关羽,男此时往mysql中再添加几条数据再进行一次增量导入
insert into student(sid,sname,gender,age) values(7,云中君,男,19),(8,百里玄策,男,20),(9,裴擒虎,男,17);再次执行增量导入语句
sqoop import \
--connect jdbc:mysql://single:3306/sqoop_test \
--username root \
--password kb10 \
--query select sid,sname,gender,age from student where \$CONDITIONS \
--target-dir /sqooptest/add1 \
-m 1 \
--incremental append \
--check-column sid \
--last-value 6hdfs dfs -cat /sqooptest/add1/part-m-*1,孙尚香,女
2,貂蝉,女
3,刘备,男
4,孙二娘,女
5,张飞,男
6,关羽,男
7,云中君,男,19
8,百里玄策,男,20
9,裴擒虎,男,173.1.5、②增量导入 lastmodified
先在mysql创建一张新表
create table orderinfo(oid int primary key,oName varchar(10) not null,oPrice double not null,oTime timestamp not null
);insert into orderinfo(oid,oName,oPrice,oTime) values
(1,爱疯12,6500.0,2020-11-11 00:00:00),
(2,华为xpro,12000.0,2020-10-1 12:52:33),
(3,行李箱,888.8,2019-5-22 21:56:17),
(4,羽绒服,1100.0,2018-3-7 14:22:31);将数据传到hdfs
sqoop import \
--connect jdbc:mysql://single:3306/sqoop_test \
--username root \
--password kb10 \
--table orderinfo \
--target-dir /sqooptest/lastmod \
-m 1往mysql的orderinfo表中新插入几条数据然后增量导入
insert into orderinfo(oid,oName,oPrice,oTime) values
(5,帕拉梅拉,1333333.3,2020-4-7 12:23:34),
(6,保温杯,86.5,2017-3-5 22:52:16),
(7,枸杞,46.3,2019-10-5 11:11:11),
(8,电动牙刷,350.0,2019-9-9 12:21:41);–incremental lastmodified修改和增加 此时搭配–check-column 必须为timestamp类型
使用lastmodified方式导入数据要指定增量数据是要–append追加还是要–merge-key合并
sqoop import \
--connect jdbc:mysql://single:3306/sqoop_test \
--username root \
--password kb10 \
--table orderinfo \
--target-dir /sqooptest/lastmod \
-m 1 \
--incremental lastmodified \
--check-column oTime \
--merge-key oid \
--last-value 2019-10-1 12:12:12数据结果如下
1,爱疯12,6500.0,2020-11-11 00:00:00.0
2,华为xpro,12000.0,2020-10-01 12:52:33.0
3,行李箱,888.8,2019-05-22 21:56:17.0
4,羽绒服,1100.0,2018-03-07 14:22:31.0
5,帕拉梅拉,1333333.3,2020-04-07 12:23:34.0
7,枸杞,46.3,2019-10-05 11:11:11.0发现只添加了两条记录因为序号为6和8的记录的时间不在–last-value的范围内 3.2、RDBMS HBase
先在hbase中建表
hbase(main):007:0 create sqooptest:sqstudent,stuinfo使用sqoop开始导入数据
sqoop import \
--connect jdbc:mysql://single:3306/sqoop_test \
--username root \
--password kb10 \
--table student \
--hbase-table sqooptest:sqstudent \
--column-family stuinfo \
--hbase-create-table \
--hbase-row-key sid–column-family stuinfo 指定列族为stuinfo –hbase-create-table 若表不存在则自动创建 –hbase-row-key sid 指定行键为sid 查看hbase表数据
hbase(main):008:0 scan sqooptest:sqstudent
ROW COLUMNCELL 1 columnstuinfo:age, timestamp1605958889301, value15 1 columnstuinfo:gender, timestamp1605958889301, value\xE5\xA5\xB3 1 columnstuinfo:sname, timestamp1605958889301, value\xE5\xAD\x99\xE5\xB0\x9A\xE9\xA6\x99 2 columnstuinfo:age, timestamp1605958889301, value16 2 columnstuinfo:gender, timestamp1605958889301, value\xE5\xA5\xB3 2 columnstuinfo:sname, timestamp1605958889301, value\xE8\xB2\x82\xE8\x9D\x89
...
...
... 9 columnstuinfo:age, timestamp1605958892765, value17 9 columnstuinfo:gender, timestamp1605958892765, value\xE7\x94\xB7 9 columnstuinfo:sname, timestamp1605958892765, value\xE8\xA3\xB4\xE6\x93\x92\xE8\x99\x8E
9 row(s) in 0.1830 secondsHBase中的数据没有数据类型统一存储为字节码是否显示具体的汉字只是前端显示问题此处没有解决因此gender和sname字段显示的都是字节码 3.3、RDBMS Hive
3.3.1、导入普通表
将mysql中retail_db库下的orders表导入hive
sqoop import \
--connect jdbc:mysql://single:3306/retail_db \
--driver com.mysql.jdbc.Driver \
--username root \
--password kb10 \
--table orders \
--hive-import \
--hive-database sqooptest \
--create-hive-table \
--hive-table orders \
--hive-overwrite \
-m 33.3.2、导入分区表
sqoop import \
--connect jdbc:mysql://single:3306/retail_db \
--driver com.mysql.jdbc.Driver \
--username root \
--password kb10 \
--query select order_id,order_status from orders where
order_date2014-07-02 and order_date2014-07-03 and \$CONDITIONS \
--hive-import \
--hive-database sqooptest \
--hive-table order_partition \
--hive-partition-key order_date \
--hive-partition-value 2014-07-02 \
-m 13.4、Hive/Hdfs RDBMS
先在mysql中建表
create table hiveTomysql(sid int primary key,sname varchar(5) not null,gender varchar(1) default 男,age int not null
);我们把刚才在hive中创建的sqstudent表数据再导出到mysql中
sqoop export \
--connect jdbc:mysql://single:3306/sqoop_test \
--username root \
--password kb10 \
--table hiveTomysql \
--num-mappers 1 \
--export-dir /opt/software/hadoop/hive110/warehouse/sqstudent/part-m-00000 \
--input-fields-terminated-by ,3.5、Sqoop Job
job参数说明
ArgumentDescription–create JOB_NAME创建job参数–delete JOB_NAME删除一个job–exec JOB_NAME执行一个job–help显示job帮助–list显示job列表–help显示job帮助–meta-connect jdbc-uri用来连接metastore服务–show JOB_NAME显示一个job的信息–verbose打印命令运行时的详细信息
创建job - -和import之间有个空格。这里–空格之后表示给job添加参数而恰好import又不需要–所以这个空格很容易被忽略。
sqoop job --create myjob \
-- import \
--connect jdbc:mysql://single:3306/sqoop_test \
--username root \
--password kb10 \
--table student \
--target-dir /sqooptest/myjob \
-m 1 \
--lines-terminated-by \n \
--null-string \\N \
--null-non-string \\N查看job
sqoop job --list显示job
sqoop job --show myjob删除job
sqoop job --delete myjob执行job
sqoop job --exec pwdjob
