How to use group by date_histogram(field='timestamp','interval'='1m') in latest version?

opendistro-for-elasticsearch / sql

🔍 Open Distro SQL Plugin

https://opendistro.github.io/for-elasticsearch/features/SQL%20Support.html

Apache License 2.0

620 stars 186 forks source link

How to use group by date_histogram(field='timestamp','interval'='1m') in latest version? #530

Open yangjiajun2014 opened 4 years ago

yangjiajun2014 commented 4 years ago

It looks like date_histogram(field='timestamp','interval'='1m') has been changed in latest version.What is the right syntax?

chloe-zh commented 4 years ago

Hi @yangjiajun2014 , the default output format is changed to JDBC format since v1.4.0 (from previous raw output from ES engine of JSON format), and histogram function is not supported in JDBC format. But it still works for JSON format by adding the format param at the request endpoint _opendistro/_sql?format=json. If you prefer the JSON format or other formats as default, please refer to the user manual: https://github.com/opendistro-for-elasticsearch/sql/blob/master/docs/user/admin/settings.rst#opendistro-sql-query-response-format

yangjiajun2014 commented 4 years ago

Thanks for u reply.It looks like group by key is missing in latest version while use jdbc or csv format.But in 1.7.0.0,it did not have such problems.

dai-chen commented 4 years ago

Hi @yangjiajun2014 , we fixed this issue recently in PR: https://github.com/opendistro-for-elasticsearch/sql/pull/522. Please check and let us know if any issue still. Thanks!

cliu3000 commented 3 years ago

Hi @dai-chen , we found that the group by key is still missing in v1.13.0.0 while using jdbc or csv format. The problem is as same as yangjiajun2014 issued.

dai-chen commented 3 years ago

@cliu3000 Apologize that I misunderstood this issue. It seems there is no way to SELECT key for histogram aggregation. The only workaround is to use JSON format and get original DSL response. Will add this support later. Thanks!