preface
In the previous part, we discussed the basic concept of ES and how to select data migration schemes according to different scenarios. In this article we’ll look at how to integrate with Spring Boot and how to “translate SQL” for a smooth migration from Mysql to ES.
Spring Boot integrated with ES
Spring Boot includes Java REST Client, Spring-data-ElasticSearch, and ES. I recommend using the Java High Level REST Client provided by Elasticsearch to integrate with the ES Cloud service of Ali Cloud in the production environment. Key version information is as follows:
- ES cluster: 7.3.0
- ES dependency: 7.3.0
Two things to note here:
-
For example, the Java High Level REST Client of 7.3.0 can ensure that it can communicate with Elasticsearch clusters whose versions are later than 7.3.0. To maximize the use of the latest Client features, it is recommended that the high-level Client version be the same as the cluster version.
-
There may be some pitfalls during the integration process, because there is a “correlation” between the Spring Boot version, the ES cluster version, and the high-level Client version, so when the Demo does not work properly, the best thing to do is to try more high-level Client versions.
1. Pom dependency
<dependency>
<groupId>org.elasticsearch.client</groupId>
<artifactId>elasticsearch-rest-high-level-client</artifactId>
<version>7.3.0</version>
</dependency>
<dependency>
<groupId>org.elasticsearch</groupId>
<artifactId>elasticsearch</artifactId>
<version>7.3.0</version>
</dependency>
<dependency>
<groupId>org.elasticsearch.client</groupId>
<artifactId>elasticsearch-rest-client</artifactId>
<version>7.3.0</version>
</dependency>
<dependency>
<groupId>org.elasticsearch.plugin</groupId>
<artifactId>rank-eval-client</artifactId>
<version>7.3.0</version>
</dependency>
<dependency>
<groupId>org.elasticsearch.plugin</groupId>
<artifactId>lang-mustache-client</artifactId>
<version>7.3.0</version>
</dependency>
Copy the code2. Initialize the client
@Configuration
public class EsConfig {
@Value("${elasticsearch.host}")
public String host;
/** * The HighLevelClient interface is 9200 */
@Value("${elasticsearch.port:9200}")
public int port;
public static final String SCHEME = "http";
@Value("${elasticsearch.username:admin}")
public String username;
@Value("${elasticsearch.authenticationPassword}")
public String authenticationPassword;
@Bean(name = "remoteHighLevelClient")
public RestHighLevelClient restHighLevelClient(a) {
final CredentialsProvider credentialsProvider = new BasicCredentialsProvider();
credentialsProvider.setCredentials(AuthScope.ANY, new UsernamePasswordCredentials(username,
authenticationPassword));
RestClientBuilder builder = RestClient.builder(new HttpHost(host, port, SCHEME)).
setHttpClientConfigCallback(httpClientBuilder -> httpClientBuilder
.setDefaultCredentialsProvider(credentialsProvider));
return newRestHighLevelClient(builder); }}Copy the codeNote in the above code that username and authenticationPassword are set in Kibana.
Second, the Java API
The following code snippets all work in the unit test. Before executing the unit test below, we create a _template, which you can optionally execute in the Dev Tools provided by Kibana.
PUT _template/hero_template
{
"index_patterns": ["hero*"]."mappings": {"properties": {"@timestamp": {"type":"date"
},
"id": {"type":"integer"
},
"name": {"type":"keyword"
},
"country": {"type":"keyword"
},
"birthday": {"type":"keyword"
},
"longevity": {"type":"integer"}}}}Copy the code1. Create an index
@Test
public void createIndex(a) throws IOException {
IndexRequest request = new IndexRequest("hero");
request.id("1");
Map<String, String> map = new HashMap<>();
map.put("id"."1");
map.put("name"."Cao cao");
map.put("country"."Wei");
map.put("birthday"."155 A.D.");
map.put("longevity"."65");
request.source(map);
IndexResponse indexResponse = client.index(request, RequestOptions.DEFAULT);
long version = indexResponse.getVersion();
assertEquals(DocWriteResponse.Result.CREATED, indexResponse.getResult());
assertEquals(1, version);
}
Copy the codeIn ES, an index is the logical unit where we store and query data. After ES7.0, it corresponds to the concept of tables in Mysql. In the above code we created an index named Hero, then we created a map as the first data we inserted, and set it into the IndexRequest object.
2. Batch insert
@Test
public void bulkRequestTest(a) throws IOException {
BulkRequest request = new BulkRequest();
request.add(new IndexRequest("hero").id("2")
.source(XContentType.JSON,"id"."2"."name"."Liu bei"."country"."Shu"."birthday"."161 A.D."."longevity"."61"));
request.add(new IndexRequest("hero").id("3")
.source(XContentType.JSON,"id"."3"."name"."Sun quan"."country"."Wu"."birthday"."182 A.D."."longevity"."61"));
request.add(new IndexRequest("hero").id("4")
.source(XContentType.JSON,"id"."4"."name"."Zhuge Liang"."country"."Shu"."birthday"."Year 181."."longevity"."53"));
request.add(new IndexRequest("hero").id("5")
.source(XContentType.JSON,"id"."5"."name".Sima Yi."country"."Wei"."birthday"."The year 179."."longevity"."72"));
request.add(new IndexRequest("hero").id("6")
.source(XContentType.JSON,"id"."6"."name"."Xun yu"."country"."Wei"."birthday"."A.D. 163."."longevity"."49"));
request.add(new IndexRequest("hero").id("Seven")
.source(XContentType.JSON,"id"."Seven"."name"."Guan yu"."country"."Shu"."birthday"."160 A.D."."longevity"."60"));
request.add(new IndexRequest("hero").id("8")
.source(XContentType.JSON,"id"."8"."name"."Zhou yu"."country"."Wu"."birthday"."The year 175 A.D."."longevity"."35"));
BulkResponse bulkResponse = client.bulk(request, RequestOptions.DEFAULT);
assertFalse(bulkResponse.hasFailures());
}
Copy the codeThe data queried in Kibana is shown below
Our subsequent queries, updates, and so on will be based on this data.
3. Update data
@Test
public void updateTest(a) throws IOException {
Map<String, Object> jsonMap = new HashMap<>();
jsonMap.put("country"."Wei");
UpdateRequest request = new UpdateRequest("hero"."Seven").doc(jsonMap);
UpdateResponse updateResponse = client.update(request, RequestOptions.DEFAULT);
assertEquals(DocWriteResponse.Result.UPDATED, updateResponse.getResult());
}
Copy the codeThe above code would look like this if expressed in SQL
> update hero set country=' 5 'where id=7;Copy the codeInsert/update data
@Test
public void insertOrUpdateOne(a){
Hero hero = new Hero();
hero.setId(5);
hero.setName(Xelloss "");
hero.setCountry("Wei");
hero.setBirthday("The year 187.");
hero.setLongevity(39);
IndexRequest request = new IndexRequest("hero");
request.id(hero.getId().toString());
request.source(JSON.toJSONString(hero), XContentType.JSON);
try {
IndexResponse indexResponse = client.index(request, RequestOptions.DEFAULT); // 1
assertEquals(DocWriteResponse.Result.UPDATED, indexResponse.getResult());
} catch (Exception e) {
throw newRuntimeException(e); }}Copy the codeNote that the line marked 1 in the above code is very similar to the one used to create the index. Using index(), we can easily create index, insert data, update data in one, create index when the specified index does not exist, insert when the data does not exist, update when the data exists.
5. Delete data
@Test
public void deleteByIdTest(a) throws IOException {
DeleteRequest deleteRequest = new DeleteRequest("hero");
deleteRequest.id("1");
DeleteResponse deleteResponse = client.delete(deleteRequest, RequestOptions.DEFAULT);
assertEquals(DocWriteResponse.Result.DELETED, deleteResponse.getResult());
}
Copy the codeSQL > alter table create id=1;
> delete from hero where id=1;
Copy the codeOf course, in ES we can not only use primary keys to delete, we can also delete by other field conditions.
@Test
public void deleteByQueryRequestTest(a) throws IOException {
DeleteByQueryRequest request = new DeleteByQueryRequest("hero");
request.setConflicts("proceed");
request.setQuery(new TermQueryBuilder("country"."Wu"));
BulkByScrollResponse bulkResponse =
client.deleteByQuery(request, RequestOptions.DEFAULT);
assertEquals(0, bulkResponse.getBulkFailures().size());
}
Copy the codeSQL:
> delete from hero where country='wu';
Copy the code6. Compound operation
The above additions, deletions and changes can only operate one type at a time, while ES also provides us with multiple types of operations at a time, such as the following code
@Test
public void bulkDiffRequestTest(a) throws IOException {
BulkRequest request = new BulkRequest();
request.add(new DeleteRequest("hero"."3"));
request.add(new UpdateRequest("hero"."Seven")
.doc(XContentType.JSON,"longevity"."70"));
BulkResponse bulkResponse = client.bulk(request, RequestOptions.DEFAULT);
BulkItemResponse[] bulkItemResponses = bulkResponse.getItems();
for (BulkItemResponse item : bulkItemResponses){
DocWriteResponse itemResponse = item.getResponse();
switch (item.getOpType()) {
case UPDATE:
UpdateResponse updateResponse = (UpdateResponse) itemResponse;
break;
caseDELETE: DeleteResponse deleteResponse = (DeleteResponse) itemResponse; } assertEquals(RestStatus.OK, item.status()); }}Copy the codeWe used BulkRequest objects to add DeleteRequest and UpdateRequest operations to BulkRequet, and then classified the returned BulkItemResponse[] arrays according to different operation types. Of course, as far as I know, Mysql currently does not have similar syntax support, if there is a hope to comment on it.
7, query
Here’s what we really focus on ES inside to support multiple types of queries, such as * * “accurate” (and RDBMS differ) query and fuzzy query, relevance queries and range queries, full-text retrieval, paging query, sorting, aggregation, * *, and so on query function, in most of the Mysql query function all can realize in the ES. The same also allows us to choose synchronous and asynchronous ways to execute queries
Single-condition query + limit
@Test
public void selectByUserTest(a){
SearchRequest request = new SearchRequest("hero");
SearchSourceBuilder builder = new SearchSourceBuilder();
builder.query(new TermQueryBuilder("country"."Wei"));
// limit 1;
builder.size(1);
request.source(builder);
try {
SearchResponse response = client.search(request, RequestOptions.DEFAULT);
SearchHit[] hits = response.getHits().getHits();
assertEquals(1, hits.length);
} catch (Exception e) {
throw newRuntimeException(e); }}Copy the codeIn the unit test above, we used user as the query condition and limited the number of returns, similar to SQL
> select * from posts where country='魏' limit 1;
Copy the codeMulti-condition query + sort + paging
@Test
public void boolQueryTest(a){
SearchRequest request = new SearchRequest("hero");
SearchSourceBuilder sourceBuilder = new SearchSourceBuilder();
BoolQueryBuilder boolQueryBuilder = new BoolQueryBuilder();
boolQueryBuilder.must(termQuery("country"."Wei"));
boolQueryBuilder.must(rangeQuery("longevity").gte(50));
sourceBuilder.query(boolQueryBuilder);
sourceBuilder.from(0).size(2);
sourceBuilder.query(boolQueryBuilder);
sourceBuilder.sort("longevity", SortOrder.DESC);
request.source(sourceBuilder);
SearchResponse response = null;
try {
response = client.search(request, RequestOptions.DEFAULT);
} catch (IOException e) {
log.error("Query by Condition execution failed: {}", e.getMessage(), e);
}
assertresponse ! =null;
assertEquals(0, response.getShardFailures().length);
SearchHit[] hits = response.getHits().getHits();
List<Hero> herosList = new ArrayList<>(hits.length);
for (SearchHit hit : hits) {
herosList.add(JSON.parseObject(hit.getSourceAsString(), Hero.class));
}
log.info("print info: {}, size: {}", herosList.toString(), herosList.size());
}
Copy the codeThe above will cao Wei group life over 50 years old heroes query, and according to the life from high to low order, only two heroes, its corresponding SQL:
> select * from hero where country='魏' and longevity >= 50 order by longevity DESC limit 2;
Copy the codeNote here that when we use multi-condition queries in the ES API, we need to encapsulate multiple conditions in the BoolQueryBuilder object, which supports the following query types
private static final String MUSTNOT = "mustNot";
private static final String MUST_NOT = "must_not";
private static final String FILTER = "filter";
private static final String SHOULD = "should";
private static final String MUST = "must";
Copy the codePlease refer to the official documentation for details
conclusion
In this section we first shared how to integrate Spring Boot with ES and best practice recommendations for building our API with a Java High Level REST Client, then shared related dependencies and how to initialize the Client.
Then we started to use High Level REST Client to create index, batch insert, update data, insert/update data, delete data, compound operation, and finally we used two simple examples to implement query data, of course there are many other query examples are not shown, recommend you according to your own needs, Go to the official website to find out how to use it.
reference
Java High Level REST Client