Date format changes and causes an error in a transform

[Jker (Julius)]

Hello!

I'm trying to create a transform from two different indices but keep on running to this error and have tried many different solutions but can't get it to work.

This is the error:

Failed to index documents into destination index due to permanent error
[BulkIndexingException[Bulk index experienced [160] failures and at least 1 irrecoverable [TransformException[Destination index mappings are incompatible with the transform configuration.]; nested: MapperParsingException[failed to parse field [user_created] of type [date] in document with id '<doc_id>'. Preview of field's value: '1.513493852792E12']; nested: IllegalArgumentException[failed to parse date field [1.513493852792E12] with format [strict_date_optional_time||epoch_millis]]; nested: DateTimeParseException[Failed to parse with all enclosed parsers];

This is the transform I'm running:

{
  "source": {
    "index": [
      "<first_index>",
      "<second_index>"
    ]
  },
  "dest": {
    "index": "<combined_index>"
  },
  "pivot": {
    "group_by": {
      "user_id": {
        "terms": {
          "field": "user_id"
        }
      }
    },
    "aggregations": {
      "user_created": {
        "max": {
          "field": "user_created"
        }
      },
      "last_access": {
        "max": {
          "field": "last_access"
        }
      }
    }
  }
}

And this is an example of the data I get running the preview:

    {
      "last_access" : 1.513493852792E12,
      "user_id" : "<user_id_string>",
      "user_created" : "2015-01-24T12:33:18.263Z"
    },

In some cases the user_created date is also the same format as the last_access in this example.

---

And this is the mapping in the destination index:

{
  "properties": {
    "user_id": {
      "type": "keyword"
    },
    "user_created": {
      "type": "date"
    },
    "last_access": {
      "type": "date"
    }
  }
}

In both of the indices the dates are completely normal "yyyy-MM-dd'T'HH:mm:ss.SSSZ" format but something happens in the transform that changes it to this 1.513493852792E12 kind of format that causes the error. Kind of still figuring out transforms and elastic as a whole.

Is there something that I'm missing or what could cause this kind of behavior? Thanks a lot!

[sophie_chang]

Hi

What version of elastic is this?

Transforms will deduce the mappings of the destination index, based on the mappings from the source indices. If the source index mappings are not the same, it could cause problems. Date formats can be tricky, but lets establish source index mappings first.

Please run

GET index1,index2/_mapping/field/user_created,last_access

Thanks

[Jker (Julius)]

Hey,

It's the latest version 8.0.

The output after running:

GET index1,index2/_mapping/field/user_created,last_access

Output:

{
  "<index1>" : {
    "mappings" : {
      "last_access" : {
        "full_name" : "last_access",
        "mapping" : {
          "last_access" : {
            "type" : "date"
          }
        }
      }
    }
  },
  "<index2>" : {
    "mappings" : {
      "user_created" : {
        "full_name" : "user_created",
        "mapping" : {
          "user_created" : {
            "type" : "date"
          }
        }
      }
    }
  }
}

Thanks!

[przemekwitek (Przemysław Witek)]

Hi,
Please create both source indices with the same mappings (even if no document in <index1> contain user_created field and vice versa):

PUT <index1>
{
  "mappings": {
    "properties": {
      "user_id": {
        "type": "keyword"
      },
      "user_created": {
        "type": "date"
      },
      "last_access": {
        "type": "date"
      }
    }
  }
}

PUT <index2>
{
  "mappings": {
    "properties": {
      "user_id": {
        "type": "keyword"
      },
      "user_created": {
        "type": "date"
      },
      "last_access": {
        "type": "date"
      }
    }
  }
}

[Jker (Julius)]

Hello!

Please create both source indices with the same mappings (even if no document in contain user_created field and vice versa):

Oh my, this worked like a charm!

Thank you very very much for the both of you!

You saved my day, maybe even the whole week.