ES8 支持SQL

转载

mob6454cc6bcf40 2024-09-12 20:39:14

文章标签 ES8 支持SQL java 大数据运维 sql 文章分类 架构后端开发

前言

前面两边文章已经讲述了如何搭建集群以及简单的查询基础

一、怎样用SQL思维来写查询代码

写惯了SQL然后来写ES的查询可能有很别扭，ES其实也提供了queryStringQuery的方式来查询，这个查询和SQL有点接近了，但是本文还是用普通代码方式达到SQL关系查询的逻辑

我们先看个简单的代码：

@Test
	public void match() {
		SearchRequestBuilder requestBuilder = client.prepareSearch("megacorp").setTypes("employee")
				.setQuery(QueryBuilders.matchQuery("about", "rock climbing"));
		System.out.println(requestBuilder.toString());

		SearchResponse response = requestBuilder.execute().actionGet();

		System.out.println(response.status());
		if (response.status().getStatus() == 200) {
			for (SearchHit hits : response.getHits().getHits()) {
				System.out.println(hits.getSourceAsString());
			}
		}
	}

===============================================================

LIKE查询 这个代码其实在普通的SQL里面是达不到这个效果的，因为matchQuery会对后面的value进行分词后再去匹配，跳过！

/**
	 * matchphrase使用，短语精准匹配
	 */
	@Test
	public void matchPhrase() {
		SearchRequestBuilder requestBuilder = client.prepareSearch("megacorp").setTypes("employee")
				.setQuery(QueryBuilders.matchPhraseQuery("about", "rock climbing"));
		System.out.println(requestBuilder.toString());

		SearchResponse response = requestBuilder.execute().actionGet();
		System.out.println(response.status());
		if (response.status().getStatus() == 200) {
			for (SearchHit hits : response.getHits().getHits()) {
				System.out.println(hits.getSourceAsString());
			}
		}
	}

上面的代码你可以理解为：

select * from megacorp_employee where about like '%rock climbing%'

聚合查询

@Test
	public void aggregation() {
		SearchRequestBuilder searchBuilder = client.prepareSearch("megacorp").setTypes("employee")
				.addAggregation(AggregationBuilders.terms("by_interests").field("interests")
						.subAggregation(AggregationBuilders.terms("by_age").field("age")).size(10));
		System.out.println(searchBuilder.toString());
		SearchResponse response = searchBuilder.execute().actionGet();

		if (response.status().getStatus() == 200) {
			for (SearchHit hits : response.getHits().getHits()) {
				System.out.println(hits.getSourceAsString());
			}
		}
		StringTerms terms = response.getAggregations().get("by_interests");
		for (StringTerms.Bucket bucket : terms.getBuckets()) {
			System.out.println("-interest:" + bucket.getKey() + "," + bucket.getDocCount());
			if (bucket.getAggregations() != null && bucket.getAggregations().get("by_age") != null) {
				LongTerms ageTerms = bucket.getAggregations().get("by_age");
				for (LongTerms.Bucket bucket2 : ageTerms.getBuckets()) {
					System.out.println("--------by age:" + bucket2.getKey() + "," + bucket2.getDocCount());
				}
			}
		}
	}

相当于SQL里面的

select interests,age,count(1) from megacorp_employee
group by interests,age limit 10

布尔查询

BoolQueryBuilder queryBuilder = QueryBuilders.boolQuery();
		if(StringUtils.isNotBlank(searchParam.getSearchWords())) {
			BoolQueryBuilder mutiShould = QueryBuilders.boolQuery();
			for(String column : searchType.getSearchColumn()) {
				mutiShould.should(QueryBuilders.termQuery(column+KEYWORD, searchParam.getSearchWords().trim()));
			}
			queryBuilder.must().add(mutiShould);
		}
		
		// 科室编码过滤
		if(StringUtils.isNotBlank(searchParam.getDeptNo())) {
			queryBuilder.must(QueryBuilders.termQuery("admissward"+KEYWORD, searchParam.getDeptNo().trim()));
		}
		
		/**
		 * 有时间范围
		 */
		if(searchParam.getTimeType() > 0 && searchParam.getTimeType() < 3) {
			Date startDate = searchParam.getStartDate();
			Date endDate = searchParam.getEndDate();
			RangeQueryBuilder rangeBuilder = null;
			
			// 入院日期
			if(searchParam.getTimeType() == 1) {
				if(null != startDate) {
					rangeBuilder = QueryBuilders.rangeQuery("admissdate").gte(startDate.getTime());
				}
				if(null != endDate) {
					if(null == rangeBuilder) {
						rangeBuilder = QueryBuilders.rangeQuery("admissdate").lte(endDate.getTime());
					} else {
						rangeBuilder.lte(endDate.getTime());
					}
				}
				
			// 出院日期
			} else if(searchParam.getTimeType() == 2) {
				if(null != startDate) {
					rangeBuilder = QueryBuilders.rangeQuery("disdate").gte(startDate.getTime());
				}
				if(null != endDate) {
					if(null == rangeBuilder) {
						rangeBuilder = QueryBuilders.rangeQuery("disdate").lte(endDate.getTime());
					} else {
						rangeBuilder.lte(endDate.getTime());
					}
				}
			}
			if(null != rangeBuilder) {
				queryBuilder.must().add(rangeBuilder);
			}
		}
		
		SearchRequestBuilder searchBuilder = client.prepareSearch(searchType.getIndexType().get_index())
		        .setTypes(searchType.getIndexType().get_type())
		        .setSearchType(SearchType.DFS_QUERY_THEN_FETCH)
		        .setQuery(queryBuilder) 
		        .addSort(StringUtils.isBlank(searchType.getSortColumn())?SCORE:searchType.getSortColumn()
		        		, searchType.getOrder()==null?SortOrder.DESC:searchType.getOrder())
		        .setFrom(pager.getStartRow()).setSize(pager.getPageSize()).setExplain(true);
		
		SearchResponse response = searchBuilder.execute().actionGet();
		long end = System.currentTimeMillis();
		logger.info("searchMutiField request indexType:{},searchparam:{},orderColumn:{},orderBy:{}.total hits:{},cost 【{}】 ms"
				,searchType.getIndexType().get_type(),queryBuilder.toString(),searchType.getSearchColumn(),
				searchType.getOrder(),response.getHits().totalHits,(end-start));

上面的稍微复杂一点，是我生产环境的部分代码，对应的SQL语句是，其实你看到这一个例子应该就大概知道了怎样用SQL转化为代码，BoolQueryBuilder.must就相当于SQL里面的 AND 的概念，Should就是OR

select * from table_name where (column1='searchwords' or column2='searchwords' .. )
   and admissward='123456' and 
   admissdate > '1412000212112' and admissdate < '141976521211' limit 10
   --我的判断逻辑是如果是入院日期查询就 admissdate > startdate and admissdate < endate
   --如果是出院日期 就disdate > startdate and disdate < enddate
   --这个逻辑我就不分开写出来了，省略了

二、使用ES注意事项

默认的java.util.Date放到map，然后去创建索引，ES中会保存UTC时间格式，这个比较恶心！当然，时间格式你可以getTime之后当做long去存储，就是不够直观，也可以通过我上一篇文章中一样在创建索引的时候指定date类型字段的format属性。为了方便创建索引，我直接创建了一个xml配置文件来指定数据创建索引时固定其类型！解析xml我就不贴了，要不然篇幅太长！

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapping SYSTEM "elastic-config.dtd">
<!-- 属性参考 https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping-store.html -->
<mapping  >
 	<!--  
	<datasource id="dataSource1" ref="springDataSource">
	</datasource>-->	  
	
	<datasource id="dataSource" >
		<username>admin</username>
		<password>admin</password>
		<jdbcurl>jdbc:mysql://127.0.0.1:3306/message?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=round&useCursorFetch=true&verifyServerCertificate=false&useSSL=false</jdbcurl>
		<driver>com.mysql.jdbc.Driver</driver>
	</datasource>
	
	<sql-mappings>
		<sql-mapping data-source-id="dataSource">
			<!-- 全量索引 构建 每周星期天3点执行 -->
			<full-sql> 
				<sql>SELECT * FROM HAHA ORDER BY ID ASC</sql>
				<expression>0 0 3 ? * SUN</expression>
			</full-sql>
			<!-- 每日增量索引构建 -->
			<incr-sql> 
				<sql>SELECT * FROM HAHA WHERE GMT_CREATE > DATE_ADD(NOW(),INTERVAL -2 DAY) 
				ORDER BY ID ASC</sql>
				<expression>0 0 2 * * ?</expression>
			</incr-sql>
			<search-info>
				<index>test</index>
				<type>test</type>
				<columns>
					<column index-column="idindex" 
					        data-type="integer"
					        sql-column="id" 
					        index="not_analyzed" 
					        store="no"  />
					<column index-column="nameindex" 
					        data-type="string"
					        sql-column="name" 
					        index="not_analyzed" 
					        store="no" />
					<column index-column="blobtindex" 
					        data-type="byte"
					        sql-column="blobt" 
					        index="not_analyzed" 
					        store="no" /> 
					<column index-column="datesindex" 
					        data-type="date"
					        sql-column="ttt" 
					        store="no" 
					        format="yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"
					        locale="CHINA" />    
					<column index-column="tinytestindex" 
					        data-type="boolean"
					        sql-column="tinytest" 
					        index="not_analyzed" 
					        store="no" />
					<column index-column="moneysindex" 
					        data-type="string"
					        sql-column="moneys" 
					        index="not_analyzed" 
					        store="no" />
					<column index-column="ggggindex" 
					        data-type="date"
					        sql-column="gggg" 
					        format="yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"
					        store="no" />                                            
				</columns>
			</search-info>
		</sql-mapping>
	</sql-mappings>
</mapping>

通过接口查出的时间格式是UTC格式，使用代码转换一下即可

SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'");
formatter.setTimeZone(TimeZone.getTimeZone("UTC"));
SimpleDateFormat standard = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try {
	return standard.format(formatter.parse(admiss_time));
} catch (ParseException e) {
	return null;
}

//我们只需要获取当前我们本地时间之后getTime传入即可 admissdate >= xxxxx
QueryBuilders.rangeQuery("admissdate").gte(startDate.getTime());

频繁更新的数据的索引ID，可以尽量不使用UUID偷懒。一个是速度快，另外如果使用我们自已的业务ID来当做索引的ID在更新的时候会很方便，你直接保存进去就会自动更新数据，而不是说新插一条数据，比如下面，分两次保存只会有一条数据存在索引，因为id是一样的！

Map<String,Object> map = new HashMap<String,Object>();
map.put("id", 1);
//map.put('test',456);
map.put("test", 1);
//map.put('hehe',567);
map.put("hehe", 2);
IndexResponse response = client.prepareIndex("emr_document2", "user_info2",map.get('id').toString())
    			.setSource(map)
                .get();

使用ES来做日志管控。官方有kibana+logstash+ES的日志管理解决方案，我们自己如果不想搞那么复杂引入那么多产品进来的话，可以直接自己用RandomAccessFile方式来读取日志文件后写入ES索引，像日志这种东西比较适合每日或者每周做一个单独饿索引，如：index = log_index_20170906 这种，好处不用说了吧，我们磁盘空间是有限的，如果把所有日志写到一个索引里面去，我们要清理历史不用的日志就麻烦一点，还不如每天一个索引，然后过期后就把历史没用的哪个索引直接删掉。