SQL to Elasticsearch Java code

Use Elasticsearch as database, because Elasticsearch does not support SQL, you need to convert SQL into code implementation.

1. Query count by a field group by

SELECTCOUNT (fielda) from table WHERE =        and =and >  <  - Group  by Fielda;

The corresponding Java code:

Searchrequestbuilder searchreq = Client.preparesearch ("Sample_index"); Searchreq.settypes ("Sample_types"); Termsbuilder TERMSB= Aggregationbuilders.terms ("My_fielda"). Field ("Fielda"). Size (100); Boolfilterbuilder BF=Filterbuilders.boolfilter (); Termfilterbuilder TF_FIELDC= Filterbuilders.termfilter ("Fieldc", "Hoge"); Termfilterbuilder tf_fieldd= Filterbuilders.termfilter ("Fieldd", "Huga"); Bf.must (TF_FIELDC); Bf.must (TF_FIELDD); Rangefilterbuilder Rangefieldbfilter= Filterbuilders.rangefilter ("Fieldb"). GT (10). LT (100); Searchreq.setquery (Querybuilders.filteredquery (Querybuilders.matchallquery (), FILTERBUILDERS.ANDF Ilter (BF, Rangefieldbfilter)). Addaggregation (TERMSB); SearchResponse Searchres=Searchreq.execute (). Actionget (); Terms fieldaterms= Searchres.getaggregations (). Get ("My_fielda"); for(Terms.bucket filedABucket:fieldATerms.getBuckets ()) {//FieldaString Fieldavalue =Filedabucket.getkey (); //COUNT (Fielda)    LongFieldacount =filedabucket.getdoccount ();}

2. Press a field and date group by and query the sum, time chart, time interval is 1 days.

SELECTSUM fromtableGroup by      DATE (CREATE_AT), Fielda;

The corresponding Java code:

Searchrequestbuilder searchreq = Client.preparesearch ("Sample_index"); Searchreq.settypes ("Sample_types");D Atehistogrambuilder DHB= Aggregationbuilders.datehistogram ("My_datehistogram"). Field ("Create_at"). Interval (DateHistogram.Interval.days (1)); Termsbuilder Termsb_fa= Aggregationbuilders.terms ("My_fielda"). Field ("Fielda"). Size (100); Termsb_fa.subaggregation (Aggregationbuilders.sum ("My_sum_fieldb"). Field ("Fieldb")) );d hb.subaggregation (TERMSB_FA) searchreq.setquery (Querybuilders.matchallquery ()). Addaggregation (DHB); SearchResponse Searchres=Searchreq.execute (). Actionget ();D atehistogram datehist= Searchres.getaggregations (). Get ("My_datehistogram"); for(Datehistogram.bucket dateBucket:dateHist.getBuckets ()) {//DATE (CREATE_AT)String Create_at =Dateentry.getkey (); Terms fieldaterms= Datebucket.getaggregations (). Get ("My_fielda");  for(Terms.bucket filedABucket:fieldATerms.getBuckets ()) {//FieldaString Fieldavalue =Filedabucket.getkey (); //SUM (FIELDB)Sum Sumagg = Filedabucket.getaggregations (). Get ("My_sum_fieldb"); LongSumfieldb = (Long) sumagg.getvalues (); }}

3. Press two field group by and query sum

SELECTSUM (fieldb) from table group       by Fielda, FIELDC;

The corresponding Java code:

Searchrequestbuilder searchreq = Client.preparesearch ("Sample_index"); Searchreq.settypes ("Sample_types"); Termsbuilder Termsb_fa= Aggregationbuilders.terms ("My_fielda"). Field ("Fielda"). Size (100); Termsbuilder Termsb_fc= Aggregationbuilders.terms ("MY_FIELDC"). Field ("FIELDC"). Size (50); Termsb_fc.subaggregation (Aggregationbuilders.sum ("My_sum_fieldb"). Field ("Fieldb")) ); Termsb_fa.subaggregation (TERMSB_FC) searchreq.setquery (Querybuilders.matchallquery ()). AddAggregation ( TERMSB_FA); SearchResponse Searchres=Searchreq.execute (). Actionget (); Terms fieldaterms= Searchres.getaggregations (). Get ("My_fielda"); for(Terms.bucket filedABucket:fieldATerms.getBuckets ()) {//FieldaString Fieldavalue =Filedabucket.getkey (); Terms fieldcterms= Filedabucket.getaggregations (). Get ("MY_FIELDC");  for(Terms.bucket filedCBucket:fieldCTerms.getBuckets ()) {//FIELDCString Fieldcvalue =Filedcbucket.getkey (); //SUM (FIELDB)Sum Sumagg = Filedcbucket.getaggregations (). Get ("My_sum_fieldb"); LongSumfieldb = (Long) sumagg.getvalues (); }}

4. Press a filed group by and query the count sum and average

SELECT COUNT SUM AVG from table     Group by Fielda;

The corresponding Java code:

Searchrequestbuilder searchreq = Client.preparesearch ("Sample_index"); Searchreq.settypes ("Sample_types"); Termsbuilder TERMSB= Aggregationbuilders.terms ("My_fielda"). Field ("Fielda"). Size (100); Termsb.subaggregation (Aggregationbuilders.sum ("My_sum_fieldb"). Field ("Fieldb"))); Termsb.subaggregation (Aggregationbuilders.avg ("My_avg_fieldb"). Field ("Fieldb")) ; Searchreq.setquery (Querybuilders.matchallquery ()). Addaggregation (TERMSB); SearchResponse Searchres=Searchreq.execute (). Actionget (); Terms fieldaterms= Searchres.getaggregations (). Get ("My_fielda"); for(Terms.bucket filedABucket:fieldATerms.getBuckets ()) {//FieldaString Fieldavalue =Filedabucket.getkey (); //COUNT (Fielda)    LongFieldacount =Filedabucket.getdoccount (); //SUM (FIELDB)Sum Sumagg = Filedabucket.getaggregations (). Get ("My_sum_fieldb"); LongSumfieldb = (Long) sumagg.getvalues (); //AVG (FIELDB)AVG Avgagg = Filedabucket.getaggregations (). Get ("My_avg_fieldb"); DoubleAvgfieldb =avgagg.getvalues ();}

SQL to Elasticsearch Java code