CODEWITHSUNDEEP
mysqlapacheapache-nifi
Shrads
Shrads

Reputation: 883

QueryRecord processor to perform aggregate SQL function in NiFi

I have the followimg flow :

QueryDatabaseTable ->QueryRecord -> UpdateAttribute->MergeContent->PutelasticsearchHttp

The idea is to fetch the records from database and perform aggregate functions on the fields. In my database Table i have following 4 fields :

DeptId  DepartmentName  Address  ExperienNo

1    DS    San Jose      4

2    GT    San Fran      6

3    At    Oakland       8

4    BMS   detroit       3

5    RT    Haawai        9

and I have set the controller services for QueryREcord as Avroreader and CSVSetWritter with following as schema:

{
"type": "record",
"name": "SQLSchema",
"fields" : [
{"name": "DeptId", "type": "int"},
{"name": "DepartmentName", "type": "string"},
{"name": "Address", "type": "string"},
{"name": "ExperienceNo", "type": "int"},
{"name": "Total_Experience", "type": "int"}
]
}

I want to perform the difference on two fields (DeptId-ExperienceNo)

My SQL query is as follow:

SELECT DeptId, DepartmentName,Address,ExperienceNo,
(DeptId - ExperienceNo) AS Total_Experience FROM flowfile

I get error as 'Total_Experience' cannot be null.

However the same query runs fine in MySQL. How can this be achieved, where I can perform SQL aggregate function on fields and alias that as a new dynamic column.

Thank you.

Any suggestion is much appreciated.

Upvotes: 3

Views: 3062

Answers (1)

notNull
notNull

Reputation: 31520

Your csv reader controller service is configured with

{"name": "Total_Experience", "type": "int"} field with out default value and in your input data you are not getting this field.

So processor is complaining Total_Experience cannot be null.

To fix this issue change the avro schema to include null value for Total_Experience field.

Avro Schema:

{
"type": "record",
"name": "SQLSchema",
"fields" : [
{"name": "DeptId", "type": "int"},
{"name": "DepartmentName", "type": "string"},
{"name": "Address", "type": "string"},
{"name": "ExperienceNo", "type": "int"},
{"name": "Total_Experience", "type": ["null","int"]}
]
}

CsvReader Controller service Configs:

enter image description here

Output:

DeptId,DepartmentName,Address,ExperienceNo,Total_Experience
1,DS,San Jose,4,-3
2,GT,San Fran,6,-4
3,At,Oakland,8,-5
4,BMS,detroit,3,1
5,RT,Haawai,9,-4

Correct way of doing this would be:

Configuring CSV Reader without Total_Experience field and Include Total_Experience in CsvSetWriter controller service as you are creating this field by using Query record processor.

Upvotes: 2

Related Questions