SQL доступ к Elasticsearch

    Что делать, если вы хотите использовать SQL-запросы к Elasticsearch, но не купили X-Pack и у вас нет доступа к Elasticsearch SQL?

Вариант первый: смириться и продолжить писать REST-запросы

Вариант второй: использовать специальную библиотеку для SQL-доступа к Elasticsearch. Например, CData Elasticsearch Driver. Она существует в множестве вариантов и позволяет получить доступ к данным в Elasticsearch из любых средств разработки и программ (FireDAC Components, ADO.NET Provider, ODBC Driver, JDBC Driver, Power BI Connectors, BizTalk Adapter, PowerShell Cmdlets, Excel Add-In, Anypoint Connectors, SSIS Component).

Рассмотрим использование CData FireDAC Components for Elasticsearch в Delphi.

1. Подготовка Elasticsearch

1.1. Создадим индекс "sql_test" и сразу смаппируем поля

PUT http://localhost:9200/sql_test
{
  "mappings": {
    "properties": {
      "name": { "type": "text" },
      "address": {
        "type": "object",
        "properties": {
          "street": { "type": "text" },
          "city": { "type": "text" },
          "state": { "type": "text" }
        }
      },
      "insured_ages": {
        "type": "integer"
      }
    }
  }
}

1.2. Добавим в индекс "sql_test" тестовые данные

POST http://localhost:9200/sql_test/_doc
{
  "name": "John Smith",
  "address": {
    "street": "Main Street",
    "city": "Chapel Hill",
    "state": "NC"
  },
  "insured_ages": [ 17, 43, 45 ]
}

POST http://localhost:9200/sql_test/_doc
{
  "name": "Joseph Newman",
  "address": {
    "street": "Oak Street",
    "city": "Raleigh",
    "state": "NC"
  },
  "insured_ages": [ 23, 25 ]
}

POST http://localhost:9200/sql_test/_doc
{
  "name": "John Second",
  "address": {
    "street": "Second Street",
    "city": "Chapel Hill",
    "state": "NC"
  },
  "insured_ages": [ 77, 33, 44 ]
}

Маппинг полей в индексе и данные позаимствованы из документации библиотеки CData FireDAC Components for Elasticsearch.

2. Подготовка тестовой программы

2.1. Создадим тестовое VCL-приложение, на главную форму которого добавим: объекты FireDAC (TFDConnection и TFDQuery) и контролы для интерфейса (TMemo - для ввода SQL-запроса, TDBGrid для отображения результатов SQL-запроса и 2 TCheckBox для параметров подключения):

2.2. Настроем подключение нашего приложения к серверу Elasticsearch. Для этого необходимо задать FDConnection всего несколько свойств. Нагляднее это сделать через диалог "FireDAC Connection Editor".

Для начала в свойстве "DriverName" выберем драйвер "CData.Elasticsearch" (на диалоге для этого есть combobox "Driver Id"). Затем укажем адрес сервера Elasticsearch (свойство "Server") и его порт (свойство "Port"). У меня локальный сервер, поэтому я прописал "localhost", а порт оставил без изменения - 9200. Если у вас все-таки куплен X-Pack, то вам для подключения к серверу Elasticsearch придется настроить еще параметры аутентификации (если она включена).

2.3. На нажатие кнопки "btnOpen" добавим обработчик, который будет выполнять наш запрос:

procedure TMain.btnOpenClick(Sender: TObject);
begin
  FDConnection.Params.Values['FlattenObjects'] := 
    BoolToStr(cbFlattenObjects.Checked, True);
  FDConnection.Params.Values['FlattenArrays'] := 
    IfThen(cbFlattenArrays.Checked, '3', '');
  FDQuery.Open(Memo.Text);
  DBGridEh.OptimizeAllColsWidth;
end;

О параметрах подключения "FlattenObjects" и "FlattenArrays" я расскажу позже.

3. SQL-запросы к Elasticsearch

CData Elasticsearch Driver рассматривает индекс Elasticsearch как таблицу. Поэтому для начала выполним простой запрос:

"Это просто праздник какой-то!" Вместо JSON из результатов REST-запроса к Elasticsearch мы получаем DataSet, который более привычен и прост в обработке.

Установим checkbox "FlattenObjects" и выполним запрос:

Как вы видите, поле "address" типа "object" с JSON-записью разделилось на три поля: "address.street", "address.city" и "address.state". Т.е. если параметр подключения FlattenObjects=True, то ключи вложенных объектов возвращаются в виде отдельных полей.

Теперь установим checkbox "FlattenArrays=3" и выполним запрос:

В DataSet добавились поля с элементами массива из поля "insured_ages". По умолчанию массивы возвращаются в виде строки JSON-массива. Но параметру подключения FlattenArrays можно задать количество элементов массива, которое нужно вернуть в качестве отдельных полей. У меня в примере FlattenArrays=3. Если при подключении установить FlattenObjects=True и задать FlattenArrays, то объекты, вложенные в элементы массива, также верутся в виде отдельных полей.

SQL-синтаксис у CData Elasticsearch Driver представляет собой микс из различных SQL-диалектов. Например, заметно влияние синтаксиса SQLServer, что позволяет написать запрос с TOP и квадратными скобками:

Обратите внимание, я обратился к элементу типа "object" (address.street) без включенного параметра "FlattenObjects".

CData Elasticsearch Driver предоставляет программисту большой выбор функций для обработки содержимого полей в SQL-запросе и составления условия выборки данных: Aggregate, JOIN, Date, Literal, Predicate, SQL, DATE, MATH, STRING, ORDER BY.

Например, агрегатные функции. Найдем максимальное значение среди элементов массива "insured_ages" во всем индексе:

или посчитаем сумму минимальных значений массива каждого документа в индексе:

А теперь замиксуем функции разного типа в одном запросе:

Как вы видите, в этом запросе функции: для работы с датой, для работы со строками, для обработки JSON и математические функции.

Поиск документов может производиться разным способом. Как стандартными возможностями SQL:

Так и с использованием специальных "predicate" функций:

Predicate функции, так же, что очень важно, позволяют эмулировать NoSQL-поиск Elasticsearch. Например, запрос:

соответствует POST-запросу к Elasticsearch:

Мои первые впечатления: CData FireDAC Components for Elasticsearch - это очень круто! Возможно даже, что реализованный в CData Elasticsearch Driver механизм SQL-запросов к Elasticsearch удобнее стандартного "Elasticsearch SQL" из X-Pack. Но все же ему не хватает ряда возможностей, которые реализуются с помощью REST-запросов. Поэтому ощутить всю мощь поисковых возможностей Elasticsearch, можно только через REST-запросы.