這個(gè)問題中發(fā)生問題的es字段映射類型為date類型?。。∽址愋偷臅?huì)好很多，關(guān)鍵我這邊是通過logstash生成索引，并且全量同步的，不好改索引類型！！！

問題描述：

這個(gè)報(bào)錯(cuò)我該怎么辦呢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

根據(jù)錯(cuò)誤日志，似乎是Elasticsearch無法解析日期字段START_TIME。這個(gè)字段的格式是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以來的毫秒數(shù)。錯(cuò)誤提示的意思是Elasticsearch試圖使用這兩種格式解析START_TIME字段，但是都失敗了。

從這個(gè)錯(cuò)誤信息來看，你可能需要將MySQL中的日期時(shí)間數(shù)據(jù)轉(zhuǎn)換為Elasticsearch可以接受的格式。你可以嘗試將日期時(shí)間數(shù)據(jù)轉(zhuǎn)換為ISO 8601格式，或者轉(zhuǎn)換為epoch_millis格式。

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

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

提問： 試了沒用！！！

將包含 'date' 的字段使用 DATE_FORMAT 轉(zhuǎn)換，只需要年月日就行，包含time的字段需要年月日時(shí)分秒并且嘗試將日期時(shí)間數(shù)據(jù)轉(zhuǎn)換為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 轉(zhuǎn)換為僅包含年月日的格式，同時(shí)，包含 ‘time’ 的字段需要年月日時(shí)分秒，而且要將日期時(shí)間數(shù)據(jù)轉(zhuǎn)換為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

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

這個(gè)貌似解決不了?。。。?！

下面是重頭戲

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

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

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

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

以上的方案適用于MySQL。如果你的數(shù)據(jù)庫是其他類型，可能需要用不同的方法來進(jìn)行轉(zhuǎn)換。

**提問：**試了有用！！?。?/p>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 轉(zhuǎn)換，只需要年月日就行，包含time的字段需要格式化成年月日時(shí)分秒，并且用UNIX_TIMESTAMP()函數(shù)進(jìn)行處理，然后還要將UNIX_TIMESTAMP()函數(shù)的結(jié)果乘以1000

回答

根據(jù)您的需求，我將對包含 ‘date’ 的字段使用 DATE_FORMAT 進(jìn)行年月日的格式化，對包含 ‘time’ 的字段，先將其格式化為年月日時(shí)分秒，然后使用 UNIX_TIMESTAMP() 函數(shù)處理，最后將結(jié)果乘以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

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

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