question Re: Nifi load data from CSV to mySQL in Support Questions

Nifi load data from CSV to mySQL

i_am_dba — Thu, 20 Oct 2022 08:45:55 GMT

Hi there,

I am trying to load simple CSV to mySQL but i have some difficulties with NULL and empty string values.
This is my table for example:

create table test1 (

id int not null,

name varchar(255) not null,

age int null);

and my data looks like this in the mySQL:

*the blank values are not NULL they were inserted like:

insert into test(id,name) values (2,'');

Now when I export this into CSV and try to load this CSV back to mySQL it fails to understand that those empty values are just blank and not NULLs.
This is my NiFi configuration:

I followed the template from another question I found here and removed some parts that I find like I don't need (or maybe I need??) = removed the AvroSchemaRegistry and instead used Use String Fields From Header.

I tried many things from exporting values with double quotes,single quotes but still didn't manage to figure out how to load empty/blank/'' values instead of NULLs.

Please help 🙂

Re: Nifi load data from CSV to mySQL

i_am_dba — Fri, 21 Oct 2022 20:13:59 GMT

Anyone?

Re: Nifi load data from CSV to mySQL

steven-matison — Mon, 24 Oct 2022 13:18:12 GMT

@i_am_dba This is a very difficult one to explain. I think the issue is the string schema, or removing the avro schema you mentioned. My first suggestion would be to try to specific the schema which should help getting the data into the right formats. An alternate solution is to try and do that manually by replacetext/regex,etc but that is not the ideal solution. That said, another higher level suggesting is to update the upstream datasource to permanently solve the instability from '' (blank string), 'null' (string), or actual NULL (not a blank, '', or string at all).