question Re: Sqoop incremental: Output directory already exists in Archives of Support Questions (Read Only)

Sqoop incremental: Output directory already exists

Daan — Fri, 16 Sep 2022 09:00:52 GMT

Hi all!

I have a seemingly simple use case for Sqoop: incrementally import data from a MySQL db into HDFS. At first I tried Sqoop2, but it seems Sqoop2 doesn't support incremental imports yet. Am I correct in this? (Sqoop2 did imports fine btw)

Then I tried to use Sqoop (1) and figured out I need to create a job so Sqoop can automatically update stuff like the last value for me. This is the command I used to create a job:

sqoop job --create import-test -- import --connect jdbc:mysql://10.211.55.1/test_sqoop --username root -P --table test_incr_update --target-dir /user/vagrant/sqooptest --check-column updated --incremental lastmodified

When I run it for the first time, it works great. When I run it for the second time, I get:

ERROR tool.ImportTool: Encountered IOException running import job: org.apache.hadoop.mapred.FileAlreadyExistsException: Output directory hdfs://vm-cluster-node1:8020/user/vagrant/sqooptest already exists

I could of course remove the target dir before running the import again, but that would defeat the whole purpose of only getting the newer data and merging it with the old data (for which the old data needs to be present, I assume)!

Is this a (known) bug? Or am I doing something wrong? Any help would be appreciated 🙂

Re: Sqoop incremental: Output directory already exists

abe — Mon, 23 Jun 2014 18:20:19 GMT

Sqoop2 does not support incremental imports just yet (https://issues.apache.org/jira/browse/SQOOP-1168).

It looks like the command you're running is creating a saved job. Have you tried just executing the saved job (http://sqoop.apache.org/docs/1.4.4/SqoopUserGuide.html#_saved_jobs)? Seems like this is achievable via: sqoop job --exec import-test.

You don't need to create a job to perform incremental imports (http://sqoop.apache.org/docs/1.4.4/SqoopUserGuide.html#_incremental_imports).

-Abe

Re: Sqoop incremental: Output directory already exists

Daan — Tue, 24 Jun 2014 07:39:19 GMT

@abe wrote:
Sqoop2 does not support incremental imports just yet (https://issues.apache.org/jira/browse/SQOOP-1168).

It looks like the command you're running is creating a saved job. Have you tried just executing the saved job (http://sqoop.apache.org/docs/1.4.4/SqoopUserGuide.html#_saved_jobs)? Seems like this is achievable via: sqoop job --exec import-test.

You don't need to create a job to perform incremental imports (http://sqoop.apache.org/docs/1.4.4/SqoopUserGuide.html#_incremental_imports).

-Abe

sqoop job --exec import-test is actually the way I ran the saved job. As said, the first time it runs fine, the second time it complains about the output dir existing already.

The reason I used a saved job for this, is because of the promise that it will keep track of and autofill the last value.

Re: Sqoop incremental: Output directory already exists

abe — Tue, 24 Jun 2014 17:40:38 GMT

Hey there,

It seems you shouldn't have to do this, but adding "--append" to your command might help.

-Abe

Re: Sqoop incremental: Output directory already exists

abe — Tue, 24 Jun 2014 17:46:11 GMT

By the way, what version of CDH are you using?

Re: Sqoop incremental: Output directory already exists

abe — Tue, 24 Jun 2014 20:36:48 GMT

Also, it looks like https://issues.apache.org/jira/browse/SQOOP-1138 exists to address this concern.

Re: Sqoop incremental: Output directory already exists

Daan — Wed, 25 Jun 2014 07:38:48 GMT

I'm using CDH 5.0.2 together with Cloudera Manager 5.0.2. I think the SQOOP issue you linked, is exactly the problem I'm having. I shouldn't have to add --append because I'm already using lastmodified, which is the other incremental mode.

As long as SQOOP-1138 isn't fixed, SQOOP will be rather useless to me 🙂 The only alternative seems to be to export the whole database each time, and replace the old data with the new export.

Re: Sqoop incremental: Output directory already exists

Harkirat — Thu, 31 Jan 2019 06:48:02 GMT

You can use "delete-target-dir" , while running sqoop command

sqoop import --connect jdbc:mysql://1.2.3.4:1234/retailfgh --username 12345 --password 12345 --table departments --delete-target-dir --target-dir /poc/sqoop_destination --fields-terminated-by "~"

Re: Sqoop incremental: Output directory already exists

ruship — Wed, 20 Mar 2019 12:07:36 GMT

Any solution to this issue?

using --append in place of --lastmodified is not the correct solution as it won't update the record but create new record in hive.

--delete-target-dir defeats the purpose to update data as it will create new directory everytime which is same as importing entire source table into hdfs-hive everytime.

I tried using --merge-key but it gives following error:

19/03/20 07:07:41 ERROR tool.ImportTool: Import failed: java.io.IOException: Could not load jar /tmp/sqoop-gfctwnsg/compile/c63dd58c7ae7aa383d4fe8e795fd8604/FRESH.EMPLOYEERUSHI.jar into JVM. (Could not find class FRESH.EMPLOYEERUSHI.)
at org.apache.sqoop.util.ClassLoaderStack.addJarFile(ClassLoaderStack.java:92)
at com.cloudera.sqoop.util.ClassLoaderStack.addJarFile(ClassLoaderStack.java:36)
at org.apache.sqoop.tool.ImportTool.loadJars(ImportTool.java:120)
at org.apache.sqoop.tool.ImportTool.lastModifiedMerge(ImportTool.java:456)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:522)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:621)
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)
Caused by: java.lang.ClassNotFoundException: FRESH.EMPLOYEERUSHI
at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
at java.net.FactoryURLClassLoader.loadClass(URLClassLoader.java:814)
at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:348)
at org.apache.sqoop.util.ClassLoaderStack.addJarFile(ClassLoaderStack.java:88)

My sqoop command is as follows:

sqoop import \
--connect "jdbc:oracle:thin:@oraasmwd17-scan.nam.nsroot.net:8889/GENIFRD" \
--username FRESH \
--password C1T12016 \
--table FRESH.EMPLOYEERUSHI \
--merge-key id \
--target-dir /data/gfctwnsg/staging/hive/gfctwnsg_staging/rp86813/sqoopimportdir \
--incremental lastmodified \
--check-column MODIFIED_DATE \
--last-value '2019-03-20 06:43:59.0' \

My source Oracle table is as follows:

1 Rushi Pradhan engineer 30000 18-MAR-19
2 abc xyz doctor 20000 18-MAR-19

I changed the salary of id =1 and updated corresponding date manually.

Now I want to reflect this change at hive end also.

But there it is not allowing me to update the record but to only append.