Копирование и преобразование данных в Azure SQL Database с помощью Azure Data Factory или Azure Synapse Analytics

ПРИМЕНИМО К: Azure Data Factory Azure Synapse Analytics

Совет

Data Factory в Microsoft Fabric — это следующее поколение Azure Data Factory с более простой архитектурой, встроенным ИИ и новыми функциями. Если вы не знакомы с интеграцией данных, начните с Fabric Data Factory. Существующие рабочие нагрузки ADF могут обновляться до Fabric для доступа к новым возможностям в области обработки и анализа данных, аналитики в режиме реального времени и отчетов.

• Start a Fabric бесплатная пробная версия.
• Переход с Azure Data Factory на Data Factory в Microsoft Fabric

В этой статье описывается использование действия копирования в конвейерах Azure Data Factory или Azure Synapse для копирования данных из Azure SQL Database и использования Data Flow для преобразования данных в Azure SQL Database. Дополнительные сведения см. в вводной статье Azure Data Factory или Azure Synapse Analytics.

Поддерживаемые возможности

Этот соединитель Azure SQL Database поддерживается для следующих возможностей:

Поддерживаемые возможности	IR	Управляемая частная конечная точка
Копирование данных (источник/приемник)	(1) (2)	✓
Поток данных для сопоставления (источник/приемник)	(1)	✓
Операция поиска	(1) (2)	✓
Активность получения метаданных	(1) (2)	✓
Действие скрипта	(1) (2)	✓
Активность хранимой процедуры	(1) (2)	✓

(1) Azure среды выполнения интеграции (2) локальная среда выполнения интеграции

Для Copy activity этот соединитель Azure SQL Database поддерживает следующие функции:

• Копирование данных с использованием аутентификации SQL и аутентификации токенов приложения Microsoft Entra с помощью служебного принципала или управляемых удостоверений для ресурсов Azure.
• В качестве источника — извлечение данных с использованием SQL-запроса или хранимой процедуры. Вы также можете выполнить параллельное копирование из источника Azure SQL Database, смотрите раздел Parallel copy from SQL database для деталей.
• В качестве приемника — автоматическое создание таблицы назначения, если она не существует на основе схемы источника. Добавление данных в таблицу или вызов хранимой процедуры с пользовательской логикой во время копирования.

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

Внимание

При копировании данных с помощью среды выполнения интеграции Azure настройте правило брандмауэра серверного уровня, чтобы Azure службы могли получить доступ к серверу. При копировании данных с помощью локальной среды выполнения интеграции настройте брандмауэр таким образом, чтобы разрешить соответствующий диапазон IP-адресов. Этот диапазон включает IP-адрес компьютера, который используется для подключения к Azure SQL Database.

Начало работы

Для выполнения действия копирования с конвейером можно использовать один из следующих средств или пакетов SDK:

• Средство копирования данных
• портал Azure
• SDK .NET
• пакет SDK Python
• Azure PowerShell
• REST API
• шаблон Azure Resource Manager

Создание связанной службы Azure SQL Database с помощью пользовательского интерфейса

Выполните следующие действия, чтобы создать связанную службу Azure SQL Database в пользовательском интерфейсе портала Azure.

1. Перейдите на вкладку "Управление" в рабочей области Azure Data Factory или Synapse и выберите "Связанные службы", а затем нажмите кнопку "Создать".

   Фабрика данных Azure

   

   Azure Synapse

   

2. Найдите SQL и выберите соединитель Azure SQL Database.

   

3. Настройте сведения о службе, проверьте подключение и создайте связанную службу.

   

Сведения о конфигурации соединителя

В следующих разделах содержатся сведения о свойствах, которые используются для определения сущностей конвейера Azure Data Factory или Synapse, относящихся к соединителю Azure SQL Database.

Свойства связанной службы

Соединитель Azure SQL Database Рекомендуемая версия поддерживает TLS 1.3. Обратитесь к этому разделу , чтобы обновить версию соединителя базы данных Azure SQL с устаревшей версии . Сведения о свойстве см. в соответствующих разделах.

• Рекомендуемая версия
• Устаревшая версия

Совет

Если вы столкнулись с ошибкой с кодом ошибки UserErrorFailedToConnectToSqlServer и сообщением, например "Ограничение сеанса для базы данных равно XXX и достигнуто", добавьте Pooling=false в connection string и повторите попытку. Pooling=false также рекомендуется для установки связанной службы типа SHIR(Self Hosted Integration Runtime). Параметры пула и других подключений можно добавить в виде новых имен параметров и значений в разделе Дополнительные свойства соединения в форме создания связанной службы.

Рекомендуемая версия

Эти универсальные свойства поддерживаются для связанной службы Azure SQL Database при применении Recommended версии:

Свойство	Описание:	Обязательное поле
тип	Для свойства type необходимо задать значение AzureSqlDatabase.	Да
server	Имя или сетевой адрес экземпляра SQL Server, к которому требуется подключиться.	Да
база данных	Имя базы данных.	Да
тип аутентификации	Тип, используемый для проверки подлинности. Допустимые значения: SQL (по умолчанию), ServicePrincipal, SystemAssignedManagedIdentity, UserAssignedManagedIdentity. Перейдите в соответствующий раздел проверки подлинности по определенным свойствам и предварительным требованиям.	Да
настройки всегда зашифрованы	Укажите информацию alwaysencryptedsettings, которая необходима, чтобы включить Always Encrypted и защитить конфиденциальные данные, хранящиеся на сервере SQL, используя Managed Identity или Service Principal. Дополнительные сведения см. в примере JSON после таблицы и в разделе Использование Always Encrypted. Если не указано иное, параметр Always Encrypted по умолчанию отключен.	Нет
шифрование	Указывает, требуется ли шифрование TLS для всех данных, отправляемых между клиентом и сервером. Параметры: обязательный (для true, по умолчанию)/необязательный (для false)/строгий.	Нет
доверятьСертификатуСервера	Укажите, будет ли канал зашифрован при обходе цепочки сертификатов для проверки доверия.	Нет
hostNameInCertificate	Имя узла, используемое при проверке сертификата сервера для подключения. Если он не указан, имя сервера используется для проверки сертификата.	Нет
connectVia	Это среда выполнения интеграции для подключения к хранилищу данных. Можно использовать среду выполнения интеграции Azure или локальную среду выполнения интеграции, если хранилище данных находится в частной сети. Если не указано, используется среда выполнения интеграции по умолчанию Azure.	Нет

Дополнительные свойства подключения см. в следующей таблице:

Свойство	Описание:	Обязательное поле
applicationIntent	Тип рабочей нагрузки приложения при подключении к серверу. Допустимые значения — ReadOnly и ReadWrite.	Нет
connectTimeout	Длина времени (в секундах) для ожидания подключения к серверу перед завершением попытки и создания ошибки.	Нет
connectRetryCount	Количество попыток повторного подключения после выявления сбоя из-за бездействия подключения. Значение должно быть целым числом от 0 до 255.	Нет
connectRetryInterval	Время (в секундах) между каждой попыткой повторного подключения после выявления сбоя бездействия подключения. Значение должно быть целым числом от 1 до 60.	Нет
таймаут_балансировки_нагрузки	Минимальное время (в секундах), в течение которого соединение существует в пуле соединений перед уничтожением.	Нет
commandTimeout	Время ожидания по умолчанию (в секундах) перед завершением попытки выполнения команды и создания ошибки.	Нет
интегрированнаябезопасность	Допустимые значения: true или false. При указании false укажите, указаны ли в подключении имя пользователя и пароль. При указании true указывает, используются ли текущие учетные данные учетной записи Windows для проверки подлинности.	Нет
failoverPartner	Имя или адрес сервера партнера, к которому нужно подключиться, если основной сервер отключен.	Нет
maxPoolSize	Максимальное количество подключений, разрешенных в пуле подключений для конкретного подключения.	Нет
minPoolSize (минимальный размер пула)	Минимальное количество подключений, разрешенных в пуле подключений для конкретного подключения.	Нет
multipleActiveResultSets (множественные активные наборы результатов)	Допустимые значения: true или false. При указании trueприложение может поддерживать несколько активных результирующих наборов (MARS). При указании falseприложение должно обрабатывать или отменять все результирующие наборы из одного пакета, прежде чем он сможет выполнять любые другие пакеты в этом соединении.	Нет
multiSubnetFailover	Допустимые значения: true или false. Если ваше приложение подключается к группе доступности AlwaysOn, расположенной в разных подсетях, установка этого свойства на true ускоряет обнаружение и подключение к текущему активному серверу.	Нет
Размер пакета	Размер в байтах сетевых пакетов, используемых для взаимодействия с экземпляром сервера.	Нет
Пуллинг	Допустимые значения: true или false. При указании trueподключение будет объединяться в пул. При указании falseподключение будет явно открыто при каждом запросе подключения.	Нет

Проверка подлинности SQL

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

Свойство	Описание:	Обязательное поле
userName	Имя пользователя, используемое для подключения к серверу.	Да
пароль	Пароль для имени пользователя. Пометьте это поле как SecureString для безопасного хранения. Кроме того, можно сослаться на секрет, хранящийся в Azure Key Vault.	Да

Пример. Использование проверки подлинности SQL

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "SQL",
            "userName": "<user name>",
            "password": {
                "type": "SecureString",
                "value": "<password>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Example: пароль в Azure Key Vault

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "SQL",
            "userName": "<user name>",
            "password": {
                "type": "AzureKeyVaultSecret",
                "store": {
                    "referenceName": "<Azure Key Vault linked service name>",
                    "type": "LinkedServiceReference"
                },
                "secretName": "<secretName>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Пример. Использование функции Always Encrypted

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "SQL",
            "userName": "<user name>",
            "password": {
                "type": "SecureString",
                "value": "<password>"
            },
            "alwaysEncryptedSettings": {
                "alwaysEncryptedAkvAuthType": "ServicePrincipal",
                "servicePrincipalId": "<service principal id>",
                "servicePrincipalKey": {
                    "type": "SecureString",
                    "value": "<service principal key>"
                }
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Аутентификация субъекта-службы

Чтобы использовать проверку подлинности субъекта-службы, в дополнение к универсальным свойствам, описанным в предыдущем разделе, укажите следующие свойства:

Свойство	Описание:	Обязательное поле
servicePrincipalId	Укажите идентификатора клиента приложения.	Да
servicePrincipalCredential	Учетные данные субъекта-службы. Укажите ключ приложения. Пометьте это поле как SecureString для безопасного хранения или для обращения к секрету, хранящемуся в Azure Key Vault.	Да
клиент	Укажите сведения о клиенте, например доменное имя или идентификатор клиента, в котором находится приложение. Чтобы получить его, наведите указатель мыши на правый верхний угол портала Azure.	Да
azureCloudType	Для проверки подлинности субъекта-службы укажите тип облачной среды Azure, в которой зарегистрировано приложение Microsoft Entra. Допустимые значения: AzurePublic, AzureChina, AzureUsGovernment и AzureGermany. По умолчанию используется облачная среда Фабрики данных Azure или конвейера Synapse.	Нет

Вам также необходимо выполнить следующие шаги:

1. Создайте приложение Microsoft Entra на портале Azure. Запишите имя приложения и следующие значения, которые используются для определения связанной службы:

   • Идентификатор приложения
   • ключ приложения.
   • Идентификатор клиента

2. Настройте администратора Microsoft Entra на портале Azure, если вы еще этого не сделали. Администратор Microsoft Entra должен быть Microsoft Entra пользователем или группой Microsoft Entra, но не может быть субъектом-службой. Этот шаг выполняется так, чтобы на следующем шаге можно использовать удостоверение Microsoft Entra для создания пользователя автономной базы данных для субъекта-службы.

3. Создайте встроенных пользователей базы данных для сервисного принципала. Подключитесь к базе данных, из которой или в которую вы хотите скопировать данные, используя такие средства, как SQL Server Management Studio, и удостоверение Microsoft Entra с разрешениями не ниже, чем ALTER ANY USER. Выполните следующий код T-SQL:

   CREATE USER [your application name] FROM EXTERNAL PROVIDER;
   

4. Предоставьте субъекту-службе необходимые разрешения точно так же, как вы предоставляете разрешения пользователям SQL или другим пользователям. Выполните следующий код. Дополнительные параметры см. в этом документе.

   ALTER ROLE [role name] ADD MEMBER [your application name];
   

5. Настройте связанную службу Azure SQL Database в рабочей области Azure Data Factory или Synapse.

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

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "hostNameInCertificate": "<host name>",
            "authenticationType": "ServicePrincipal",
            "servicePrincipalId": "<service principal id>",
            "servicePrincipalCredential": {
                "type": "SecureString",
                "value": "<application key>"
            },
            "tenant": "<tenant info, e.g. microsoft.onmicrosoft.com>"
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Проверка подлинности с помощью назначенного системой управляемого удостоверения

Фабрика данных или рабочая область Synapse может быть связана с управляемым удостоверением, назначенным системой для ресурсов Azure, представляющим службу при проверке подлинности для других ресурсов в Azure. Это управляемое удостоверение можно использовать для проверки подлинности Azure SQL Database. С помощью этого удостоверения назначенная рабочая область Фабрики или Synapse может обращаться к данным и копировать их из вашей базы данных или в неё.

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

1. Настройте администратора Microsoft Entra на портале Azure, если вы еще этого не сделали. Администратором Microsoft Entra может быть пользователь Microsoft Entra или группа Microsoft Entra. Если вы предоставляете группе с управляемым удостоверением роль администратора, пропустите шаги 3 и 4. Администратор имеет полный доступ к этой базе данных.

2. Создайте пользователей встроенной базы данных для управляемого удостоверения. Подключитесь к базе данных, из которой или в которую вы хотите скопировать данные, используя такие средства, как SQL Server Management Studio, и удостоверение Microsoft Entra с разрешениями не ниже, чем ALTER ANY USER. Выполните следующий код T-SQL:

   CREATE USER [your_resource_name] FROM EXTERNAL PROVIDER;
   

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

   ALTER ROLE [role name] ADD MEMBER [your_resource_name];
   

4. Настройте связанную службу Azure SQL Database.

Пример

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "SystemAssignedManagedIdentity"
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Аутентификация пользовательской управляемой идентичностью

Фабрика данных или рабочая область Synapse может быть связана с назначаемыми пользователем управляемыми удостоверениями, которые представляют службу при аутентификации для других ресурсов в Azure. Это управляемое удостоверение можно использовать для проверки подлинности Azure SQL Database. С помощью этого удостоверения назначенная рабочая область Фабрики или Synapse может обращаться к данным и копировать их из вашей базы данных или в неё.

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

Свойство	Описание:	Обязательное поле
учетные данные	Укажите назначаемое пользователем управляемое удостоверение в качестве объекта учетных данных.	Да

Вам также необходимо выполнить следующие шаги:

1. Настройте администратора Microsoft Entra на портале Azure, если вы еще этого не сделали. Администратором Microsoft Entra может быть пользователь Microsoft Entra или группа Microsoft Entra. Если вы предоставляете группе с управляемым удостоверением, назначаемым пользователем, роль администратора, пропустите шаг 3. Администратор имеет полный доступ к этой базе данных.

2. Создайте пользователей автономной базы данных для управляемого удостоверения, назначаемого пользователем. Подключитесь к базе данных, из которой или в которую вы хотите скопировать данные, используя такие средства, как SQL Server Management Studio, и удостоверение Microsoft Entra с разрешениями не ниже, чем ALTER ANY USER. Выполните следующий код T-SQL:

   CREATE USER [your_resource_name] FROM EXTERNAL PROVIDER;
   

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

   ALTER ROLE [role name] ADD MEMBER [your_resource_name];
   

4. Назначьте одну или несколько пользовательских управляемых идентичностей вашей фабрике данных и создайте учетные данные для каждой из них.

5. Настройте связанную службу Azure SQL Database.

Пример

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "UserAssignedManagedIdentity",
            "credential": {
                "referenceName": "credential1",
                "type": "CredentialReference"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Устаревшая версия

Эти универсальные свойства поддерживаются для связанной службы Azure SQL Database при применении версии Legacy:

Свойство	Описание:	Обязательное поле
тип	Для свойства type необходимо задать значение AzureSqlDatabase.	Да
connectionString	Укажите сведения, необходимые для подключения к экземпляру Azure SQL Database для свойства connectionString. Вы также можете поместить пароль или ключ сервисного принципала в Azure Key Vault. Если это проверка подлинности SQL, извлеките конфигурацию password из connection string. Дополнительные сведения см. в разделе Хранение учетных данных в Azure Key Vault.	Да
настройки всегда зашифрованы	Укажите информацию alwaysencryptedsettings, которая необходима, чтобы включить Always Encrypted и защитить конфиденциальные данные, хранящиеся на сервере SQL, используя Managed Identity или Service Principal. Дополнительные сведения см. в статье Использование функции Always Encrypted. Если не указано иное, параметр Always Encrypted по умолчанию отключен.	Нет
connectVia	Это среда выполнения интеграции для подключения к хранилищу данных. Можно использовать среду выполнения интеграции Azure или локальную среду выполнения интеграции, если хранилище данных находится в частной сети. Если не указано, используется среда выполнения интеграции по умолчанию Azure.	Нет

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

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

Проверка подлинности SQL для устаревшей версии

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

Аутентификация служебного принципала для старой версии

Чтобы использовать проверку подлинности субъекта-службы, в дополнение к универсальным свойствам, описанным в предыдущем разделе, укажите следующие свойства:

Свойство	Описание:	Обязательное поле
servicePrincipalId	Укажите идентификатора клиента приложения.	Да
servicePrincipalKey	Укажите ключ приложения. Пометьте это поле как SecureString для безопасного хранения или ссылаясь на секрет, хранящийся в Azure Key Vault.	Да
клиент	Укажите сведения о клиенте, например доменное имя или идентификатор клиента, в котором находится приложение. Чтобы получить его, наведите указатель мыши на правый верхний угол портала Azure.	Да
azureCloudType	Для проверки подлинности субъекта-службы укажите тип облачной среды Azure, в которой зарегистрировано приложение Microsoft Entra. Допустимые значения: AzurePublic, AzureChina, AzureUsGovernment и AzureGermany. По умолчанию используется облачная среда Фабрики данных Azure или конвейера Synapse.	Нет

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

Проверка подлинности назначаемого системой управляемого удостоверения для устаревшей версии

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

Аутентификация назначаемого пользователем управляемого удостоверения для устаревшей версии

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

Свойства набора данных

Полный список разделов и свойств, доступных для определения наборов данных, см. в разделе Наборы данных.

Для набора данных Azure SQL Database поддерживаются следующие свойства:

Свойство	Описание:	Обязательное поле
тип	Свойство type для набора данных должно иметь значение: AzureSqlTable.	Да
схема	Имя схемы.	"Нет" для источника, "Да" для приемника
таблица	Имя таблицы или представления.	"Нет" для источника, "Да" для приемника
tableName	Имя таблицы или представления со схемой. Это свойство поддерживается только для обеспечения обратной совместимости. Для новой рабочей нагрузки используйте schema и table.	"Нет" для источника, "Да" для приемника

Пример свойств набора данных

{
    "name": "AzureSQLDbDataset",
    "properties":
    {
        "type": "AzureSqlTable",
        "linkedServiceName": {
            "referenceName": "<Azure SQL Database linked service name>",
            "type": "LinkedServiceReference"
        },
        "schema": [ < physical schema, optional, retrievable during authoring > ],
        "typeProperties": {
            "schema": "<schema_name>",
            "table": "<table_name>"
        }
    }
}

Свойства Copy activity

Полный список разделов и свойств, доступных для определения действий, см. в разделе Конвейеры. В этом разделе представлен список свойств, поддерживаемых источником и приемником Azure SQL Database.

Azure SQL Database в качестве источника

Совет

Чтобы эффективно загружать данные из Azure SQL Database с помощью секционирования данных, узнайте больше о копировании Parallel из базы данных SQL.

Чтобы скопировать данные из Azure SQL Database, в разделе действия копирования source поддерживаются следующие свойства:

Свойство	Описание:	Обязательное поле
тип	Свойство type источника действия Copy должно иметь значение AzureSqlSource. Тип SqlSource по-прежнему поддерживается для обратной совместимости.	Да
sqlReaderQuery	Это свойство применяет пользовательский SQL-запрос для чтения данных. Например, select * from MyTable.	Нет
sqlReaderStoredProcedureName	Имя хранимой процедуры, которая считывает данные из исходной таблицы. Последней инструкцией SQL должна быть инструкция SELECT в хранимой процедуре.	Нет
параметры хранимой процедуры	Параметры для хранимой процедуры. Допустимые значения: пары имен или значений. Имена и регистр параметров должны совпадать с именами и регистром параметров хранимой процедуры.	Нет
уровень изоляции	Задает режим блокировки транзакций для источника данных SQL. Допустимые значения: ReadCommitted, ReadUncommitted, RepeatableRead, Serializable, Snapshot. Если значение не указано, используется уровень изоляции базы данных по умолчанию. Дополнительные сведения см. в этом документе.	Нет
параметры_разбиения	Задает параметры секционирования данных, используемые для загрузки данных из Azure SQL Database. Допустимые значения: Нет (по умолчанию), PhysicalPartitionsOfTable и DynamicRange. Если параметр разбиения включен (то есть не None), степень параллелизма для одновременной загрузки данных из Azure SQL Database управляется параметром parallelCopies, установленным в действии копирования.	Нет
настройки раздела	Позволяет указать группу параметров для секционирования данных. Применяется, если параметр секционирования имеет значение, отличное от None.	Нет
В разделе partitionSettings:
partitionColumnName	Укажите имя исходного столбца в виде целого числа или типа date/datetime (int, smallint, bigint, date, smalldatetime, datetime, datetime2 или datetimeoffset), которое будет использоваться для секционирования по диапазонам при параллельном копировании. Если значение не указано, автоматически определяется индекс или первичный ключ таблицы, который затем используется в качестве столбца секционирования. Применяется, если параметр секции имеет значение DynamicRange. Если для получения исходных данных используется запрос, подключите ?DfDynamicRangePartitionCondition в предложении WHERE. Пример можно найти в разделе Параллельное копирование из базы данных SQL.	Нет
верхняя граница раздела	Максимальное значение столбца секционирования для разделения диапазона секционирования. Это значение используется для выбора шага секционирования, а не для фильтрации строк в таблице. Все строки в таблице или результатах запроса будут секционированы и скопированы. Если значение не указано, действие копирования автоматически определяет значение. Применяется, если параметр секции имеет значение DynamicRange. Пример можно найти в разделе Параллельное копирование из базы данных SQL.	Нет
partitionLowerBound	Минимальное значение столбца секционирования для разделения диапазона секционирования. Это значение используется для выбора шага секционирования, а не для фильтрации строк в таблице. Все строки в таблице или результатах запроса будут секционированы и скопированы. Если значение не указано, действие копирования автоматически определяет значение. Применяется, если параметр секции имеет значение DynamicRange. Пример можно найти в разделе Параллельное копирование из базы данных SQL.	Нет

Обратите внимание на следующие моменты.

• Если sqlReaderQuery указан для AzureSqlSource действие копирования запускает этот запрос к источнику Azure SQL Database для получения данных. Есть и другой вариант: создать хранимую процедуру, указав ее имя в sqlReaderStoredProcedureName и параметры в storedProcedureParameters, если она принимает параметры.
• При использовании в источнике хранимой процедуры для получения данных посмотрите, разработана ли хранимая процедура таким образом, чтобы возвращать разные схемы при передаче разных значений параметра. При импорте схемы из пользовательского интерфейса или при копировании данных в базу данных SQL путем автоматического создания таблиц может возникнуть сбой или появиться непредвиденный результат.

Пример SQL-запроса

"activities":[
    {
        "name": "CopyFromAzureSQLDatabase",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<Azure SQL Database input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "AzureSqlSource",
                "sqlReaderQuery": "SELECT * FROM MyTable"
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

Пример хранимой процедуры

"activities":[
    {
        "name": "CopyFromAzureSQLDatabase",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<Azure SQL Database input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "AzureSqlSource",
                "sqlReaderStoredProcedureName": "CopyTestSrcStoredProcedureWithParameters",
                "storedProcedureParameters": {
                    "stringData": { "value": "str3" },
                    "identifier": { "value": "$$Text.Format('{0:yyyy}', <datetime parameter>)", "type": "Int"}
                }
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

Определение хранимой процедуры

CREATE PROCEDURE CopyTestSrcStoredProcedureWithParameters
(
    @stringData varchar(20),
    @identifier int
)
AS
SET NOCOUNT ON;
BEGIN
     select *
     from dbo.UnitTestSrcTable
     where dbo.UnitTestSrcTable.stringData != stringData
    and dbo.UnitTestSrcTable.identifier != identifier
END
GO

Azure SQL Database в качестве приемника

Совет

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

Чтобы скопировать данные в Azure SQL Database, в разделе действия копирования поддерживаются следующие свойства sink:

Свойство	Описание:	Обязательное поле
тип	Свойство type приемника действия Copy должно иметь значение AzureSqlSink. Тип SqlSink по-прежнему поддерживается для обратной совместимости.	Да
preCopyScript	Укажите SQL-запрос для выполнения действия копирования перед записью данных в Azure SQL Database. Он вызывается однократно при каждом запуске копирования. Это свойство используется для очистки предварительно загруженных данных.	Нет
настройка таблицы	Указывает, следует ли автоматически создавать приемную таблицу, если ее еще нет, на основе схемы источника. Если приемник указывает хранимую процедуру, автоматическое создание таблицы не поддерживается. Допустимые значения: none (по умолчанию), autoCreate.	Нет
sqlWriterStoredProcedureName	Имя хранимой процедуры, в которой определяется, как применить исходные данные в целевой таблице. Эта хранимая процедура вызывается для каждого пакета. Для однократно выполняемых операций, в которых не используются исходные данные, например для удаления или усечения данных, примените свойство preCopyScript. См. пример в разделе Вызов хранимой процедуры из приемника SQL.	Нет
storedProcedureTableTypeParameterName	Имя параметра типа таблицы, указанного в хранимой процедуре.	Нет
sqlWriterTableType	Имя типа таблицы для использования в хранимой процедуре. Действие копирования предоставляет доступ к перемещаемым данным во временной таблице с указанным здесь типом. Код хранимой процедуры затем может объединить данные, которые копируются, с существующими данными.	Нет
параметры хранимой процедуры	Параметры для хранимой процедуры. Допустимые значения — это пары из имени и значения. Имена и регистр параметров должны совпадать с именами и регистром параметров хранимой процедуры.	Нет
writeBatchSize	Число строк для вставки в таблицу SQL в одном пакете. Допустимое значение: целое число (количество строк). По умолчанию эта служба динамически определяет соответствующий размер пакета в зависимости от размера строки.	Нет
writeBatchTimeout	Время ожидания завершения операции вставки, upsert или хранимой процедуры до истечения времени отведенного на выполнение. Допустимые значения приведены для интервала времени. Например, 00:30:00 (30 минут). Если значение не указано, время ожидания по умолчанию равно "00:30:00".	Нет
отключить сбор метрик	Служба собирает такие метрики, как DTU в базе данных Azure SQL для оптимизации производительности копирования и предоставления рекомендаций, что требует дополнительного доступа к главной базе данных. Если вас не устраивает такое поведение, укажите true, чтобы отключить его.	Нет (значение по умолчанию — false)
максимальное количество одновременных подключений	Верхний предел одновременных подключений, установленных в хранилище данных при запуске задачи. Указывайте значение только при необходимости ограничить количество одновременных подключений.	Нет
WriteBehavior	Укажите поведение записи для действия копирования для загрузки данных в Azure SQL Database. Допустимые значения: Insert и Upsert. По умолчанию служба использует режим Insert для загрузки данных.	Нет
upsertSettings	Укажите группу параметров для режима записи. Применяется, если параметр WriteBehavior имеет значение Upsert.	Нет
В разделе upsertSettings:
useTempDB	Укажите, следует ли использовать глобальную временную или физическую таблицу в качестве промежуточной таблицы для upsert. По умолчанию служба использует глобальную временную таблицу в качестве промежуточной таблицы. Значение — true.	Нет
interimSchemaName (временноеНазваниеСхемы)	Укажите промежуточную схему для создания промежуточной таблицы, если используется физическая таблица. Примечание. Пользователь должен иметь разрешение на создание и удаление таблиц. По умолчанию промежуточная таблица будет использовать ту же схему, что и таблица приемника. Применяется, если параметр useTempDB имеет значение False.	Нет
ключи	Укажите имена столбцов для уникальной идентификации строк. Можно использовать один ключ или ряд ключей. Если значение не указано, то используется первичный ключ.	Нет

Пример 1. Добавление данных

"activities":[
    {
        "name": "CopyToAzureSQLDatabase",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<Azure SQL Database output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "<source type>"
            },
            "sink": {
                "type": "AzureSqlSink",
                "tableOption": "autoCreate",
                "writeBatchSize": 100000
            }
        }
    }
]

Пример 2. Вызов хранимой процедуры во время копирования

Дополнительные сведения см. в разделе Вызов хранимой процедуры из приемника SQL.

"activities":[
    {
        "name": "CopyToAzureSQLDatabase",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<Azure SQL Database output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "<source type>"
            },
            "sink": {
                "type": "AzureSqlSink",
                "sqlWriterStoredProcedureName": "CopyTestStoredProcedureWithParameters",
                "storedProcedureTableTypeParameterName": "MyTable",
                "sqlWriterTableType": "MyTableType",
                "storedProcedureParameters": {
                    "identifier": { "value": "1", "type": "Int" },
                    "stringData": { "value": "str1" }
                }
            }
        }
    }
]

Пример 3. Операция Upsert с данными

"activities":[
    {
        "name": "CopyToAzureSQLDatabase",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<Azure SQL Database output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "<source type>"
            },
            "sink": {
                "type": "AzureSqlSink",
                "tableOption": "autoCreate",
                "writeBehavior": "upsert",
                "upsertSettings": {
                    "useTempDB": true,
                    "keys": [
                        "<column name>"
                    ]
                },
            }
        }
    }
]

Параллельное копирование из базы данных SQL

Соединитель Azure SQL Database в операции копирования обеспечивает встроенное секционирование для параллельного копирования данных. Параметры секционирования данных можно найти на вкладке Источник действия Copy.

При включении секционированного копирования действие копирования выполняет параллельные запросы к источнику Azure SQL Database для загрузки данных по секциям. Степень параллелизма определяется с помощью параметра parallelCopies для действия копирования. Например, если вы установите значение parallelCopies равным четырем, служба одновременно создает и выполняет четыре запроса на основе указанного параметра разделения и настроек, и каждый запрос извлекает часть данных из вашей базы данных Azure SQL.

Рекомендуется включить параллельную копию с секционированием данных, особенно при загрузке большого объема данных из Azure SQL Database. Ниже приведены рекомендуемые конфигурации для разных сценариев. Если данные копируются в файловое хранилище данных, то рекомендуется сохранять данные в папку несколькими файлами (указывая только имя папки), так как производительность в таком случае будет выше, чем при записи в один файл.

Сценарий	Предлагаемые параметры
Полная загрузка из большой таблицы с физическими разделами.	Параметр секционирования. Физические секции таблицы. Во время выполнения служба автоматически определяет физические секции и копирует данные по секциям. Чтобы проверить, имеет ли таблица физическую секцию, выполните следующий запрос.
Полная загрузка из большой таблицы без физических разделов, при том, что таблица содержит столбец целочисленного типа или типа даты и времени для секционирования данных.	Параметры разбиения: динамическое разбиение диапазонов. Столбец секционирования (необязательно). Укажите столбец для секционирования данных. Если значение не указано, то используется столбец с индексом или первичным ключом. Верхняя граница секционирования и Нижняя граница секционирования (необязательно). Указывайте, если необходимо определить шаг секционирования. Эти значения не предназначены для фильтрации строк в таблице. Все строки в таблице будут секционированы и скопированы. Если значения не указаны, действие Copy автоматически определяет эти значения. К примеру, если ваш столбец раздела "Идентификатор" имеет диапазон значений от 1 до 100 и вы установили нижнюю границу как 20, а верхнюю границу как 80 с параллельным копированием как 4, служба извлекает данные по 4 разделам — идентификаторы в диапазоне <=20, [21, 50], [51, 80] и >=81 соответственно.
Загрузка большого объема данных с помощью пользовательского запроса без использования физических разделов, но с использованием столбца целочисленного типа или типа даты/времени для секционирования данных.	Параметры разбиения: динамическое разбиение диапазонов. Запрос: SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause>. Столбец секционирования: укажите столбец, используемый для секционирования данных. Верхняя граница секционирования и Нижняя граница секционирования (необязательно). Указывайте, если необходимо определить шаг секционирования. Эти значения не предназначены для фильтрации строк в таблице. Все строки в результатах запроса будут секционированы и скопированы. Если значение не указано, действие копирования автоматически определяет значение. К примеру, если ваш столбец раздела "Идентификатор" имеет диапазон значений от 1 до 100, и вы установили нижнюю границу равной 20, а верхнюю границу равной 80, с параллельным копированием равным 4, служба извлекает данные по 4 разделам — идентификаторы в диапазоне <=20, [21, 50], [51, 80] и >=81 соответственно. Ниже приведены дополнительные примеры запросов для различных сценариев. 1. Запросите всю таблицу: SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition 2. Запрос из таблицы с выбором столбцов и дополнительными фильтрами с условиями where. SELECT <column_list> FROM <TableName> WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause> 3. Запрос с вложенными запросами: SELECT <column_list> FROM (<your_sub_query>) AS T WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause> 4. Запрос с разделом в подзапросе: SELECT <column_list> FROM (SELECT <your_sub_query_column_list> FROM <TableName> WHERE ?DfDynamicRangePartitionCondition) AS T

Ниже приведены рекомендации по загрузке данных с параметром секционирования.

1. Чтобы избежать неравномерного распределения данных, выбирайте в качестве столбца секционирования отличительный столбец (например, первичный ключ или уникальный ключ).
2. Если таблица имеет встроенную секцию, используйте параметр секционирования "Физические секции таблицы" для повышения производительности.
3. Если вы используете Azure Integration Runtime для копирования данных, можно задать больше "Data Integration Units (DIU)" (>4) для использования дополнительных вычислительных ресурсов. Ознакомьтесь со сценариями использования этого механизма.
4. Параметр "Степень параллелизма копирования" контролирует номера секций. Если это число слишком велико, это может существенно сказаться на производительности. Рекомендуется задавать это число следующим образом: (DIU или число узлов локальной среды IR) * (от 2 до 4).

Пример. Полная загрузка из большой таблицы с физическими секциями

"source": {
    "type": "AzureSqlSource",
    "partitionOption": "PhysicalPartitionsOfTable"
}

Пример: запрос с секционированием по динамическому диапазону

"source": {
    "type": "AzureSqlSource",
    "query": "SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause>",
    "partitionOption": "DynamicRange",
    "partitionSettings": {
        "partitionColumnName": "<partition_column_name>",
        "partitionUpperBound": "<upper_value_of_partition_column (optional) to decide the partition stride, not as data filter>",
        "partitionLowerBound": "<lower_value_of_partition_column (optional) to decide the partition stride, not as data filter>"
    }
}

Пример запроса для проверки физического раздела

SELECT DISTINCT s.name AS SchemaName, t.name AS TableName, pf.name AS PartitionFunctionName, c.name AS ColumnName, iif(pf.name is null, 'no', 'yes') AS HasPartition
FROM sys.tables AS t
LEFT JOIN sys.objects AS o ON t.object_id = o.object_id
LEFT JOIN sys.schemas AS s ON o.schema_id = s.schema_id
LEFT JOIN sys.indexes AS i ON t.object_id = i.object_id 
LEFT JOIN sys.index_columns AS ic ON ic.partition_ordinal > 0 AND ic.index_id = i.index_id AND ic.object_id = t.object_id 
LEFT JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id 
LEFT JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id 
LEFT JOIN sys.partition_functions pf ON pf.function_id = ps.function_id 
WHERE s.name='[your schema]' AND t.name = '[your table name]'

Если таблица содержит физическую секцию, параметр HasPartition имеет значение yes, как показано ниже.

Рекомендации по загрузке данных в Azure SQL Database

При копировании данных в Azure SQL Database может потребоваться другое поведение записи:

• Добавление: исходные данные содержат только новые записи.
• Upsert: исходные данные содержат как вставки, так и обновления.
• Перезапись: необходимо каждый раз перезагружать всю таблицу измерения.
• Запись с помощью пользовательской логики: перед окончательной вставкой в таблицу назначения требуется дополнительная обработка.

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

Добавление данных.

Добавление данных — это поведение по умолчанию этого соединителя приемника Azure SQL Database. Сервис выполняет массовую вставку для эффективной записи данных в вашу таблицу. Вы можете настроить источник и приемник соответствующим образом в операции копирования.

Обновление или вставка данных (upsert)

Copy activity теперь поддерживает встроенную загрузку данных во временную таблицу базы данных с последующим обновлением данных в целевой таблице, если ключ существует; в противном случае вставляются новые данные. Дополнительные сведения о параметрах upsert в действиях копирования см. в разделе Azure SQL Database в качестве приемника.

Перезапись всей таблицы

Свойство preCopyScript можно настроить в приемнике действия Copy. В этом случае для каждого выполняемого действия копирования служба сначала запускает сценарий. Затем выполняется операция копирования для вставки данных. Например, чтобы перезаписать всю таблицу последними данными, перед массовой загрузкой новых данных из источника укажите сценарий для удаления всех записей.

Запись данных с помощью пользовательской логики

Действия по записи данных с помощью пользовательской логики аналогичны действиям, описанным в разделе Операция Upsert с данными. Если необходимо применить дополнительную обработку перед окончательной вставкой исходных данных в целевую таблицу, можно загрузить в промежуточную таблицу, а затем вызвать действие хранимой процедуры или вызвать хранимую процедуру в приемнике действий копирования для применения данных или использовать сопоставление Data Flow.

Вызов хранимой процедуры из SQL-приемника

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

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

В следующем примере показано, как использовать хранимую процедуру для выполнения операции вставки или обновления в таблицу в Azure SQL Database. Предположим, что и входные данные, и таблица Marketing приемника состоят из трех столбцов: ProfileID, State и Category. Выполните операцию Upsert на основе данных столбца ProfileID только для определенной категории под названием ProductA.

1. В своей базе данных определите тип таблицы с тем же именем, что и sqlWriterTableType. Схема типа таблицы такая же, как схема, возвращаемая вашими входными данными.

   CREATE TYPE [dbo].[MarketingType] AS TABLE(
       [ProfileID] [varchar](256) NOT NULL,
       [State] [varchar](256) NOT NULL,
       [Category] [varchar](256) NOT NULL
   )
   

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

   CREATE PROCEDURE spOverwriteMarketing @Marketing [dbo].[MarketingType] READONLY, @category varchar(256)
   AS
   BEGIN
   MERGE [dbo].[Marketing] AS target
   USING @Marketing AS source
   ON (target.ProfileID = source.ProfileID and target.Category = @category)
   WHEN MATCHED THEN
       UPDATE SET State = source.State
   WHEN NOT MATCHED THEN
       INSERT (ProfileID, State, Category)
       VALUES (source.ProfileID, source.State, source.Category);
   END
   

3. В конвейере Azure Data Factory или Synapse определите приемник SQL в действии копирования следующим образом:

   "sink": {
       "type": "AzureSqlSink",
       "sqlWriterStoredProcedureName": "spOverwriteMarketing",
       "storedProcedureTableTypeParameterName": "Marketing",
       "sqlWriterTableType": "MarketingType",
       "storedProcedureParameters": {
           "category": {
               "value": "ProductA"
           }
       }
   }
   

При записи данных в Azure SQL Database с помощью хранимой процедуры приемник разбивает исходные данные на мини-пакеты, а затем выполняет вставку, поэтому дополнительный запрос в хранимой процедуре можно выполнять несколько раз. Если у вас есть запрос для выполнения действия копирования перед записью данных в Azure SQL Database, не рекомендуется добавлять его в хранимую процедуру; добавьте его в поле скрипт предварительного копирования.

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

При преобразовании данных в потоке данных сопоставления можно читать из таблиц и записывать в них данные в Azure SQL Database. Дополнительные сведения см. в описаниях преобразования источника и преобразования приемника в разделе, посвященном потокам данных для сопоставления.

Преобразование источника

Параметры, относящиеся к Azure SQL Database, доступны на вкладке Source Options преобразования источника.

Входные данные: выберите, указываете ли источник на таблицу (эквивалент Select * from <table-name>) или введите пользовательский SQL-запрос.

Запрос. Если выбрать запрос в поле ввода, введите SQL-запрос для источника. Этот параметр переопределяет любую таблицу, выбранную в наборе данных. Здесь не поддерживаются предложения Order By, но можно задать полноценный оператор SELECT FROM. Кроме того, можно использовать табличные функции, определяемые пользователем. В SQL функция select * from udfGetData() является UDF и возвращает таблицу. Этот запрос создаст исходную таблицу, которую можно использовать в потоке данных. Использование запросов — также отличный способ сокращения количества строк для тестирования или поиска.

Совет

Обобщенное табличное выражение (CTE) в SQL не поддерживается в режиме запроса потока данных для сопоставления, так как в этом режиме требуется использовать запросы в предложении FROM запроса SQL, но обобщенные табличные выражения это не поддерживают. Чтобы использовать CTE, необходимо создать хранимую процедуру с помощью следующего запроса:

CREATE PROC CTESP @query nvarchar(max)
AS
BEGIN
EXECUTE sp_executesql @query;
END

Затем используйте режим хранимой процедуры в преобразовании источника потока данных для сопоставления и задайте @query как в примере with CTE as (select 'test' as a) select * from CTE. Затем можно будет использовать выражения CTE обычным способом.

Хранимая процедура: выберите этот параметр, если необходимо создать проекцию и исходные данные из хранимой процедуры, которая выполняется из базы данных-источника. Можно ввести схему, имя процедуры и параметры или нажать кнопку "Обновить", чтобы запросить обнаружение схем и имен процедур в службе. Затем можно нажать кнопку "Импорт", чтобы импортировать все параметры процедуры с помощью формы @paraName.

• Пример SQL: Select * from MyTable where customerId > 1000 and customerId < 2000
• Пример параметризованного SQL-запроса: "select * from {$tablename} where orderyear > {$year}"

Размер пакета: введите количество для разделения больших объемов данных на части для чтения.

Уровень изоляции: значение по умолчанию для источников SQL в потоке данных сопоставления — чтение без фиксации. Здесь можно изменить уровень изоляции на одно из следующих значений.

• Read Committed (чтение зафиксированных данных)
• Read Uncommitted (чтение незафиксированных данных)
• Повторяющаяся операция чтения
• Сериализуемый
• Нет — игнорировать уровень изоляции

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

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

Включение собственного отслеживания измененных данных (Предварительная версия): используйте этот параметр, чтобы указать ADF обрабатывать только дельта-данные, зафиксированные технологией изменения данных SQL с момента последнего выполнения конвейера. С помощью этого параметра разностные данные, включая вставку строк, обновление и удаление, будут загружаться автоматически без каких-либо добавочных столбцов. Перед использованием этого параметра в ADF необходимо включить контроль изменений данных в базе данных Azure SQL. Дополнительные сведения об этом параметре в ADF см., в сборе собственных данных об изменениях.

Начать чтение с самого начала. При установке этого параметра вместе с добавочным извлечением ADF будет считывать все строки при первом выполнении конвейера с включенным добавочным извлечением.

Преобразование приемника

Параметры, относящиеся к Azure SQL Database, доступны на вкладке Settings преобразования приемника.

Метод обновления. Определяет, какие операции разрешены в назначении базы данных. По умолчанию разрешены только операции вставки. Для обновления, вставки с изменением (upsert) или удаления строк требуется преобразование alter-row для пометки строк, предназначенных для этих действий. Для обновлений, вставок и удалений ключевой столбец или столбцы должны быть заданы, чтобы определить, какие строки изменять.

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

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

Действие таблицы: определяет, следует ли повторно создавать или удалять все строки из целевой таблицы перед записью.

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

Размер пакета: определяет количество строк, записываемых в каждом контейнере. Более крупные размеры пакетов улучшают сжатие и оптимизацию памяти, но при кэшировании данных возникает риск нехватки памяти.

Использование TempDB: по умолчанию служба будет использовать глобальную временную таблицу для хранения данных в ходе процесса загрузки. Можно также снять флажок "Использовать TempDB" и вместо этого попросить службу сохранить временную таблицу в пользовательской базе данных, расположенной в базе данных, которая используется для этого приемника.

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

Совет

1. Рекомендуется разбивать пакетные скрипты с несколькими командами на несколько пакетов.
2. В качестве части пакета могут выполняться только инструкции языка описания данных DDL и языка обработки данных DML, возвращающие простой счетчик обновлений. Узнайте больше о выполнении пакетных операций.

Обработка строк с ошибками

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

• Символьные или двоичные данные будут усечены в таблице.
• Не удалось вставить значение NULL в столбец.
• Конфликт инструкции INSERT с ограничением CHECK.

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

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

Вывод отклоненных данных: Если включено, вы можете выводить ошибочные строки в файл CSV в Azure Blob Storage или выбранное вами хранилище Azure Data Lake Storage Gen2. При этом строки с ошибками будут записаны с тремя дополнительными столбцами: операции SQL, например INSERT или UPDATE, код ошибки потока данных и сообщение об ошибке в строке.

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

Сопоставление типов данных для Azure SQL Database

При копировании данных в или из Azure SQL Database следующие сопоставления используются для преобразования типов данных Azure SQL Database в промежуточные типы данных Azure Data Factory. Те же сопоставления используются функцией конвейера Synapse, которая реализует Azure Data Factory напрямую. Дополнительные сведения о том, как действие копирования сопоставляет исходную схему и тип данных с приемником, см. в статье Сопоставление схем и типов данных.

тип данных Azure SQL Database	Тип промежуточных данных службы Data Factory
bigint	Int64
двоичный	Byte[]
бит	Логический
char	Строка, символ[]
Дата	ДатаВремя
Дата и время	ДатаВремя
datetime2	ДатаВремя
Datetimeoffset	DateTimeOffset (датайм оффсет)
Десятичное число	Десятичное число
Атрибут FILESTREAM (varbinary(max))	Byte[]
Тип с плавающей запятой	Двойной
Изображение	Byte[]
INT	Int32
деньги	Десятичное число
nchar	Строка, символ[]
ntext	Строка, символ[]
числовой	Десятичное число
nvarchar	Строка, символ[]
реальный	Одна
rowversion (версия строки)	Byte[]
smalldatetime	ДатаВремя
smallint	Int16
smallmoney	Десятичное число
sql_variant	Объект
текст	Строка, символ[]
Время	TimeSpan
метка времени	Byte[]
tinyint	Байт
уникальный идентификатор	GUID
varbinary	Byte[]
varchar	Строка, символ[]
xml	Строка

Примечание.

Для типов данных, которые сопоставляют с десятичным промежуточным типом, в настоящее время Copy activity поддерживает точность до 28. При наличии данных с точностью больше 28 их можно преобразовать в строку в SQL-запросе.

Свойства операции поиска

Подробные сведения об этих свойствах см. в разделе Действие поиска.

Свойства активности GetMetadata

Подробные сведения об этих свойствах см. в статье Действие GetMetadata.

Использование Always Encrypted

При копировании данных из Azure SQL Database с помощью Always Encrypted выполните следующие действия:

1. Сохраните главный ключ Column (CMK) в Azure Key Vault. Дополнительные сведения о том, как настроить Always Encrypted с помощью Azure Key Vault.

2. Обязательно получите доступ к хранилищу ключей, где хранится главный ключ столбца (CMK ). В данной статье приведен список необходимых разрешений.

3. Создайте связанную службу для подключения к SQL-базе данных и включите функцию "Always Encrypted", используя либо управляемое удостоверение, либо основной идентификатор службы.

Примечание.

Azure SQL Database Always Encrypted поддерживает следующие сценарии:

1. Источники или приемники данных используют управляемое удостоверение или служебный принципал для аутентификации поставщика ключей.
2. Хранилища данных источника и приемника используют управляемую идентификацию в качестве типа проверки подлинности поставщика ключей.
3. В хранилищах данных источника и приемника используется тот же субъект-служба, что и для типа проверки подлинности поставщика ключей.

Примечание.

В настоящее время Azure SQL Database Always Encrypted не поддерживается в качестве конечного узла в потоках сопоставления данных.

Извлечение данных о изменениях в нативной форме

Azure Data Factory поддерживает собственные возможности захвата измененных данных для SQL Server, Azure SQL Database и Azure SQL MI. Измененные данные, включая вставку строк, обновление и удаление в хранилищах SQL, можно автоматически обнаружить и извлечь из потока данных сопоставления ADF. Без использования кода в сопоставлении потока данных пользователи могут легко достичь сценария репликации данных из хранилищ SQL, добавив базу данных в качестве целевого хранилища. Более того, пользователи могут создавать любую логику преобразования данных, чтобы обеспечить инкрементальный сценарий ETL из SQL-хранилищ.

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

При отладке конвейера эта функция работает таким же образом. Имейте в виду, что при перезагрузке браузера в процессе отладки контрольная точка будет сброшена. Когда вы будете довольны результатами отладки конвейера, его можно опубликовать и запустить. В тот момент, когда вы впервые запускаете опубликованный конвейер, он автоматически перезапускается с самого начала или начинает применять изменения с этого момента.

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

Пример 1:

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

source(output(
		id as integer,
		name as string
	),
	allowSchemaDrift: true,
	validateSchema: false,
	enableNativeCdc: true,
	netChanges: true,
	skipInitialLoad: false,
	isolationLevel: 'READ_UNCOMMITTED',
	format: 'table') ~> source1
source1 sink(allowSchemaDrift: true,
	validateSchema: false,
	deletable:true,
	insertable:true,
	updateable:true,
	upsertable:true,
	keys:['id'],
	format: 'table',
	skipDuplicateMapInputs: true,
	skipDuplicateMapOutputs: true,
	errorHandlingOption: 'stopOnFirstError') ~> sink1

Пример 2:

Если вы хотите включить сценарий ETL вместо репликации данных между базой данных через SQL CDC, можно использовать выражения в сопоставлении потоков данных, включая isInsert(1), isUpdate(1) и isDelete(1), чтобы различать строки с различными типами операций. Ниже приведен один из примеров скриптов для сопоставления потока данных для создания одного столбца со значением: 1, чтобы указать вставленные строки, 2, чтобы указать обновленные строки и 3, чтобы указать удаленные строки для преобразования нижестоящего потока для обработки разностных данных.

source(output(
		id as integer,
		name as string
	),
	allowSchemaDrift: true,
	validateSchema: false,
	enableNativeCdc: true,
	netChanges: true,
	skipInitialLoad: false,
	isolationLevel: 'READ_UNCOMMITTED',
	format: 'table') ~> source1
source1 derive(operationType = iif(isInsert(1), 1, iif(isUpdate(1), 2, 3))) ~> derivedColumn1
derivedColumn1 sink(allowSchemaDrift: true,
	validateSchema: false,
	skipDuplicateMapInputs: true,
	skipDuplicateMapOutputs: true) ~> sink1

Известное ограничение:

• Только итоговые изменения из SQL CDC будут загружены в ADF через cdc.fn_cdc_get_net_changes_.

Обновление версии Azure SQL Database

Чтобы обновить версию Azure SQL Database, на странице Редактирование связанной службы выберите Рекомендуемая в разделе Версия и настройте связанную службу, обратившись к свойствам связанной службы для рекомендуемой версии.

Различия между рекомендуемой и устаревшей версией

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

Рекомендуемая версия	Устаревшая версия
Поддерживать TLS 1.3 через encrypt в качестве strict.	TLS 1.3 не поддерживается.

Связанный контент

Список хранилищ данных, которые поддерживаются в качестве источников и приемников для действия копирования, приведен в таблице Поддерживаемые хранилища данных и форматы.