Naming convention in NiFi

Question

I have a flow GetFile->ConvertRecord->splittext->PutdatabaseRecord. My csv file I am sending in to the GetFile contains followings fields:

ID  TIME                      M00B01  M00B02  M00B03
1   2018-09-27 10:44:23.972   3242    35      335
2   2018-09-21 11:44:23.972   323     24      978

MY database table skeleton in MYSQL is as follows:

Create table test(ID INT,TIME DATETIME(3),MxB01 INT,MxB02 INT,MxB03 INT);

Note: I have replaced the name of headers as MxB00,MxB01 etc..

I have error in my convertRecord processor as I am reading as CSVReader And writting as CSVSetWritter. I am attaching the config of both for your reference.

The problem is It reads the CSV file but because of change in header names it gives all other fields as blank(i changed the header names because I have to write the headers name as MxB00 in order to match the headers defined in MySQL table). I get the value of ID and Time because I have not changed the Header name of those fields in the CSVWritter and MySQL table definition. so those values I get but for other values I get blank Because it is getting confused due to name change.

How can I solve this problem? Any help is much appreciated.Thank you!

notNull notNull · Accepted Answer · 2018-11-03T02:54:59

As you want to create custom header for the output CSV file then configure CSV reader controller service as shown below.

Configs:

As we are using Schema Access Strategy as Schema Text and giving the schema as

{
"type": "record",
"name": "SQLSchema",
"fields" : [
{"name": "ID", "type": ["null","int"]},
{"name": "TIME", "type": ["null","string"]},
{"name": "MxB01", "type": ["null","int"]},
{"name": "MxB02", "type": ["null","int"]},
{"name": "MxB03", "type": ["null","int"]}
]
}

And we are treating First line of csv data as Header and ignoring CSV header column names, so the output flowfile will have the schema that we defined above.

CsvWriter Configs:

As we are inheriting the schema write strategy so output flowfile will have same header what we have specified in the reader.

In addition i'm not sure why you are using SplitText processor after ConvertRecord as PutDatabaseRecord processor designed to work with chunks of records at a time.

Even you can configure PutDatabaseRecord processor with the CsvReader controller service mentioned above then your flow will be:

Flow:

GetFile -> PutDatabaseRecord

Note:

As i haven't used Avro logical types for the timestamp field if you are using logical types then change the controller service configs accordingly.

Naming convention in NiFi

2 Answers