java.sql.SQLException: Error while compiling statement: FAILED: SemanticException Line 0:-1 Invalid column reference 'first'
Is there a column 'first' in your database? While I am not much database experiences I would miss some values to insert with that sql: insert overwrite table address_target select t2.first,t2.second,t2.three,t2.four,t2.five,t2.six,t2.seven from Address t2 left outer join address_target t1 on t1.postalcode=t2.postalcode Jan > -----Ursprüngliche Nachricht----- > Von: smilevasu6 [mailto:[email protected]] > Gesendet: Montag, 8. Dezember 2014 05:16 > An: [email protected] > Betreff: Re: AW: Camel - Array > > Hi All, > > Thank you for support. > > I am able get the table name and able to load the data into the table. > > Need one more small help from your end. > > I am trying for dynamic insertion. where again its not finding the > element name. > > PSB, > > Router: > > from("file:///home/cloudera/Desktop/?fileName=Sample.xsd") > > .bean(XSDToCamel.class,"parseXSD(/home/cloudera/Desktop/Sample.xs > d)") > > .setHeader("length", simple("${body.length}")) > .setHeader("table", simple("${body[0]}")) > .setHeader("first", simple("${body[1]}")) > .setHeader("second", simple("${body[2]}")) > .setHeader("three", simple("${body[3]}")) > .setHeader("four", simple("${body[4]}")) > .setHeader("five", simple("${body[5]}")) > .setHeader("six", simple("${body[6]}")) > .setHeader("seven", simple("${body[7]}")) > .setBody(simple("load data local inpath > '/sri/address.txt' > OVERWRITE into table ${in.header.table}")) > > .to("jdbc:fromdatasource?resetAutoCommit=false") > .setBody(simple("insert overwrite table address_target > select t2.first, t2.second,t2.three,t2.four,t2.five,t2.six,t2.seven > from ${in.header.table} t2 left outer join address_target t1 on > t1.postalcode=t2.postalcode")) > .to("jdbc:todatasource?resetAutoCommit=false") > .end(); > > > > And error: > > 20:10:39.828 [Camel (camel-1) thread #0 - > file:///home/cloudera/Desktop/] DEBUG > o.a.c.processor.DefaultErrorHandler - Failed delivery for (MessageId: > ID-quickstart-cloudera-33319-1418011835914-0-3 on ExchangeId: > ID-quickstart-cloudera-33319-1418011835914-0-2). On delivery attempt: 0 > caught: java.sql.SQLException: Error while compiling statement: FAILED: > SemanticException Line 0:-1 Invalid column reference 'first' > 20:10:39.867 [Camel (camel-1) thread #0 - > file:///home/cloudera/Desktop/] ERROR > o.a.c.processor.DefaultErrorHandler - Failed delivery for (MessageId: > ID-quickstart-cloudera-33319-1418011835914-0-3 on ExchangeId: > ID-quickstart-cloudera-33319-1418011835914-0-2). Exhausted after > delivery > attempt: 1 caught: java.sql.SQLException: Error while compiling > statement: > FAILED: SemanticException Line 0:-1 Invalid column reference 'first' > > Message History > ----------------------------------------------------------------------- > ---------------------------------------------------------------- > RouteId ProcessorId Processor > Elapsed (ms) > [route1 ] [route1 ] > [file:///home/cloudera/Desktop/?fileName=Sample.xsd > ] [ 1195] > [route1 ] [bean1 ] > [bean[com.edms.emagia.XSDToCamel@76fc1719] > ] [ 86] > [route1 ] [setHeader1 ] [setHeader[length] > ] [ 12] > [route1 ] [setHeader2 ] [setHeader[table] > ] [ 5] > [route1 ] [setHeader3 ] [setHeader[first] > ] [ 1] > [route1 ] [setHeader4 ] [setHeader[second] > ] [ 1] > [route1 ] [setHeader5 ] [setHeader[three] > ] [ 1] > [route1 ] [setHeader6 ] [setHeader[four] > ] [ 1] > [route1 ] [setHeader7 ] [setHeader[five] > ] [ 0] > [route1 ] [setHeader8 ] [setHeader[six] > ] [ 2] > [route1 ] [setHeader9 ] [setHeader[seven] > ] [ 1] > [route1 ] [setBody1 ] [setBody[simple{Simple: load > data > local inpath '/sri/address.txt' OVERWRITE int] [ 13] > [route1 ] [to1 ] > [jdbc:fromdatasource?resetAutoCommit=false > ] [ 916] > [route1 ] [setBody2 ] [setBody[simple{Simple: > insert > overwrite table address_target select t2.first, ] [ 2] > [route1 ] [to2 ] > [jdbc:todatasource?resetAutoCommit=false > ] [ 147] > > Exchange > ----------------------------------------------------------------------- > ---------------------------------------------------------------- > Exchange[ > Id ID-quickstart-cloudera-33319-1418011835914-0- > 2 > ExchangePattern InOnly > Headers > {breadcrumbId=ID-quickstart-cloudera-33319-1418011835914-0-1, > CamelFileAbsolute=true, > CamelFileAbsolutePath=/home/cloudera/Desktop/Sample.xsd, > CamelFileLastModified=1417177680000, CamelFileLength=671, > CamelFileName=Sample.xsd, CamelFileNameConsumed=Sample.xsd, > CamelFileNameOnly=Sample.xsd, CamelFileParent=/home/cloudera/Desktop, > CamelFilePath=/home/cloudera/Desktop/Sample.xsd, > CamelFileRelativePath=Sample.xsd, CamelJdbcUpdateCount=0, > CamelRedelivered=false, CamelRedeliveryCounter=0, first=Recipient, > five=County, four=Town, length=8, second=House, seven=Country, > six=PostCode, table=Address, three=Street} > BodyType String > Body insert overwrite table address_target select > t2.first, > t2.second,t2.three,t2.four,t2.five,t2.six,t2.seven from Address t2 left > outer join address_target t1 on t1.postalcode=t2.postalcode ] > > Stacktrace > ----------------------------------------------------------------------- > ---------------------------------------------------------------- > java.sql.SQLException: Error while compiling statement: FAILED: > SemanticException Line 0:-1 Invalid column reference 'first' > at org.apache.hive.jdbc.Utils.verifySuccess(Utils.java:167) > at > org.apache.hive.jdbc.Utils.verifySuccessWithInfo(Utils.java:155) > at > org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:210) > at > org.apache.camel.component.jdbc.JdbcProducer.doCreateAndExecuteSqlState > ment(JdbcProducer.java:184) > at > org.apache.camel.component.jdbc.JdbcProducer.createAndExecuteSqlStateme > nt(JdbcProducer.java:116) > at > org.apache.camel.component.jdbc.JdbcProducer.processingSqlWithoutSettin > gAutoCommit(JdbcProducer.java:106) > at > org.apache.camel.component.jdbc.JdbcProducer.process(JdbcProducer.java: > 68) > at > org.apache.camel.util.AsyncProcessorConverterHelper$ProcessorToAsyncPro > cessorBridge.process(AsyncProcessorConverterHelper.java:61) > at > org.apache.camel.processor.SendProcessor.process(SendProcessor.java:113 > ) > at > org.apache.camel.management.InstrumentationProcessor.process(Instrument > ationProcessor.java:72) > at > org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErr > orHandler.java:398) > at > org.apache.camel.processor.CamelInternalProcessor.process(CamelInternal > Processor.java:191) > at org.apache.camel.processor.Pipeline.process(Pipeline.java:118) > at org.apache.camel.processor.Pipeline.process(Pipeline.java:80) > at > org.apache.camel.processor.CamelInternalProcessor.process(CamelInternal > Processor.java:191) > at > org.apache.camel.component.file.GenericFileConsumer.processExchange(Gen > ericFileConsumer.java:423) > at > org.apache.camel.component.file.GenericFileConsumer.processBatch(Generi > cFileConsumer.java:211) > at > org.apache.camel.component.file.GenericFileConsumer.poll(GenericFileCon > sumer.java:175) > at > org.apache.camel.impl.ScheduledPollConsumer.doRun(ScheduledPollConsumer > .java:187) > at > org.apache.camel.impl.ScheduledPollConsumer.run(ScheduledPollConsumer.j > ava:114) > at > java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) > at > java.util.concurrent.FutureTask.runAndReset(FutureTask.java:304) > at > java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.ac > cess$301(ScheduledThreadPoolExecutor.java:178) > at > java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.ru > n(ScheduledThreadPoolExecutor.java:293) > at > java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.ja > va:1145) > at > java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.j > ava:615) > at java.lang.Thread.run(Thread.java:745) > > > > Please help > > > > -- > View this message in context: http://camel.465427.n5.nabble.com/Camel- > Array-tp5760111p5760262.html > Sent from the Camel - Users mailing list archive at Nabble.com.
