Open tenndou opened 2 years ago
Can you provide the es schema and the foreign table definition please as well as an example insert statement that fails. Thank you.
Thank you for your reply.To keep things simple, I wrote an es schema and the foreign table definition just for testing, and the effect is the same as the production ENV.I am ES beginner, please instruct, thank you very much!
ES Version 7.14.1
es schema:
PUT tst_fdw { "mappings": { "properties": { "tid": { "type": "integer" }, "tname": { "type": "text", "analyzer": "ik_smart" }, "tupd": { "type": "date", "format": "date_hour_minute_second_millis||date_time||date_optional_time" } } } }
foreign table definition:
DROP FOREIGN TABLE IF EXISTS "public"."es_tst_fdw"; CREATE FOREIGN TABLE "public"."es_tst_fdw" ( "id" BIGINT, "tid" BIGINT, "tname" varchar(100), "tupd" TIMESTAMPTZ ) SERVER "multicorn_es" OPTIONS ( "host" '192.168.1.18', "port" '9200', "index" 'tst_fdw', "rowid_column" 'id', "query_dsl" 'true', "default_sort" 'tupd', "refresh" 'false', "complete_returning" 'false', "timeout" '20', "username" 'elastic', "password" '123456' );
insert statement:
INSERT INTO "es_tst_fdw" ("id", "tid", "tname", "tupd") VALUES (11111, 11111, 'tst', '2021-01-15T01:01:01.172+08')
info:
ERROR: INDEX for /tst_fdw/11111 and document {'tid': '11111', 'tname': 'tst', 'tupd': '2021-01-15 01:01:01.172+08'} failed: RequestError(400, 'mapper_parsing_exception', "failed to parse field [tupd] of type [date] in document with id '11111'. Preview of field's value: '2021-01-15 01:01:01.172+08'")
The following is the data inserted using logstash, which is the result I want:["tupd"]
"hits" : [ { "_index" : "tst_fdw", "_type" : "_doc", "_id" : "9999", "_score" : 1.0, "_source" : { "tid" : 9999, "tname" : "中文测试9999", "tupd" : "2021-01-15T01:01:01.172+08" } } ]
The foreign Table Definition field is defined as the TIMESTAMP type and data can be inserted, but milliseconds and time zone are lost as a result.
INSERT INTO "es_tst_fdw" ("id", "tid", "tname", "tupd") VALUES (123123, 123123, '中文1', '2021-01-15T01:01:01.172+08')
"hits" : [ { "_index" : "tst_fdw", "_type" : "_doc", "_id" : "123123", "_score" : 1.0, "_source" : { "tid" : "123123", "tname" : "中文1", "tupd" : "2021-01-15T01:01:01" } } ]
[created_stamp] field is Timestamptz type, an error occurred when inserting data:
failed: RequestError(400, 'mapper_parsing_exception', "failed to parse field [created_stamp] of type [date] in document with id '1111111111'. Preview of field's value: '2018-05-28 19:00:15.359+08'")