CrateDB uses its very own execution engine so the requests will be executed completely different. In order to make that more clear and avoid confusions (CrateDB is not on top of ES), the ES API shipped with CrateDB was deprecated with v3.1.0 and removed by v4.0.0.
Specially on how to execute aggregations, ES is very different from what CrateDB does.
Which makes the main difference in your case it that the CrateDB SQL is:
- doing a additional aggregation on the
hotel_code field while the ES request isn’t
- is returning all rows (limit 100) while the ES request is returning 0 (size: 0).
- it will aggregate all found
year terms while ES’s
terms aggregation will return only the top ten terms ordered by doc_count.
Beside of that, CrateDB’s execution engine it optimized for distributed execution, so adding nodes will increase performance a lot (which may not completely the same for ES aggregations).