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

Пример использования SQL

• Используйте SQL для поиска данных в Kibana (рекомендовано).

  Войдите в Kibana и, на Dev Tools странице, отправьте запросы к _opendistro/_sql using request parameters or the request body.

  Для примера, выполните следующую команду, чтобы получить 50 записей из my-index индекс:

  1
  2
  3
  4POST _opendistro/_sql
  {
    "query": "SELECT * FROM my-index LIMIT 50"
  }
  
  

  По умолчанию результат запроса возвращается в формате JSON. Чтобы вернуть данные в формате CSV, установите формат параметр в команде следующим образом:

  1
  2
  3
  4POST _opendistro/_sql?format=csv
  {
    "query": "SELECT * FROM my-index LIMIT 50"
  }
  
  

  Когда результат запроса возвращается в формате CSV, каждая строка соответствует документу, а каждый столбец — полю.

• Запустите команды cURL на ECS, чтобы выполнить SQL‑операторы для поиска данных.

  Например, выполните следующую команду, чтобы получить 10 записей из kibana_sample_data_flights index.

  curl -XPOST https://localhost:9200/_opendistro/_sql -u username:password -k -d '{"query": "SELECT * FROM kibana_sample_data_flights LIMIT 10"}' -H 'Content-Type: application/json'
  
  

  localhost указывает адрес доступа к кластеру. имя пользователя и пароль укажите имя пользователя и пароль для доступа к кластеру security-mode, соответственно.

Поддерживаемые операции SQL

• Операторы

  Таблица 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)
  Лимит	SELECT * FROM my-index LIMIT 50 (по умолчанию 200)
  Объединение	SELECT * FROM my-index1 UNION SELECT * FROM my-index2
  Минус	SELECT * FROM my-index1 MINUS SELECT * FROM my-index2

  Note

  Как и любой сложный запрос, большие операторы UNION и MINUS могут нагружать или даже вывести из строя ваш кластер.

• Условия

  Таблица 2 Условия

  Условие	Пример
  LIKE	SELECT * FROM my-index WHERE name LIKE 'j%'
  И	SELECT * FROM my-index WHERE name LIKE 'j%' AND age > 21
  ИЛИ	SELECT * FROM my-index WHERE name LIKE 'j%' OR age > 21
  Подсчёт уникальных	SELECT count(distinct age) FROM my-index
  В	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

• Агрегации

  Таблица 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

• Включение и исключение полей

  Таблица 4 Включение и исключение полей

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

• Функции

  Таблица 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

  Note

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

• Соединения

  Таблица 6 Соединения

  Соединение	Пример
  Внутреннее соединение	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
  Левое внешнее соединение	SELECT s.firstname, s.lastname, s.gender, sc.name FROM student s LEFT JOIN school sc ON sc.name = s.school_name
  Кросс‑соединение	SELECT s.firstname, s.lastname, s.gender, sc.name FROM student s CROSS JOIN school sc

  Для получения подробной информации о ограничениях см. Соединения.

• Показать

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

  Таблица 7 Показать

  Показать	Пример
  Показать таблицы как	SHOW TABLES LIKE logs-*

Соединения

• Вы можете объединять только два индекса.

• Для каждого индекса необходимо использовать псевдоним (например, people p).

• В условии ON вы можете использовать только условия AND.

• В операторе 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)
  
  

• Нельзя использовать GROUP BY или ORDER BY для получения результатов.

• LIMIT с OFFSET (например, LIMIT 25 OFFSET 25) не поддерживается.

JDBC драйвер