Einführung in Elasticsearch SQL mit praktischen Beispielen – Teil 1
Version 6.3 von Elastic Stack bietet mehr neue Features als fast alle vorherigen Versionen. Wir haben nicht nur Code und Supportstrukturen für X-Pack geöffnet und Rollups hinzugefügt, sondern bieten nun auf vielfachen Wunsch der Elasticsearch-Anwender SQL-Support als experimentelles Feature an.
In dieser Blogreihe erkunden wir einige der derzeit von Elasticsearch SQL unterstützten Features und Funktionen und sprechen über die wenigen derzeit vorhandenen Einschränkungen sowie über unsere Zukunftspläne.
Zielpublikum
Bei Elastic wurde lange und aus einer Vielzahl von Gründen gezögert, das Produkt durch SQL zu ergänzen. Zu den häufigsten Fragen gehörten:
- Was deckt die SQL-Unterstützung ab?
- Welche Features werden unterstützt? Konkret: Werden JOINs unterstützt?
- Und wie sieht es mit Ausdrücken/Funktionen oder dem Gruppieren aus?
- Müssen wir die JDBC-/ODBC-Konnektivität unterstützen?
- Müssen wir denn überhaupt SQL unterstützen oder stellen wir unseren Anwendern einfach nur nicht genügend Dokumentation und Hilfe für den Einstieg in unsere DSL (Domain Specific Language) bereit?
Nach mehreren Vorstößen gelang es uns, die erforderlichen Features auf die unserem Ermessen nach für die Anwender nützlichen Features zu beschränken. In Gesprächen mit unseren Anwendern kristallisierten sich zwei Hauptzielgruppen für SQL heraus:
- Neue Anwender von Elastic Stack, die sich von Elasticsearch DSL als Ausgangspunkt überfordert fühlen oder die einfach nicht die vollständige Syntax erlernen möchten: Wenn Anwender beispielsweise eine vorhandene SQL-basierte Anwendung aus Performance- und Skalierbarkeitsgründen konvertieren, benötigen sie möglicherweise einfach nur die entsprechende Abfrage, ohne dazu die komplette Syntax erlernen zu müssen. Wir sind uns zudem bewusst, dass eine erfolgreiche und verbreitete Lernstrategie darin besteht, Neues zu erlernen, in dem wir einen Bezug zu bereits vorhandenem Wissen herstellen.
- Datenkonsumenten, die Elasticsearch DSL weder erlernen möchten noch müssen: Hierzu gehören z. B. Datenwissenschaftler, die einfach nur die Daten zur externen Verarbeitung extrahieren möchten, oder weniger technisch versierte BI-Anwender, die weitestgehend mit SQL vertraut sind und diese Datenbanksprache tagtäglich einsetzen.
SQL ist nicht nur für die oben genannten Zielgruppen interessant, sondern auch als deklarative Sprache für alle Anwender ein äußerst attraktives Programmierparadigma, wie diese Blogreihe immer wieder veranschaulichen wird. Die Vorrangstellung von SQL beruht auf der Fähigkeit, die Berechnungslogik und das von Ihnen angestrebte Ziel ohne vorherige Definition des Kontrollflusses auszudrücken. Wir führen zudem vor, wie Probleme, die sich mit Elasticsearch DSL nur umständlich ausdrücken lassen, mit bestimmten SQL-Abfragen auf elegante Weise definiert werden können. Die Stärke von Elasticsearch DSL liegt in der geschickten Beschreibung von Volltext-Suchproblemen, während SQL strukturierte, analysebasierte Abfragen wirkungsvoller beschreiben kann.
Funktionsumfang von Elasticsearch SQL
Elasticsearch SQL bietet in dieser ersten Version eine schreibgeschützte Benutzeroberfläche, die einer Untergruppe der ANSI SQL-Spezifikation entspricht und die Darstellung von Elasticsearch als tabellarische Datenquelle ermöglicht. Darüber hinaus bieten wir zusätzliche Operatoren, die im Gegensatz zu RDBMS-basierten Implementierungen Elasticsearch-spezifische Funktionalität ermöglichen. Unser Ziel ist eine leichte, schnelle Implementierung mit minimalen externen Abhängigkeiten und wenig beweglichen Komponenten. Hierdurch wird jedoch weder Elasticsearch zu einer umfassenden relationalen Datenbank (mit den dazugehörigen Eigenschaften) noch wird die Datenmodellierung überflüssig. Während einige Datenbearbeitungsfunktionen und -ausdrücke durch das SQL-Plugin implementiert werden, wird das Pushdown-Prinzip immer dann angewendet, wenn der Ergebniswert und die Ergebnisreihenfolge beeinträchtigt sind oder eine Gruppierung angefordert wird. Auf diese Weise werden die derzeitige Verarbeitung von Daten durch das Elasticsearch SQL-Plugin auf die reine Ergebnisbearbeitung (z. B. auf Feldfunktionen) und der Client (JDBC-Treiber/CLI oder Browser) auf das reine Rendering beschränkt. Bei diesem Ansatz wird die Skalierbarkeit und Performance von Elasticsearch genutzt, um schwierige Arbeiten für Sie zu erledigen.
Mappingkonzepte: Indizes und Dokumente oder Tabellen und Zeilen
In der Anfangsphase von Elasticsearch wurden Indizes und Typen oft mit relationalen Datenbanken und Tabellen in einem RDBMS verglichen, um Anwender an die für sie möglicherweise neuen Konzepte heranzuführen und ihnen die Arbeit damit zu erleichtern. Wie bereits in der Dokumentation zu Elasticsearch 6.3 erläutert, war diese Analogie nicht nur falsch und irreführend, sondern möglicherweise sogar gefährlich. Wir entfernen zwar Typen aus Elasticsearch, doch wir benötigen weiterhin eine passende und verwendbare logische Entsprechung zwischen dem schemalosen, dokumentenorientierten Elasticsearch-Modell und den stark typisierten SQL-Konzepten.
Erfreulicherweise sind Elasticsearch-Indizes ähnlich wie RDBMS-Tabellen physisch isoliert und sollten weitestgehend auf die gleiche Weise genutzt werden (d. h. zum Speichern zugehöriger Daten). Obwohl Zeilen eher strikt (stärkere Erzwingungen) und Dokumente dagegen etwas flexibler/lockerer sind (ohne ihre Struktur zu verlieren), sind Zeilen und Dokumente ebenfalls eine natürliche Entsprechung, da sie es ermöglichen, Felder/Spalten zu gruppieren. In Elasticsearch stellen Felder einen Namenseintrag dar und unterstützen verschiedene Datentypen, denen möglicherweise auch eine Liste von Werten zugeordnet sein kann. Mit Ausnahme dieser mehrwertigen Felder ist dieses Konzept direkt SQL-Spalten zugewiesen. Hinweis: Wird die Anweisung SELECT für ein mehrwertiges Feld ausgeführt, wird zur Abfragezeit eine Fehlermeldung ausgegeben.
Bei anderen Konzepten ist das Mapping nicht so direkt: Eine SQL-Datenbank und ein Elasticsearch-Cluster haben nur eine geringe Äquivalenz. Dies stört jedoch Elasticsearch SQL-Anwender generell nicht. Weitere Informationen zu diesem Thema enthält die Dokumentation Mapping concepts across SQL and Elasticsearch (Mapping von Konzepten zwischen SQL und Elasticsearch).
Kurz gesagt: Verwenden Sie den Indexnamen in der Klausel WHERE, um die Auswahlbedingungen für einen bestimmten Index bzw. eine bestimmte Tabelle festzulegen. Die spezifischen Dokumente werden dann jeweils als eine Zeile zurückgegeben und die Felder werden als Spalten zugewiesen. Durch dieses weitestgehend transparente Mapping können wir diese Begriffe im Folgenden synonym verwenden.
Kurz gesagt: Verwenden Sie den Indexnamen in der Klausel WHERE, um die Auswahlbedingungen für einen bestimmten Index bzw. eine bestimmte Tabelle festzulegen. Die spezifischen Dokumente werden dann jeweils als eine Zeile zurückgegeben und die Felder werden als Spalten zugewiesen. Durch dieses weitestgehend transparente Mapping können wir diese Begriffe im Folgenden synonym verwenden.
Einzelheiten zur Implementierung
Die Elasticsearch SQL-Implementierung umfasst vier Ausführungsphasen:
Vom Parser wird die SQL-Abfrage in einen Abstrakten Syntaxbaum (Abstract Syntax Tree, AST) konvertiert. Jegliche Syntaxvalidierung erfolgt in dieser Phase, bevor der Analyzer den Abstrakten Syntaxbaum validiert und alle Tabellen, Spalten, Funktionen, Aliase und Namensfelder auflöst, um daraus einen logischen Plan zu erstellen. Dieser logische Plan wird vor der Umwandlung in einen ausführbaren physischen Plan (d. h. Elasticsearch DSL) zunächst optimiert und redundante Ausdrücke werden entfernt. Der Query Executor führt dann die eigentliche Abfrage durch und streamt die Ergebnisse zum Client. Dabei werden nach Bedarf alle erforderlichen Typen- und Tabellenkonvertierungen durchgeführt, wie z. B. die Konvertierung des Aggregationsbaums in eine Tabelle.
Konnektivitätsmethoden
Bei der Bereitstellung von SQL-Lösungen ist die Unterstützung der Konnektivität ein ausschlaggebender Faktor. Eine reine REST-Schnittstelle mag zwar für einige Anwender akzeptabel sein, die meisten Anwender erwarten jedoch, eine Verbindung über Standardschnittstellen herstellen zu können – üblicherweise über JDBC und ODBC. Die Unterstützung von ODBC ist geplant und wird derzeit entwickelt, JDBC ist dagegen bereits ab diesem ersten Release verfügbar und kann heruntergeladen werden.
Wichtig: Jegliche Kommunikation mit diesen Treibern verläuft auch weiterhin über HTTP und unsere REST-Schnittstelle. Dies bietet eine Reihe von Vorteilen:
- Die Vorgehensweise zum Gewähren von SQL-Zugang für Ihre Anwender unterscheidet sich nicht vom Öffnen und Verfügbarmachen eines Elasticsearch-Ports mit seiner systemeigenen Integration in Sicherheitsfunktionen. Wir sind daher in der Lage, SQL sofort in unserem gehosteten Elasticsearch Service in der Elastic Cloud bereitzustellen, und Bestandsanwender können dies mit den OOTB-Zugangskontrollberechtigungen kombinieren.
- Auf diese Weise können wir SQL direkt über die REST-Schnittstelle nutzen und einen zusätzlichen CLI-Client bereitstellen, um den Anwendungskomfort zu erhöhen. Wir erwarten, dass der CLI-Client besonders Administratoren ansprechen wird, die bereits mit der in RDBMS üblichen Interaktion über die Befehlszeile vertraut sind.
Der JDBC-Treiber nutzt die neu erstellte XContent-Bibliothek, die für das Parsen von Abfragen und Antworten zuständig ist (historisch war dieser Code eng an Elasticsearch gebunden). Auf diese Weise ist der Treiber nicht von allen Elasticsearch-Bibliotheken abhängig und bleibt so leicht und portabel. Die Entkopplung wird weiterhin ständig verbessert, sodass der Treiber in zukünftigen Versionen kleiner und schneller sein wird.
Einige einfache Beispiele
Schauen wir uns einige Beispiele an, die eine Kombination aus CLI und REST-API verwenden. Für unsere Beispiele verwenden wir einen Beispieldatensatz, der in Kürze mit Kibana mitgeliefert werden wird. Wenn Sie nicht so lange warten möchten, können Sie diesen Flug-Datensatz auch unter demo.elastic.co finden. Dort können Sie die folgenden Beispiele über die Kibana-Konsole ausführen. Wir stellen Links zu demo.elastic.co bereit, die im gesamten Blog automatisch mit der relevanten Abfrage vervollständigt werden sollten. Alternativ stellen wir eine vollständige Liste der Fragen bereit, die in der Kibana-Demokonsole ausgeführt werden können. In einigen Fällen fallen die Ergebnisse unterschiedlich aus, falls keine explizite Sortierung oder Einschränkung der Beispielabfrage angegeben wird. Dies wird durch die natürliche Sortierung der Ergebnisse in Elasticsearch bedingt, die erfolgt, wenn keine Relevanz oder Sortierreihenfolge angewendet wird.
Abrufen von Elasticsearch-Schemainformationen: Vergleich von DSL und SQL
Identifizieren wir zunächst das Schema der Tabelle/des Indexes sowie die Felder, die uns zur Verfügung stehen. Dies geschieht über die REST-Schnittstelle:
Anfrage
POST _xpack/sql
{
"query":"DESCRIBE flights"
}
Probieren Sie es auf demo.elastic.co aus.
Antwort
{
"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" ]
]
}
Die oben aufgeführte Antwort kann durch den URL-Parameter ?format=txt auch tabellarisch formatiert werden. Beispiel:
POST _xpack/sql?format=txt
{
"query":"DESCRIBE flights"
}
Probieren Sie es auf demo.elastic.co aus.
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
Künftig werden wir die oben dargestellte tabellarische Antwortstruktur immer dann verwenden, wenn wir eine exemplarische Antwort von der REST API bereitstellen. Zur Archivierung dieser Abfrage über die Konsole müssen wir uns unter Verwendung der folgenden Anmeldeinformationen anmelden:
./elasticsearch-sql-cli http://elastic@localhost:9200
Nach der Reaktion auf die Kennwort-Eingabeaufforderung ...
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>
Das zuvor abgebildete Schema wird zudem bei jeder Abfrage für die in der Anweisung SELECT dargestellten Felder zurückgegeben. Auf diese Weise erhalten alle potenziellen Treiber die zum Formatieren der Ergebnisse oder zum Arbeiten mit den Ergebnissen erforderlichen Typeninformationen. Betrachten Sie beispielsweise ein einfaches SELECT, bei dem die Antwort mit der Klausel LIMIT kurzgehalten wird. Standardmäßig werden 1000 Zeilen zurückgegeben.
Einfache Anweisung SELECT
POST _xpack/sql?format=txt
{
"query":"SELECT FlightNum FROM flights LIMIT 1"
}
Probieren Sie es auf demo.elastic.co aus (unterschiedliche Ergebnisse möglich).
FlightNum --------------- 1Y0TZOE
Diese REST-Anfrage/-Antwort wird vom JDBC-Treiber und der Konsole verarbeitet, bleibt dem Anwender jedoch verborgen.
sql> SELECT OriginCountry, OriginCityName FROM flights LIMIT 1; OriginCountry |OriginCityName ---------------+--------------- US |San Diego
Probieren Sie es auf demo.elastic.co aus (unterschiedliche Ergebnisse möglich).
Hinweis: Falls Sie eine Anfrage für ein Feld stellen, das nicht vorhanden ist (Groß-/Kleinschreibung beachten), wird aufgrund der für einen tabellarischen Datenspeicher mit strikten Typen geltenden Semantik ein Fehler zurückgegeben. Elasticsearch zeigt hingegen ein anderes Verhalten: Das betroffene Feld wird einfach nicht zurückgegeben. Wird beispielsweise in der vorherigen Anfrage der Feldname „OrigincityName“ statt des Feldnamens „OriginCityName“ verwendet, wird die folgende Fehlermeldung mit nützlichen Informationen ausgegeben:
{
"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
}
Probieren Sie es auf demo.elastic.co aus.
Falls wir versuchen, eine Funktion oder einen Ausdruck auf ein nicht kompatibles Feld anzuwenden, wird eine entsprechende Fehlermeldung ausgegeben. Im Allgemeinen tritt bei der Validierung des Abstrakten Syntaxbaums früher ein Analyser-Fehler auf. Hierzu muss Elasticsearch das Indexmapping und die Funktionen der einzelnen Felder kennen. Aus diesem Grund muss jeder Client, der auf die gesicherte SQL-Schnittstelle zugreift, über die erforderlichen Berechtigungen verfügen.
Wir können an dieser Stelle nur einige Abfragen von zunehmender Komplexität und mit interessanten Anmerkungen vorstellen, um den Blog nicht zu langatmig zu gestalten.
Anweisung SELECT mit den Klauseln WHERE und ORDER BY
„Finde die 10 längsten Flüge von US-amerikanischen Flughäfen mit einer Flugzeit von über 5 Stunden.“
POST _xpack/sql?format=txt
{
"query":"SELECT OriginCityName, DestCityName FROM flights WHERE FlightTimeHour > 5 AND OriginCountry='US' ORDER BY FlightTimeHour DESC LIMIT 10"
}
Probieren Sie es auf demo.elastic.co aus.
OriginCityName | DestCityName ---------------+--------------- Atlanta |Durban Louisville |Melbourne Peoria |Melbourne Albuquerque |Durban Birmingham |Durban Bangor |Brisbane Seattle |Durban Huntsville |Sydney Savannah |Shanghai Philadelphia |Xi'an
Der Operator für die Beschränkung der Zeilenanzahl hängt von der jeweiligen SQL-Implementierung ab. Für Elasticsearch SQL implementieren wir den LIMIT-Operator im Einklang mit Postgresql/Mysql.
Zufällige Auswahl
Auswahl nach dem Zufallsprinzip ...
sql> SELECT ((1 + 3) * 1.5 / (7 - 6)) * 2 AS random;
random
---------------
12.0
Probieren Sie es auf demo.elastic.co aus.
Diese Darstellung zeigt exemplarisch, wie ein Teil der Nachverarbeitung von Funktionen auf der Serverseite durchgeführt wird. Es gibt hierzu keine äquivalente Elasticsearch DSL-Abfrage.
Funktionen und Ausdrücke
„Finde alle Flüge ab Juli mit einer Flugzeit von mehr als 5 Stunden und sortiere sie nach der längsten Flugzeit.“
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"
}
Try on demo.elastic.co
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
Für diese Funktionen muss Code in der Elasticsearch-Skriptsprache Painless geschrieben werden, um ein entsprechendes Ergebnis in Elasticsearch zu erzielen. In den funktionellen Prozedurvereinbarungen von SQL wird hingegen jegliches Skripting vermieden. Beachten Sie die Verwendung der Funktion sowohl in der Klausel WHERE und der Anweisung SELECT. Die Komponente der Klausel WHERE wird an Elasticsearch übergeben, da sie den Ergebniszähler beeinflusst. Die Funktion SELECT wird jedoch nach der Übermittlung vom Plug-in auf dem Server verarbeitet.
Hinweis: Mit SHOW FUNCTIONS (Funktionen anzeigen) kann eine Liste der verfügbaren Funktionen abgerufen werden.
Probieren Sie es auf demo.elastic.co aus.
Wir kombinieren dies mit unseren früheren Mathematikfunktionen und können so mit der Formulierung von Abfragen beginnen, die den meisten Anwendern in DSL aufgrund ihrer Komplexität Schwierigkeiten bereiten würden.
„Finde die Flugdistanz und die durchschnittliche Geschwindigkeit der schnellsten beiden Flüge, die montag-, dienstag- oder mittwochmorgens zwischen 9:00 und 11:00 h abfliegen und deren Flugdistanz größer als 500 km beträgt. Runde die Flugdistanz und die Geschwindigkeit auf die nächste ganze Zahl auf oder ab. Falls die Geschwindigkeit mehrerer Flüge gleich ist, zeige den Flug mit der größten Flugdistanz an.“
Probieren Sie es auf demo.elastic.co aus.
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
Die Abfrage mag recht verschachtelt und seltsam anmuten, aber sie verdeutlicht hoffentlich das hier Gesagte. Interessant ist zudem die Art, in der wir Feldaliase erstellen und in der Klausel ORDER BY auf diese verweisen.
Hinweis: Nicht alle in den Klauseln WHERE und ORDER BY verwendeten Felder müssen in der Anweisung SELECT angegeben werden. Hierdurch unterscheidet sich diese Implementierung wahrscheinlich von den zuvor von Ihnen genutzten SLQ-Implementierungen. Folgender Code ist daher uneingeschränkt gültig:
POST _xpack/sql
{
"query":"SELECT timestamp, FlightNum FROM flights WHERE AvgTicketPrice > 500 ORDER BY AvgTicketPrice"
}
Probieren Sie es auf demo.elastic.co aus.
Übertragen von SQL-Abfragen in DSL mithilfe von „translate“
Manche SQL-Abfragen lassen sich nur unter Schwierigkeiten in Elasticsearch DSL übertragen oder scheinen nicht optimal formuliert zu sein. Die neue SQL-Schnittstelle unterstützt unerfahrene Elasticsearch-Anwender bei derartigen Problemen. Über die REST-Schnittstelle hängen wir an den „sql“-Endpunkt einfach /translate an, um die vom Treiber verwendbare Elasticsearch-Abfrage zu erhalten.
Betrachten wir einige der vorherigen Abfragen:
POST _xpack/sql/translate
{
"query":"SELECT OriginCityName, DestCityName FROM flights WHERE FlightTimeHour > 5 AND OriginCountry='US' ORDER BY FlightTimeHour DESC LIMIT 10"
}
Probieren Sie es auf demo.elastic.co aus.
Das DSL-Äquivalent sollte wie folgt aussehen:
{
"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"
}
}
]
}
Die Klausel WHERE wurde erwartungsgemäß in die Abfragen range und term übertragen. Interessant ist die Art, in der die Variante „OriginCountry.keyword“ des Unterfelds an Stelle des übergeordneten „OriginCountry“ (ein Feld vom Typ „Text“) zum exakten Abgleich des Ausdrucks (Term) verwendet wird. Der Anwender braucht die Verhaltensunterschiede des zugrundeliegenden Mappings nicht zu kennen, der richtige Feldtyp wird automatisch ausgewählt. Interessanterweise versucht die Schnittstelle die Abrufleistung durch die Verwendung von „docvalue_fields“ an Stelle von „_source“ zu optimieren, sofern diese verfügbar sind, d. h. für exakte Typen (numerische Werte, Datumsangaben, Schlüsselwörter) mit aktivierten Dokumentenwerten. Elasticsearch SQL generiert zuverlässig die optimale DSL für die angegebene Abfrage.
Sehen wir uns nun die komplexeste Abfrage an, die wir letztes Mal verwendet haben:
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"
}
Probieren Sie es auf demo.elastic.co aus.
Die Antwort lautet:
{
"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"
}
}
]
}
Die Klauseln WHERE und ORDER BY wurden in Painless-Skripte konvertiert und in der von Elasticsearch bereitgestellten Abfrage sort und script eingesetzt. Diese Skripte sind gleichmäßig parametrisiert, um Kompilierungen und die Nutzung von Skriptcaching zu vermeiden.
Hinweis: Der zuvor dargestellte Code stellt zwar die optimale Übertragung der SQL-Anweisung dar, bietet jedoch nicht unbedingt die beste Lösung für das umfassendere Problem. In der Praxis würden wir den Wochentag, die Stunde und die Geschwindigkeit für das Dokument während der Indizierung kodieren, sodass wir uns auf die Verwendung einfacherer Bereichsabfragen (range) beschränken können. Auf diese Weise lässt sich dieses spezielle Problem wahrscheinlich besser und schneller lösen als durch die Verwendung von Painless-Skripten. Einige dieser Felder sind aus diesem Grund tatsächlich bereits im Dokument vorhanden. Die Elasticsearch SQL-Implementierung stellt uns zwar zuverlässig optimale Übertragungen bereit, sie kann jedoch nur die in der Abfrage angegebenen Felder verwenden und daher nicht unbedingt eine optimale Lösung für das umfassendere Problem bieten. Für einen optimalen Ansatz muss auch das Leistungsvermögen der zugrundeliegenden Plattform berücksichtigt werden. Die API _translate kann dabei als erster Schritt dieses Prozesses erachtet werden.
Thema des nächsten Blogs in dieser Reihe
In dem Blog Eine Einführung in Elasticsearch SQL mit praktischen Beispielen – Teil 2 zeigen wir weitere Einsatzmöglichkeiten für die API _translate und veranschaulichen einige komplexere Elasticsearch SQL-Features. Darüber hinaus betrachten wir derzeitige Einschränkungen und stellen einige für die Zukunft geplante Erweiterungen des Produkts vor.