ExecuteSQL failed to process due to illegal character

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

ExecuteSQL failed to process due to illegal character

Josefz

Hi guys, we are using the Impala JDBC driver in the NiFi ExecuteSQL processor and we try to execute the following simple SQL query: “show range partitions test_sql_drop;”

 

As result with an external JDBC client I’m getting the following output with one column (first line is the header)

RANGE (flowenddate)

VALUES < 1535102117

1535102117 <= VALUES < 1535188517

1535188517 <= VALUES < 1535274917

1535274917 <= VALUES < 1535361317

1535361317 <= VALUES < 1535447717

1535447717 <= VALUES < 1535534117

1535534117 <= VALUES < 1535620517

1535620517 <= VALUES < 1535706917

1535706917 <= VALUES < 1535793317

1535793317 <= VALUES < 1535879717

1535879717 <= VALUES < 1535966117

1535966117 <= VALUES < 1536052517

1536052517 <= VALUES < 1536138917

1536138917 <= VALUES < 1536225317

1536225317 <= VALUES < 1536311717

VALUES >= 1536311717

 

However, in NiFi I’m getting the ERROR message “failed to process session due to illegal character in RANGE (flowenddate);” I assume this is because of those two signs “< or =” in the result. Anybody an idea how to fix this?

 

Cheers Josef

 


smime.p7s (7K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: ExecuteSQL failed to process due to illegal character

Matt Burgess-2
ExecuteSQL outputs Avro with an embedded schema, it is the Avro schema
that doesn't allow parentheses in the field names. You can avoid the
error by setting Normalize names to true as you found, and also you
could alias the field names in the SQL itself, such as:

SELECT RANGE(flowenddate) AS flowEndDateRange FROM myTable

Then the outgoing Avro file will have the field name as
flowEndDateRange and won't need to transform any illegal characters.

Regards,
Matt

On Fri, Sep 7, 2018 at 5:21 AM <[hidden email]> wrote:

>
> I just found a solution, I’ve changed the “Normalize Table/Column Names” to “true” instead of the default “false”. Now the output is below. My guessing was wrong, the problem was related to the Column Name and not to the content of the column. Seems that NiFi ExecuteSQL processor has a problem with the “( and )” signs in a column name… is this expected behavior or a bug?
>
>
>
> Flowfile Output: Avro formatted
>
> [ {
>
>   "RANGE__flowenddate_" : "VALUES < 1535102117"
>
> }, {
>
>   "RANGE__flowenddate_" : "1535102117 <= VALUES < 1535188517"
>
> }, {
>
>   "RANGE__flowenddate_" : "1535188517 <= VALUES < 1535274917"
>
> }, {
>
>   "RANGE__flowenddate_" : "1535274917 <= VALUES < 1535361317"
>
> }, {
>
>   "RANGE__flowenddate_" : "1535361317 <= VALUES < 1535447717"
>
> }, {
>
>   "RANGE__flowenddate_" : "1535447717 <= VALUES < 1535534117"
>
> }, {
>
>   "RANGE__flowenddate_" : "1535534117 <= VALUES < 1535620517"
>
> }, {
>
>   "RANGE__flowenddate_" : "1535620517 <= VALUES < 1535706917"
>
> }, {
>
>   "RANGE__flowenddate_" : "1535706917 <= VALUES < 1535793317"
>
> }, {
>
>   "RANGE__flowenddate_" : "1535793317 <= VALUES < 1535879717"
>
> }, {
>
>   "RANGE__flowenddate_" : "1535879717 <= VALUES < 1535966117"
>
> }, {
>
>   "RANGE__flowenddate_" : "1535966117 <= VALUES < 1536052517"
>
> }, {
>
>   "RANGE__flowenddate_" : "1536052517 <= VALUES < 1536138917"
>
> }, {
>
>   "RANGE__flowenddate_" : "1536138917 <= VALUES < 1536225317"
>
> }, {
>
>   "RANGE__flowenddate_" : "1536225317 <= VALUES < 1536311717"
>
> }, {
>
>   "RANGE__flowenddate_" : "VALUES >= 1536311717"
>
> } ]
>
>
>
>
>
> On 2018/09/07 09:12:25, <[hidden email]> wrote:
>
> > Hi guys, we are using the Impala JDBC driver in the NiFi ExecuteSQL processor and we try to execute the following simple SQL query: “show range partitions test_sql_drop;”>
>
> >
>
> >
>
> >
>
> > As result with an external JDBC client I’m getting the following output with one column (first line is the header)>
>
> >
>
> > RANGE (flowenddate)>
>
> >
>
> > VALUES < 1535102117>
>
> >
>
> > 1535102117 <= VALUES < 1535188517>
>
> >
>
> > 1535188517 <= VALUES < 1535274917>
>
> >
>
> > 1535274917 <= VALUES < 1535361317>
>
> >
>
> > 1535361317 <= VALUES < 1535447717>
>
> >
>
> > 1535447717 <= VALUES < 1535534117>
>
> >
>
> > 1535534117 <= VALUES < 1535620517>
>
> >
>
> > 1535620517 <= VALUES < 1535706917>
>
> >
>
> > 1535706917 <= VALUES < 1535793317>
>
> >
>
> > 1535793317 <= VALUES < 1535879717>
>
> >
>
> > 1535879717 <= VALUES < 1535966117>
>
> >
>
> > 1535966117 <= VALUES < 1536052517>
>
> >
>
> > 1536052517 <= VALUES < 1536138917>
>
> >
>
> > 1536138917 <= VALUES < 1536225317>
>
> >
>
> > 1536225317 <= VALUES < 1536311717>
>
> >
>
> > VALUES >= 1536311717>
>
> >
>
> >
>
> >
>
> > However, in NiFi I’m getting the ERROR message “failed to process session due to illegal character in RANGE (flowenddate);” I assume this is because of those two signs “< or =” in the result. Anybody an idea how to fix this?>
>
> >
>
> >
>
> >
>
> > Cheers Josef>
>
> >
>
> >
>
> >
>
> >