CODEWITHSUNDEEP
mysqlapacheapache-nifi
Shrads
Shrads

Reputation: 883

QueryDatabaseTable error on AGGREGATE SQL functions

I want to perform average on few fields in the database table usingQueryDatabaseRecord processor. My query works fine in MySQL as follows:

select TimeOfDay,
    AVG(BMU_Debug_Pack_Blk_Volt_Delta), 
    AVG(BMU_Debug_Pack_CTemp_Min) 
from tt8;

All my fields are of "double" type" except TimeOfDay which is "varchar (10)". when I run Aggregate functions like AVG/Min etc on fields it gives me following error:

org.apache.nifi.processor.exception.ProcessException: 
Error during database query or conversion of records to Avro

What is the correct way to write SQL query that allows me to perform aggregate function. Thank you! enter image description here

Upvotes: 2

Views: 731

Answers (1)

notNull
notNull

Reputation: 31520

Alias the aggregated columns in the custom query and try to execute again in NiFi, as processor outputs the data in avro format so we need to alias the aggregated columns to get the avro schema.

 select TimeOfDay,
        AVG(BMU_Debug_Pack_Blk_Volt_Delta) avg_BMU_Debug_Pack_Blk_Volt_Delta, 
        AVG(BMU_Debug_Pack_CTemp_Min) avg_BMU_Debug_Pack_CTemp_Min
 from tt8;

In Addition as you mentioned you are able to run the above query on mysql side without adding group by clause so, If doesn't work then add all the non aggregated columns into group by clause and execute the query again

select TimeOfDay,
            AVG(BMU_Debug_Pack_Blk_Volt_Delta) avg_BMU_Debug_Pack_Blk_Volt_Delta, 
            AVG(BMU_Debug_Pack_CTemp_Min) avg_BMU_Debug_Pack_CTemp_Min
        from tt8
group by TimeOfDay;

Upvotes: 2

Related Questions