Présentation d'Elasticsearch SQL et exemples pratiques – 2e partie
Dans la 1re partie de la série Présentation d'Elasticsearch SQL, nous vous avons offert une présentation sommaire de la nouvelle fonctionnalité Elasticsearch SQL, ainsi que de l'API _translate. Le présent article vient compléter cette série en vous faisant découvrir des fonctionnalités plus complexes. Par la suite, nous étudierons certaines des limites actuelles auxquelles les utilisateurs sont susceptibles d'être confrontés lors de l'utilisation de cette première version d'Elasticsearch SQL. Puis, nous clôturerons la série par un aperçu de la roadmap.
Nous vous rappelons que tous les lecteurs ont la possibilité d'exécuter les exemples présentés dans l'environnement demo.elastic.co ou d'attendre que l'ensemble de données soit disponible via Kibana. Tous les ensembles de données utilisés dans le cadre de la présente série ont été indexés, suite à la mise à disposition de SQL par le biais de la console Kibana. Chaque exemple s'accompagne d'un lien vers le site demo.elastic.co. Cependant, vous pouvez également voir toutes les requêtes dans un même fil dans notre console de démonstration Kibana.
Exemples complexes et avantages d'Elasticsearch
Regroupement
L'architecture d'agrégation d'Elasticsearch, qui est en mesure de synthétiser des milliards de points de données, est l'une des fonctionnalités les plus puissantes et les plus appréciées de la Suite. En termes de fonctionnalités, elle est l'équivalent naturel de la commande GROUP BY sous SQL. En plus de vous présenter des exemples des fonctionnalités de la commande GROUP BY, nous nous servirons à nouveau de l'API _translate pour vous montrer les agrégations équivalentes.
"Rechercher la durée moyenne d'un vol en fonction du pays d'origine pour les vols à destination de Londres. Trier les pays par ordre alphabétique."
sql> SELECT AVG(FlightTimeHour) Avg_Flight_Time, OriginCountry FROM flights GROUP BY OriginCountry ORDER BY OriginCountry LIMIT 5; Avg_Flight_Time | OriginCountry ------------------+--------------- 9.342180244924574 |AE 13.49582274385201 |AR 4.704097126921018 |AT 15.081367354940724|AU 7.998943401875511 |CA
L'analyse du DSL de cette requête montre l'utilisation de l'agrégation imbriquée (Composite Aggregation).
{
"size": 0,
"_source": false,
"stored_fields": "_none_",
"aggregations": {
"groupby": {
"composite": {
"size": 1000,
"sources": [
{
"3471": {
"terms": {
"field": "OriginCountry.keyword",
"order": "asc"
}
}
}
]
},
"aggregations": {
"3485": {
"avg": {
"field": "FlightTimeHour"
}
}
}
}
}
}
Nous pouvons également effectuer des regroupements par champs d'alias définis dans la sélection en recourant à une fonction.
"Rechercher le nombre de vols et la durée moyenne des vols par mois."
POST _xpack/sql
{
"query":"SELECT COUNT(*), MONTH_OF_YEAR(timestamp) AS month_of_year, AVG(FlightTimeHour) AS Avg_Flight_Time FROM flights GROUP BY month_of_year"
}
COUNT(1) | month_of_year | Avg_Flight_Time ---------------+---------------+----------------- 1303 |5 |8.628949653846158 8893 |6 |8.520481551839334 2863 |7 |8.463433805045094
L'utilisation de l'agrégation imbriquée offre un principal avantage : elle garantit que l'implémentation de la commande GROUP BY est scalable, même dans le cas des champs dont la cardinalité est élevée, et propose donc un mécanisme de transmission de tous les intervalles d'une agrégation précise dont l'action est comparable à celle du défilement pour les documents. Elle garantit également que l'implémentation ne soit pas exposée aux mêmes limitations de mémoire qu'en cas d'utilisation de l'agrégation de termes. En revanche, cela signifie que nous ne sommes, pour le moment, pas en mesure de trier les résultats GROUP BY par l'intermédiaire d'un indicateur. Par exemple, la proposition ci-dessous produirait une erreur :
POST _xpack/sql/translate
{
"query":"SELECT AVG(FlightTimeHour) Avg_Flight_Time, OriginCountry FROM flights GROUP BY OriginCountry ORDER BY Avg_Flight_Time"
}
Filtrage des groupes
Afin de pouvoir filtrer les groupes, nous pouvons nous servir de la condition HAVING, qui peut aussi utiliser un alias indiqué dans la clause SELECT. Pour certains experts SQL, cette action peut s'avérer inhabituelle, étant donné qu'elle est généralement impossible dans le cadre des implémentations basées sur RDBMS en raison de l'exécution de la clause SELECT après la condition HAVING. Dans notre cas, la clause HAVING se sert d'un alias déclaré au cours de la phase d'exécution. Toutefois, notre analyseur est suffisamment intelligent pour anticiper cela et relever la déclaration afin de l'utiliser dans la clause HAVING.
"Rechercher le nombre de vols, la distance moyenne et la distance au 95e percentile des vols au départ de chaque ville d'origine, pour lesquels la distance moyenne est comprise entre 3 000 et 4 000 miles."
sql> SELECT OriginCityName, ROUND(AVG(DistanceKilometers)) avg_distance, COUNT(*) c, ROUND(PERCENTILE(DistanceKilometers,95)) AS percentile_distance FROM flights GROUP BY OriginCityName HAVING avg_distance BETWEEN 3000 AND 4000; OriginCityName | avg_distance | c |percentile_distance ---------------+---------------+---------------+------------------- Verona |3078 |120 |7927 Vienna |3596 |120 |7436 Xi'an |3842 |114 |7964
Pour implémenter la condition HAVING, Elasticsearch SQL utilise l'agrégation de pipeline Bucket Selector et filtre les valeurs en recourant à un script Painless paramétrable. Notez ci-dessous la façon dont la variante keyword du champ OriginCityName est automatiquement sélectionnée pour l'agrégation, au lieu de tenter d'utiliser la variante de texte standard, qui échouerait probablement en raison de la non-activation des données de champs. Les agrégations d'indicateurs avg et percentile offrent des fonctions équivalentes aux variantes SQL.
{
"size": 0,
"_source": false,
"stored_fields": "_none_",
"aggregations": {
"groupby": {
"composite": {
"size": 1000,
"sources": [
{
"4992": {
"terms": {
"field": "OriginCityName.keyword",
"order": "asc"
}
}
}
]
},
"aggregations": {
"4947": {
"bucket_selector": {
"buckets_path": {
"a0": "5010",
"a1": "5010"
},
"script": {
"source": "params.v0 <= params.a0 && params.a1 <= params.v1",
"lang": "painless",
"params": {
"v0": 3000,
"v1": 4000
}
},
"gap_policy": "skip"
}
},
"5010": {
"avg": {
"field": "DistanceKilometers"
}
},
"5019": {
"percentiles": {
"field": "DistanceKilometers",
"percents": [
95
],
"keyed": true,
"tdigest": {
"compression": 100
}
}
}
}
}
}
}
Opérateurs de texte et pertinence
Par rapport à un RDBMS classique, l'une des fonctionnalités propres à Elasticsearch en tant que moteur de recherche est sa capacité à fournir des résultats plus étoffés qu'un simple oui / non, en tenant compte des propriétés des données textuelles par le biais d'un calcul de pertinence. L'élargissement de la syntaxe SQL nous permet de révéler cette fonctionnalité et d'aller bien au-delà de ce qu'un RDBMS classique offrirait.
De ce fait, nous vous présentons deux nouvelles clauses : QUERY et MATCH. Pour les personnes habituées à Elasticsearch, elles sont les équivalentes des opérateurs sous-jacents multi_match et query_string. Les utilisateurs de Kibana connaissent le comportement de l'opérateur query_string, puisqu'il servait à alimenter la barre de recherche par défaut. Cet opérateur offre des fonctionnalités d'analyse intelligentes et permet de produire des requêtes employant un langage de requête naturel. Cet article ne présente pas dans le détail ces deux opérateurs, mais la section correspondante du Definitive Guide fournit une parfaite présentation de ces concepts.
Par exemple, considérez ce qui suit :
"Rechercher tous les vols retardés à destination et au départ de l'aéroport de Kastrup entre le 06/06/2018 et le 17/06/2018, triés par date."
En réalité, l'aéroport de Kastrup se trouve à Copenhague, son nom complet étant "Aéroport de Kastrup-Copenhague". En utilisant l'opérateur QUERY, nous recherchons simplement Kastrup.
sql> SELECT timestamp, FlightNum, OriginCityName, DestCityName FROM flights WHERE QUERY('Kastrup') AND FlightDelay=true AND timestamp > '2018-06-20' AND timestamp < '2018-06-27' ORDER BY timestamp;
timestamp | FlightNum |OriginCityName | DestCityName
------------------------+---------------+---------------+---------------
2018-06-21T01:46:28.000Z|57SWSLT |Copenhagen |Orlando
2018-06-21T07:28:07.000Z|X43J6GE |Abu Dhabi |Copenhagen
2018-06-21T13:36:31.000Z|7T04SK7 |Copenhagen |Milan
2018-06-22T19:52:15.000Z|NXMN87D |Mumbai |Copenhagen
2018-06-23T08:05:02.000Z|YXHMDKV |Copenhagen |Oslo
2018-06-25T18:21:04.000Z|2R86JEZ |Copenhagen |Shanghai
2018-06-26T22:16:10.000Z|TCE99LO |Copenhagen |Edmonton
Notez que nous n'étions pas forcés d'indiquer le champ ici. La recherche de "kastrup" avec l'opérateur QUERY a suffi. De plus, notez que nous avons des vols retardés à destination et au départ de Kastrup. Voici la requête Elasticsearch :
{
"size": 1000,
"query": {
"bool": {
"filter": [
{
"bool": {
"filter": [
{
"bool": {
"filter": [
{
"query_string": {
"query": "Kastrup",
"fields": [],
"type": "best_fields",
"default_operator": "or",
"max_determinized_states": 10000,
"enable_position_increments": true,
"fuzziness": "AUTO",
"fuzzy_prefix_length": 0,
"fuzzy_max_expansions": 50,
"phrase_slop": 0,
"escape": false,
"auto_generate_synonyms_phrase_query": true,
"fuzzy_transpositions": true,
"boost": 1
}
},
{
"term": {
"FlightDelay": {
"value": true,
"boost": 1
}
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
{
"range": {
"timestamp": {
"from": "2018-06-20",
"to": null,
"include_lower": false,
"include_upper": false,
"boost": 1
}
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
{
"range": {
"timestamp": {
"from": null,
"to": "2018-06-27",
"include_lower": false,
"include_upper": false,
"boost": 1
}
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
"_source": {
"includes": [
"FlightNum",
"OriginCityName",
"DestCityName"
],
"excludes": []
},
"docvalue_fields": [
"timestamp"
],
"sort": [
{
"timestamp": {
"order": "asc"
}
}
]
}
Pour les utilisateurs qui commencent à utiliser Elasticsearch, cette requête est relativement complexe. En effet, nous avons une requête booléenne présentant une plage imbriquée, une restriction de terme et un opérateur de chaîne de recherche. Pour les opérateurs migrant une application depuis SQL, cette tâche aurait traditionnellement été assez ardue avant même d'avoir à se demander si la requête finale était correcte et optimale d'un point de vue fonctionnel. L'opérateur query_string actuel a été intégré à un filtre, puisqu'aucune pertinence n'était requise (le tri s'effectue par date), ce qui nous permet de tirer profit du cache de filtre, d'ignorer l'attribution d'un score et d'améliorer le temps de réponse.
Les paramètres de ces opérateurs sont également exposés dans SQL. Ce dernier exemple présente l'utilisation d'une requête MATCH avec plusieurs termes de recherche dans plusieurs champs pour limiter les résultats.
"Rechercher des vols à destination ou au départ de Barcelone pour lesquels la météo a inclus des éclairs."
Aux fins de cet exemple, nous effectuons également un tri et montrons le score de pertinence par l'intermédiaire de la fonction Score().
sql> SELECT Score(), timestamp, FlightNum, OriginCityName, DestCityName, DestWeather, OriginWeather FROM flights WHERE MATCH('*Weather,*City*', 'Lightning Barcelona', 'type=cross_fields;operator=AND') ORDER BY Score() DESC LIMIT 5;
SCORE() | timestamp | FlightNum |OriginCityName | DestCityName | DestWeather | OriginWeather
---------------+------------------------+---------------+---------------+---------------+-------------------+-------------------
6.990964 |2018-05-31T06:00:41.000Z|L637ISB |Barcelona |Santiago |Rain |Thunder & Lightning
6.990964 |2018-06-13T12:23:44.000Z|0GIHB62 |Barcelona |Buenos Aires |Clear |Thunder & Lightning
6.9796515 |2018-06-14T21:04:51.000Z|7GEMEDR |Barcelona |Hyderabad |Thunder & Lightning|Rain
6.9133706 |2018-05-31T01:58:51.000Z|ZTOD7RQ |Barcelona |Dubai |Sunny |Thunder & Lightning
6.9095163 |2018-06-06T14:02:34.000Z|QSQA5CT |Barcelona |Naples |Rain |Thunder & Lightning
Nous utilisons des modèles à caractère générique pour indiquer les champs à faire correspondre et demander à ce que le résultat soit un booléen AND. Le paramètre de champs croisés ne requiert pas des termes qu'ils apparaissent dans un même champ ; ils sont en effet autorisés à apparaître dans différents champs du moment qu'ils existent. Au vu de la structure des données, cette condition est essentielle au résultat.
Ici, nos exemples ont renvoyé une comparaison des lignes et des groupes. Cependant, les opérateurs QUERY et MATCH peuvent aussi être utilisés avec une commande GROUP BY, une agrégation filtrée efficacement dans Elasticsearch.
Recherches d'index croisé et alias
Jusqu'à maintenant, nos requêtes ne ciblaient qu'une table / un index unique. Si nous dupliquons l'index des vols, en copiant les documents et en leur attribuant un nouveau nom en créant une requête de réindexation, nous pouvons requêter les deux index au même moment, à condition qu'ils aient des mappages identiques. Toute différence de mappage produirait une erreur de requête lors de l'analyse. Afin de demander simultanément plusieurs index, l'utilisateur peut les ajouter à un alias Elasticsearch ou utiliser des caractères génériques dans la clause WHERE. Par exemple, supposons que j'ai deux index "flight" et "flights-2" sous l'alias suivant "f_alias" :
POST /_aliases
{
"actions" : [
{ "add" : { "index" : "flights-2", "alias" : "f_alias" } },
{ "add" : { "index" : "flights", "alias" : "f_alias" } }
]
}
Les éléments suivants sont logiquement équivalents :
sql> SELECT FlightNum, OriginCityName, DestCityName, DestWeather, OriginWeather FROM flights* ORDER BY timestamp DESC LIMIT 1; FlightNum |OriginCityName | DestCityName | DestWeather | OriginWeather ---------------+---------------+---------------+---------------+--------------- 6UPDDGK |Zurich |Zurich |Rain |Hail sql> SELECT FlightNum, OriginCityName, DestCityName, DestWeather, OriginWeather FROM f_alias ORDER BY timestamp DESC LIMIT 1; FlightNum |OriginCityName | DestCityName | DestWeather | OriginWeather ---------------+---------------+---------------+---------------+--------------- 6UPDDGK |Zurich |Zurich |Rain |Hail
Essayez sur demo.elastic.co. (Remarque : notre environnement de démonstration dispose des deux index avec un alias pré-configuré pour que les utilisateurs testent l'exemple ci-dessus.)
Il se peut que nous assouplissions les exigences ci-dessus à l'avenir, mais pour le moment, elles simplifient la logique de cette première version.
Clauses JOIN
Les clauses JOIN d'une implémentation SQL avec RDBMS classique permettent de combiner les lignes de différentes tables via des colonnes associées dans une réponse tabulaire à part. Cette action permet la modélisation relationnelle des données, et il s'agit d'une rubrique importante par rapport aux options nativement disponibles dans Elasticsearch pour y parvenir. Bien qu'Elasticsearch SQL ne propose aucun opérateur JOIN pour le moment, elle permet aux utilisateurs d'exploiter des documents imbriqués, et donc d'effectuer une modélisation relationnelle simple en un et plusieurs documents. La demande de documents imbriqués est transférée en toute transparence à l'utilisateur. Pour vous faire la démonstration de cette fonctionnalité, nous avons besoin d'un index présentant de telles données. Aux fins de cet exemple, nous avons téléchargé un index "orders" sur demo.elastic.co. Les documents de cet index correspondent aux commandes d'un site de commerce électronique et contiennent des champs, tels que order_date, billing_city et customer_last_name. Par ailleurs, un champ "products" contient un sous-document imbriqué pour chaque produit de la commande. Exemple :
{
"billing_last_name": "Green",
"billing_first_name": "Jason",
"order_id": 519894,
"products": [
{
"tax_amount": 0,
"taxful_price": 22.99,
"quantity": 1,
"taxless_price": 22.99,
"discount_amount": 0,
"base_unit_price": 22.99,
"discount_percentage": 0,
"product_name": "Briefcase - black",
"manufacturer": "Pier One",
"min_price": 11.27,
"created_on": "2016-11-08T04:16:19+00:00",
"unit_discount_amount": 0,
"price": 22.99,
"product_id": 12733,
"base_price": 22.99,
"_id": "sold_product_519894_12733",
"category": "Men's Accessories",
"sku": "PI952HA0M-Q11"
},
{
"tax_amount": 0,
"taxful_price": 16.99,
"quantity": 1,
"taxless_price": 16.99,
"discount_amount": 0,
"base_unit_price": 16.99,
"discount_percentage": 0,
"product_name": "3 PACK - Boxer shorts - white/navy",
"manufacturer": "Pier One",
"min_price": 8.33,
"created_on": "2016-11-08T04:16:19+00:00",
"unit_discount_amount": 0,
"price": 16.99,
"product_id": 18370,
"base_price": 16.99,
"_id": "sold_product_519894_18370",
"category": "Men's Clothing",
"sku": "PI982AA0Y-A11"
}
],
"has_error": false,
"customer_last_name": "Green",
"currency": "EUR",
"billing_first_name": "Jason",
"shipping_country_code": "US",
"email": "swagelastic@gmail.com",
"day_of_week": "Tuesday",
"geoip": {
"continent_name": "North America",
"city_name": "New York",
"country_iso_code": "US",
"location": {
"lon": -73.9862,
"lat": 40.7662
},
"region_name": "New York"
},
"payment_status": "FULLY_PAID",
...
}
Normalement, pour demander ces documents, l'utilisateur doit comprendre pourquoi nous utilisons un type de donnée imbriqué pour le champ des produits, sans oublier la syntaxe de la requête imbriquée. Toutefois, avec Elasticsearch SQL, nous pouvons demander ces documents imbriqués comme si chacun d'entre eux représentait une ligne distincte comportant les champs de sa commande parente (c.-à-d. que nous aplatissons efficacement la structure aux fins de la présentation). Regardez la commande ci-dessus, qui comporte deux produits. Lorsque nous la requêtons, elle se présente sous la forme de deux lignes si nous demandons les champs des sous-documents des produits. Chaque ligne peut dorénavant contenir les champs de la commande parente, si nous le demandons. Par exemple :
"Rechercher le nom de facturation utilisé pour les vols et les produits achetés, pour la commande 518894."
sql> SELECT billing_last_name, billing_first_name, products.price, products.product_id FROM orders WHERE order_id=519894; billing_last_name|billing_first_name|products.price |products.product_id -----------------+------------------+---------------+------------------- Green |Jason |16.984375 |18370 Green |Jason |22.984375 |12733
L'API _translate présente la structure de cette requête en utilisant une requête imbriquée :
{
"size": 1000,
"query": {
"bool": {
"filter": [
{
"term": {
"order_id": {
"value": 519894,
"boost": 1
}
}
},
{
"nested": {
"query": {
"match_all": {
"boost": 1
}
},
"path": "products",
"ignore_unmapped": false,
"score_mode": "none",
"boost": 1,
"inner_hits": {
"ignore_unmapped": false,
"from": 0,
"size": 99,
"version": false,
"explain": false,
"track_scores": false,
"_source": false,
"stored_fields": "_none_",
"docvalue_fields": [
"products.product_id",
"products.price"
]
}
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
"_source": {
"includes": [
"billing_last_name",
"billing_first_name"
],
"excludes": []
},
"sort": [
{
"_doc": {
"order": "asc"
}
}
]
}
À l'inverse, si nous demandons simplement les champs parents, une seule ligne est présentée :
"Rechercher le nom de facturation utilisé pour les vols, pour la commande 518894."
sql> SELECT billing_last_name, billing_first_name FROM orders WHERE order_id=519894; billing_last_name|billing_first_name -----------------+------------------ Green |Jason
Fonctionnalités manquantes et à venir...
Limites actuelles auxquelles vous risquez d'être confronté au début de toute expérimentation :
- Requêtes de table / d'index croisés : possibles à condition que les mappages des index soient identiques. À l'heure actuelle, toute différence produit une erreur au moment de la requête.
- Opérateurs JOIN : comme nous l'avons souligné plus haut, nous prenons en charge des opérateurs JOIN limités uniquement en cas d'utilisation de documents imbriqués.
- Tri des groupes dans la commande GROUP BY : comme mentionné plus haut, cette limitation provient de l'agrégation imbriquée Elasticsearch.
- Clauses SELECT imbriquées : il s'agit d'un mécanisme ordinaire visant à étayer l'analyse des outils BI. Bien que les instructions SELECT imbriquées utilisant plusieurs index peuvent être l'équivalent d'un opérateur JOIN, il serait possible de réécrire et d'exécuter des instructions utilisant le même index. Nous pourrions envisager cette possibilité dans le futur.
Voici quelques-uns des avantages d'Elasticsearch que nous prévoyons d'intégrer dans les prochaines versions :
- Possibilité d'exécuter une commande GROUP BY sur plusieurs niveaux (p. ex. "rechercher des itinéraires de vols (départ et destination) par mois avec un retard moyen supérieur à 2 h")
- Présentation des opérateurs géospatiaux Elasticsearch
- Support de type INTERVAL (p. ex. possibilité de travailler sur la différence entre deux dates et deux heures)
- Amélioration des fonctions Data/Time Math et String
- Prise en charge des histogrammes dans le cadre du regroupement
- Recherche d'index croisé et de cluster croisé présentant des mappages non identiques
Merci d'être arrivé jusqu'ici et surveillez la sortie des prochains numéros de la série Présentation d'Elasticsearch SQL.