ElasticSearch5.6.3之java查询

最近刚接触ElasticSearch,记录一下相关的用法：

一、java-api查询

1、下载ElasticSearch5.6.3,windows版本压缩包，解压到D:\tools\elasticsearch-5.6.3

2、cmd窗口进入bin目录，输入 elasticsearch.bat,回车，等待服务启动成功，检验方法是，在浏览器窗口输入：http://localhost:9200,出现如下内容则启动成功：

    

{
  "name" : "tb0gatF",
  "cluster_name" : "elasticsearch",
  "cluster_uuid" : "gTNzllFpT4Gqy_arnGkw_A",
  "version" : {
    "number" : "5.6.3",
    "build_hash" : "1a2f265",
    "build_date" : "2017-10-06T20:33:39.012Z",
    "build_snapshot" : false,
    "lucene_version" : "6.6.1"
  },
  "tagline" : "You Know, for Search"
}

3、创建es客户端对象：

先在pom文件添加elasticsearch的相关依赖：

       <dependency>
			<groupId>org.elasticsearch</groupId>
			<artifactId>elasticsearch</artifactId>
			<version>5.6.3</version>
		</dependency>

		<dependency>
			<groupId>org.elasticsearch.client</groupId>
			<artifactId>transport</artifactId>
			<version>5.6.3</version>
		</dependency>

初始化client:

private static TransportClient client;  
     
     @SuppressWarnings("resource")  
     public static TransportClient getClient() {  
            if(client!=null){  
                return client;  
            }  
            try {  
                Settings settings = Settings.builder()
                        .put("cluster.name", "elasticsearch").build();  

//连接服务器的端口是9300，浏览器访问接口是9200
            client = new PreBuiltTransportClient(settings).addTransportAddress(new InetSocketTransportAddress(InetAddress.getByName("127.0.0.1"), 9300));  
            } catch (UnknownHostException e) {  
                e.printStackTrace();  
            }  
            return client;  
        }  

4、全文检索：

//创建searchrequest对象，指定索引和type,也可以只指定索引 

SearchRequestBuilder srb = client .prepareSearch("users").setTypes("user");

//    keys为检索内容，会在type的所有属性中查找

QueryStringQueryBuilder queryString = new QueryStringQueryBuilder(keys);

 SearchResponse s1 = srb
                        .setQuery(srb)
                        .setFrom(0).setSize(100).setExplain(true) .execute().actionGet();  
  SearchHit[] sh = s1.getHits().getHits();//结果集
  Long countsL =  s1.getHits().getTotalHits();//结果总数
  for(int i=0;i<sh.length;i++) {

//sh[i].getSource()相当于整行数据
                    System.out.println("source_ip====" + sh[i].getSource().get("Source_IP"));
                }

5、相等检索：

QueryBuilders.matchPhraseQuery（“Login_Account”，“张三”）；

6、范围检索：

QueryBuilders.rangeQuery(“age”).gte（15）；

类似的有gt(大于)，lt(小于)，lte(小于等于)，from(起始值).to(终止值)等。

7、模糊匹配：

QueryBuilders.wildcardQuery(“name”,"张*")；

8、聚合：

//accountAgg为聚合名称，Login_Account为聚合字段
TermsAggregationBuilder gradeTermsBuilder
= AggregationBuilders.terms("accountAgg").field("Login_Account");
//日期聚合：
DateHistogramAggregationBuilder classTermsBuilder = 
AggregationBuilders.
dateHistogram("timeAgg").field("Login_Time");

//设定时间间隔为1小时，1个半小时为1.5
 classTermsBuilder.dateHistogramInterval(
        DateHistogramInterval.hours(1));  
  //这里设置时区，感觉没有什么效果 
 classTermsBuilder.timeZone(DateTimeZone.
             forTimeZone(TimeZone.getDefault()));  
 classTermsBuilder.format("yyyy-MM-dd HH"); 
//获取聚合内容：
  Map<String, Aggregation> aggMap = sr.getAggregations().asMap();
  StringTerms gradeTerms = (StringTerms) aggMap.get("accountAgg");
  Iterator<StringTerms.Bucket> gradeBucketIt = 
gradeTerms.getBuckets().iterator();
  List<Map<String,Object>> failList = new ArrayList<Map<String,Object>>();
  DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH");
  while(gradeBucketIt.hasNext()){
      Bucket gradeBucket = gradeBucketIt.next();
 InternalDateHistogram classTerms =
(InternalDateHistogram) gradeBucket.getAggregations()
    .asMap().get("timeAgg");
      Iterator<InternalDateHistogram.Bucket> classBucketIt = 
           classTerms.getBuckets().iterator();
      while(classBucketIt.hasNext()) {
          InternalDateHistogram.Bucket classBucket = classBucketIt.next();
          System.out.println("账户："+gradeBucket.getKey() + "，在：" 
          + classBucket.getKey() + "，登录失败次数：" 
          + classBucket.getDocCount());
                }
            }

    如果有3次，4次聚会，可以像上边这样一直钻取下去。需要注意的是，聚会字段需要先mapping一次，否则会报错，mapping的代码：

TransportClient client=getClient();  
	    	PutMappingRequestBuilder putMapping = client.admin().indices().preparePutMapping("alert")   
	        .setType("loginInfo");                                
	    	putMapping.setSource("{\"properties\":{\"Login_Account\":{\"type\":\"string\",\"fielddata\":true}}}")
	        .get();
	        putMapping.setSource(
"{\"properties\": {\"Login_Time\": {\"type\": \"date\"}}}")
	        .get();

    二、ElasticSearch-SQL查询

1、maven中央仓库没有找到5.6.3版本的依赖地址，所以到github上把elasticsearch-sql的主干分支加载到本地，并build出jar包，然后在自己项目的pom中添加依赖：
 

       <dependency>
		    <groupId>org.nlpcn</groupId>
		    <artifactId>elasticsearch-sql</artifactId>
		    <version>5.6.3.4</version>
		</dependency>

2、初始化druid的DruidDataSource对象：

  Properties properties = new Properties();//读取配置文件
  properties.put("url", "jdbc:elasticsearch://127.0.0.1:9300");
  try {
       _dds =  (DruidDataSource) ElasticSearchDruidDataSourceFactory
	    	        .createDataSource(properties);
       _dds.setInitialSize(1);
       _dds.setMaxActive(20);
  }

3、获取Connection:

    

 _dds.getConnection()

4、查询sql，已查询列表为例：

String sql2 = "select Login_Account,Login_Time,"+
"count(*) as total "+
"FROM alert/loginInfo "+
"WHERE Login_Result='失败' "+
" and (Login_Time>='now+2h'  "+
"group by Login_Account,"+
"date_histogram(field='Login_Time',"+
"'format'='yyyy-MM-dd HH:mm','interval'='1h')";
PreparedStatement ps = connection.prepareStatement(sql2);
ResultSet resultSet = ps.executeQuery();
ResultSetMetaData md = resultSet.getMetaData();
int columnCount = md.getColumnCount(); 
List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();   
Map<String,Object> rowData = new HashMap<String,Object>();   
while (resultSet.next()) {
	 rowData = new HashMap<String,Object>(columnCount);   
	 for (int i = 1; i <= columnCount; i++) {
     //md从1开始，resultSet从0开始  
	     rowData.put(md.getColumnName(i), resultSet.getObject(i-1));   
	 }   
	 list.add(rowData);   
  }
ps.close();
connection.close();
_dds.close()

目前就这些，以后遇到相关问题，再记录一下。

转载于:https://my.oschina.net/u/161393/blog/1571769