LRF

Sqoop User Guide(Sqoop使用指南)

September 11, 2018

1、简介

Sqoop是一种用于转换Hadoop和关系型数据库之间的数据。你可以导出关系型数据库如mysql到HDFS上，在Hadoop MapReduce上进行数据转换，然后在导出到关系型数据库中。Sqoop依靠数据库来描述要导入的数据的模式，Sqoop使用MapReduce来做数据的导入和导出，它提供并行操作和容错.

sqoop

2、基本使用

使用Sqoop，可以把关系型数据库中的数据导入到HDFS中。对于数据库，Sqoop将逐行读取表格到HDFS,此导入过程的输出是一组文件，其中包含导入的表或数据集的副本(parquet)，导入是由MapReduce并行执行的，因此输出会在多个parquet文件中。

导入过程的副产品是生成的java类，

在进行数据操作之后(使用MapReduce或者Hive)计算之后，会有一个结果集，然后你可以将其导出回关系型数据库中，Sqoop的导出过程将并行读取HDFS中的一组分割文本文件，将他们解析为记录，并将它们作为新行插入目录数据库表中，供外部应用程序或用户使用。

Sqoop包含一些其它命令，如：可以列出目前所有可用的数据库，sqoop-list-databases --connect=jdbc:mysql://10.1.4.99:3306/test --username=lrf --password=lrf,列出数据库中的所有表：sqoop-list-tables --connect=jdbc:mysql://10.1.4.99:3306/test --username=lrf --password=lrf,还包括一个原始的SQl执行shell:sqoop-eval

可以自定义导入,对于数据库，可以控制导入的特定行范围或列，

3、Sqoop 工具

Sqoop时相关工具的集合，要使用Sqoop,需要指定要使用的工具和控制工具的参数。

$ sqoop -help
usage: sqoop COMMAND [ARGS]

Available commands:
  codegen            Generate code to interact with database records #生成与数据库交互的代码
  create-hive-table  Import a table definition into Hive # 导入表定义到hive
  eval               Evaluate a SQL statement and display the results # 评估sql语句并显示结果
  export             Export an HDFS directory to a database table # 将HDFS目录导出到数据库
  help               List available commands # 列出可用命令
  import             Import a table from a database to HDFS # 从数据库中导入一个指定表到HDFS
  import-all-tables  Import tables from a database to HDFS # 把一个数据库中的表都导入到HDFS
  import-mainframe   Import mainframe datasets to HDFS # 导入主数据库到HDFS
  list-databases     List available databases on a server # 
  list-tables        List available tables in a database
  version            Display version information

See 'sqoop help COMMAND' for information on a specific command.

4、从mysql 到 HDFS

# 导入指定的表
$ sqoop import --connect jdbc:mysql://<dburi>/<dbname>?zeroDateTimeBehavior=CONVERT_TO_NULL # db
                --username <username> # db user
                --password <password> # db pwd
                --table <tablename> # db table
                --check-column <col> # 要检查的列的名称
                --incremental <mode>  # 改模式决定sqoop如何定义哪些行为新的行。取值为append或lastmodified
                --last-value <value> # 前一个导入中检查列的最大值
                --target-dir <hdfs-dir> # HDFS 的写入目录，例如：/user/hdfs/result.
# 列子
sqoop import --connect jdbc:mysql://10.1.4.99:3306/test?zeroDateTimeBehavior=CONVERT_TO_NULL --table student --username lrf --password lrf --target-dir hdfs://hadoop-11.146.dev.net/user/hdfs/test/student

# 导入数据库下的所有表
sqoop import-all-tables \
    --username <username> # db user
    --password <password> # db pwd
    --check-column <col> # 要检查的列的名称
    --incremental <mode>  # 改模式决定sqoop如何定义哪些行为新的行。取值为append或lastmodified
    --last-value <value> # 前一个导入中检查列的最大值
# 列子,不能指定导入，默认会全部导入到/user/hdfs目录下
sqoop import-all-tables --connect jdbc:mysql://10.1.4.99:3306/test?zeroDateTimeBehavior=CONVERT_TO_NULL --username lrf --password lrf

5、从HDFS 到Mysql

需要先创建好对应 HDFS 中的数据结构的 MySQL 表，然后在集群的 Master 节点上执行如下命令，指定要导的数据文件的路径

$ sqoop export --connect jdbc:mysql://<dburi>/<dbname> 
               --username <username> 
               --password <password> 
               --table <tablename> 
               --export-dir <hdfs-dir>
# 列子
sqoop export --connect jdbc:mysql://10.1.4.99:3306/test --username lrf --password lrf --table student 
--export-dir hdfs://hadoop-11.146.dev.net/user/hdfs/test/student

6、从mysql 到Hive

在集群的 Master 节点上执行如下命令后，从MySQL数据库导入数据的同时，也会新建一个 Hive 表。

$ sqoop import --connect jdbc:mysql://<dburi>/<dbname> 
               --username <username> 
               --password <password> 
               --table <tablename> 
               --check-column <col> 
               --incremental <mode> 
               --last-value <value> 
               --fields-terminated-by "\t" 
               --lines-terminated-by "\n" 
               --hive-import 
               --warehouse-dir <hdfs-dir> 
               --hive-table <hive-tablename>
# 列子
sqoop import-all-tables \
    -m 3 \ 
    --connect jdbc:mysql://10.1.4.99:3306/test?zeroDateTimeBehavior=CONVERT_TO_NULL \ #mysql连接，zeroDateTimeBehavior 处理异常时间的配置,比如0，
    --username=lrf \
    --password=lrf \
    --compression-codec=snappy \
    --as-parquetfile \
    --warehouse-dir=/user/hive/warehouse \
    --hive-import

7、从Hive到mysql

$ sqoop export --connect jdbc:mysql://<dburi>/<dbname> 
               --username <username> 
               --password <password> 
               --table <tablename> 
               --export-dir <hive-dir>

# 例子,如果导出时要指定hive表的字段，不然分割不了，导出默认是使用逗号分割的，需要指定hive的列对应上mysql中的列，下列中的columns字段就是hive表中的字段。
sqoop export --connect jdbc:mysql://10.1.4.99:3306/test --username lrf --password lrf --table nginx_access_logs --columns reote_addr,time_local,request,status,body_bytes_sent,http_referer,http_user_agent,http_x_forwarded_for --export-dir hdfs://hadoop-11.146.dev.net/user/hive/warehouse/nginx_access_logs

8、使用sql条件导入到hdfs或hive

sqoop import --connect jdbc:mysql://<dburi>/<dbname> 
             --username <username> 
             --password <password> 
             --query <query-sql> 
             --split-by <sp-column> # 查询的条件,如:'select * from student where age > 20'
             --hive-import 
             --hive-table <hive-tablename> 
             --target-dir <hdfs-dir>

参考

LRF 记录学习、生活的点滴