You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@nifi.apache.org by Shawn Weeks <sw...@weeksconsulting.us> on 2020/01/06 21:35:41 UTC

Validating CSV File

I’m trying to validate that a csv file has the number of fields defined in it’s Avro schema. Consider the following schema and CSVs. I would like to be able to reject the invalid csv as missing fields.

{
   "type" : "record",
   "namespace" : "nifi",
   "name" : "nifi",
   "fields" : [
      { "name" : "c1" , "type" :  ["null", "string"] },
      { "name" : "c2" , "type" : ["null", "string"] },
      { "name" : "c3" , "type" : ["null", "string"] }
   ]
}

Good CSV
c1,c2,c3
hello,world,1
hello,world,
hello,,

Bad CSV
c1,c2,c3
hello,world,1
hello,world
hello


RE: Validating CSV File

Posted by "Oliveira, Emanuel" <Em...@fmr.com>.
Shawn you don’t need to say field has to be there..
You saying how many fields needed when you setting Schemas!

If you set ValidationCsv.Schemas to
myrule1,myrule2 --> then you csv need to have 2 fields

If you set ValidationCsv.Schemas to
myrule1,myrule2, .., myrule100 --> then you csv need to have 100 fields

Then each rule goes deeper specifying:

  *   Mandatory or optional (BUT THIS IS NO LONGER RELATED TO COMMAS/DELIMITERS)
  *   Anything(string), a number?, regex ? etc..)


Example of my template I shared on previous msg – of ValidationCsv.Schemas:
StrNotNullOrEmpty,StrNotNullOrEmpty,Optional(ParseInt)

So what you saying is:
CSV MUST have 2 commas (3 rules therefore 3 fields needed  - aka 2 commas or whatever delimiter char chosen).

Then you giving details per field – which are obvious to read/understand:
Field 1 --> well I want anything (Str), and must not be null/empty .,
Field 2 -->  again a mandatory not empty string/anything.
Field 3 --> Optional field + and it to be populated it must be an Integer.

So pretty obvious and easy to define.


Thanks//Regards,
Emanuel Oliveira


From: Shawn Weeks <sw...@weeksconsulting.us>
Sent: Thursday 9 January 2020 14:07
To: users@nifi.apache.org
Subject: Re: Validating CSV File

This email is from an external source - exercise caution regarding links and attachments.

Thank you for the example, What’s the correct way to say optional string but the field has to be there. I tried “Optional(StrNotNullOrEmpty)” which seemed to work but looks odd.

From: "Oliveira, Emanuel" <Em...@fmr.com>>
Reply-To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Date: Thursday, January 9, 2020 at 4:56 AM
To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Subject: RE: Validating CSV File

Hi Shawn,

Heres working example of ValidateCsv:
[cid:image012.jpg@01D5C6DB.5E922070]

Schema:
3 fields on CSV with first 2 mandatory and 3rd optional integer.


GenerateFlowFile - input
c1,c2,doesntmatterColName
hello,world,1
hello,world,
hello,,
hello
hello,world,notAnumber
,
,,

generates – invalid ff:
c1,c2,doesntmatterColName
hello,,                           <-- 2nd field cant be empty
hello                             <-- missing 2 fields
hello,world,notAnumber           <-- ok 1st and 2nd mandatory fields exists BUT 3rd field not an integer
,                                 <-- 2nd field cant be empty and missing 3rd field
,,                                <-- 1st and 2nd fields cant be null

generates – valid ff:
c1,c2,doesntmatterColName
hello,world,1                     <-- ok 1st and 2nd mandatory fields exists and 3rd optional is integer
hello,world,                      <-- ok 1st and 2nd mandatory fields exists and 3rd optional is empty


additionally – in some processors you can get more detail on errors by inspecting provenance of the invalid ff:
[cid:image013.jpg@01D5C6DB.5E922070]
[cid:image014.jpg@01D5C6DB.5E922070]
And then click view Details on the FF with EVENT ROUTE:
[cid:image004.png@01D5C6DB.5E65B940]

And then one can see error detail on the Details (some processors list multiple errors here, but ValidateCSV seems to only show the 1st error):
[cid:image015.jpg@01D5C6DB.5E922070][cid:image006.jpg@01D5C701.E3A90FA0]
Now lets fix the 1st error – and change:
hello,,                           <-- 2nd field cant be empty
into
hello,fixed,

so new input ff is:
c1,c2,doesntmatterColName
hello,world,1
hello,world,
hello,fixed,
hello
hello,world,notAnumber
,
,,

means if we run/inspect provenance event details, now it shall display error explanation for the:
hello                             <-- missing 2 fields

run the flow and lets see output..

invalid ff:
c1,c2,doesntmatterColName
hello
hello,world,notAnumber
,
,,


Valid ff:
c1,c2,doesntmatterColName
hello,world,1
hello,world,
hello,fixed,


Now instead of checking provenance event for detail, we can see the errors detail as an attribute on the invalid FF. And we can see the error detail for 1st error line (missing 2 fields):
hello


[cid:image007.jpg@01D5C701.E3A90FA0]
[cid:image018.jpg@01D5C6DB.5E922070]
Provenance event would show same validation.error.message.

ValidateCsv is in my opinion atm the easiest, robust ay to validate CSVs, theres lot of functions to force mandatory or not, types etc, listed on the documentation:
https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-standard-nar/1.5.0/org.apache.nifi.processors.standard.ValidateCsv/additionalDetails.html

so as long as you specify on ValidateCSV.Schema spec for 3 fields then 1st check is to check you have enough commas 😊 and then it checks if mandatory or not and then if correct type or not.

Hops this helps.



Thanks//Regards,
Emanuel Oliveira
Senior Oracle/Data Engineer | CTG | Galway
TEL ext: 353 – (0)91-74  4971 | int: 8-737 4971 |  who's who<http://fidelitycentral.fmr.com/ww/a639704> 

[Zoom Support Logo]
https://fmr.zoom.us/my/emanueoliveira
[9EBBBF8B]
Join Skype Meeting<https://meet.fmr.com/fmr/emanuel.oliveira/6PKNP8RG>
[8E06C359]
Chat with me using TEAMS<si...@fmr.com>


From: Shawn Weeks <sw...@weeksconsulting.us>>
Sent: Tuesday 7 January 2020 18:25
To: users@nifi.apache.org<ma...@nifi.apache.org>
Subject: Re: Validating CSV File

This email is from an external source - exercise caution regarding links and attachments.

I’ve been playing around with it but I’m not sure how to do the kind of validation I need. Consider this CSV. How would I validate this with ValidateCSV?

Good CSV
c1,c2,c3
hello,world,1
hello,world,
hello,,

Bad CSV
c1,c2,c3
hello,world,1
hello,world
hello

From: Emanuel Oliveira <em...@gmail.com>>
Reply-To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Date: Tuesday, January 7, 2020 at 12:21 PM
To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Subject: Re: Validating CSV File

ValidateCsv is the most robust (handles missing fields as your need), it doesn't use Avro Schemas, instead use inline sequence of functions to accomplish anything you want (nulls ok or not, types, regex etc).

In recent project while struggling for maximum data quality i tried all different processors and options and ValidateCsv is the clear winner for CSVs.

Emanuel O.

On Mon 6 Jan 2020, 23:36 Matt Burgess, <ma...@gmail.com>> wrote:
What about ValidateCsv, could that do what you want?
Sent from my iPhone

On Jan 6, 2020, at 6:10 PM, Shawn Weeks <sw...@weeksconsulting.us>> wrote:
I’m poking around to see if I can make the csv parsers fail on a schema mismatch like that. A stream command would be a good option though.

Thanks
Shawn

From: Mike Thomsen <mi...@gmail.com>>
Reply-To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Date: Monday, January 6, 2020 at 4:35 PM
To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Subject: Re: Validating CSV File

We have a lot of the same issues where I work, and our solution is to use ExecuteStreamCommand to pass CSVs off to Python scripts that will read stdin line by line to check to see if the export isn't screwed up. Some of our sources are good and we don't have to do that, but others are minefields in terms of the quality of the upstream data source, and that's the only way we've found where we can predictably handle such things.

On Mon, Jan 6, 2020 at 4:57 PM Shawn Weeks <sw...@weeksconsulting.us>> wrote:
That's the challenge, the values can be null but I want to know the fields are missing(aka not enough delimiters). I run into a common scenario where line feeds end up in the data making a short row. Currently the reader just ignores the fact that there aren't enough delimiters and makes them null.

On 1/6/20, 3:50 PM, "Matt Burgess" <ma...@apache.org>> wrote:

    Shawn,

    Your schema indicates that the fields are optional because of the
    "type" :  ["null", "string"] , so IIRC they won't be marked as invalid
    because they are treated as null (I'm not sure there's a difference in
    the code between missing and null fields).

    You can try "type": "string" in ValidateRecord to see if that fixes
    it, or there's a "StrNotNullOrEmpty" operator in ValidateCSV.

    Regards,
    Matt

    On Mon, Jan 6, 2020 at 4:35 PM Shawn Weeks <sw...@weeksconsulting.us>> wrote:
    >
    > I’m trying to validate that a csv file has the number of fields defined in it’s Avro schema. Consider the following schema and CSVs. I would like to be able to reject the invalid csv as missing fields.
    >
    >
    >
    > {
    >
    >    "type" : "record",
    >
    >    "namespace" : "nifi",
    >
    >    "name" : "nifi",
    >
    >    "fields" : [
    >
    >       { "name" : "c1" , "type" :  ["null", "string"] },
    >
    >       { "name" : "c2" , "type" : ["null", "string"] },
    >
    >       { "name" : "c3" , "type" : ["null", "string"] }
    >
    >    ]
    >
    > }
    >
    >
    >
    > Good CSV
    >
    > c1,c2,c3
    >
    > hello,world,1
    >
    > hello,world,
    >
    > hello,,
    >
    >
    >
    > Bad CSV
    >
    > c1,c2,c3
    >
    > hello,world,1
    >
    > hello,world
    >
    > hello
    >
    >

Re: Validating CSV File

Posted by Shawn Weeks <sw...@weeksconsulting.us>.
Thank you for the example, What’s the correct way to say optional string but the field has to be there. I tried “Optional(StrNotNullOrEmpty)” which seemed to work but looks odd.

From: "Oliveira, Emanuel" <Em...@fmr.com>
Reply-To: "users@nifi.apache.org" <us...@nifi.apache.org>
Date: Thursday, January 9, 2020 at 4:56 AM
To: "users@nifi.apache.org" <us...@nifi.apache.org>
Subject: RE: Validating CSV File

Hi Shawn,

Heres working example of ValidateCsv:
[cid:image012.jpg@01D5C6DB.5E922070]

Schema:
3 fields on CSV with first 2 mandatory and 3rd optional integer.


GenerateFlowFile - input
c1,c2,doesntmatterColName
hello,world,1
hello,world,
hello,,
hello
hello,world,notAnumber
,
,,

generates – invalid ff:
c1,c2,doesntmatterColName
hello,,                           <-- 2nd field cant be empty
hello                             <-- missing 2 fields
hello,world,notAnumber           <-- ok 1st and 2nd mandatory fields exists BUT 3rd field not an integer
,                                 <-- 2nd field cant be empty and missing 3rd field
,,                                <-- 1st and 2nd fields cant be null

generates – valid ff:
c1,c2,doesntmatterColName
hello,world,1                     <-- ok 1st and 2nd mandatory fields exists and 3rd optional is integer
hello,world,                      <-- ok 1st and 2nd mandatory fields exists and 3rd optional is empty


additionally – in some processors you can get more detail on errors by inspecting provenance of the invalid ff:
[cid:image013.jpg@01D5C6DB.5E922070]
[cid:image014.jpg@01D5C6DB.5E922070]
And then click view Details on the FF with EVENT ROUTE:
[cid:image004.png@01D5C6DB.5E65B940]

And then one can see error detail on the Details (some processors list multiple errors here, but ValidateCSV seems to only show the 1st error):
[cid:image015.jpg@01D5C6DB.5E922070][cid:image006.jpg@01D5C6C3.CB3D1D00]
Now lets fix the 1st error – and change:
hello,,                           <-- 2nd field cant be empty
into
hello,fixed,

so new input ff is:
c1,c2,doesntmatterColName
hello,world,1
hello,world,
hello,fixed,
hello
hello,world,notAnumber
,
,,

means if we run/inspect provenance event details, now it shall display error explanation for the:
hello                             <-- missing 2 fields

run the flow and lets see output..

invalid ff:
c1,c2,doesntmatterColName
hello
hello,world,notAnumber
,
,,


Valid ff:
c1,c2,doesntmatterColName
hello,world,1
hello,world,
hello,fixed,


Now instead of checking provenance event for detail, we can see the errors detail as an attribute on the invalid FF. And we can see the error detail for 1st error line (missing 2 fields):
hello


[cid:image007.jpg@01D5C6C3.CB3D1D00]
[cid:image018.jpg@01D5C6DB.5E922070]
Provenance event would show same validation.error.message.

ValidateCsv is in my opinion atm the easiest, robust ay to validate CSVs, theres lot of functions to force mandatory or not, types etc, listed on the documentation:
https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-standard-nar/1.5.0/org.apache.nifi.processors.standard.ValidateCsv/additionalDetails.html

so as long as you specify on ValidateCSV.Schema spec for 3 fields then 1st check is to check you have enough commas 😊 and then it checks if mandatory or not and then if correct type or not.

Hops this helps.



Thanks//Regards,
Emanuel Oliveira
Senior Oracle/Data Engineer | CTG | Galway
TEL ext: 353 – (0)91-74  4971 | int: 8-737 4971 |  who's who<http://fidelitycentral.fmr.com/ww/a639704> 

[Zoom Support Logo]
https://fmr.zoom.us/my/emanueoliveira
[9EBBBF8B]
Join Skype Meeting<https://meet.fmr.com/fmr/emanuel.oliveira/6PKNP8RG>
[8E06C359]
Chat with me using TEAMS<si...@fmr.com>


From: Shawn Weeks <sw...@weeksconsulting.us>
Sent: Tuesday 7 January 2020 18:25
To: users@nifi.apache.org
Subject: Re: Validating CSV File

This email is from an external source - exercise caution regarding links and attachments.

I’ve been playing around with it but I’m not sure how to do the kind of validation I need. Consider this CSV. How would I validate this with ValidateCSV?

Good CSV
c1,c2,c3
hello,world,1
hello,world,
hello,,

Bad CSV
c1,c2,c3
hello,world,1
hello,world
hello

From: Emanuel Oliveira <em...@gmail.com>>
Reply-To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Date: Tuesday, January 7, 2020 at 12:21 PM
To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Subject: Re: Validating CSV File

ValidateCsv is the most robust (handles missing fields as your need), it doesn't use Avro Schemas, instead use inline sequence of functions to accomplish anything you want (nulls ok or not, types, regex etc).

In recent project while struggling for maximum data quality i tried all different processors and options and ValidateCsv is the clear winner for CSVs.

Emanuel O.

On Mon 6 Jan 2020, 23:36 Matt Burgess, <ma...@gmail.com>> wrote:
What about ValidateCsv, could that do what you want?
Sent from my iPhone

On Jan 6, 2020, at 6:10 PM, Shawn Weeks <sw...@weeksconsulting.us>> wrote:
I’m poking around to see if I can make the csv parsers fail on a schema mismatch like that. A stream command would be a good option though.

Thanks
Shawn

From: Mike Thomsen <mi...@gmail.com>>
Reply-To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Date: Monday, January 6, 2020 at 4:35 PM
To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Subject: Re: Validating CSV File

We have a lot of the same issues where I work, and our solution is to use ExecuteStreamCommand to pass CSVs off to Python scripts that will read stdin line by line to check to see if the export isn't screwed up. Some of our sources are good and we don't have to do that, but others are minefields in terms of the quality of the upstream data source, and that's the only way we've found where we can predictably handle such things.

On Mon, Jan 6, 2020 at 4:57 PM Shawn Weeks <sw...@weeksconsulting.us>> wrote:
That's the challenge, the values can be null but I want to know the fields are missing(aka not enough delimiters). I run into a common scenario where line feeds end up in the data making a short row. Currently the reader just ignores the fact that there aren't enough delimiters and makes them null.

On 1/6/20, 3:50 PM, "Matt Burgess" <ma...@apache.org>> wrote:

    Shawn,

    Your schema indicates that the fields are optional because of the
    "type" :  ["null", "string"] , so IIRC they won't be marked as invalid
    because they are treated as null (I'm not sure there's a difference in
    the code between missing and null fields).

    You can try "type": "string" in ValidateRecord to see if that fixes
    it, or there's a "StrNotNullOrEmpty" operator in ValidateCSV.

    Regards,
    Matt

    On Mon, Jan 6, 2020 at 4:35 PM Shawn Weeks <sw...@weeksconsulting.us>> wrote:
    >
    > I’m trying to validate that a csv file has the number of fields defined in it’s Avro schema. Consider the following schema and CSVs. I would like to be able to reject the invalid csv as missing fields.
    >
    >
    >
    > {
    >
    >    "type" : "record",
    >
    >    "namespace" : "nifi",
    >
    >    "name" : "nifi",
    >
    >    "fields" : [
    >
    >       { "name" : "c1" , "type" :  ["null", "string"] },
    >
    >       { "name" : "c2" , "type" : ["null", "string"] },
    >
    >       { "name" : "c3" , "type" : ["null", "string"] }
    >
    >    ]
    >
    > }
    >
    >
    >
    > Good CSV
    >
    > c1,c2,c3
    >
    > hello,world,1
    >
    > hello,world,
    >
    > hello,,
    >
    >
    >
    > Bad CSV
    >
    > c1,c2,c3
    >
    > hello,world,1
    >
    > hello,world
    >
    > hello
    >
    >

RE: Validating CSV File

Posted by "Oliveira, Emanuel" <Em...@fmr.com>.
Hi Shawn,

Heres working example of ValidateCsv:
[cid:image012.jpg@01D5C6DB.5E922070]

Schema:
3 fields on CSV with first 2 mandatory and 3rd optional integer.


GenerateFlowFile - input
c1,c2,doesntmatterColName
hello,world,1
hello,world,
hello,,
hello
hello,world,notAnumber
,
,,

generates – invalid ff:
c1,c2,doesntmatterColName
hello,,                           <-- 2nd field cant be empty
hello                             <-- missing 2 fields
hello,world,notAnumber           <-- ok 1st and 2nd mandatory fields exists BUT 3rd field not an integer
,                                 <-- 2nd field cant be empty and missing 3rd field
,,                                <-- 1st and 2nd fields cant be null

generates – valid ff:
c1,c2,doesntmatterColName
hello,world,1                     <-- ok 1st and 2nd mandatory fields exists and 3rd optional is integer
hello,world,                      <-- ok 1st and 2nd mandatory fields exists and 3rd optional is empty


additionally – in some processors you can get more detail on errors by inspecting provenance of the invalid ff:
[cid:image013.jpg@01D5C6DB.5E922070]
[cid:image014.jpg@01D5C6DB.5E922070]
And then click view Details on the FF with EVENT ROUTE:
[cid:image004.png@01D5C6DB.5E65B940]

And then one can see error detail on the Details (some processors list multiple errors here, but ValidateCSV seems to only show the 1st error):
[cid:image015.jpg@01D5C6DB.5E922070] [cid:image016.jpg@01D5C6DB.5E922070]
Now lets fix the 1st error – and change:
hello,,                           <-- 2nd field cant be empty
into
hello,fixed,

so new input ff is:
c1,c2,doesntmatterColName
hello,world,1
hello,world,
hello,fixed,
hello
hello,world,notAnumber
,
,,

means if we run/inspect provenance event details, now it shall display error explanation for the:
hello                             <-- missing 2 fields

run the flow and lets see output..

invalid ff:
c1,c2,doesntmatterColName
hello
hello,world,notAnumber
,
,,


Valid ff:
c1,c2,doesntmatterColName
hello,world,1
hello,world,
hello,fixed,


Now instead of checking provenance event for detail, we can see the errors detail as an attribute on the invalid FF. And we can see the error detail for 1st error line (missing 2 fields):
hello


[cid:image017.jpg@01D5C6DB.5E922070]
[cid:image018.jpg@01D5C6DB.5E922070]
Provenance event would show same validation.error.message.

ValidateCsv is in my opinion atm the easiest, robust ay to validate CSVs, theres lot of functions to force mandatory or not, types etc, listed on the documentation:
https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-standard-nar/1.5.0/org.apache.nifi.processors.standard.ValidateCsv/additionalDetails.html

so as long as you specify on ValidateCSV.Schema spec for 3 fields then 1st check is to check you have enough commas 😊 and then it checks if mandatory or not and then if correct type or not.

Hops this helps.



Thanks//Regards,
Emanuel Oliveira
Senior Oracle/Data Engineer | CTG | Galway
TEL ext: 353 – (0)91-74  4971 | int: 8-737 4971 |  who's who<http://fidelitycentral.fmr.com/ww/a639704> 

[Zoom Support Logo]
https://fmr.zoom.us/my/emanueoliveira
[9EBBBF8B]
Join Skype Meeting<https://meet.fmr.com/fmr/emanuel.oliveira/6PKNP8RG>
[8E06C359]
Chat with me using TEAMS<si...@fmr.com>


From: Shawn Weeks <sw...@weeksconsulting.us>
Sent: Tuesday 7 January 2020 18:25
To: users@nifi.apache.org
Subject: Re: Validating CSV File

This email is from an external source - exercise caution regarding links and attachments.

I’ve been playing around with it but I’m not sure how to do the kind of validation I need. Consider this CSV. How would I validate this with ValidateCSV?

Good CSV
c1,c2,c3
hello,world,1
hello,world,
hello,,

Bad CSV
c1,c2,c3
hello,world,1
hello,world
hello

From: Emanuel Oliveira <em...@gmail.com>>
Reply-To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Date: Tuesday, January 7, 2020 at 12:21 PM
To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Subject: Re: Validating CSV File

ValidateCsv is the most robust (handles missing fields as your need), it doesn't use Avro Schemas, instead use inline sequence of functions to accomplish anything you want (nulls ok or not, types, regex etc).

In recent project while struggling for maximum data quality i tried all different processors and options and ValidateCsv is the clear winner for CSVs.

Emanuel O.

On Mon 6 Jan 2020, 23:36 Matt Burgess, <ma...@gmail.com>> wrote:
What about ValidateCsv, could that do what you want?
Sent from my iPhone

On Jan 6, 2020, at 6:10 PM, Shawn Weeks <sw...@weeksconsulting.us>> wrote:
I’m poking around to see if I can make the csv parsers fail on a schema mismatch like that. A stream command would be a good option though.

Thanks
Shawn

From: Mike Thomsen <mi...@gmail.com>>
Reply-To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Date: Monday, January 6, 2020 at 4:35 PM
To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Subject: Re: Validating CSV File

We have a lot of the same issues where I work, and our solution is to use ExecuteStreamCommand to pass CSVs off to Python scripts that will read stdin line by line to check to see if the export isn't screwed up. Some of our sources are good and we don't have to do that, but others are minefields in terms of the quality of the upstream data source, and that's the only way we've found where we can predictably handle such things.

On Mon, Jan 6, 2020 at 4:57 PM Shawn Weeks <sw...@weeksconsulting.us>> wrote:
That's the challenge, the values can be null but I want to know the fields are missing(aka not enough delimiters). I run into a common scenario where line feeds end up in the data making a short row. Currently the reader just ignores the fact that there aren't enough delimiters and makes them null.

On 1/6/20, 3:50 PM, "Matt Burgess" <ma...@apache.org>> wrote:

    Shawn,

    Your schema indicates that the fields are optional because of the
    "type" :  ["null", "string"] , so IIRC they won't be marked as invalid
    because they are treated as null (I'm not sure there's a difference in
    the code between missing and null fields).

    You can try "type": "string" in ValidateRecord to see if that fixes
    it, or there's a "StrNotNullOrEmpty" operator in ValidateCSV.

    Regards,
    Matt

    On Mon, Jan 6, 2020 at 4:35 PM Shawn Weeks <sw...@weeksconsulting.us>> wrote:
    >
    > I’m trying to validate that a csv file has the number of fields defined in it’s Avro schema. Consider the following schema and CSVs. I would like to be able to reject the invalid csv as missing fields.
    >
    >
    >
    > {
    >
    >    "type" : "record",
    >
    >    "namespace" : "nifi",
    >
    >    "name" : "nifi",
    >
    >    "fields" : [
    >
    >       { "name" : "c1" , "type" :  ["null", "string"] },
    >
    >       { "name" : "c2" , "type" : ["null", "string"] },
    >
    >       { "name" : "c3" , "type" : ["null", "string"] }
    >
    >    ]
    >
    > }
    >
    >
    >
    > Good CSV
    >
    > c1,c2,c3
    >
    > hello,world,1
    >
    > hello,world,
    >
    > hello,,
    >
    >
    >
    > Bad CSV
    >
    > c1,c2,c3
    >
    > hello,world,1
    >
    > hello,world
    >
    > hello
    >
    >

Re: Validating CSV File

Posted by Shawn Weeks <sw...@weeksconsulting.us>.
Just saw it. It looks pretty simple and you gave plenty of details on how to implement it. Thanks 

Sent from my iPhone

> On Jan 7, 2020, at 2:56 PM, Mark Payne <ma...@hotmail.com> wrote:
> 
> Shawn,
> 
> Please see the Jira that I referenced below. It explains how to determine whether or not a field exists in the record.
> 
> Thanks
> -Mark
> 
>> On Jan 7, 2020, at 3:38 PM, Shawn Weeks <sw...@weeksconsulting.us> wrote:
>> 
>> Seems like all you need is for the getValue method on the Record Interface to return something different if the Field doesn't exist instead of null. Maybe an enum? Then you could correctly determine that a nullable field didn't exist vs was just null in ValidateRecord.
>> 
>> On 1/7/20, 2:03 PM, "Shawn Weeks" <sw...@weeksconsulting.us> wrote:
>> 
>>   So I'm playing around with it some more and ValidateRecord is kinda working but it's finding stuff valid that isn't. Shouldn't some of those records have been rejected? I realized that by writing out to AVRO it was stubbing out the records but the actual input didn't have them.
>> 
>>   This is the input
>>   c1,c2,c3
>>   hello,world,1
>>   hello,world
>>   hello
>>   hello,,
>> 
>>   The schema
>>   {
>>      "type" : "record",
>>      "namespace" : "nifi",
>>      "name" : "nifi",
>>      "fields" : [
>>         { "name" : "c1" , "type" : ["null","string"] },
>>         { "name" : "c2" , "type" : ["null","string"] },
>>         { "name" : "c3" , "type" : ["null","string"] }
>>      ]
>>   }
>> 
>>   The output in JSON
>>   [ {
>>     "c1" : "hello",
>>     "c2" : "world",
>>     "c3" : "1"
>>   }, {
>>     "c1" : "hello",
>>     "c2" : "world"
>>   }, {
>>     "c1" : "hello"
>>   }, {
>>     "c1" : "hello",
>>     "c2" : null,
>>     "c3" : null
>>   } ]
>> 
>>   On 1/7/20, 1:14 PM, "Shawn Weeks" <sw...@weeksconsulting.us> wrote:
>> 
>>       I think to do that we'll need a way to distinguish between fields that were there and blank and fields that were never populated. Maybe an extension of the record api itself to distinguish between null and not there. Or allowing the CSV Reader to redefine null to something like empty string.
>> 
>>       I welcome any ideas.
>> 
>>       Thanks
>>       Shawn
>> 
>>       On 1/7/20, 1:07 PM, "Matt Burgess" <ma...@apache.org> wrote:
>> 
>>           I believe ValidateRecord would route the flow file to failure in that
>>           case. If you know the kind of data going in, then you might just be
>>           able to treat the failure relationship more like the invalid
>>           relationship. However I like the idea of doing it in ValidateRecord as
>>           Mark proposed in the Jira.
>> 
>>>           On Tue, Jan 7, 2020 at 2:02 PM Shawn Weeks <sw...@weeksconsulting.us> wrote:
>>> 
>>> I see where I think we could do this in the CSV Reader itself.  Jackson actually has an option to fail on missing fields and for csv commons we know how many fields we have and how many are in a record. Maybe just add a couple of true/false fields to fail on too few or too many fields like we do with PutDatabaseRecord. Not sure how to integrate that into ValidateRecord though as by that point it’s already parses.
>>> 
>>> 
>>> 
>>> Thanks
>>> 
>>> Shawn
>>> 
>>> 
>>> 
>>> From: Mark Payne <ma...@hotmail.com>
>>> Reply-To: "users@nifi.apache.org" <us...@nifi.apache.org>
>>> Date: Tuesday, January 7, 2020 at 12:52 PM
>>> To: "users@nifi.apache.org" <us...@nifi.apache.org>
>>> Subject: Re: Validating CSV File
>>> 
>>> 
>>> 
>>> I do agree that this is something that is not easily done with ValidateRecord (and probably cannot be done with ValidateRecord). But it's something that I think ValidateRecord *should* allow for. To that end, I've created a JIRA [1] to track this.
>>> 
>>> 
>>> 
>>> Thanks
>>> 
>>> -Mark
>>> 
>>> 
>>> 
>>> [1] https://issues.apache.org/jira/browse/NIFI-6986
>>> 
>>> 
>>> 
>>> 
>>> 
>>>> On Jan 7, 2020, at 1:24 PM, Shawn Weeks <sw...@weeksconsulting.us> wrote:
>>> 
>>> 
>>> 
>>> I’ve been playing around with it but I’m not sure how to do the kind of validation I need. Consider this CSV. How would I validate this with ValidateCSV?
>>> 
>>> 
>>> 
>>> Good CSV
>>> 
>>> c1,c2,c3
>>> 
>>> hello,world,1
>>> 
>>> hello,world,
>>> 
>>> hello,,
>>> 
>>> 
>>> 
>>> Bad CSV
>>> 
>>> c1,c2,c3
>>> 
>>> hello,world,1
>>> 
>>> hello,world
>>> 
>>> hello
>>> 
>>> 
>>> 
>>> From: Emanuel Oliveira <em...@gmail.com>
>>> Reply-To: "users@nifi.apache.org" <us...@nifi.apache.org>
>>> Date: Tuesday, January 7, 2020 at 12:21 PM
>>> To: "users@nifi.apache.org" <us...@nifi.apache.org>
>>> Subject: Re: Validating CSV File
>>> 
>>> 
>>> 
>>> ValidateCsv is the most robust (handles missing fields as your need), it doesn't use Avro Schemas, instead use inline sequence of functions to accomplish anything you want (nulls ok or not, types, regex etc).
>>> 
>>> 
>>> 
>>> In recent project while struggling for maximum data quality i tried all different processors and options and ValidateCsv is the clear winner for CSVs.
>>> 
>>> 
>>> 
>>> Emanuel O.
>>> 
>>> 
>>> 
>>>> On Mon 6 Jan 2020, 23:36 Matt Burgess, <ma...@gmail.com> wrote:
>>> 
>>> What about ValidateCsv, could that do what you want?
>>> 
>>> Sent from my iPhone
>>> 
>>> 
>>> 
>>> 
>>>> On Jan 6, 2020, at 6:10 PM, Shawn Weeks <sw...@weeksconsulting.us> wrote:
>>> 
>>> I’m poking around to see if I can make the csv parsers fail on a schema mismatch like that. A stream command would be a good option though.
>>> 
>>> 
>>> 
>>> Thanks
>>> 
>>> Shawn
>>> 
>>> 
>>> 
>>> From: Mike Thomsen <mi...@gmail.com>
>>> Reply-To: "users@nifi.apache.org" <us...@nifi.apache.org>
>>> Date: Monday, January 6, 2020 at 4:35 PM
>>> To: "users@nifi.apache.org" <us...@nifi.apache.org>
>>> Subject: Re: Validating CSV File
>>> 
>>> 
>>> 
>>> We have a lot of the same issues where I work, and our solution is to use ExecuteStreamCommand to pass CSVs off to Python scripts that will read stdin line by line to check to see if the export isn't screwed up. Some of our sources are good and we don't have to do that, but others are minefields in terms of the quality of the upstream data source, and that's the only way we've found where we can predictably handle such things.
>>> 
>>> 
>>> 
>>>> On Mon, Jan 6, 2020 at 4:57 PM Shawn Weeks <sw...@weeksconsulting.us> wrote:
>>> 
>>> That's the challenge, the values can be null but I want to know the fields are missing(aka not enough delimiters). I run into a common scenario where line feeds end up in the data making a short row. Currently the reader just ignores the fact that there aren't enough delimiters and makes them null.
>>> 
>>>> On 1/6/20, 3:50 PM, "Matt Burgess" <ma...@apache.org> wrote:
>>> 
>>>   Shawn,
>>> 
>>>   Your schema indicates that the fields are optional because of the
>>>   "type" :  ["null", "string"] , so IIRC they won't be marked as invalid
>>>   because they are treated as null (I'm not sure there's a difference in
>>>   the code between missing and null fields).
>>> 
>>>   You can try "type": "string" in ValidateRecord to see if that fixes
>>>   it, or there's a "StrNotNullOrEmpty" operator in ValidateCSV.
>>> 
>>>   Regards,
>>>   Matt
>>> 
>>>   On Mon, Jan 6, 2020 at 4:35 PM Shawn Weeks <sw...@weeksconsulting.us> wrote:
>>>> 
>>>> I’m trying to validate that a csv file has the number of fields defined in it’s Avro schema. Consider the following schema and CSVs. I would like to be able to reject the invalid csv as missing fields.
>>>> 
>>>> 
>>>> 
>>>> {
>>>> 
>>>>  "type" : "record",
>>>> 
>>>>  "namespace" : "nifi",
>>>> 
>>>>  "name" : "nifi",
>>>> 
>>>>  "fields" : [
>>>> 
>>>>     { "name" : "c1" , "type" :  ["null", "string"] },
>>>> 
>>>>     { "name" : "c2" , "type" : ["null", "string"] },
>>>> 
>>>>     { "name" : "c3" , "type" : ["null", "string"] }
>>>> 
>>>>  ]
>>>> 
>>>> }
>>>> 
>>>> 
>>>> 
>>>> Good CSV
>>>> 
>>>> c1,c2,c3
>>>> 
>>>> hello,world,1
>>>> 
>>>> hello,world,
>>>> 
>>>> hello,,
>>>> 
>>>> 
>>>> 
>>>> Bad CSV
>>>> 
>>>> c1,c2,c3
>>>> 
>>>> hello,world,1
>>>> 
>>>> hello,world
>>>> 
>>>> hello
>>>> 
>>>> 
>>> 
>>> 
>>> 
>> 
>> 
>> 
>> 
>> 
>> 
> 

Re: Validating CSV File

Posted by Mark Payne <ma...@hotmail.com>.
Shawn,

Please see the Jira that I referenced below. It explains how to determine whether or not a field exists in the record.

Thanks
-Mark

> On Jan 7, 2020, at 3:38 PM, Shawn Weeks <sw...@weeksconsulting.us> wrote:
> 
> Seems like all you need is for the getValue method on the Record Interface to return something different if the Field doesn't exist instead of null. Maybe an enum? Then you could correctly determine that a nullable field didn't exist vs was just null in ValidateRecord.
> 
> On 1/7/20, 2:03 PM, "Shawn Weeks" <sw...@weeksconsulting.us> wrote:
> 
>    So I'm playing around with it some more and ValidateRecord is kinda working but it's finding stuff valid that isn't. Shouldn't some of those records have been rejected? I realized that by writing out to AVRO it was stubbing out the records but the actual input didn't have them.
> 
>    This is the input
>    c1,c2,c3
>    hello,world,1
>    hello,world
>    hello
>    hello,,
> 
>    The schema
>    {
>       "type" : "record",
>       "namespace" : "nifi",
>       "name" : "nifi",
>       "fields" : [
>          { "name" : "c1" , "type" : ["null","string"] },
>          { "name" : "c2" , "type" : ["null","string"] },
>          { "name" : "c3" , "type" : ["null","string"] }
>       ]
>    }
> 
>    The output in JSON
>    [ {
>      "c1" : "hello",
>      "c2" : "world",
>      "c3" : "1"
>    }, {
>      "c1" : "hello",
>      "c2" : "world"
>    }, {
>      "c1" : "hello"
>    }, {
>      "c1" : "hello",
>      "c2" : null,
>      "c3" : null
>    } ]
> 
>    On 1/7/20, 1:14 PM, "Shawn Weeks" <sw...@weeksconsulting.us> wrote:
> 
>        I think to do that we'll need a way to distinguish between fields that were there and blank and fields that were never populated. Maybe an extension of the record api itself to distinguish between null and not there. Or allowing the CSV Reader to redefine null to something like empty string.
> 
>        I welcome any ideas.
> 
>        Thanks
>        Shawn
> 
>        On 1/7/20, 1:07 PM, "Matt Burgess" <ma...@apache.org> wrote:
> 
>            I believe ValidateRecord would route the flow file to failure in that
>            case. If you know the kind of data going in, then you might just be
>            able to treat the failure relationship more like the invalid
>            relationship. However I like the idea of doing it in ValidateRecord as
>            Mark proposed in the Jira.
> 
>            On Tue, Jan 7, 2020 at 2:02 PM Shawn Weeks <sw...@weeksconsulting.us> wrote:
>> 
>> I see where I think we could do this in the CSV Reader itself.  Jackson actually has an option to fail on missing fields and for csv commons we know how many fields we have and how many are in a record. Maybe just add a couple of true/false fields to fail on too few or too many fields like we do with PutDatabaseRecord. Not sure how to integrate that into ValidateRecord though as by that point it’s already parses.
>> 
>> 
>> 
>> Thanks
>> 
>> Shawn
>> 
>> 
>> 
>> From: Mark Payne <ma...@hotmail.com>
>> Reply-To: "users@nifi.apache.org" <us...@nifi.apache.org>
>> Date: Tuesday, January 7, 2020 at 12:52 PM
>> To: "users@nifi.apache.org" <us...@nifi.apache.org>
>> Subject: Re: Validating CSV File
>> 
>> 
>> 
>> I do agree that this is something that is not easily done with ValidateRecord (and probably cannot be done with ValidateRecord). But it's something that I think ValidateRecord *should* allow for. To that end, I've created a JIRA [1] to track this.
>> 
>> 
>> 
>> Thanks
>> 
>> -Mark
>> 
>> 
>> 
>> [1] https://issues.apache.org/jira/browse/NIFI-6986
>> 
>> 
>> 
>> 
>> 
>> On Jan 7, 2020, at 1:24 PM, Shawn Weeks <sw...@weeksconsulting.us> wrote:
>> 
>> 
>> 
>> I’ve been playing around with it but I’m not sure how to do the kind of validation I need. Consider this CSV. How would I validate this with ValidateCSV?
>> 
>> 
>> 
>> Good CSV
>> 
>> c1,c2,c3
>> 
>> hello,world,1
>> 
>> hello,world,
>> 
>> hello,,
>> 
>> 
>> 
>> Bad CSV
>> 
>> c1,c2,c3
>> 
>> hello,world,1
>> 
>> hello,world
>> 
>> hello
>> 
>> 
>> 
>> From: Emanuel Oliveira <em...@gmail.com>
>> Reply-To: "users@nifi.apache.org" <us...@nifi.apache.org>
>> Date: Tuesday, January 7, 2020 at 12:21 PM
>> To: "users@nifi.apache.org" <us...@nifi.apache.org>
>> Subject: Re: Validating CSV File
>> 
>> 
>> 
>> ValidateCsv is the most robust (handles missing fields as your need), it doesn't use Avro Schemas, instead use inline sequence of functions to accomplish anything you want (nulls ok or not, types, regex etc).
>> 
>> 
>> 
>> In recent project while struggling for maximum data quality i tried all different processors and options and ValidateCsv is the clear winner for CSVs.
>> 
>> 
>> 
>> Emanuel O.
>> 
>> 
>> 
>> On Mon 6 Jan 2020, 23:36 Matt Burgess, <ma...@gmail.com> wrote:
>> 
>> What about ValidateCsv, could that do what you want?
>> 
>> Sent from my iPhone
>> 
>> 
>> 
>> 
>> On Jan 6, 2020, at 6:10 PM, Shawn Weeks <sw...@weeksconsulting.us> wrote:
>> 
>> I’m poking around to see if I can make the csv parsers fail on a schema mismatch like that. A stream command would be a good option though.
>> 
>> 
>> 
>> Thanks
>> 
>> Shawn
>> 
>> 
>> 
>> From: Mike Thomsen <mi...@gmail.com>
>> Reply-To: "users@nifi.apache.org" <us...@nifi.apache.org>
>> Date: Monday, January 6, 2020 at 4:35 PM
>> To: "users@nifi.apache.org" <us...@nifi.apache.org>
>> Subject: Re: Validating CSV File
>> 
>> 
>> 
>> We have a lot of the same issues where I work, and our solution is to use ExecuteStreamCommand to pass CSVs off to Python scripts that will read stdin line by line to check to see if the export isn't screwed up. Some of our sources are good and we don't have to do that, but others are minefields in terms of the quality of the upstream data source, and that's the only way we've found where we can predictably handle such things.
>> 
>> 
>> 
>> On Mon, Jan 6, 2020 at 4:57 PM Shawn Weeks <sw...@weeksconsulting.us> wrote:
>> 
>> That's the challenge, the values can be null but I want to know the fields are missing(aka not enough delimiters). I run into a common scenario where line feeds end up in the data making a short row. Currently the reader just ignores the fact that there aren't enough delimiters and makes them null.
>> 
>> On 1/6/20, 3:50 PM, "Matt Burgess" <ma...@apache.org> wrote:
>> 
>>    Shawn,
>> 
>>    Your schema indicates that the fields are optional because of the
>>    "type" :  ["null", "string"] , so IIRC they won't be marked as invalid
>>    because they are treated as null (I'm not sure there's a difference in
>>    the code between missing and null fields).
>> 
>>    You can try "type": "string" in ValidateRecord to see if that fixes
>>    it, or there's a "StrNotNullOrEmpty" operator in ValidateCSV.
>> 
>>    Regards,
>>    Matt
>> 
>>    On Mon, Jan 6, 2020 at 4:35 PM Shawn Weeks <sw...@weeksconsulting.us> wrote:
>>> 
>>> I’m trying to validate that a csv file has the number of fields defined in it’s Avro schema. Consider the following schema and CSVs. I would like to be able to reject the invalid csv as missing fields.
>>> 
>>> 
>>> 
>>> {
>>> 
>>>   "type" : "record",
>>> 
>>>   "namespace" : "nifi",
>>> 
>>>   "name" : "nifi",
>>> 
>>>   "fields" : [
>>> 
>>>      { "name" : "c1" , "type" :  ["null", "string"] },
>>> 
>>>      { "name" : "c2" , "type" : ["null", "string"] },
>>> 
>>>      { "name" : "c3" , "type" : ["null", "string"] }
>>> 
>>>   ]
>>> 
>>> }
>>> 
>>> 
>>> 
>>> Good CSV
>>> 
>>> c1,c2,c3
>>> 
>>> hello,world,1
>>> 
>>> hello,world,
>>> 
>>> hello,,
>>> 
>>> 
>>> 
>>> Bad CSV
>>> 
>>> c1,c2,c3
>>> 
>>> hello,world,1
>>> 
>>> hello,world
>>> 
>>> hello
>>> 
>>> 
>> 
>> 
>> 
> 
> 
> 
> 
> 
> 


Re: Validating CSV File

Posted by Shawn Weeks <sw...@weeksconsulting.us>.
Seems like all you need is for the getValue method on the Record Interface to return something different if the Field doesn't exist instead of null. Maybe an enum? Then you could correctly determine that a nullable field didn't exist vs was just null in ValidateRecord.

On 1/7/20, 2:03 PM, "Shawn Weeks" <sw...@weeksconsulting.us> wrote:

    So I'm playing around with it some more and ValidateRecord is kinda working but it's finding stuff valid that isn't. Shouldn't some of those records have been rejected? I realized that by writing out to AVRO it was stubbing out the records but the actual input didn't have them.
    
    This is the input
    c1,c2,c3
    hello,world,1
    hello,world
    hello
    hello,,
    
    The schema
    {
       "type" : "record",
       "namespace" : "nifi",
       "name" : "nifi",
       "fields" : [
          { "name" : "c1" , "type" : ["null","string"] },
          { "name" : "c2" , "type" : ["null","string"] },
          { "name" : "c3" , "type" : ["null","string"] }
       ]
    }
    
    The output in JSON
    [ {
      "c1" : "hello",
      "c2" : "world",
      "c3" : "1"
    }, {
      "c1" : "hello",
      "c2" : "world"
    }, {
      "c1" : "hello"
    }, {
      "c1" : "hello",
      "c2" : null,
      "c3" : null
    } ]
    
    On 1/7/20, 1:14 PM, "Shawn Weeks" <sw...@weeksconsulting.us> wrote:
    
        I think to do that we'll need a way to distinguish between fields that were there and blank and fields that were never populated. Maybe an extension of the record api itself to distinguish between null and not there. Or allowing the CSV Reader to redefine null to something like empty string.
        
        I welcome any ideas.
        
        Thanks
        Shawn
        
        On 1/7/20, 1:07 PM, "Matt Burgess" <ma...@apache.org> wrote:
        
            I believe ValidateRecord would route the flow file to failure in that
            case. If you know the kind of data going in, then you might just be
            able to treat the failure relationship more like the invalid
            relationship. However I like the idea of doing it in ValidateRecord as
            Mark proposed in the Jira.
            
            On Tue, Jan 7, 2020 at 2:02 PM Shawn Weeks <sw...@weeksconsulting.us> wrote:
            >
            > I see where I think we could do this in the CSV Reader itself.  Jackson actually has an option to fail on missing fields and for csv commons we know how many fields we have and how many are in a record. Maybe just add a couple of true/false fields to fail on too few or too many fields like we do with PutDatabaseRecord. Not sure how to integrate that into ValidateRecord though as by that point it’s already parses.
            >
            >
            >
            > Thanks
            >
            > Shawn
            >
            >
            >
            > From: Mark Payne <ma...@hotmail.com>
            > Reply-To: "users@nifi.apache.org" <us...@nifi.apache.org>
            > Date: Tuesday, January 7, 2020 at 12:52 PM
            > To: "users@nifi.apache.org" <us...@nifi.apache.org>
            > Subject: Re: Validating CSV File
            >
            >
            >
            > I do agree that this is something that is not easily done with ValidateRecord (and probably cannot be done with ValidateRecord). But it's something that I think ValidateRecord *should* allow for. To that end, I've created a JIRA [1] to track this.
            >
            >
            >
            > Thanks
            >
            > -Mark
            >
            >
            >
            > [1] https://issues.apache.org/jira/browse/NIFI-6986
            >
            >
            >
            >
            >
            > On Jan 7, 2020, at 1:24 PM, Shawn Weeks <sw...@weeksconsulting.us> wrote:
            >
            >
            >
            > I’ve been playing around with it but I’m not sure how to do the kind of validation I need. Consider this CSV. How would I validate this with ValidateCSV?
            >
            >
            >
            > Good CSV
            >
            > c1,c2,c3
            >
            > hello,world,1
            >
            > hello,world,
            >
            > hello,,
            >
            >
            >
            > Bad CSV
            >
            > c1,c2,c3
            >
            > hello,world,1
            >
            > hello,world
            >
            > hello
            >
            >
            >
            > From: Emanuel Oliveira <em...@gmail.com>
            > Reply-To: "users@nifi.apache.org" <us...@nifi.apache.org>
            > Date: Tuesday, January 7, 2020 at 12:21 PM
            > To: "users@nifi.apache.org" <us...@nifi.apache.org>
            > Subject: Re: Validating CSV File
            >
            >
            >
            > ValidateCsv is the most robust (handles missing fields as your need), it doesn't use Avro Schemas, instead use inline sequence of functions to accomplish anything you want (nulls ok or not, types, regex etc).
            >
            >
            >
            > In recent project while struggling for maximum data quality i tried all different processors and options and ValidateCsv is the clear winner for CSVs.
            >
            >
            >
            > Emanuel O.
            >
            >
            >
            > On Mon 6 Jan 2020, 23:36 Matt Burgess, <ma...@gmail.com> wrote:
            >
            > What about ValidateCsv, could that do what you want?
            >
            > Sent from my iPhone
            >
            >
            >
            >
            > On Jan 6, 2020, at 6:10 PM, Shawn Weeks <sw...@weeksconsulting.us> wrote:
            >
            > I’m poking around to see if I can make the csv parsers fail on a schema mismatch like that. A stream command would be a good option though.
            >
            >
            >
            > Thanks
            >
            > Shawn
            >
            >
            >
            > From: Mike Thomsen <mi...@gmail.com>
            > Reply-To: "users@nifi.apache.org" <us...@nifi.apache.org>
            > Date: Monday, January 6, 2020 at 4:35 PM
            > To: "users@nifi.apache.org" <us...@nifi.apache.org>
            > Subject: Re: Validating CSV File
            >
            >
            >
            > We have a lot of the same issues where I work, and our solution is to use ExecuteStreamCommand to pass CSVs off to Python scripts that will read stdin line by line to check to see if the export isn't screwed up. Some of our sources are good and we don't have to do that, but others are minefields in terms of the quality of the upstream data source, and that's the only way we've found where we can predictably handle such things.
            >
            >
            >
            > On Mon, Jan 6, 2020 at 4:57 PM Shawn Weeks <sw...@weeksconsulting.us> wrote:
            >
            > That's the challenge, the values can be null but I want to know the fields are missing(aka not enough delimiters). I run into a common scenario where line feeds end up in the data making a short row. Currently the reader just ignores the fact that there aren't enough delimiters and makes them null.
            >
            > On 1/6/20, 3:50 PM, "Matt Burgess" <ma...@apache.org> wrote:
            >
            >     Shawn,
            >
            >     Your schema indicates that the fields are optional because of the
            >     "type" :  ["null", "string"] , so IIRC they won't be marked as invalid
            >     because they are treated as null (I'm not sure there's a difference in
            >     the code between missing and null fields).
            >
            >     You can try "type": "string" in ValidateRecord to see if that fixes
            >     it, or there's a "StrNotNullOrEmpty" operator in ValidateCSV.
            >
            >     Regards,
            >     Matt
            >
            >     On Mon, Jan 6, 2020 at 4:35 PM Shawn Weeks <sw...@weeksconsulting.us> wrote:
            >     >
            >     > I’m trying to validate that a csv file has the number of fields defined in it’s Avro schema. Consider the following schema and CSVs. I would like to be able to reject the invalid csv as missing fields.
            >     >
            >     >
            >     >
            >     > {
            >     >
            >     >    "type" : "record",
            >     >
            >     >    "namespace" : "nifi",
            >     >
            >     >    "name" : "nifi",
            >     >
            >     >    "fields" : [
            >     >
            >     >       { "name" : "c1" , "type" :  ["null", "string"] },
            >     >
            >     >       { "name" : "c2" , "type" : ["null", "string"] },
            >     >
            >     >       { "name" : "c3" , "type" : ["null", "string"] }
            >     >
            >     >    ]
            >     >
            >     > }
            >     >
            >     >
            >     >
            >     > Good CSV
            >     >
            >     > c1,c2,c3
            >     >
            >     > hello,world,1
            >     >
            >     > hello,world,
            >     >
            >     > hello,,
            >     >
            >     >
            >     >
            >     > Bad CSV
            >     >
            >     > c1,c2,c3
            >     >
            >     > hello,world,1
            >     >
            >     > hello,world
            >     >
            >     > hello
            >     >
            >     >
            >
            >
            >
            
        
        
    
    


Re: Validating CSV File

Posted by Shawn Weeks <sw...@weeksconsulting.us>.
So I'm playing around with it some more and ValidateRecord is kinda working but it's finding stuff valid that isn't. Shouldn't some of those records have been rejected? I realized that by writing out to AVRO it was stubbing out the records but the actual input didn't have them.

This is the input
c1,c2,c3
hello,world,1
hello,world
hello
hello,,

The schema
{
   "type" : "record",
   "namespace" : "nifi",
   "name" : "nifi",
   "fields" : [
      { "name" : "c1" , "type" : ["null","string"] },
      { "name" : "c2" , "type" : ["null","string"] },
      { "name" : "c3" , "type" : ["null","string"] }
   ]
}

The output in JSON
[ {
  "c1" : "hello",
  "c2" : "world",
  "c3" : "1"
}, {
  "c1" : "hello",
  "c2" : "world"
}, {
  "c1" : "hello"
}, {
  "c1" : "hello",
  "c2" : null,
  "c3" : null
} ]

On 1/7/20, 1:14 PM, "Shawn Weeks" <sw...@weeksconsulting.us> wrote:

    I think to do that we'll need a way to distinguish between fields that were there and blank and fields that were never populated. Maybe an extension of the record api itself to distinguish between null and not there. Or allowing the CSV Reader to redefine null to something like empty string.
    
    I welcome any ideas.
    
    Thanks
    Shawn
    
    On 1/7/20, 1:07 PM, "Matt Burgess" <ma...@apache.org> wrote:
    
        I believe ValidateRecord would route the flow file to failure in that
        case. If you know the kind of data going in, then you might just be
        able to treat the failure relationship more like the invalid
        relationship. However I like the idea of doing it in ValidateRecord as
        Mark proposed in the Jira.
        
        On Tue, Jan 7, 2020 at 2:02 PM Shawn Weeks <sw...@weeksconsulting.us> wrote:
        >
        > I see where I think we could do this in the CSV Reader itself.  Jackson actually has an option to fail on missing fields and for csv commons we know how many fields we have and how many are in a record. Maybe just add a couple of true/false fields to fail on too few or too many fields like we do with PutDatabaseRecord. Not sure how to integrate that into ValidateRecord though as by that point it’s already parses.
        >
        >
        >
        > Thanks
        >
        > Shawn
        >
        >
        >
        > From: Mark Payne <ma...@hotmail.com>
        > Reply-To: "users@nifi.apache.org" <us...@nifi.apache.org>
        > Date: Tuesday, January 7, 2020 at 12:52 PM
        > To: "users@nifi.apache.org" <us...@nifi.apache.org>
        > Subject: Re: Validating CSV File
        >
        >
        >
        > I do agree that this is something that is not easily done with ValidateRecord (and probably cannot be done with ValidateRecord). But it's something that I think ValidateRecord *should* allow for. To that end, I've created a JIRA [1] to track this.
        >
        >
        >
        > Thanks
        >
        > -Mark
        >
        >
        >
        > [1] https://issues.apache.org/jira/browse/NIFI-6986
        >
        >
        >
        >
        >
        > On Jan 7, 2020, at 1:24 PM, Shawn Weeks <sw...@weeksconsulting.us> wrote:
        >
        >
        >
        > I’ve been playing around with it but I’m not sure how to do the kind of validation I need. Consider this CSV. How would I validate this with ValidateCSV?
        >
        >
        >
        > Good CSV
        >
        > c1,c2,c3
        >
        > hello,world,1
        >
        > hello,world,
        >
        > hello,,
        >
        >
        >
        > Bad CSV
        >
        > c1,c2,c3
        >
        > hello,world,1
        >
        > hello,world
        >
        > hello
        >
        >
        >
        > From: Emanuel Oliveira <em...@gmail.com>
        > Reply-To: "users@nifi.apache.org" <us...@nifi.apache.org>
        > Date: Tuesday, January 7, 2020 at 12:21 PM
        > To: "users@nifi.apache.org" <us...@nifi.apache.org>
        > Subject: Re: Validating CSV File
        >
        >
        >
        > ValidateCsv is the most robust (handles missing fields as your need), it doesn't use Avro Schemas, instead use inline sequence of functions to accomplish anything you want (nulls ok or not, types, regex etc).
        >
        >
        >
        > In recent project while struggling for maximum data quality i tried all different processors and options and ValidateCsv is the clear winner for CSVs.
        >
        >
        >
        > Emanuel O.
        >
        >
        >
        > On Mon 6 Jan 2020, 23:36 Matt Burgess, <ma...@gmail.com> wrote:
        >
        > What about ValidateCsv, could that do what you want?
        >
        > Sent from my iPhone
        >
        >
        >
        >
        > On Jan 6, 2020, at 6:10 PM, Shawn Weeks <sw...@weeksconsulting.us> wrote:
        >
        > I’m poking around to see if I can make the csv parsers fail on a schema mismatch like that. A stream command would be a good option though.
        >
        >
        >
        > Thanks
        >
        > Shawn
        >
        >
        >
        > From: Mike Thomsen <mi...@gmail.com>
        > Reply-To: "users@nifi.apache.org" <us...@nifi.apache.org>
        > Date: Monday, January 6, 2020 at 4:35 PM
        > To: "users@nifi.apache.org" <us...@nifi.apache.org>
        > Subject: Re: Validating CSV File
        >
        >
        >
        > We have a lot of the same issues where I work, and our solution is to use ExecuteStreamCommand to pass CSVs off to Python scripts that will read stdin line by line to check to see if the export isn't screwed up. Some of our sources are good and we don't have to do that, but others are minefields in terms of the quality of the upstream data source, and that's the only way we've found where we can predictably handle such things.
        >
        >
        >
        > On Mon, Jan 6, 2020 at 4:57 PM Shawn Weeks <sw...@weeksconsulting.us> wrote:
        >
        > That's the challenge, the values can be null but I want to know the fields are missing(aka not enough delimiters). I run into a common scenario where line feeds end up in the data making a short row. Currently the reader just ignores the fact that there aren't enough delimiters and makes them null.
        >
        > On 1/6/20, 3:50 PM, "Matt Burgess" <ma...@apache.org> wrote:
        >
        >     Shawn,
        >
        >     Your schema indicates that the fields are optional because of the
        >     "type" :  ["null", "string"] , so IIRC they won't be marked as invalid
        >     because they are treated as null (I'm not sure there's a difference in
        >     the code between missing and null fields).
        >
        >     You can try "type": "string" in ValidateRecord to see if that fixes
        >     it, or there's a "StrNotNullOrEmpty" operator in ValidateCSV.
        >
        >     Regards,
        >     Matt
        >
        >     On Mon, Jan 6, 2020 at 4:35 PM Shawn Weeks <sw...@weeksconsulting.us> wrote:
        >     >
        >     > I’m trying to validate that a csv file has the number of fields defined in it’s Avro schema. Consider the following schema and CSVs. I would like to be able to reject the invalid csv as missing fields.
        >     >
        >     >
        >     >
        >     > {
        >     >
        >     >    "type" : "record",
        >     >
        >     >    "namespace" : "nifi",
        >     >
        >     >    "name" : "nifi",
        >     >
        >     >    "fields" : [
        >     >
        >     >       { "name" : "c1" , "type" :  ["null", "string"] },
        >     >
        >     >       { "name" : "c2" , "type" : ["null", "string"] },
        >     >
        >     >       { "name" : "c3" , "type" : ["null", "string"] }
        >     >
        >     >    ]
        >     >
        >     > }
        >     >
        >     >
        >     >
        >     > Good CSV
        >     >
        >     > c1,c2,c3
        >     >
        >     > hello,world,1
        >     >
        >     > hello,world,
        >     >
        >     > hello,,
        >     >
        >     >
        >     >
        >     > Bad CSV
        >     >
        >     > c1,c2,c3
        >     >
        >     > hello,world,1
        >     >
        >     > hello,world
        >     >
        >     > hello
        >     >
        >     >
        >
        >
        >
        
    
    


Re: Validating CSV File

Posted by Shawn Weeks <sw...@weeksconsulting.us>.
I think to do that we'll need a way to distinguish between fields that were there and blank and fields that were never populated. Maybe an extension of the record api itself to distinguish between null and not there. Or allowing the CSV Reader to redefine null to something like empty string.

I welcome any ideas.

Thanks
Shawn

On 1/7/20, 1:07 PM, "Matt Burgess" <ma...@apache.org> wrote:

    I believe ValidateRecord would route the flow file to failure in that
    case. If you know the kind of data going in, then you might just be
    able to treat the failure relationship more like the invalid
    relationship. However I like the idea of doing it in ValidateRecord as
    Mark proposed in the Jira.
    
    On Tue, Jan 7, 2020 at 2:02 PM Shawn Weeks <sw...@weeksconsulting.us> wrote:
    >
    > I see where I think we could do this in the CSV Reader itself.  Jackson actually has an option to fail on missing fields and for csv commons we know how many fields we have and how many are in a record. Maybe just add a couple of true/false fields to fail on too few or too many fields like we do with PutDatabaseRecord. Not sure how to integrate that into ValidateRecord though as by that point it’s already parses.
    >
    >
    >
    > Thanks
    >
    > Shawn
    >
    >
    >
    > From: Mark Payne <ma...@hotmail.com>
    > Reply-To: "users@nifi.apache.org" <us...@nifi.apache.org>
    > Date: Tuesday, January 7, 2020 at 12:52 PM
    > To: "users@nifi.apache.org" <us...@nifi.apache.org>
    > Subject: Re: Validating CSV File
    >
    >
    >
    > I do agree that this is something that is not easily done with ValidateRecord (and probably cannot be done with ValidateRecord). But it's something that I think ValidateRecord *should* allow for. To that end, I've created a JIRA [1] to track this.
    >
    >
    >
    > Thanks
    >
    > -Mark
    >
    >
    >
    > [1] https://issues.apache.org/jira/browse/NIFI-6986
    >
    >
    >
    >
    >
    > On Jan 7, 2020, at 1:24 PM, Shawn Weeks <sw...@weeksconsulting.us> wrote:
    >
    >
    >
    > I’ve been playing around with it but I’m not sure how to do the kind of validation I need. Consider this CSV. How would I validate this with ValidateCSV?
    >
    >
    >
    > Good CSV
    >
    > c1,c2,c3
    >
    > hello,world,1
    >
    > hello,world,
    >
    > hello,,
    >
    >
    >
    > Bad CSV
    >
    > c1,c2,c3
    >
    > hello,world,1
    >
    > hello,world
    >
    > hello
    >
    >
    >
    > From: Emanuel Oliveira <em...@gmail.com>
    > Reply-To: "users@nifi.apache.org" <us...@nifi.apache.org>
    > Date: Tuesday, January 7, 2020 at 12:21 PM
    > To: "users@nifi.apache.org" <us...@nifi.apache.org>
    > Subject: Re: Validating CSV File
    >
    >
    >
    > ValidateCsv is the most robust (handles missing fields as your need), it doesn't use Avro Schemas, instead use inline sequence of functions to accomplish anything you want (nulls ok or not, types, regex etc).
    >
    >
    >
    > In recent project while struggling for maximum data quality i tried all different processors and options and ValidateCsv is the clear winner for CSVs.
    >
    >
    >
    > Emanuel O.
    >
    >
    >
    > On Mon 6 Jan 2020, 23:36 Matt Burgess, <ma...@gmail.com> wrote:
    >
    > What about ValidateCsv, could that do what you want?
    >
    > Sent from my iPhone
    >
    >
    >
    >
    > On Jan 6, 2020, at 6:10 PM, Shawn Weeks <sw...@weeksconsulting.us> wrote:
    >
    > I’m poking around to see if I can make the csv parsers fail on a schema mismatch like that. A stream command would be a good option though.
    >
    >
    >
    > Thanks
    >
    > Shawn
    >
    >
    >
    > From: Mike Thomsen <mi...@gmail.com>
    > Reply-To: "users@nifi.apache.org" <us...@nifi.apache.org>
    > Date: Monday, January 6, 2020 at 4:35 PM
    > To: "users@nifi.apache.org" <us...@nifi.apache.org>
    > Subject: Re: Validating CSV File
    >
    >
    >
    > We have a lot of the same issues where I work, and our solution is to use ExecuteStreamCommand to pass CSVs off to Python scripts that will read stdin line by line to check to see if the export isn't screwed up. Some of our sources are good and we don't have to do that, but others are minefields in terms of the quality of the upstream data source, and that's the only way we've found where we can predictably handle such things.
    >
    >
    >
    > On Mon, Jan 6, 2020 at 4:57 PM Shawn Weeks <sw...@weeksconsulting.us> wrote:
    >
    > That's the challenge, the values can be null but I want to know the fields are missing(aka not enough delimiters). I run into a common scenario where line feeds end up in the data making a short row. Currently the reader just ignores the fact that there aren't enough delimiters and makes them null.
    >
    > On 1/6/20, 3:50 PM, "Matt Burgess" <ma...@apache.org> wrote:
    >
    >     Shawn,
    >
    >     Your schema indicates that the fields are optional because of the
    >     "type" :  ["null", "string"] , so IIRC they won't be marked as invalid
    >     because they are treated as null (I'm not sure there's a difference in
    >     the code between missing and null fields).
    >
    >     You can try "type": "string" in ValidateRecord to see if that fixes
    >     it, or there's a "StrNotNullOrEmpty" operator in ValidateCSV.
    >
    >     Regards,
    >     Matt
    >
    >     On Mon, Jan 6, 2020 at 4:35 PM Shawn Weeks <sw...@weeksconsulting.us> wrote:
    >     >
    >     > I’m trying to validate that a csv file has the number of fields defined in it’s Avro schema. Consider the following schema and CSVs. I would like to be able to reject the invalid csv as missing fields.
    >     >
    >     >
    >     >
    >     > {
    >     >
    >     >    "type" : "record",
    >     >
    >     >    "namespace" : "nifi",
    >     >
    >     >    "name" : "nifi",
    >     >
    >     >    "fields" : [
    >     >
    >     >       { "name" : "c1" , "type" :  ["null", "string"] },
    >     >
    >     >       { "name" : "c2" , "type" : ["null", "string"] },
    >     >
    >     >       { "name" : "c3" , "type" : ["null", "string"] }
    >     >
    >     >    ]
    >     >
    >     > }
    >     >
    >     >
    >     >
    >     > Good CSV
    >     >
    >     > c1,c2,c3
    >     >
    >     > hello,world,1
    >     >
    >     > hello,world,
    >     >
    >     > hello,,
    >     >
    >     >
    >     >
    >     > Bad CSV
    >     >
    >     > c1,c2,c3
    >     >
    >     > hello,world,1
    >     >
    >     > hello,world
    >     >
    >     > hello
    >     >
    >     >
    >
    >
    >
    


Re: Validating CSV File

Posted by Matt Burgess <ma...@apache.org>.
I believe ValidateRecord would route the flow file to failure in that
case. If you know the kind of data going in, then you might just be
able to treat the failure relationship more like the invalid
relationship. However I like the idea of doing it in ValidateRecord as
Mark proposed in the Jira.

On Tue, Jan 7, 2020 at 2:02 PM Shawn Weeks <sw...@weeksconsulting.us> wrote:
>
> I see where I think we could do this in the CSV Reader itself.  Jackson actually has an option to fail on missing fields and for csv commons we know how many fields we have and how many are in a record. Maybe just add a couple of true/false fields to fail on too few or too many fields like we do with PutDatabaseRecord. Not sure how to integrate that into ValidateRecord though as by that point it’s already parses.
>
>
>
> Thanks
>
> Shawn
>
>
>
> From: Mark Payne <ma...@hotmail.com>
> Reply-To: "users@nifi.apache.org" <us...@nifi.apache.org>
> Date: Tuesday, January 7, 2020 at 12:52 PM
> To: "users@nifi.apache.org" <us...@nifi.apache.org>
> Subject: Re: Validating CSV File
>
>
>
> I do agree that this is something that is not easily done with ValidateRecord (and probably cannot be done with ValidateRecord). But it's something that I think ValidateRecord *should* allow for. To that end, I've created a JIRA [1] to track this.
>
>
>
> Thanks
>
> -Mark
>
>
>
> [1] https://issues.apache.org/jira/browse/NIFI-6986
>
>
>
>
>
> On Jan 7, 2020, at 1:24 PM, Shawn Weeks <sw...@weeksconsulting.us> wrote:
>
>
>
> I’ve been playing around with it but I’m not sure how to do the kind of validation I need. Consider this CSV. How would I validate this with ValidateCSV?
>
>
>
> Good CSV
>
> c1,c2,c3
>
> hello,world,1
>
> hello,world,
>
> hello,,
>
>
>
> Bad CSV
>
> c1,c2,c3
>
> hello,world,1
>
> hello,world
>
> hello
>
>
>
> From: Emanuel Oliveira <em...@gmail.com>
> Reply-To: "users@nifi.apache.org" <us...@nifi.apache.org>
> Date: Tuesday, January 7, 2020 at 12:21 PM
> To: "users@nifi.apache.org" <us...@nifi.apache.org>
> Subject: Re: Validating CSV File
>
>
>
> ValidateCsv is the most robust (handles missing fields as your need), it doesn't use Avro Schemas, instead use inline sequence of functions to accomplish anything you want (nulls ok or not, types, regex etc).
>
>
>
> In recent project while struggling for maximum data quality i tried all different processors and options and ValidateCsv is the clear winner for CSVs.
>
>
>
> Emanuel O.
>
>
>
> On Mon 6 Jan 2020, 23:36 Matt Burgess, <ma...@gmail.com> wrote:
>
> What about ValidateCsv, could that do what you want?
>
> Sent from my iPhone
>
>
>
>
> On Jan 6, 2020, at 6:10 PM, Shawn Weeks <sw...@weeksconsulting.us> wrote:
>
> I’m poking around to see if I can make the csv parsers fail on a schema mismatch like that. A stream command would be a good option though.
>
>
>
> Thanks
>
> Shawn
>
>
>
> From: Mike Thomsen <mi...@gmail.com>
> Reply-To: "users@nifi.apache.org" <us...@nifi.apache.org>
> Date: Monday, January 6, 2020 at 4:35 PM
> To: "users@nifi.apache.org" <us...@nifi.apache.org>
> Subject: Re: Validating CSV File
>
>
>
> We have a lot of the same issues where I work, and our solution is to use ExecuteStreamCommand to pass CSVs off to Python scripts that will read stdin line by line to check to see if the export isn't screwed up. Some of our sources are good and we don't have to do that, but others are minefields in terms of the quality of the upstream data source, and that's the only way we've found where we can predictably handle such things.
>
>
>
> On Mon, Jan 6, 2020 at 4:57 PM Shawn Weeks <sw...@weeksconsulting.us> wrote:
>
> That's the challenge, the values can be null but I want to know the fields are missing(aka not enough delimiters). I run into a common scenario where line feeds end up in the data making a short row. Currently the reader just ignores the fact that there aren't enough delimiters and makes them null.
>
> On 1/6/20, 3:50 PM, "Matt Burgess" <ma...@apache.org> wrote:
>
>     Shawn,
>
>     Your schema indicates that the fields are optional because of the
>     "type" :  ["null", "string"] , so IIRC they won't be marked as invalid
>     because they are treated as null (I'm not sure there's a difference in
>     the code between missing and null fields).
>
>     You can try "type": "string" in ValidateRecord to see if that fixes
>     it, or there's a "StrNotNullOrEmpty" operator in ValidateCSV.
>
>     Regards,
>     Matt
>
>     On Mon, Jan 6, 2020 at 4:35 PM Shawn Weeks <sw...@weeksconsulting.us> wrote:
>     >
>     > I’m trying to validate that a csv file has the number of fields defined in it’s Avro schema. Consider the following schema and CSVs. I would like to be able to reject the invalid csv as missing fields.
>     >
>     >
>     >
>     > {
>     >
>     >    "type" : "record",
>     >
>     >    "namespace" : "nifi",
>     >
>     >    "name" : "nifi",
>     >
>     >    "fields" : [
>     >
>     >       { "name" : "c1" , "type" :  ["null", "string"] },
>     >
>     >       { "name" : "c2" , "type" : ["null", "string"] },
>     >
>     >       { "name" : "c3" , "type" : ["null", "string"] }
>     >
>     >    ]
>     >
>     > }
>     >
>     >
>     >
>     > Good CSV
>     >
>     > c1,c2,c3
>     >
>     > hello,world,1
>     >
>     > hello,world,
>     >
>     > hello,,
>     >
>     >
>     >
>     > Bad CSV
>     >
>     > c1,c2,c3
>     >
>     > hello,world,1
>     >
>     > hello,world
>     >
>     > hello
>     >
>     >
>
>
>

Re: Validating CSV File

Posted by Shawn Weeks <sw...@weeksconsulting.us>.
I see where I think we could do this in the CSV Reader itself.  Jackson actually has an option to fail on missing fields and for csv commons we know how many fields we have and how many are in a record. Maybe just add a couple of true/false fields to fail on too few or too many fields like we do with PutDatabaseRecord. Not sure how to integrate that into ValidateRecord though as by that point it’s already parses.

Thanks
Shawn

From: Mark Payne <ma...@hotmail.com>
Reply-To: "users@nifi.apache.org" <us...@nifi.apache.org>
Date: Tuesday, January 7, 2020 at 12:52 PM
To: "users@nifi.apache.org" <us...@nifi.apache.org>
Subject: Re: Validating CSV File

I do agree that this is something that is not easily done with ValidateRecord (and probably cannot be done with ValidateRecord). But it's something that I think ValidateRecord *should* allow for. To that end, I've created a JIRA [1] to track this.

Thanks
-Mark

[1] https://issues.apache.org/jira/browse/NIFI-6986



On Jan 7, 2020, at 1:24 PM, Shawn Weeks <sw...@weeksconsulting.us>> wrote:

I’ve been playing around with it but I’m not sure how to do the kind of validation I need. Consider this CSV. How would I validate this with ValidateCSV?

Good CSV
c1,c2,c3
hello,world,1
hello,world,
hello,,

Bad CSV
c1,c2,c3
hello,world,1
hello,world
hello

From: Emanuel Oliveira <em...@gmail.com>>
Reply-To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Date: Tuesday, January 7, 2020 at 12:21 PM
To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Subject: Re: Validating CSV File

ValidateCsv is the most robust (handles missing fields as your need), it doesn't use Avro Schemas, instead use inline sequence of functions to accomplish anything you want (nulls ok or not, types, regex etc).

In recent project while struggling for maximum data quality i tried all different processors and options and ValidateCsv is the clear winner for CSVs.

Emanuel O.

On Mon 6 Jan 2020, 23:36 Matt Burgess, <ma...@gmail.com>> wrote:
What about ValidateCsv, could that do what you want?
Sent from my iPhone



On Jan 6, 2020, at 6:10 PM, Shawn Weeks <sw...@weeksconsulting.us>> wrote:
I’m poking around to see if I can make the csv parsers fail on a schema mismatch like that. A stream command would be a good option though.

Thanks
Shawn

From: Mike Thomsen <mi...@gmail.com>>
Reply-To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Date: Monday, January 6, 2020 at 4:35 PM
To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Subject: Re: Validating CSV File

We have a lot of the same issues where I work, and our solution is to use ExecuteStreamCommand to pass CSVs off to Python scripts that will read stdin line by line to check to see if the export isn't screwed up. Some of our sources are good and we don't have to do that, but others are minefields in terms of the quality of the upstream data source, and that's the only way we've found where we can predictably handle such things.

On Mon, Jan 6, 2020 at 4:57 PM Shawn Weeks <sw...@weeksconsulting.us>> wrote:
That's the challenge, the values can be null but I want to know the fields are missing(aka not enough delimiters). I run into a common scenario where line feeds end up in the data making a short row. Currently the reader just ignores the fact that there aren't enough delimiters and makes them null.

On 1/6/20, 3:50 PM, "Matt Burgess" <ma...@apache.org>> wrote:

    Shawn,

    Your schema indicates that the fields are optional because of the
    "type" :  ["null", "string"] , so IIRC they won't be marked as invalid
    because they are treated as null (I'm not sure there's a difference in
    the code between missing and null fields).

    You can try "type": "string" in ValidateRecord to see if that fixes
    it, or there's a "StrNotNullOrEmpty" operator in ValidateCSV.

    Regards,
    Matt

    On Mon, Jan 6, 2020 at 4:35 PM Shawn Weeks <sw...@weeksconsulting.us>> wrote:
    >
    > I’m trying to validate that a csv file has the number of fields defined in it’s Avro schema. Consider the following schema and CSVs. I would like to be able to reject the invalid csv as missing fields.
    >
    >
    >
    > {
    >
    >    "type" : "record",
    >
    >    "namespace" : "nifi",
    >
    >    "name" : "nifi",
    >
    >    "fields" : [
    >
    >       { "name" : "c1" , "type" :  ["null", "string"] },
    >
    >       { "name" : "c2" , "type" : ["null", "string"] },
    >
    >       { "name" : "c3" , "type" : ["null", "string"] }
    >
    >    ]
    >
    > }
    >
    >
    >
    > Good CSV
    >
    > c1,c2,c3
    >
    > hello,world,1
    >
    > hello,world,
    >
    > hello,,
    >
    >
    >
    > Bad CSV
    >
    > c1,c2,c3
    >
    > hello,world,1
    >
    > hello,world
    >
    > hello
    >
    >



Re: Validating CSV File

Posted by Mark Payne <ma...@hotmail.com>.
I do agree that this is something that is not easily done with ValidateRecord (and probably cannot be done with ValidateRecord). But it's something that I think ValidateRecord *should* allow for. To that end, I've created a JIRA [1] to track this.

Thanks
-Mark

[1] https://issues.apache.org/jira/browse/NIFI-6986 <https://issues.apache.org/jira/browse/NIFI-6986>

> On Jan 7, 2020, at 1:24 PM, Shawn Weeks <sw...@weeksconsulting.us> wrote:
> 
> I’ve been playing around with it but I’m not sure how to do the kind of validation I need. Consider this CSV. How would I validate this with ValidateCSV?
>  
> Good CSV
> c1,c2,c3
> hello,world,1
> hello,world,
> hello,,
>  
> Bad CSV
> c1,c2,c3
> hello,world,1
> hello,world
> hello
>  
> From: Emanuel Oliveira <em...@gmail.com>
> Reply-To: "users@nifi.apache.org" <us...@nifi.apache.org>
> Date: Tuesday, January 7, 2020 at 12:21 PM
> To: "users@nifi.apache.org" <us...@nifi.apache.org>
> Subject: Re: Validating CSV File
>  
> ValidateCsv is the most robust (handles missing fields as your need), it doesn't use Avro Schemas, instead use inline sequence of functions to accomplish anything you want (nulls ok or not, types, regex etc).
>  
> In recent project while struggling for maximum data quality i tried all different processors and options and ValidateCsv is the clear winner for CSVs.
>  
> Emanuel O.
>  
> On Mon 6 Jan 2020, 23:36 Matt Burgess, <mattyb149@gmail.com <ma...@gmail.com>> wrote:
> What about ValidateCsv, could that do what you want?
> 
> Sent from my iPhone
> 
> 
> On Jan 6, 2020, at 6:10 PM, Shawn Weeks <sweeks@weeksconsulting.us <ma...@weeksconsulting.us>> wrote:
> 
> I’m poking around to see if I can make the csv parsers fail on a schema mismatch like that. A stream command would be a good option though.
>  
> Thanks
> Shawn
>  
> From: Mike Thomsen <mikerthomsen@gmail.com <ma...@gmail.com>>
> Reply-To: "users@nifi.apache.org <ma...@nifi.apache.org>" <users@nifi.apache.org <ma...@nifi.apache.org>>
> Date: Monday, January 6, 2020 at 4:35 PM
> To: "users@nifi.apache.org <ma...@nifi.apache.org>" <users@nifi.apache.org <ma...@nifi.apache.org>>
> Subject: Re: Validating CSV File
>  
> We have a lot of the same issues where I work, and our solution is to use ExecuteStreamCommand to pass CSVs off to Python scripts that will read stdin line by line to check to see if the export isn't screwed up. Some of our sources are good and we don't have to do that, but others are minefields in terms of the quality of the upstream data source, and that's the only way we've found where we can predictably handle such things.
>  
> On Mon, Jan 6, 2020 at 4:57 PM Shawn Weeks <sweeks@weeksconsulting.us <ma...@weeksconsulting.us>> wrote:
> That's the challenge, the values can be null but I want to know the fields are missing(aka not enough delimiters). I run into a common scenario where line feeds end up in the data making a short row. Currently the reader just ignores the fact that there aren't enough delimiters and makes them null.
> 
> On 1/6/20, 3:50 PM, "Matt Burgess" <mattyb149@apache.org <ma...@apache.org>> wrote:
> 
>     Shawn,
> 
>     Your schema indicates that the fields are optional because of the
>     "type" :  ["null", "string"] , so IIRC they won't be marked as invalid
>     because they are treated as null (I'm not sure there's a difference in
>     the code between missing and null fields).
> 
>     You can try "type": "string" in ValidateRecord to see if that fixes
>     it, or there's a "StrNotNullOrEmpty" operator in ValidateCSV.
> 
>     Regards,
>     Matt
> 
>     On Mon, Jan 6, 2020 at 4:35 PM Shawn Weeks <sweeks@weeksconsulting.us <ma...@weeksconsulting.us>> wrote:
>     >
>     > I’m trying to validate that a csv file has the number of fields defined in it’s Avro schema. Consider the following schema and CSVs. I would like to be able to reject the invalid csv as missing fields.
>     >
>     >
>     >
>     > {
>     >
>     >    "type" : "record",
>     >
>     >    "namespace" : "nifi",
>     >
>     >    "name" : "nifi",
>     >
>     >    "fields" : [
>     >
>     >       { "name" : "c1" , "type" :  ["null", "string"] },
>     >
>     >       { "name" : "c2" , "type" : ["null", "string"] },
>     >
>     >       { "name" : "c3" , "type" : ["null", "string"] }
>     >
>     >    ]
>     >
>     > }
>     >
>     >
>     >
>     > Good CSV
>     >
>     > c1,c2,c3
>     >
>     > hello,world,1
>     >
>     > hello,world,
>     >
>     > hello,,
>     >
>     >
>     >
>     > Bad CSV
>     >
>     > c1,c2,c3
>     >
>     > hello,world,1
>     >
>     > hello,world
>     >
>     > hello
>     >
>     >
> 


Re: Validating CSV File

Posted by Shawn Weeks <sw...@weeksconsulting.us>.
I’ve been playing around with it but I’m not sure how to do the kind of validation I need. Consider this CSV. How would I validate this with ValidateCSV?

Good CSV
c1,c2,c3
hello,world,1
hello,world,
hello,,

Bad CSV
c1,c2,c3
hello,world,1
hello,world
hello

From: Emanuel Oliveira <em...@gmail.com>
Reply-To: "users@nifi.apache.org" <us...@nifi.apache.org>
Date: Tuesday, January 7, 2020 at 12:21 PM
To: "users@nifi.apache.org" <us...@nifi.apache.org>
Subject: Re: Validating CSV File

ValidateCsv is the most robust (handles missing fields as your need), it doesn't use Avro Schemas, instead use inline sequence of functions to accomplish anything you want (nulls ok or not, types, regex etc).

In recent project while struggling for maximum data quality i tried all different processors and options and ValidateCsv is the clear winner for CSVs.

Emanuel O.

On Mon 6 Jan 2020, 23:36 Matt Burgess, <ma...@gmail.com>> wrote:
What about ValidateCsv, could that do what you want?
Sent from my iPhone


On Jan 6, 2020, at 6:10 PM, Shawn Weeks <sw...@weeksconsulting.us>> wrote:
I’m poking around to see if I can make the csv parsers fail on a schema mismatch like that. A stream command would be a good option though.

Thanks
Shawn

From: Mike Thomsen <mi...@gmail.com>>
Reply-To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Date: Monday, January 6, 2020 at 4:35 PM
To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Subject: Re: Validating CSV File

We have a lot of the same issues where I work, and our solution is to use ExecuteStreamCommand to pass CSVs off to Python scripts that will read stdin line by line to check to see if the export isn't screwed up. Some of our sources are good and we don't have to do that, but others are minefields in terms of the quality of the upstream data source, and that's the only way we've found where we can predictably handle such things.

On Mon, Jan 6, 2020 at 4:57 PM Shawn Weeks <sw...@weeksconsulting.us>> wrote:
That's the challenge, the values can be null but I want to know the fields are missing(aka not enough delimiters). I run into a common scenario where line feeds end up in the data making a short row. Currently the reader just ignores the fact that there aren't enough delimiters and makes them null.

On 1/6/20, 3:50 PM, "Matt Burgess" <ma...@apache.org>> wrote:

    Shawn,

    Your schema indicates that the fields are optional because of the
    "type" :  ["null", "string"] , so IIRC they won't be marked as invalid
    because they are treated as null (I'm not sure there's a difference in
    the code between missing and null fields).

    You can try "type": "string" in ValidateRecord to see if that fixes
    it, or there's a "StrNotNullOrEmpty" operator in ValidateCSV.

    Regards,
    Matt

    On Mon, Jan 6, 2020 at 4:35 PM Shawn Weeks <sw...@weeksconsulting.us>> wrote:
    >
    > I’m trying to validate that a csv file has the number of fields defined in it’s Avro schema. Consider the following schema and CSVs. I would like to be able to reject the invalid csv as missing fields.
    >
    >
    >
    > {
    >
    >    "type" : "record",
    >
    >    "namespace" : "nifi",
    >
    >    "name" : "nifi",
    >
    >    "fields" : [
    >
    >       { "name" : "c1" , "type" :  ["null", "string"] },
    >
    >       { "name" : "c2" , "type" : ["null", "string"] },
    >
    >       { "name" : "c3" , "type" : ["null", "string"] }
    >
    >    ]
    >
    > }
    >
    >
    >
    > Good CSV
    >
    > c1,c2,c3
    >
    > hello,world,1
    >
    > hello,world,
    >
    > hello,,
    >
    >
    >
    > Bad CSV
    >
    > c1,c2,c3
    >
    > hello,world,1
    >
    > hello,world
    >
    > hello
    >
    >

Re: Validating CSV File

Posted by Emanuel Oliveira <em...@gmail.com>.
ValidateCsv is the most robust (handles missing fields as your need), it
doesn't use Avro Schemas, instead use inline sequence of functions to
accomplish anything you want (nulls ok or not, types, regex etc).

In recent project while struggling for maximum data quality i tried all
different processors and options and ValidateCsv is the clear winner for
CSVs.

Emanuel O.

On Mon 6 Jan 2020, 23:36 Matt Burgess, <ma...@gmail.com> wrote:

> What about ValidateCsv, could that do what you want?
>
> Sent from my iPhone
>
> On Jan 6, 2020, at 6:10 PM, Shawn Weeks <sw...@weeksconsulting.us> wrote:
>
> 
>
> I’m poking around to see if I can make the csv parsers fail on a schema
> mismatch like that. A stream command would be a good option though.
>
>
>
> Thanks
>
> Shawn
>
>
>
> *From: *Mike Thomsen <mi...@gmail.com>
> *Reply-To: *"users@nifi.apache.org" <us...@nifi.apache.org>
> *Date: *Monday, January 6, 2020 at 4:35 PM
> *To: *"users@nifi.apache.org" <us...@nifi.apache.org>
> *Subject: *Re: Validating CSV File
>
>
>
> We have a lot of the same issues where I work, and our solution is to use
> ExecuteStreamCommand to pass CSVs off to Python scripts that will read
> stdin line by line to check to see if the export isn't screwed up. Some of
> our sources are good and we don't have to do that, but others are
> minefields in terms of the quality of the upstream data source, and that's
> the only way we've found where we can predictably handle such things.
>
>
>
> On Mon, Jan 6, 2020 at 4:57 PM Shawn Weeks <sw...@weeksconsulting.us>
> wrote:
>
> That's the challenge, the values can be null but I want to know the fields
> are missing(aka not enough delimiters). I run into a common scenario where
> line feeds end up in the data making a short row. Currently the reader just
> ignores the fact that there aren't enough delimiters and makes them null.
>
> On 1/6/20, 3:50 PM, "Matt Burgess" <ma...@apache.org> wrote:
>
>     Shawn,
>
>     Your schema indicates that the fields are optional because of the
>     "type" :  ["null", "string"] , so IIRC they won't be marked as invalid
>     because they are treated as null (I'm not sure there's a difference in
>     the code between missing and null fields).
>
>     You can try "type": "string" in ValidateRecord to see if that fixes
>     it, or there's a "StrNotNullOrEmpty" operator in ValidateCSV.
>
>     Regards,
>     Matt
>
>     On Mon, Jan 6, 2020 at 4:35 PM Shawn Weeks <sw...@weeksconsulting.us>
> wrote:
>     >
>     > I’m trying to validate that a csv file has the number of fields
> defined in it’s Avro schema. Consider the following schema and CSVs. I
> would like to be able to reject the invalid csv as missing fields.
>     >
>     >
>     >
>     > {
>     >
>     >    "type" : "record",
>     >
>     >    "namespace" : "nifi",
>     >
>     >    "name" : "nifi",
>     >
>     >    "fields" : [
>     >
>     >       { "name" : "c1" , "type" :  ["null", "string"] },
>     >
>     >       { "name" : "c2" , "type" : ["null", "string"] },
>     >
>     >       { "name" : "c3" , "type" : ["null", "string"] }
>     >
>     >    ]
>     >
>     > }
>     >
>     >
>     >
>     > Good CSV
>     >
>     > c1,c2,c3
>     >
>     > hello,world,1
>     >
>     > hello,world,
>     >
>     > hello,,
>     >
>     >
>     >
>     > Bad CSV
>     >
>     > c1,c2,c3
>     >
>     > hello,world,1
>     >
>     > hello,world
>     >
>     > hello
>     >
>     >
>
>

Re: Validating CSV File

Posted by Matt Burgess <ma...@gmail.com>.
What about ValidateCsv, could that do what you want?

Sent from my iPhone

> On Jan 6, 2020, at 6:10 PM, Shawn Weeks <sw...@weeksconsulting.us> wrote:
> 
> 
> I’m poking around to see if I can make the csv parsers fail on a schema mismatch like that. A stream command would be a good option though.
>  
> Thanks
> Shawn
>  
> From: Mike Thomsen <mi...@gmail.com>
> Reply-To: "users@nifi.apache.org" <us...@nifi.apache.org>
> Date: Monday, January 6, 2020 at 4:35 PM
> To: "users@nifi.apache.org" <us...@nifi.apache.org>
> Subject: Re: Validating CSV File
>  
> We have a lot of the same issues where I work, and our solution is to use ExecuteStreamCommand to pass CSVs off to Python scripts that will read stdin line by line to check to see if the export isn't screwed up. Some of our sources are good and we don't have to do that, but others are minefields in terms of the quality of the upstream data source, and that's the only way we've found where we can predictably handle such things.
>  
> On Mon, Jan 6, 2020 at 4:57 PM Shawn Weeks <sw...@weeksconsulting.us> wrote:
> That's the challenge, the values can be null but I want to know the fields are missing(aka not enough delimiters). I run into a common scenario where line feeds end up in the data making a short row. Currently the reader just ignores the fact that there aren't enough delimiters and makes them null.
> 
> On 1/6/20, 3:50 PM, "Matt Burgess" <ma...@apache.org> wrote:
> 
>     Shawn,
> 
>     Your schema indicates that the fields are optional because of the
>     "type" :  ["null", "string"] , so IIRC they won't be marked as invalid
>     because they are treated as null (I'm not sure there's a difference in
>     the code between missing and null fields).
> 
>     You can try "type": "string" in ValidateRecord to see if that fixes
>     it, or there's a "StrNotNullOrEmpty" operator in ValidateCSV.
> 
>     Regards,
>     Matt
> 
>     On Mon, Jan 6, 2020 at 4:35 PM Shawn Weeks <sw...@weeksconsulting.us> wrote:
>     >
>     > I’m trying to validate that a csv file has the number of fields defined in it’s Avro schema. Consider the following schema and CSVs. I would like to be able to reject the invalid csv as missing fields.
>     >
>     >
>     >
>     > {
>     >
>     >    "type" : "record",
>     >
>     >    "namespace" : "nifi",
>     >
>     >    "name" : "nifi",
>     >
>     >    "fields" : [
>     >
>     >       { "name" : "c1" , "type" :  ["null", "string"] },
>     >
>     >       { "name" : "c2" , "type" : ["null", "string"] },
>     >
>     >       { "name" : "c3" , "type" : ["null", "string"] }
>     >
>     >    ]
>     >
>     > }
>     >
>     >
>     >
>     > Good CSV
>     >
>     > c1,c2,c3
>     >
>     > hello,world,1
>     >
>     > hello,world,
>     >
>     > hello,,
>     >
>     >
>     >
>     > Bad CSV
>     >
>     > c1,c2,c3
>     >
>     > hello,world,1
>     >
>     > hello,world
>     >
>     > hello
>     >
>     >
> 

Re: Validating CSV File

Posted by Mike Thomsen <mi...@gmail.com>.
Are you seeing the screwups happening consistently?

On Mon, Jan 6, 2020 at 6:10 PM Shawn Weeks <sw...@weeksconsulting.us>
wrote:

> I’m poking around to see if I can make the csv parsers fail on a schema
> mismatch like that. A stream command would be a good option though.
>
>
>
> Thanks
>
> Shawn
>
>
>
> *From: *Mike Thomsen <mi...@gmail.com>
> *Reply-To: *"users@nifi.apache.org" <us...@nifi.apache.org>
> *Date: *Monday, January 6, 2020 at 4:35 PM
> *To: *"users@nifi.apache.org" <us...@nifi.apache.org>
> *Subject: *Re: Validating CSV File
>
>
>
> We have a lot of the same issues where I work, and our solution is to use
> ExecuteStreamCommand to pass CSVs off to Python scripts that will read
> stdin line by line to check to see if the export isn't screwed up. Some of
> our sources are good and we don't have to do that, but others are
> minefields in terms of the quality of the upstream data source, and that's
> the only way we've found where we can predictably handle such things.
>
>
>
> On Mon, Jan 6, 2020 at 4:57 PM Shawn Weeks <sw...@weeksconsulting.us>
> wrote:
>
> That's the challenge, the values can be null but I want to know the fields
> are missing(aka not enough delimiters). I run into a common scenario where
> line feeds end up in the data making a short row. Currently the reader just
> ignores the fact that there aren't enough delimiters and makes them null.
>
> On 1/6/20, 3:50 PM, "Matt Burgess" <ma...@apache.org> wrote:
>
>     Shawn,
>
>     Your schema indicates that the fields are optional because of the
>     "type" :  ["null", "string"] , so IIRC they won't be marked as invalid
>     because they are treated as null (I'm not sure there's a difference in
>     the code between missing and null fields).
>
>     You can try "type": "string" in ValidateRecord to see if that fixes
>     it, or there's a "StrNotNullOrEmpty" operator in ValidateCSV.
>
>     Regards,
>     Matt
>
>     On Mon, Jan 6, 2020 at 4:35 PM Shawn Weeks <sw...@weeksconsulting.us>
> wrote:
>     >
>     > I’m trying to validate that a csv file has the number of fields
> defined in it’s Avro schema. Consider the following schema and CSVs. I
> would like to be able to reject the invalid csv as missing fields.
>     >
>     >
>     >
>     > {
>     >
>     >    "type" : "record",
>     >
>     >    "namespace" : "nifi",
>     >
>     >    "name" : "nifi",
>     >
>     >    "fields" : [
>     >
>     >       { "name" : "c1" , "type" :  ["null", "string"] },
>     >
>     >       { "name" : "c2" , "type" : ["null", "string"] },
>     >
>     >       { "name" : "c3" , "type" : ["null", "string"] }
>     >
>     >    ]
>     >
>     > }
>     >
>     >
>     >
>     > Good CSV
>     >
>     > c1,c2,c3
>     >
>     > hello,world,1
>     >
>     > hello,world,
>     >
>     > hello,,
>     >
>     >
>     >
>     > Bad CSV
>     >
>     > c1,c2,c3
>     >
>     > hello,world,1
>     >
>     > hello,world
>     >
>     > hello
>     >
>     >
>
>

Re: Validating CSV File

Posted by Shawn Weeks <sw...@weeksconsulting.us>.
I’m poking around to see if I can make the csv parsers fail on a schema mismatch like that. A stream command would be a good option though.

Thanks
Shawn

From: Mike Thomsen <mi...@gmail.com>
Reply-To: "users@nifi.apache.org" <us...@nifi.apache.org>
Date: Monday, January 6, 2020 at 4:35 PM
To: "users@nifi.apache.org" <us...@nifi.apache.org>
Subject: Re: Validating CSV File

We have a lot of the same issues where I work, and our solution is to use ExecuteStreamCommand to pass CSVs off to Python scripts that will read stdin line by line to check to see if the export isn't screwed up. Some of our sources are good and we don't have to do that, but others are minefields in terms of the quality of the upstream data source, and that's the only way we've found where we can predictably handle such things.

On Mon, Jan 6, 2020 at 4:57 PM Shawn Weeks <sw...@weeksconsulting.us>> wrote:
That's the challenge, the values can be null but I want to know the fields are missing(aka not enough delimiters). I run into a common scenario where line feeds end up in the data making a short row. Currently the reader just ignores the fact that there aren't enough delimiters and makes them null.

On 1/6/20, 3:50 PM, "Matt Burgess" <ma...@apache.org>> wrote:

    Shawn,

    Your schema indicates that the fields are optional because of the
    "type" :  ["null", "string"] , so IIRC they won't be marked as invalid
    because they are treated as null (I'm not sure there's a difference in
    the code between missing and null fields).

    You can try "type": "string" in ValidateRecord to see if that fixes
    it, or there's a "StrNotNullOrEmpty" operator in ValidateCSV.

    Regards,
    Matt

    On Mon, Jan 6, 2020 at 4:35 PM Shawn Weeks <sw...@weeksconsulting.us>> wrote:
    >
    > I’m trying to validate that a csv file has the number of fields defined in it’s Avro schema. Consider the following schema and CSVs. I would like to be able to reject the invalid csv as missing fields.
    >
    >
    >
    > {
    >
    >    "type" : "record",
    >
    >    "namespace" : "nifi",
    >
    >    "name" : "nifi",
    >
    >    "fields" : [
    >
    >       { "name" : "c1" , "type" :  ["null", "string"] },
    >
    >       { "name" : "c2" , "type" : ["null", "string"] },
    >
    >       { "name" : "c3" , "type" : ["null", "string"] }
    >
    >    ]
    >
    > }
    >
    >
    >
    > Good CSV
    >
    > c1,c2,c3
    >
    > hello,world,1
    >
    > hello,world,
    >
    > hello,,
    >
    >
    >
    > Bad CSV
    >
    > c1,c2,c3
    >
    > hello,world,1
    >
    > hello,world
    >
    > hello
    >
    >


Re: Validating CSV File

Posted by Mike Thomsen <mi...@gmail.com>.
We have a lot of the same issues where I work, and our solution is to use
ExecuteStreamCommand to pass CSVs off to Python scripts that will read
stdin line by line to check to see if the export isn't screwed up. Some of
our sources are good and we don't have to do that, but others are
minefields in terms of the quality of the upstream data source, and that's
the only way we've found where we can predictably handle such things.

On Mon, Jan 6, 2020 at 4:57 PM Shawn Weeks <sw...@weeksconsulting.us>
wrote:

> That's the challenge, the values can be null but I want to know the fields
> are missing(aka not enough delimiters). I run into a common scenario where
> line feeds end up in the data making a short row. Currently the reader just
> ignores the fact that there aren't enough delimiters and makes them null.
>
> On 1/6/20, 3:50 PM, "Matt Burgess" <ma...@apache.org> wrote:
>
>     Shawn,
>
>     Your schema indicates that the fields are optional because of the
>     "type" :  ["null", "string"] , so IIRC they won't be marked as invalid
>     because they are treated as null (I'm not sure there's a difference in
>     the code between missing and null fields).
>
>     You can try "type": "string" in ValidateRecord to see if that fixes
>     it, or there's a "StrNotNullOrEmpty" operator in ValidateCSV.
>
>     Regards,
>     Matt
>
>     On Mon, Jan 6, 2020 at 4:35 PM Shawn Weeks <sw...@weeksconsulting.us>
> wrote:
>     >
>     > I’m trying to validate that a csv file has the number of fields
> defined in it’s Avro schema. Consider the following schema and CSVs. I
> would like to be able to reject the invalid csv as missing fields.
>     >
>     >
>     >
>     > {
>     >
>     >    "type" : "record",
>     >
>     >    "namespace" : "nifi",
>     >
>     >    "name" : "nifi",
>     >
>     >    "fields" : [
>     >
>     >       { "name" : "c1" , "type" :  ["null", "string"] },
>     >
>     >       { "name" : "c2" , "type" : ["null", "string"] },
>     >
>     >       { "name" : "c3" , "type" : ["null", "string"] }
>     >
>     >    ]
>     >
>     > }
>     >
>     >
>     >
>     > Good CSV
>     >
>     > c1,c2,c3
>     >
>     > hello,world,1
>     >
>     > hello,world,
>     >
>     > hello,,
>     >
>     >
>     >
>     > Bad CSV
>     >
>     > c1,c2,c3
>     >
>     > hello,world,1
>     >
>     > hello,world
>     >
>     > hello
>     >
>     >
>
>
>

Re: Validating CSV File

Posted by Shawn Weeks <sw...@weeksconsulting.us>.
That's the challenge, the values can be null but I want to know the fields are missing(aka not enough delimiters). I run into a common scenario where line feeds end up in the data making a short row. Currently the reader just ignores the fact that there aren't enough delimiters and makes them null.

On 1/6/20, 3:50 PM, "Matt Burgess" <ma...@apache.org> wrote:

    Shawn,
    
    Your schema indicates that the fields are optional because of the
    "type" :  ["null", "string"] , so IIRC they won't be marked as invalid
    because they are treated as null (I'm not sure there's a difference in
    the code between missing and null fields).
    
    You can try "type": "string" in ValidateRecord to see if that fixes
    it, or there's a "StrNotNullOrEmpty" operator in ValidateCSV.
    
    Regards,
    Matt
    
    On Mon, Jan 6, 2020 at 4:35 PM Shawn Weeks <sw...@weeksconsulting.us> wrote:
    >
    > I’m trying to validate that a csv file has the number of fields defined in it’s Avro schema. Consider the following schema and CSVs. I would like to be able to reject the invalid csv as missing fields.
    >
    >
    >
    > {
    >
    >    "type" : "record",
    >
    >    "namespace" : "nifi",
    >
    >    "name" : "nifi",
    >
    >    "fields" : [
    >
    >       { "name" : "c1" , "type" :  ["null", "string"] },
    >
    >       { "name" : "c2" , "type" : ["null", "string"] },
    >
    >       { "name" : "c3" , "type" : ["null", "string"] }
    >
    >    ]
    >
    > }
    >
    >
    >
    > Good CSV
    >
    > c1,c2,c3
    >
    > hello,world,1
    >
    > hello,world,
    >
    > hello,,
    >
    >
    >
    > Bad CSV
    >
    > c1,c2,c3
    >
    > hello,world,1
    >
    > hello,world
    >
    > hello
    >
    >
    


Re: Validating CSV File

Posted by Matt Burgess <ma...@apache.org>.
Shawn,

Your schema indicates that the fields are optional because of the
"type" :  ["null", "string"] , so IIRC they won't be marked as invalid
because they are treated as null (I'm not sure there's a difference in
the code between missing and null fields).

You can try "type": "string" in ValidateRecord to see if that fixes
it, or there's a "StrNotNullOrEmpty" operator in ValidateCSV.

Regards,
Matt

On Mon, Jan 6, 2020 at 4:35 PM Shawn Weeks <sw...@weeksconsulting.us> wrote:
>
> I’m trying to validate that a csv file has the number of fields defined in it’s Avro schema. Consider the following schema and CSVs. I would like to be able to reject the invalid csv as missing fields.
>
>
>
> {
>
>    "type" : "record",
>
>    "namespace" : "nifi",
>
>    "name" : "nifi",
>
>    "fields" : [
>
>       { "name" : "c1" , "type" :  ["null", "string"] },
>
>       { "name" : "c2" , "type" : ["null", "string"] },
>
>       { "name" : "c3" , "type" : ["null", "string"] }
>
>    ]
>
> }
>
>
>
> Good CSV
>
> c1,c2,c3
>
> hello,world,1
>
> hello,world,
>
> hello,,
>
>
>
> Bad CSV
>
> c1,c2,c3
>
> hello,world,1
>
> hello,world
>
> hello
>
>