How to remove default "LIMIT 100" in SQL Console?

CrateDB
1

I notice every query is limited to 100 results in the Admin UI. I didn’t see anything in the SQL Console docs about this limit. Is there a way to remove the limit? Or set it to something higher?

Thanks.

2

you can just manually set it to a higher number by just adding another LIMIT to your query
It is basically a safety measure so that you don’t SELECT your complete multi-billion-row table :wink:

3

Thanks!

For the record, querying a SELECT DISTINCT on the billion row table is pretty quick:

SELECT OK, 241 rows in set (4.681 sec)
1 Like
4

might also want to try hyperloglog_distinct

https://crate.io/docs/crate/reference/en/4.4/general/builtins/aggregation.html#hyperloglog-distinct

5

It is not faster:

select hyperloglog_distinct(servername) from billion_row_table limit 1000;

SELECT OK, 1 row in set (69.863 sec)

same count in the end, but i needed the actual results, not just the count.

although, it at least returns a result, as compared to:

select COUNT(DISTINCT servername) from billion_row_table limit 1000;



Error!

CircuitBreakingException[[query] Data too large, data for [collect: 0] would be [19327877120/18gb], which is larger than the limit of [19327352832/18gb]]

Thanks!

1 Like
6

right … maybe something that could be optimized :thinking: