Secciones

From SQL to Elasticsearch

Inicio » Artículos » From SQL to Elasticsearch
La categoría del artículo es
Escrito el 30 January 2018, 21:05


Nowadays, almost everybody is used to work with some SQL queries in a daily basis, but what happens when the data store changes to Elasticsearch? You can find here some useful tips (I hope they are) to ease the transition.


Nowadays, almost everybody is used to work with some SQL queries in a daily basis, but what happens when the data store changes to Elasticsearch? You can find here some useful tips (I hope they are) to ease the transition.

For the Elasticsearch version, we will use single urls, bearing in mind that you are exposing Elasticsearch on the port 9200. In these examples we will use this User model:

groovy:
class User {
        String id
        String name
        String department
}

Description MySQL ElasticSearch
Select by a field SELECT * FROM user WHERE id = ‘1’; http://localhost:9200/user/_search?q=id:1
Select by a field SELECT * FROM user WHERE name = ‘Fran’; http://localhost:9200/user/_search?q=name:Fran
Select only some fields SELECT id FROM user WHERE name = ‘Fran’; http://localhost:9200/user/_search?q=name:Fran&_source=id
Select only some records SELECT id FROM user WHERE department = ‘Engineering’ limit 0,50; http://localhost:9200/user/_search?q=department:Engineering&size=50
Count by a field SELECT count(*) as total FROM user WHERE name=‘Fran’; http://localhost:9200/user/_count?q=name:Fran
Deleting DELETE FROM user WHERE id =‘1’; curl -XPOST ‘http://localhost:9200/userindex/_delete_by_query’ —data ‘{ “query”: { “match”: { “id”: “1” } }
}’
Updating UPDATE user SET name=‘Frank’ WHERE name=‘Fran’; curl -XPOST ‘localhost:9200/user/_update_by_query?q=name:Fran’ -H ‘Content-Type: application/json’ —data ‘{“script”:{“source”:“ctx._source.name=Frank”,“lang”:“painless”}}
Grouping SELECT count(*) FROM user GROUP BY department curl -XGET ‘localhost:9200/user/_search?pretty’ -H ‘Content-Type: application/json’ -d’
{ “size”: 0, “aggs”: { “group_by_department”: { “terms”: { “field”: “department” } } }
}

Looking forward your comments

Ayuda Textile