Description:

Validates the contents of FlowFiles against a user-specified CSV schema. Take a look at the additional documentation of this processor for some schema examples.

Tags:

CSV, schema, validation

Properties:

In the list below, the names of required properties appear in bold. Any other properties (not in bold) are considered optional. The table also indicates any default values.

Name

Default Value

Allowable Values

Description

Schema

The schema to be used for validation. Is expected a comma-delimited string representing the cell processors to apply. The following cell processors are allowed in the schema definition: [ParseBigDecimal, ParseBool, ParseChar, ParseDate, ParseDouble, ParseInt, ParseLong, Optional, DMinMax, Equals, ForbidSubStr, LMinMax, NotNull, Null, RequireHashCode, RequireSubStr, Strlen, StrMinMax, StrNotNullOrEmpty, StrRegEx, Unique, UniqueHashCode, IsIncludedIn]. Note: cell processors cannot be nested except with Optional.

Header

true * true * false True if the incoming flow file contains a header to ignore, false otherwise.

Delimiter character

, Character used as 'delimiter' in the incoming data. Example: ,

Quote character

" Character used as 'quote' in the incoming data. Example: "

End of line symbols

\n Symbols used as 'end of line' in the incoming data. Example: \n

Validation strategy

FlowFile validation
  • Line by line validation In case an error is found, the input CSV file will be split into two FlowFiles: one routed to the 'valid' relationship containing all the correct lines and one routed to the 'invalid' relationship containing all the incorrect lines. Take care if choosing this option while using Unique cell processors in schema definition:the first occurrence will be considered valid and the next ones as invalid.
  • FlowFile validation As soon as an error is found in the CSV file, the validation will stop and the whole flow file will be routed to the 'invalid' relationship. This option offers best performances.
Strategy to apply when routing input files to output relationships.

Relationships:

Name

Description

valid FlowFiles that are successfully validated against the schema are routed to this relationship
invalid FlowFiles that are not valid according to the specified schema are routed to this relationship

Usage Information

The Validate CSV processor is based on the super-csv library and the concept of Cell Processors. The corresponding Java documentation can be found here.
The cell processors cannot be nested (except with Optional which gives the possibility to define a CellProcessor for values that could be null) and must be defined in a comma-delimited string as the Schema property.

The supported cell processors are:

  • ParseBigDecimal
  • ParseBool
  • ParseChar
  • ParseDate
  • ParseDouble
  • ParseInt
  • Optional
  • DMinMax
  • Equals
  • ForbidSubStr
  • LMinMax
  • NotNull
  • Null
  • RequireHashCode
  • RequireSubStr
  • Strlen
  • StrMinMax
  • StrNotNullOrEmpty
  • StrRegEx
  • Unique
  • UniqueHashCode
  • IsIncludedIn

Here are some examples:

    Schema property: Null, ParseDate("dd/MM/yyyy"), Optional(ParseDouble())
    Meaning: the input CSV has three columns, the first one can be null and has no specification, the second one must be a date formatted as expected, and the third one must a double or null (no value).
    Schema property: ParseBigDecimal(), ParseBool(), ParseChar(), ParseInt(), ParseLong()
    Meaning: the input CSV has five columns, the first one must be a big decimal, the second one must be a boolean, the third one must be a char, the fourth one must be an integer and the fifth one must be a long.
    Schema property: Equals(), NotNull(), StrNotNullOrEmpty()
    Meaning: the input CSV has three columns, all the values of the first column must be equal to each other, all the values of the second column must be not null, and all the values of the third column are not null/empty string values.
    Schema property: Strlen(4), StrMinMax(3,5), StrRegex("[a-z0-9\\._]+@[a-z0-9\\.]+")
    Meaning: the input CSV has three columns, all the values of the first column must be 4-characters long, all the values of the second column must be between 3 and 5 characters (inclusive), and all the values of the last column must match the provided regular expression (email address).
    Schema property: Unique(), UniqueHashCode()
    Meaning: the input CSV has two columns. All the values of the first column must be unique (all the values are stored in memory and this can be consuming depending of the input). All the values of the second column must be unique (only hash codes of the input values are stored to ensure uniqueness).
    Schema property: ForbidSubStr("test", "tset"), RequireSubStr("test")
    Meaning: the input CSV has two columns. None of the values in the first column must contain one of the provided strings. And all the values of the second column must contain the provided string.