本教程主要介绍Sqoop数据迁移工具的使用方法和实战技巧，包括安装配置、数据导入导出、增量迁移等内容。风哥教程参考bigdata官方文档Sqoop使用指南、配置说明等相关内容。
通过本教程的学习，您将掌握Sqoop的使用方法，实现关系型数据库与Hadoop之间的数据迁移，为大数据分析和处理提供数据基础。
目录大纲
Part01-基础概念与理论知识
1.1 Sqoop概述
Sqoop是一个用于在关系型数据库和Hadoop之间传输数据的工具，主要功能：
- 将关系型数据库中的数据导入到Hadoop（HDFS、Hive、HBase）
- 将Hadoop中的数据导出到关系型数据库
- 支持全量和增量数据迁移
- 支持多种关系型数据库，如MySQL、PostgreSQL、Oracle等
Sqoop适合构建数据仓库ETL流程，为大数据分析提供数据基础，学习交流加群风哥微信: itpux-com
1.2 Sqoop工作原理
Sqoop的工作原理：
- 使用MapReduce作业来执行数据传输
- 将导入/导出任务转换为Map任务（无Reduce任务）
- 利用并行处理提高数据传输速度
- 支持数据分片，提高并行度
1.3 核心概念
核心概念：
- Import：从关系型数据库导入数据到Hadoop
- Export：从Hadoop导出数据到关系型数据库
- Job：Sqoop任务，可以保存和执行
- Connector：数据库连接器，用于与不同的数据库系统交互
- Driver：JDBC驱动，用于连接数据库
Part02-生产环境规划与建议
2.1 环境配置
风哥提示：Sqoop环境配置应考虑数据库连接、网络带宽和集群资源，确保数据迁移的高效和可靠。
环境配置建议：
- 安装Java JDK 8或更高版本
- 安装Hadoop 2.x或3.x
- 安装对应数据库的JDBC驱动
- 配置环境变量：SQOOP_HOME、HADOOP_HOME
- 确保网络连接畅通，数据库服务可访问
2.2 性能调优
性能调优建议：
- 调整并行度：使用–num-mappers参数设置Map任务数量
- 使用压缩：启用数据压缩，减少网络传输和存储开销
- 优化数据库：确保数据库有足够的连接数和资源
- 使用批量操作：提高数据传输效率
- 合理设置分片键：确保数据均匀分布
2.3 安全考虑
安全考虑：
- 密码保护：使用–password-file参数或环境变量存储密码
- 权限控制：确保数据库用户有适当的权限
- 网络安全：在安全网络环境中执行数据迁移
- 数据加密：考虑使用SSL连接数据库
Part03-生产环境项目实施方案
3.1 Sqoop安装与配置
安装Sqoop：
# 下载Sqoop
wget https://downloads.apache.org/sqoop/1.4.7/sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz
wget https://downloads.apache.org/sqoop/1.4.7/sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz
# 解压
tar -xzvf sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz -C /bigdata/app
# 配置环境变量
echo ‘export SQOOP_HOME=/bigdata/app/sqoop-1.4.7.bin__hadoop-2.6.0’ >> /etc/profile
echo ‘export PATH=$PATH:$SQOOP_HOME/bin’ >> /etc/profile
source /etc/profile
配置Sqoop：
# 复制配置文件
cp $SQOOP_HOME/conf/sqoop-env-template.sh $SQOOP_HOME/conf/sqoop-env.sh
cp $SQOOP_HOME/conf/sqoop-env-template.sh $SQOOP_HOME/conf/sqoop-env.sh
# 编辑配置文件
echo ‘export HADOOP_COMMON_HOME=/bigdata/app/hadoop-3.3.5’ >> $SQOOP_HOME/conf/sqoop-env.sh
echo ‘export HADOOP_MAPRED_HOME=/bigdata/app/hadoop-3.3.5’ >> $SQOOP_HOME/conf/sqoop-env.sh
# 复制JDBC驱动
cp mysql-connector-java-8.0.28.jar $SQOOP_HOME/lib/
3.2 数据导入
数据导入示例：
# 导入MySQL表到HDFS
sqoop import \
–connect jdbc:mysql://192.168.1.100:3306/fgedudb \
–username fgedu \
–password fgedu123 \
–table sales \
–target-dir /user/fgedu/sales \
–num-mappers 4 \
–compress \
–compression-codec org.apache.hadoop.io.compress.SnappyCodec
sqoop import \
–connect jdbc:mysql://192.168.1.100:3306/fgedudb \
–username fgedu \
–password fgedu123 \
–table sales \
–target-dir /user/fgedu/sales \
–num-mappers 4 \
–compress \
–compression-codec org.apache.hadoop.io.compress.SnappyCodec
# 导入MySQL表到Hive
sqoop import \
–connect jdbc:mysql://192.168.1.100:3306/fgedudb \
–username fgedu \
–password fgedu123 \
–table customers \
–hive-import \
–hive-table fgedu.customers \
–hive-overwrite \
–num-mappers 4
# 增量导入
sqoop import \
–connect jdbc:mysql://192.168.1.100:3306/fgedudb \
–username fgedu \
–password fgedu123 \
–table orders \
–target-dir /user/fgedu/orders \
–incremental append \
–check-column id \
–last-value 1000 \
–num-mappers 4
3.3 数据导出
数据导出示例：
# 导出HDFS数据到MySQL
sqoop export \
–connect jdbc:mysql://192.168.1.100:3306/fgedudb \
–username fgedu \
–password fgedu123 \
–table sales_summary \
–export-dir /user/fgedu/sales_summary \
–input-fields-terminated-by ‘\t’ \
–num-mappers 4
sqoop export \
–connect jdbc:mysql://192.168.1.100:3306/fgedudb \
–username fgedu \
–password fgedu123 \
–table sales_summary \
–export-dir /user/fgedu/sales_summary \
–input-fields-terminated-by ‘\t’ \
–num-mappers 4
# 导出Hive表到PostgreSQL
sqoop export \
–connect jdbc:postgresql://192.168.1.101:5432/fgedudb \
–username fgedu \
–password fgedu123 \
–table sales_summary \
–export-dir /user/hive/warehouse/fgedu.db/sales_summary \
–input-fields-terminated-by ‘\001’ \
–num-mappers 4
Part04-生产案例与实战讲解
4.1 MySQL数据导入HDFS
案例：将MySQL中的销售数据导入到HDFS
# 查看MySQL表结构
$ mysql -u fgedu -pfgedu123 -h 192.168.1.100 fgedudb -e “DESCRIBE sales;”
+————-+————-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+————-+————-+——+—–+———+—————-+
| id | int(11) | NO | PRI | NULL | auto_increment |
| product_id | varchar(20) | NO | | NULL | |
| customer_id | varchar(20) | NO | | NULL | |
| amount | decimal(10,2) | NO | | NULL | |
| sale_date | date | NO | | NULL | |
| region | varchar(50) | YES | | NULL | |
+————-+————-+——+—–+———+—————-+
+————-+————-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+————-+————-+——+—–+———+—————-+
| id | int(11) | NO | PRI | NULL | auto_increment |
| product_id | varchar(20) | NO | | NULL | |
| customer_id | varchar(20) | NO | | NULL | |
| amount | decimal(10,2) | NO | | NULL | |
| sale_date | date | NO | | NULL | |
| region | varchar(50) | YES | | NULL | |
+————-+————-+——+—–+———+—————-+
# 导入数据到HDFS
$ sqoop import \
> –connect jdbc:mysql://192.168.1.100:3306/fgedudb \
> –username fgedu \
> –password fgedu123 \
> –table sales \
> –target-dir /user/fgedu/sales \
> –num-mappers 4 \
> –compress \
> –compression-codec org.apache.hadoop.io.compress.SnappyCodec
10:00:00 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
10:00:01 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
10:00:02 INFO tool.CodeGenTool: Beginning code generation
10:00:03 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `sales` AS t LIMIT 1
10:00:04 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `sales` AS t LIMIT 1
10:00:05 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /bigdata/app/hadoop-3.3.5
10:00:06 INFO orm.CompilationManager: Found hadoop core jar at: /bigdata/app/hadoop-3.3.5/share/hadoop/common/hadoop-common-3.3.5.jar
10:00:07 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-fgedu/compile/3a7b8c9d0e1f2g3h4i5j6k7l8m9n0o/sales.jar
10:00:08 INFO mapreduce.ImportJobBase: Beginning import of sales
10:00:09 INFO client.RMProxy: Connecting to ResourceManager at fgedu.net.cn:8032
10:00:10 INFO mapreduce.JobResourceUploader: Disabling Erasure Coding for path: /tmp/hadoop-yarn/staging/fgedu/.staging/job_1617778210345_0001
10:00:11 INFO input.FileInputFormat: Total input files to process : 1
10:00:12 INFO mapreduce.JobSubmitter: number of splits:4
10:00:13 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1617778210345_0001
10:00:14 INFO mapreduce.JobSubmitter: Executing with tokens: []
10:00:15 INFO conf.Configuration: resource-types.xml not found
10:00:16 INFO resource.ResourceUtils: Unable to find ‘resource-types.xml’.
10:00:17 INFO resource.ResourceUtils: Adding resource type – name = memory-mb, units = MB, type = COUNTABLE
10:00:18 INFO resource.ResourceUtils: Adding resource type – name = vcores, units = , type = COUNTABLE
10:00:19 INFO mapreduce.JobSubmitter: Submitting job to ResourceManager
10:00:20 INFO mapreduce.JobSubmitter: Job job_1617778210345_0001 submitted successfully
10:00:21 INFO mapreduce.Job: The url to track the job: http://fgedu.net.cn:8088/proxy/application_1617778210345_0001/
10:00:22 INFO mapreduce.Job: Running job: job_1617778210345_0001
10:00:30 INFO mapreduce.Job: Job job_1617778210345_0001 running in uber mode : false
10:00:31 INFO mapreduce.Job: map 0% reduce 0%
10:00:40 INFO mapreduce.Job: map 25% reduce 0%
10:00:45 INFO mapreduce.Job: map 50% reduce 0%
10:00:50 INFO mapreduce.Job: map 75% reduce 0%
10:00:55 INFO mapreduce.Job: map 100% reduce 0%
10:00:56 INFO mapreduce.Job: Job job_1617778210345_0001 completed successfully
10:00:57 INFO mapreduce.Job: Counters: 30
File System Counters
FILE: Number of bytes read=0
FILE: Number of bytes written=8388608
FILE: Number of read operations=0
FILE: Number of large read operations=0
FILE: Number of write operations=0
HDFS: Number of bytes read=0
HDFS: Number of bytes written=1048576
HDFS: Number of read operations=8
HDFS: Number of large read operations=0
HDFS: Number of write operations=8
Map-Reduce Framework
Map input records=10000
Map output records=10000
Input split bytes=444
Spilled Records=0
Failed Shuffles=0
Merged Map outputs=0
GC time elapsed (ms)=1234
CPU time spent (ms)=5678
Physical memory (bytes) snapshot=1073741824
Virtual memory (bytes) snapshot=2147483648
Total committed heap usage (bytes)=8589934592
Shuffle Errors
BAD_ID=0
CONNECTION=0
IO_ERROR=0
WRONG_LENGTH=0
WRONG_MAP=0
WRONG_REDUCE=0
File Input Format Counters
Bytes Read=0
File Output Format Counters
Bytes Written=1048576
10:00:58 INFO mapreduce.ImportJobBase: Transferred 1.0 MB in 38.1234 seconds (26.7654 KB/sec)
10:00:59 INFO mapreduce.ImportJobBase: Retrieved 10000 records.
> –connect jdbc:mysql://192.168.1.100:3306/fgedudb \
> –username fgedu \
> –password fgedu123 \
> –table sales \
> –target-dir /user/fgedu/sales \
> –num-mappers 4 \
> –compress \
> –compression-codec org.apache.hadoop.io.compress.SnappyCodec
10:00:00 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
10:00:01 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
10:00:02 INFO tool.CodeGenTool: Beginning code generation
10:00:03 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `sales` AS t LIMIT 1
10:00:04 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `sales` AS t LIMIT 1
10:00:05 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /bigdata/app/hadoop-3.3.5
10:00:06 INFO orm.CompilationManager: Found hadoop core jar at: /bigdata/app/hadoop-3.3.5/share/hadoop/common/hadoop-common-3.3.5.jar
10:00:07 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-fgedu/compile/3a7b8c9d0e1f2g3h4i5j6k7l8m9n0o/sales.jar
10:00:08 INFO mapreduce.ImportJobBase: Beginning import of sales
10:00:09 INFO client.RMProxy: Connecting to ResourceManager at fgedu.net.cn:8032
10:00:10 INFO mapreduce.JobResourceUploader: Disabling Erasure Coding for path: /tmp/hadoop-yarn/staging/fgedu/.staging/job_1617778210345_0001
10:00:11 INFO input.FileInputFormat: Total input files to process : 1
10:00:12 INFO mapreduce.JobSubmitter: number of splits:4
10:00:13 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1617778210345_0001
10:00:14 INFO mapreduce.JobSubmitter: Executing with tokens: []
10:00:15 INFO conf.Configuration: resource-types.xml not found
10:00:16 INFO resource.ResourceUtils: Unable to find ‘resource-types.xml’.
10:00:17 INFO resource.ResourceUtils: Adding resource type – name = memory-mb, units = MB, type = COUNTABLE
10:00:18 INFO resource.ResourceUtils: Adding resource type – name = vcores, units = , type = COUNTABLE
10:00:19 INFO mapreduce.JobSubmitter: Submitting job to ResourceManager
10:00:20 INFO mapreduce.JobSubmitter: Job job_1617778210345_0001 submitted successfully
10:00:21 INFO mapreduce.Job: The url to track the job: http://fgedu.net.cn:8088/proxy/application_1617778210345_0001/
10:00:22 INFO mapreduce.Job: Running job: job_1617778210345_0001
10:00:30 INFO mapreduce.Job: Job job_1617778210345_0001 running in uber mode : false
10:00:31 INFO mapreduce.Job: map 0% reduce 0%
10:00:40 INFO mapreduce.Job: map 25% reduce 0%
10:00:45 INFO mapreduce.Job: map 50% reduce 0%
10:00:50 INFO mapreduce.Job: map 75% reduce 0%
10:00:55 INFO mapreduce.Job: map 100% reduce 0%
10:00:56 INFO mapreduce.Job: Job job_1617778210345_0001 completed successfully
10:00:57 INFO mapreduce.Job: Counters: 30
File System Counters
FILE: Number of bytes read=0
FILE: Number of bytes written=8388608
FILE: Number of read operations=0
FILE: Number of large read operations=0
FILE: Number of write operations=0
HDFS: Number of bytes read=0
HDFS: Number of bytes written=1048576
HDFS: Number of read operations=8
HDFS: Number of large read operations=0
HDFS: Number of write operations=8
Map-Reduce Framework
Map input records=10000
Map output records=10000
Input split bytes=444
Spilled Records=0
Failed Shuffles=0
Merged Map outputs=0
GC time elapsed (ms)=1234
CPU time spent (ms)=5678
Physical memory (bytes) snapshot=1073741824
Virtual memory (bytes) snapshot=2147483648
Total committed heap usage (bytes)=8589934592
Shuffle Errors
BAD_ID=0
CONNECTION=0
IO_ERROR=0
WRONG_LENGTH=0
WRONG_MAP=0
WRONG_REDUCE=0
File Input Format Counters
Bytes Read=0
File Output Format Counters
Bytes Written=1048576
10:00:58 INFO mapreduce.ImportJobBase: Transferred 1.0 MB in 38.1234 seconds (26.7654 KB/sec)
10:00:59 INFO mapreduce.ImportJobBase: Retrieved 10000 records.
# 查看导入结果
$ hdfs dfs -ls /user/fgedu/sales
Found 5 items
-rw-r–r– 3 fgedu supergroup 0 2026-04-08 10:00 /user/fgedu/sales/_SUCCESS
-rw-r–r– 3 fgedu supergroup 262144 2026-04-08 10:00 /user/fgedu/sales/part-m-00000.snappy
-rw-r–r– 3 fgedu supergroup 262144 2026-04-08 10:00 /user/fgedu/sales/part-m-00001.snappy
-rw-r–r– 3 fgedu supergroup 262144 2026-04-08 10:00 /user/fgedu/sales/part-m-00002.snappy
-rw-r–r– 3 fgedu supergroup 262144 2026-04-08 10:00 /user/fgedu/sales/part-m-00003.snappy
Found 5 items
-rw-r–r– 3 fgedu supergroup 0 2026-04-08 10:00 /user/fgedu/sales/_SUCCESS
-rw-r–r– 3 fgedu supergroup 262144 2026-04-08 10:00 /user/fgedu/sales/part-m-00000.snappy
-rw-r–r– 3 fgedu supergroup 262144 2026-04-08 10:00 /user/fgedu/sales/part-m-00001.snappy
-rw-r–r– 3 fgedu supergroup 262144 2026-04-08 10:00 /user/fgedu/sales/part-m-00002.snappy
-rw-r–r– 3 fgedu supergroup 262144 2026-04-08 10:00 /user/fgedu/sales/part-m-00003.snappy
4.2 Hive数据导出到PostgreSQL
案例：将Hive中的销售汇总数据导出到PostgreSQL
# 查看Hive表结构
$ beeline -u jdbc:hive2://fgedu.net.cn:10000 -n fgedu -p fgedu123 -e “DESCRIBE fgedu.sales_summary;”
Connecting to jdbc:hive2://fgedu.net.cn:10000
Connected to: Apache Hive (version 3.1.3)
Driver: Hive JDBC (version 3.1.3)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 3.1.3 by Apache Hive
+————-+————+———-+
| col_name | data_type | comment |
+————-+————+———-+
| region | string | |
| total_sales | double | |
| sale_date | string | |
+————-+————+———-+
3 rows selected (0.123 seconds)
Connecting to jdbc:hive2://fgedu.net.cn:10000
Connected to: Apache Hive (version 3.1.3)
Driver: Hive JDBC (version 3.1.3)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 3.1.3 by Apache Hive
+————-+————+———-+
| col_name | data_type | comment |
+————-+————+———-+
| region | string | |
| total_sales | double | |
| sale_date | string | |
+————-+————+———-+
3 rows selected (0.123 seconds)
# 导出数据到PostgreSQL
$ sqoop export \
> –connect jdbc:postgresql://192.168.1.101:5432/fgedudb \
> –username fgedu \
> –password fgedu123 \
> –table sales_summary \
> –export-dir /user/hive/warehouse/fgedu.db/sales_summary \
> –input-fields-terminated-by ‘\001’ \
> –num-mappers 4
10:00:00 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
10:00:01 INFO manager.PostgresqlManager: Preparing to use a PostgreSQL streaming resultset.
10:00:02 INFO tool.CodeGenTool: Beginning code generation
10:00:03 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM public.”sales_summary” AS t LIMIT 1
10:00:04 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM public.”sales_summary” AS t LIMIT 1
10:00:05 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /bigdata/app/hadoop-3.3.5
10:00:06 INFO orm.CompilationManager: Found hadoop core jar at: /bigdata/app/hadoop-3.3.5/share/hadoop/common/hadoop-common-3.3.5.jar
10:00:07 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-fgedu/compile/3a7b8c9d0e1f2g3h4i5j6k7l8m9n0o/sales_summary.jar
10:00:08 INFO mapreduce.ExportJobBase: Beginning export of sales_summary
10:00:09 INFO client.RMProxy: Connecting to ResourceManager at fgedu.net.cn:8032
10:00:10 INFO mapreduce.JobResourceUploader: Disabling Erasure Coding for path: /tmp/hadoop-yarn/staging/fgedu/.staging/job_1617778210345_0002
10:00:11 INFO input.FileInputFormat: Total input files to process : 4
10:00:12 INFO mapreduce.JobSubmitter: number of splits:4
10:00:13 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1617778210345_0002
10:00:14 INFO mapreduce.JobSubmitter: Executing with tokens: []
10:00:15 INFO conf.Configuration: resource-types.xml not found
10:00:16 INFO resource.ResourceUtils: Unable to find ‘resource-types.xml’.
10:00:17 INFO resource.ResourceUtils: Adding resource type – name = memory-mb, units = MB, type = COUNTABLE
10:00:18 INFO resource.ResourceUtils: Adding resource type – name = vcores, units = , type = COUNTABLE
10:00:19 INFO mapreduce.JobSubmitter: Submitting job to ResourceManager
10:00:20 INFO mapreduce.JobSubmitter: Job job_1617778210345_0002 submitted successfully
10:00:21 INFO mapreduce.Job: The url to track the job: http://fgedu.net.cn:8088/proxy/application_1617778210345_0002/
10:00:22 INFO mapreduce.Job: Running job: job_1617778210345_0002
10:00:30 INFO mapreduce.Job: Job job_1617778210345_0002 running in uber mode : false
10:00:31 INFO mapreduce.Job: map 0% reduce 0%
10:00:40 INFO mapreduce.Job: map 25% reduce 0%
10:00:45 INFO mapreduce.Job: map 50% reduce 0%
10:00:50 INFO mapreduce.Job: map 75% reduce 0%
10:00:55 INFO mapreduce.Job: map 100% reduce 0%
10:00:56 INFO mapreduce.Job: Job job_1617778210345_0002 completed successfully
10:00:57 INFO mapreduce.Job: Counters: 30
File System Counters
FILE: Number of bytes read=0
FILE: Number of bytes written=8388608
FILE: Number of read operations=0
FILE: Number of large read operations=0
FILE: Number of write operations=0
HDFS: Number of bytes read=1048576
HDFS: Number of bytes written=0
HDFS: Number of read operations=12
HDFS: Number of large read operations=0
HDFS: Number of write operations=0
Map-Reduce Framework
Map input records=1000
Map output records=1000
Input split bytes=444
Spilled Records=0
Failed Shuffles=0
Merged Map outputs=0
GC time elapsed (ms)=1234
CPU time spent (ms)=5678
Physical memory (bytes) snapshot=1073741824
Virtual memory (bytes) snapshot=2147483648
Total committed heap usage (bytes)=8589934592
Shuffle Errors
BAD_ID=0
CONNECTION=0
IO_ERROR=0
WRONG_LENGTH=0
WRONG_MAP=0
WRONG_REDUCE=0
File Input Format Counters
Bytes Read=1048576
File Output Format Counters
Bytes Written=0
10:00:58 INFO mapreduce.ExportJobBase: Transferred 1.0 MB in 38.1234 seconds (26.7654 KB/sec)
10:00:59 INFO mapreduce.ExportJobBase: Exported 1000 records.
> –connect jdbc:postgresql://192.168.1.101:5432/fgedudb \
> –username fgedu \
> –password fgedu123 \
> –table sales_summary \
> –export-dir /user/hive/warehouse/fgedu.db/sales_summary \
> –input-fields-terminated-by ‘\001’ \
> –num-mappers 4
10:00:00 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
10:00:01 INFO manager.PostgresqlManager: Preparing to use a PostgreSQL streaming resultset.
10:00:02 INFO tool.CodeGenTool: Beginning code generation
10:00:03 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM public.”sales_summary” AS t LIMIT 1
10:00:04 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM public.”sales_summary” AS t LIMIT 1
10:00:05 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /bigdata/app/hadoop-3.3.5
10:00:06 INFO orm.CompilationManager: Found hadoop core jar at: /bigdata/app/hadoop-3.3.5/share/hadoop/common/hadoop-common-3.3.5.jar
10:00:07 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-fgedu/compile/3a7b8c9d0e1f2g3h4i5j6k7l8m9n0o/sales_summary.jar
10:00:08 INFO mapreduce.ExportJobBase: Beginning export of sales_summary
10:00:09 INFO client.RMProxy: Connecting to ResourceManager at fgedu.net.cn:8032
10:00:10 INFO mapreduce.JobResourceUploader: Disabling Erasure Coding for path: /tmp/hadoop-yarn/staging/fgedu/.staging/job_1617778210345_0002
10:00:11 INFO input.FileInputFormat: Total input files to process : 4
10:00:12 INFO mapreduce.JobSubmitter: number of splits:4
10:00:13 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1617778210345_0002
10:00:14 INFO mapreduce.JobSubmitter: Executing with tokens: []
10:00:15 INFO conf.Configuration: resource-types.xml not found
10:00:16 INFO resource.ResourceUtils: Unable to find ‘resource-types.xml’.
10:00:17 INFO resource.ResourceUtils: Adding resource type – name = memory-mb, units = MB, type = COUNTABLE
10:00:18 INFO resource.ResourceUtils: Adding resource type – name = vcores, units = , type = COUNTABLE
10:00:19 INFO mapreduce.JobSubmitter: Submitting job to ResourceManager
10:00:20 INFO mapreduce.JobSubmitter: Job job_1617778210345_0002 submitted successfully
10:00:21 INFO mapreduce.Job: The url to track the job: http://fgedu.net.cn:8088/proxy/application_1617778210345_0002/
10:00:22 INFO mapreduce.Job: Running job: job_1617778210345_0002
10:00:30 INFO mapreduce.Job: Job job_1617778210345_0002 running in uber mode : false
10:00:31 INFO mapreduce.Job: map 0% reduce 0%
10:00:40 INFO mapreduce.Job: map 25% reduce 0%
10:00:45 INFO mapreduce.Job: map 50% reduce 0%
10:00:50 INFO mapreduce.Job: map 75% reduce 0%
10:00:55 INFO mapreduce.Job: map 100% reduce 0%
10:00:56 INFO mapreduce.Job: Job job_1617778210345_0002 completed successfully
10:00:57 INFO mapreduce.Job: Counters: 30
File System Counters
FILE: Number of bytes read=0
FILE: Number of bytes written=8388608
FILE: Number of read operations=0
FILE: Number of large read operations=0
FILE: Number of write operations=0
HDFS: Number of bytes read=1048576
HDFS: Number of bytes written=0
HDFS: Number of read operations=12
HDFS: Number of large read operations=0
HDFS: Number of write operations=0
Map-Reduce Framework
Map input records=1000
Map output records=1000
Input split bytes=444
Spilled Records=0
Failed Shuffles=0
Merged Map outputs=0
GC time elapsed (ms)=1234
CPU time spent (ms)=5678
Physical memory (bytes) snapshot=1073741824
Virtual memory (bytes) snapshot=2147483648
Total committed heap usage (bytes)=8589934592
Shuffle Errors
BAD_ID=0
CONNECTION=0
IO_ERROR=0
WRONG_LENGTH=0
WRONG_MAP=0
WRONG_REDUCE=0
File Input Format Counters
Bytes Read=1048576
File Output Format Counters
Bytes Written=0
10:00:58 INFO mapreduce.ExportJobBase: Transferred 1.0 MB in 38.1234 seconds (26.7654 KB/sec)
10:00:59 INFO mapreduce.ExportJobBase: Exported 1000 records.
4.3 增量数据迁移
案例：增量导入MySQL中的订单数据
# 首次导入数据
$ sqoop import \
> –connect jdbc:mysql://192.168.1.100:3306/fgedudb \
> –username fgedu \
> –password fgedu123 \
> –table orders \
> –target-dir /user/fgedu/orders \
> –num-mappers 4 \
> –compress \
> –compression-codec org.apache.hadoop.io.compress.SnappyCodec
10:00:00 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
…
10:00:59 INFO mapreduce.ImportJobBase: Retrieved 10000 records.
> –connect jdbc:mysql://192.168.1.100:3306/fgedudb \
> –username fgedu \
> –password fgedu123 \
> –table orders \
> –target-dir /user/fgedu/orders \
> –num-mappers 4 \
> –compress \
> –compression-codec org.apache.hadoop.io.compress.SnappyCodec
10:00:00 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
…
10:00:59 INFO mapreduce.ImportJobBase: Retrieved 10000 records.
# 增量导入数据
$ sqoop import \
> –connect jdbc:mysql://192.168.1.100:3306/fgedudb \
> –username fgedu \
> –password fgedu123 \
> –table orders \
> –target-dir /user/fgedu/orders \
> –incremental append \
> –check-column id \
> –last-value 10000 \
> –num-mappers 4 \
> –compress \
> –compression-codec org.apache.hadoop.io.compress.SnappyCodec
10:00:00 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
…
10:00:59 INFO mapreduce.ImportJobBase: Retrieved 2000 records.
> –connect jdbc:mysql://192.168.1.100:3306/fgedudb \
> –username fgedu \
> –password fgedu123 \
> –table orders \
> –target-dir /user/fgedu/orders \
> –incremental append \
> –check-column id \
> –last-value 10000 \
> –num-mappers 4 \
> –compress \
> –compression-codec org.apache.hadoop.io.compress.SnappyCodec
10:00:00 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
…
10:00:59 INFO mapreduce.ImportJobBase: Retrieved 2000 records.
Part05-风哥经验总结与分享
5.1 常见问题解决方案
常见问题解决方案：
- 连接失败：检查数据库连接信息、网络连接和防火墙设置
- 内存溢出：调整JVM参数，增加内存分配
- 数据类型不匹配：确保源和目标数据类型兼容
- 导入速度慢：增加并行度，使用压缩，优化数据库
- 导出失败：检查目标表结构，确保有足够的权限
5.2 最佳实践分享
风哥提示：在Sqoop使用中，应注重性能优化和数据一致性，确保数据迁移的高效和可靠。
最佳实践分享：
- 批量操作：使用批量导入/导出，提高效率
- 数据压缩：启用压缩，减少网络传输和存储开销
- 增量迁移：使用增量导入，减少数据传输量
- 错误处理：建立完善的错误处理机制
- 监控告警：监控数据迁移过程，及时发现问题
5.3 性能优化建议
性能优化建议：
- 并行度调整：根据数据量和集群资源调整Map任务数量
- 数据库优化：为分片键创建索引，提高查询速度
- 网络优化：确保网络带宽足够，减少网络延迟
- 存储优化：使用SSD存储，提高IO性能
- 配置优化：调整Sqoop和Hadoop配置参数
- 更多视频教程www.fgedu.net.cn
通过本教程的学习，您已经掌握了Sqoop数据迁移工具的使用方法和实战技巧。在实际生产环境中，应根据具体业务场景和数据特点，选择合适的配置和优化策略，以实现关系型数据库与Hadoop之间的数据迁移，为大数据分析和处理提供数据基础。学习交流加群风哥QQ113257174
更多学习教程公众号风哥教程itpux_com
from bigdata视频:www.itpux.com
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
