Ah, indeed, good catch!
(It’s single quote :)
Giovanni Lanzani
Chief Science Officer GoDataDriven
T: @gglanzani
M: +31 6 5120 6163
From: Matt Burgess<mailto:mattyb149@apache.org>
Sent: Wednesday, June 7, 2017 4:58 PM
To: users@nifi.apache.org<mailto:users@nifi.apache.org>
Subject: Re: Expression language in QueryRecord
Giovanni,
Expression Language is evaluated before the SQL query. So in your case you end up with a query
that looks like:
SELECT
message,
2015-12-03T11:50:24-0500 AS lmt
FROM
FLOWFILE
And it complains about the colon in the timestamp. Try putting the EL expression in quotes
(I think double-quotes but I can't remember).
Regards,
Matt
On Wed, Jun 7, 2017 at 10:51 AM, Giovanni Lanzani <giovannilanzani@godatadriven.com<mailto:giovannilanzani@godatadriven.com>>
wrote:
Hi Mark and Matt,
I think, in the form I wrote it, it does not work.
This is what the logs say
java.lang.RuntimeException: parse failed: Encountered ":" at line 4, column 16.
Was expecting one of:
<EOF>
"ORDER" ...
"LIMIT" ...
"OFFSET" ...
"FETCH" ...
"FROM" ...
"," ...
"UNION" ...
"INTERSECT" ...
"EXCEPT" ...
"MINUS" ...
at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:750)
at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:632)
at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:602)
at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:214)
at org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:595)
at org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:615)
at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:148)
at org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:218)
at org.apache.nifi.processors.standard.QueryRecord.query(QueryRecord.java:481)
at org.apache.nifi.processors.standard.QueryRecord.onTrigger(QueryRecord.java:280)
at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)
at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1118)
at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:144)
at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47)
at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:132)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
This is the SQL query
SELECT
message,
${file.lastModifiedTime} AS lmt
FROM
FLOWFILE
If I do the following it works
SELECT
message,
CURRENT_TIME AS lmt
FROM
FLOWFILE
(so that’s not a schema error).
Giovanni Lanzani
Chief Science Officer GoDataDriven
T: @gglanzani
M: +31 6 5120 6163
From: Mark Payne<mailto:markap14@hotmail.com>
Sent: Wednesday, June 7, 2017 4:03 PM
To: users@nifi.apache.org<mailto:users@nifi.apache.org>
Subject: Re: Expression language in QueryRecord
Giovanni,
Yes, this should work. In the upcoming version 1.3.0, there is actually an UpdateRecord processor
that
may actually be easier than this though. You'll be able to just add a property named /lmt
with a value of ${file.lastModifiedTime} and
that will insert it for you. :)
Thanks
-Mark
On Jun 7, 2017, at 9:59 AM, Giovanni Lanzani <giovannilanzani@godatadriven.com<mailto:giovannilanzani@godatadriven.com>>
wrote:
Sorry, hit send to quickly:
Hi,
Is it possible to do something like this in a QueryRecord processor:
SELECT
message,
${file.lastModifiedTime} AS lmt
FROM
FLOWFILE
?
i.e. access a flowfile attribute? The Record Reader is a json, that has the “message”
field, the Record Writer is also a JSON, with a message and lmt field.
I was hoping that, in this way, it would have been easy to add attributes to json’s (or
other things) without using the Executescript processor
(in reality I wanted some excuses to familiarize myself with all the new goodies you introduced
😊)
Thanks in advance, and sorry for the double email.
Giovanni Lanzani
Chief Science Officer GoDataDriven
T: @gglanzani
M: +31 6 5120 6163
From: Giovanni Lanzani<mailto:giovannilanzani@godatadriven.com>
Sent: Wednesday, June 7, 2017 3:56 PM
To: users@nifi.apache.org<mailto:users@nifi.apache.org>
Subject: Expression language in QueryRecord
Hi,
Is it possible to do something like this in a QueryRecord processor:
SELECT
message,
${file.lastModifiedTime} AS lmt
Giovanni Lanzani
Chief Science Officer GoDataDriven
T: @gglanzani
M: +31 6 5120 6163
|