Introducción a Elasticsearch SQL y ejemplos prácticos: Parte 1
La versión 6.3 del Elastic Stack representó uno de los lanzamientos con más funcionalidades que se recuerden. Por si la apertura de X-Pack y la adición de paquetes acumulativos no fuera suficiente para emocionarse, anunciamos la adición de la compatibilidad con SQL como función experimental. Esta representa una de las funcionalidades más buscadas y solicitadas en la historia relativamente corta de Elasticsearch.
En esta serie del blog exploraremos algunas de las funcionalidades y capacidades que Elasticsearch SQL admite actualmente. También analizaremos abiertamente algunas limitaciones y abordaremos planes para el futuro.
Destinatarios
Históricamente, en Elastic se ha dudado en añadir SQL al producto por varios motivos. Algunas de las interrogantes son las siguientes:
- ¿En qué consistiría la compatibilidad con SQL?
- ¿Qué funcionalidades se admitirían? Con frecuencia, esto se ha expresado como "¿Se admiten sentencias JOIN?
- ¿expresiones/funciones? ¿agrupamientos?"
- ¿Es necesario admitir conectividad con JDBC/ODBC?
- ¿Acaso es necesario admitir SQL o es que simplemente no estamos ofreciendo el material suficiente para que los nuevos usuarios se familiaricen con nuestro propio lenguaje específico de dominio (DSL, por sus siglas en inglés)?
Después de varias iniciativas, logramos restringir con éxito las características requeridas a lo que sentimos que sería útil para nuestros usuarios. Con base en las discusiones con ellos, creemos que SQL será más valioso para dos destinatarios principales:
- Los nuevos usuarios que pudieran sentir que el DSL de Elasticsearch es un poco abrumador, o que simplemente no necesitan aprender la sintaxis completa. Por ejemplo, puede ser que un usuario convierta una aplicación existente basada en SQL por motivos de rendimiento y capacidad de escalamiento, y que tan solo quiera la consulta equivalente sin tener que aprender toda la sintaxis. También apreciamos la estrategia de aprendizaje común para aprender "lenguajes" nuevos al buscar equivalencias basadas en lo que ya se sabe.
- Los consumidores de datos que realmente no desean ni necesitan aprender íntegramente el DSL de Elasticsearch. Estos usuarios pueden ser científicos de datos que simplemente quieran extraer los datos para procesamiento externo. Alternativamente, tal vez sean usuarios de información comercial, menos técnicos, que están familiarizados a nivel general con SQL y lo usan de manera cotidiana.
Además de los destinatarios anteriores, tampoco se puede refutar que, como lenguaje declarativo, SQL es un paradigma sumamente atractivo para todos los usuarios, como se demostrará con frecuencia en este blog. Sin lugar a dudas, la prevalencia de SQL se basa en su capacidad de expresar la lógica de la computación y lo que estás tratando de lograr sin necesidad de definir su flujo de control. Asimismo, como demostraremos, algunas consultas de SQL definen con elegancia un problema que puede ser un poco mas complejo con el DSL equivalente de Elasticsearch. Si bien el DSL de Elasticsearch describe de manera refinada problemas de búsqueda de texto completo, SQL puede ser más eficaz en la descripción de consultas estructuradas basadas en análisis.
Qué es y qué no es Elasticsearch SQL
En esta versión inicial, Elasticsearch SQL proporciona una interfaz de solo lectura que cumple con un subconjunto de la especificación ANSI SQL y permite la representación de Elasticsearch en una fuente tabular. También proporcionamos operadores adicionales que exceden a esta especificación, lo que expone capacidades únicas de Elasticsearch en comparación con las implementaciones basadas en RDBMS. Nuestro objetivo fue proporcionar una implementación liviana y rápida que minimiza las piezas y dependencias externas. Bajo ningún punto de vista, esta primera oferta transforma Elasticsearch en una base de datos totalmente relacional (con las propiedades asociadas) o reemplaza la necesidad de modelado de datos. Si bien el complemento SQL implementa algunas funciones y expresiones de manipulación de datos, se respeta un principio de push down cuando el orden y el recuento de los resultados se ve afectado o se requiere agrupación. Esto limita el procesamiento actual de datos del complemento Elasticsearch SQL a la manipulación de datos únicamente (p. ej., funciones de los campos) y al cliente (controlador JDBC/CLI o navegador) a representarlos. Este enfoque explota la capacidad de escalamiento y el rendimiento de Elasticsearch, lo que le permite hacer el trabajo pesado.
Correspondencia de conceptos: índices y documentos comparados con tablas y filas
En los inicios de Elasticsearch, con frecuencia se hacía referencia a los índices y tipos como análogos a bases de datos RDBMS y tablas, principalmente para ayudar a los usuarios a entender y trabajar con conceptos posiblemente nuevos. Como se explicó en la documentación de Elasticsearch 6.3, esto fue una analogía incorrecta y potencialmente peligrosa. Aunque estamos eliminando tipos, aún necesitamos una equivalencia lógica correcta y utilizable entre el modelo de Elasticsearch sin esquemas, orientado a documentos, y los conceptos de SQL con presencia importante de tipos.
Afortunadamente, los índices de Elasticsearch, tales como las tablas RDBMS, se encuentran aislados físicamente y deben usarse, a grandes rasgos, de la misma forma (es decir, para almacenar datos relacionados). Las filas y documentos también son una analogía natural, toda vez que proporcionan un mecanismo para agrupar campos/columnas, aunque las filas tienden a ser estrictas (y tienen más imposiciones), mientras que un documento tiende a ser un poco más flexible o libre (sin dejar de tener una estructura). Los campos en Elasticsearch representan una entrada de nombre y admiten varios tipos de datos que contienen potencialmente múltiples valores (es decir, una lista). Salvo por estos campos de múltiples valores, este concepto se corresponde directamente con las columnas de SQL. Nota: si intentas hacer una selección, SELECT, en un campo de múltiples valores, recibirás un error en el momento de la consulta.
Otros conceptos no se corresponden tan directamente: una base de datos de SQL y un clúster de Elasticsearch tienen una equivalencia solo aproximada. Sin embargo, estos no afectarán al usuario de Elasticsearch SQL. Puedes encontrar más información sobre este tema en nuestro documento sobre Correspondencia de conceptos entre SQL y Elasticsearch.
En resumen, usa el nombre de índice en tu cláusula WHERE para enfocarte en un índice o tabla específicos. Puedes prever que se devuelvan documentos únicos como filas y campos asignados como columnas. Debido a esta correspondencia en gran medida transparente, usamos estos términos de manera intercambiable para continuar.
En resumen, usa el nombre de índice en tu cláusula WHERE para enfocarte en un índice o tabla específicos. Puedes prever que se devuelvan documentos únicos como filas y campos asignados como columnas. Debido a esta correspondencia en gran medida transparente, usamos estos términos de manera intercambiable para continuar.
Componentes internos de la implementación
La implementación de Elasticsearch SQL consiste en 4 fases de ejecución:
La fase de Análisis sintáctico es responsable de convertir la consulta de SQL en un Árbol de Sintaxis Abstracto (AST, por sus siglas en inglés). En esta etapa se realiza toda validación de la sintaxis, antes de que el analizador valide el AST y resuelva cualquier tabla, columna, función, alias y espacios de nombres para producir un plan lógico. Este plan se optimiza y se elimina cualquier expresión redundante antes de ser convertido en un plan físico (es decir, DSL de Elasticsearch) para la ejecución. El Ejecutor de consultas ejecuta entonces la consulta real y transmite los resultados al cliente, realizando cualquier conversión de tipo y tabular que se requiera, por ejemplo, árbol de agregación a tabla.
Métodos de conectividad
Al ofrecer una solución de SQL, es primordial considerar la admisión de conectividad. Si bien una interfaz solo REST puede ser aceptable para algunos usuarios, la mayoría espera ser capaz de conectarse mediante interfaces estándar, generalmente JDBC y ODBC. Aunque se tiene previsto admitir ODBC y se está desarrollando activamente, JDBC está disponible con esta primera versión y ya puede descargarse.
Cabe destacar que todas las comunicaciones con estos controladores seguirán siendo mediante HTTP a través de nuestra interfaz REST. Esto tiene varias ventajas atractivas:
- Otorgar acceso a SQL a tus usuarios no es distinto a la apertura y exposición de un puerto de Elasticsearch que se integre nativamente con seguridad. Por lo tanto, también somos capaces de admitir SQL inmediatamente en nuestro Servicio de Elasticsearch alojado en Elastic Cloud, lo que da a los usuarios existentes la capacidad de combinar con permisos de control de acceso.
- Esto nos permite utilizar SQL directamente a través de la interfaz REST y lanzar un cliente CLI adicional por comodidad. Esperamos que este último sea especialmente popular con los administradores, quienes estarán familiarizados con los medios de interacción por líneas de comandos, comunes en RDBMS.
El controlador JDBC utiliza la recién creada librería XContent, responsable del análisis sintáctico de consultas y respuestas (históricamente, este código estaba acoplado estrechamente con Elasticsearch). Esto evita la necesidad de que el controlador dependa de todas las librerías de Elasticsearch, con lo que se garantiza que siga siendo ligero y portátil. Este desacoplamiento seguirá mejorándose en versiones futuras para dar lugar a un controlador más pequeño y rápido.
Algunos ejemplos sencillos
Veamos algunos ejemplos en los que se utiliza una mezcla de CLI y la API REST. Para nuestros ejemplos, usaremos un conjunto de datos de muestra que se distribuirá en breve con Kibana. Si no puedes esperar el lanzamiento, este conjunto de datos de vuelo está disponible también en demo.elastic.co, en donde puedes ejecutar los siguientes ejemplos mediante la consola de Kibana. Facilitamos enlaces a demo.elastic.co que deben autorrellenarse con la consulta pertinente en todo el blog. De manera alternativa, ofrecemos la lista completa de consultas para ejecutarse en la consola de Kibana de demostración. En algunos casos, los resultados pueden variar si no hay un ordenamiento o restricción explícitos en la consulta de ejemplo, debido al ordenamiento natural de los resultados en Elasticsearch cuando no se aplica ningún orden de relevancia o clasificación.
Recuperación de la información de esquema de Elasticsearch: DSL comparado con SQL
Identifiquemos primero el esquema de la tabla/índice y los campos disponibles para manipular. Haremos esto a través de la interfaz REST:
Solicitud
POST _xpack/sql
{
"query":"DESCRIBE flights"
}
Respuesta
{
"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" ]
]
}
A la respuesta anterior también se le puede dar un formato tabular mediante el parámetro url ?format=txt. Por ejemplo:
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
Al avanzar, cuando se proporciona una respuesta de ejemplo de la API REST, usamos la estructura de respuesta tabular mostrada anteriormente. Para lograr la misma consulta mediante la consola es necesario que iniciemos sesión con:
./elasticsearch-sql-cli http://elastic@localhost:9200
Después de responder a la solicitud de contraseña...
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>
El esquema anterior también se devuelve con cualquier consulta de los campos mostrados en la cláusula SELECT, lo que proporciona a cualquier controlador potencial la información de tipos necesarios que se requiere para dar formato a los resultados o manejarlos. Por ejemplo, considera un SELECT simple con una cláusula LIMIT para mantener la respuesta breve. De manera predeterminada, se devolverán 1000 filas.
SELECT simple
POST _xpack/sql?format=txt
{
"query":"SELECT FlightNum FROM flights LIMIT 1"
}
Prueba en demo.elastic.co (los resultados pueden variar)
FlightNum --------------- 1Y0TZOE
El controlador JDBC y la consola consumen esta misma solicitud/respuesta REST, pero se oculta al usuario.
sql> SELECT OriginCountry, OriginCityName FROM flights LIMIT 1; OriginCountry |OriginCityName ---------------+--------------- US |San Diego
Prueba en demo.elastic.co (los resultados pueden variar)
Comprueba si en cualquier momento solicitas un campo que no existe (distinción entre mayúsculas y minúsculas), la semántica de un almacén tabular y con fuerte presencia de tipos significa que se devolverá un error. Esto difiere del comportamiento de Elasticsearch, en donde el campo simplemente no será devuelto. Por ejemplo, al modificar lo anterior para usar el campo "OrigincityName" en vez de «OriginCityName», el resultado será un mensaje de error útil:
{
"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
}
Del mismo modo, si intentamos usar una función o expresión en un campo que no es compatible, se nos presentará un error pertinente. En general, el Analizador fallará temprano al validar el AST. Para lograr esto, Elasticsearch debe estar al tanto de la asignación de índices y las capacidades de cada campo. Por este motivo, todo cliente que acceda a la interfaz de SQL con seguridad necesitará los permisos adecuados.
Si continuamos proporcionando todas las solicitudes y sus respuestas adjuntas, acabaremos con una entrada de blog extremadamente larga. Para fines de brevedad, he aquí algunas consultas de complejidad creciente con notas de interés.
SELECT con WHERE y ORDER BY
"Encuentra los 10 vuelos estadounidenses más largos, en los que el tiempo de vuelo sea mayor a 5 horas".
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
El operador para restringir los recuentos de filas varía con la implementación de SQL. En el caso de Elasticsearch SQL, nos apegamos a Postgresql/Mysql en la implementación del operador LIMIT.
Cálculos
Solo algunos cálculos al azar…
sql> SELECT ((1 + 3) * 1.5 / (7 - 6)) * 2 AS random;
random
---------------
12.0
Esto representa un ejemplo de dónde el lado del servidor realiza cierto procesamiento posterior de las funciones. Esto no tiene una consulta equivalente en el DSL de Elasticsearch.
Funciones y expresiones
"Encuentra todos los vuelos posteriores al mes de junio cuyos tiempos de vuelo hayan sido de más de 5 horas y ordénelos empezando por el más largo".
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
Estas funciones requerirían típicamente escribir en Painless para obtener el resultado equivalente en Elasticsearch, aunque las declarativas funcionales de SQL evitan todo tipo de scripts. Nota también cómo podemos usar la función tanto en la cláusula WHERE como en SELECT. El componente WHERE es empujado hacia abajo en Elasticsearch a medida que afecta el recuento de resultados. No obstante, la función SELECT es manejada por el complemento del lado del servidor en la presentación.
Nota que es posible recuperar una lista de las funciones disponibles con "SHOW FUNCTIONS
Al combinar esto con nuestra anterior capacidad de cálculo, podemos empezar a formular consultas cuya articulación sería compleja para la mayoría de usuarios en DSL.
"Encuentra la distancia y la velocidad promedio de los 2 vuelos más rápidos (velocidad) que salen los lunes, martes o miércoles entre las 9 y las 11 horas, y que también son de más de 500 km. Redondea la distancia y la velocidad al entero más cercano. Si la velocidad es igual, muestra primero el más largo".
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
Una pregunta bastante intrincada y extraña, pero esperamos que entiendas su finalidad. Nota también cómo creamos alias de campos y nos referimos a estos en la cláusula ORDER BY.
Observa también que no es un requisito especificar todos los campos en la cláusula SELECT que se usan en WHERE y ORDER BY. Esto probablemente difiere de las implementaciones de SQL que usaste anteriormente. Por ejemplo, lo siguiente sería perfectamente válido:
POST _xpack/sql
{
"query":"SELECT timestamp, FlightNum FROM flights WHERE AvgTicketPrice > 500 ORDER BY AvgTicketPrice"
}
Traducir las consultas de SQL al DSL
Todos hemos tenido una consulta de SQL con la que hemos batallado para expresarla en el DSL de Elasticsearch o nos hemos preguntado si es tan óptima posible. Una de las características atractivas de la nueva interfaz de SQL es su capacidad de auxiliar a los nuevos usuarios de Elasticsearch en tales problemas. Al usar la interfaz REST simplemente pegamos /translate en el punto de conexión con "SQL" para obtener la consulta de Elasticsearch que el controlador emitiría.
Consideremos algunas de las consultas previas:
POST _xpack/sql/translate
{
"query":"SELECT OriginCityName, DestCityName FROM flights WHERE FlightTimeHour > 5 AND OriginCountry='US' ORDER BY FlightTimeHour DESC LIMIT 10"
}
El DSL equivalente debería ser bastante obvio para cualquier usuario experimentado de 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"
}
}
]
}
La cláusula WHERE se convierte en consultas de intervalo y de término, como podrías esperar. Ve cómo la variante OriginCountry.keyword del subcampo se usa para la coincidencia exacta del término, frente al original OriginCountry (cuyo tipo es de texto). No es necesario que el usuario conozca las diferencias en el comportamiento de la asignación subyacente: el tipo de campo correcto se selecciona automáticamente. Curiosamente, la interfaz intenta optimizar el rendimiento de la recuperación al usar docvalue_fields en vez de _source cuando están disponibles, es decir, para tipos exactos (numéricos, de datos, palabras clave) con los valores de documento habilitados. Podemos confiar en que Elasticsearch SQL generará el DSL óptimo para la consulta especificada.
Ahora consideremos la consulta más compleja que usamos la última vez:
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"
}
Y la respuesta...
{
"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"
}
}
]
}
Nuestras cláusulas de WHERE y ORDER BY se han convertido en scripts en Painless, y se han usado en la clasificación y la consulta de scripts proporcionadas por Elasticsearch. Estos scripts están incluso parametrizados para evitar compilaciones y explotar el almacenamiento en caché de los scripts.
Como nota al margen, si bien lo anterior representa la traducción óptima del enunciado de SQL, no constituye la mejor solución para el problema en general. En realidad querríamos codificar el día de la semana, la hora del día y la velocidad en el documento al momento de indexar la información, lo que nos permitiría usar tan solo consultas de intervalos simples. Esto podría ser más eficaz que usar scripts en Painless para resolver este problema particular. Algunos de esos campos ya están de hecho presentes en el documento por este motivo. Esto es un tema común del que los usuarios deberían estar alerta: aun cuando podemos confiar en que la implementación de Elasticsearch SQL nos proporcionará la traducción óptima, solo puede usar los campos especificados en la consulta, por lo que no puede facilitar necesariamente una solución óptima al problema mayor. Es necesario considerar las fortalezas de la plataforma subyacente para lograr el enfoque óptimo, y la API _translate puede ser el primer paso en este proceso.
La próxima vez
En la Introducción práctica a Elasticsearch, parte II, continuaremos usando la API _translate a medida que demostramos algunas funcionalidades de Elasticsearch SQL más complejas. También revisaremos algunas de las limitaciones que los usuarios posiblemente encuentren en esta primera versión. Después de eso, ofreceré un adelanto de lo que está en la hoja de ruta.