Sqoop 之基本使用

Sqoop 的基本使用一、Sqoop 基本命令1. 查看所有命令2. 查看某条命令的具体使用方法二、Sqoop 与 MySQL1. 查询MySQL所有数据库2. 查询指定数据库中所有数据表三、Sqoop 与 HDFS3.1 MySQL数据导入到HDFS1. 导入命令2. 导入验证3.2 HDFS数据导出到MySQL四、Sqoop 与 Hive4.1 MySQL数据导入到Hive1. 导入命令2.

蔡大远

415人浏览 · 2020-04-28 14:23:57

蔡大远 · 2020-04-28 14:23:57 发布

一、Sqoop 基本命令

1. 查看所有命令

sqoop help

[hadoop@zhangyu lib]$ 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
  eval               Evaluate a SQL statement and display the results
  export             Export an HDFS directory to a database table
  help               List available commands
  import             Import a table from a database to HDFS
  import-all-tables  Import tables from a database to HDFS
  import-mainframe   Import datasets from a mainframe server to HDFS
  job                Work with saved jobs
  list-databases     List available databases on a server
  list-tables        List available tables in a database
  merge              Merge results of incremental imports
  metastore          Run a standalone Sqoop metastore
  version            Display version information

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

这里提示我们使用sqoop help command（要查询的命令）进行该命令的详细查询

2. 查看某条命令的具体使用方法

sqoop help 命令名

二、Sqoop 与 MySQL

1. 查询MySQL所有数据库

通常用于Sqoop与MySQL连通测试：

sqoop list-databases \
--connect jdbc:mysql://hadoop001:3306/ \
--username root \
--password root

2. 查询指定数据库中所有数据表

sqoop list-tables \
--connect jdbc:mysql://hadoop001:3306/mysql \
--username root \
--password root

三、Sqoop 与 HDFS

3.1 MySQL数据导入到HDFS

常用参数

-m 指定启动map进程个数，默认是4个
--delete-target-dir 删除目标目录
--mapreduce-job-name 指定mapreduce的job的名字
--target-dir 导入到指定目录
--fields-terminated-by 指定字段之间的分隔符
--null-string 含义是 string类型的字段，当Value是NULL，替换成指定的字符
--null-non-string 含义是非string类型的字段，当Value是NULL，替换成指定字符
--columns 导入表中的部分字段
--where 按条件导入数据
--query 按照sql语句进行导入 使用--query关键字，就不能使用--table和--columns
--options-file 在文件中执行

1. 导入命令

示例：导出MySQL数据库中的help_keyword表到HDFS的/sqoop目录下，如果导入目录存在则先删除再导入，使用3个map tasks并行导入。

注：help_keyword是MySQL内置的一张字典表，之后的示例均使用这张表。

sqoop import \
--connect jdbc:mysql://hadoop001:3306/mysql \     
--username root \
--password root \
--table help_keyword \           # 待导入的表
--delete-target-dir \            # 目标目录存在则先删除
--target-dir /sqoop \            # 导入的目标目录
--fields-terminated-by '\t'  \   # 指定导出数据的分隔符
-m 3                             # 指定并行执行的map tasks数量

日志输出如下，可以看到输入数据被平均split为三份，分别由三个map task进行处理。数据默认以表的主键列作为拆分依据，如果你的表没有主键，有以下两种方案：

添加– autoreset-to-one-mapper参数，代表只启动一个map task，即不并行执行；
若仍希望并行执行，则可以使用–split-by < column-name> 指明拆分数据的参考列。

2. 导入验证

查看导入后的目录
hadoop fs -ls  -R /sqoop
查看导入内容
hadoop fs -text  /sqoop/part-m-00000

查看HDFS导入目录,可以看到表中数据被分为3部分进行存储，这是由指定的并行度决定的。

3.2 HDFS数据导出到MySQL

常用参数：

--table 指定导出表的名称
--input-fields-terminated-by 指定hdfs上文件的分隔符，默认是逗号
--export-dir 导出数据的目录
--columns 指定导出的字段

sqoop export  \
    --connect jdbc:mysql://hadoop001:3306/mysql \
    --username root \
    --password root \
    --table help_keyword_from_hdfs \        # 导出数据存储在MySQL的help_keyword_from_hdf的表中
    --export-dir /sqoop  \
    --input-fields-terminated-by '\t'\
    --m 3

表必须预先创建，建表语句如下：

CREATE TABLE help_keyword_from_hdfs LIKE help_keyword ;

四、Sqoop 与 Hive

常用参数

--create-hive-table 创建目标表，如果有会报错
--hive-database 指定hive数据库
--hive-import 指定导入hive（没有这个条件导入到hdfs中）
--hive-overwrite 覆盖
--hive-table 指定hive中表的名字，如果不指定使用导入的表的表名
--hive-partition-key 指定Hive分区表字段
--hive-partition-value 指定导入的分区值

4.1 MySQL数据导入到Hive

Sqoop导入数据到Hive是通过先将数据导入到HDFS上的临时目录，然后再将数据从HDFS上Load到Hive中，最后将临时目录删除。可以使用target-dir来指定临时目录。

1. 导入命令

sqoop import \
  --connect jdbc:mysql://hadoop001:3306/mysql \
  --username root \
  --password root \
  --table help_keyword \        # 待导入的表     
  --delete-target-dir \         # 如果临时目录存在删除
  --target-dir /sqoop_hive  \   # 临时目录位置
  --hive-database sqoop_test \  # 导入到Hive的sqoop_test数据库，数据库需要预先创建。不指定则默认为default库
  --hive-import \               # 导入到Hive
  --hive-overwrite \            # 如果Hive表中有数据则覆盖，这会清除表中原有的数据，然后再写入
  -m 3                          # 并行度

导入到Hive中的sqoop_test数据库需要预先创建，不指定则默认使用Hive中的default库。

 # 查看hive中的所有数据库
 hive>  SHOW DATABASES;
 # 创建sqoop_test数据库
 hive>  CREATE DATABASE sqoop_test;

2. 导入验证

 查看 sqoop_test 数据库的所有表
 hive>  SHOW  TABLES  IN  sqoop_test;
 查看表中数据
 hive> SELECT * FROM sqoop_test.help_keyword;

在这里插入图片描述

3. 可能出现的问题

如果执行报错java.io.IOException: java.lang.ClassNotFoundException: org.apache.hadoop.hive.conf.HiveConf，则需将Hive安装目录下lib下的hive-exec-**.jar放到sqoop 的lib 。

[root@hadoop001 lib]# ll hive-exec-*
-rw-r--r--. 1 1106 4001 19632031 11月 13 21:45 hive-exec-1.1.0-cdh5.15.2.jar
[root@hadoop001 lib]# cp hive-exec-1.1.0-cdh5.15.2.jar  ${SQOOP_HOME}/lib

4.2 Hive 导出数据到MySQL

由于Hive的数据是存储在HDFS上的，所以Hive导入数据到MySQL，实际上就是HDFS导入数据到MySQL。

1. 查看Hive表在HDFS的存储位置

#进入对应的数据库
hive> use sqoop_test;
查看表信息
hive> desc formatted help_keyword;

Location属性为其存储位置：
在这里插入图片描述

这里可以查看一下这个目录，文件结构如下：
在这里插入图片描述

4.3 执行导出命令

sqoop export  \
    --connect jdbc:mysql://hadoop001:3306/mysql \
    --username root \
    --password root \
    --table help_keyword_from_hive \
    --export-dir /user/hive/warehouse/sqoop_test.db/help_keyword  \
    -input-fields-terminated-by '\001' \             # 需要注意的是 hive 中默认的分隔符为 \001
    --m 3

MySQL中的表需要预先创建：

CREATE TABLE help_keyword_from_hive LIKE help_keyword ;

五、Sqoop 与 HBase

本小节只讲解从RDBMS导入数据到HBase，因为暂时没有命令能够从HBase直接导出数据到RDBMS。

5.1 MySQL导入数据到HBase

1. 导入数据

将help_keyword表中数据导入到HBase上的 help_keyword_hbase表中，使用原表的主键help_keyword_id作为RowKey，原表的所有列都会在keywordInfo列族下，目前只支持全部导入到一个列族下，不支持分别指定列族。

sqoop import \
    --connect jdbc:mysql://hadoop001:3306/mysql \
    --username root \
    --password root \
    --table help_keyword \              # 待导入的表
    --hbase-table help_keyword_hbase \  # hbase 表名称，表需要预先创建
    --column-family keywordInfo \       # 所有列导入到 keywordInfo 列族下 
    --hbase-row-key help_keyword_id     # 使用原表的help_keyword_id作为RowKey

导入的HBase表需要预先创建：

查看所有表
hbase> list
创建表
hbase> create 'help_keyword_hbase', 'keywordInfo'
查看表信息
hbase> desc 'help_keyword_hbase'

2. 导入验证

使用scan查看表数据：
在这里插入图片描述

六、全库导出

Sqoop支持通过import-all-tables命令进行全库导出到HDFS/Hive，但需要注意有以下两个限制：

所有表必须有主键；或者使用–autoreset-to-one-mapper，代表只启动一个map task;
你不能使用非默认的分割列，也不能通过WHERE子句添加任何限制。

第二点解释得比较拗口，这里列出官方原本的说明：

 - You must not intend to use non-default splitting column, nor impose
   any conditions via a WHERE clause.

全库导出到HDFS：

sqoop import-all-tables \
    --connect jdbc:mysql://hadoop001:3306/数据库名 \
    --username root \
    --password root \
    --warehouse-dir  /sqoop_all \     # 每个表会单独导出到一个目录，需要用此参数指明所有目录的父目录
    --fields-terminated-by '\t'  \
    -m 3

全库导出到Hive：

sqoop import-all-tables -Dorg.apache.sqoop.splitter.allow_text_splitter=true \
  --connect jdbc:mysql://hadoop001:3306/数据库名 \
  --username root \
  --password root \
  --hive-database sqoop_test \         # 导出到Hive对应的库   
  --hive-import \
  --hive-overwrite \
  -m 3

七、Sqoop 数据过滤

7.1 query参数

Sqoop支持使用query参数定义查询SQL，从而可以导出任何想要的结果集。使用示例如下：

sqoop import \
  --connect jdbc:mysql://hadoop001:3306/mysql \
  --username root \
  --password root \
  --query 'select * from help_keyword where  $CONDITIONS and  help_keyword_id < 50' \  
  --delete-target-dir \            
  --target-dir /sqoop_hive  \ 
  --hive-database sqoop_test \           # 指定导入目标数据库 不指定则默认使用Hive中的default库
  --hive-table filter_help_keyword \     # 指定导入目标表
  --split-by help_keyword_id \           # 指定用于split的列      
  --hive-import \                        # 导入到Hive
  --hive-overwrite \                     、
  -m 3

在使用query进行数据过滤时，需要注意以下三点：

必须用–hive-table指明目标表；
如果并行度-m不为1或者没有指定–autoreset-to-one-mapper，则需要用–split-by指明参考列；
SQL的where字句必须包含$CONDITIONS，这是固定写法，作用是动态替换。

7.2 增量导入

sqoop import \
    --connect jdbc:mysql://hadoop001:3306/mysql \
    --username root \
    --password root \
    --table help_keyword \
    --target-dir /sqoop_hive  \
    --hive-database sqoop_test \         
    --incremental  append  \             # 指明模式
    --check-column  help_keyword_id \    # 指明用于增量导入的参考列
    --last-value 300  \                  # 指定参考列上次导入的最大值
    --hive-import \   
    -m 3

incremental参数有以下两个可选的选项：

append：要求参考列的值必须是递增的，所有大于last-value的值都会被导入；
lastmodified：要求参考列的值必须是timestamp类型，且插入数据时候要在参考列插入当前时间戳，更新数据时也要更新参考列的时间戳，所有时间晚于last-value的数据都会被导入。

通过上面的解释我们可以看出来，其实Sqoop的增量导入并没有太多神器的地方，就是依靠维护的参考列来判断哪些是增量数据。当然我们也可以使用上面介绍的query参数来进行手动的增量导出，这样反而更加灵活。

八、类型支持

Sqoop默认支持数据库的大多数字段类型，但是某些特殊类型是不支持的。遇到不支持的类型，程序会抛出异常Hive does not support the SQL type for column xxx异常，此时可以通过下面两个参数进行强制类型转换：

–map-column-java ：重写SQL到Java类型的映射；
–map-column-hive ：重写Hive到Java类型的映射。

示例如下，将原先id字段强制转为String类型，value字段强制转为Integer类型：

$ sqoop import ... --map-column-java id=String,value=Integer

参考博客如下：
https://blog.csdn.net/m0_37809146/article/details/91127801

一门面向 Data 和 AI 的低代码、云原生的开源编程语言

无需安装部署，在线快速体验 Byzer

更多推荐

编译时权限控制

前言权限控制，对于 MLSQL 而言的重要程度可以说是生命线。 MLSQL 需要面对各式各样的资源访问，比如 MySQL, Oracle,HDFS，Hive，Kafka，Sorl，ElasticSearch，Redis，API，Web等等，不同用户对这些数据源（以及表，列）的权限是不一样的。传统模式是，每个用户都需要有个 proxy user，然后到每个数据源里面给这个 proxy user

Byzer 白泽

Byzer 支持 JDBC 聚合下推

聚合下推PR链接聚合下推我们知道 Byzer 支持多数据源和联邦查询，可以方便分析师在一个平台上快速的分析来自多种数据源的数据，从而进行灵活的探索式分析。使用场景和优势当前 Byzer 中加载数据的方式会拉取明细数据到 spark 中进行聚合计算，对于小数据量或者在分布式文件系统上的数据源来说是常规操作。但是对于 JDBC 数据源或者有分析能力的 OLAP 系统来说拉取明细数据可能就不是最

Byzer 白泽

Byzer 术语表

Byzer-lang Byzer ，又称为 Byzer-lang，一门面向 Data 和 AI 的低代码、云原生的开源编程语言。 Byzer 是一门结合了声明式编程和命令式编程的混合编程语言，其低代码且类 SQL 的编程逻辑配合内置算法及插件的加持，能帮助数据工作者们高效打通数据链路，完成数据的清洗转换，并快速地进行机器学习相关的训练及预测。 Byzer 希望能够提供一套语言、一个引擎，就能覆盖整