You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@nifi.apache.org by Dan S <ds...@gmail.com> on 2024/03/06 17:14:10 UTC
QueryRecord processor fails with use of CONTAINS_SUBSTR
I am trying to use the QueryRecord processor with a SQL statement similar
to:
SELECT mi FROM FLOWFILE WHERE CONTAIN_SUBSTR(somedetails, 'Fred')
which fails with the following error message in the logs:
Caused by org.apache.calcite.runtime.CalciteContextException: From column
31 to line 1, column 66: No match found for function signature
CONTAINS_SUBSTR(<CHARACHTER>, <CHARACHTER>)
In the Apache Calcite documentation
<https://calcite.apache.org/docs/reference.html>I see CONTAINS_SUBSTR
defined. Why cannot I use it in NIFI? Are there limitations of what
functions that can be used from Apache Calcite?
Re: QueryRecord processor fails with use of CONTAINS_SUBSTR
Posted by Dan S <ds...@gmail.com>.
Thanks Pierre! I did not realize that. BTW I was able to get my queries
working with LIKE.
On Wed, Mar 6, 2024 at 12:19 PM Pierre Villard <pi...@gmail.com>
wrote:
> Hi Dan,
>
> As you can see on the doc there is a 'b' next to CONTAINS_SUBSTR and the
> documentation explains its meaning:
>
> The ‘C’ (compatibility) column contains value:
> > ‘*’ for all libraries,
> > ‘b’ for Google BigQuery (‘fun=bigquery’ in the connect string),
> > ‘c’ for Apache Calcite (‘fun=calcite’ in the connect string),
> > ‘h’ for Apache Hive (‘fun=hive’ in the connect string),
> > ‘m’ for MySQL (‘fun=mysql’ in the connect string),
> > ‘q’ for Microsoft SQL Server (‘fun=mssql’ in the connect string),
> > ‘o’ for Oracle (‘fun=oracle’ in the connect string),
> > ‘p’ for PostgreSQL (‘fun=postgresql’ in the connect string),
> > ’s’ for Apache Spark (‘fun=spark’ in the connect string).
>
>
> I believe we only support the ones for Apache Calcite. This is probably
> something we could/should improve.
>
> HTH,
> Pierre
>
>
> Le mer. 6 mars 2024 à 18:14, Dan S <ds...@gmail.com> a écrit :
>
> > I am trying to use the QueryRecord processor with a SQL statement similar
> > to:
> >
> > SELECT mi FROM FLOWFILE WHERE CONTAIN_SUBSTR(somedetails, 'Fred')
> >
> > which fails with the following error message in the logs:
> > Caused by org.apache.calcite.runtime.CalciteContextException: From column
> > 31 to line 1, column 66: No match found for function signature
> > CONTAINS_SUBSTR(<CHARACHTER>, <CHARACHTER>)
> >
> > In the Apache Calcite documentation
> > <https://calcite.apache.org/docs/reference.html>I see CONTAINS_SUBSTR
> > defined. Why cannot I use it in NIFI? Are there limitations of what
> > functions that can be used from Apache Calcite?
> >
>
Re: QueryRecord processor fails with use of CONTAINS_SUBSTR
Posted by Pierre Villard <pi...@gmail.com>.
Hi Dan,
As you can see on the doc there is a 'b' next to CONTAINS_SUBSTR and the
documentation explains its meaning:
The ‘C’ (compatibility) column contains value:
> ‘*’ for all libraries,
> ‘b’ for Google BigQuery (‘fun=bigquery’ in the connect string),
> ‘c’ for Apache Calcite (‘fun=calcite’ in the connect string),
> ‘h’ for Apache Hive (‘fun=hive’ in the connect string),
> ‘m’ for MySQL (‘fun=mysql’ in the connect string),
> ‘q’ for Microsoft SQL Server (‘fun=mssql’ in the connect string),
> ‘o’ for Oracle (‘fun=oracle’ in the connect string),
> ‘p’ for PostgreSQL (‘fun=postgresql’ in the connect string),
> ’s’ for Apache Spark (‘fun=spark’ in the connect string).
I believe we only support the ones for Apache Calcite. This is probably
something we could/should improve.
HTH,
Pierre
Le mer. 6 mars 2024 à 18:14, Dan S <ds...@gmail.com> a écrit :
> I am trying to use the QueryRecord processor with a SQL statement similar
> to:
>
> SELECT mi FROM FLOWFILE WHERE CONTAIN_SUBSTR(somedetails, 'Fred')
>
> which fails with the following error message in the logs:
> Caused by org.apache.calcite.runtime.CalciteContextException: From column
> 31 to line 1, column 66: No match found for function signature
> CONTAINS_SUBSTR(<CHARACHTER>, <CHARACHTER>)
>
> In the Apache Calcite documentation
> <https://calcite.apache.org/docs/reference.html>I see CONTAINS_SUBSTR
> defined. Why cannot I use it in NIFI? Are there limitations of what
> functions that can be used from Apache Calcite?
>