You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@nifi.apache.org by Eric Chaves <er...@uolet.com> on 2019/10/16 17:30:22 UTC

What UDFs are supported by QueryRecord?

Hi Folks,

I'd like to use a SQL statement in QueryRecords like this uses the extract
the last 10 chars of  field and compare it to today, similar to the line
below:

SELECT substring( '/index', 10, -1) as expired FROM FLOWFILE WHERE expired
<= '${now():format("yyyy-MM-dd")}'

This statement is not work and I can't find a list of QueryRecord supported
UDF or if (nd how) I can use RecordPath functions.

Is there any documentation where I can see the available UDFs?

Regards,

Re: What UDFs are supported by QueryRecord?

Posted by Mark Payne <ma...@hotmail.com>.
Eric,

I’m not sure that I understand. Can you provide an example illustrating what you’d like to do?

Thanks
-Mark

Sent from my iPhone

On Oct 17, 2019, at 4:49 PM, Eric Chaves <er...@uolet.com> wrote:


Hi mark,

Thanks for pointing that out but from the docs I only got how to use RPATH to get a RecordPath value. How should I do for example if I wanted to apply a RecordPath function to in a Record Field?

Em qui, 17 de out de 2019 às 14:57, Mark Payne <ma...@hotmail.com>> escreveu:
Eric,

You can use RecordPath with QueryRecord, via the RPATH, RPATH_STRING, RPATH_INT, etc. These are explained in the Processor's documentation. For example, see [1].

You can also use the Expression Language with QueryRecord. The Expression Language is evaluated before the SQL is parsed. So, for example, if you had an attribute named 'Field of Interest' you could actually use SQL like:

SELECT ${'Field of Interest'}
FROM FLOWFILE

Thanks
-Mark

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

On Oct 16, 2019, at 2:10 PM, Eric Chaves <er...@uolet.com>> wrote:

Ok, figured out a way to do it. I noticed that QueryRecord uses Apache Calcite so I tried some of Calcite functions until I got into this statement that seems to work:

SELECT index FROM FLOWFILE WHERE CAST( SUBSTRING(index FROM CHAR_LENGTH(index)-9) AS DATE) <= CURRENT_DATE

Anyhow, I would still like to know if (and how) I could use either NiFi expression or RecordPath functions in a QueryRecord statement.

Thanks in advance,

Em qua, 16 de out de 2019 às 14:30, Eric Chaves <er...@uolet.com>> escreveu:
Hi Folks,

I'd like to use a SQL statement in QueryRecords like this uses the extract the last 10 chars of  field and compare it to today, similar to the line below:

SELECT substring( '/index', 10, -1) as expired FROM FLOWFILE WHERE expired <= '${now():format("yyyy-MM-dd")}'

This statement is not work and I can't find a list of QueryRecord supported UDF or if (nd how) I can use RecordPath functions.

Is there any documentation where I can see the available UDFs?

Regards,

Re: What UDFs are supported by QueryRecord?

Posted by Eric Chaves <er...@uolet.com>.
Hi mark,

Thanks for pointing that out but from the docs I only got how to use RPATH
to get a RecordPath value. How should I do for example if I wanted to apply
a RecordPath function to in a Record Field?

Em qui, 17 de out de 2019 às 14:57, Mark Payne <ma...@hotmail.com>
escreveu:

> Eric,
>
> You can use RecordPath with QueryRecord, via the RPATH, RPATH_STRING,
> RPATH_INT, etc. These are explained in the Processor's documentation. For
> example, see [1].
>
> You can also use the Expression Language with QueryRecord. The Expression
> Language is evaluated before the SQL is parsed. So, for example, if you had
> an attribute named 'Field of Interest' you could actually use SQL like:
>
> SELECT ${'Field of Interest'}
> FROM FLOWFILE
>
> Thanks
> -Mark
>
> [1]
> http://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-standard-nar/1.9.2/org.apache.nifi.processors.standard.QueryRecord/index.html
>
> On Oct 16, 2019, at 2:10 PM, Eric Chaves <er...@uolet.com> wrote:
>
> Ok, figured out a way to do it. I noticed that QueryRecord uses Apache
> Calcite so I tried some of Calcite functions until I got into this
> statement that seems to work:
>
> SELECT index FROM FLOWFILE WHERE CAST( SUBSTRING(index FROM
> CHAR_LENGTH(index)-9) AS DATE) <= CURRENT_DATE
>
> Anyhow, I would still like to know if (and how) I could use either NiFi
> expression or RecordPath functions in a QueryRecord statement.
>
> Thanks in advance,
>
> Em qua, 16 de out de 2019 às 14:30, Eric Chaves <er...@uolet.com> escreveu:
>
>> Hi Folks,
>>
>> I'd like to use a SQL statement in QueryRecords like this uses the
>> extract the last 10 chars of  field and compare it to today, similar to the
>> line below:
>>
>> SELECT substring( '/index', 10, -1) as expired FROM FLOWFILE WHERE
>> expired <= '${now():format("yyyy-MM-dd")}'
>>
>> This statement is not work and I can't find a list of QueryRecord
>> supported UDF or if (nd how) I can use RecordPath functions.
>>
>> Is there any documentation where I can see the available UDFs?
>>
>> Regards,
>>
>>
>

Re: What UDFs are supported by QueryRecord?

Posted by Mark Payne <ma...@hotmail.com>.
Eric,

You can use RecordPath with QueryRecord, via the RPATH, RPATH_STRING, RPATH_INT, etc. These are explained in the Processor's documentation. For example, see [1].

You can also use the Expression Language with QueryRecord. The Expression Language is evaluated before the SQL is parsed. So, for example, if you had an attribute named 'Field of Interest' you could actually use SQL like:

SELECT ${'Field of Interest'}
FROM FLOWFILE

Thanks
-Mark

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

On Oct 16, 2019, at 2:10 PM, Eric Chaves <er...@uolet.com>> wrote:

Ok, figured out a way to do it. I noticed that QueryRecord uses Apache Calcite so I tried some of Calcite functions until I got into this statement that seems to work:

SELECT index FROM FLOWFILE WHERE CAST( SUBSTRING(index FROM CHAR_LENGTH(index)-9) AS DATE) <= CURRENT_DATE

Anyhow, I would still like to know if (and how) I could use either NiFi expression or RecordPath functions in a QueryRecord statement.

Thanks in advance,

Em qua, 16 de out de 2019 às 14:30, Eric Chaves <er...@uolet.com>> escreveu:
Hi Folks,

I'd like to use a SQL statement in QueryRecords like this uses the extract the last 10 chars of  field and compare it to today, similar to the line below:

SELECT substring( '/index', 10, -1) as expired FROM FLOWFILE WHERE expired <= '${now():format("yyyy-MM-dd")}'

This statement is not work and I can't find a list of QueryRecord supported UDF or if (nd how) I can use RecordPath functions.

Is there any documentation where I can see the available UDFs?

Regards,

Re: What UDFs are supported by QueryRecord?

Posted by Eric Chaves <er...@uolet.com>.
Ok, figured out a way to do it. I noticed that QueryRecord uses Apache
Calcite so I tried some of Calcite functions until I got into this
statement that seems to work:

SELECT index FROM FLOWFILE WHERE CAST( SUBSTRING(index FROM
CHAR_LENGTH(index)-9) AS DATE) <= CURRENT_DATE

Anyhow, I would still like to know if (and how) I could use either NiFi
expression or RecordPath functions in a QueryRecord statement.

Thanks in advance,

Em qua, 16 de out de 2019 às 14:30, Eric Chaves <er...@uolet.com> escreveu:

> Hi Folks,
>
> I'd like to use a SQL statement in QueryRecords like this uses the extract
> the last 10 chars of  field and compare it to today, similar to the line
> below:
>
> SELECT substring( '/index', 10, -1) as expired FROM FLOWFILE WHERE expired
> <= '${now():format("yyyy-MM-dd")}'
>
> This statement is not work and I can't find a list of QueryRecord
> supported UDF or if (nd how) I can use RecordPath functions.
>
> Is there any documentation where I can see the available UDFs?
>
> Regards,
>
>