Markus,
Thank you, kindly, for your assistance. I have tried using a local store without the --meta-connect and with the service running locally as in the example job create command below. The behavior is the same. I know I must be missing something obvious. Being a newb to sqoop I just don't know what it is. sqoop job --create sqooptest --meta-connect jdbc:hsqldb:hsql:// mydb.notyours.bs:16000/sqoop -- import --class-name codegen_sqooptest --bindir /root/sqoop_jobs/ --target-dir /sqooptest --connect "jdbc:mysql:// mydb.notyours.bs/sandbox" --username fakeuser --password-file /.sqooppass_fake --as-parquetfile --table sqooptest --incremental lastmodified --check-column last_update --merge-key id --last-value '1987-05-22 02:02:02' On Fri, Jan 27, 2017 at 11:52 AM, Markus Kemper <[email protected]> wrote: > Hello Matthew, > > Are you able to share your Sqoop job "create job" command? > Are you using a shared Sqoop Metastore or local? > > > Markus Kemper > Customer Operations Engineer > [image: www.cloudera.com] <http://www.cloudera.com> > > > On Fri, Jan 27, 2017 at 2:31 PM, Matthew Barnes <matthew.barnes@ > revolveclothing.com> wrote: > >> I am having an issue getting sqoop to update data correctly using >> last-value. After creating the job the first run goes great. The job config >> store shows the new updated date. The second run, the job also performs >> great doing the update you would expect it to. But at the end when it would >> normally write the new last value to the job, it fails with a ClassNotFound >> exception. After this, the last-value date never changes. >> >> The classfile that it says is missing is the same as the job name. In the >> jar file that gets generated, only a class named codegen_sqooptest.class >> exists. I've tried naively specifying the classfile to use the name >> "codegen_sqooptest" but this does not change the behavior or the name of >> the class it is looking for. >> >> Is there more that I need to do to set this up correctly? >> >> Below please find the description of the mysql table being sqooped and >> the sqoop job descriptions spewed from `sqoop job --show sqooptest` before >> each run. >> >> Please help me understand what I am missing? >> >> Thank you very kindly in advance! >> >> >> >> My Table: >> >> CREATE TABLE `sqooptest` ( >> >> `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, >> >> `value` varchar(255) DEFAULT NULL, >> >> `name` varchar(255) DEFAULT NULL, >> >> `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE >> CURRENT_TIMESTAMP, >> >> PRIMARY KEY (`id`), >> >> KEY `name` (`name`) >> >> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 >> >> >> Initial sqoop job: >> >> 17/01/26 18:04:58 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6 >> >> Job: sqooptest >> >> Tool: import >> >> Options: >> >> ---------------------------- >> >> verbose = false >> >> incremental.last.value = 1987-05-22 02:02:02 >> >> db.connect.string = jdbc:mysql://mydb.notyours.bs/sandbox >> >> codegen.java.classname = codegen_sqooptest >> >> codegen.output.delimiters.escape = 0 >> >> codegen.output.delimiters.enclose.required = false >> >> codegen.input.delimiters.field = 0 >> >> hbase.create.table = false >> >> hdfs.append.dir = false >> >> db.table = sqooptest >> >> codegen.input.delimiters.escape = 0 >> >> accumulo.create.table = false >> >> import.fetch.size = null >> >> codegen.input.delimiters.enclose.required = false >> >> db.username = notyou >> >> reset.onemapper = false >> >> codegen.output.delimiters.record = 10 >> >> import.max.inline.lob.size = 16777216 >> >> hbase.bulk.load.enabled = false >> >> hcatalog.create.table = false >> >> db.clear.staging.table = false >> >> incremental.col = last_update >> >> codegen.input.delimiters.record = 0 >> >> db.password.file = /.sqooppass_khan >> >> enable.compression = false >> >> hive.overwrite.table = false >> >> hive.import = false >> >> codegen.input.delimiters.enclose = 0 >> >> accumulo.batch.size = 10240000 >> >> hive.drop.delims = false >> >> codegen.output.delimiters.enclose = 0 >> >> hdfs.delete-target.dir = false >> >> codegen.output.dir = . >> >> codegen.auto.compile.dir = false >> >> relaxed.isolation = false >> >> mapreduce.num.mappers = 4 >> >> accumulo.max.latency = 5000 >> >> import.direct.split.size = 0 >> >> codegen.output.delimiters.field = 44 >> >> export.new.update = UpdateOnly >> >> incremental.mode = DateLastModified >> >> hdfs.file.format = ParquetFile >> >> codegen.compile.dir = /root/sqoop_jobs/ >> >> direct.import = false >> >> hdfs.target.dir = /sqooptest >> >> hive.fail.table.exists = false >> >> merge.key.col = id >> >> db.batch = false >> >> >> The results of the job: >> >> 17/01/26 18:07:14 INFO mapred.LocalJobRunner: map task executor complete. >> >> 17/01/26 18:07:15 INFO mapreduce.Job: Job job_local146376087_0001 >> completed successfully >> >> 17/01/26 18:07:16 INFO mapreduce.Job: Counters: 20 >> >> File System Counters >> >> FILE: Number of bytes read=72124136 >> >> FILE: Number of bytes written=73909624 >> >> FILE: Number of read operations=0 >> >> FILE: Number of large read operations=0 >> >> FILE: Number of write operations=0 >> >> HDFS: Number of bytes read=89728 >> >> HDFS: Number of bytes written=5442596 >> >> HDFS: Number of read operations=626 >> >> HDFS: Number of large read operations=0 >> >> HDFS: Number of write operations=130 >> >> Map-Reduce Framework >> >> Map input records=59269 Map output >> records=59269 Input split bytes=433 >> >> Spilled Records=0 Failed Shuffles=0 >> Merged Map outputs=0 GC time elapsed (ms)=345 >> >> Total committed heap usage (bytes)=1811939328 File >> Input Format Counters Bytes Read=0 File Output >> Format Counters >> >> Bytes Written=017/01/26 18:07:16 INFO >> mapreduce.ImportJobBase: Transferred 5.1905 >> >> MB in 14.2325 seconds (373.4433 KB/sec)17/01/26 18:07:16 INFO >> mapreduce.ImportJobBase: Retrieved 59269 records. >> >> 17/01/26 18:07:16 INFO tool.ImportTool: Saving incremental import state >> to the metastore17/01/26 18:07:16 INFO tool.ImportTool: Updated data for >> job: sqoo >> >> ptest >> >> >> the config after the first run: >> >> >> 17/01/26 18:08:09 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6 >> >> Job: sqooptest >> >> Tool: import >> >> Options: >> >> ---------------------------- >> >> verbose = false >> >> incremental.last.value = 2017-01-26 18:07:00.0 >> >> db.connect.string = jdbc:mysql://mydb.notyours.bs/sandbox >> >> codegen.java.classname = codegen_sqooptest >> >> codegen.output.delimiters.escape = 0 >> >> codegen.output.delimiters.enclose.required = false >> >> codegen.input.delimiters.field = 0 >> >> hbase.create.table = false >> >> hdfs.append.dir = false >> >> db.table = sqooptest >> >> codegen.input.delimiters.escape = 0 >> >> accumulo.create.table = false >> >> import.fetch.size = null >> >> codegen.input.delimiters.enclose.required = false >> >> db.username = notyou >> >> reset.onemapper = false >> >> codegen.output.delimiters.record = 10 >> >> import.max.inline.lob.size = 16777216 >> >> hbase.bulk.load.enabled = false >> >> hcatalog.create.table = false >> >> db.clear.staging.table = false >> >> incremental.col = last_update >> >> codegen.input.delimiters.record = 0 >> >> db.password.file = /.sqooppass_khan >> >> enable.compression = false >> >> hive.overwrite.table = false >> >> hive.import = false >> >> codegen.input.delimiters.enclose = 0 >> >> accumulo.batch.size = 10240000 >> >> hive.drop.delims = false >> >> codegen.output.delimiters.enclose = 0 >> >> hdfs.delete-target.dir = false >> >> codegen.output.dir = . >> >> codegen.auto.compile.dir = false >> >> relaxed.isolation = false >> >> mapreduce.num.mappers = 4 >> >> accumulo.max.latency = 5000 >> >> import.direct.split.size = 0 >> >> codegen.output.delimiters.field = 44 >> >> export.new.update = UpdateOnly >> >> incremental.mode = DateLastModified >> >> hdfs.file.format = ParquetFile >> >> codegen.compile.dir = /root/sqoop_jobs/ >> >> direct.import = false >> >> hdfs.target.dir = /sqooptest >> >> hive.fail.table.exists = false >> >> merge.key.col = id >> >> db.batch = false >> >> >> Everything looks right but if add a few records to the table and run the >> job again there is a failure in the saving of the job: >> >> 17/01/26 18:09:33 INFO mapred.LocalJobRunner: map task executor complete. >> >> 17/01/26 18:09:35 INFO mapreduce.Job: Job job_local1796782311_0001 >> completed successfully >> >> 17/01/26 18:09:35 INFO mapreduce.Job: Counters: 20 >> >> File System Counters >> >> FILE: Number of bytes read=72124136 >> >> FILE: Number of bytes written=73915944 >> >> FILE: Number of read operations=0 >> >> FILE: Number of large read operations=0 >> >> FILE: Number of write operations=0 >> >> HDFS: Number of bytes read=89818 >> >> HDFS: Number of bytes written=52253 >> >> HDFS: Number of read operations=626 >> >> HDFS: Number of large read operations=0 >> >> HDFS: Number of write operations=130 >> >> Map-Reduce Framework >> >> Map input records=100 >> >> Map output records=100 >> >> Input split bytes=433 >> >> Spilled Records=0 >> >> Failed Shuffles=0 >> >> Merged Map outputs=0 >> >> GC time elapsed (ms)=141 >> >> Total committed heap usage (bytes)=1958739968 >> >> File Input Format Counters >> >> Bytes Read=0 >> >> File Output Format Counters >> >> Bytes Written=0 >> >> 17/01/26 18:09:35 INFO mapreduce.ImportJobBase: Transferred 51.0283 KB in >> 11.2063 seconds (4.5535 KB/sec) >> >> 17/01/26 18:09:35 INFO mapreduce.ImportJobBase: Retrieved 100 records. >> >> 17/01/26 18:09:35 ERROR tool.ImportTool: Encountered IOException running >> import job: java.io.IOException: Could not load jar >> /root/sqoop_jobs/codegen_sqooptest.jar into JVM. (Could not find class >> sqooptest.) >> >> at org.apache.sqoop.util.ClassLoaderStack.addJarFile(ClassLoade >> rStack.java:92) >> >> at com.cloudera.sqoop.util.ClassLoaderStack.addJarFile(ClassLoa >> derStack.java:36) >> >> at org.apache.sqoop.tool.ImportTool.loadJars(ImportTool.java:11 >> 4) >> >> at org.apache.sqoop.tool.ImportTool.lastModifiedMerge(ImportToo >> l.java:449) >> >> at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java >> :506) >> >> at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:605) >> >> at org.apache.sqoop.tool.JobTool.execJob(JobTool.java:228) >> >> at org.apache.sqoop.tool.JobTool.run(JobTool.java:283) >> >> at org.apache.sqoop.Sqoop.run(Sqoop.java:143) >> >> at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70) >> >> at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:179) >> >> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:218) >> >> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:227) >> >> at org.apache.sqoop.Sqoop.main(Sqoop.java:236) >> >> Caused by: java.lang.ClassNotFoundException: sqooptest >> >> at java.net.URLClassLoader$1.run(URLClassLoader.java:366) >> >> at java.net.URLClassLoader$1.run(URLClassLoader.java:355) >> >> at java.security.AccessController.doPrivileged(Native Method) >> >> at java.net.URLClassLoader.findClass(URLClassLoader.java:354) >> >> at java.lang.ClassLoader.loadClass(ClassLoader.java:425) >> >> at java.net.FactoryURLClassLoader.loadClass(URLClassLoader.java >> :789) >> >> at java.lang.ClassLoader.loadClass(ClassLoader.java:358) >> >> at java.lang.Class.forName0(Native Method) >> >> at java.lang.Class.forName(Class.java:270) >> >> at org.apache.sqoop.util.ClassLoaderStack.addJarFile(ClassLoade >> rStack.java:88) >> >> ... 13 more >> >> >> The job config did not update the last-value: >> >> >> 17/01/26 18:10:53 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6 >> >> Job: sqooptest >> >> Tool: import >> >> Options: >> >> ---------------------------- >> >> verbose = false >> >> incremental.last.value = 2017-01-26 18:07:00.0 >> >> db.connect.string = jdbc:mysql://mydb.notyours.bs/sandbox >> >> codegen.java.classname = codegen_sqooptest >> >> codegen.output.delimiters.escape = 0 >> >> codegen.output.delimiters.enclose.required = false >> >> codegen.input.delimiters.field = 0 >> >> hbase.create.table = false >> >> hdfs.append.dir = false >> >> db.table = sqooptest >> >> codegen.input.delimiters.escape = 0 >> >> accumulo.create.table = false >> >> import.fetch.size = null >> >> codegen.input.delimiters.enclose.required = false >> >> db.username = notyou >> >> reset.onemapper = false >> >> codegen.output.delimiters.record = 10 >> >> import.max.inline.lob.size = 16777216 >> >> hbase.bulk.load.enabled = false >> >> hcatalog.create.table = false >> >> db.clear.staging.table = false >> >> incremental.col = last_update >> >> codegen.input.delimiters.record = 0 >> >> db.password.file = /.sqooppass_khan >> >> enable.compression = false >> >> hive.overwrite.table = false >> >> hive.import = false >> >> codegen.input.delimiters.enclose = 0 >> >> accumulo.batch.size = 10240000 >> >> hive.drop.delims = false >> >> codegen.output.delimiters.enclose = 0 >> >> hdfs.delete-target.dir = false >> >> codegen.output.dir = . >> >> codegen.auto.compile.dir = false >> >> relaxed.isolation = false >> >> mapreduce.num.mappers = 4 >> >> accumulo.max.latency = 5000 >> >> import.direct.split.size = 0 >> >> codegen.output.delimiters.field = 44 >> >> export.new.update = UpdateOnly >> >> incremental.mode = DateLastModified >> >> hdfs.file.format = ParquetFile >> >> codegen.compile.dir = /root/sqoop_jobs/ >> >> direct.import = false >> >> hdfs.target.dir = /sqooptest >> >> hive.fail.table.exists = false >> >> merge.key.col = id >> >> db.batch = false >> > >
