Делимся подробностями, как мы сделали хороший поиск по закрытой корпоративной соцсети в условиях, когда:

• данные хранятся в разных колонках таблиц MSSQL,
• раньше поиска по ним не было,
• а перенести их оттуда дорого — вся система завязана на MSSQL. Использовать сторонние сервисы не получится по соображениям информационной безопасности.

Критерий хорошего поиска для нас звучит так: даже если пользователь ввел запрос с опечаткой или неточно указал название группы, то всё равно нашёл её.

Также на перспективу нам нужно было продумать поиск по хэштегам как по раздельным словам, поиск по синонимам, ранжирование результатов и выдачу промежуточных результатов на лету.

Почему ElasticSearch?

Стандартные средства (впрочем, нестандартные тоже) MSSQL не позволяли сделать этот поиск. Несмотря на то, что в MSSQL есть Full-Text Search, на перспективу это решение могло привести к проблемам. Во-первых, у клиента не самая последняя версия SQL Server, а в ней поиск работает не так хорошо, как в новых версиях. Во-вторых, работу с опечатками, работу с хэштегами и ранжирование пришлось бы реализовывать вручную.

Также отказались от SharePoint, поскольку он умеет искать только по спискам. Заводить ещё один список и поддерживать его в актуальном состоянии оказалось слишком сложным. Списки, особенно на больших объёмах, не отличаются надёжностью и скоростью работы. Да и полнотекстовый поиск в SharePoint уступает по качеству MSSQL.

Использовать какой-либо сторонний сервис было невозможно из соображений безопасности. Поэтому выбор пал на ElasticSearch.

Как заиндексировать данные в Elasticsearch?

Нужно было заиндексировать данные в Elasticsearch и поддерживать их в актуальном состоянии. Когда в данных происходят изменения, индекс Elastic должен их автоматически перезаписывать, но не перезаписывать всё — а только новое.

Стало понятно, что нужно было искать решение, которое работает с JDBС драйвером. Это единственный очевидный путь, как из Elastic можно получить доступ к данным в MSSQL.

Первое, что попалось под руку — это Elastic-JDBC. Но последнее обновление этого решения датируется августом 2016 года и работает с версией ElasticSearch 2.3.4. Очевидно, что завязывать решение сразу на устаревшую версию – это неверный путь. Других готовых решений для работы напрямую с Elastic найти не удалось.

Загрузка данных через Logstash

В настоящее время за загрузку данных в ElasticSearch, как правило, отвечает Logstash. Для него-то и было найдено готовое решение – плагин JDBC Input plugin. Казалось бы, решение найдено и осталось только корректно его установить и настроить, но возникли небольшие трудности. О них мы расскажем чуть ниже.

Настройка Logstash: наш опыт и советы

Скачиваем и по инструкции ставим рядом с Logstash. Настройка довольно подробно описана на страничке плагина, но есть некоторые тонкости.

Вот так примерно выглядит файл конфигурации:

input {
  jdbc {
    jdbc_driver_library => "etc/logstash/bin/sqljdbc42.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_connection_string => "connection string"
    jdbc_user => "user"
    jdbc_password => "password"
    statement => "SELECT id, name, timestamp FROM [TableName] WHERE timestamp > :sql_last_value"
     schedule => "* * * * *"
   tracking_column => "timestamp"
   tracking_column_type => "timestamp"
   use_column_value => true
  }
}

Из нестандартных моментов можно заметить statement. Здесь мы указываем, какие именно столбцы мы будем индексировать. Sql_last_value — это специальное значение, которое перезаписывается при каждой итерации. Плагин берёт самое максимальное значение, которое может было обнаружено при загрузке. Это может быть либо number, либо timestamp. То есть мы можем завязаться на id и индексировать только новые записи или можем завязаться на DateTime колонку и отслеживать время последнего обновления. Записи, которые были изменены или добавлены позже, будут заиндексированы. Можно указать не один Input, а столько, сколько таблиц вам необходимо загрузить.

Советуем настраивать Output так, чтобы Id в Elastic совпадал с Id в вашей базе. Это исключит вероятность дублирования данных.

Проблемы, с которыми столкнулись

Logstash настроен на работу с логами и не понимает, зачем их удалять. Кому в здравом уме придёт в голову удалять логи? Такого функционала просто не было. Соответственно, удаление пришлось прописывать руками, отслеживать удаление из базы и удалять из Elasticsearch.
Изменения и добавления были реализованы таким образом: в базу MSSQL мы добавили колонку timestamp (дату последнего изменения). Если что-то изменено, эти дата и время меняются на текущее. И этот драйвер трекает последние изменения — раз в минуту он проверяет базу и у него хранится timestemp последнего изменения.

Добрые советы

Немного советов по реализации этого функционала. Если у вас CodeFirst база, то добавьте интерфейс:

internal interface IDateModified
    {
        DateTime TimeStamp { get; set; }
    }

Имплементируйте этот интерфейс для всех таблиц, которые будете потом индексировать в ElasticSearch. Если у вас DbFirst, то можно отредактировать tt файл для добавления интерфейса.
Далее при сохранении контекста вам будет очень легко отслеживать изменения интересующих вас таблиц.

var trackables = context.ChangeTracker.Entries<IDateModified>().Where(t => t.State == EntityState.Modified || t.State == EntityState.Added);
            foreach (var item in trackables)
            {
                item.Entity.TimeStampForElastic = DateTime.UtcNow;
            }

Также можно отслеживать EntityState.Deleted и удалять из Elastic одновременно с базой.

Итого

Таким образом, удалось получать из MSSQL актуальные данные прямо на лету и при запросах обращаться сперва в Elastic, получать оттуда нужные ID, удовлетворяющие поисковым критериям, а потом смотреть, есть ли они в базе, и довольно шустро доставать их оттуда.

Существует огромное количество статей по настройке поисковых индексов в ElasticSearch. Мы смотрели на эту статью — здесь описаны возможности для полнотекстового поиска. Хотелось также, но у нас не было возможности хранить все данные в Elasticsearch. У нас в Elasticsearch данные попадают не полностью, а только те, по которым нужно совершать поиск.

Мы указали в настройках, какие данные участвуют в поиске: ищем по новостям, по группам, по хештегам, названию и описанию группы, игнорируя количество участников.

Вот как мы настроили поиск с помощью MSSQL. В будущем мы хотим добавить поиск по сообщениям соцсети.

P.S. Бонусом ловите ссылку на плагин для русской морфологии, который мы использовали.