Support Questions
Find answers, ask questions, and share your expertise

Unable to export a Sequence file to mysql using SQOOP export

Highlighted

Unable to export a Sequence file to mysql using SQOOP export

Explorer

HI

I am trying to export a sequence file back to MYSQL using SQOOP export and it is giving me below error. Initially i used to import a categories table from MYSQL to HDFS in sequence file format and then tired to export back to a table called test in MYSQL , which is failing with eroor

import command : sqoop import --connect "jdbc:mysql://sandbox.hortonworks.com:3306/retail_db" --username retail_dba --password hadoop --driver com.mysql.jdbc.Driver --table categories --target-dir /user/root/categories -m 1 --fields-terminated-by '|' --lines-terminated-by '\n' --null-string '\\N' --null-non-string '-1' --as-sequencefile --outdir /root/code --bindir /root/code ;

Export Command (failing):

sqoop export --connect "jdbc:mysql://sandbox.hortonworks.com:3306/retail_db" --username retail_dba --password-file /user/root/pass.file --driver com.mysql.jdbc.Driver --table test -m 5 --export-dir "hdfs://sandbox.hortonworks.com:8020/user/root/categories" --input-fields-terminated-by '|' --input-null-string '\\N' --input-null-non-string '\\N'

Error message:

16/06/02 17:16:58 INFO impl.YarnClientImpl: Submitted application application_1464851426921_0                                                                                                                      087
16/06/02 17:16:58 INFO mapreduce.Job: The url to track the job: http://sandbox.hortonworks.co                                                                                                                      m:8088/proxy/application_1464851426921_0087/
16/06/02 17:16:58 INFO mapreduce.Job: Running job: job_1464851426921_0087
16/06/02 17:17:08 INFO mapreduce.Job: Job job_1464851426921_0087 running in uber mode : false
16/06/02 17:17:08 INFO mapreduce.Job:  map 0% reduce 0%
16/06/02 17:17:27 INFO mapreduce.Job: Task Id : attempt_1464851426921_0087_m_000004_0, Status                                                                                                                       : FAILED
Error: java.lang.RuntimeException: java.io.IOException: WritableName can't load class: categories
        at org.apache.hadoop.io.SequenceFile$Reader.getValueClass(SequenceFile.java:2033)
        at org.apache.hadoop.io.SequenceFile$Reader.init(SequenceFile.java:1963)
        at org.apache.hadoop.io.SequenceFile$Reader.initialize(SequenceFile.java:1813)
        at org.apache.hadoop.io.SequenceFile$Reader.<init>(SequenceFile.java:1762)
        at org.apache.hadoop.io.SequenceFile$Reader.<init>(SequenceFile.java:1776)
        at org.apache.hadoop.mapreduce.lib.input.SequenceFileRecordReader.initialize(Sequence                                                                                                                      FileRecordReader.java:54)
        at org.apache.sqoop.mapreduce.CombineShimRecordReader.initialize(CombineShimRecordRea                                                                                                                      der.java:76)
        at org.apache.sqoop.mapreduce.CombineFileRecordReader.initialize(CombineFileRecordRea                                                                                                                      der.java:64)
        at org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.initialize(MapTask.java:5                                                                                                                      48)
        at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:786)
        at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
        at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:168)
        at java.security.AccessController.doPrivileged(Native Method)
        at javax.security.auth.Subject.doAs(Subject.java:415)
        at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:165                                                                                                                      7)
        at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:162)
Caused by: java.io.IOException: WritableName can't load class: categories
        at org.apache.hadoop.io.WritableName.getClass(WritableName.java:77)
        at org.apache.hadoop.io.SequenceFile$Reader.getValueClass(SequenceFile.java:2031)
        ... 15 more
Caused by: java.lang.ClassNotFoundException: Class categories not found
        at org.apache.hadoop.conf.Configuration.getClassByName(Configuration.java:2101)
        at org.apache.hadoop.io.WritableName.getClass(WritableName.java:75)
        ... 16 more
16/06/02 17:17:27 INFO mapreduce.Job: Task Id : attempt_1464851426921_0087_m_000002_0, Status                                                                                                                       : FAILED
Error: java.lang.RuntimeException: java.io.IOException: WritableName can't load class: catego                                                                                                                      ries
        at org.apache.hadoop.io.SequenceFile$Reader.getValueClass(SequenceFile.java:2033)
        at org.apache.hadoop.io.SequenceFile$Reader.init(SequenceFile.java:1963)
        at org.apache.hadoop.io.SequenceFile$Reader.initialize(SequenceFile.java:1813)
3 REPLIES 3
Highlighted

Re: Unable to export a Sequence file to mysql using SQOOP export

Explorer

I tired passing jar and class name as below , but then it is stuck at reduce phase for ever and no error message. It retires after 300 seconds for sometime and then fails

sqoop export --connect "jdbc:mysql://sandbox.hortonworks.com:3306/retail_db" --username retail_dba --password-file /user/root/pass.file --driver com.mysql.jdbc.Driver --table categories -m 1 --export-dir "hdfs://sandbox.hortonworks.com:8020/user/root/categories" --input-fields-terminated-by '|' --jar-file myclass.jar --class-name myclass

16/06/03 13:27:10 INFO input.FileInputFormat: Total input paths to process : 1 16/06/03 13:27:10 INFO input.FileInputFormat: Total input paths to process : 1 16/06/03 13:27:10 INFO mapreduce.JobSubmitter: number of splits:1 16/06/03 13:27:11 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1464959822670_0001 16/06/03 13:27:12 INFO impl.YarnClientImpl: Submitted application application_1464959822670_0001 16/06/03 13:27:12 INFO mapreduce.Job: The url to track the job:http://sandbox.hortonworks.com:8088/proxy/application_1464959822670_0001/ 16/06/03 13:27:12 INFO mapreduce.Job: Running job: job_1464959822670_0001 16/06/03 13:27:24 INFO mapreduce.Job: Job job_1464959822670_0001 running in uber mode : false 16/06/03 13:27:24 INFO mapreduce.Job: map 0% reduce 0% 16/06/03 13:27:38 INFO mapreduce.Job: map 100% reduce 0% 16/06/03 13:32:52 INFO mapreduce.Job: Task Id : attempt_1464959822670_0001_m_000000_0, Status : FAILED AttemptID:attempt_1464959822670_0001_m_000000_0 Timed out after 300 secs 16/06/03 13:32:53 INFO mapreduce.Job: map 0% reduce 0% 16/06/03 13:33:06 INFO mapreduce.Job: map 100% reduce 0%

Highlighted

Re: Unable to export a Sequence file to mysql using SQOOP export

Explorer

Comment from SqoopUserGuide @ sqoop.apache.org

"If the records to be exported were generated as the result of a previous import, then the original generated class can be used to read the data back. Specifying --jar-file and --class-name obviate the need to specify delimiters in this case."

Example:

If I import table say stud using below command ,

sqoop import --connect "jdbc:mysql://192.168.0.7:3306/test" --driver com.mysql.jdbc.Driver --table stud  --target-dir /user/guest/sqoop/stud -m 1 --fields-terminated-by '|' --lines-terminated-by '\n' --null-string '\\N' --null-non-string '-1' --as-sequencefile --outdir /home/guest/sqoop/code --bindir /home/guest/sqoop/code  --username root --password TESTPASS

This will import stud table data to HDFS dir /user/guest/sqoop/stud and it will also generate Java code/class to interpret sequenceFile data.

Then , to export files from /user/guest/sqoop/stud to mysql successfully, export command would be as given below.

sqoop export --connect "jdbc:mysql://192.168.0.7:3306/test" --username root --password TESTPASS --driver com.mysql.jdbc.Driver --table stud2 -m 5 --export-dir "hdfs://sandbox.hortonworks.com:8020/user/guest/sqoop/stud" --class-name stud  --jar-file stud.jar 

Re: Unable to export a Sequence file to mysql using SQOOP export

Guru

"categories" is the name of your export-dir, not a class, so my guess is that something is wrong with your command - not your environment. (You are not really getting a ClassNotFoundException - Sqoop is having trouble parsing your command properties).

Try removing the double-quotes around the value of your export-dir, and do you need to prefix it with hdfs://sandbox.hortonworks.com:8020, or can you try just using:

--export-dir /user/root/categories/