Bug in queryRecord when caching is not activated

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Bug in queryRecord when caching is not activated

Thierry Hanot

Hello

I’m trying to use a query record to do on the fly aggregation. I’m facing an issue when I ‘m deactivating the cache ( As I need to be able to use expression langage to generate the sql ).. When cache is disabled the execution failed :

QueryRecord[id=a3709a0c-015d-1000-8050-ecd623d10102] Unable to query StandardFlowFileRecord[uuid=8ba0d4c1-6fd0-4d76-9cb8-cf956cfe53ac,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1501680615481-4, container=default, section=4], offset=928895, length=15],offset=0,name=183093504803784,size=15] due to java.lang.RuntimeException: parse failed: Encountered "as \"" at line 1, column 12.).

 

To demonstrate , please find a template configure with 2 queryRecord doing exactly the same request. One have cache active , the other not. The one with caching work fine , the one without caching fail. The request is very simple : select '1' as "a",'2' as "b" from FLOWFILE

 

I have not try enough different topology of request to highlight what part is problematic , but I ‘m currently suspecting something related to double quote and literal.

 

Is there something wrong in my implementation ? Is it related to NiFi-4051 and no bug to open ?

 

Thanks in advance for your help.

 

 

Thierry Hanot

InfoVista | Chief Architect

cid:image001.jpg@01D2B879.7F499CD0

23 avenue Carnot, 91300 Massy, France

[hidden email] | Tel: +33 1 64 86 79 57 | Mob: +33 6 70 10 40 44

www.infovista.com | www.ipanematech.com | Blog | LinkedIn | Twitter | YouTube

 


BugQueryRecord.xml (30K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Bug in queryRecord when caching is not activated

Mark Payne
Thierry,

I believe the SQL you should be using is:

SELECT '1' AS a, '2' AS b
FROM FLOWFILE

I.e., I don't think you should be using double-quotes there

Thanks
-Mark

On Aug 2, 2017, at 11:14 AM, Thierry Hanot <[hidden email]> wrote:

Hello 
I’m trying to use a query record to do on the fly aggregation. I’m facing an issue when I ‘m deactivating the cache ( As I need to be able to use expression langage to generate the sql ).. When cache is disabled the execution failed :
QueryRecord[id=a3709a0c-015d-1000-8050-ecd623d10102] Unable to query StandardFlowFileRecord[uuid=8ba0d4c1-6fd0-4d76-9cb8-cf956cfe53ac,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1501680615481-4, container=default, section=4], offset=928895, length=15],offset=0,name=183093504803784,size=15] due to java.lang.RuntimeException: parse failed: Encountered "as \"" at line 1, column 12.).
 
To demonstrate , please find a template configure with 2 queryRecord doing exactly the same request. One have cache active , the other not. The one with caching work fine , the one without caching fail. The request is very simple : select '1' as "a",'2' as "b" from FLOWFILE
 
I have not try enough different topology of request to highlight what part is problematic , but I ‘m currently suspecting something related to double quote and literal.
 
Is there something wrong in my implementation ? Is it related to NiFi-4051 and no bug to open ?
 
Thanks in advance for your help.
 
 

Thierry Hanot

InfoVista | Chief Architect

23 avenue Carnot, 91300 Massy, France

[hidden email] | Tel: +33 1 64 86 79 57 | Mob: +33 6 70 10 40 44

www.infovista.com | www.ipanematech.com | Blog | LinkedIn | Twitter | YouTube

 
<BugQueryRecord.xml>

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Bug in queryRecord when caching is not activated

Thierry Hanot
Hello Mark
Yes in the example I can remove the double quote, but in real life it s not possible because some columns have name which are reserved keyword and I cannot change the source or destination of the data.
I can transform and rename before and after but it generate unnecessary additional load.

May be there is another way but I don t find it .

Thanks a lot .

Thierry

Sent from my iPhone

On 2 Aug 2017, at 17:19, Mark Payne <[hidden email]> wrote:

Thierry,

I believe the SQL you should be using is:

SELECT '1' AS a, '2' AS b
FROM FLOWFILE

I.e., I don't think you should be using double-quotes there

Thanks
-Mark

On Aug 2, 2017, at 11:14 AM, Thierry Hanot <[hidden email]> wrote:

Hello 
I’m trying to use a query record to do on the fly aggregation. I’m facing an issue when I ‘m deactivating the cache ( As I need to be able to use expression langage to generate the sql ).. When cache is disabled the execution failed :
QueryRecord[id=a3709a0c-015d-1000-8050-ecd623d10102] Unable to query StandardFlowFileRecord[uuid=8ba0d4c1-6fd0-4d76-9cb8-cf956cfe53ac,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1501680615481-4, container=default, section=4], offset=928895, length=15],offset=0,name=183093504803784,size=15] due to java.lang.RuntimeException: parse failed: Encountered "as \"" at line 1, column 12.).
 
To demonstrate , please find a template configure with 2 queryRecord doing exactly the same request. One have cache active , the other not. The one with caching work fine , the one without caching fail. The request is very simple : select '1' as "a",'2' as "b" from FLOWFILE
 
I have not try enough different topology of request to highlight what part is problematic , but I ‘m currently suspecting something related to double quote and literal.
 
Is there something wrong in my implementation ? Is it related to NiFi-4051 and no bug to open ?
 
Thanks in advance for your help.
 
 

Thierry Hanot

InfoVista | Chief Architect

23 avenue Carnot, 91300 Massy, France

[hidden email] | Tel: +33 1 64 86 79 57 | Mob: +33 6 70 10 40 44

www.infovista.com | www.ipanematech.com | Blog | LinkedIn | Twitter | YouTube

 
<BugQueryRecord.xml>

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Bug in queryRecord when caching is not activated

Mark Payne
Thierry,

Sorry for the delay in responding.

I understand. I dropped your template into the latest build on master, and the good news
is that the flow worked perfectly.

I believe the issue that you are hitting is actually NIFI-4047, not NIFI-4051.We have already
addressed NIFI-4047 (but haven't released that yet).

So if you're interested in building master and running from that, the issue should be addressed.
Alternatively, you could make use of the Schema "aliases" feature. For example, if you want to do
something like:

SELECT COUNT(*) AS count
FROM FLOWFILE

You could instead use:

SELECT COUNT(*) AS totalCount
FROM FLOWFILE

and then in your schema have a field such as:

{ "name": "count", "type": "int", "aliases": [ "totalCount" ] }

That should cause the field to be written out with the name 'count' even though in your query it is named 'totalCount' because
the schema's alias would handle this mapping. Again, of course, in the latest version this should work as-is, by using double-quotes:

SELECT COUNT(*) AS "count"
FROM FLOWFILE

I hope this helps!

Thanks
-Mark



On Aug 2, 2017, at 11:46 AM, Thierry Hanot <[hidden email]> wrote:

Hello Mark
Yes in the example I can remove the double quote, but in real life it s not possible because some columns have name which are reserved keyword and I cannot change the source or destination of the data.
I can transform and rename before and after but it generate unnecessary additional load.

May be there is another way but I don t find it .

Thanks a lot .

Thierry

Sent from my iPhone

On 2 Aug 2017, at 17:19, Mark Payne <[hidden email]> wrote:

Thierry,

I believe the SQL you should be using is:

SELECT '1' AS a, '2' AS b
FROM FLOWFILE

I.e., I don't think you should be using double-quotes there

Thanks
-Mark

On Aug 2, 2017, at 11:14 AM, Thierry Hanot <[hidden email]> wrote:

Hello 
I’m trying to use a query record to do on the fly aggregation. I’m facing an issue when I ‘m deactivating the cache ( As I need to be able to use expression langage to generate the sql ).. When cache is disabled the execution failed :
QueryRecord[id=a3709a0c-015d-1000-8050-ecd623d10102] Unable to query StandardFlowFileRecord[uuid=8ba0d4c1-6fd0-4d76-9cb8-cf956cfe53ac,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1501680615481-4, container=default, section=4], offset=928895, length=15],offset=0,name=183093504803784,size=15] due to java.lang.RuntimeException: parse failed: Encountered "as \"" at line 1, column 12.).
 
To demonstrate , please find a template configure with 2 queryRecord doing exactly the same request. One have cache active , the other not. The one with caching work fine , the one without caching fail. The request is very simple : select '1' as "a",'2' as "b" from FLOWFILE
 
I have not try enough different topology of request to highlight what part is problematic , but I ‘m currently suspecting something related to double quote and literal.
 
Is there something wrong in my implementation ? Is it related to NiFi-4051 and no bug to open ?
 
Thanks in advance for your help.
 
 

Thierry Hanot

InfoVista | Chief Architect

23 avenue Carnot, 91300 Massy, France

[hidden email] | Tel: +33 1 64 86 79 57 | Mob: +33 6 70 10 40 44

www.infovista.com | www.ipanematech.com | Blog | LinkedIn | Twitter | YouTube

 
<BugQueryRecord.xml>


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: Bug in queryRecord when caching is not activated

Thierry Hanot

Thanks a lot Mark

I’ll try it right now J.

 

Regards

 

 

From: Mark Payne [mailto:[hidden email]]
Sent: 03 August 2017 22:54
To: [hidden email]
Subject: Re: Bug in queryRecord when caching is not activated

 

Thierry,

 

Sorry for the delay in responding.

 

I understand. I dropped your template into the latest build on master, and the good news

is that the flow worked perfectly.

 

I believe the issue that you are hitting is actually NIFI-4047, not NIFI-4051.We have already

addressed NIFI-4047 (but haven't released that yet).

 

So if you're interested in building master and running from that, the issue should be addressed.

Alternatively, you could make use of the Schema "aliases" feature. For example, if you want to do

something like:

 

SELECT COUNT(*) AS count

FROM FLOWFILE

 

You could instead use:

 

SELECT COUNT(*) AS totalCount

FROM FLOWFILE

 

and then in your schema have a field such as:

 

{ "name": "count", "type": "int", "aliases": [ "totalCount" ] }

 

That should cause the field to be written out with the name 'count' even though in your query it is named 'totalCount' because

the schema's alias would handle this mapping. Again, of course, in the latest version this should work as-is, by using double-quotes:

 

SELECT COUNT(*) AS "count"

FROM FLOWFILE

 

I hope this helps!

 

Thanks

-Mark

 

 

 

On Aug 2, 2017, at 11:46 AM, Thierry Hanot <[hidden email]> wrote:

 

Hello Mark

Yes in the example I can remove the double quote, but in real life it s not possible because some columns have name which are reserved keyword and I cannot change the source or destination of the data.

I can transform and rename before and after but it generate unnecessary additional load.

 

May be there is another way but I don t find it .

 

Thanks a lot .

 

Thierry


Sent from my iPhone


On 2 Aug 2017, at 17:19, Mark Payne <[hidden email]> wrote:

Thierry,

 

I believe the SQL you should be using is:

 

SELECT '1' AS a, '2' AS b

FROM FLOWFILE

 

I.e., I don't think you should be using double-quotes there

 

Thanks

-Mark

 

On Aug 2, 2017, at 11:14 AM, Thierry Hanot <[hidden email]> wrote:

 

Hello 

I’m trying to use a query record to do on the fly aggregation. I’m facing an issue when I ‘m deactivating the cache ( As I need to be able to use expression langage to generate the sql ).. When cache is disabled the execution failed :

QueryRecord[id=a3709a0c-015d-1000-8050-ecd623d10102] Unable to query StandardFlowFileRecord[uuid=8ba0d4c1-6fd0-4d76-9cb8-cf956cfe53ac,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1501680615481-4, container=default, section=4], offset=928895, length=15],offset=0,name=183093504803784,size=15] due to java.lang.RuntimeException: parse failed: Encountered "as \"" at line 1, column 12.).

 

To demonstrate , please find a template configure with 2 queryRecord doing exactly the same request. One have cache active , the other not. The one with caching work fine , the one without caching fail. The request is very simple : select '1' as "a",'2' as "b" from FLOWFILE

 

I have not try enough different topology of request to highlight what part is problematic , but I ‘m currently suspecting something related to double quote and literal.

 

Is there something wrong in my implementation ? Is it related to NiFi-4051 and no bug to open ?

 

Thanks in advance for your help.

 

 

<BugQueryRecord.xml>

 

 

Loading...