sqoop详细导入导出数据步骤

水深无声 2023-03-01 09:59 77阅读 0赞

前面已经给大家讲过sqoop的安装，如果还没有安装的可以再去看看详细安装流程：

[sqoop详细安装与环境配置][sqoop]

sqoop的作用前面也说过主要用于做数据迁移，它用于从关系数据库（如MySQL，Oracle）导入数据到Hadoop HDFS，并从Hadoop文件系统导出到关系数据库的互相导入。

### Sqoop功能 ###

*  将关系型数据库数据导入HDFS，解决HDFS的数据来源问题，使数据能够方便地进入HDFS。
 *  支持HDFS数据导出到关系型数据库，Hadoop计算出的数据可以方便地写回到数据库中。
 *  支持关系型数据库直接将数据导入到Hive。
 *  Sqoop是批处理类型的任务，不是常驻服务，不需要像WEB服务一样常驻运行，在需要的时候提交任务就可以完成数据的导入导出。
 *  Sqoop是使用命令行进行任务的提交，提供类似于Shell的脚本的Sqopp命令，提交方式很简便。
 *  Sqoop支持各种存储类型，包括行存、列存以及各种数压缩算法。

### Sqoop架构 ###

如果是你设计一个数据库数据导入导出工具的话，会怎么实现呢？首先比较简单而且也容易想到的方式是使用JDBC，将数据从数据库中拉取出来然后写入HDFS，这种方法简单易行，但是缺点也很明显，数据量较大时，效率不高。

Sqoop是怎么做的呢，它其实是依赖MapReduce的计算框架，将数据导入并行化，采用分而治之的思想，每个Map只处理一部分数据，然后由Reduce将Map的中间结果聚合起来。

其实并不需要Reduce，只是用Map就可以完成数据的并行导入导出工作了，每个Map使用JDBC将数据从数据库抽取出来，写入到HDFS，就可以完成数据的导入任务。

由于使用了MapReduce并发计算的特性，Sqoop可以显著提高数据导入导出的效率。在实际使用中，Sqoop一般不会称为性能的瓶颈，在磁盘读写和宽带都不是瓶颈的前提下，数据的导入导出效率往往取决于DB的性能。

上面的框架中Sqoop和数据库之间使用的是JDBC，所以逻辑上讲，所有支持JDBC操作的数据库都支持使用Sqoop将数据导入到HDFS中，当然各个数据库之间会存在差异，目前在不改造Sqoop的前提下，Sqoop支持的数据库有：MySQL，Oracle，SqlServer， postgreSQL，DB2等，基本涵盖了所有主流的数据库。

### 数据导入Hive流程 ###

如果命令指定要新建HIVE Table，Sqoop会先生成HIVE table的定义语句，Hive table的column和数据库的column建立一一映射的关系，然后Sqoop会生成一个rawData语句，rawdata语句会在Hive MateStore中注册元数据，并进行数迁移。

上面的Hive的建表语句和rawdata语句都会被写入到一个script脚本中，最后会sqoop启动.hive命令执行刚刚生成的script脚本，提交Hive任务，完成Hive导出。

### 数据导入HDFS原理 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pwMTc4MzQ5OTQwNzE_size_16_color_FFFFFF_t_70][]  
Sqoop在解析完命令参数后会查找参数指定的分片字段的数据范围，假设用户指定的分片字段为id，通过jdbc接口可以找到id的最大值和最小值，分别是5000和1。  
数据范围除以map数量可以得到每个Map传输的数据id范围，假设map数是5个，那么每个map的范围就是5000/5=1000。每个map使用SQL语句进行筛选，筛选方法就是在SQL中添加where条件，比如第一个map的where条件就可以写为id >= 1 and id <= 1000。  
每个map都会在HDFS上生成一个对应的文件，存储向HDFS导入的数据。

### 数据从HDFS导出原理 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pwMTc4MzQ5OTQwNzE_size_16_color_FFFFFF_t_70 1][]  
数据导出跟导入正好相反，是把HDFS上的文件导出到数据库中，导出任务的数据划分完全依赖于HDFS的 FileInputFormat 的 split 划分；

默认情况下会使用 CombineInputFormat ，尽量把同一个节点的数据使用同一个map来处理，这样可以众多小文件导致的创建map任务的开销，提高任务的执行效率。

但是如果导出的数据文件是压缩格式，且这种压缩格式不能再分片，那么文件就会当以单个文件进行分片，每一个文件会生成一个map进行导出。

导出任务的分片逻辑比较复杂，用户指定的并发参数往往不会生效。

**Sqoop是基于MapReduce框架的数据同步工具，启用多个map进行数据的并发导入和导出。对于导入任务，数据的分片是基于数据中数据的范围和用户指定的并发数；**

**对于导出任务，数据分片是依赖HDFS的 FileInputFormat 的分片策略。**

## Sqoop导数据语法 ##

首先导sql文件到mysql数据库中,test.sql文件放在linux系统的opt目录下  
**source /opt/test.sql;**  
这样mysql中就有数据了。

### mysql中导入按条件查询后的表到HDFS ###

语法：

sqoop import \
    --connect jdbc:mysql://localhost:3306/数据库名 \
    --driver com.mysql.jdbc.Driver \
    --query ''select * from userinfos where username='zs'  and \$CONDITIONS" \
    --username root \
    --password root \
    --split-by userid \
    --delete-target-dir \
    --target-dir /tmp/customs \
    --as-textfile
    --m 3

参数解析：

*  **connect**：指定JDBC连接数据库所在的地址、端口、数据库名称、字符编码格式等信息
 *  **username**：数据库连接用户名
 *  **password**：数据库连接密码
 *  **query**：获取导入数据的查询语句
 *  **$CONDITIONS**：query查询语句必须有的字段，\\ $转义符
 *  **split-by**：指定分片字段，后续会使用改字段对数据进行换分，放到不同的map中执行，一般使用主键或者有索引的字段，可提高执行效率
 *  **delete-target-dir**：如果目标文件存在就先删除在导入
 *  **target-dir**：指定HDFS上的目标路径，即导入数据的存储路径
 *  **as-textfile**：指定导入数据到HDFS后的数据存储格式。
 *  **m**：导入的数据分成多少个文件存放

### mysql中导入表的指定列到HDFS ###

语法：

sqoop import \
    --connect jdbc:mysql://localhost:3306/数据库名 \
    --driver com.mysql.jdbc.Driver \
    --table test \
    --username root \
    --password root \
    --delete-target-dir \
    --target-dir /tmp/customs \
    --columns "EMPNO,ENAME,JOB,SAL,COMM" \ 
    --as-textfile
    --m 3

参数解析：  
**table**: 指定数据库的表名  
**columns**：指定mysql数据库中的具体列导入到HDFS

### mysql中增量导入数据到HDFS ###

语法：

sqoop import \
    --connect jdbc:mysql://localhost:3306/数据库名 \
    --driver com.mysql.jdbc.Driver \
    --table test \
    --username root \
    --password root \
    --Incremental append \
    --check-column order_date \
    --delete-target-dir \
    --last_value '2013-07-25 00:00:00"
    --target-dir /data/retail_db \
    --m 3

参数解析：

**增量导入（incremental**）：指定关系数据库的某个Column和last value，下次导入的时候只导入比last value大的数据。增量导入分两种模式：

1.  **append**：把新数据追加到目标路径中
2.  **last modified**：会将新数据和老数据进行合并，合并的话需要新老数据的对应关系，所以还需要添加参数merge key来指定主键，最后执行结果只保留最新数据。该模式下last value的值一定要是timestamp或者data。
3.  **check-column**: 指定递增的列

### 导入数据到Hive中 ###

先在Hive中创建数据库  
hive -e “create database if not exists test;”  
语法：

sqoop import \
    --connect jdbc:mysql://localhost:3306/数据库名 \
    --driver com.mysql.jdbc.Driver \
    --table test \
    --username root \
    --password root \
    --hive-import \
    --create-hive-table \
    --hive-database test \
    --hive-table orders \
    --m 3

参数解析：

1.  支持hive table的创建与覆盖：**create-hive-table**, **hive-overwritess**
2.  **create-hive-table**: 自动创建表，如果表已经存在则报错  
    ,生产中一般不使用
3.  **hive-overwrite**：覆盖原有表数据
4.  **hive-import**: 通过hive-import指定导入到Hive
5.  **hive-database**: 指定导入的hive的数据库名
6.  **hive-table**: 指定导入的hive的表名

### 导入数据到Hive分区中 ###

先删除Hive表  
drop table if exists orders;  
语法：

sqoop import \
    --connect jdbc:mysql://localhost:3306/数据库名 \
    --driver com.mysql.jdbc.Driver \
    --query "select order_id,order_status from orders where order_date>='2013-11-03' and order_date <'2013-11-04' and \$CONDITIONS" \
    --username root \
    --password root \
    --hive-import \
    --delete-target-dir \
    --target-dir /data/test/orders \
    --split-by order_id \
    --hive-import \
    --hive-database test \
    --hive-table orders \
    --hive-partition-key "order_date" \
    --hive-partition-value "2013-11-03" \
    --m 3

参数解析：  
**query**:指定查询语句，解决多表关联插入  
**hive-partition-key**: 指定分区的某一列作为键  
**hive-partition-value**指定分区的某一列的某个值为value  
注意：分区字段不能当成普通字段导入表中

### 导入数据到HBase中 ###

先在HBase中建表和列簇  
create ‘products’,‘data’,‘category’  
语法：

sqoop import \
    --connect jdbc:mysql://localhost:3306/数据库名 \
    --driver com.mysql.jdbc.Driver \
    --username root \
    --password root \
    --table products \
    --hbase-table products \
    --column-family data \
    --m 3

参数解析：  
**table**：指定mysql数据库表名  
**hbase-table**: 指定hbase表名  
**column-family**：指定hbase列簇名

### HDFS向mysql中导出数据 ###

1.  **在mysql中建表结构**

create table customers_demo as select * from customers where 1=2;

1.  **上传数据**

hdfs dfs -mkdir /customerinput
    hdfs dfs -put customers.csv /customerinput

1.  **导出数据**

sqoop export \
    --connect jdbc:mysql://localhost:3306/数据库名 \
    --driver com.mysql.jdbc.Driver \
    --username root \
    --password root \
    --table customers_demo \
    --export-dir /customerinput \
    --m 1

参数解析：  
**table**: 指定mysql数据库表名  
**export-dir**: 表示待导出数据在HDFS上的路径。

### 导入数据到job ###

语法：  
创建job，并导数据

sqoop job --create mysql2hdfs \
    --connect jdbc:mysql://localhost:3306/数据库名 \
    --driver com.mysql.jdbc.Driver \
    --username root \
    --password root \
    --table orders \
    --incremental append \
    --check-column order_date \
    --last-value '0' \
    --target-dir /data/test/orders \
    --m 3

参数解析：  
**table**：指定mysql数据看的表名  
**check-column**：指定递增的列

**查看job**  
sqoop job --list

**执行job**  
sqoop job --exec mysql2hdfs

**注意：**  
每次job执行成功之后都会修改 --last-value 值，将最后一次的最大值填充进去；  
这里的 ‘0’ 没有实际含义，只是为了保证第一次数据导入时值最小。

**每天可以定时执行job,定时增量导入数据到hdfs**  
crontab -e

* 2 */1 * * sqoop job --exec mysql2hdfs

### 导数据报错处理 ###

导入数据到hive中及你刚才会报错，原因大致是因为没有导hive驱动包到sqoop的lib目录下，今天就着重讲以下几个报错处理：

**1.使用sqoop将mysql中的某张表导入到hdfs上的时候报错：**

Exception in thread "main" java.lang.NoClassDefFoundError: org/json/JSONObject
     at org.apache.sqoop.util.SqoopJsonUtil.getJsonStringforMap(SqoopJsonUtil.java:43)
     at org.apache.sqoop.SqoopOptions.writeProperties(SqoopOptions.java:784)
     at org.apache.sqoop.mapreduce.JobBase.putSqoopOptionsToConfiguration(JobBase.java:392)
     at org.apache.sqoop.mapreduce.JobBase.createJob(JobBase.java:378)
     at org.apache.sqoop.mapreduce.ImportJobBase.runImport(ImportJobBase.java:256)
     at org.apache.sqoop.manager.SqlManager.importTable(SqlManager.java:692)
     at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:513)
     at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:621)
     at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
     at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
     at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
     at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
     at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
     at org.apache.sqoop.Sqoop.main(Sqoop.java:252)
    Caused by: java.lang.ClassNotFoundException: org.json.JSONObject

是少了一个java-json.jar 的驱动包  
**处理方法**：  
拷贝这个java-json.jar 的驱动包到sqoop的lib目录下  
cp /opt/software/java-json.jar /opt/install/sqoop-1.4.6-cdh5.14.2/lib/

**2.导入数据到hive上的时候报错，输出路径已存在**

ERROR tool.ImportTool: Import failed: org.apache.hadoop.mapred.FileAlreadyExistsException: Output directory hdfs://hadoop1:9000/user/root/orders already exists
     at org.apache.hadoop.mapreduce.lib.output.FileOutputFormat.checkOutputSpecs(FileOutputFormat.java:146)
     at org.apache.hadoop.mapreduce.JobSubmitter.checkSpecs(JobSubmitter.java:270)
     at org.apache.hadoop.mapreduce.JobSubmitter.submitJobInternal(JobSubmitter.java:143)
     at org.apache.hadoop.mapreduce.Job$10.run(Job.java:1307)
     at org.apache.hadoop.mapreduce.Job$10.run(Job.java:1304)
     at java.security.AccessController.doPrivileged(Native Method)
     at javax.security.auth.Subject.doAs(Subject.java:422)
     at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1920)
     at org.apache.hadoop.mapreduce.Job.submit(Job.java:1304)
     at org.apache.hadoop.mapreduce.Job.waitForCompletion(Job.java:1325)
     at org.apache.sqoop.mapreduce.ImportJobBase.doSubmitJob(ImportJobBase.java:203)
     at org.apache.sqoop.mapreduce.ImportJobBase.runJob(ImportJobBase.java:176)
     at org.apache.sqoop.mapreduce.ImportJobBase.runImport(ImportJobBase.java:273)
     at org.apache.sqoop.manager.SqlManager.importTable(SqlManager.java:692)
     at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:513)
     at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:621)
     at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
     at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
     at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
     at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
     at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
     at org.apache.sqoop.Sqoop.main(Sqoop.java:252)

**处理方法**：  
删除已存在的目录

hdfs dfs -rmr hdfs://hadoop1:9000/user/root/orders

3.**继续报错，缺少hive-common-1.1.0-cdh5.14.2.jar驱动包**

ERROR tool.ImportTool: Import failed: java.io.IOException: java.lang.ClassNotFoundException: org.apache.hadoop.hive.conf.HiveConf
     at org.apache.sqoop.hive.HiveConfig.getHiveConf(HiveConfig.java:50)
     at org.apache.sqoop.hive.HiveImport.getHiveArgs(HiveImport.java:392)
     at org.apache.sqoop.hive.HiveImport.executeExternalHiveScript(HiveImport.java:379)
     at org.apache.sqoop.hive.HiveImport.executeScript(HiveImport.java:337)
     at org.apache.sqoop.hive.HiveImport.importTable(HiveImport.java:241)
     at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:530)
     at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:621)
     at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
     at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
     at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
     at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
     at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
     at org.apache.sqoop.Sqoop.main(Sqoop.java:252)
    Caused by: java.lang.ClassNotFoundException: org.apache.hadoop.hive.conf.HiveConf

**处理方法**：  
因为缺少hive-common-1.1.0-cdh5.14.2.jar，所以从Hive的lib中进行拷贝

cp /opt/install/hive-1.1.0-cdh5.14.2/lib/hive-common-1.1.0-cdh5.14.2.jar /opt/install/sqoop-1.4.6-cdh5.14.2/lib/

**4.还是继续报错，缺少hive-shims的驱动包**

Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/hadoop/hive/shims/ShimLoader
     at org.apache.hadoop.hive.conf.HiveConf$ConfVars.<clinit>(HiveConf.java:370)
     at org.apache.hadoop.hive.conf.HiveConf.<clinit>(HiveConf.java:108)
     at java.lang.Class.forName0(Native Method)
     at java.lang.Class.forName(Class.java:264)
     at org.apache.sqoop.hive.HiveConfig.getHiveConf(HiveConfig.java:44)
     at org.apache.sqoop.hive.HiveImport.getHiveArgs(HiveImport.java:392)
     at org.apache.sqoop.hive.HiveImport.executeExternalHiveScript(HiveImport.java:379)
     at org.apache.sqoop.hive.HiveImport.executeScript(HiveImport.java:337)
     at org.apache.sqoop.hive.HiveImport.importTable(HiveImport.java:241)
     at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:530)
     at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:621)
     at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
     at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
     at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
     at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
     at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
     at org.apache.sqoop.Sqoop.main(Sqoop.java:252)
    Caused by: java.lang.ClassNotFoundException: org.apache.hadoop.hive.shims.ShimLoader

**处理方法**：  
因为缺少hive-shims的jar包，需要从Hive中进行拷贝

cp /opt/install/hive-1.1.0-cdh5.14.2/lib/hive-shims* /opt/install/sqoop-1.4.6-cdh5.14.2/lib/

经常出现的报错以及处理办法已经给大家一一举例了，希望对你们有帮助，持续关注小编，后续还会有更多呕心沥血之作等着大家给建议。

[sqoop]: https://blog.csdn.net/zp17834994071/article/details/107538935
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pwMTc4MzQ5OTQwNzE_size_16_color_FFFFFF_t_70]: /images/20230209/822b3d488c6d4019bb7ec7f4fb65b2c7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pwMTc4MzQ5OTQwNzE_size_16_color_FFFFFF_t_70 1]: /images/20230209/02abc1f63da74d84aa90dbfde2939a59.png