Uma introdução ao Elasticsearch SQL com exemplos práticos - Parte 1
A versão 6.3 do Elastic Stack foi uma das mais ricas em recursos que já lançamos nos últimos tempos. Se a abertura do X-Pack e os rollups não foram suficientes para você se animar, anunciamos também o suporte a SQL como um recurso experimental. Esse foi um dos recursos mais pedidos na história relativamente curta da Elasticsearch.
Nesta série de blog, vamos explorar alguns recursos que são compatíveis atualmente com o Elasticsearch SQL. Também discutiremos algumas limitações do recurso e falaremos de planos para o futuro.
Público alvo
Até hoje na Elastic, hesitamos em adicionar SQL ao produto por uma série de razões. Perguntas comuns incluíam:
- De que se constituiria o suporte ao SQL?
- Que recursos seriam compatíveis? E ainda mais comum, isso era articulado como “Somos compatíveis com JOINs”?
- Expressões/funções? Agrupamentos?”
- Precisamos ser compatíveis com conectividade JDBC/ODBC?
- Precisamos ser compatíveis com SQL ou simplesmente falhamos em oferecer material suficiente para novos usuários para se familiarizarem com nossa Domain Specific Language (DSL)?
Depois de diversas iniciativas, restringimos com êxito os recursos exigidos a algo que achamos que seria útil para nossos usuários. Com base nas discussões com nossos usuários, acreditamos que o SQL será mais valioso especialmente para dois públicos principais:
- Novos usuários do stack que podem achar o DSL do Elasticsearch um pouco complicado no começo ou que simplesmente não precisam aprender a sintaxe completa. Por exemplo, um usuário pode estar convertendo uma aplicação existente baseada em SQL por questões de desempenho e escalabilidade e simplesmente querer a consulta equivalente sem ter que aprender a sintaxe completa. Também reconhecemos uma estratégia de aprendizado comum para aprender novas linguagens, que é encontrar equivalência com algo que você já sabe.
- Consumidores de dados que não tenham desejo de aprender ou precisem aprender o DSL do Elasticsearch. Esses usuários podem ser cientistas de dados que simplesmente querem extrair os dados para processamento interno. Alternativamente, eles podem ser usuários de BI menos técnicos que são geralmente mais familiarizados com SQL e o usam no dia a dia.
Além dos públicos acima, também não podemos negar que, como uma linguagem declarativa, o SQL é um paradigma altamente atraente para todos os usuários, como essa série de blog posts irá ilustrar com frequência. A prevalência do SQL sem dúvida é baseada nessa habilidade de expressar a lógica da computação e no que você está tentando fazer, sem precisar definir seu controle de fluxo. Além disso, conforme vamos ilustrar, algumas consultas de SQL definem elegantemente um problema que pode ser um pouco prolixo usando seu equivalente no DSL do Elasticsearch. Enquanto o DSL do Elasticsearch mais elegantemente descreve problemas de busca de texto completo, o SQL pode ser mais eficaz para descrever consultas baseadas em analítica estruturada.
O que o Elasticsearch SQL é, O que o Elasticsearch SQL não é...
O Elasticsearch SQL, em sua versão inicial, oferece uma interface somente-leitura que é composta de um subconjunto da especificação ANSI SQL e permite que o Elasticsearch seja exposto como uma fonte tabular. Também oferecemos operadores adicionais que vão além dessa especificação, expondo capacidades exclusivas do Elasticsearch em comparação com implementações baseadas em RDBMS. Nosso objetivo foi oferecer uma implementação leve e rápida que minimiza a dependência de recursos externos e partes móveis. Essa primeira versão, de forma nenhuma transforma o Elasticsearch em um banco de dados completamente relacional (com suas propriedades associadas) ou substitui a necessidade de modelagem de dados. Enquanto algumas funções e expressões de manipulação de dados são implementadas pelo plugin SQL, um princípio de push down é aderido sempre que a contagem e a ordem do resultado for impactada ou for solicitado agrupamento. Isso limita o processamento atual de dados no plugin do Elasticsearch SQL a somente manipulação de resultados (ex.: funções em campos) e o cliente (driver JDBC/CLI ou navegador) a renderizar. Essa abordagem explora a escalabilidade e a performance do Elasticsearch, permitindo que ele faça o trabalho pesado.
Conceitos de mapeamento: índices e documentos X tabelas e linhas
Nos primórdios do Elasticsearch, índices e tipos eram considerados análogos aos bancos de dados e tabelas de RDBMS, principalmente para ajudar usuários a compreenderem e trabalharem com novos conceitos em potencial. Conforme explicado na documentação do Elasticsearch 6.3, essa era uma analogia incorreta e potencialmente perigosa. Embora estejamos removendo tipos, ainda precisamos de uma equivalência lógica usável e correta entre o modelo orientado a documento sem esquema do Elasticsearch e os conceitos de tipagem forte do SQL.
Felizmente, os índices do Elasticsearch, como tabelas RDBMS, são fisicamente isoladas e devem ser geralmente usadas da mesma maneira (por exemplo, para armazenar dados relacionados). Linhas e documentos também são uma analogia natural pois oferecem um mecanismo de agrupar campos/colunas, embora uma linha tenda a ser rígida (e ter mais imposições) enquanto um documento tende a ser mais flexível ou livre (embora ainda tenha uma estrutura). Os campos no Elasticsearch representam uma entrada de nome e são compatíveis com diversos tipos de dados, potencialmente contendo diversos valores (por exemplo, uma lista). Com a exceção desses campos de multivalores, esse conceito mapeia diretamente as colunas SQL. Nota: se você tentar um SELECT em um campo multivalor, verá um erro em tempo de consulta.
Outros conceitos não mapeiam de forma tão direta. Um banco de dados SQL e o cluster Elasticsearch tem apenas uma equivalência tênue. Mas isso não costuma impactar usuários de Elasticsearch SQL. Veja mais detalhes deste assunto em nossa documentação Mapeando conceitos entre SQL e Elasticsearch.
Em resumo, use o nome do índice na sua cláusula WHERE para utilizar um índice ou tabela específico. Você pode esperar que documentos únicos sejam retornados como linhas com campos mapeados como colunas. Por conta desse mapeamento amplamente transparente, usaremos esses termos indistintamente daqui pra frente.
Internos da Implementação
A implementação do Elasticsearch SQL consiste em 4 etapas de execução:
A fase de Parsing é responsável por converter a consulta SQL em uma Abstract Syntax Tree (AST). Qualquer validação de sintaxe é realizada neste estágio, antes do analisador validar o AST e resolver qualquer tabela coluna, função, alias e namespace para produzir um plano lógico. O plano é otimizado, incluindo a remoção de quaisquer expressões redundantes antes de ser convertido a um plano físico (ex.: DSL Elasticsearch) para execução. O Executor de Consultas executa então a consulta e mostra o resultado para o cliente, realizando quaisquer conversões de tipo e tabulares conforme necessário (por exemplo, árvore de agregação para tabela).
Métodos de conectividade
Ao oferecer qualquer solução SQL, suporte à conectividade se torna uma consideração primordial. Embora uma interface somente REST possa ser aceitável para alguns usuários, a maioria espera conseguir se conectar através de interfaces padrão, tipicamente JDBC e ODBC. Enquanto o suporte ao ODBC está nos planos e já ativamente sendo desenvolvido, JDBC está disponível com essa primeira versão e disponível para download.
É importante dizer que todas as comunicações com esses drivers se manterão em HTTP através de nossa interface REST. Isso tem diversas vantagens interessantes:
- Permitir acesso SQL aos seus usuários não é diferente de abrir e expor uma porta Elasticsearch com ela se integrando de forma nativa com segurança. Por isso nosso Elasticsearch Service na Elastic Cloud já é compatível com o SQL, e os usuários já existentes conseguem combiná-lo com permissões de controle de acesso por padrão.
- Isso nos permite utilizar o SQL diretamente através da interface REST e lançar um cliente CLI para conveniência. Esperamos que esse último seja particularmente popular entre os administradores, que ficarão familiarizados com os meios de interação da linha de comando comuns em RDBMS.
O driver JDBC utiliza a recém-criada biblioteca XContent, responsável por parsing de consultas e respostas (historicamente esse código foi vinculado ao Elasticsearch). Isso evita a necessidade de o driver depender de todas as bibliotecas do Elasticsearch e garante que ele permaneça leve e portátil. Este desacoplamento continuará a ser melhorado nas próximas versões, o que resultará em um driver menor e mais rápido.
Alguns exemplos simples
Vamos ver alguns exemplos que utilizam um mix de CLI e API REST. Para esses exemplos, usaremos um conjunto de dados que em breve será distribuído com o Kibana. Se não quiser esperar o lançamento, esse conjunto de dados também está disponível em demo.elastic.co, onde você pode executar os exemplos seguintes pelo console do Kibana. Oferecemos links para demo.elastic.co, que devem ser autopreenchidos com a consulta relevante através do blog. Como alternativa, oferecemos a lista completa de consultas para executar no console de demonstração do Kibana. Em alguns casos, os resultados podem variar se não houver ordenamento ou restrição explicita à consulta de exemplo, por conta do ordenamento natural dos resultados no Elasticsearch quando não há relevância ou critério de ordenação aplicado.
Recuperando informações do Elasticsearch Schema: DSL X SQL
Primeiro vamos identificar o esquema da tabela/índice e os campos disponíveis. Vamos fazer isso pela interface REST:
Requisição
POST _xpack/sql
{
"query":"DESCRIBE flights"
}
Experimente em demo.elastic.co
Resposta
{
"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 resposta acima também pode ser formatada em formato tabular através do parâmetro ?format=txt. Por exemplo:
POST _xpack/sql?format=txt
{
"query":"DESCRIBE flights"
}
Experimente em demo.elastic.co
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
Continuando, usamos a estrutura de respostas tabulares mostrada acima sempre que fornecermos uma resposta de exemplo da API REST. Obter a mesma consulta pelo console exige que façamos login usando:
./elasticsearch-sql-cli http://elastic@localhost:9200
Depois de responder ao pedido de senha...
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>
O esquema acima também é devolvido com qualquer consulta para os campos que estão sendo exibidos na cláusula SELECT, oferecendo para qualquer driver em potencial o tipo necessário de informação exigida para formatar ou operar nos resultados. Por exemplo, considere um SELECT simples com uma cláusula LIMIT para manter a resposta curta. Por padrão, retornamos 1000 linhas.
SELECT simples
POST _xpack/sql?format=txt
{
"query":"SELECT FlightNum FROM flights LIMIT 1"
}
Experimente em demo.elastic.co (os resultados podem variar)
FlightNum --------------- 1Y0TZOE
A mesma requisição/resposta REST é consumida pelo driver JDBC e a console, mas oculta para o usuário.
sql> SELECT OriginCountry, OriginCityName FROM flights LIMIT 1; OriginCountry |OriginCityName ---------------+--------------- US |San Diego
Experimente em demo.elastic.co (os resultados podem variar)
Observe que, se a qualquer momento, você requisitar um campo que não exista (a operação difere maiúsculas e minúsculas), a semântica de um armazenamento tabular e de tipagem forte retornará um erro. Isso é diferente do comportamento do Elasticsearch onde o campo simplesmente não é retornado. Por exemplo, modificar a consulta acima para usar o campo “OrigincityName” em vez de “OriginCityName” resulta em uma mensagem de erro ú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
}
Experimente em demo.elastic.co
Da mesma forma, se tentássemos usar uma função ou expressão em um campo que não fosse compatível, veremos o erro. Em geral, o Analyser falhará mais cedo na hora de validar o AST. Para conseguir isso, o Elasticsearch precisa estar ciente do mapeamento de índice e das capacidades de cada campo. Por essa razão, qualquer cliente que acesse a interface SQL com segurança precisará das permissões adequadas.
Se continuarmos a oferecer cada requisição e a resposta correspondente, terminaremos com um post de blog extremamente longo. Para efeitos de concisão, veja algumas consultas mais complexas com notas de interesse.
SELECT com WHERE e ORDER BY
Encontre os 10 voos mais longos nos EUA com duração maior do que 5h.
POST _xpack/sql?format=txt
{
"query":"SELECT OriginCityName, DestCityName FROM flights WHERE FlightTimeHour > 5 AND OriginCountry='US' ORDER BY FlightTimeHour DESC LIMIT 10"
}
Experimente em demo.elastic.co
OriginCityName | DestCityName ---------------+--------------- Atlanta |Durban Louisville |Melbourne Peoria |Melbourne Albuquerque |Durban Birmingham |Durban Bangor |Brisbane Seattle |Durban Huntsville |Sydney Savannah |Shanghai Philadelphia |Xi'an
O operador para restringir contagens de linhas varia de acordo com a implementação de SQL. Para o Elasticsearch SQL, somos consistentes com Postgresql/Mysql na implementação do operador LIMIT.
Matemática
Veja alguns cálculos aleatórios...
sql> SELECT ((1 + 3) * 1.5 / (7 - 6)) * 2 AS random;
random
---------------
12.0
Experimente em demo.elastic.co
Isso representa um exemplo de onde o lado do servidor desempenha alguns processamentos posteriores para funções. Isso não tem equivalente no DSL de consulta do Elasticsearch.
Funções e expressões
“Encontre todos os voos, depois do mês de junho, em que a duração seja maior do que 5 horas e ordene pelo mais longo.”
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"
}
Experimente em 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
Essas funções exigiriam tipicamente escrever Painless para um resultado equivalente no Elasticsearch, enquanto os declarativos funcionais do SQL evitam a necessidade de qualquer script. Observe também como podemos usar a função nas cláusulas WHERE e SELECT. A cláusula WHERE é mandada para o Elasticsearch porque impacta na contagem do resultado. A função SELECT, no entanto, é feita com o plugin do lado do servidor na apresentação.
Observe que uma lista de funções disponíveis pode ser recuperada com SHOW FUNCTIONS.
Experimente em demo.elastic.co
Combinando isso com nossas habilidades anteriores em matemática podemos começar a formular consultas que seriam complexas de articular para a maioria dos usuários em DSL.
“Encontre a distância e a velocidade média dos 2 voos mais velozes que saem na segunda, terça ou quarta entre 9h e 11h e cuja distância seja maior do que 500 km. Arredonde a distância e a velocidade para a integral mais próxima. Se a velocidade for igual, mostre a mais longa primeiro.”
Experimente em demo.elastic.co
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
Uma pergunta complicada e estranha, mas com sorte você vê o ponto. Note também como criamos aliases de campos e nos referimos a eles na cláusula ORDER BY.
Observe também que não existe exigência para especificar todos os campos na cláusula SELECT que são usados em WHERE e ORDER BY. Isso pode ser diferente das implementações de SQL que você já usou no passado. A opção abaixo é perfeitamente válida:
POST _xpack/sql
{
"query":"SELECT timestamp, FlightNum FROM flights WHERE AvgTicketPrice > 500 ORDER BY AvgTicketPrice"
}
Experimente em demo.elastic.co
Traduzir consultas em SQL para DSL
Todos nós já tivemos uma consulta em SQL que foi difícil expressar em DSL no Elasticsearch ou que nos perguntamos se estava do melhor jeito. Um dos recursos interessantes da interface SQL é sua habilidade de ajudar novos usuários de Elasticsearch com esses problemas. Usando a interface REST, simplesmente adicionamos o /translate ao endpoint “sql” para obter a consulta de Elasticsearch que o driver emitiria.
Vamos considerar algumas das consultas anteriores:
POST _xpack/sql/translate
{
"query":"SELECT OriginCityName, DestCityName FROM flights WHERE FlightTimeHour > 5 AND OriginCountry='US' ORDER BY FlightTimeHour DESC LIMIT 10"
}
Experimente em demo.elastic.co
O DSL equivalente deveria ser razoavelmente óbvio para qualquer usuário experiente 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"
}
}
]
}
A cláusula WHERE é convertida em uma consulta de faixa e termo conforme é de se esperar. Observe como a variante OriginCountry.keyword do subcampo é usada para a combinação do termo exato X OriginCountry (que é do tipo texto). O usuário não precisa saber as diferenças de comportamento do mapeamento inerente. O tipo de campo correto foi selecionado automaticamente. É interessante notar que a interface tenta otimizar o desempenho da recuperação usando docvalue_fields em vez de _source quando disponível, por exemplo, para tipos exatos (numéricos, datas, palavras-chaves) com doc values habilitados. Podemos confiar no Elasticsearch SQL para gerar a melhor opção de DSL para a consulta.
Agora considere a consulta mais complexa que usamos da ú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"
}
Experimente em demo.elastic.co
E a resposta...
{
"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"
}
}
]
}
Nossas cláusulas para WHERE e ORDER BY foram convertidas em scripts Painless e utilizadas em ordenação e consulta de script fornecidos pelo Elasticsearch. Esses scripts têm inclusive parâmetros para evitar compilações e explorar cache de script.
Uma observação importante, enquanto o acima representa a melhor tradução para a instrução SQL, não representa a melhor solução para o problema como um todo. Na realidade, queremos codificar o dia da semana, a hora do dia e a velocidade no documento em tempo de indexação da informação, o que nos permite usar consultas de intervalos simples. Isso será provavelmente mais eficiente do que usar scripts Painless para resolver esse problema em específico. Alguns desses campos já estão inclusive presentes no documento por essa razão. Esse é um tema comum com o qual os usuários devem tomar cuidado: Embora possamos confiar na implementação do Elasticsearch SQL para oferecer uma tradução ótima, ela só pode utilizar os campos especificados na consulta e, por isso, pode, não necessariamente oferecer a solução ideal para o problema como um todo. É importante considerar os pontos fortes da plataforma subjacente para alcançar uma abordagem ideal, e a API _translate pode ser o primeiro passo nesse processo.
Da próxima vez
Em Uma introdução prática à Elasticsearch SQL - Parte II, continuaremos a utilizar a API _translate para demonstrar recursos mais complexos do Elasticsearch SQL. Também abordaremos algumas limitações que um usuário pode encontrar nessa primeira versão. Depois disso, oferecerei um teaser do que está por vir.