question Re: Error when copying CSV files from Windows directory into SQL Server DB by using Apache NiFi in Support Questions

Error when copying CSV files from Windows directory into SQL Server DB by using Apache NiFi

BerniHacker — Mon, 15 Feb 2021 08:37:22 GMT

I am trying to copy CSV files from my local directory into a SQL Server database running in my local machine by using Apache NiFi.

I am new to the tool and I have been spending few days googling and building my flow. I managed to connect to source and destination but still I am not able to populate the database since I get the following error: "None of the fields in the record map to the columns defined by the tablename table."

I have been struggling with this for a while and I have not been able to find a solution in the Web.

Any hint would be highly appreciated.

Here are further details.

I have built a simple flow using GetFile and PutDatabaseRecord processors

My input is a simple table with 8 columns

My configurations for GetCSV process are here (I have added the input directory and left the rest as default)

The configuration for PutDatabaseRecord process is here (I have referred to the CSVReader and DBCPConnectionPool controller services, used the MS SQL 2012+ database type (I have 2019 version), configured INSERT statement type, inserted the schema and correct table name and left everything else as default)

The CSVReader configuration looks as shown here (Schema Access Strategy = Use String Fields From Header; CSV Format = Microsoft Excel)

And this is the configuration of the DBCPConnectionPool (I have added the correct URL, DB driver class name, driver location, DB user and password)

Finally, this is a snapshot of the description of the table I have created in the database to host the content

Many thanks in advance!

Re: Error when copying CSV files from Windows directory into SQL Server DB by using Apache NiFi

justenji — Tue, 16 Feb 2021 06:47:29 GMT

@BerniHacker

As far as I see the cause could be different column names and in the PutDatabaseRecord the option "Unmatched Column Behavior" is set to "Fail on Unmatched Columns".

I suppose the options concerning "Unmatched Field Behavior" or "Unmatched Column Behavior" in connection with "Translate Field Names" causing your problem.

Perhaps you like to post your schema-definition?
But at the moment I can't give you a concreter answer.

Re: Error when copying CSV files from Windows directory into SQL Server DB by using Apache NiFi

justenji — Tue, 16 Feb 2021 07:11:02 GMT

@BerniHacker
Sorry, my reply was not good.
Did not see that you are working with "Use String Fields From Header" and not a separate schema.
I will try to get it work on my local installation.
If you found a solution by so kind to let us know. Thanks.

Re: Error when copying CSV files from Windows directory into SQL Server DB by using Apache NiFi

BerniHacker — Tue, 16 Feb 2021 08:47:17 GMT

@justenjiThanks a lot for offering your help!

I am really stuck with this. I tried many different things (for example, table name = dbo.alpha, CSV Format = Customer Format, ...), always with same result.

Now I changed Unmatched Column Behaviour (Unmatched Column Behaviour = Ignore Unmatched Columns). Again same result.

I also tried with xlsx files by using a ConvertExcelToCSVProcessor in the middle. Same result.

Re: Error when copying CSV files from Windows directory into SQL Server DB by using Apache NiFi

justenji — Tue, 16 Feb 2021 17:13:19 GMT

@BerniHacker

The whole day I couldn't achive to work on this further. Now I found a solution but at the moment I can't document this detailed because "work is calling".

In small words:
GetFile -> UpdateAttribute (to set NiFi-attribute schema.name) -> PutDatabaseRecord (with CSV-Record Reader)
BUT the reader has to use a schema which is defined in the controller services as AvroSchemaRegistry and there has to exist a avro schema for the structure.

Maybe this will help you further.

Have a look here https://community.cloudera.com/t5/Support-Questions/Loading-a-simple-CSV-file-using-nifi-PutDatabaseRecord-and/td-p/222678

Tomorrow I will describe my solution detailed! Sorry...

Re: Error when copying CSV files from Windows directory into SQL Server DB by using Apache NiFi

justenji — Tue, 16 Feb 2021 17:40:52 GMT

@BerniHacker

Come on work can wait...

Here my description with NiFi 1.12.1. on a windows standalone installation.

This is my test csv.

id;title;first;last 1;miss;marlene;shaw 2;ms;letitia;jordan 3;mr;todd;graham 4;mr;seth;martinez 5;mr;guy;mckinney 6;ms;anna;smith 7;mr;johnny;johnson 8;mrs;robin;white 9;miss;allison;williams 10;mrs;erika;king

This is my flow:
Flow

This is my sql table.
SQL table

This is the configuration of the csv-reader.
Note the green bordered information!
Controller Service - dsv reader

The schema has to be defined in a controller service of the type "AvroSchemaRegistry".
AvroSchemaRegistry

With the UpdateAttribute you set the NiFi-attribute schema.name to the concrete schema you use.
UpdateAttribute schema.name

And last but not least configure the PutDatabaseRecord.
PutDatabaseRecord

Here is the result in my sql table.

SQL Result

Hope this helps and works for you. Bye!

Re: Error when copying CSV files from Windows directory into SQL Server DB by using Apache NiFi

BerniHacker — Wed, 17 Feb 2021 13:35:34 GMT

@justenji

Thanks a lot! The suggestion you proposed works great!

However, the source of the problem was not a lack of schema definition. In fact, I managed also to have a working flow with the option Schema Access Strategy = Use String Fields From Header in the CSVReader processor.

The warning "None of the fields in the record map to the columns defined by the tablename table." is also obtained when the processor is not able to find the table and this can happen also when the table name is correctly configured in PutDatabaseRecord but there is some issue with user access rights (which ended up to be the actual cause of my error ...).