解決Mysql同步到ES時date和time字段類型轉換問題

這個問題中發生問題的es字段映射類型為date類型?。。∽址愋偷臅煤芏?，關鍵我這邊是通過logstash生成索引，并且全量同步的，不好改索引類型！??！

問題描述：

這個報錯我該怎么辦呢2023-07-10 03:01:39.104 [pool-2-thread-1] INFO c.a.o.canal.client.adapter.logger.LoggerAdapterExample - DML: {"data":[{"ORDER_ID":"4444","SETTLE_DATE_LOC":"20200329","TRANS_DATE":"20200329","TRANS_TIME":"165440","USER_ID":null,"SPACE_NO":null,"SPACE_NAME":null,"PARK_NO":"JC01331","PARK_NAME":"DELETED","CARNO":"滬A666666","CAR_IN_SEQ":"20200329165438071000000000785843","CAR_OUT_SEQ":null,"START_TIME":"20181126103000","END_TIME":null,"LENGTH":null,"OVER_VALID_TIME":null,"LEAVE_TIME":null,"STATUS":"03","PAY_STYLE":null,"TRANS_AMT":"0","PRE_AMT":"0","BALANCE":"0","FAV_AMT":"0","RET_AMT":"0","RET_STATUS":null,"SETTLE_STATUS":null,"SETTLE_DATE":null,"SETTLE_SEQ":null,"SETTLE_AMT":null,"REC_CRT_TIME":"20200329165440","REC_UPD_TIME":"20200329165440","OPR_FLAG":"0","RESVD1":null,"RESVD2":"11111111111111111111111111111111","IF_HOLD":"0","LAST_UPD_TIME":null,"LAST_UPD_RESV":null,"RESVD3":null,"RESVD4":null,"RESVD5":null,"RESVD6":null,"IN_TYPE":"00","IF_RENT":null,"ORG_ID":"0","MCHNT_NO":"1000058","MCHNT_NAME":"捷順總部大廈停車場","IN_OUT_DATE":"2018-11-26","LEAVE_DATE":null,"IF_SCORE":"0","EXCEP_TYPE":null,"EXCEP_DATE":null,"EXCEP_TIME":null,"EXCEP_STATUS":null,"IF_PAYBACK":null,"DEVICE_TYPE":"112","SUB_DEVICE_TYPE":"r"}],"database":"cstc245_test","destination":"example","es":1688929250000,"groupId":"g1","isDdl":false,"old":null,"pkNames":["ORDER_ID","IN_OUT_DATE"],"sql":"","table":"tbl_ps_order_in","ts":1688929299104,"type":"INSERT"}2023-07-10 03:01:39.107 [pool-2-thread-1] DEBUG c.a.o.canal.client.adapter.es.core.service.ESSyncService - DML: {"data":[{"ORDER_ID":"4444","SETTLE_DATE_LOC":"20200329","TRANS_DATE":"20200329","TRANS_TIME":"165440","USER_ID":null,"SPACE_NO":null,"SPACE_NAME":null,"PARK_NO":"JC01331","PARK_NAME":"DELETED","CARNO":"滬A666666","CAR_IN_SEQ":"20200329165438071000000000785843","CAR_OUT_SEQ":null,"START_TIME":"20181126103000","END_TIME":null,"LENGTH":null,"OVER_VALID_TIME":null,"LEAVE_TIME":null,"STATUS":"03","PAY_STYLE":null,"TRANS_AMT":"0","PRE_AMT":"0","BALANCE":"0","FAV_AMT":"0","RET_AMT":"0","RET_STATUS":null,"SETTLE_STATUS":null,"SETTLE_DATE":null,"SETTLE_SEQ":null,"SETTLE_AMT":null,"REC_CRT_TIME":"20200329165440","REC_UPD_TIME":"20200329165440","OPR_FLAG":"0","RESVD1":null,"RESVD2":"11111111111111111111111111111111","IF_HOLD":"0","LAST_UPD_TIME":null,"LAST_UPD_RESV":null,"RESVD3":null,"RESVD4":null,"RESVD5":null,"RESVD6":null,"IN_TYPE":"00","IF_RENT":null,"ORG_ID":"0","MCHNT_NO":"1000058","MCHNT_NAME":"捷順總部大廈停車場","IN_OUT_DATE":"2018-11-26","LEAVE_DATE":null,"IF_SCORE":"0","EXCEP_TYPE":null,"EXCEP_DATE":null,"EXCEP_TIME":null,"EXCEP_STATUS":null,"IF_PAYBACK":null,"DEVICE_TYPE":"112","SUB_DEVICE_TYPE":"r"}],"database":"cstc245_test","destination":"example","es":1688929250000,"groupId":"g1","isDdl":false,"old":null,"pkNames":["ORDER_ID","IN_OUT_DATE"],"sql":"","table":"tbl_ps_order_in","ts":1688929299104,"type":"INSERT"} Affected indexes: tbl_ps_order_in 2023-07-10 03:01:39.116 [pool-2-thread-1] ERROR c.a.otter.canal.adapter.launcher.loader.AdapterProcessor - ES sync commit error ElasticsearchException[Elasticsearch exception [type=mapper_parsing_exception, reason=failed to parse field [START_TIME] of type [date] in document with id '4444'. Preview of field's value: '2018-11-26 10:30:00']]; nested: ElasticsearchException[Elasticsearch exception [type=illegal_argument_exception, reason=failed to parse date field [2018-11-26 10:30:00] with format [strict_date_optional_time||epoch_millis]]]; nested: ElasticsearchException[Elasticsearch exception [type=date_time_parse_exception, reason=Failed to parse with all enclosed parsers]];java.lang.RuntimeException: ES sync commit error ElasticsearchException[Elasticsearch exception [type=mapper_parsing_exception, reason=failed to parse field [START_TIME] of type [date] in document with id '4444'. Preview of field's value: '2018-11-26 10:30:00']]; nested: ElasticsearchException[Elasticsearch exception [type=illegal_argument_exception, reason=failed to parse date field [2018-11-26 10:30:00] with format [strict_date_optional_time||epoch_millis]]]; nested: ElasticsearchException[Elasticsearch exception [type=date_time_parse_exception, reason=Failed to parse with all enclosed parsers]]; at com.alibaba.otter.canal.client.adapter.es7x.support.ESConnection$ES7xBulkResponse.processFailBulkResponse(ESConnection.java:470) ~[na:na] at com.alibaba.otter.canal.client.adapter.es7x.support.ES7xTemplate.commit(ES7xTemplate.java:175) ~[na:na] at com.alibaba.otter.canal.client.adapter.es.core.service.ESSyncService.commit(ESSyncService.java:873) ~[na:na] at com.alibaba.otter.canal.client.adapter.es.core.ESAdapter.sync(ESAdapter.java:86) ~[na:na] at com.alibaba.otter.canal.client.adapter.ProxyOuterAdapter.sync(ProxyOuterAdapter.java:42) ~[client-adapter.common-1.1.7-SNAPSHOT.jar:na] at com.alibaba.otter.canal.adapter.launcher.loader.AdapterProcessor.batchSync(AdapterProcessor.java:139) ~[client-adapter.launcher-1.1.7-SNAPSHOT.jar:na] at com.alibaba.otter.canal.adapter.launcher.loader.AdapterProcessor.lambda$null$1(AdapterProcessor.java:97) ~[client-adapter.launcher-1.1.7-SNAPSHOT.jar:na] at java.util.concurrent.CopyOnWriteArrayList.forEach(CopyOnWriteArrayList.java:895) ~[na:1.8.0_262] at com.alibaba.otter.canal.adapter.launcher.loader.AdapterProcessor.lambda$null$2(AdapterProcessor.java:94) ~[client-adapter.launcher-1.1.7-SNAPSHOT.jar:na] at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[na:1.8.0_262] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[na:1.8.0_262] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[na:1.8.0_262] at java.lang.Thread.run(Thread.java:748) ~[na:1.8.0_262]2023-07-10 03:01:39.116 [Thread-4] ERROR c.a.otter.canal.adapter.launcher.loader.AdapterProcessor - Outer adapter sync failed! Error sync and rollback, execute times: 17^C

根據錯誤日志，似乎是Elasticsearch無法解析日期字段START_TIME。這個字段的格式是YYYY-MM-DD HH:MM:SS，而Elasticsearch期望的格式是strict_date_optional_time||epoch_millis。

strict_date_optional_time格式的例子是2015-01-01T12:10:30Z，而epoch_millis則是自1970-01-01 00:00:00 UTC以來的毫秒數。錯誤提示的意思是Elasticsearch試圖使用這兩種格式解析START_TIME字段，但是都失敗了。

從這個錯誤信息來看，你可能需要將MySQL中的日期時間數據轉換為Elasticsearch可以接受的格式。你可以嘗試將日期時間數據轉換為ISO 8601格式，或者轉換為epoch_millis格式。

如果你有權限更改Elasticsearch索引的映射，你也可以更改START_TIME字段的類型為text，這樣Elasticsearch就會將其當作普通字符串處理，而不會嘗試將其解析為日期。

這種情況下，你可能需要更改日期字段的處理方式，你可以在SQL查詢中使用UNIX_TIMESTAMP()函數將日期時間轉換為epoch格式，或者在數據同步到Elasticsearch之前進行格式轉換。具體解決方案需要根據你的實際情況來確定。

提問： 試了沒用?。?！

將包含 'date' 的字段使用 DATE_FORMAT 轉換，只需要年月日就行，包含time的字段需要年月日時分秒并且嘗試將日期時間數據轉換為ISO 8601格式SELECT a.ORDER_ID as _id, a.ORDER_ID, a.SETTLE_DATE_LOC, a.TRANS_DATE, a.TRANS_TIME, a.USER_ID, a.SPACE_NO, a.SPACE_NAME, a.PARK_NO, a.PARK_NAME, a.CARNO, a.CAR_IN_SEQ, a.CAR_OUT_SEQ, a.START_TIME, a.END_TIME, a.LENGTH, a.OVER_VALID_TIME, a.LEAVE_TIME, a.STATUS, a.PAY_STYLE, a.TRANS_AMT, a.PRE_AMT, a.BALANCE, a.FAV_AMT, a.RET_AMT, a.RET_STATUS, a.SETTLE_STATUS, a.SETTLE_DATE, a.SETTLE_SEQ, a.SETTLE_AMT, a.REC_CRT_TIME, a.REC_UPD_TIME, a.OPR_FLAG, a.RESVD1, a.RESVD2, a.IF_HOLD, a.LAST_UPD_TIME, a.LAST_UPD_RESV, a.RESVD3, a.RESVD4, a.RESVD5, a.RESVD6, a.IN_TYPE, a.IF_RENT, a.ORG_ID, a.MCHNT_NO, a.MCHNT_NAME, a.IN_OUT_DATE, a.LEAVE_DATE, a.IF_SCORE, a.EXCEP_TYPE, a.EXCEP_DATE, a.EXCEP_TIME, a.EXCEP_STATUS, a.IF_PAYBACK, a.DEVICE_TYPE, a.SUB_DEVICE_TYPEFROM tbl_ps_order_in a

回答

你想將查詢中包含 ‘date’ 的字段用 DATE_FORMAT 轉換為僅包含年月日的格式，同時，包含 ‘time’ 的字段需要年月日時分秒，而且要將日期時間數據轉換為ISO 8601格式。以下是你可以使用的SQL語句：

SELECT a.ORDER_ID as _id, a.ORDER_ID, DATE_FORMAT(a.SETTLE_DATE_LOC, '%Y-%m-%d') as SETTLE_DATE_LOC, DATE_FORMAT(a.TRANS_DATE, '%Y-%m-%d') as TRANS_DATE, DATE_FORMAT(a.TRANS_TIME, '%Y-%m-%dT%H:%i:%s') as TRANS_TIME, a.USER_ID, a.SPACE_NO, a.SPACE_NAME, a.PARK_NO, a.PARK_NAME, a.CARNO, a.CAR_IN_SEQ, a.CAR_OUT_SEQ, DATE_FORMAT(a.START_TIME, '%Y-%m-%dT%H:%i:%s') as START_TIME, DATE_FORMAT(a.END_TIME, '%Y-%m-%dT%H:%i:%s') as END_TIME, a.LENGTH, a.OVER_VALID_TIME, DATE_FORMAT(a.LEAVE_TIME, '%Y-%m-%dT%H:%i:%s') as LEAVE_TIME, a.STATUS, a.PAY_STYLE, a.TRANS_AMT, a.PRE_AMT, a.BALANCE, a.FAV_AMT, a.RET_AMT, a.RET_STATUS, a.SETTLE_STATUS, DATE_FORMAT(a.SETTLE_DATE, '%Y-%m-%d') as SETTLE_DATE, a.SETTLE_SEQ, a.SETTLE_AMT, DATE_FORMAT(a.REC_CRT_TIME, '%Y-%m-%dT%H:%i:%s') as REC_CRT_TIME, DATE_FORMAT(a.REC_UPD_TIME, '%Y-%m-%dT%H:%i:%s') as REC_UPD_TIME, a.OPR_FLAG, a.RESVD1, a.RESVD2, a.IF_HOLD, DATE_FORMAT(a.LAST_UPD_TIME, '%Y-%m-%dT%H:%i:%s') as LAST_UPD_TIME, a.LAST_UPD_RESV, a.RESVD3, a.RESVD4, a.RESVD5, a.RESVD6, a.IN_TYPE, a.IF_RENT, a.ORG_ID, a.MCHNT_NO, a.MCHNT_NAME, DATE_FORMAT(a.IN_OUT_DATE, '%Y-%m-%d') as IN_OUT_DATE, DATE_FORMAT(a.LEAVE_DATE, '%Y-%m-%d') as LEAVE_DATE, a.IF_SCORE, a.EXCEP_TYPE, DATE_FORMAT(a.EXCEP_DATE, '%Y-%m-%d') as EXCEP_DATE, DATE_FORMAT(a.EXCEP_TIME, '%Y-%m-%dT%H:%i:%s') as EXCEP_TIME, a.EXCEP_STATUS, a.IF_PAYBACK, a.DEVICE_TYPE, a.SUB_DEVICE_TYPEFROM tbl_ps_order_in a

注意，這個 SQL 查詢是針對 MySQL 數據庫的，假設所有的時間字段都是以字符串形式存儲，如果字段的存儲形式不同，這個查詢可能需要做相應的調整。此外，‘%Y-%m-%dT%H:%i:%s’ 是 ISO 8601 時間格式的 MySQL 版本，如果你的數據庫不支持這種格式，你可能需要在應用層面進行處理。

這個貌似解決不了?。。。?！

下面是重頭戲

你可以使用UNIX_TIMESTAMP()函數將日期和時間字段轉換為epoch格式。這個函數將日期和時間的值轉換為自1970-01-01 00:00:00 UTC以來的秒數。以下是你的SQL查詢的一部分，展示了如何進行這種轉換：

SELECT a.ORDER_ID as _id, a.ORDER_ID, UNIX_TIMESTAMP(a.SETTLE_DATE_LOC) as SETTLE_DATE_LOC, UNIX_TIMESTAMP(a.TRANS_DATE) as TRANS_DATE, UNIX_TIMESTAMP(STR_TO_DATE(a.TRANS_TIME, '%Y-%m-%d %H:%i:%s')) as TRANS_TIME, a.USER_ID, ... UNIX_TIMESTAMP(STR_TO_DATE(a.START_TIME, '%Y-%m-%d %H:%i:%s')) as START_TIME, UNIX_TIMESTAMP(STR_TO_DATE(a.END_TIME, '%Y-%m-%d %H:%i:%s')) as END_TIME, ... UNIX_TIMESTAMP(a.SETTLE_DATE) as SETTLE_DATE, ... UNIX_TIMESTAMP(STR_TO_DATE(a.REC_CRT_TIME, '%Y-%m-%d %H:%i:%s')) as REC_CRT_TIME, UNIX_TIMESTAMP(STR_TO_DATE(a.REC_UPD_TIME, '%Y-%m-%d %H:%i:%s')) as REC_UPD_TIME, ... UNIX_TIMESTAMP(STR_TO_DATE(a.LAST_UPD_TIME, '%Y-%m-%d %H:%i:%s')) as LAST_UPD_TIME, ... UNIX_TIMESTAMP(a.IN_OUT_DATE) as IN_OUT_DATE, UNIX_TIMESTAMP(a.LEAVE_DATE) as LEAVE_DATE, ... UNIX_TIMESTAMP(a.EXCEP_DATE) as EXCEP_DATE, UNIX_TIMESTAMP(STR_TO_DATE(a.EXCEP_TIME, '%Y-%m-%d %H:%i:%s')) as EXCEP_TIME, ...FROM tbl_ps_order_in a

這里，我將所有的日期時間字段都用UNIX_TIMESTAMP()函數進行了處理。對于時間字段（如TRANS_TIME、START_TIME等），我還使用了STR_TO_DATE()函數，將其從字符串格式轉換為日期時間格式，然后再調用UNIX_TIMESTAMP()。

注意，UNIX_TIMESTAMP()函數返回的是秒數，而Elasticsearch的epoch_millis格式是毫秒數。如果你需要毫秒數，你可以將UNIX_TIMESTAMP()函數的結果乘以1000。

以上的方案適用于MySQL。如果你的數據庫是其他類型，可能需要用不同的方法來進行轉換。

**提問：**試了有用！?。?！

SELECT a.ORDER_ID as _id, a.ORDER_ID, a.SETTLE_DATE_LOC, a.TRANS_DATE, a.TRANS_TIME, a.USER_ID, a.SPACE_NO, a.SPACE_NAME, a.PARK_NO, a.PARK_NAME, a.CARNO, a.CAR_IN_SEQ, a.CAR_OUT_SEQ, a.START_TIME, a.END_TIME, a.LENGTH, a.OVER_VALID_TIME, a.LEAVE_TIME, a.STATUS, a.PAY_STYLE, a.TRANS_AMT, a.PRE_AMT, a.BALANCE, a.FAV_AMT, a.RET_AMT, a.RET_STATUS, a.SETTLE_STATUS, a.SETTLE_DATE, a.SETTLE_SEQ, a.SETTLE_AMT, a.REC_CRT_TIME, a.REC_UPD_TIME, a.OPR_FLAG, a.RESVD1, a.RESVD2, a.IF_HOLD, a.LAST_UPD_TIME, a.LAST_UPD_RESV, a.RESVD3, a.RESVD4, a.RESVD5, a.RESVD6, a.IN_TYPE, a.IF_RENT, a.ORG_ID, a.MCHNT_NO, a.MCHNT_NAME, a.IN_OUT_DATE, a.LEAVE_DATE, a.IF_SCORE, a.EXCEP_TYPE, a.EXCEP_DATE, a.EXCEP_TIME, a.EXCEP_STATUS, a.IF_PAYBACK, a.DEVICE_TYPE, a.SUB_DEVICE_TYPEFROM tbl_ps_order_in a將包含 'date' 的字段使用 DATE_FORMAT 轉換，只需要年月日就行，包含time的字段需要格式化成年月日時分秒，并且用UNIX_TIMESTAMP()函數進行處理，然后還要將UNIX_TIMESTAMP()函數的結果乘以1000

回答

根據您的需求，我將對包含 ‘date’ 的字段使用 DATE_FORMAT 進行年月日的格式化，對包含 ‘time’ 的字段，先將其格式化為年月日時分秒，然后使用 UNIX_TIMESTAMP() 函數處理，最后將結果乘以1000。以下是更新后的 SQL 查詢：

SELECT a.ORDER_ID as _id, a.ORDER_ID, DATE_FORMAT(a.SETTLE_DATE_LOC, '%Y-%m-%d') as SETTLE_DATE_LOC, DATE_FORMAT(a.TRANS_DATE, '%Y-%m-%d') as TRANS_DATE, UNIX_TIMESTAMP(DATE_FORMAT(a.TRANS_TIME, '%Y-%m-%d %H:%i:%s')) * 1000 as TRANS_TIME, a.USER_ID, a.SPACE_NO, a.SPACE_NAME, a.PARK_NO, a.PARK_NAME, a.CARNO, a.CAR_IN_SEQ, a.CAR_OUT_SEQ, UNIX_TIMESTAMP(DATE_FORMAT(a.START_TIME, '%Y-%m-%d %H:%i:%s')) * 1000 as START_TIME, UNIX_TIMESTAMP(DATE_FORMAT(a.END_TIME, '%Y-%m-%d %H:%i:%s')) * 1000 as END_TIME, a.LENGTH, a.OVER_VALID_TIME, UNIX_TIMESTAMP(DATE_FORMAT(a.LEAVE_TIME, '%Y-%m-%d %H:%i:%s')) * 1000 as LEAVE_TIME, a.STATUS, a.PAY_STYLE, a.TRANS_AMT, a.PRE_AMT, a.BALANCE, a.FAV_AMT, a.RET_AMT, a.RET_STATUS, a.SETTLE_STATUS, DATE_FORMAT(a.SETTLE_DATE, '%Y-%m-%d') as SETTLE_DATE, a.SETTLE_SEQ, a.SETTLE_AMT, UNIX_TIMESTAMP(DATE_FORMAT(a.REC_CRT_TIME, '%Y-%m-%d %H:%i:%s')) * 1000 as REC_CRT_TIME, UNIX_TIMESTAMP(DATE_FORMAT(a.REC_UPD_TIME, '%Y-%m-%d %H:%i:%s')) * 1000 as REC_UPD_TIME, a.OPR_FLAG, a.RESVD1, a.RESVD2, a.IF_HOLD, UNIX_TIMESTAMP(DATE_FORMAT(a.LAST_UPD_TIME, '%Y-%m-%d %H:%i:%s')) * 1000 as LAST_UPD_TIME, a.LAST_UPD_RESV, a.RESVD3, a.RESVD4, a.RESVD5, a.RESVD6, a.IN_TYPE, a.IF_RENT, a.ORG_ID, a.MCHNT_NO, a.MCHNT_NAME, DATE_FORMAT(a.IN_OUT_DATE, '%Y-%m-%d') as IN_OUT_DATE, DATE_FORMAT(a.LEAVE_DATE, '%Y-%m-%d') as LEAVE_DATE, a.IF_SCORE, a.EXCEP_TYPE, DATE_FORMAT(a.EXCEP_DATE, '%Y-%m-%d') as EXCEP_DATE, UNIX_TIMESTAMP(DATE_FORMAT(a.EXCEP_TIME, '%Y-%m-%d %H:%i:%s')) * 1000 as EXCEP_TIME, a.EXCEP_STATUS, a.IF_PAYBACK, a.DEVICE_TYPE, a.SUB_DEVICE_TYPEFROM tbl_ps_order_in a

注意：上述查詢是在假設所有包含 ‘time’ 的字段都是 TIMESTAMP 類型，可以被轉換為 ‘%Y-%m-%d %H:%i:%s’ 這種格式。如果不是這種類型，可能需要進行一些調整。

到此這篇關于Mysql同步到ES時date和time字段類型轉換問題解決辦法的文章就介紹到這了,更多相關mysql同步到ES內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！