Présentation d'Elasticsearch SQL et exemples pratiques – 1re partie
La version 6.3 de la Suite Elastic était l'une des versions récentes les plus riches en fonctionnalités. Comme si l'ouverture de X-Pack et l'ajout des rollups de données ne suffisaient pas à susciter votre enthousiasme, nous avons annoncé l'ajout de la prise en charge de SQL comme fonction expérimentale. Il s'agit de l'une des fonctions les plus demandées depuis les débuts, relativement récents, d'Elasticsearch.
Dans cette série d'articles, nous vous présentons quelques-unes des fonctionnalités et capacités actuellement prises en charge par Elasticsearch SQL. En outre, nous aborderons ouvertement les quelques limitations que présente ce système, ainsi que nos projets à venir.
Public cible
Historiquement, Elastic a toujours hésité à ajouter SQL à Elasticsearch, pour plusieurs raisons. Voici quelques-unes des questions les plus souvent posées :
- Que représente exactement la prise en charge de SQL ?
- Quelles fonctionnalités seraient prises en charge ? Le plus souvent, cette question était présentée ainsi : "Prenons-nous en charge les jointures ? Les expressions/fonctions ? Le regroupement ?"
- Doit-on prendre en charge la connectivité JDBC/ODBC ?
- Est-il vraiment nécessaire de prendre en charge SQL ou ne parvenons-nous simplement pas à fournir les ressources suffisantes aux nouveaux utilisateurs pour qu'ils puissent se familiariser avec notre propre langage dédié (Elasticsearch DSL ou DSL) ?
Après plusieurs tentatives, nous avons identifié les fonctionnalités requises qui, selon nous, seraient utiles à nos utilisateurs. D'après les discussions que nous avons eues avec eux, nous pensons que SQL s'avère être une solution avantageuse pour deux principaux types d'utilisateurs :
- Les nouveaux utilisateurs de la Suite Elastic, pour qui le langage dédié d'Elasticsearch (DSL) peut sembler terrifiant de prime abord, ou qui n'ont tout simplement pas le temps d'en apprendre la syntaxe dans le détail. Par exemple, pour un utilisateur qui convertit une application basée sur SQL afin d'améliorer les performances et l'évolutivité, et qui recherche la requête équivalente sans avoir à apprendre toute la syntaxe. Trouver des équivalences en fonction de ce que vous connaissez déjà est une approche courante pour l'apprentissage de nouveaux langages.
- Les consommateurs de données qui ne souhaitent pas apprendre l'intégralité du langage DSL, ou qui n'en ont pas besoin. Il peut s'agir de scientifiques des données qui souhaitent extraire des données à des fins de traitement externe. Alternativement, il peut s'agir d'utilisateurs BI moins spécialisés, qui ont une connaissance générale de SQL et qui utilisent ces opérations au quotidien.
En dehors des publics décrits ci-dessus, on ne peut pas nier que le langage SQL, en tant que langage déclaratif, constitue un paradigme extrêmement séduisant pour tous. C'est ce qu'explique cette série d'articles. Il ne fait aucun doute que la prévalence du langage SQL s'appuie sur la capacité à exprimer la logique du calcul et ce que vous essayez d'accomplir, sans avoir à définir le flux de contrôle. De plus, comme vous le verrez, certaines requêtes SQL définissent élégamment un problème qui peut s'avérer "verbeux" avec avec un DSL équivalent. En résumé, DSL décrit élégamment des problèmes de recherche en texte intégral, SQL s'avère plus efficace pour décrire des requêtes basées sur des analyses structurées.
Ce qu'est Elasticsearch SQL, et ce qu'il n'est pas…
Dans sa version initiale, Elasticsearch SQL est une interface en lecture seule conforme à un sous-ensemble de spécifications ANSI SQL, qui permet d'exposer Elasticsearch sous forme de source tabulaire. Nous proposons également des opérateurs supplémentaires, en plus de ces spécifications, qui affichent des capacités propres à Elasticsearch, par rapport aux déploiements basés sur RDBMS. Nous nous sommes efforcés de fournir une implémentation légère et rapide qui minimise les dépendances externes et les pièces mobiles. Cette première offre ne transforme en aucun cas Elasticsearch en base de données entièrement relationnelle (avec ses propriétés associées) ni ne remplace l'impératif de modélisation des données. Bien que certaines expressions et fonctions de manipulation des données soient intégrées par le plug-in SQL, nous respectons un principe de pushdown chaque fois que l'ordre et le nombre de résultats sont impactés ou que le regroupement est requis. Cela permet de limiter le traitement actuel des données dans le plug-in Elasticsearch SQL à la seule manipulation des résultats (par exemple, fonctions sur les champs) et de cantonner le client (pilote JDBC/CLI ou navigateur) au rendu. Cette approche exploite l'évolutivité et la performance d'Elasticsearch, afin qu'il se charge des opérations complexes.
Concepts de mappage : index et documents vs tables et lignes
Aux débuts d'Elasticsearch, les index et les types étaient souvent considérés comme analogues aux tables et bases de données RDBMS, principalement pour aider les utilisateurs à comprendre des concepts potentiellement nouveaux et à les utiliser. Comme nous l'expliquons dans la documentation d'Elasticsearch 6.3, il s'agit d'une analogie incorrecte et potentiellement dangereuse. C'est pourquoi nous supprimons des types. Cependant, nous avons encore besoin d'une équivalence logique, correcte et utilisable, entre le modèle axé sur un document sans schéma d'Elasticsearch et les concepts fortement "typés" de SQL.
Heureusement, les index Elasticsearch, comme les tables RDBMS, sont physiquement isolés et doivent être utilisés, grossièrement, de la même manière (c'est-à-dire, pour stocker des données associées). Les lignes et les documents constituent aussi une analogie naturelle, car ils fournissent un mécanisme permettant de regrouper les champs/colonnes, bien qu'une ligne soit généralement plus stricte (et ait plus d'applications), alors qu'un document est généralement un peu plus flexible (tout en conservant une structure). Les champs dans Elasticsearch représentent une entrée de nom et prennent en charge plusieurs types de données. De plus, ils peuvent contenir plusieurs valeurs (c'est-à-dire une liste). À l'exception de ces champs à plusieurs valeurs, ce concept permet un mappage direct avec les colonnes SQL. Remarque : si vous tentez une commande SELECT dans un champ à plusieurs valeurs, la requête échouera et indiquera une erreur.
Les autres concepts n'offrent pas de mappage aussi direct : les équivalences entre les bases de données SQL et les clusters Elasticsearch restent limitées. Cependant, elles n'affectent généralement pas l'utilisateur d'Elasticsearch SQL. Pour en savoir plus sur ce sujet, consultez la documentation Mappage des concepts dans SQL et Elasticsearch.
Pour résumer, utilisez le nom de l'index dans votre clause WHERE pour cibler une table / un index spécifique. Les documents uniques seront alors rendus sous forme de lignes et les champs seront mappés en colonnes. En raison de ce mappage largement transparent, nous utiliserons ces termes de manière interchangeable dans la suite de cet article.
Éléments d'implémentation internes
L'implémentation d'Elasticsearch SQL se compose de 4 phases d'exécution :
La phase d'analyse est chargée de convertir la requête SQL en arbre de syntaxe abstraite (AST). La validation de la syntaxe est effectuée lors de cette phase, avant que l'analyseur valide l'AST et résolve les tables, colonnes, fonctions, alias et espaces de noms pour produire un plan logique. Ce plan est optimisé et inclut le retrait des éventuelles expressions redondantes, avant d'être converti en plan physique (c'est-à-dire, en DSL) afin d'être exécuté. L'exécuteur de requête exécute alors la requête réelle et transmet les résultats au client, en procédant à la conversion des types et la conversion tabulaire, selon les besoins (par exemple, arbre d'agrégation en table).
Méthodes de connectivité
Lorsque l'on fournit une solution SQL, la prise en charge de la connectivité devient un facteur essentiel. Bien qu'une interface exclusivement REST soit acceptable pour certains utilisateurs, la plupart des utilisateurs souhaitent pouvoir se connecter via des interfaces standard (typiquement JDBC et ODBC). Nous prévoyons la prise en charge d'ODBC et nous y travaillons activement. L'interface JDBC est quant à elle disponible avec cette première version et disponible au téléchargement (en version bêta).
Surtout, toutes les communications avec ces pilotes se feront via HTTP, par l'intermédiaire de notre interface REST. Cela offre plusieurs avantages intéressants :
- Accorder l'accès SQL aux utilisateurs équivaut à ouvrir et exposer un port Elasticsearch qui s'intègre de manière native avec le système de sécurité. Par conséquent, nous pouvons immédiatement prendre en charge le langage SQL dans Elasticsearch Service hébergé dans le cloud et disponible via Elastic Cloud, et les utilisateurs existants peuvent alors l'associer à des permissions de contrôle d'accès prêtes à l'emploi.
- Cela nous permet d'utiliser SQL directement via l'interface REST et de fournir un client CLI supplémentaire, pour plus de simplicité. Cette seconde offre devrait rencontrer un grand succès auprès des administrateurs qui connaissent déjà le fonctionnement des lignes de commande, courantes dans les systèmes RDBMS.
Le pilote JDBC utilise la toute nouvelle bibliothèque XContent, chargée d'analyser les requêtes et les réponses (historiquement, ce code était étroitement lié à Elasticsearch). Ainsi, le pilote ne dépend plus de l'ensemble des bibliothèques Elasticsearch et reste donc léger et portable. Nous continuerons d'améliorer ce processus de séparation dans les futures versions, afin de créer un pilote plus petit et plus rapide.
Exemples simples
Intéressons-nous à quelques exemples qui utilisent un mélange CLI et API REST. Dans les cas que nous étudierons, nous utilisons un exemple d'ensemble de données qui sera bientôt distribué avec Kibana. Si vous trépignez d'impatience, cet ensemble de données de vol est aussi disponible sur demo.elastic.co. Vous pouvez y exécuter les exemples suivants, via la console Kibana. Tout au long de cette série d'articles, nous vous proposons des liens vers demo.elastic.co qui se remplissent automatiquement avec la requête concernée. Nous offrons aussi la liste complète des requêtes à exécuter dans la console Kibana de démonstration. Dans certains cas, les résultats peuvent varier s'il n'existe aucun ordre ni aucune restriction explicite concernant la demande, en raison de l'ordre naturel des résultats dans Elasticsearch, lorsqu'aucun ordre de tri ni aucun facteur de pertinence ne s'applique.
Récupération des informations de schéma Elasticsearch : DSL vs SQL
Commençons par identifier le schéma dans la table / index et les champs avec lesquels nous pouvons jouer. Pour cela, nous utilisons l'interface REST :
Requête
POST _xpack/sql
{
"query":"DESCRIBE flights"
}
Réponse
{
"columns": [
{
"name": "column",
"type": "keyword"
},
{
"name": "type",
"type": "keyword"
}
],
"rows": [
[ "AvgTicketPrice", "REAL" ],
[ "Cancelled", "BOOLEAN" ],
[ "Carrier", "VARCHAR" ],
[ "Dest", "VARCHAR" ],
[ "DestAirportID", "VARCHAR" ],
[ "DestCityName", "VARCHAR" ],
[ "DestCountry", "VARCHAR" ],
[ "DestLocation", "OTHER" ],
[ "DestRegion", "VARCHAR" ],
[ "DestWeather", "VARCHAR" ],
[ "DistanceKilometers", "REAL" ],
[ "DistanceMiles", "REAL" ],
[ "FlightDelay", "BOOLEAN" ],
[ "FlightDelayMin", "INTEGER" ],
[ "FlightDelayType", "VARCHAR" ],
[ "FlightNum", "VARCHAR" ],
[ "FlightTimeHour", "VARCHAR" ],
[ "FlightTimeMin", "REAL" ],
[ "Origin", "VARCHAR" ],
[ "OriginAirportID", "VARCHAR" ],
[ "OriginCityName", "VARCHAR" ],
[ "OriginCountry", "VARCHAR" ],
[ "OriginLocation", "OTHER" ],
[ "OriginRegion", "VARCHAR" ],
[ "OriginWeather", "VARCHAR" ],
[ "dayOfWeek", "INTEGER" ],
[ "timestamp", "TIMESTAMP" ]
]
}
La réponse ci-dessus peut aussi être formatée sous forme tabulaire, grâce au paramètre URL ?format=txt. Par exemple :
POST _xpack/sql?format=txt
{
"query":"DESCRIBE flights"
}
column | type --------------------------+--------------- AvgTicketPrice |REAL Cancelled |BOOLEAN Carrier |VARCHAR Carrier.keyword |VARCHAR Dest |VARCHAR Dest.keyword |VARCHAR DestAirportID |VARCHAR DestAirportID.keyword |VARCHAR DestCityName |VARCHAR DestCityName.keyword |VARCHAR DestCountry |VARCHAR DestCountry.keyword |VARCHAR DestLocation |STRUCT DestLocation.lat |VARCHAR DestLocation.lat.keyword |VARCHAR DestLocation.lon |VARCHAR DestLocation.lon.keyword |VARCHAR DestRegion |VARCHAR DestRegion.keyword |VARCHAR DestWeather |VARCHAR DestWeather.keyword |VARCHAR DistanceKilometers |REAL DistanceMiles |REAL FlightDelay |BOOLEAN FlightDelayMin |BIGINT FlightDelayType |VARCHAR FlightDelayType.keyword |VARCHAR FlightNum |VARCHAR FlightNum.keyword |VARCHAR FlightTimeHour |REAL FlightTimeMin |REAL Origin |VARCHAR Origin.keyword |VARCHAR OriginAirportID |VARCHAR OriginAirportID.keyword |VARCHAR OriginCityName |VARCHAR OriginCityName.keyword |VARCHAR OriginCountry |VARCHAR OriginCountry.keyword |VARCHAR OriginLocation |STRUCT OriginLocation.lat |VARCHAR OriginLocation.lat.keyword|VARCHAR OriginLocation.lon |VARCHAR OriginLocation.lon.keyword|VARCHAR OriginRegion |VARCHAR OriginRegion.keyword |VARCHAR OriginWeather |VARCHAR OriginWeather.keyword |VARCHAR dayOfWeek |BIGINT timestamp |TIMESTAMP
Nous allons ensuite utiliser la structure de réponse tabulaire illustrée ci-dessus pour fournir un exemple de réponse depuis l'API REST. Pour obtenir la même requête depuis la console, nous devons nous connecter avec les identifiants suivants :
./elasticsearch-sql-cli http://elastic@localhost:9200
Après avoir répondu à la demande de saisie de mot de passe…
sql> DESCRIBE flights; column | type ------------------+--------------- AvgTicketPrice |REAL Cancelled |BOOLEAN Carrier |VARCHAR Dest |VARCHAR DestAirportID |VARCHAR DestCityName |VARCHAR DestCountry |VARCHAR DestLocation |OTHER DestRegion |VARCHAR DestWeather |VARCHAR DistanceKilometers|REAL DistanceMiles |REAL FlightDelay |BOOLEAN FlightDelayMin |INTEGER FlightDelayType |VARCHAR FlightNum |VARCHAR FlightTimeHour |VARCHAR FlightTimeMin |REAL Origin |VARCHAR OriginAirportID |VARCHAR OriginCityName |VARCHAR OriginCountry |VARCHAR OriginLocation |OTHER OriginRegion |VARCHAR OriginWeather |VARCHAR dayOfWeek |INTEGER timestamp |TIMESTAMP sql>
Le schéma ci-dessus est également renvoyé avec chaque requête pour les champs affichés dans la clause SELECT. Ainsi, le pilote bénéficie de toutes les informations de type nécessaires et requises pour formater ou exploiter les résultats. Par exemple, une clause SELECT simple avec une clause LIMIT pour que la réponse reste courte. Par défaut, nous renvoyons 1 000 lignes.
SELECT simple
POST _xpack/sql?format=txt
{
"query":"SELECT FlightNum FROM flights LIMIT 1"
}
Essayez sur demo.elastic.co (remarque : les résultats peuvent varier)
FlightNum --------------- 1Y0TZOE
Cette même requête / réponse REST est consommée par le pilote JDBC et la console, mais l'utilisateur ne la voit pas.
sql> SELECT OriginCountry, OriginCityName FROM flights LIMIT 1; OriginCountry |OriginCityName ---------------+--------------- US |San Diego
Essayez sur demo.elastic.co (remarque : les résultats peuvent varier)
Notez que si vous effectuez une requête pour un champ qui n'existe pas (sensibilité aux minuscules ou aux majuscules), la sémantique d'un stockage fortement typé implique qu'une erreur sera renvoyée. Cela diffère du comportement d'Elasticsearch, avec lequel le champ ne sera tout simplement pas renvoyé. Par exemple, si vous modifiez la structure ci-dessus pour utiliser le champ « OrigincityName » au lieu de « OriginCityName », vous obtiendrez un message d'erreur utile :
{
"error": {
"root_cause": [
{
"type": "verification_exception",
"reason": "Found 1 problem(s)\nline 1:8: Unknown column [OrigincityName], did you mean any of [OriginCityName, DestCityName]?"
}
],
"type": "verification_exception",
"reason": "Found 1 problem(s)\nline 1:8: Unknown column [OrigincityName], did you mean any of [OriginCityName, DestCityName]?"
},
"status": 400
}
De la même manière, si nous tentons d'utiliser une fonction ou une expression sur un champ non compatible, nous obtenons une erreur adaptée. De manière générale, l'analyseur échoue rapidement lors de la validation de l'AST. Pour cela, Elasticsearch doit connaître le mappage d'index et les capacités de chaque champ. C'est pourquoi, lorsqu'un client accède à l'interface SQL avec sécurité, il doit posséder les autorisations appropriées.
Nous ne pouvons pas illustrer toutes les requêtes et leurs réponses. Cet article serait interminable. Pour aller au plus court, voici quelques requêtes, par ordre de complexité, ainsi que quelques points d'intérêt.
SELECT avec WHERE et ORDER BY
"Rechercher les 10 plus longs vols de plus de 5 h aux États-Unis."
POST _xpack/sql?format=txt
{
"query":"SELECT OriginCityName, DestCityName FROM flights WHERE FlightTimeHour > 5 AND OriginCountry='US' ORDER BY FlightTimeHour DESC LIMIT 10"
}
OriginCityName | DestCityName ---------------+--------------- Atlanta |Durban Louisville |Melbourne Peoria |Melbourne Albuquerque |Durban Birmingham |Durban Bangor |Brisbane Seattle |Durban Huntsville |Sydney Savannah |Shanghai Philadelphia |Xi'an
L'opérateur de restriction du nombre de lignes varie en fonction de l'implémentation SQL. Pour Elasticsearch SQL, nous utilisons PostgreSQL / MySQL de manière constante pour l'implémentation de l'opérateur LIMIT.
Maths
Un petit calcul, au hasard…
sql> SELECT ((1 + 3) * 1.5 / (7 - 6)) * 2 AS random;
random
---------------
12.0
Dans cet exemple, le côté serveur effectue une opération post-traitement concernant les fonctions. Il n'existe aucune requête équivalente en DSL.
Fonctions et expressions
"Rechercher tous les vols, après le mois de juin, dont la durée de vol est supérieure à 5 h et classés du plus long au plus court."
POST _xpack/sql?format=txt
{
"query":"SELECT MONTH_OF_YEAR(timestamp), OriginCityName, DestCityName FROM flights WHERE FlightTimeHour > 5 AND MONTH_OF_YEAR(timestamp) > 6 ORDER BY FlightTimeHour DESC LIMIT 10"
}
MONTH_OF_YEAR(timestamp [UTC])|OriginCityName | DestCityName ------------------------------+---------------+------------------- 7 |Buenos Aires |Shanghai 7 |Stockholm |Sydney 7 |Chengdu |Bogota 7 |Adelaide |Cagliari 7 |Osaka |Buenos Aires 7 |Buenos Aires |Chitose / Tomakomai 7 |Buenos Aires |Shanghai 7 |Adelaide |Washington 7 |Osaka |Quito 7 |Buenos Aires |Xi'an
Typiquement, ces fonctions nécessitent d'écrire avec Painless pour obtenir un résultat équivalent dans Elasticsearch alors que les éléments déclaratifs fonctionnels de SQL évitent toute sorte de scripts. En outre, notez que nous pouvons utiliser la fonction pour les clauses WHERE et SELECT. L'élément de la clause WHERE est dirigé vers Elasticsearch, car il affecte le nombre de résultats. La fonction SELECT, quant à elle, est gérée par le plug-in côté serveur, dès son apparition.
Notez que vous pouvez accéder à une liste de fonctions disponibles grâce à "SHOW FUNCTIONS".
En associant cela aux capacités mathématiques mentionnées précédemment, nous pouvons commencer à formuler des requêtes qui seraient trop complexes à articuler en DSL pour la plupart des utilisateurs.
"Rechercher la distance et la vitesse moyenne des 2 vols les plus rapides (vélocité) qui partent lundi, mardi ou mercredi, entre 9 h et 11 h et qui parcourent plus de 500 km. Arrondir la distance et la vitesse à l'entier le plus proche. Si la vitesse est la même, afficher le vol le plus long en premier."
sql> SELECT timestamp, FlightNum, OriginCityName, DestCityName, ROUND(DistanceMiles) AS distance, ROUND(DistanceMiles/FlightTimeHour) AS speed, DAY_OF_WEEK(timestamp) AS day_of_week FROM flights WHERE DAY_OF_WEEK(timestamp) >= 0 AND DAY_OF_WEEK(timestamp) <= 2 AND HOUR_OF_DAY(timestamp) >=9 AND HOUR_OF_DAY(timestamp) <= 10 ORDER BY speed DESC, distance DESC LIMIT 2;
timestamp | FlightNum |OriginCityName | DestCityName | distance | speed | day_of_week
------------------------+---------------+---------------+---------------+---------------+---------------+---------------
2018-07-03T10:03:11.000Z|REPKGRT |Melbourne |Norfolk |10199 |783 |2
2018-06-05T09:18:29.000Z|J72Y2HS |Dubai |Lima |9219 |783 |2
Cette question est curieuse et plutôt alambiquée, mais vous avez compris le principe. Notez également comment nous avons créé des alias de champs et comment nous y faisons référence dans la clause ORDER BY.
De plus, notez que dans la clause SELECT, vous n'êtes pas obligé de remplir tous les champs utilisés dans les clauses WHERE et ORDER BY. Il s'agit là d'une légère différence par rapport aux implémentations de SQL que vous utilisiez précédemment. Par exemple, la structure suivante est parfaitement valide :
POST _xpack/sql
{
"query":"SELECT timestamp, FlightNum FROM flights WHERE AvgTicketPrice > 500 ORDER BY AvgTicketPrice"
}
Traduire des requêtes SQL en DSL
Nous avons tous déjà été confrontés à une requête SQL difficile à exprimer en DSL. Et nous nous sommes tous déjà demandé si cette solution était réellement optimale. L'une des fonctions particulièrement intéressantes de la nouvelle interface SQL est sa capacité à aider les nouveaux utilisateurs d'Elasticsearch à pallier ces problèmes. À l'aide de l'interface REST, nous ajoutons /translate au point de terminaison "sql" afin d'obtenir la requête Elasticsearch que le pilote enverrait.
Prenons l'exemple des précédentes requêtes :
POST _xpack/sql/translate
{
"query":"SELECT OriginCityName, DestCityName FROM flights WHERE FlightTimeHour > 5 AND OriginCountry='US' ORDER BY FlightTimeHour DESC LIMIT 10"
}
Le DSL équivalent est relativement évident pour les utilisateurs expérimentés d'Elasticsearch :
{
"size": 10,
"query": {
"bool": {
"filter": [
{
"range": {
"FlightTimeHour": {
"from": 5,
"to": null,
"include_lower": false,
"include_upper": false,
"boost": 1
}
}
},
{
"term": {
"OriginCountry.keyword": {
"value": "US",
"boost": 1
}
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
"_source": {
"includes": [
"OriginCityName",
"DestCityName"
],
"excludes": []
},
"sort": [
{
"FlightTimeHour": {
"order": "desc"
}
}
]
}
Sans surprise, la clause WHERE est convertie en requêtes de range et de term. Notez que la variante OriginCountry.keyword du sous-champ est utilisée pour la correspondance de terme exacte, par rapport à l'élément OriginCountry parent (qui correspond à un texte). L'utilisateur n'est pas tenu de connaître les différences de comportement du mappage sous-jacent : le bon type de champ est sélectionné automatiquement. Il est intéressant de noter que l'interface essaie d'optimiser les performances de récupération en utilisant docvalue_fields sur l'élément _source lorsque cela est possible, c'est-à-dire pour les types exacts (valeurs numériques, dates, mots-clés) avec les Doc Values activées. Vous pouvez compter sur Elasticsearch SQL pour générer le DSL le plus optimal pour la requête spécifiée.
À présent, prenons l'exemple de la requête la plus complexe que nous avons utilisée :
POST _xpack/sql/translate
{
"query":"SELECT timestamp, FlightNum, OriginCityName, DestCityName, ROUND(DistanceMiles) AS distance, ROUND(DistanceMiles/FlightTimeHour) AS speed, DAY_OF_WEEK(timestamp) AS day_of_week FROM flights WHERE DAY_OF_WEEK(timestamp) >= 0 AND DAY_OF_WEEK(timestamp) <= 2 AND HOUR_OF_DAY(timestamp) >=9 AND HOUR_OF_DAY(timestamp) <= 10 ORDER BY speed DESC, distance DESC LIMIT 2"
}
Et la réponse…
{
"size": 2,
"query": {
"bool": {
"filter": [
{
"bool": {
"filter": [
{
"script": {
"script": {
"source": "(params.v0 <= doc[params.v1].value.getDayOfWeek()) && (doc[params.v2].value.getDayOfWeek() <= params.v3)",
"lang": "painless",
"params": {
"v0": 0,
"v1": "timestamp",
"v2": "timestamp",
"v3": 2
}
},
"boost": 1
}
},
{
"script": {
"script": {
"source": "doc[params.v0].value.getHourOfDay() >= params.v1",
"lang": "painless",
"params": {
"v0": "timestamp",
"v1": 9
}
},
"boost": 1
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
{
"script": {
"script": {
"source": "doc[params.v0].value.getHourOfDay() <= params.v1",
"lang": "painless",
"params": {
"v0": "timestamp",
"v1": 10
}
},
"boost": 1
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
"_source": false,
"stored_fields": "_none_",
"docvalue_fields": [
"timestamp",
"FlightNum",
"OriginCityName",
"DestCityName",
"DistanceMiles",
"FlightTimeHour"
],
"sort": [
{
"_script": {
"script": {
"source": "Math.round((doc[params.v0].value) / (doc[params.v1].value))",
"lang": "painless",
"params": {
"v0": "DistanceMiles",
"v1": "FlightTimeHour"
}
},
"type": "number",
"order": "desc"
}
},
{
"_script": {
"script": {
"source": "Math.round(doc[params.v0].value)",
"lang": "painless",
"params": {
"v0": "DistanceMiles"
}
},
"type": "number",
"order": "desc"
}
}
]
}
Nos clauses WHERE et ORDER BY ont été converties en scripts Painless et utilisées dans les requêtes sort et script fournies par Elasticsearch. Ces scripts sont même paramétrés de manière à éviter les compilations et à exploiter la mise en cache du script.
Notons que, bien que la structure ci-dessus représente la traduction la plus optimale pour la déclaration SQL, elle ne représente pas la meilleure solution pour le problème dans son intégralité. En réalité, il faudrait chiffrer le jour de la semaine, l'heure de la journée et la vitesse sur le document au moment de l'index. Cela nous permettrait d'utiliser des requêtes de plages simples. Cette solution peut s'avérer légèrement plus performante que l'utilisation de scripts Painless pour résoudre ce problème précis. Certains de ces champs sont en fait déjà présents dans le document, pour cette raison. C'est un thème courant auquel les utilisateurs doivent prêter attention : nous pouvons compter sur l'implémentation Elasticsearch SQL pour obtenir une traduction optimale, cependant, elle n'utilise que les champs spécifiés dans la requête et ne fournit donc pas nécessairement la meilleure solution pour le problème dans son ensemble. Il est impératif de tenir compte des points forts de la plateforme sous-jacente pour obtenir une approche optimale. L'API _translate représente alors une première étape de ce processus.
À suivre
Dans l'article Introduction pratique à Elasticsearch – 2e partie, nous continuerons à utiliser l'API _translate afin d'illustrer des fonctions plus complexes d'Elasticsearch SQL. Nous nous intéresserons également aux limitations auxquelles les utilisateurs peuvent être confrontés dans la première version. Enfin, vous aurez un aperçu des projets à venir.