ElasticSearch date range query aggregation using Java API

Question

Hi I have the document for cpu usage with date_time field inside it. Now I would like to find avg cpu usage for the date range. I have come up with the following solution. Please let me know if there are any advance or better approach as I am new to Elastic Search.

client.prepareSearch("myindex").
       setTypes("mytype").
       setQuery(
           QueryBuilders.filteredQuery(QueryBuilders.matchAllQuery(),
           FilterBuilders.andFilter(FilterBuilders.termFilter("server","x"),
           FilterBuilders.rangeFilter(date_time).from(fdate).to(tdate)))).get()

Now above query returns me as expected documents which falls within from/to date range. Now what I try to do is I find all unique dates from these documents using SearchHitsand I store this unique combinations of dates in a HashSet and now for all items inside this HashSet I execute the following query

client.prepareSearch("myindex").
       setTypes("mytype").
       setQuery(
           QueryBuilders.filteredQuery(QueryBuilders.matchAllQuery(),
           FilterBuilders.andFilter(FilterBuilders.termFilter("server","x"),
           FilterBuilders.termFilter(date_time),"dateinputfromloop"))).
       addAggregation(AggregationBuilders.avg("cpu_agg").field("cpu_time"))
       .get()

Now above query works fine and gives output I get avg CPU for each date time combination. I was wondering if these is any better approach as I execute above query in a loop for all date combinations. Please guide thanks in advance.

So, how is your date is stored? I mean is it mm/dd/yy or with time, How do you find if date is unique? — progrrammer
– progrrammer, Commented Aug 10, 2014 at 14:44
Hi thanks for the reply. Date is stored in ISO date format. There can be similar/duplicate date per document so I am storing in HashSet and then once I have unique date combinations one by one I am firing query to get avg cpu for each date. — Umesh Kacha
– Umesh Kacha, Commented Aug 10, 2014 at 15:10

progrrammer · Accepted Answer · 2014-08-10 16:30:55Z

So, I think you can have data for daily, You can use date_histogram aggregation for this. So, that your two request can be done in single request.

Here is code,

client.prepareSearch("myindex").
                setTypes("mytype").
                setQuery(
                        QueryBuilders.filteredQuery(QueryBuilders.matchAllQuery(),
                                FilterBuilders.andFilter(FilterBuilders.termFilter("server","x"),
                                        FilterBuilders.rangeFilter("date_time").from("fdate").to("tdate")))).
                addAggregation(
                        AggregationBuilders.dateHistogram("dateagg").field("date_time").interval(DateHistogram.Interval.DAY)
                                .subAggregation(
                                AggregationBuilders.avg("cpu_agg").field("cpu_time")
                        )
                )
                .get();

You can change interval in dateHistogram aggregation to fit your need.

If you want to be based on unique (in millisecond also), then you can use terms aggregation for date instead of date histogram aggregation.

Terms Aggregation

A multi-bucket value source based aggregation where buckets are dynamically built - one per unique value.

Hope this helps, Thanks.

Thanks a lot for the answer. So using your solution I dont need to have a loop for each date combination this one query will do all job. My date format looks like 2014-07-25T10:25:00:000.Z and it can have combination in terms of seconds or mili second difference. I have to find avg for all of these combinations will date histogram do it for me.
I think it will work, It depends on how do you group dates or how you consider unique. i.e, if two events occured in gap of 1 millisecond, is it different?? if it is, then you can change interval =1 , so millisecond is supported (have to test). Just change the interval, every time format is supported.
Hi it worked amazingly. Just one query gives me sorted output. I wish I could give you +10!

Collectives™ on Stack Overflow

ElasticSearch date range query aggregation using Java API

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related