Использование SQL для поиска

Open Distro for Elasticsearch SQL по сути является переводчиком между SQL и Elasticsearch DSL. Он работает следующим образом:

1. Парсинг: Он получает и проверяет SQL‑запросы, отправленные пользователями.
2. Трансляция: Он преобразует SQL‑запросы в нативные запросы Elasticsearch DSL.
3. Выполнение: Он отправляет DSL‑запросы в кластер для выполнения.
4. Возврат: Он возвращает результаты в формате JSON или CSV.

Для получения дополнительной информации см. SQL Functions.

Только Elasticsearch версии 6.5.4 и новее поддерживает Open Distro for Elasticsearch SQL.

• Метод 1: Выполнение SQL‑запросов в Kibana (рекомендовано)

  Используйте этот метод для разработки, отладки и разовых запросов.

  • Выполните базовый запрос.

    Например, выполните следующую команду для поиска в индексе my-index документов, у которых поле age превышает 20, возвращая до 10 результатов:

    POST _opendistro/_sql
    {
      "query": "SELECT * FROM my-index WHERE age > 20 LIMIT 10"
    }
    
    

  • Экспортировать данные в CSV. Для экспорта данных в документ Excel для анализа укажите format=csv.

    Например, выполните следующую команду для получения 10 документов из индекса my-index на основе полей name и age и возврата результатов в формате CSV:

    POST _opendistro/_sql?format=csv
    {
      "query": "SELECT name, age FROM my-index LIMIT 10"
    }
    
    

  • Преобразовать SQL в DSL. Чтобы узнать, как писать запросы DSL или анализировать производительность запросов SQL, используйте API _explain для проверки преобразованных запросов DSL.

    Например, выполните следующую команду для преобразования SQL‑запроса в эквивалентный запрос DSL:

    POST _opendistro/_sql/_explain
    {
      "query": "SELECT * FROM my-index WHERE age > 20"
    }
    
    

• Метод 2: Выполнение SQL‑запросов с помощью cURL‑команд на сервере

  Используйте этот метод, когда необходимо включить запросы Elasticsearch в автоматизированные скрипты или запланированные задачи.

  Например, выполните следующую команду для получения 10 записей из индекса my-index. Ниже приведён пример кода, используемого в кластере в режиме security-mode, который использует HTTP:

  curl -XPOST http://<cluster IP address>:9200/_opendistro/_sql \
    -u <username>:<password> -k \
    -H 'Content-Type: application/json' \
    -d '{"query": "SELECT * FROM my-index LIMIT 10"}'
  
  

Open Distro for Elasticsearch SQL поддерживает следующие элементы синтаксиса SQL: операторы, условия, агрегаты, поля include и exclude, общие функции, соединения и операторы SHOW.

• Операторы

  Table 1 Операторы

  Оператор	Пример
  Select	SELECT * FROM my-index
  Delete	DELETE FROM my-index WHERE _id=1
  Where	SELECT * FROM my-index WHERE ['field']='value'
  Order by	SELECT * FROM my-index ORDER BY _id asc
  Group by	SELECT * FROM my-index GROUP BY range(age, 20,30,39)
  Limit	SELECT * FROM my-index LIMIT 50 (default is 200)
  Union	SELECT * FROM my-index1 UNION SELECT * FROM my-index2
  Minus	SELECT * FROM my-index1 MINUS SELECT * FROM my-index2

  Caution

  Большие запросы UNION и MINUS могут вызывать высокое потребление ресурсов, что потенциально приводит к ухудшению производительности кластера или даже к перебоям в работе сервиса. Чтобы снизить этот риск, рекомендуется оптимизировать структуру запроса или разбить большие запросы на более мелкие партии.

• Условия

  Table 2 Условия

  Условие	Пример
  Like	SELECT * FROM my-index WHERE name LIKE 'j%'
  And	SELECT * FROM my-index WHERE name LIKE 'j%' AND age > 21
  Or	SELECT * FROM my-index WHERE name LIKE 'j%' OR age > 21
  Count distinct	SELECT count(distinct age) FROM my-index
  In	SELECT * FROM my-index WHERE name IN ('Bob', 'David')
  Не	SELECT * FROM my-index WHERE name NOT IN ('Bob')
  Между	SELECT * FROM my-index WHERE age BETWEEN 20 AND 30
  Псевдонимы	SELECT avg(age) AS Average_Age FROM my-index
  Дата	SELECT * FROM my-index WHERE birthday='1990-11-15'
  Null	SELECT * FROM my-index WHERE name IS NULL

• Агрегации

  Table 3 Агрегации

  Агрегация	Пример
  avg()	SELECT avg(age) FROM my-index
  count()	SELECT count(age) FROM my-index
  max()	SELECT max(age) AS Highest_Age FROM my-index
  min()	SELECT min(age) AS Lowest_Age FROM my-index
  sum()	SELECT sum(age) AS Age_Sum FROM my-index

• Включить и исключить поля

  Table 4 Включить и исключить поля

  Шаблон	Пример
  include()	SELECT include('a*'), exclude('age') FROM my-index
  exclude()	SELECT exclude('*name') FROM my-index

• Функции
  Caution

  Необходимо включить fielddata в document mapping, чтобы большинство строковых функций работали корректно.

  Table 5 Функции

  Функция	Пример
  floor	SELECT floor(number) AS Rounded_Down FROM my-index
  trim	SELECT trim(name) FROM my-index
  log	SELECT log(number) FROM my-index
  log10	SELECT log10(number) FROM my-index
  substring	SELECT substring(name, 2,5) FROM my-index
  round	SELECT round(number) FROM my-index
  sqrt	SELECT sqrt(number) FROM my-index
  concat_ws	SELECT concat_ws(' ', age, height) AS combined FROM my-index
  /	SELECT number / 100 FROM my-index
  %	SELECT number % 100 FROM my-index
  date_format	SELECT date_format(date, 'Y') FROM my-index

• Соединения
  Caution

  Поддерживаемые типы соединений: INNER JOIN, LEFT JOIN, and CROSS JOIN.

  Жесткие ограничения:

  • Можно объединять только две таблицы (индекса).
  • Для каждой таблицы необходимо указать псевдоним, например, FROM people p JOIN index b.
  • Условие ON поддерживает только комбинации AND, но не OR и не вложенные условия.
  • В запросе JOIN нельзя использовать GROUP BY или ORDER BY.
  • LIMIT и OFFSET нельзя использовать одновременно. Отрицательный пример: LIMIT 25 OFFSET 25
  • Поля из нескольких индексов нельзя комбинировать в предложении WHERE.

    Положительный пример:

    WHERE (a.type1 > 3 OR a.type1 < 0) AND (b.type2 > 4 OR b.type2 < -1)
    
    

    Отрицательный пример:

    WHERE (a.type1 > 3 OR b.type2 < 0) AND (a.type1 > 4 OR b.type2 < -1)
    
    

  Table 6 Объединения

  Join	Example
  Inner join	SELECT s.firstname, s.lastname, s.gender, sc.name FROM student s JOIN school sc ON sc.name = s.school_name WHERE s.age > 20
  Left outer join	SELECT s.firstname, s.lastname, s.gender, sc.name FROM student s LEFT JOIN school sc ON sc.name = s.school_name
  Cross join	SELECT s.firstname, s.lastname, s.gender, sc.name FROM student s CROSS JOIN school sc

• Show

  Команды Show отображают индексы и сопоставления, соответствующие шаблону индекса. Вы можете использовать * или % в качестве подстановочных знаков.

  Table 7 Show

  Show	Пример
  Show tables like	SHOW TABLES LIKE logs-*

Если вы используете Tableau, Power BI или самостоятельно управляемое Java‑приложение, вы можете подключиться к кластеру CSS с помощью JDBC driver.

Скачайте JDBC driver из GitHub repository.