Sqoop导入mysql所有表到HDFS

最新推荐文章于 2024-08-12 03:07:57 发布
最新推荐文章于 2024-08-12 03:07:57 发布
阅读量831 收藏 1
点赞数 1
分类专栏： Sqoop 文章标签： sqoop
本文介绍使用Sqoop工具从MySQL数据库批量导入多个表格至HDFS的过程，包括解决因驱动版本过低导致的问题及成功导入后的文件验证。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

1、sqoop-import-all-tables导入多表
[root@node1 sqoop-1.4.7]# bin/sqoop-import-all-tables --connect jdbc:mysql://node1:3306/esdb --username root --password 123456 --as-textfile --warehouse-dir /user/root
Warning: /opt/sqoop-1.4.7/bin/../../hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
18/05/24 14:58:06 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
18/05/24 14:58:06 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
18/05/24 14:58:06 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
18/05/24 14:58:06 INFO tool.CodeGenTool: Beginning code generation
18/05/24 14:58:06 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `files` AS t LIMIT 1
18/05/24 14:58:06 ERROR manager.SqlManager: Error reading from database: java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@1817d444 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.
java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@1817d444 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:931)
    at com.mysql.jdbc.MysqlIO.checkForOutstandingStreamingData(MysqlIO.java:2518)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1748)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1961)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2537)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2466)
    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1383)
    at com.mysql.jdbc.ConnectionImpl.getMaxBytesPerChar(ConnectionImpl.java:2939)
    at com.mysql.jdbc.Field.getMaxBytesPerCharacter(Field.java:576)
    at com.mysql.jdbc.ResultSetMetaData.getPrecision(ResultSetMetaData.java:440)
    at org.apache.sqoop.manager.SqlManager.getColumnInfoForRawQuery(SqlManager.java:305)
    at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:260)
    at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:246)
    at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:327)
    at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1872)
    at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1671)
    at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:106)
    at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:501)
    at org.apache.sqoop.tool.ImportAllTablesTool.run(ImportAllTablesTool.java:110)
    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)
18/05/24 14:58:06 ERROR tool.ImportAllTablesTool: Encountered IOException running import job: java.io.IOException: No columns to generate for ClassWriter
[root@node1 sqoop-1.4.7]#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
由于之前上传到Sqoop lib下的MySQL驱动程序有些低，更新到mysql-connector-java-5.1.32-bin.jar即可

[root@node1 ~]# ls /opt/sqoop-1.4.7/lib |grep mysql
mysql-connector-java-5.1.32-bin.jar
[root@node1 ~]#
1
2
3
2、再次执行
[root@node1 sqoop-1.4.7]# bin/sqoop-import-all-tables --connect jdbc:mysql://node1:3306/esdb --username root --password 123456 --as-textfile --warehouse-dir /user/root
Warning: /opt/sqoop-1.4.7/bin/../../hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
18/05/24 15:03:33 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
18/05/24 15:03:33 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
18/05/24 15:03:34 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
18/05/24 15:03:34 INFO tool.CodeGenTool: Beginning code generation
18/05/24 15:03:34 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `files` AS t LIMIT 1
18/05/24 15:03:34 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `files` AS t LIMIT 1
18/05/24 15:03:34 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /opt/hadoop-2.7.5
Note: /tmp/sqoop-root/compile/6a64974699f84d472b1e4438782a3423/files.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
18/05/24 15:03:39 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-root/compile/6a64974699f84d472b1e4438782a3423/files.jar
18/05/24 15:03:39 WARN manager.MySQLManager: It looks like you are importing from mysql.
18/05/24 15:03:39 WARN manager.MySQLManager: This transfer can be faster! Use the --direct
18/05/24 15:03:39 WARN manager.MySQLManager: option to exercise a MySQL-specific fast path.
18/05/24 15:03:39 INFO manager.MySQLManager: Setting zero DATETIME behavior to convertToNull (mysql)
18/05/24 15:03:39 INFO mapreduce.ImportJobBase: Beginning import of files
18/05/24 15:03:39 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar
18/05/24 15:03:40 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps
18/05/24 15:03:40 INFO client.ConfiguredRMFailoverProxyProvider: Failing over to rm2
18/05/24 15:03:43 INFO db.DBInputFormat: Using read commited transaction isolation
18/05/24 15:03:43 INFO db.DataDrivenDBInputFormat: BoundingValsQuery: SELECT MIN(`id`), MAX(`id`) FROM `files`
18/05/24 15:03:43 INFO db.IntegerSplitter: Split size: 810; Num splits: 4 from: 1 to: 3244
18/05/24 15:03:43 INFO mapreduce.JobSubmitter: number of splits:4
18/05/24 15:03:43 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1526097883376_0013
18/05/24 15:03:44 INFO impl.YarnClientImpl: Submitted application application_1526097883376_0013
18/05/24 15:03:44 INFO mapreduce.Job: The url to track the job: http://bigdata03-test:8088/proxy/application_1526097883376_0013/
18/05/24 15:03:44 INFO mapreduce.Job: Running job: job_1526097883376_0013
18/05/24 15:03:51 INFO mapreduce.Job: Job job_1526097883376_0013 running in uber mode : false
18/05/24 15:03:51 INFO mapreduce.Job:  map 0% reduce 0%
18/05/24 15:03:57 INFO mapreduce.Job:  map 25% reduce 0%
18/05/24 15:03:58 INFO mapreduce.Job:  map 75% reduce 0%
18/05/24 15:03:59 INFO mapreduce.Job:  map 100% reduce 0%
18/05/24 15:03:59 INFO mapreduce.Job: Job job_1526097883376_0013 completed successfully
18/05/24 15:03:59 INFO mapreduce.Job: Counters: 30
    File System Counters
        FILE: Number of bytes read=0
        FILE: Number of bytes written=570260
        FILE: Number of read operations=0
        FILE: Number of large read operations=0
        FILE: Number of write operations=0
        HDFS: Number of bytes read=412
        HDFS: Number of bytes written=3799556
        HDFS: Number of read operations=16
        HDFS: Number of large read operations=0
        HDFS: Number of write operations=8
    Job Counters 
        Launched map tasks=4
        Other local map tasks=4
        Total time spent by all maps in occupied slots (ms)=18756
        Total time spent by all reduces in occupied slots (ms)=0
        Total time spent by all map tasks (ms)=18756
        Total vcore-milliseconds taken by all map tasks=18756
        Total megabyte-milliseconds taken by all map tasks=19206144
    Map-Reduce Framework
        Map input records=3244
        Map output records=3244
        Input split bytes=412
        Spilled Records=0
        Failed Shuffles=0
        Merged Map outputs=0
        GC time elapsed (ms)=279
        CPU time spent (ms)=7090
        Physical memory (bytes) snapshot=727486464
        Virtual memory (bytes) snapshot=8496140288
        Total committed heap usage (bytes)=443023360
    File Input Format Counters 
        Bytes Read=0
    File Output Format Counters 
        Bytes Written=3799556
18/05/24 15:03:59 INFO mapreduce.ImportJobBase: Transferred 3.6235 MB in 19.1194 seconds (194.0698 KB/sec)
18/05/24 15:03:59 INFO mapreduce.ImportJobBase: Retrieved 3244 records.
18/05/24 15:03:59 INFO tool.CodeGenTool: Beginning code generation
18/05/24 15:03:59 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `logs` AS t LIMIT 1
18/05/24 15:03:59 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /opt/hadoop-2.7.5
Note: /tmp/sqoop-root/compile/6a64974699f84d472b1e4438782a3423/logs.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
18/05/24 15:03:59 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-root/compile/6a64974699f84d472b1e4438782a3423/logs.jar
18/05/24 15:03:59 INFO mapreduce.ImportJobBase: Beginning import of logs
18/05/24 15:03:59 INFO client.ConfiguredRMFailoverProxyProvider: Failing over to rm2
18/05/24 15:04:01 INFO db.DBInputFormat: Using read commited transaction isolation
18/05/24 15:04:01 INFO db.DataDrivenDBInputFormat: BoundingValsQuery: SELECT MIN(`id`), MAX(`id`) FROM `logs`
18/05/24 15:04:01 INFO db.IntegerSplitter: Split size: 0; Num splits: 4 from: 1 to: 1
18/05/24 15:04:01 INFO mapreduce.JobSubmitter: number of splits:1
18/05/24 15:04:01 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1526097883376_0014
18/05/24 15:04:01 INFO impl.YarnClientImpl: Submitted application application_1526097883376_0014
18/05/24 15:04:01 INFO mapreduce.Job: The url to track the job: http://bigdata03-test:8088/proxy/application_1526097883376_0014/
18/05/24 15:04:01 INFO mapreduce.Job: Running job: job_1526097883376_0014
18/05/24 15:04:10 INFO mapreduce.Job: Job job_1526097883376_0014 running in uber mode : false
18/05/24 15:04:10 INFO mapreduce.Job:  map 0% reduce 0%
18/05/24 15:04:17 INFO mapreduce.Job:  map 100% reduce 0%
18/05/24 15:04:17 INFO mapreduce.Job: Job job_1526097883376_0014 completed successfully
18/05/24 15:04:17 INFO mapreduce.Job: Counters: 30
    File System Counters
        FILE: Number of bytes read=0
        FILE: Number of bytes written=142495
        FILE: Number of read operations=0
        FILE: Number of large read operations=0
        FILE: Number of write operations=0
        HDFS: Number of bytes read=99
        HDFS: Number of bytes written=47
        HDFS: Number of read operations=4
        HDFS: Number of large read operations=0
        HDFS: Number of write operations=2
    Job Counters 
        Launched map tasks=1
        Other local map tasks=1
        Total time spent by all maps in occupied slots (ms)=4490
        Total time spent by all reduces in occupied slots (ms)=0
        Total time spent by all map tasks (ms)=4490
        Total vcore-milliseconds taken by all map tasks=4490
        Total megabyte-milliseconds taken by all map tasks=4597760
    Map-Reduce Framework
        Map input records=1
        Map output records=1
        Input split bytes=99
        Spilled Records=0
        Failed Shuffles=0
        Merged Map outputs=0
        GC time elapsed (ms)=72
        CPU time spent (ms)=1560
        Physical memory (bytes) snapshot=179109888
        Virtual memory (bytes) snapshot=2121519104
        Total committed heap usage (bytes)=108003328
    File Input Format Counters 
        Bytes Read=0
    File Output Format Counters 
        Bytes Written=47
18/05/24 15:04:17 INFO mapreduce.ImportJobBase: Transferred 47 bytes in 17.8489 seconds (2.6332 bytes/sec)
18/05/24 15:04:17 INFO mapreduce.ImportJobBase: Retrieved 1 records.
[root@node1 sqoop-1.4.7]# 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
一共导入了2张表

[root@node1 ~]# hdfs dfs -ls /user/root
Found 5 items
drwxr-xr-x   - root supergroup          0 2018-05-24 15:03 /user/root/files
drwxr-xr-x   - root supergroup          0 2018-04-23 14:05 /user/root/input
drwxr-xr-x   - root supergroup          0 2018-05-24 15:04 /user/root/logs
drwxr-xr-x   - root supergroup          0 2018-05-22 13:36 /user/root/users
[root@node1 ~]# hdfs dfs -ls /user/root/files
Found 5 items
-rw-r--r--   3 root supergroup          0 2018-05-24 15:03 /user/root/files/_SUCCESS
-rw-r--r--   3 root supergroup     895593 2018-05-24 15:03 /user/root/files/part-m-00000
-rw-r--r--   3 root supergroup     912033 2018-05-24 15:03 /user/root/files/part-m-00001
-rw-r--r--   3 root supergroup    1109032 2018-05-24 15:03 /user/root/files/part-m-00002
-rw-r--r--   3 root supergroup     882898 2018-05-24 15:03 /user/root/files/part-m-00003
[root@node1 ~]#
--------------------- 
作者：程裕强 
来源：优快云 
原文：https://blog.youkuaiyun.com/chengyuqiang/article/details/80434840 
版权声明：本文为博主原创文章，转载请附上博文链接！

Sqoop全部数据导入_将mysql数据库中的数据导入到hdfs中---大数据之Apache Sqoop工作笔记002
添柴程序猿的专栏
02-21 421
可以看到导入命令,注意这里的\ 是用来在Linux中进行换行的,因为不用\的话,直接回车,就执行命令了.然后来看一下如何用sqoop导入数据,这里的导入指的是从其他数据源导入到hdfs,反过来是导出。然后执行以后可以看看日志,他把任务发送到resourcemanager 去执行了,可以看到。--fields-terminated-by 是指定数据的分隔符。--delete-target-dir 是有的话就删除.可以看得到下载后的内容,其实就是我们导入的数据对吧.测试数据有了,然后看看导入命令。
【大数据入门核心技术-Sqoop】（四）使用Sqoop将MySQL数据导入到HDFS
12-13 1074
sqoop import \ --connect jdbc:mysql://hadoop101:3306/sqoop_test?useSSL=false \ --username root \ --password 123456 \ --query 'select sid,sname from student where $CONDITIONS ' \ --target-dir /sqooptest/query \ --delete-target-dir \ --num-mappers 1 \ --fiel
利用sqoop将MySQL数据导入HDFS、Hive、HBase
qq_71375720的博客
07-13 1037
此处的MySQLToHDFS.conf为相对路径，在执行脚本时要在文件MySQLToHDFS.conf所在的目录下执行。执行后的文件保存在hdfs上的/sqoop/data/student 目录下利用命令hadoop fs -ls /sqoop/data/student查看。加上这个参数，可以在导出MySQL数据的时候，使用MySQL提供的导出工具mysqldump，加快导出速度，提高效率。需要将master上的/usr/bin/mysqldump分发至 node1、node2的/usr/bin目录下。
Sqoop全量导入mysql表数据到HDFS
m0_63086929的博客
10-21 2798
Sqoop全量导入mysql表数据到HDFS
sqoop将数据从mysql中导入到hdfs中
最新发布
weixin_40204206的博客
08-12 434
我整理的一些关于【MySQL,SQL】的项目学习资料（附讲解～～）和大家一起分享、学习一下：https://d.51cto.com/yOSbkR使用Sqoop将数据从MySQL导入HDFS的完整指导 在大数据技术的生态系统中，Sqoop是一款常用的工具，它主要用于在Hadoop和关系数据库之间传输大量数据。本文将详细指导...
使用sqoop把MySQL数据全量导入HDFS脚本
weixin_67805929的博客
02-14 488
echo -e "\033[32mSqoop写入HDFS任务完成，总用时：$(date -ud "@${run_time}" +%H小时%M分%S秒\033[0m"echo "===========db:$DB,table:$var 导入 ============="#为了保护账户密码的安全性，在建立配置文件的时候可以创建为隐藏文件，并且为文件设置权限为400。#jdbc 将用户相关信息放到一个配置文件中，截取用户配置文件中的第二行。#任务开始时间转化为秒单位。#输出任务执行时间到前台。
2、sqoop导入（RMDB-mysql、sybase到HDFS-hive）
05-29
在本主题中，我们将深入探讨 Sqoop 的导入功能，特别是如何将数据从 MySQL 和 Sybase 数据库导入到 HDFS，并进一步加载到 Hive 表中。 ### 一、Sqoop 导入介绍 1. **语法结构**： Sqoop 导入数据的基本语法如下：...
Sqoop将MySQL数据导入到HDFS和Hive中
qq_43184146的博客
08-26 592
一、将数据从mysql导入 HDFS sqoop import --connect jdbc:mysql://192.168.76.1:3306/workflow --username root --password a --table project 报错 tool.ImportTool: Encountered IOException running import job: jav...
sqoop从MySQL发送数据到hdfs拒绝连接
enjoy_zm的博客
05-18 712
sqoopa从MySQL发送数据到hdfs拒绝连接 报错：java.net.ConnectException: Call From hadoop1/192.168.222.131 to hadoop3:8032 failed on connection exception: java.net.ConnectException: 拒绝连接; For more details see: 参考：http://wiki.apache.org/hadoop/ConnectionRefused 原因及
使用Sqoop实现Mysql到HDFS的数据导入
QYHuiiQ
05-11 3498
查看当前mysql下有哪些库 [root@hadoop01 ~]# sqoop list-databases --connect jdbc:mysql://hadoop03:3306 --username root --password 123456; 查看某个库下的所有表 [root@hadoop01 ~]# sqoop list-tables --connect jdbc:mysql://hadoop03:3306/hive --username root --password 12345.
sqoop导入数据到hdfs
m0_51984816的博客
08-27 3026
sqoop
用sqoop从Mysql多表/单表导入hdfs命令
海鸥~
06-22 5145
一、 sqoop命令从Mysql单表导入hdfs命令 bin/sqoop import \ --connect jdbc:mysql://10.160.000.117:3306/phjr_data \ --m 1 \ --username mysql \ --password minmin@123 \ --table t_fgw_qt_lhszzxx \ --target-dir /smxaq/PHJR/phjr_data/t_fgw_qt_lhszzxx \ --as-textfile \ --fiel
sqoop导入到hdfs、导出mysql数据
qq_52299578的博客
11-07 660
sqoop导入导出
Sqoop导入所有一个库的所有表
阿正的博客
04-08 2048
参考：Sqoop导入所有表https://www.yiibai.com/sqoop/sqoop_import_all_tables.html https://www.jianshu.com/p/da2a073f279d利用sqoop实现mysql与Hdfs间的数据的导入导出 sqoop-import-all-tableshttps://www.liangzl.com/get-articl...
Sqoop工具模块之sqoop-import-all-tables
weixin_33875564的博客
06-12 1164
2019独角兽企业重金招聘Python工程师标准>>> ...
Sqoop - Import All Tables
大数据技术杂谈
11-09 2958
sqoop import-all-tables --connect jdbc:oracle:thin:@10.10.0.155:1521:mes --username mes --password meshadoop -m 1 --hive-import 在进行数据库全表导入时，则必须强制所有数据库中的每个表都必须有一个主键字段
sqoop从msyql抽取数据导入hive,并创建表，分区
weixin_41877582的博客
11-21 1471
bin/sqoop import \ --connect jdbc:mysql://sfbg101:3306/gmall \ --username root \ --password 123456 \ --table activity_info \ --num-mappers 1 \ --hive-import \ --hive-overwrite \ --hive-drop-import-delims \ --hive-database test \ --hive-table ods_activity_i
Sqoop一些常用详细命令及参数
@小刘同学nice
07-09 2114
常用命令列举 这里给大家列出来了一部分Sqoop操作时的常用参数，以供参考，需要深入学习的可以参看对应类的源代码。 命令 类 说明 import ImportTool 将数据导入到集群 export ExportTool 将集群数据导出 codegen CodeGenTool 获取数据库中某张表数据生成Java并打包Jar create-hive-table Creat...
yarn高可用队列报错
leezsj的博客
07-05 6104
submitted by user root to unknown queue: qfuser INFO client.ConfiguredRMFailoverProxyProvider: Failing over to rm2 原因在于默认的节点并不处于active状态， 解决方法，找到现在处于active状态的resourcemanagerkill jps 查看运行的服务 kill -9 要杀死的id yarn-deamon.sh start resourcemanager 重启resour.
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值