Sqoop export sequencefile to mysql

How to export avro format file to MySQL using sqoop.

Nothing specific you just run sqoop export and it will take care. Have you tried running it?

Actually my export og squencefile was failing. I was thinking to do avrodatafile so wrongly posted for avro. I tried avro it is working with normal export.

With sequencefile my export was not exporting with following command, pls see last part of sqoop export log
[cloudera@quickstart ~]$ sqoop export --connect jdbc:mysql://127.0.0.1:3306/retail_db --username retail_dba --password cloudera **–table departments2 --export-dir /user/cloudera/departments1

16/12/19 23:53:56 INFO mapreduce.Job: Counters: 12
Job Counters
Failed map tasks=1
Killed map tasks=1
Launched map tasks=2
Data-local map tasks=2
Total time spent by all maps in occupied slots (ms)=2348288
Total time spent by all reduces in occupied slots (ms)=0
Total time spent by all map tasks (ms)=18346
Total vcore-seconds taken by all map tasks=18346
Total megabyte-seconds taken by all map tasks=2348288
Map-Reduce Framework
CPU time spent (ms)=0
Physical memory (bytes) snapshot=0
Virtual memory (bytes) snapshot=0
16/12/19 23:53:56 WARN mapreduce.Counters: Group FileSystemCounters is deprecated. Use org.apache.hadoop.mapreduce.FileSystemCounter instead
16/12/19 23:53:56 INFO mapreduce.ExportJobBase: Transferred 0 bytes in 27.9957 seconds (0 bytes/sec)
16/12/19 23:53:56 INFO mapreduce.ExportJobBase: Exported 0 records.
16/12/19 23:53:56 ERROR tool.ExportTool: Error during export: Export job failed!

But it was working as normal for following command

[cloudera@quickstart ~]$ sqoop export --connect jdbc:mysql://127.0.0.1:3306/retail_db --username retail_dba --password cloudera --table departments1 --export-dir /user/cloudera/departments1

Later today found solution as following for export which is exporting correctly;

[cloudera@quickstart 336d8375f779056a774eacdc97d03524]$ sqoop export --connect jdbc:mysql://127.0.0.1:3306/retail_db --username retail_dba --password cloudera --table departments2 --export-dir /user/cloudera/departments --jar-file departments.jar --class-name departments

    File System Counters
            FILE: Number of bytes read=0
            FILE: Number of bytes written=557960
            FILE: Number of read operations=0
            FILE: Number of large read operations=0
            FILE: Number of write operations=0
            HDFS: Number of bytes read=1514
            HDFS: Number of bytes written=0
            HDFS: Number of read operations=24
            HDFS: Number of large read operations=0
            HDFS: Number of write operations=0
    Job Counters
            Launched map tasks=4
            Data-local map tasks=4
            Total time spent by all maps in occupied slots (ms)=4453632
            Total time spent by all reduces in occupied slots (ms)=0
            Total time spent by all map tasks (ms)=34794
            Total vcore-seconds taken by all map tasks=34794
            Total megabyte-seconds taken by all map tasks=4453632
    Map-Reduce Framework
            Map input records=7
            Map output records=7
            Input split bytes=691
            Spilled Records=0
            Failed Shuffles=0
            Merged Map outputs=0
            GC time elapsed (ms)=609
            CPU time spent (ms)=6210
            Physical memory (bytes) snapshot=559124480
            Virtual memory (bytes) snapshot=2933907456
            Total committed heap usage (bytes)=189792256
    File Input Format Counters
            Bytes Read=0
    File Output Format Counters
            Bytes Written=0

16/12/20 00:08:41 INFO mapreduce.ExportJobBase: Transferred 1.4785 KB in 37.4495 seconds (40.4278 bytes/sec)
16/12/20 00:08:41 INFO mapreduce.ExportJobBase: Exported 7 records.

Thank you sir.

How is the sequence file generated?

Sequence file generated by sqoop import. Same jar file and class file was used to export sequence file to MySQL.

Can you change the subject?

Also give sqoop import and export commands you are using. If you have hive table, please share the details about hive query as well.

Dear Durga Sir,

Pls see sqoop import into HDFS command as below:

sqoop import --connect jdbc:mysql://127.0.0.1:3306/retail_db --username retail_dba --password cloudera --as-sequencefile -m 1 --table departments1

If I run following command i.e hdfs dir name and mysql table name is same then it exported without error:

[cloudera@quickstart ~]$ sqoop export --connect jdbc:mysql://127.0.0.1:3306/retail_db --username retail_dba --password cloudera --table departments1 --export-dir /user/cloudera/departments1

If I run following command i.e hdfs dir name and mysql table name is not same then it did not export:

[cloudera@quickstart ~]$ sqoop export --connect jdbc:mysql://127.0.0.1:3306/retail_db --username retail_dba --password cloudera --table departments2 --export-dir /user/cloudera/departments1

Using jar and class file generated while import, by running below command export worked without error:(i had into the temp dir where the jar and class file was there and run the export else it will fail again)

[cloudera@quickstart 336d8375f779056a774eacdc97d03524]$ sqoop export --connect jdbc:mysql://127.0.0.1:3306/retail_db --username retail_dba --password cloudera --table departments2 --export-dir /user/cloudera/departments1 --jar-file departments1.jar --class-name departments1

Also while exporting from the hive table to mysql same issue pls see below commands:

[cloudera@quickstart ~]$ sqoop import --connect jdbc:mysql://127.0.0.1:3306/retail_db --username retail_dba --password cloudera --as-sequencefile --warehouse-dir /user/hive/warehouse/sqoop_test.db -m 1 --fields-terminated-by ‘,’ --table departments1

Below export command without error

[cloudera@quickstart ~]$ sqoop export --connect jdbc:mysql://127.0.0.1:3306/retail_db --username retail_dba --password cloudera --table departments1 -m 1 --export-dir /user/hive/warehouse/sqoop_test.db/departments1

Below export command had error

[cloudera@quickstart ~]$ sqoop export --connect jdbc:mysql://127.0.0.1:3306/retail_db --username retail_dba --password cloudera --table departments2 -m 1 --export-dir /user/hive/warehouse/sqoop_test.db/departments1

Need to go in to the temp folder to run export else it was failing as below:

[cloudera@quickstart ~]$ sqoop export --connect jdbc:mysql://127.0.0.1:3306/retail_db --username retail_dba --password cloudera --table departments2 -m 1 --export-dir /user/hive/warehouse/sqoop_test.db/departments1 --jar-file /tmp/sqoop-cloudera/compile/591922d20c5901bb88128cfac2f38c4e/departments1.jar --class-name /tmp/sqoop-cloudera/compile/591922d20c5901bb88128cfac2f38c4e/departments1
Warning: /usr/lib/sqoop/…/accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
16/12/22 01:23:07 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.7.0
16/12/22 01:23:07 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
16/12/22 01:23:08 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
16/12/22 01:23:08 INFO tool.CodeGenTool: Using existing jar: /tmp/sqoop-cloudera/compile/591922d20c5901bb88128cfac2f38c4e/departments1.jar
16/12/22 01:23:08 INFO mapreduce.ExportJobBase: Beginning export of departments2
16/12/22 01:23:08 ERROR tool.ExportTool: Encountered IOException running export job: java.io.IOException: Could not load jar /tmp/sqoop-cloudera/compile/591922d20c5901bb88128cfac2f38c4e/departments1.jar into JVM. (Could not find class /tmp/sqoop-cloudera/compile/591922d20c5901bb88128cfac2f38c4e/departments1.)

Below successful export command

[cloudera@quickstart ~]$ cd /tmp/sqoop-cloudera/compile/591922d20c5901bb88128cfac2f38c4e

[cloudera@quickstart 591922d20c5901bb88128cfac2f38c4e]$ sqoop export --connect jdbc:mysql://127.0.0.1:3306/retail_db --username retail_dba --password cloudera --table departments2 -m 1 --export-dir /user/hive/warehouse/sqoop_test.db/departments1 --jar-file departments1.jar --class-name departments1

1 Like