trino关于查询elasticSearch中时间字段的问题-Toy模板网

这篇具有很好参考价值的文章主要介绍了trino关于查询elasticSearch中时间字段的问题。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

查看官方文档：Elasticsearch connector — Trino 395 Documentation

trino es,java,java,elasticsearch,es

这段话的意思就是，elasticSearch支持很多时间类型的格式化方式，但是trino中es连接器只支持默认的date类型，就是不能加时间格式化

创建 yc_test_data 索引，索引中包含两个属性create_time 和 name

PUT http://10.226.21.38:9200/yc_test_data

{
	"mappings": {
		"properties": {
			"create_time": {
				"type": "date",
				"format": "yyyy-MM-dd"
			},
			"name": {
				"type": "keyword"
			}
		}
	}
}

在索引中添加文档

POST http://10.226.21.38:9200/yc_test_data/_doc

{
  "create_time":"2022-09-15",
  "name":"a"
}

查看索引中的文档

GET http://10.226.21.38:9200/yc_test_data/_search

{

}

response:

{
    "took": 1,
    "timed_out": false,
    "_shards": {
        "total": 1,
        "successful": 1,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": {
            "value": 1,
            "relation": "eq"
        },
        "max_score": 1.0,
        "hits": [
            {
                "_index": "yc_test_data",
                "_type": "_doc",
                "_id": "gO12QIMBCEJMLwqlLGYV",
                "_score": 1.0,
                "_source": {
                    "create_time": "2022-09-15",
                    "name": "a"
                }
            }
        ]
    }
}

用trino查询

trino es,java,java,elasticsearch,es

注意看，带有格式化的时间字段create_time没有查询出来

如果索引yc_test_data的mapping改成这样，则用trino可以查询出来

{
	"mappings": {
		"properties": {
			"create_time": {
				"type": "date"
			},
			"name": {
				"type": "keyword"
			}
		}
	}
}

POST http://10.226.21.38:9200/yc_test_data/_doc

{
  "create_time":"2022-09-15",
  "name":"a"
}
{
  "create_time":"2015-01-01T12:10:30Z",
  "name":"b"
}
{
  "create_time":"1663233358000",
  "name":"c"
}

GET http://10.226.21.38:9200/yc_test_data/_search

{
    "took": 279,
    "timed_out": false,
    "_shards": {
        "total": 1,
        "successful": 1,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": {
            "value": 3,
            "relation": "eq"
        },
        "max_score": 1.0,
        "hits": [
            {
                "_index": "yc_test_data",
                "_type": "_doc",
                "_id": "ue1_QIMBCEJMLwqlZGhW",
                "_score": 1.0,
                "_source": {
                    "create_time": "2022-09-15",
                    "name": "a"
                }
            },
            {
                "_index": "yc_test_data",
                "_type": "_doc",
                "_id": "7O2AQIMBCEJMLwqlPmgp",
                "_score": 1.0,
                "_source": {
                    "create_time": "2015-01-01T12:10:30Z",
                    "name": "b"
                }
            },
            {
                "_index": "yc_test_data",
                "_type": "_doc",
                "_id": "CO2AQIMBCEJMLwqlvGkA",
                "_score": 1.0,
                "_source": {
                    "create_time": "1663233358000",
                    "name": "c"
                }
            }
        ]
    }
}

用trino查询:

trino es,java,java,elasticsearch,es