Индексирование данных из SQL Azure в службе "Поиск искусственного интеллекта Azure"

Статья
07/31/2023

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

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

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

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

Примечание.

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

Необходимые компоненты

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

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

Используйте представление, если необходимо объединить данные из нескольких таблиц. Большие представления не идеально подходят для индексатора SQL. Обходной путь — создать новую таблицу только для приема в индекс поиска ИИ Azure. Вы сможете использовать интегрированное отслеживание изменений SQL, которое проще реализовать, чем High Water Mark.
Разрешения на чтение. Поиск ИИ Azure поддерживает проверку подлинности SQL Server, где имя пользователя и пароль предоставляются в строка подключения. Кроме того, можно настроить управляемое удостоверение и использовать роли Azure.

Для работы с примерами в этой статье требуется клиент REST.

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

Определение источника данных

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

Создайте источник данных или обновите источник данных, чтобы задать его определение:

 POST https://myservice.search.windows.net/datasources?api-version=2020-06-30
 Content-Type: application/json
 api-key: admin-key

 {
     "name" : "myazuresqldatasource",
     "description" : "A database for testing Azure AI Search indexes.",
     "type" : "azuresql",
     "credentials" : { "connectionString" : "Server=tcp:<your server>.database.windows.net,1433;Database=<your database>;User ID=<your user name>;Password=<your password>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;" },
     "container" : { 
         "name" : "name of the table or view that you want to index",
         "query" : null (not supported in the Azure SQL indexer)
         },
     "dataChangeDetectionPolicy": null,
     "dataDeletionDetectionPolicy": null,
     "encryptionKey": null,
     "identity": null
 }

Укажите уникальное имя источника данных, который следует соглашениям об именовании поиска ИИ Azure.
Задайте для параметра type "azuresql" (обязательный).
Задайте для параметра "учетные данные" строка подключения:
- Вы можете получить полный доступ строка подключения из портал Azure. Использовать параметр ADO.NET connection string. Задайте имя пользователя и пароль.
- Кроме того, можно указать управляемое удостоверение строка подключения, включающее секреты базы данных со следующим форматом. Initial Catalog|Database=<your database name>;ResourceId=/subscriptions/<your subscription ID>/resourceGroups/<your resource group name>/providers/Microsoft.Sql/servers/<your SQL Server name>/;Connection Timeout=connection timeout length;
Дополнительные сведения см. в Подключение для База данных SQL Azure индексатора с помощью управляемого удостоверения.

Добавление полей поиска в индекс

В индексе поиска добавьте поля, соответствующие полям в базе данных SQL. Убедитесь, что схема индекса поиска совместима с исходной схемой с помощью эквивалентных типов данных.

Создайте или обновите индекс , чтобы определить поля поиска, которые будут хранить данные:

POST https://[service name].search.windows.net/indexes?api-version=2020-06-30
Content-Type: application/json
api-key: [Search service admin key]
{
    "name": "mysearchindex",
    "fields": [{
        "name": "id",
        "type": "Edm.String",
        "key": true,
        "searchable": false
    }, 
    {
        "name": "description",
        "type": "Edm.String",
        "filterable": false,
        "searchable": true,
        "sortable": false,
        "facetable": false,
        "suggestions": true
    }
  ]
}

Создайте поле ключа документа ("key": true), которое однозначно идентифицирует каждый документ поиска. Это единственное поле, необходимое в индексе поиска. Как правило, первичный ключ таблицы сопоставляется с полем ключа индекса. Ключ документа должен быть уникальным и ненулевым. Значения могут быть числовыми в исходных данных, но в индексе поиска ключ всегда является строкой.
Создайте дополнительные поля, чтобы добавить дополнительное содержимое, доступные для поиска. Дополнительные сведения см. в статье "Создание индекса ".

Сопоставление типов данных

Тип данных SQL	Типы полей поиска ИИ Azure	Примечания.
bit	Edm.Boolean, Edm.String
int, smallint, tinyint	Edm.Int32, Edm.Int64, Edm.String
bigint	Edm.Int64, Edm.String
real, float	Edm.Double, Edm.String
smallmoney, money decimal numeric	Edm.String	Поиск по искусственному интеллекту Azure не поддерживает преобразование десятичных типов в `Edm.Double` , так как это приведет к потере точности.
char, nchar, varchar, nvarchar	Edm.String Collection(Edm.String)	Строку SQL можно использовать для заполнения поля Collection(`Edm.String`), если строка представляет массив строк JSON: `["red", "white", "blue"]`
smalldatetime, datetime, datetime2, date, datetimeoffset	Edm.DateTimeOffset, Edm.String
uniqueidentifer	Edm.String
география	Edm.GeographyPoint	Поддерживаются только географические объекты типа POINT с SRID 4326 (значение по умолчанию).
rowversion	Нет данных	Столбцы версии строк не могут храниться в индексе поиска, но их можно использовать для отслеживания изменений.
time, timespan, binary, varbinary, image, xml, geometry, CLR types	Нет данных	Не поддерживается

Настройка и запуск индексатора SQL Azure

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

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

POST https://[service name].search.windows.net/indexers?api-version=2020-06-30
Content-Type: application/json
api-key: [search service admin key]
{
    "name" : "[my-sqldb-indexer]",
    "dataSourceName" : "[my-sqldb-ds]",
    "targetIndexName" : "[my-search-index]",
    "disabled": null,
    "schedule": null,
    "parameters": {
        "batchSize": null,
        "maxFailedItems": 0,
        "maxFailedItemsPerBatch": 0,
        "base64EncodeKeys": false,
        "configuration": {
            "queryTimeout": "00:04:00",
            "convertHighWaterMarkToRowVersion": false,
            "disableOrderByHighWaterMarkColumn": false
        }
    },
    "fieldMappings": [],
    "encryptionKey": null
}

В разделе "Параметры" есть параметры, относящиеся к SQL Azure:
- Время ожидания запроса по умолчанию для выполнения SQL-запроса — 5 минут, которые можно переопределить.
- ConvertHighWaterMarkToRowVersion оптимизирует политику обнаружения изменений с высоким уровнем воды. Политики обнаружения изменений задаются в источнике данных. Если вы используете собственную политику обнаружения изменений, этот параметр не действует.
- "disableOrderByHighWaterMarkColumn" приводит к тому, что SQL-запрос, используемый политикой высокой водяной метки, опустит предложение ORDER BY. Если вы используете собственную политику обнаружения изменений, этот параметр не действует.
Укажите сопоставления полей, если существуют различия в имени или типе поля, или если в индексе поиска требуется несколько версий исходного поля.
Дополнительные сведения о других свойствах см. в статье "Создание индексатора ".

Индексатор запускается автоматически при его создании. Это можно предотвратить, задав для параметра "Отключено" значение true. Чтобы управлять выполнением индексатора, запустите индексатор по запросу или поместите его в расписание.

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

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

GET https://myservice.search.windows.net/indexers/myindexer/status?api-version=2020-06-30
  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 Server 2012 с пакетом обновления 3 (SP3) и более поздних версий, если используется SQL Server на виртуальных машинах Azure
База данных Azure SQL или управляемый экземпляр SQL
Только таблицы (без представлений)
В базе данных включите отслеживание изменений для таблицы.
Составной первичный ключ (первичный ключ, содержащий более одного столбца) в таблице
Кластеризованные индексы в таблице не отображаются. В качестве обходного решения все кластеризованные индексы необходимо удалить и повторно создать как некластеризованный индекс, однако производительность может повлиять на источник по сравнению с кластеризованным индексом.

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

POST https://myservice.search.windows.net/datasources?api-version=2020-06-30
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.

Примечание.

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

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

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

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

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

Примечание.

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

POST https://myservice.search.windows.net/datasources?api-version=2020-06-30
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]"
        }
    }

Примечание.

Если в исходной таблице нет индекса в столбце высокой водяной отметки, запросы, используемые индексатором SQL, могут истекть. В частности, предложение ORDER BY [High Water Mark Column] требует эффективного выполнения индекса, если таблица содержит много строк.

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" } }
    }

disableOrderByHighWaterMarkColumn

Можно также отключить предложение 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. В приведенных выше примерах просто введите 1True или true в поле портала.

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

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

№ Столбцы Always Encrypted в настоящее время не поддерживаются индексаторами поиска ИИ Azure.

Вопрос. Можно ли использовать индексатор 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 ИИ не перекрывается с расписанием обновления таблицы).
Вы периодически выполняете полное повторное индексирование, чтобы обнаружить все пропущенные строки.

Индексирование данных из SQL Azure в службе "Поиск искусственного интеллекта Azure"

Необходимые компоненты

Определение источника данных

Добавление полей поиска в индекс

Сопоставление типов данных

Настройка и запуск индексатора SQL Azure

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

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

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

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

convertHighWaterMarkToRowVersion

queryTimeout

disableOrderByHighWaterMarkColumn

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

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

Дополнительные ресурсы