更新时间:2023-03-17 GMT+08:00

从零开始使用Sqoop

Sqoop是一款开源的工具,主要用于在Hadoop(Hive)与传统的数据库(MySQL、PostgreSQL...)间进行数据的传递,可以将一个关系型数据库(例如:MySQL、Oracle、PostgreSQL等)中的数据导进到Hadoop的HDFS中,也可以将HDFS的数据导进到关系型数据库中。

前提条件

  • MRS 3.1.0及之后版本在创建集群时已勾选Sqoop组件。
  • 安装客户端,具体请参考安装客户端(3.x及之后版本)。例如安装目录为“/opt/client”,以下操作的客户端目录只是举例,请根据实际安装目录修改。

sqoop export (HDFS 到 MySQL)

  1. 登录客户端所在节点。
  2. 执行如下命令初始化环境变量。

    source /opt/client/bigdata_env

  3. 使用sqoop命令操作sqoop客户端。

    sqoop export --connect jdbc:mysql://10.100.231.134:3306/test --username root --password xxxxxx --table component13 -export-dir hdfs://hacluster/user/hive/warehouse/component_test3 --fields-terminated-by ',' -m 1

    表1 参数说明

    参数

    说明

    -direct

    快速模式,利用了数据库的导入工具,如MySQL的mysqlimport,可以比jdbc连接的方式更为高效的将数据导入到关系数据库中。

    -export-dir <dir>

    存放数据的HDFS的源目录。

    -m或-num-mappers <n>

    启动n个map来并行导入数据,默认是4个,该值请勿高于集群的最大Map数。

    -table <table-name>

    要导入的目的关系数据库表。

    -update-key <col-name>

    后面接条件列名,通过该参数可以将关系数据库中已经存在的数据进行更新操作,类似于关系数据库中的update操作。

    -update-mode <mode>

    更新模式,有两个值updateonly和默认的allowinsert,该参数只能在关系数据表里不存在要导入的记录时才能使用,比如要导入的hdfs中有一条id=1的记录,如果在表里已经有一条记录id=2,那么更新会失败。

    -input-null-string <null-string>

    可选参数,如果没有指定,则字符串null将被使用。

    -input-null-non-string <null-string>

    可选参数,如果没有指定,则字符串null将被使用。

    -staging-table <staging-table-name>

    创建一个与导入目标表同样数据结构的表,将所有数据先存放在该表中,然后由该表通过一次事务将结果写入到目标表中。

    该参数是用来保证在数据导入关系数据库表的过程中的事务安全性,因为在导入的过程中可能会有多个事务,那么一个事务失败会影响到其它事务,比如导入的数据会出现错误或出现重复的记录等等情况,那么通过该参数可以避免这种情况。

    -clear-staging-table

    如果该staging-table非空,则通过该参数可以在运行导入前清除staging-table里的数据。

sqoop import(MySQL 到 Hive表)

  1. 登录客户端所在节点。
  2. 执行如下命令初始化环境变量。

    source /opt/client/bigdata_env

  3. 使用sqoop命令操作sqoop客户端。

    sqoop import --connect jdbc:mysql://10.100.231.134:3306/test --username root --password xxxxxx --table component --hive-import --hive-table component_test2 --delete-target-dir --fields-terminated-by "," -m 1 --as-textfile

    表2 参数说明

    参数

    说明

    -append

    将数据追加到hdfs中已经存在的dataset中。使用该参数,sqoop将把数据先导入到一个临时目录中,然后重新给文件命名到一个正式的目录中,以避免和该目录中已存在的文件重名。

    -as-avrodatafile

    将数据导入到一个Avro数据文件中。

    -as-sequencefile

    将数据导入到一个sequence文件中。

    -as-textfile

    将数据导入到一个普通文本文件中,生成该文本文件后,可以在hive中通过sql语句查询出结果。

    -boundary-query <statement>

    边界查询,在导入前先通过SQL查询得到一个结果集,然后导入的数据就是该结果集内的数据,格式如:-boundary-query 'select id,creationdate from person where id = 3',表示导入的数据为id=3的记录,或者select min(<split-by>), max(<split-by>) from <table name>

    注意:查询的字段中不能有数据类型为字符串的字段,否则会报错:java.sql.SQLException: Invalid value for getLong()。

    -columns<col,col,col…>

    指定要导入的字段值,格式如:-columns id,username

    -direct

    快速模式,利用了数据库的导入工具,如MySQL的mysqlimport,可以比jdbc连接的方式更为高效的将数据导入到关系数据库中。

    -direct-split-size

    在使用上面direct直接导入的基础上,对导入的流按字节数分块,特别是使用直连模式从PostgreSQL导入数据时,可以将一个到达设定大小的文件分为几个独立的文件。

    -inline-lob-limit

    设定大对象数据类型的最大值。

    -m或-num-mappers

    启动n个map来并行导入数据,默认是4个,该值请勿高于集群的最大Map数。

    -query,-e<statement>

    从查询结果中导入数据,该参数使用时必须指定-target-dir、-hive-table,在查询语句中一定要有where条件且在where条件中需要包含$CONDITIONS。

    示例:-query 'select * from person where $CONDITIONS ' -target-dir /user/hive/warehouse/person -hive-table person

    -split-by<column-name>

    表的列名,用来切分工作单元,一般后面跟主键ID。

    -table <table-name>

    关系数据库表名,数据从该表中获取。

    -target-dir <dir>

    指定hdfs路径。

    -warehouse-dir <dir>

    -target-dir不能同时使用,指定数据导入的存放目录,适用于导入hdfs,不适合导入hive目录。

    -where

    从关系数据库导入数据时的查询条件,示例:-where 'id = 2'

    -z,-compress

    压缩参数,默认数据不压缩,通过该参数可以使用gzip压缩算法对数据进行压缩,适用于SequenceFile,text文本文件,和Avro文件。

    –compression-codec

    Hadoop压缩编码,默认为gzip。

    –null-string <null-string>

    替换null字符串,如果没有指定,则字符串null将被使用。

    –null-non-string<null-string>

    替换非String的null字符串,如果没有指定,则字符串null将被使用。

    -check-column (col)

    增量导入参数,用来作为判断的列名,如id。

    -incremental (mode) append

    或lastmodified

    增量导入参数。

    append:追加,比如对大于last-value指定的值之后的记录进行追加导入。

    lastmodified:最后的修改时间,追加last-value指定的日期之后的记录。

    -last-value (value)

    增量导入参数,指定自从上次导入后列的最大值(大于该指定的值),也可以自己设定某一值。

Sqoop使用样例

  • sqoop import(MySQL到HDFS)

    sqoop import --connect jdbc:mysql://10.100.231.134:3306/test --username root --password xxx --query 'SELECT * FROM component where $CONDITIONS and component_id ="MRS 1.0_002"' --target-dir /tmp/component_test --delete-target-dir --fields-terminated-by "," -m 1 --as-textfile

  • sqoop export (obs到MySQL)

    sqoop export --connect jdbc:mysql://10.100.231.134:3306/test --username root --password xxx --table component14 -export-dir obs://obs-file-bucket/xx/part-m-00000 --fields-terminated-by ',' -m 1

  • sqoop import(MySQL到obs)

    sqoop import --connect jdbc:mysql://10.100.231.134:3306/test --username root --password xxx --table component --target-dir obs://obs-file-bucket/xx --delete-target-dir --fields-terminated-by "," -m 1 --as-textfile

  • sqoop import(MySQL到Hive外obs表)

    sqoop import --connect jdbc:mysql://10.100.231.134:3306/test --username root --password xxx --table component --hive-import --hive-table component_test01 --fields-terminated-by "," -m 1 --as-textfile