目录

  • Sqoop介绍
  • sqoop数据传输
  • 一、RDBMS —> HDFS
  • a.增量导入-incremental append
  • b.增量导入-incremental lastmodified
  • 二、RDBMS —> HBASE
  • 单列族传输
  • 多列族传输
  • 三、RDBMS —> HIVE
  • 四、Hive/Hdfs—> RDBMS
  • 五、Hbase数据映射Hive


Sqoop介绍

  • Sqoop是一个用来将关系型数据库和Hadoop中的数据进行相互转移的工具，可以将一个关系型数据库(例如Mysql、Oracle)中的数据导入到Hadoop(例如HDFS、Hive、Hbase)中，也可以将Hadoop(例如HDFS、Hive、Hbase)中的数据导入到关系型数据库(例如Mysql、Oracle)中。

Android OSS断点续传 任务会重新开始 sqoop断点续传_mysql

  • Sqoop工具接收到客户端的shell命令或者Java api命令后，通过Sqoop中的任务翻译器(Task Translator)将命令转换为对应的MapReduce任务，而后将关系型数据库和Hadoop中的数据进行相互转移，进而完成数据的拷贝。

sqoop数据传输

一、RDBMS —> HDFS

a.增量导入-incremental append

        incremental append -->只支持新增不支持更新;

        先将服务启动好之后进行如下操作，这里演示从关系型数据库传输数据至HDFS：

  • 实例演示
sqoop import \
--connect jdbc:mysql://single:3306/mysqltest \
--username root \
--password root \
--query "select sid,student_id,course_id,score from score where \$CONDITIONS"  \
--target-dir /kb10/mysqlscore1119 \
--split-by sid \
-m 2 \
--check-column sid \
--incremental append \
--last-value 0
  • 代码翻译:

    *  表示从系型数据库传输数据至HDFS( import)；
    *  表示连接到mysql的mysqtest数据库(图中的single为服务器的hostname,也可写为ip地址，使用hostname的前提是地址与
       hostname做过关联配置)；
    *  表示连接数据库的用户名；
    *  表示连接数据库的密码；
    *  query为请求的查询语句，后面的$CONDITIONS表示引用查询到的输出结果；
    *  表示数据导出的目标路径；
    *  这里的split-by与下一行相配合，图上为sid为分割，不同的sid将进行Hash操作之后取余mapreduce的数量，最后的结果决定了
       进入哪一个mapreduce内（也可以写成--num-mappers 2 \）；
    *  表示启动mapredce的数量；
    *  从这一行到最后一行配合使用，可以实现增量插入数据，相当于where sid >0；
    *  表示使用incremental append增量导入操作；
    *  表示增量导入的开始行，这里是第一次导入所以也是全量导入；

  • 实例结果展示

        运行时代码截图展示，可以见到上下边界值的大小，下边界作为下一次增量导入的上边值；

20/11/19 18:39:23 INFO tool.ImportTool: Incremental import based on column `sid`
20/11/19 18:39:23 INFO tool.ImportTool: Lower bound value: 0
20/11/19 18:39:23 INFO tool.ImportTool: Upper bound value: 60
20/11/19 18:39:23 INFO mapreduce.ImportJobBase: Beginning query import.
  • 结果展示
            落盘文件数量为2，对应的启动的mapreduce数量，数据查询到第60行结束；

Android OSS断点续传 任务会重新开始 sqoop断点续传_数据_02

1,1,1,60
2,1,2,59
3,1,3,58
4,1,4,22
5,1,5,59

...
...
58,11,7,99
59,12,3,100
60,12,8,60

        下一次再次进行增量导入时，将之前的到的下边界值作为条件写入语句中；

  • 实例演示
sqoop import \
--connect jdbc:mysql://single:3306/mysqltest \
--username root \
--password root \
--query "select sid,student_id,course_id,score from score where \$CONDITIONS"  \
--target-dir /kb10/mysqlscore1119 \
--split-by sid \
-m 1 \
--check-column sid \
--incremental append \
--last-value 60
  • 代码翻译:

    *  注意最后的last-value，此时的value值是上一次全量导入后返回的下边界值；最后三行相当于where sid >60

  • 实例结果展示

        运行时代码截图展示，可以见到上下边界值是60和68；

20/11/19 18:59:13 INFO tool.ImportTool: Incremental import based on column `sid`
20/11/19 18:59:13 INFO tool.ImportTool: Lower bound value: 60
20/11/19 18:59:13 INFO tool.ImportTool: Upper bound value: 68
20/11/19 18:59:13 INFO mapreduce.ImportJobBase: Beginning query import.
  • 实例结果展示

        因为这一次我设置的mapreduce数量为1，所以最后落盘也只增加了一个文件，最后查看数据只到第68行数据；

Android OSS断点续传 任务会重新开始 sqoop断点续传_sqoop_03

1,1,1,60
2,1,2,59
3,1,3,58
4,1,4,22
5,1,5,59
...
...
66,12,5,49
67,12,6,82
68,12,7,77

b.增量导入-incremental lastmodified

        incremental lastmodified -->修改和增加

        check-column 必须为timestamp类型,如果导入的表数据中没有date(time)类型的，那么这种方式将无法使用；

  • 实例演示
sqoop import \
--connect jdbc:mysql://single:3306/mysqltest \
--username root \
--password root \
--query "select id,name,time from lmtest where \$CONDITIONS"  \
--target-dir /kb10/lm1118 \
--split-by id \
-m 1 \
--check-column time \
--incremental lastmodified \
--merge-key id \
--last-value '2020-11-19 12:24:12'
  • 代码解释:

    *  与上面的append增量导入有所区别在于，check-column必须为date(time)类型。此时更新的数据是根据时间字段更新数据。
    *  更新的是incremental.last.value中的值所表示的时间到当前系统时间的时间段的数据。

  • 实例结果展示

        运行时代码截图展示，可以见到上下边界时间；

20/11/19 17:36:27 INFO tool.ImportTool: Incremental import based on column `time`
20/11/19 17:36:27 INFO tool.ImportTool: Lower bound value: '2020-11-19 12:24:12'
20/11/19 17:36:27 INFO tool.ImportTool: Upper bound value: '2020-11-19 17:36:27.0'
20/11/19 17:36:27 INFO mapreduce.ImportJobBase: Beginning query import.
1,henry,2020-11-19 17:24:12.0
2,curry,2020-11-19 17:24:12.0
3,paul,2020-11-19 17:24:12.0
4,alex,2020-11-19 17:24:12.0

二、RDBMS —> HBASE

        利用hbase作为hive的数据源，因为hbase延时低，数据吞吐量大，所以从MySQL传输至Hbase需要重点掌握！

单列族传输

        先在Hbase内建表；

hbase(main):004:0> create 'stu:mysql_stu2','info','score'
0 row(s) in 2.3930 seconds

=> Hbase::Table - stu:mysql_stu2
  • 实例演示
sqoop import \
--connect jdbc:mysql://single:3306/mysqltest \
--username root \
--password root \
--table score \
--hbase-table stu:mysql_stu2 \
--column-family score \
--hbase-create-table \
--hbase-row-key sid \
--hbase-bulkloa
  • 代码解释:

    *  表示从关系型数据库传输数据至hbase(import)；
    *  表示连接到mysql的mysqtest数据库；
    *  表示连接数据库的用户名；
    *  表示连接数据库的密码；
    *  表示连接mysqltest数据库的表score；
    *  数据传输至hbase的表；
    *  column-family指定列族；
    *  表示如果表不存在会自动创建表；
    *  指定hbase表的row-key；
    *  hbase-bulkloa：不用指定输出的路径，自动数据转移；

  • 实例结果展示
hbase(main):001:0> scan 'stu:mysql_stu2'
ROW                        COLUMN+CELL                                                                
 1                         column=score:course_id, timestamp=1605785555916, value=1                   
 1                         column=score:score, timestamp=1605785555916, value=60                      
 1                         column=score:student_id, timestamp=1605785555916, value=1                  
 10                        column=score:course_id, timestamp=1605785555916, value=1                   
 10                        column=score:score, timestamp=1605785555916, value=99       
 ...
 ...
 8                         column=score:student_id, timestamp=1605785555916, value=1                  
 9                         column=score:course_id, timestamp=1605785555916, value=9                   
 9                         column=score:score, timestamp=1605785555916, value=88                      
 9                         column=score:student_id, timestamp=1605785555916, value=1                  
64 row(s) in 0.3630 seconds

多列族传输

        以下均是从同一MySQL的表中传输至hbase上同一表中的不同列族中的操作；
        如果数据不在同一张表中，可以考虑做成视图统一从视图提取数据，这样可以保证数据的一致性，不然可能导致数据错乱的问题；

        先在Hbase中建表；

hbase(main):001:0>create 'kb10:mysql_school','basicinfo','classinfo','scoreinfo'
0 row(s) in 2.3930 seconds

=> Hbase::Table - kb10:mysql_school
  • 实例演示
            这里也可以使用query进行传输，但是使用query需要将–hbase-bulkload删除，否则会出错！
sqoop import \
--connect jdbc:mysql://single:3306/school \
--username root \
--password root \
--table stuscore_info \
--columns id,stuname,stugender,province,city,district \
--hbase-table kb10:mysql_school \
--column-family basicinfo \
--hbase-row-key id \
--split-by id \
--num-mappers 1 \
--hbase-bulkload
sqoop import \
--connect jdbc:mysql://single:3306/school \
--username root \
--password root \
--table stuscore_info \
--columns id,proname,classname,opendate \
--hbase-table kb10:mysql_school \
--column-family classinfo \
--hbase-row-key id \
--split-by id \
--num-mappers 1 \
--hbase-bulkload
sqoop import \
--connect jdbc:mysql://single:3306/school \
--username root \
--password root \
--table stuscore_info \
--columns id,subname,score \
--hbase-table kb10:mysql_school \
--column-family scoreinfo \
--hbase-row-key id \
--split-by id \
--num-mappers 1 \
--hbase-bulkload
  • 代码解释:

    *  与上面的单列族导入类似，三次导入不同的是MySQL表的字段名，还有对应hbase的列族名；值得一提的是，三次导入时的rowkey必须一致，保证了数据的准确;

        因为hbase中文显示乱码问题，这里就不放实例结果了，可以将数据映射Hive，Hive查询并操作数据。(见第四项的映射操作)

三、RDBMS —> HIVE

        先在hive中建表，建表时设置的字段分割要和sqoop导入时设置的字段分割一致，不然即使显示导入成功，但是查询数据字段数据都为null。

create table student(
sid int,
sname string,
gender string,
class_id int)
row format delimited
fields terminated by '\t'
stored as textfile
  • 实例演示
sqoop import \
--connect jdbc:mysql://192.168.182.130:3306/mysqltest \
--username root \
--password root \
-m 1 \
--table student \
--hive-database 'myhive' \
--hive-import \
--hive-overwrite \
--direct \
--hive-table 'student' \
--null-string '\\ N' \
--null-non-string '\\ N' \
--fields-terminated-by '\t'
  • 实例结果展示(图片来自zeppelin截图)

Android OSS断点续传 任务会重新开始 sqoop断点续传_数据_04

四、Hive/Hdfs—> RDBMS

        先在MySql内建表；

create table hive_shop(
	id int,
	name varchar(50),
	mobile varchar(20),
	address varchar(100),
	vol2020 int
);
  • 实例演示
sqoop export \
--connect jdbc:mysql://single:3306/mysqtest\
--username root \
--password root \
--table hive_shop \
--columns id,name,mobile,address,vol2020 \
--fields-terminated-by ',' \
--export-dir '/kb10/shop1118/000000_0'
  • 代码解释:

    *  表示从HIVE/HDFS传输数据至关系型数据库；(import)
    *  表示连接到mysql的mysqtest数据库；
    *  表示连接数据库的用户名；
    *  表示连接数据库的密码；
    *  表示导入至数据库的表名；
    *  表示数据库表中相应的字段；
    *  字段间隔以“，”隔开；
    *  表示数据导入所在的目录；

  • 实例结果展示
+----+--------------+-------------+-------------------------+---------+
| id | name         | mobile      | address                 | vol2020 |
+----+--------------+-------------+-------------------------+---------+
|  1 | 紫燕百味鸡     | 18014499655 | 江苏南京雨花小行路288号 | 1000000 |
|  2 | 如家快捷酒店   | 13987499234 | 江苏南京雨花小行路28号  | 1259800 |
|  3 | 苏果超市      | 13814987651  | 江苏南京雨花小行路06号  | 1123000 |
|  4 | 罗森          | 14766848644 | 江苏南京雨花小行路08号  | 1000000 |
+----+--------------+-------------+-------------------------+---------+

五、Hbase数据映射Hive

  • 建立映射对应关系
  • 实例演示（对应第二点的MySQL传输至Hbase多列族）
CREATE EXTERNAL TABLE mysql_school(id string,stuname string,stugender string,province string,city string,district string,classname string,proname string,opendate string,subname string,score int)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,basicinfo:stuname,basicinfo:stugender,basicinfo:province,basicinfo:city,basicinfo:district,classinfo:classname,classinfo:proname,classinfo:opendate,scoreinfo:subname,scoreinfo:score")
TBLPROPERTIES ("hbase.table.name" = "test:mysql_school")
  • 实例结果展示（进入Hive查询数据）

PS：如果有写错或者写的不好的地方，欢迎各位大佬在评论区留下宝贵的意见或者建议，敬上！如果这篇博客对您有帮助，希望您可以顺手帮我点个赞！不胜感谢！



原创作者：wsjslient