Introducción a Elasticsearch SQL y ejemplos prácticos: Parte 2
En la Parte 1 de esta serie de Introducción a Elasticsearch SQL, proporcionamos una breve introducción a la nueva función de Elasticsearch SQL, así como a la API _translate. Esta publicación específica es una continuación del blog anterior, ya que explora características más complejas. Después de eso, veremos algunas de las limitaciones actuales con las que un usuario puede encontrarse en esta versión inicial de SQL. Finalmente, concluiremos con un resumen de lo que está en la hoja de ruta.
Les recordamos a todos los lectores que pueden ejecutar cualquier ejemplo proporcionado en demo.elastic.co, o bien pueden esperar a que el conjunto de datos esté disponible en Kibana. Todos los conjuntos de datos usados en esta serie se han indexado, con SQL disponible a través de la consola Kibana. Para cada ejemplo, se proporcionan ligas a demo.elastic.co. Como alternativa, todas las consultas pueden visualizarse como un solo hilo en nuestra consola de demostración Kibana.
Ejemplos complejos y ventajas de Elasticsearch
Agrupamiento
El marco de agregación de Elasticsearch, con el potencial de resumir miles de millones de datos, representa una de las características más poderosas y populares del Elastic Stack. Desde la perspectiva de la capacidad, tiene una equivalencia natural con el operador de la función GROUP BY (AGRUPAR POR) en SQL. Además de brindar algunos ejemplos de las capacidades de GROUP BY, volveremos a usar la API para mostrar las agregaciones equivalentes.
“Buscar el tiempo de vuelo promedio por país de destino para vuelos a Londres. Ordenar los países alfabéticamente”.
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
La inspección del DSL para esta consulta muestra el uso de la Composite Aggregation (Agregación compuesta).
{
"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"
}
}
}
}
}
}
También podemos agrupar los campos definidos en la selección por alias mediante una función.
“Buscar el tiempo de vuelo promedio y la cantidad de vuelos por mes”.
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
El uso de la Agregación compuesta tiene, por principio, esta ventaja: asegura que la implementación de la función GROUP BY (AGRUPAR POR) sea escalable incluso para campos de alta cardinalidad, lo que proporciona un mecanismo para transmitir todos los grupos de una agregación específica de manera similar a lo que hace la opción de desplazarse por un documento. Esto, además, garantiza que la implementación no sufra las mismas limitaciones de memoria que sufriría si utilizara los términos agregación. Sin embargo, también significa que actualmente no podemos ordenar la función GROUP BY (AGRUPAR POR) una métrica. Lo siguiente, por ejemplo, resultaría en un error:
POST _xpack/sql/translate
{
"query":"SELECT AVG(FlightTimeHour) Avg_Flight_Time, OriginCountry FROM flights GROUP BY OriginCountry ORDER BY Avg_Flight_Time"
}
Filtro de grupos
Para filtrar grupos podemos utilizar el operador HAVING (QUE TENGA), que también puede utilizar un alias especificado en la cláusula SELECT (SELECCIONAR). Esto puede ser poco habitual para algunos expertos en SQL, ya que generalmente no es posible en implementaciones basadas en RDBMS debido a que la cláusula SELECT se está ejecutando después de HAVING. Aquí, la cláusula HAVING está usando un alias declarado en la fase de ejecución. Sin embargo, nuestro analizador es lo suficientemente inteligente como para adelantarse y seleccionar la declaración para usar en HAVING.
“Buscar la cantidad, el promedio y el percentil 95 de distancia de los vuelos desde cada ciudad de origen, donde la distancia promedio sea entre 3000 y 4000 millas”.
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
Para implementar la función HAVING (QUE TENGA), SQL Elasticsearch utiliza la Bucket Selector pipeline aggregation (Agregación de procesos de Bucket Selector), que permite filtrar valores a través de un script con parámetros en Painless. Observe cómo, a continuación, la variante de la keyword (palabra clave) del campo OriginCityName (Nombre de la ciudad de origen) se selecciona automáticamente para la agregación, en lugar de intentar usar la variante de texto estándar, que probablemente fallaría debido a que los datos del campo no están habilitados. Las agregaciones de métricas avg (promedio) y percentile (percentil) proporcionan funciones equivalentes a las variantes de 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
}
}
}
}
}
}
}
Operadores de texto y relevancia
Una de las capacidades únicas de Elasticsearch como motor de búsqueda, en comparación con un RDBMS tradicional, es su habilidad para calificar coincidencias más allá de un simple sí/no, al considerar las propiedades de los datos textuales mediante un relevancy calculation (cálculo de relevancia). La extensión de la sintaxis de SQL nos permite exponer esta función e ir más allá de lo que podría proporcionar un RDBMS tradicional.
Así introducimos dos nuevos operadores: QUERY (CONSULTA) y MATCH (COINCIDENCIA). Para quienes están familiarizados con Elasticsearch, estos son equivalentes a los operadores subyacentes multi_match y query_string. Los usuarios de Kibana estarán familiarizados con el comportamiento del operador query_string, que se usa para alimentar la barra de búsqueda predeterminada. Este operador proporciona capacidades de análisis inteligente y permite un estilo de consulta en lenguaje natural. Los detalles de estos dos operadores están más allá del alcance de este blog, pero la entrada de la guía definitiva proporciona una excelente introducción a estos conceptos.
Como ejemplo, piensa en lo siguiente:
“Buscar todos los vuelos retrasados desde y hacia el aeropuerto de Kastrup entre las fechas 06-06-2018 y 17-06-2018, ordenados por fecha”.
En realidad, el aeropuerto de Kastrup está en Copenhague, y su nombre completo es "Copenhagen Kastrup Airport”. Mediante el operador QUERY (CONSULTA), podemos simplemente buscar "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
Ten en cuenta que en este caso no había ningún requisito para especificar el campo. Simplemente bastó con buscar “kastrup” mediante el operador QUERY (CONSULTA). Además, ten en cuenta que tenemos vuelos retrasados tanto to (hacia) como from (desde) Kastrup. Puedes ver la consulta de Elasticsearch aquí:
{
"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"
}
}
]
}
Para los usuarios que son nuevos en Elasticsearch, esto representa una consulta relativamente compleja. Tenemos una consulta booleana con un rango anidado, una restricción de términos y un operador de cadena de consulta. Para un usuario que migra una aplicación de SQL, tradicionalmente esto podría haber sido una tarea bastante desalentadora, incluso antes de preocuparse por el hecho de que la consulta final fuera correcta y óptima desde el punto de vista funcional. El operador query_string real se ha anidado en un filtro, ya que no se requería relevancia (ordenamos los resultados por fecha); esto nos permite explotar el caché de filtro, omitir la puntuación y mejorar el tiempo de respuesta.
Los parámetros para estos operadores también están expuestos en SQL. Este último ejemplo ilustra el uso de una consulta de MATCH (COINCIDENCIA) con múltiples términos de búsqueda en varios campos para limitar los resultados.
“Buscar vuelos hacia o desde Barcelona donde el clima incluya relámpagos”.
Para ejemplificar, también ordenamos y mostramos la puntuación de relevancia mediante la función Score() (Puntuación).
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
Usamos patrones de comodines para especificar los campos que deben coincidir y solicitar que dicha coincidencia tenga un AND booleano. El parámetro de campos cruzados no requiere que todos los términos aparezcan en un mismo campo, sino que se les permite estar presentes en campos diferentes, siempre que ambos estén presentes. Dada la estructura de datos, esto es esencial para establecer una coincidencia.
Nuestros ejemplos han arrojado los resultados “filas” comparado con “grupos”. Sin embargo, los operadores QUERY (CONSULTA) y MATCH (COINCIDENCIA) también se pueden usar con GROUP BY (AGRUPAR POR), que es una agregación filtrada efectivamente en Elasticsearch.
Búsquedas y alias con índices cruzados
Hasta ahora, nuestras consultas se han dirigido a una sola tabla o índice. Si duplicamos el índice de vuelos y copiamos los documentos a una nueva versión nombrada a través de una solicitud de reindexación, podemos hacer ambas consultas al mismo tiempo, siempre que ambos índices tengan mapeos idénticos. Cualquier diferencia en el mapeo causaría un error en la consulta durante el análisis. Para consultar varios índices al mismo tiempo, el usuario puede agregarlos a un Elasticsearch alias (Alias de Elasticsearch) o utilizar comodines en la cláusula WHERE (DÓNDE). Por ejemplo, supongamos que tengo dos índices “flights” (vuelos) y “flights-2” (vuelos-2) con el siguiente alias “f_alias”:
POST /_aliases
{
"actions" : [
{ "add" : { "index" : "flights-2", "alias" : "f_alias" } },
{ "add" : { "index" : "flights", "alias" : "f_alias" } }
]
}
Los siguientes son equivalentes lógicos:
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
Prueba en demo.elastic.co. Nuestro entorno de demostración contiene ambos índices con un alias preconfigurado para que los usuarios prueben lo anterior.
En el futuro, podemos reducir los requisitos anteriores; por ahora, sin embargo, esto simplifica la lógica en esta versión inicial.
JOINs
En una implementación tradicional de RDBMS SQL, las JOINs permiten combinar filas de tablas separadas a través de columnas relacionadas en una única respuesta tabular. Esto posibilita el modelado relacional de datos y representa un tema importante en comparación con las opciones disponibles de forma nativa en Elasticsearch para lograrlo. Si bien Elasticsearch SQL actualmente no expone ningún operador JOIN, les permite a los usuarios explotar documentos anidados, que ofrecen un modelado relacional simple de uno a varios. La consulta de nested documents (documentos anidados) se maneja de una manera transparente para el usuario. Para demostrar esta funcionalidad, necesitamos un índice con dichos datos. A modo de ejemplo, hemos cargado un índice de “orders” (pedidos) en demo.elastic.co. Los documentos de este índice representan pedidos de un sitio de comercio electrónico y contienen campos como order_date (fecha_pedido), billing_city (ciudad_facturación) y customer_last_name (apellido_cliente). Además, incluye un campo de “products” (productos), que contiene un subdocumento anidado para cada producto incluido en el pedido. Por ejemplo:
{
"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",
...
}
En general, la consulta de estos documentos requerirá que el usuario entienda por qué usaríamos un tipo de datos anidados para el campo de productos y que, además, comprenda la sintaxis de consultas anidadas. Sin embargo, con Elasticsearch SQL podemos consultar estos documentos anidados como si cada uno representara una fila separada con los campos de su fila original (es decir, si aplanáramos la estructura para la presentación). Observa el pedido anterior, que contiene dos productos. Ante una consulta, esto se presenta como dos filas, cuando se solicitan campos de los subdocumentos del producto. Cada fila también puede contener los campos del pedido principal, si se solicitan. Por ejemplo:
“Buscar vuelos con el nombre de facturación usado y los productos adquiridos para el pedido 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
La API _translate demostrará cómo se construye una consulta mediante una consulta anidada.
{
"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"
}
}
]
}
Si, por el contrario, hiciéramos una consulta de los campos originales, se nos presentaría una sola fila:
“Buscar vuelos con el nombre de facturación usado para el pedido 518894”.
sql> SELECT billing_last_name, billing_first_name FROM orders WHERE order_id=519894; billing_last_name|billing_first_name -----------------+------------------ Green |Jason
Lo que falta y lo que viene...
Limitaciones actuales que podrían aparecer al comienzo de cualquier experimentación:
- Consultas de tablas o índices cruzados: esto es posible siempre que los mapeos de los índices sean idénticos. Cualquier diferencia causaría un error en el momento de la consulta.
- JOINs: Como se destacó anteriormente, tenemos compatibilidad limitada con JOINs, solo a través del uso de documentos anidados.
- Función GROUP BY (AGRUPAR POR) para ordenar grupos. Como mencionamos anteriormente, esta es una limitación heredada de la agregación compuesta de Elasticsearch.
- Cláusulas SELECT (SELECCIONAR) anidadas. Estos son mecanismos comunes para respaldar el desglose de las herramientas de BI. Si bien los enunciados anidados SELECT que usan índices múltiples fueran equivalentes a una JOIN, los enunciados que usan el mismo índice posiblemente podrían reescribirse y ejecutarse. Esto es algo que podemos considerar en el futuro.
Algunas de las ventajas de Elasticsearch que planeamos abordar en futuras versiones incluyen las siguientes:
- La capacidad de GROUP BY (AGRUPAR POR) en múltiples niveles (por ejemplo, “Buscar rutas de vuelo, de origen y destino, por mes con un retraso promedio de hasta dos horas”).
- Exposición de los operadores geoespaciales de Elasticsearch
- Compatibilidad con tipos de INTERVAL (INTERVALO) (por ejemplo, la habilidad de operar sobre la diferencia entre dos fechas y horas).
- Funciones Data/Time (Hora/Fecha) y String (Cadena) mejoradas
- Compatibilidad con histogramas de agrupamiento
- Búsquedas con Cross-index (Índices cruzados) y Cross- Cluster (Clústeres cruzados) donde los mapeos no sean idénticos
Gracias por su atención, y estén atentos a los próximos lanzamientos en esta serie de Introducción a Elasticsearch SQL.