Sqoop从Oracle导入一个大表（3亿）到Hive失败

最新推荐文章于 2022-04-23 13:01:06 发布
最新推荐文章于 2022-04-23 13:01:06 发布
阅读量1.9k 收藏 1
点赞数
分类专栏： Hadoop基础教程 文章标签： sqoop 大表
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/chengyuqiang/article/details/80452720
版权

大约3亿条记录

SQL> SELECT count(*) FROM INFO;

  COUNT(*)
----------
 294239674

SQL>

导入Hive

[root@node1 sqoop-1.4.7]# bin/sqoop import --connect jdbc:oracle:thin:@node1:1521:ORA --username test --password test --table info --hive-import --target-dir temp_table --hive-table info --null-string '\\N' --null-non-string '\\N' --fields-terminated-by '\001' --hive-drop-import-delims -m 1 --hive-overwrite --delete-target-dir
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/25 15:03:27 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
18/05/25 15:03:27 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
18/05/25 15:03:27 INFO oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop is disabled.
18/05/25 15:03:27 INFO manager.SqlManager: Using default fetchSize of 1000
18/05/25 15:03:27 INFO tool.CodeGenTool: Beginning code generation
18/05/25 15:03:40 INFO manager.OracleManager: Time zone has been set to GMT
18/05/25 15:03:40 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM info t WHERE 1=0
18/05/25 15:03:40 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /opt/hadoop-2.7.5
Note: /tmp/sqoop-root/compile/cc373056b9d4974d8e7a97947a06ac4b/info.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
18/05/25 15:03:42 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-root/compile/cc373056b9d4974d8e7a97947a06ac4b/info.jar
18/05/25 15:03:43 INFO tool.ImportTool: Destination directory temp_table deleted.
18/05/25 15:03:43 INFO manager.OracleManager: Time zone has been set to GMT
18/05/25 15:03:43 INFO manager.OracleManager: Time zone has been set to GMT
18/05/25 15:03:43 INFO mapreduce.ImportJobBase: Beginning import of info
18/05/25 15:03:43 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar
18/05/25 15:03:43 INFO manager.OracleManager: Time zone has been set to GMT
18/05/25 15:03:43 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps
18/05/25 15:03:44 INFO client.ConfiguredRMFailoverProxyProvider: Failing over to rm2
18/05/25 15:03:46 INFO db.DBInputFormat: Using read commited transaction isolation
18/05/25 15:03:46 INFO mapreduce.JobSubmitter: number of splits:1
18/05/25 15:03:46 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1526097883376_0063
18/05/25 15:03:46 INFO impl.YarnClientImpl: Submitted application application_1526097883376_0063
18/05/25 15:03:46 INFO mapreduce.Job: The url to track the job: http://bigdata03-test:8088/proxy/application_1526097883376_0063/
18/05/25 15:03:46 INFO mapreduce.Job: Running job: job_1526097883376_0063
18/05/25 15:03:56 INFO mapreduce.Job: Job job_1526097883376_0063 running in uber mode : false
18/05/25 15:03:56 INFO mapreduce.Job:  map 0% reduce 0%
18/05/25 15:32:01 INFO mapreduce.Job: Task Id : attempt_1526097883376_0063_m_000000_0, Status : FAILED
Error: java.io.IOException: SQLException in nextKeyValue
	at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:277)
	at org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.nextKeyValue(MapTask.java:556)
	at org.apache.hadoop.mapreduce.task.MapContextImpl.nextKeyValue(MapContextImpl.java:80)
	at org.apache.hadoop.mapreduce.lib.map.WrappedMapper$Context.nextKeyValue(WrappedMapper.java:91)
	at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:145)
	at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
	at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:787)
	at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
	at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:164)
	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:1754)
	at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158)
Caused by: java.sql.SQLRecoverableException: No more data to read from socket
	at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1157)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:350)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
	at oracle.jdbc.driver.T4CPreparedStatement.fetch(T4CPreparedStatement.java:1121)
	at oracle.jdbc.driver.OracleResultSetImpl.close_or_fetch_from_next(OracleResultSetImpl.java:384)
	at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:288)
	at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:237)
	... 12 more

Container killed by the ApplicationMaster.
Container killed on request. Exit code is 143
Container exited with a non-zero exit code 143

18/05/25 15:32:10 INFO mapreduce.Job: Task Id : attempt_1526097883376_0063_m_000000_1, Status : FAILED
Error: java.lang.RuntimeException: java.lang.RuntimeException: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12528, TNS:listener: all appropriate instances are blocking new connections
 
	at org.apache.sqoop.mapreduce.db.DBInputFormat.setDbConf(DBInputFormat.java:170)
	at org.apache.sqoop.mapreduce.db.DBInputFormat.setConf(DBInputFormat.java:161)
	at org.apache.hadoop.util.ReflectionUtils.setConf(ReflectionUtils.java:76)
	at org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:136)
	at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:751)
	at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
	at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:164)
	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:1754)
	at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158)
Caused by: java.lang.RuntimeException: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12528, TNS:listener: all appropriate instances are blocking new connections
 
	at org.apache.sqoop.mapreduce.db.DBInputFormat.getConnection(DBInputFormat.java:223)
	at org.apache.sqoop.mapreduce.db.DBInputFormat.setDbConf(DBInputFormat.java:168)
	... 10 more
Caused by: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12528, TNS:listener: all appropriate instances are blocking new connections
 
	at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:489)
	at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:553)
	at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:254)
	at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
	at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:528)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:247)
	at org.apache.sqoop.mapreduce.db.DBConfiguration.getConnection(DBConfiguration.java:302)
	at org.apache.sqoop.mapreduce.db.DBInputFormat.getConnection(DBInputFormat.java:216)
	... 11 more
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12528, TNS:listener: all appropriate instances are blocking new connections
 
	at oracle.net.ns.NSProtocol.connect(NSProtocol.java:399)
	at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1140)
	at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:340)
	... 19 more

Container killed by the ApplicationMaster.
Container killed on request. Exit code is 143
Container exited with a non-zero exit code 143

18/05/25 15:32:29 INFO mapreduce.Job: Task Id : attempt_1526097883376_0063_m_000000_2, Status : FAILED
Error: java.lang.RuntimeException: java.lang.RuntimeException: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12528, TNS:listener: all appropriate instances are blocking new connections
 
	at org.apache.sqoop.mapreduce.db.DBInputFormat.setDbConf(DBInputFormat.java:170)
	at org.apache.sqoop.mapreduce.db.DBInputFormat.setConf(DBInputFormat.java:161)
	at org.apache.hadoop.util.ReflectionUtils.setConf(ReflectionUtils.java:76)
	at org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:136)
	at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:751)
	at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
	at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:164)
	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:1754)
	at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158)
Caused by: java.lang.RuntimeException: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12528, TNS:listener: all appropriate instances are blocking new connections
 
	at org.apache.sqoop.mapreduce.db.DBInputFormat.getConnection(DBInputFormat.java:223)
	at org.apache.sqoop.mapreduce.db.DBInputFormat.setDbConf(DBInputFormat.java:168)
	... 10 more
Caused by: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12528, TNS:listener: all appropriate instances are blocking new connections
 
	at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:489)
	at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:553)
	at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:254)
	at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
	at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:528)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:247)
	at org.apache.sqoop.mapreduce.db.DBConfiguration.getConnection(DBConfiguration.java:302)
	at org.apache.sqoop.mapreduce.db.DBInputFormat.getConnection(DBInputFormat.java:216)
	... 11 more
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12528, TNS:listener: all appropriate instances are blocking new connections
 
	at oracle.net.ns.NSProtocol.connect(NSProtocol.java:399)
	at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1140)
	at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:340)
	... 19 more

Container killed by the ApplicationMaster.
Container killed on request. Exit code is 143
Container exited with a non-zero exit code 143

18/05/25 15:32:34 INFO mapreduce.Job:  map 100% reduce 0%
18/05/25 15:32:35 INFO mapreduce.Job: Job job_1526097883376_0063 failed with state FAILED due to: Task failed task_1526097883376_0063_m_000000
Job failed as tasks failed. failedMaps:1 failedReduces:0

18/05/25 15:32:36 INFO mapreduce.Job: Counters: 8
	Job Counters 
		Failed map tasks=4
		Launched map tasks=4
		Other local map tasks=4
		Total time spent by all maps in occupied slots (ms)=1710308
		Total time spent by all reduces in occupied slots (ms)=0
		Total time spent by all map tasks (ms)=1710308
		Total vcore-milliseconds taken by all map tasks=1710308
		Total megabyte-milliseconds taken by all map tasks=1751355392
18/05/25 15:32:36 WARN mapreduce.Counters: Group FileSystemCounters is deprecated. Use org.apache.hadoop.mapreduce.FileSystemCounter instead
18/05/25 15:32:36 INFO mapreduce.ImportJobBase: Transferred 0 bytes in 1,732.3058 seconds (0 bytes/sec)
18/05/25 15:32:36 WARN mapreduce.Counters: Group org.apache.hadoop.mapred.Task$Counter is deprecated. Use org.apache.hadoop.mapreduce.TaskCounter instead
18/05/25 15:32:36 INFO mapreduce.ImportJobBase: Retrieved 0 records.
18/05/25 15:32:36 ERROR tool.ImportTool: Import failed: Import job failed!
[root@node1 sqoop-1.4.7]#
程裕强
关注
专栏目录
sqoop从mysql中导入数据到parquet格式的hive中
07-07
sqoop导入数据到hive
Sqoop1.4.7编程，10分钟导入一亿数据
yanger_Bu的博客
05-19 1240
最近公司有个需求要让我使用sqoop往hdfs导入一亿的数据，要求5分钟完成，但是网上关于sqoop的资料比较少，因此就对此做一些补充。 要使用sqoop做一些嵌入式的编程，需要做如下准备。 window本地需要有hadoop的环境，这里就不对hadoop的环境做阐述，可自行上网查找 下载sqoop的包，并配置sqoop的环境变量. 配置好环境之后，我们就可以开始编程了。 maven没有sqoop的相关依赖 此时需要手动的将jar包添加到本地的maven仓库中，上图中，我们能看到sqoop-1.4.7
sqoop mysql 拒绝连接_Sqoop 连接MySQL报错：拒绝连接 (Connection refused)
weixin_39849671的博客
01-19 3410
MarsJ老师，看你的Sqoop视频，按照你视频里的步骤一步步实现的，但是准备用sqoop连接mysql时出现错误。困惑了很久，还请老师解惑，给予指导。@MarsJlog：[root@bigdata opt]# ls -lt /opt/sqoop-1.4.6/lib/mysql-connector-java-5.1.45-bin.jar-rwxrwxrwx. 1 root root 999810 ...
sqoop查看版本时 Warning的解决方法
追梦人的博客
10-28 5045
因为单节点虚拟机中没安装HBase、HCatalog 和Accumulo以及Zookeeper，所以报下列错误： `Warning: /usr/sqoop/sqoop-1.4.7.bin__hadoop-2.6.0/../hbase does not exist! HBase imports will fail. Please set $HBASE_HOME to the root of your HBase installation. Warning: /usr/sqoop/sqoop-1.4.7.bin
sqoop详解-导入导出
上帝之手&传奇 - MartinLee
08-12 1611
从mysql 导入到 hadoop 文件： http://ftp.wayne.edu/apache/sqoop/ -- sqoop下载地址：https://mirrors.tuna.tsinghua.edu.cn/apache/sqoop/1.4.7/ 大家下的时候注意自己的版本！！（注意不要下sqoop2，不是给我们这类需求使用的。） (一定一定要选择 bin__hadoop 版本，否则也可能报错java.lang.NoClassDefFoundError: org/apache/avro/.
sqoop安装笔记
baosq99的博客
01-11 408
先到http://sqoop.apache.org/官网下载sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz 解压安装 设置环境变量 [root@bigdata baosq]# vim /etc/profile 添加SQOOP_HOME和path SQOOP_HOME=/users/baosq/sqoop-1.4.6和path=$SQOOP_HOME/
尝试用sqoop将mysql数据导入hdfs中失败==
u013029726的博客
06-11 1683
使用命令行：sqoop import --connect jdbc:mysql://*.*.*.*:3306/test?characterEncoding=UTF-8--username aaa--password 'bbb' -tablelll 尝试将mysql的数据导入到hdfs中，结果一直导入失败，并显示： Warning: /usr/local/sqoop/sqoop/../hba...
Sqoop导Oracle数据到Hive
12-21
接下来使用`sqoop import`命令将Oracle数据库中的表`HADOOP_EVENT_INFO`导入到HDFS中，并同时导入到Hive表中。 ```bash sqoop import --connect jdbc:oracle:thin:@192.168.91.4:1521:ORCL --username JIAOTONG --...
2、sqoop导入（RMDB-mysql、sybase到HDFS-hive）
05-29
在大数据处理中，Sqoop 提供了方便的数据导入和导出功能，它能够将结构化的数据从传统数据库迁移到 Hadoop 生态系统中的组件，如 Hive。 在本主题中，我们将深入探讨 Sqoop 的导入功能，特别是如何将数据从 MySQL ...
sqoop从mysql到hive的时间字段问题
01-07
3、对比其他表，看看是大范围现象还是特殊情况，发现其他的同样情况字段的一样没有问题 二、解决办法 经过对比：sqoop在转换MySQL的datatime字段类型为hive的string时会出现问题：默认先转为对应时间戳，再转换为...
运行Sqoop出现hcatalog does not exist!…accumulo does not exist!解决方案
01-07
有一段时间没用sqoop了，今天打开进行测试的时候，发现命令行总是出现下面这样的警示信息： Warning: /opt/module/sqoop/bin/…/…/hcatalog does not exist! HCatalog jobs will fail. Please set $HCAT_HOME to the root of your HCatalog installation. Warning: /opt/module/sqoop/bin/…/…/accumulo does not exist! Accumulo imports will fail. Please set $ACCU
Hadoop-Sqoop-Oracle:使用Sqoop在Oracle数据库和HDFS之间进行导入和导出
04-07
Hadoop-Sqoop-Oracle 使用Sqoop在Oracle数据库和HDFS之间进行导入和导出 ... 可以将来自外部系统的数据导入HDFS并以Hive表和HBase表等各种Hadoop文件格式保存。 Sqoop v2仅支持保存到HDFS 在所有与JDBC
sqoop导入大表解决方案
XDSXHDYY的博客
08-22 1853
导入表过大时报错： is running 24993792B beyond the ‘PHYSICAL’ memory limit. Current usage: 1.0 GB of 1 GB physical memory used; 2.5 GB of 2.1 GB virtual memory used. Killing container. 方案一： yarn资源配置 mapreduce....
Sqoop全量增量将数据从SqlServer/MySQL导入HDFS/Hive，再从HDFS/Hive导出到数据库最全总结
qq_36881881的博客
03-29 2541
最近总结了很全的sqoop应用，有以下内容 1.SqlServer/MySQL全量增量导入HDFS/Hive， 2.HDFS导入hive 3.hdfs导出到SqlServer/MySQL 4.hive导出到hdfs 5.hive导出到SqlServer/MySQL 6.还有以上过程的注意事项、操作过程中可能遇到的错误、改正方法 如有不正确的地方，欢迎各位指正^_^；有不太清楚的...
Sqoop解决宽表内存溢出OutOfMemoryError
db_guy的博客
08-20 3188
oracle里面导入一张大款表，字段数非常多，而且有很多长文本，这些文本都需要用到，导入的时候报错OutOfMemoryError,内存溢出错误。 解决办法之一是增加内存，目前情况来说不显示，于是我采用另一种方法：减小 cache到内存的记录条数，即使用–fetch-size选项调整fetchSize。fetchSize默认值为1000,这里我们把它调小到200： sqoop import \ ...
大数据优化方案---Mysql中47G大表通过SQOOP导入数据仓库
北京小辉
05-10 1589
目录 一、需求 二、思路 三、解决方案 一、需求 1、由于公司的Mysql表中有很多单表存储量有几亿，单表达到40多个G 2、 二、思路 三、解决步骤
sqoop搭建配置使用
ypf2019的博客
04-23 2427
sqoop搭建及使用
[Sqoop]Sqoop安装与部署
SmartSi
10-08 1858
1. 下载 http://www.apache.org/dyn/closer.lua/sqoop/1.4.6 2. 解压 xiaosi@Qunar:~$ sudo tar -zxvf sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz -C /opt 进行重命名： xiaosi@Qunar:/opt$ sudo mv sq
Sqoop常用操作命令
曹利荣的博客
01-19 4453
sqoop的所有操作脚本在$SQOOP_HOME/bin 目录下 sqoop脚本 1、 [hadoop@hadoop001 bin]$ sqoop Warning: /home/hadoop/apps/sqoop-1.4.6-cdh5.7.0/../hbase does not exist! HBase imports will fail. Please set $HBASE_HOME ...
sqoop从oracle导入数据到hive
最新发布
06-10
使用Sqoop将数据从Oracle导入到Hive需要以下步骤： 1. 确保Hive和Oracle的JDBC驱动程序已正确安装并配置在Sqoop的CLASSPATH中。 2. 在Hive中创建一个表，以便Sqoop可以将数据导入到该表中。 3. 在Sqoop中使用import命令指定源数据的连接信息、目标表的信息、以及其他参数，例如选择要导入的列、指定分隔符等。 以下是一个示例Sqoop命令，用于将Oracle中的一个表导入到Hive中： ``` sqoop import \ --connect jdbc:oracle:thin:@//localhost:1521/ORCL \ --username <username> \ --password <password> \ --table <table_name> \ --hive-import \ --hive-table <hive_table_name> \ --create-hive-table \ --fields-terminated-by ',' \ --null-string '\\N' \ --null-non-string '\\N' ``` 其中，`<username>`和`<password>`是Oracle数据库的用户名和密码，`<table_name>`是要导入的Oracle表的名称，`<hive_table_name>`是要创建或导入数据的Hive表的名称。 `--hive-import`选项告诉Sqoop将数据导入到Hive中，`--create-hive-table`选项告诉Sqoop如果Hive表不存在则创建该表。 `--fields-terminated-by`选项指定源数据文件中列之间的分隔符。 `--null-string`和`--null-non-string`选项指定源数据文件中表示空值的字符串。 执行此命令后，Sqoop将使用Oracle JDBC驱动程序连接到Oracle数据库，并将数据导入到Hive表中。
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值