投稿
  1. 极客之音首页
  2. 后端笔记

Sqoop操作

seven_ • • 后端笔记 • 阅读 173

导读：本篇文章讲解 Sqoop操作，希望对大家有帮助，欢迎收藏，转发！站点地址：www.bmabk.com

文章目录

前言

sqoop-1.4.7的数据导入导出相关操作。

一、Sqoop操作相关参数解释

1.1 通用参数

–connect

作用：指定JDBC连接字符串

–driver

作用：手动指定连接数据库时的驱动名

–username

作用：指定访问数据库的用户名

–password

作用：指定访问数据库的密码

1.2 import控制参数

–append

作用：通过追加的方式导入到HDFS

–delete-target-dir

作用：如果目标文件夹存在，则删除

–columns

作用：指定需要导入的列，可以指定多列，列与列之间用“,”进行隔开

–target-dir

作用：指定HDFS的目标文件夹,若路径不存在时会自动创建

–split-by

作用：当表没有主键列时，手动指定需要拆分的列。

–table

作用：指定需要导入到hdfs的mysql中的表名

–target-dir

作用：指定需要导入的hdfs目录

–where

作用：作为query语句后的过滤条件

$CONDITIONS

作用：在query查询语句中的where过滤条件中，必须包含$CONDITIONS，其本身是没有实际意义的。

–fields-terminated-by

作用：指定导出文件的列与列之间的分隔符，默认分隔符为“,”

-e,–query

作用：指定查询语句

注意：–query不建议和–where、–columns一起使用

-z,–compress

作用：是否要进行压缩

-m

作用：指定用于执行导入的map任务的数量，默认为4。默认情况下，Sqoop会将表中的主键id列作为拆分列，然后再将其分成大小均匀的几个部分。如果当一个表没有主键时，那么默认m=4进行切分就会报错，那么此时可以通过将m设置为1来避免报错。m为1表示只使用一个Mapper进行运算，不进行数据切分。

切分后的每部分大小计算：（指定字段的最大值-最小值+1）/ m。

1.3 导入到Hive命令参数

–hive-home

作用：指定环境配置的$HIVE_HOME

–hive-import

作用：指定导入数据到Hive中

–hive-overwrite

作用：覆盖当前已有的数据

–create-hive-table

作用：是否创建hive表，如果已经存在则会失败。

–hive-table

作用：设置要导入的hive的表名

二、MySQL -> HDFS

数据准备

    -- 在mysql建表
    CREATE TABLE cq01 ( id INT PRIMARY KEY, NAME VARCHAR ( 50 ), job VARCHAR ( 50 ), height INT );
    -- 插入数据
    INSERT INTO cq01 VALUES(1,'les','歌手',173);
    INSERT INTO cq01 VALUES(2,'hin','歌手',175);
    INSERT INTO cq01 VALUES(3,'eas','歌手',177);
    INSERT INTO cq01 VALUES(4,'xin','歌手',176);
    INSERT INTO cq01 VALUES(5,'hua','歌手',187);
    INSERT INTO cq01 VALUES(6,'zuo','歌手',167);
    INSERT INTO cq01 VALUES(7,'hong','歌手',177);
    INSERT INTO cq01 VALUES(8,'xu','导演',173);
    INSERT INTO cq01 VALUES(9,'teng','演员',167);
    INSERT INTO cq01 VALUES(10,'jj','歌手',197);

将表导入HDFS

    [root@cq01 ~]# sqoop import --connect jdbc:mysql://cq03:3306/cq01 \
    > --username root \
    > --password 123456 \
    > --table cq01 \
    > --target-dir hdfs://cq01:9820/sqoopdata/cq01 \
    > --delete-target-dir
    
    # 使用HDFS命令，快速查看结果
    [root@cq01 ~]# hdfs dfs -cat /sqoopdata/cq01/*

注意：MySQL表没有指定主键的时候需要使用–split-by来手动指定要分片的列。否则系统报错。

将指定列导入

    # 使用--columns进行指定
    [root@cq01 ~]# sqoop import --connect jdbc:mysql://cq03/cq01 \
    > --username root \
    > --password 123456 \
    > --table cq01 \
    > --columns 'id,name' \
    > --target-dir hdfs://cq01:9820/sqoopdata/cq01 \
    > --delete-target-dir
    # 使用hdfs命令进行快速查看
    [root@cq01 ~]# hdfs dfs -cat /sqoopdata/cq01/*

指定条件导入

    # 使用--where进行条件过滤
    [root@cq01 ~]# sqoop import --connect jdbc:mysql://cq03:3306/cq01 \
    > --username root \
    > --password 123456 \
    > --table cq01 \
    > --columns 'id,name' \
    > --where 'id < 5' \
    > --target-dir hdfs://cq01:9820/sqoopdata/cq01 \
    > --delete-target-dir

    # 使用hdfs命令进行快速查看
    [root@cq01 ~]# hdfs dfs -cat /sqoopdata/cq01/*

指定sql语句导入

    # 使用--query进行指定自定义sql语句
    [root@cq01 ~]# sqoop import --connect jdbc:mysql://cq03:3306/cq01 \
    > --username root \
    > --password 123456 \
    > --query 'select id,name from cq01 where id > 5 and $CONDITIONS' \
    > --target-dir hdfs://cq01:9820/sqoopdata/cq01 \
    > --delete-target-dir \
    > --split-by id \
    > -m 1

    # 使用hdfs命令进行快速查看
    [root@cq01 ~]# hdfs dfs -cat /sqoopdata/cq01/*

>  注意：此处--query必须加上$CONDITIONS。否则报错。不建议和--where、--columns一起使用，可能会出现报错。

几种命令情形以及运行结果

情形1：直接报错。
–query语句后不跟$CONDITIONS

    [root@cq01 ~]# sqoop import --connect jdbc:mysql://cq03:3306/cq01 \
    > --username root \
    > --password 123456 \
    > --query 'select id,name from cq01 where id > 5' \
    > --target-dir hdfs://cq01:9820/sqoopdata/cq01 \
    > --delete-target-dir \
    > --split-by id \
    > -m 1

情形2：直接报错
–query后加上–where和–columns,且–columns指定的列与–query指定的列不相同时。同时这里过滤条件为–query后的where子句条件，而不是–where指定的过滤条件。

   [root@cq01 ~]# sqoop import --connect jdbc:mysql://cq03:3306/cq01 \
    > --username root \
    > --password 123456 \
    > --query 'select id,job from cq01 where id > 5 and $CONDITIONS' \
    > --columns 'id,name' \
    > --where 'id < 5' \
    > --target-dir hdfs://cq01:9820/sqoopdata/cq01 \
    > --delete-target-dir \
    > --split-by id \
    > -m 1

情形3：可以通过并执行。
–query后的where子句不加过滤条件，而是在–where后指定过滤条件。但是在执行时–where指定的条件不会进行过滤。而是直接将所有的数据进行导入。

   [root@cq01 ~]# sqoop import --connect jdbc:mysql://cq03:3306/cq01 \
   > --username root \
   > --password 123456 \
   > --query 'select id,name from cq01 where $CONDITIONS' \
   > --columns 'id,name' \
   > --where 'id = 5' \
   > --target-dir hdfs://cq01:9820/sqoopdata/cq01 \
   > --delete-target-dir \
   > --split-by id \
   > -m 1

情形4：直接报错
–query语句后不加where子句。而是通过–where进行指定条件

    [root@cq01 ~]# sqoop import --connect jdbc:mysql://cq03:3306/cq01 \
    > --username root \
    > --password 123456 \
    > --query 'select id,name from cq01 $CONDITIONS' \
    > --columns 'id,name' \
    > --where 'id = 5' \
    > --target-dir hdfs://cq01:9820/sqoopdata/cq01 \
    > --delete-target-dir \
    > --split-by id \
    > -m 1

情形5：直接报错
当没有使用–split-by进行指定拆分列而且m不是0时。系统会报错。但是当没有使用–split-by时，但是指定了m=1，此时系统不会报错。

    [root@cq01 ~]# sqoop import --connect jdbc:mysql://cq03:3306/cq01 \
    > --username root \
    > --password 123456 \
    > --query 'select id,name from cq01 where id > 5 and $CONDITIONS' \
    > --target-dir hdfs://cq01:9820/sqoopdata/cq01 \
    > --delete-target-dir

注意：

在–query后指定sql语句时使用单引号和双引号是有区别的。

单引号：

--query 'select id,name from cq01 where id > 5 and $CONDITIONS'

双引号：

--query "select id,name from cq01 where id > 5 and \$CONDITIONS"

单引号在$CONDITIONS前不用加反斜杠，而双引号在$CONDITIONS前需要加反斜杠。这是因为使用双引号后，sqoop会在解析的时候进行转义的解析，所以必须加上转义字符反斜杠。而默认的字符引号就是单引号，所以在使用单引号的时候，sqoop解析就按照字面量来解析，所以不需要加反斜杠。

总结

结果上述几种情形可以得出一下结论：

  1. –query进行自定义sql语句指定后，where子句后一定要加$CONDITIONS，否则报错。
  2. –query不建议和–columns一起使用。因为如果二者指定的列不一致，那么就会报错。如果指定同样的列那为什么还要再多写个–columns呢
  3. –query不建议和–where一起使用。因为写了–where后，虽然系统不会报错，可以执行命令。但是系统根本不认–where后指定的过滤条件，写了也是白写那为什么还要写。
  4. 当m不等于1时，必须使用–split-by指定拆分列。否则报错。
  5. 使用–query来导入数据时，必须指定–target-dir。

三、HDFS 与 MySql之间数据传输示例

整表导入、导出

    # 用于数据存放的表需要在MySQL中提前创建
    CREATE TABLE cq02 like cq01;
    # 创建临时数据表，并添加数据
    CREATE TABLE tmp_cq02 like cq01;
    # 导入到HDFS上
    [root@cq01 ~]# sqoop import --connect jdbc:mysql://cq03:3306/cq01 \
    > --username root \
    > --password 123456 \
    > --table tmp_cq02 \
    > --driver com.mysql.cj.jdbc.Driver \
    > --target-dir /sqoop01/tmp_cq02 \
    > --fields-terminated-by '\t' \
    > --null-string '\\N' \
    > --null-non-string '0';
    
    sqoop import --connect jdbc:mysql://cq03:3306/cq01 \
    --username root \
    --password 123456 \
    --table tmp_cq02 \
    --driver com.mysql.cj.jdbc.Driver \
    --target-dir /sqoop01/tmp_cq02 \
    --fields-terminated-by '\t' \
    --null-string '\\N' \
    --null-non-string '0';
    
    # 导出到MySQL表中
    [root@cq01 ~]# sqoop export --connect jdbc:mysql://cq03:3306/cq01 \
    > --username root \
    > --password 123456 \
    > --table cq02 \
    > --driver com.mysql.cj.jdbc.Driver \
    > --export-dir '/sqoop01/tmp_cq02/*' \
    > --input-fields-terminated-by '\t' \
    > --input-null-string '\\N' \
    > --input-null-non-string '0' \
    > -m 1;
    
    sqoop export --connect jdbc:mysql://cq03:3306/cq01 \
    --username root \
    --password 123456 \
    --table cq02 \
    --driver com.mysql.cj.jdbc.Driver \
    --export-dir '/sqoop01/tmp_cq02/*' \
    --input-fields-terminated-by '\t' \
    --input-null-string '\\N' \
    --input-null-non-string '0' \
    -m 1;
    
    # 查询效果
    select * from cq02;

部分数据导入、导出

    # 将数据部分导入到HDFS
    [root@cq01 ~]# sqoop import --connect jdbc:mysql://cq03:3306/cq01 \
    > --username root \
    > --password 123456 \
    > --query 'select id,name from cq02 where id < 3 and $CONDITIONS' \
    > --driver com.mysql.cj.jdbc.Driver \
    > --delete-target-dir \
    > --target-dir '/sqoop01/tmp_cq03' \
    > --split-by id \
    > -m 1 \
    > --fields-terminated-by '\t' \
    > --null-string '\\N' \
    > --null-non-string '0';
    # 查看HDFS上的数据
    [root@cq01 ~]# hdfs dfs -cat /sqoop01/tmp_cq03/*;
    
    # 先在MySQL中创建需要存放数据的表cq03
    mysql> create table cq03 like cq02;
    # 将HDFS上的数据导出到MySQL
    [root@cq01 ~]# sqoop export --connect jdbc:mysql://cq03:3306/cq01 \
    > --username root \
    > --password 123456 \
    > --table cq03 \
    > --driver com.mysql.jdbc.Driver \
    > --export-dir '/sqoop01/tmp_cq03/*' \
    > --input-fields-terminated-by '\t' \
    > --input-null-string '\\N' \
    > --input-null-non-string '\\N' \
    > -m 1

    # 查看数据
    mysql> select * from cq03;

总结

  • 最好使用–input-fields-terminated-by进行分隔符指定
  • –export-dir是一个hdfs中的目录，不识别_SUCCESS文件
  • 使用–query时，要注意其他参数的设置
  • 导出数据中有些列值有“null”，会报无法解析，需要使用相关参数进行设置
  • 导出数据的类型要和MySQL中定义的类型一致。

四、MySQL -> Hive

    # 导入到Hive中示例
    [root@cq01 ~]# sqoop import --connect jdbc:mysql://cq03:3306/cq01 \
    > --username root \
    > --password 123456 \
    > --table cq01 \
    > --hive-import \
    > --hive-overwrite \
    > --hive-table "cq15" \
    > --hive-database cq01 \
    > -m 1
    
    #在Hive中查看数据是否导入
    hive (cq01)> show tables;
    hive (cq01)> select * from cq15;

五、Hive -> MySql

因为Hive的数据是存在HDFS上的，所以Hive导出到MySQL实质上和HDFS导出到MySQL是一样的。这里就不在演示，可以参考上面HDFS导出示例。

总结

以上就是Sqoop相关数据导入导出的命令操作了，有啥没写到的地方之后整理的时候再补上吧。

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/116577.html

赞 (0)
seven_的头像seven_bm
0 0

相关推荐

发表回复

登录后才能评论
极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！