CODEWITHSUNDEEP
mysqldatetimeexporthdfssqoop
Lejla Džanko
Lejla Džanko

Reputation: 31

Sqoop 1.99.3, export into MySQL returns empty tables

Original question

I am working with Sqoop 1.99.3 and I am trying to export a file from HDFS to MySql, that contains a DateTime field. Unfortunately, after the job is executed, the target table is empty, and there are no error messages/logs.

An example of csv row:

Create statement for the MySQL table:

CREATE TABLE `unit` (
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `name` varchar(50) NOT NULL,
  `u_save_date` datetime DEFAULT NULL,
  `c_save_time` datetime NOT NULL,
   PRIMARY KEY (`id`));

Since I was asked to, here is my code. Nothing revolutionary, copied from Sqoop 5-minute demo Client Api guide (link) with modified parameters (here I pasted their default ones).

The code works just fine for the tables that do not contain datetime format. I also tried exporting through sqoop shell (with same parameters) but the target tables were, again, empty

Code in Java :

String url = "http://localhost:12000/sqoop/";
SqoopClient client = new SqoopClient(url);
MJob newjob = client.newJob(1, org.apache.sqoop.model.MJob.Type.EXPORT);
MJobForms connectorForm = newjob.getConnectorPart();
MJobForms frameworkForm = newjob.getFrameworkPart();

newjob.setName("ExportJob");
connectorForm.getStringInput("table.schemaName").setValue("");
connectorForm.getStringInput("table.tableName").setValue("table");
connectorForm.getStringInput("table.columns").setValue("id,name");

frameworkForm.getStringInput("input.inputDirectory").setValue("/input");

frameworkForm.getIntegerInput("throttling.extractors").setValue(1);
frameworkForm.getIntegerInput("throttling.loaders").setValue(1);

Status status = client.createJob(newjob);
if(status.canProceed()) {
  System.out.println("New Job ID: "+ newjob.getPersistenceId());
} else {
  System.out.println("Check for status and forms error ");
}

printMessage(newjob.getConnectorPart().getForms());
printMessage(newjob.getFrameworkPart().getForms());

Follow-up question

Okay, I've managed to resolve this problem. However, now I am facing the same problem with a different table (even with DateTime being properly formatted).

An example of csv row:

The last column is Java generated UUID and is stored as char(36).

Upvotes: 2

Views: 184

Answers (1)

Lejla Džanko
Lejla Džanko

Reputation: 31

I've figured out the problem with datetime fields. Datetime should be enclosed by single quotations(''), f. e:

  • 1,'name','2016-04-01 15:10:57','2016-05-31 05:00:59'

Upvotes: 1

Related Questions