Mysql import datetime format

[richardoctoey]

If i want to change the import format for datetime(yyyy-MM-dd HH:mm:ss) field it will fail to import. I already write the mapping about date format in sh file, but jdbc seems automatically insert data with elastic date format (epoch millis) rather than mysql format.

my sh file

echo '
{
  "type": "jdbc",
  "jdbc": {
    "url": "jdbc:mysql://localhost:3304/db_tes",
    "user": "xxxx",
    "password": "xxx",
    "sql": "select * from tbl_badan_hukum limit 0,1000",
    "index": "tbl_badan_hukum",
    "type": "tbl_badan_hukum",
    "index_settings": {
      "index": {
        "analysis": {
          "filter": {
            "dbl_metaphone": {
              "type": "phonetic",
              "encoder": "double_metaphone"
            }
          },
          "analyzer": {
            "dbl_metaphone": {
              "tokenizer": "standard",
              "filter": "dbl_metaphone"
            }
          }
        }
      }
    },
    "type_mapping": {
      "properties": {
        "nama": {
          "type": "string",
          "fields": {
            "phonetic": {
              "type": "string",
              "analyzer": "dbl_metaphone"
            }
          }
        },
        "created_date": {
          "type": "date",
          "format": "yyyy-MM-dd HH:mm:ss"
        }
      }
    },
    "elasticsearch": {
      "host": "localhost:9300"
    }
  }
}
' | java \
    -cp "${lib}/*" \
    -Dlog4j.configurationFile=${bin}/log4j2.xml \
    org.xbib.tools.Runner \
    org.xbib.tools.JDBCImporter

the error log (repetitive until end)

[20:43:30,500][ERROR][org.xbib.elasticsearch.helper.client.BulkTransportClient][elasticsearch[importer][listener][T#1]] bulk [1] failed with 1000 failed items, failure message = failure in bulk execution: [0]: index [tbl_badan_hukum], type [tbl_badan_hukum], id [AVRDXEh2wvrO2IR6-yNu], message [MapperParsingException[failed to parse [created]]; nested: IllegalArgumentException[Invalid format: "2014-10-01T13:40:24.000+07:00" is malformed at "T13:40:24.000+07:00"];]

[jprante]

You have to convert date/timestamps to ISO 8601 UTC in your SQL query to match Elasticsearch date format. JDBC importer does not support any "native" date format of a database, such as MySQL.