Sqoop export sequencefile to mysql

#1

How to export avro format file to MySQL using sqoop.

0 Likes

#2

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

0 Likes

#3

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.

0 Likes

#4

How is the sequence file generated?

0 Likes

#5

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

0 Likes

#6

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.

0 Likes

#7

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