I am not able to extract more than 1000 records in canvas Elasticsearch SQL query

[anish_jha (anish)]

I am using this query .

SELECT transaction_id FROM "dev__event*" limit 10000

Still i am getting only 1000 records , can anyone please help me on this.

[wylie (Wylie Conlon)]

Yes, this is a limitation/bug in the way Canvas is consuming ES SQL. https://github.com/elastic/kibana/issues/51191

It could also highlight a logical issue- why do you need so many IDs to be loaded into Canvas? How will they be visualized?

[anish_jha (anish)]

Actually i need to show the number of transaction per 24 hours/week/month . I am using the count operation on transaction _id so i will get the exact count of transaction . is there any other way to do that ?

[wylie (Wylie Conlon)]

Yes, try this: SELECT HISTOGRAM("@timestamp", INTERVAL 24 HOURS), COUNT(*) FROM myindex GROUP BY HISTOGRAM("@timestamp", INTERVAL 24 HOURS)

[anish_jha (anish)]

@wylie

HISTOGRAM

Sorry, I didn't get what this query means ,Can you please explain , as i am using time filter element in canvas do i still need to pass @timestamp in es SQL.

[wylie (Wylie Conlon)]

Try it out- you will see that the filters are applied in addition to SQL. The histogram function will apply a date histogram aggregation. https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-functions-grouping.html

[anish_jha (anish)]

Thank you, your help is much appreciated.