Expression language in QueryRecord

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Expression language in QueryRecord

Giovanni Lanzani

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

 

Reply | Threaded
Open this post in threaded view
|

RE: Expression language in QueryRecord

Giovanni Lanzani

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: [hidden email]
Sent: Wednesday, June 7, 2017 3:56 PM
To: [hidden email]
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

 

Reply | Threaded
Open this post in threaded view
|

Re: Expression language in QueryRecord

Matt Burgess-2
In reply to this post by Giovanni Lanzani
The documentation says that is true (see the Dynamic Properties
section under [1]), although I haven't tried it myself.

Regards,
Matt

[1] https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-standard-nar/1.2.0/org.apache.nifi.processors.standard.QueryRecord/index.html

On Wed, Jun 7, 2017 at 9:56 AM, Giovanni Lanzani
<[hidden email]> wrote:

> 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
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Expression language in QueryRecord

Mark Payne
In reply to this post by Giovanni Lanzani
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 <[hidden email]> 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: [hidden email]
Sent: Wednesday, June 7, 2017 3:56 PM
To: [hidden email]
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

Reply | Threaded
Open this post in threaded view
|

RE: Expression language in QueryRecord

Giovanni Lanzani
In reply to this post by Giovanni Lanzani

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: [hidden email]
Sent: Wednesday, June 7, 2017 4:03 PM
To: [hidden email]
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 <[hidden email]> 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: [hidden email]
Sent: Wednesday, June 7, 2017 3:56 PM
To: [hidden email]
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

 

 

Reply | Threaded
Open this post in threaded view
|

Re: Expression language in QueryRecord

Matt Burgess-2
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 <[hidden email]> 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: [hidden email]
Sent: Wednesday, June 7, 2017 4:03 PM
To: [hidden email]
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 <[hidden email]> 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: [hidden email]
Sent: Wednesday, June 7, 2017 3:56 PM
To: [hidden email]
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

 

 


Reply | Threaded
Open this post in threaded view
|

RE: Expression language in QueryRecord

Giovanni Lanzani

Ah, indeed, good catch!

 

(It’s single quote :)

 

Giovanni Lanzani
Chief Science Officer GoDataDriven
T: @gglanzani
M: +31 6 5120 6163

 

From: [hidden email]
Sent: Wednesday, June 7, 2017 4:58 PM
To: [hidden email]
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 <[hidden email]> 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: [hidden email]
Sent: Wednesday, June 7, 2017 4:03 PM
To: [hidden email]
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 <[hidden email]> 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: [hidden email]
Sent: Wednesday, June 7, 2017 3:56 PM
To: [hidden email]
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