Data Visualizer - CSV Import - Date Mapping issue with Empty Sting/NULL values

[Matt_Vasquez]

I am trying to import data from a CSV that has several date column with the format of "6/21/2012" which my mapping reflects as
"type": "date",
"format": "M/d/yyyy"

Note: There are empty strings as for some values in the date columns, I don't think these are being treated as null while importing.

While trying to import with the above date mappings I receive this error:

Index:
test_index

Documents ingested:
41

Failed documents:
4909

Some documents could not be imported:
4950 out of 4950 documents could not be imported. This could be due to lines not matching the Grok pattern.

elasticsearch.log is showing:

org.elasticsearch.index.mapper.MapperParsingException: failed to parse field [DATE] of type [date]
.....
...
..
.
Caused by: java.lang.IllegalArgumentException: Invalid format: ""

The only way i can get it to ingest is by passing " "index.mapping.ignore_malformed": true" in the index settings. This seems kinda of lazy as I want to be informed about other malformed mappings..

[Joe_Fleming]

I haven't tried this, but you could pretty easily; given the mapping on the date field, if you index a document with a value of an empty string (""), do you see the same error? And also, do you see the same error when you index a document that simply doesn't contain that field?

I think that's where that error comes from, Elasticsearch expects a string in the M/d/yyyy format, because that's what you set in the mapping, but it's getting an empty string instead, and that doesn't follow that rule. ES supports sparse data, but enforces mappings, so the solution here, aside from using ignore_malformed may be to just exclude the field in the document when you index it, instead of using an empty string.

I am curious too, what are you using to index the CSV data?

[Matt_Vasquez]

I am using the data visualizer in the machine learning section to import the CSV data and apply mappings. I believe its a pretty new feature..

this is the error I see when manually indexing a document with an empty sting on a mapped date field:

{
  "error": {
    "root_cause": [
      {
        "type": "mapper_parsing_exception",
        "reason": "failed to parse field [date] of type [date]"
      }
    ],
    "type": "mapper_parsing_exception",
    "reason": "failed to parse field [date] of type [date]",
    "caused_by": {
      "type": "illegal_argument_exception",
      "reason": "Invalid format: \"\""
    }
  },
  "status": 400
}

[richcollier]

The CSV import functionality is indeed new (and experimental in v6.5). You can see from the docs that the following date formats are supported:

dd/MMM/YYYY:HH:mm:ss Z
EEE MMM dd HH:mm zzz YYYY
EEE MMM dd HH:mm:ss YYYY
EEE MMM dd HH:mm:ss zzz YYYY
EEE MMM dd YYYY HH:mm zzz
EEE MMM dd YYYY HH:mm:ss zzz
EEE, dd MMM YYYY HH:mm Z
EEE, dd MMM YYYY HH:mm ZZ
EEE, dd MMM YYYY HH:mm:ss Z
EEE, dd MMM YYYY HH:mm:ss ZZ
ISO8601
MMM d HH:mm:ss
MMM d HH:mm:ss,SSS
MMM d YYYY HH:mm:ss
MMM dd HH:mm:ss
MMM dd HH:mm:ss,SSS
MMM dd YYYY HH:mm:ss
MMM dd, YYYY h:mm:ss a
TAI64N
UNIX
UNIX_MS
YYYY-MM-dd HH:mm:ss
YYYY-MM-dd HH:mm:ss,SSS
YYYY-MM-dd HH:mm:ss,SSS Z
YYYY-MM-dd HH:mm:ss,SSSZ
YYYY-MM-dd HH:mm:ss,SSSZZ
YYYY-MM-dd HH:mm:ssZ
YYYY-MM-dd HH:mm:ssZZ
YYYYMMddHHmmss

Your date format isn't one of those.