Индексирование данных из баз данных Azure SQL

Из этой статьи вы узнаете, как настроить индексатор, который импортирует содержимое из База данных SQL Azure или управляемого экземпляра SQL Azure и делает его доступным для поиска в службе "Поиск ИИ Azure".

В этой статье описано , как создать индексатор с информацией, относяющейся к SQL Azure. В нем используются портал Azure и REST API для демонстрации трех частей рабочего процесса, общего для всех индексаторов: создание источника данных, создание индекса, создание индексатора. Извлечение данных происходит при отправке запроса create Indexer.

Эта статья также содержит следующее:

• Описание политик обнаружения изменений, поддерживаемых индексатором SQL Azure, чтобы настроить добавочное индексирование.

• Раздел часто задаваемых вопросов и ответов на вопросы о совместимости функций.

Предварительные условия

• База данных SQL Azure или Управляемый экземпляр SQL с общедоступной конечной точкой.

• Одна таблица или представление.

  Используйте таблицу, если данные большие или требуются добавочное индексирование с помощью собственных возможностей обнаружения изменений SQL (встроенное отслеживание изменений SQL) для отражения новых, измененных и удаленных строк в индексе поиска.

  Используйте представление, если необходимо объединить данные из нескольких таблиц. Большие представления не идеально подходят для индексатора SQL. Обходной путь — создать новую таблицу только для приема в индекс поиска ИИ Azure. Если вы решили использовать просмотр, вы можете использовать "High Water Mark" для обнаружения изменений, но необходимо использовать обходное решение для обнаружения удаления.

• Первичный ключ должен быть однозначным. В таблице она также должна быть некластикционной для полного встроенного отслеживания изменений SQL.

• Разрешения на чтение. Поиск ИИ Azure поддерживает проверку подлинности SQL Server, где имя пользователя и пароль предоставляются в строке подключения. Кроме того, вы можете настроить управляемое удостоверение и использовать роли Azure с членством в роли участника SQL Server или роли участника базы данных SQL .

Для работы с примерами, приведенными в этой статье, вам потребуется портал Azure или клиент REST. Если вы используете портал Azure, убедитесь, что доступ ко всем общедоступным сетям включен в брандмауэре SQL Azure и что клиент имеет доступ через правило входящего трафика. Для клиента REST, работающего локально, настройте брандмауэр SQL Server, чтобы разрешить входящий доступ с IP-адреса устройства. Другие подходы к созданию индексатора SQL Azure включают пакеты SDK Azure.

Попробуйте использовать примеры данных

Используйте эти инструкции для создания и загрузки таблицы в База данных SQL Azure для тестирования.

1. Скачайте hotels-azure-sql.sql из GitHub, чтобы создать таблицу на База данных SQL Azure, которая содержит подмножество примера набора данных отелей.

2. Войдите в портал Azure и создайте базу данных SQL Azure и сервер базы данных. Рассмотрите возможность настройки как проверки подлинности SQL Server, так и аутентификации через идентификатор Microsoft Entra. Если у вас нет разрешений на настройку ролей в Azure, можно использовать проверку подлинности SQL в качестве обходного решения.

3. Настройте брандмауэр сервера для всех входящих запросов с локального устройства.

4. В базе данных SQL Azure выберите редактор запросов (предварительная версия) и выберите новый запрос.

5. Вставьте и запустите скрипт T-SQL, который создает таблицу отелей. Некластикционный первичный ключ — это требование для интегрированного отслеживания изменений SQL.

   CREATE TABLE tbl_hotels
    (
        Id TINYINT PRIMARY KEY NONCLUSTERED,
        Modified DateTime NULL DEFAULT '0000-00-00 00:00:00',
        IsDeleted TINYINT,
        HotelName VARCHAR(40),
        Category VARCHAR(20),
        City VARCHAR(30),
        State VARCHAR(4),
        Description VARCHAR(500)
    );
   

6. Вставьте и запустите скрипт T-SQL, который вставляет записи.

    -- Insert rows
    INSERT INTO tbl_hotels (Id, Modified, IsDeleted, HotelName, Category, City, State, Description) VALUES (1, CURRENT_TIMESTAMP, 0,  'Stay-Kay City Hotel', 'Boutique', 'New York', 'NY', 'This classic hotel is fully-refurbished and ideally located on the main commercial artery of the city in the heart of New York. A few minutes away is Times Square and the historic centre of the city, as well as other places of interest that make New York one of Americas most attractive and cosmopolitan cities.');
    INSERT INTO tbl_hotels (Id, Modified, IsDeleted, HotelName, Category, City, State, Description) VALUES (10, CURRENT_TIMESTAMP, 0, 'Countryside Hotel', 'Extended-Stay', 'Durham', 'NC', 'Save up to 50% off traditional hotels. Free WiFi, great location near downtown, full kitchen, washer & dryer, 24\/7 support, bowling alley, fitness center and more.');
    INSERT INTO tbl_hotels (Id, Modified, IsDeleted, HotelName, Category, City, State, Description) VALUES (11, CURRENT_TIMESTAMP, 0, 'Royal Cottage Resort', 'Extended-Stay', 'Bothell', 'WA', 'Your home away from home. Brand new fully equipped premium rooms, fast WiFi, full kitchen, washer & dryer, fitness center. Inner courtyard includes water features and outdoor seating. All units include fireplaces and small outdoor balconies. Pets accepted.');
    INSERT INTO tbl_hotels (Id, Modified, IsDeleted, HotelName, Category, City, State, Description) VALUES (12, CURRENT_TIMESTAMP, 0, 'Winter Panorama Resort', 'Resort and Spa', 'Wilsonville', 'OR', 'Plenty of great skiing, outdoor ice skating, sleigh rides, tubing and snow biking. Yoga, group exercise classes and outdoor hockey are available year-round, plus numerous options for shopping as well as great spa services. Newly-renovated with large rooms, free 24-hr airport shuttle & a new restaurant. Rooms\/suites offer mini-fridges & 49-inch HDTVs.');
    INSERT INTO tbl_hotels (Id, Modified, IsDeleted, HotelName, Category, City, State, Description) VALUES (13, CURRENT_TIMESTAMP, 0, 'Luxury Lion Resort', 'Luxury', 'St. Louis', 'MO', 'Unmatched Luxury. Visit our downtown hotel to indulge in luxury accommodations. Moments from the stadium and transportation hubs, we feature the best in convenience and comfort.');
    INSERT INTO tbl_hotels (Id, Modified, IsDeleted, HotelName, Category, City, State, Description) VALUES (14, CURRENT_TIMESTAMP, 0, 'Twin Vortex Hotel', 'Luxury', 'Dallas', 'TX', 'New experience in the making. Be the first to experience the luxury of the Twin Vortex. Reserve one of our newly-renovated guest rooms today.');
    INSERT INTO tbl_hotels (Id, Modified, IsDeleted, HotelName, Category, City, State, Description) VALUES (15, CURRENT_TIMESTAMP, 0, 'By the Market Hotel', 'Budget', 'New York', 'NY', 'Book now and Save up to 30%. Central location. Walking distance from the Empire State Building & Times Square, in the Chelsea neighborhood. Brand new rooms. Impeccable service.');
    INSERT INTO tbl_hotels (Id, Modified, IsDeleted, HotelName, Category, City, State, Description) VALUES (16, CURRENT_TIMESTAMP, 0, 'Double Sanctuary Resort', 'Resort and Spa', 'Seattle', 'WA', '5 Star Luxury Hotel - Biggest Rooms in the city. #1 Hotel in the area listed by Traveler magazine. Free WiFi, Flexible check in\/out, Fitness Center & espresso in room.');
    INSERT INTO tbl_hotels (Id, Modified, IsDeleted, HotelName, Category, City, State, Description) VALUES (17, CURRENT_TIMESTAMP, 0, 'City Skyline Antiquity Hotel', 'Boutique', 'New York', 'NY', 'In vogue since 1888, the Antiquity Hotel takes you back to bygone era. From the crystal chandeliers that adorn the Green Room, to the arched ceilings of the Grand Hall, the elegance of old New York beckons. Elevate Your Experience. Upgrade to a premiere city skyline view for less, where old world charm combines with dramatic views of the city, local cathedral and midtown.');
    INSERT INTO tbl_hotels (Id, Modified, IsDeleted, HotelName, Category, City, State, Description) VALUES (18, CURRENT_TIMESTAMP, 0, 'Ocean Water Resort & Spa', 'Luxury', 'Tampa', 'FL', 'New Luxury Hotel for the vacation of a lifetime. Bay views from every room, location near the pier, rooftop pool, waterfront dining & more.');
    INSERT INTO tbl_hotels (Id, Modified, IsDeleted, HotelName, Category, City, State, Description) VALUES (19, CURRENT_TIMESTAMP, 0, 'Economy Universe Motel', 'Budget', 'Redmond', 'WA', 'Local, family-run hotel in bustling downtown Redmond. We are a pet-friendly establishment, near expansive Marymoor park, haven to pet owners, joggers, and sports enthusiasts. Close to the highway and just a short drive away from major cities.');
    INSERT INTO tbl_hotels (Id, Modified, IsDeleted, HotelName, Category, City, State, Description) VALUES (20, CURRENT_TIMESTAMP, 0, 'Delete Me Hotel', 'Unknown', 'Nowhere', 'XX', 'Test-case row for change detection and delete detection . For change detection, modify any value, and then re-run the indexer. For soft-delete, change IsDelete from zero to a one, and then re-run the indexer.');
   
   

7. Выполните запрос, чтобы подтвердить отправку.

   SELECT Description FROM tbl_hotels;
   

   Вы увидите результаты, аналогичные следующему снимку экрана.

   

Поле "Описание" предоставляет наиболее подробное содержимое. Это поле должно быть предназначено для полнотекстового поиска и необязательной векторизации.

Теперь, когда у вас есть таблица базы данных, вы можете использовать портал Azure, клиент REST или пакет SDK Azure для индексирования данных.

Настройка конвейера индексатора

На этом шаге укажите источник данных, индекс и индексатор.

Проверка состояния индексатора

Чтобы отслеживать состояние индексатора и журнал выполнения, проверьте журнал выполнения индексатора в портал Azure или отправьте запрос REST API состояния индексатора

GET https://myservice.search.windows.net/indexers/myindexer/status?api-version=2024-07-01
  Content-Type: application/json  
  api-key: [admin key]

    {
        "status":"running",
        "lastResult": {
            "status":"success",
            "errorMessage":null,
            "startTime":"2022-02-21T00:23:24.957Z",
            "endTime":"2022-02-21T00:36:47.752Z",
            "errors":[],
            "itemsProcessed":1599501,
            "itemsFailed":0,
            "initialTrackingState":null,
            "finalTrackingState":null
        },
        "executionHistory":
        [
            {
                "status":"success",
                "errorMessage":null,
                "startTime":"2022-02-21T00:23:24.957Z",
                "endTime":"2022-02-21T00:36:47.752Z",
                "errors":[],
                "itemsProcessed":1599501,
                "itemsFailed":0,
                "initialTrackingState":null,
                "finalTrackingState":null
            },
            ... earlier history items
        ]
    }

Журнал выполнения содержит до 50 последних завершенных выполнений, которые сортируются в обратном хронологическом порядке, чтобы последнее выполнение было первым.

Индексирование новых, измененных и удаленных строк

Если база данных SQL поддерживает отслеживание изменений, индексатор поиска может получить только новое и обновленное содержимое при последующих запусках индексатора.

Чтобы включить добавочное индексирование, задайте свойство dataChangeDetectionPolicy в определении источника данных. Это свойство сообщает индексатору, какой механизм отслеживания изменений используется в таблице или представлении.

Для индексаторов SQL Azure существует две политики обнаружения изменений:

• "SqlIntegratedChangeTrackingPolicy" (применяется только к таблицам)

• "HighWaterMarkChangeDetectionPolicy" (работает для представлений)

Интегрированная политика отслеживания изменений SQL

Мы рекомендуем использовать SqlIntegratedChangeTrackingPolicy для его эффективности и возможности идентификации удаленных строк.

Требования к базе данных:

• База данных SQL Azure или Управляемый экземпляр SQL. SQL Server 2016 или более поздней версии, если вы используете виртуальную машину Azure.
• База данных должна иметь включенную функцию отслеживания изменений
• Только таблицы (без представлений).
• Таблицы не могут быть кластеризованы. Чтобы удовлетворить это требование, удалите кластеризованный индекс и повторно создайте его как некластикционный индекс. Это решение часто снижает производительность. Дублирование содержимого во второй таблице, предназначенной для обработки индексатора, может быть полезной мерой.
• Таблицы не могут быть пустыми. Если вы используете TRUNCATE TABLE для очистки строк, сброс и повторное выполнение индексатора не удалит соответствующие документы поиска. Чтобы удалить потерянные документы поиска, необходимо индексировать их с помощью действия удаления.
• Первичный ключ не может быть составным ключом (содержащим несколько столбцов).
• Первичный ключ должен быть не кластеризованным, если требуется обнаружение удаления.

Политики обнаружения изменений добавляются в определения источников данных. Чтобы использовать эту политику, измените определение источника данных на портале Azure или используйте REST для обновления источника данных следующим образом:

POST https://myservice.search.windows.net/datasources?api-version=2024-07-01
Content-Type: application/json
api-key: admin-key
    {
        "name" : "myazuresqldatasource",
        "type" : "azuresql",
        "credentials" : { "connectionString" : "connection string" },
        "container" : { "name" : "table name" },
        "dataChangeDetectionPolicy" : {
            "@odata.type" : "#Microsoft.Azure.Search.SqlIntegratedChangeTrackingPolicy"
        }
    }

При использовании интегрированной политики отслеживания изменений SQL не указывайте отдельную политику обнаружения удаления данных. Встроенная политика отслеживания изменений SQL поддерживает встроенную поддержку идентификации удаленных строк. Однако для автоматического обнаружения удаленных строк ключ документа в индексе поиска должен совпадать с первичным ключом в таблице SQL, а первичный ключ должен быть некластеризованным.

Политика обнаружения изменений уровня контрольной точки

Эта политика обнаружения изменений зависит от столбца метки уровня изменений в таблице или представлении, который фиксирует версию или время последнего обновления строки. При использовании представления следует использовать политику максимального уровня.

Столбец с отметкой высокого уровня воды должен соответствовать следующим требованиям:

• Все вставки указывают значение для столбца.
• при всех обновлениях элементов также изменяется значение столбца;
• значение этого столбца растет с каждой вставкой или обновлением;
• Возможно эффективное выполнение запросов со следующими предложениями WHERE и ORDER BY: WHERE [High Water Mark Column] > [Current High Water Mark Value] ORDER BY [High Water Mark Column].

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

POST https://myservice.search.windows.net/datasources?api-version=2024-07-01
Content-Type: application/json
api-key: admin-key
    {
        "name" : "myazuresqldatasource",
        "type" : "azuresql",
        "credentials" : { "connectionString" : "connection string" },
        "container" : { "name" : "table or view name" },
        "dataChangeDetectionPolicy" : {
            "@odata.type" : "#Microsoft.Azure.Search.HighWaterMarkChangeDetectionPolicy",
            "highWaterMarkColumnName" : "[a rowversion or last_updated column name]"
        }
    }

convertHighWaterMarkToRowVersion

Если вы используете тип данных rowversion для столбца, обозначающего последний рубеж данных, рекомендуется задать свойство convertHighWaterMarkToRowVersion в конфигурации индексатора. При задании этого свойства значение true приводит к следующему поведению:

• Использует тип данных rowversion для столбца контрольной точки в SQL-запросе индексатора. Использование правильного типа данных повышает производительность запросов индексатора.

• Вычитает единицу из значения rowversion перед выполнением запроса индексатора. Представления с соединениями "один ко многим" могут иметь строки с повторяющимися значениями rowversion. Вычитание одного гарантирует, что запрос индексатора не пропускает эти строки.

Чтобы включить это свойство, создайте или обновите индексатор со следующей конфигурацией:

    {
      ... other indexer definition properties
     "parameters" : {
            "configuration" : { "convertHighWaterMarkToRowVersion" : true } }
    }

queryTimeout

Если возникают ошибки времени ожидания, задайте queryTimeout для параметра конфигурации индексатора значение, превышающее 5-минутное время ожидания по умолчанию. Например, чтобы задать время ожидания, равное 10 минутам, создайте или обновите индексатор, используя приведенную ниже конфигурацию.

    {
      ... other indexer definition properties
     "parameters" : {
            "configuration" : { "queryTimeout" : "00:10:00" } }
    }

отключитьСортировкуПоВысокойОтметке

Можно также отключить параграф ORDER BY [High Water Mark Column]. Однако это не рекомендуется, так как если выполнение индексатора прерывается ошибкой, индексатор должен повторно обработать все строки, если он выполняется позже, даже если индексатор уже обработал почти все строки во время его прерывания. Чтобы отключить предложение ORDER BY, используйте параметр disableOrderByHighWaterMarkColumn в определении индексатора.

    {
     ... other indexer definition properties
     "parameters" : {
            "configuration" : { "disableOrderByHighWaterMarkColumn" : true } }
    }

Политика обнаружения мягкого удаления столбцов

Строки, удаляемые из исходной таблицы, вероятно, также следует удалить из индекса поиска. Если вы используете интегрированную политику отслеживания изменений SQL, это происходит автоматически. Однако политика отслеживания изменений по контрольной отметке не помогает вам с удаленными строками. Что делать?

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

При использовании метода обратимого удаления можно указать соответствующую политику во время создания или обновления источника данных:

    {
        …,
        "dataDeletionDetectionPolicy" : {
           "@odata.type" : "#Microsoft.Azure.Search.SoftDeleteColumnDeletionDetectionPolicy",
           "softDeleteColumnName" : "[a column name]",
           "softDeleteMarkerValue" : "[the value that indicates that a row is deleted]"
        }
    }

softDeleteMarkerValue должен быть строкой в JSON-представлении вашего источника данных. Используйте строковое представление фактического значения. Например, если имеется столбец целочисленных значений, в котором удаленные строки помечаются значением 1, то следует использовать "1". Если имеется битовый столбец, в котором удаленные строки помечаются логическим значением true, то используйте строковый литерал "True" или "true", при этом регистр значения не имеет.

Если вы настраиваете политику обратимого удаления из портала Azure, не добавляйте кавычки вокруг значения метки обратимого удаления. Содержимое поля уже понимается как строка и автоматически преобразуется в строку JSON. В предыдущих примерах просто введите 1, True или true в поле в портале Azure.

Вопросы и ответы

Вопрос. Можно ли индексировать столбцы Always Encrypted?

Нет, столбцы Always Encrypted на данный момент не поддерживаются индексаторами Azure AI Search.

Вопрос. Можно ли использовать индексатор SQL Azure с базами данных SQL на виртуальных машинах IaaS в Azure?

Да. Тем не менее необходимо разрешить службе поиска подключаться к базе данных. Дополнительные сведения см. в статье "Настройка подключения от индексатора поиска ИИ Azure к SQL Server на виртуальной машине Azure".

Вопрос. Можно ли использовать индексатор SQL Azure с локальными базами данных SQL?

Не напрямую. Мы не рекомендуем и не поддерживаем прямое подключение, так как для этого потребуется открыть базы данных для интернет-трафика. Клиенты преуспели в этом сценарии, используя технологии моста, такие как фабрика данных Azure. Дополнительные сведения см. в статье "Отправка данных в индекс поиска ИИ Azure" с помощью Фабрика данных Azure.

Вопрос. Можно ли использовать вторичную реплику в отказоустойчивом кластере в качестве источника данных?

Это зависит от ряда обстоятельств. Для полной индексации таблицы или представления можно использовать вторичные реплики.

Для пошагового индексирования поиск ИИ Azure поддерживает две политики обнаружения изменений: интегрированное отслеживание изменений SQL и отметка высокого уровня.

В репликах только для чтения База данных SQL не поддерживает встроенное отслеживание изменений. Таким образом необходимо использовать политику максимального уровня.

Стандартно рекомендуется использовать тип данных rowversion для столбца отметки верхнего уровня. Однако использование rowversion зависит от MIN_ACTIVE_ROWVERSION функции, которая не поддерживается в репликах с доступом только для чтения. Поэтому при использовании rowversion необходимо указать индексатор на первичную реплику.

Если вы попытаетесь использовать rowversion на реплике только для чтения, вы получите следующую ошибку:

"Использование столбца rowversion для отслеживания изменений не поддерживается во вторичных (только для чтения) реплик доступности. Обновите источник данных и укажите подключение к первичной реплике доступности. Текущее свойство "Updateability" базы данных — "READ_ONLY".

Вопрос. Можно ли использовать другой столбец, не rowversion, для отслеживания изменений максимального уровня?

Не рекомендуется. Только rowversion обеспечивает надежную синхронизацию данных. Однако в зависимости от логики приложения он может быть безопасным, если:

• Вы можете убедиться, что при запуске индексатора отсутствуют незавершенные транзакции в таблице, которая индексируется (например, все обновления таблиц выполняются как пакет по расписанию, а расписание индексатора поиска Azure ИИ не перекрывается с расписанием обновления таблицы).

• Вы периодически выполняете полное повторное индексирование, чтобы обнаружить все пропущенные строки.