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 <[email protected]> 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, <[email protected]> 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> > > > > > > > > > > > >
