Tag Archives: Sqoop error

[Solved] sqoop Error: jSQLException in nextKeyValue Caused by: ORA-24920:column size too large for client

Question

When importing Oracle data with sqoop, the following errors are reported:

INFO mapreduce.Job: Task Id : attempt_1646802944907_15460_m_000000_1, Status : FAILED
Error: java.io.IOException: SQLException in nextKeyValue
        at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:275)
        at org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.nextKeyValue(MapTask.java:568)
        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:799)
        at org.apache.hadoop.mapred.MapTask.run(MapTask.java:347)
        at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:174)
        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:1875)
        at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:168)
Caused by: java.sql.SQLException: ORA-24920: column size too large for client

reason

Before using sqoop import other database is normal, this time from the new database import data problems, first check what is the difference between the two databases, found an Oracle version is 11, the new Oracle database version is 19, which may be the cause of the problem.
Go online to check the ORA-24920 error, said to upgrade the oracle client, further speculation may be the problem of Oracle driver.
Under the lib file of sqoop tool, the Oracle JDBC driver found for sqoop is ojdbc6.jar, which does not match with Oracle version 19.
You can check the Oracle version and the corresponding Oracle JDBC driver version on this page:
https://www.oracle.com/database/technologies/faq-jdbc.html#02_03
The screenshot is as follows:

the link to the download page is as follows:
https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html

Solution:

According to the version, ojdbc8.0.jar was downloaded. After uploading, delete the original version and re import the data.
the driver of the original version here needs to be deleted or moved, otherwise it will not succeed. Guess that if there are two versions, the old version may be read

[Solved] Sqoop Error: ERROR tool.ImportTool: Import failed: java.io.IOException 

21/11/08 12:13:10 ERROR tool.ImportTool: Import failed: java.io.IOException: Cannot initialize Cluster. Please check your configuration for mapreduce.framework.name and the correspond server addresses.
        at org.apache.hadoop.mapreduce.Cluster.initialize(Cluster.java:143)
        at org.apache.hadoop.mapreduce.Cluster.<init>(Cluster.java:108)
        at org.apache.hadoop.mapreduce.Cluster.<init>(Cluster.java:101)
        at org.apache.hadoop.mapreduce.Job$10.run(Job.java:1311)
        at org.apache.hadoop.mapreduce.Job$10.run(Job.java:1307)
        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:1844)
        at org.apache.hadoop.mapreduce.Job.connect(Job.java:1306)
        at org.apache.hadoop.mapreduce.Job.submit(Job.java:1335)
        at org.apache.hadoop.mapreduce.Job.waitForCompletion(Job.java:1359)
        at org.apache.sqoop.mapreduce.ImportJobBase.doSubmitJob(ImportJobBase.java:200)
        at org.apache.sqoop.mapreduce.ImportJobBase.runJob(ImportJobBase.java:173)
        at org.apache.sqoop.mapreduce.ImportJobBase.runImport(ImportJobBase.java:270)
        at org.apache.sqoop.manager.SqlManager.importTable(SqlManager.java:692)
        at org.apache.sqoop.manager.MySQLManager.importTable(MySQLManager.java:127)
        at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:520)
        at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:628)
        at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
        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)

The above error occurs when running the sqoop script (the content of the script is to import MySQL data into HDFS). It is found that there is a lack of dependency. I still report an error after copying the two jar packages hadoop-mapreduce-client-common-2.8.5.jar and hadoop-mapreduce-client-core-2.8.5.jar to the Lib directory of sqoop, Then I copied all the jar packages in the hadoop-2.8.5/share/hadoop/mapreduce directory of Hadoop to solve the problem and run the script successfully. It was simple and violent.

Step on the pit — error reported by sqoop tool.ExportTool : Error during export

@To live better

Step on the pit — error reported by sqoop tool.ExportTool : Error during export

The error printed on the console is

19/04/19 20:17:09 ERROR mapreduce.ExportJobBase: Export job failed!
19/04/19 20:17:09 ERROR tool.ExportTool: Error during export: 
Export job failed!
	at org.apache.sqoop.mapreduce.ExportJobBase.runExport(ExportJobBase.java:445)
	at org.apache.sqoop.manager.MySQLManager.upsertTable(MySQLManager.java:145)
	at org.apache.sqoop.tool.ExportTool.exportTable(ExportTool.java:73)
	at org.apache.sqoop.tool.ExportTool.run(ExportTool.java:99)
	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)

finishing touch : this error indicates that the fields of MySQL and hive do not correspond or the data format is different
solution:
Step 1 : check whether the structure (field name and data type) of MySQL and hive tables are consistent.
Step 2 : check whether the data has been imported?If the data is not imported, please change the time type in MySQL and hive to string or varchar in the first step. If there is an import, but the imported data is incomplete or incorrect. It must be that your data type is inconsistent with the actual data. There are two situations. See the following three steps for details.
Step 3 : in MySQL to hive, please be sure to check whether your data contains the default line break for hive table creation ( lines terminated by '\ n' ). If yes, add hive delims replacement or hive drop import delims to the sqoop statement, as follows

#!/bin/bash
/usr/local/package/sqoop-1.4.7.bin/bin/sqoop import \
--connect jdbc:mysql://ip:3306/mysql_DB \
--username root \
--password 1q2w3e4r \
--table mysql_Table \
--delete-target-dir \
--hive-delims-replacement , \
--hive-import \
--hive-overwrite \
--hive-database hive_DB \
--hive-table hive_Table \
--hive-partition-key ymday \
--hive-partition-value 20190419 \
--fields-terminated-by '\t' \
-m 1

Step 4 : in MySQL to hive, please be sure to check whether your data contains the field separator commonly used in hive table creation ( Fields terminated by '\ t' ). If yes, the fields terminated by parameter cannot be used in the sqoop statement, and the code is as follows:

Step 5 : if there are many empty columns from hive to hive, sometimes the above error will be reported, then add input null string and input null non string in the sqoop statement, and the following code
remember ^ A, and use Ctrl + V and Ctrl + A in VI of Linux To generate, you can't copy the following ^ a
remember ^ a directly. In VI of Linux, you can't copy the following ^ a
remember ^ a directly. In VI of Linux, you can't copy the following ^ a directly

#!/bin/bash
/usr/local/package/sqoop-1.4.7.bin/bin/sqoop export \
--connect "jdbc:mysql://ip:3306/report_db?useUnicode=true&characterEncoding=utf-8" \
--username root \
--password 1q2w3e4r \
--table mysql_table \
--columns name,age \
--update-key name \
--update-mode allowinsert \
--input-null-string '\\N' \
--input-null-non-string '\\N' \
--export-dir "/hive/warehouse/dw_db.db/hive_table/ymday=20190419/*" \
--input-fields-terminated-by '^A' \
-m 1

====================================================================

@To live better

If you have any questions about the blog, please leave a message