4 ms·

Challenge, with context in my other comment at the same depth: Suppose you have a table which includes power usage readings for all devices, sent every five se

Challenge, with context in my other comment at the same depth: Suppose you have a table which includes power usage readings for all devices, sent every five seconds. Something like this: (id, timestamp, power) Write me an ES query which gets the average power usage for device id=x over the last year over the seven days in the week, each divided over fifteen-minute intervals. To clarify: The groups will be the product of the days of the week with the ninety-six fifteen-minute intervals in a day, making 7*96 unique groups. You will not be able to do this in any simple way.

AznHisoka 8y ago
This is an indexing problem. Add 2 extra fields. For each timestamp, you also need to index the 15-minute interval (ie 7:13:05 maps to 7:00-7:15) and the day of the week. After that, do a terms aggregation on day of week with a subaggregation on 15 min interval. If you can’t reindex all your data, then of course it will be hard. But once the data is indexed and mapped the right way, its easy.
- 0db532a0 8y ago
  So you couldn’t write the query. It just isn’t practical to create an index for every query which comes to mind during research, and even to create the index, you have to use a terrible, undocumented embedded query language.
  - AznHisoka 8y ago
    Not create a new index - reindex the existing documents I have personally managed 10+ TB clusters and reindexed multiple times when I need to add new fields. The key is to take some time to clearly understand what analytics you want to conduct, then add the fields you need afterwards.
    - 0db532a0 8y ago
      Thanks for the detailed reply. I have been a bit inflammatory explaining my problem. I appreciate that for queries which are expected to be re-run, it is useful to have an appropriate index. What I see from your proposal though is that the work for building the same query is split over re-indexing plus another now slightly less complex query. What I have missed in Elasticsearch is the ease of expressing a data problem. I am also no stranger to learning new languages when needed. You might think differently, but I am of the opinion that the problem that I described translates a lot more easily into SQL. What I need in this context is to have a language and tool where it is easy to try lots of different queries in succession; to be able to get a feeling for how the data works and test hypotheses. I haven’t been able to do this in Elasticsearch. I am not saying that there’s nothing which Elasticsearch is good for, but it’s just about using the right tool for the job.

AznHisoka 8y ago

This is an indexing problem. Add 2 extra fields. For each timestamp, you also need to index the 15-minute interval (ie 7:13:05 maps to 7:00-7:15) and the day of the week. After that, do a terms aggregation on day of week with a subaggregation on 15 min interval. If you can’t reindex all your data, then of course it will be hard. But once the data is indexed and mapped the right way, its easy.

0db532a0 8y ago
So you couldn’t write the query. It just isn’t practical to create an index for every query which comes to mind during research, and even to create the index, you have to use a terrible, undocumented embedded query language.
- AznHisoka 8y ago
  Not create a new index - reindex the existing documents I have personally managed 10+ TB clusters and reindexed multiple times when I need to add new fields. The key is to take some time to clearly understand what analytics you want to conduct, then add the fields you need afterwards.
  - 0db532a0 8y ago
    Thanks for the detailed reply. I have been a bit inflammatory explaining my problem. I appreciate that for queries which are expected to be re-run, it is useful to have an appropriate index. What I see from your proposal though is that the work for building the same query is split over re-indexing plus another now slightly less complex query. What I have missed in Elasticsearch is the ease of expressing a data problem. I am also no stranger to learning new languages when needed. You might think differently, but I am of the opinion that the problem that I described translates a lot more easily into SQL. What I need in this context is to have a language and tool where it is easy to try lots of different queries in succession; to be able to get a feeling for how the data works and test hypotheses. I haven’t been able to do this in Elasticsearch. I am not saying that there’s nothing which Elasticsearch is good for, but it’s just about using the right tool for the job.