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

ОБЛАСТЬ ПРИМЕНЕНИЯ: Фабрика данных Azure Azure Synapse Analytics

Совет

Попробуйте использовать фабрику данных в Microsoft Fabric, решение для аналитики с одним интерфейсом для предприятий. Microsoft Fabric охватывает все, от перемещения данных до обработки и анализа данных в режиме реального времени, бизнес-аналитики и отчетности. Узнайте, как бесплатно запустить новую пробную версию !

В этой статье описано, как использовать действие Copy в конвейерах Фабрики данных Azure или Azure Synapse, чтобы копировать данные в Базу данных SQL Azure и обратно, а также использовать Поток данных для преобразования данных в Базе данных SQL Azure. Дополнительные сведения см. в вводной статье о Фабрике данных Azure или Azure Synapse Analytics.

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

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

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

① Среда выполнения интеграции Azure ② Локальная среда выполнения интеграции

Этот соединитель Базы данных SQL Microsoft Azure поддерживает следующие функции для действия Copy:

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

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

Внимание

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

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

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

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

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

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

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

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

   

   Azure Synapse

   

2. Выполните поиск SQL и выберите соединитель Базы данных SQL Azure.

   

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

   

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

Следующие разделы содержат сведения о свойствах, которые используются для определения сущностей конвейера Фабрики данных Azure или Synapse, характерных для соединителя Базы данных SQL Microsoft Azure.

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

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

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

Совет

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

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

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

Свойство	Описание:	Обязательное поле
type	Для свойства type необходимо задать значение AzureSqlDatabase.	Да
server	Имя или сетевой адрес экземпляра SQL Server, к которому требуется подключиться.	Да
database	Имя базы данных.	Да
authenticationType	Тип, используемый для проверки подлинности. Допустимые значения: SQL (по умолчанию), ServicePrincipal, SystemAssignedManagedIdentity, UserAssignedManagedIdentity. Перейдите в соответствующий раздел проверки подлинности по определенным свойствам и предварительным требованиям.	Да
alwaysEncryptedSettings	Укажите информацию alwaysencryptedsettings, которая необходима для включения Always Encrypted для защиты конфиденциальных данных, хранящихся на сервере SQL, с помощью управляемого удостоверения или субъекта-службы. Дополнительные сведения см. в примере JSON после таблицы и в разделе Использование Always Encrypted. Если не указано иное, параметр Always Encrypted по умолчанию отключен.	No
шифрование	Указывает, требуется ли шифрование TLS для всех данных, отправляемых между клиентом и сервером. Параметры: обязательный (для true, default)/необязательный (для false)/strict.	No
trustServerCertificate	Укажите, будет ли канал зашифрован при обходе цепочки сертификатов для проверки доверия.	No
hostNameInCertificate	Имя узла, используемое при проверке сертификата сервера для подключения. Если он не указан, имя сервера используется для проверки сертификата.	No
connectVia	Это среда выполнения интеграции для подключения к хранилищу данных. Вы можете использовать Azure Integration Runtime или локальную среду IR (если хранилище данных расположено в частной сети). Если не указано другое, используется среда выполнения интеграции Azure по умолчанию.	No

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

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

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

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

Свойство	Описание:	Обязательное поле
userName	Имя пользователя, используемое для подключения к серверу.	Да
password	Пароль для имени пользователя. Пометьте это поле как 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"
        }
    }
}

Пример. Пароль в 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.	Да
tenant	Укажите сведения о клиенте, например доменное имя или идентификатор клиента, в котором находится приложение. Эти сведения можно получить, наведя указатель мыши на правый верхний угол страницы портала Azure.	Да
azureCloudType	Для проверки подлинности субъекта-службы укажите тип облачной среды Azure, в которой зарегистрировано приложение Microsoft Entra. Допустимые значения: AzurePublic, AzureChina, AzureUsGovernment и AzureGermany. По умолчанию используется облачная среда Фабрики данных Azure или конвейера Synapse.	No

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

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

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

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. Настройте связанную службу Базы данных SQL Azure в рабочей области Фабрики данных Azure или 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. Это управляемое удостоверение можно использовать для проверки подлинности Базы данных SQL Azure. С помощью этого удостоверения назначенная рабочая область Фабрики или 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. Настройте связанную службу Базы данных SQL Azure.

Пример

{
    "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. Это управляемое удостоверение можно использовать для проверки подлинности Базы данных SQL Azure. С помощью этого удостоверения назначенная рабочая область Фабрики или 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. Настройте связанную службу Базы данных SQL Azure.

Пример

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

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

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

Свойство	Описание:	Обязательное поле
type	Для свойства type необходимо задать значение AzureSqlDatabase.	Да
connectionString	В свойстве connectionString указываются сведения, необходимые для подключения к экземпляру Базы данных SQL Azure. Вы также можете поместить пароль или ключ субъекта-службы в Azure Key Vault. Если это проверка подлинности SQL, извлеките конфигурацию password из строки подключения. Дополнительные сведения см. в разделе "Хранение учетных данных" в Azure Key Vault.	Да
alwaysEncryptedSettings	Укажите информацию alwaysencryptedsettings, которая необходима для включения Always Encrypted для защиты конфиденциальных данных, хранящихся на сервере SQL, с помощью управляемого удостоверения или субъекта-службы. Дополнительные сведения см. в статье Использование функции Always Encrypted. Если не указано иное, параметр Always Encrypted по умолчанию отключен.	No
connectVia	Это среда выполнения интеграции для подключения к хранилищу данных. Вы можете использовать Azure Integration Runtime или локальную среду IR (если хранилище данных расположено в частной сети). Если не указано другое, используется среда выполнения интеграции Azure по умолчанию.	No

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

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

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

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

Проверка подлинности субъекта-службы для устаревшей версии

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

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

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

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

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

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

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

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

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

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

Свойство	Описание:	Обязательное поле
type	Свойство type для набора данных должно иметь значение: AzureSqlTable.	Да
schema	Имя схемы.	"Нет" для источника, "Да" для приемника
table	Имя таблицы или представления.	"Нет" для источника, "Да" для приемника
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>"
        }
    }
}

Свойства действия копирования

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

База данных SQL Azure в качестве источника

Совет

Чтобы эффективно загружать данные из Базы данных SQL Microsoft Azure с использованием секционирования данных, изучите дополнительные сведения в разделе Параллельное копирование из Базы данных SQL.

При копировании данных из Базы данных SQL Microsoft Azure в разделе source для действия Copy поддерживаются следующие свойства.

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

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

• Если для AzureSqlSource указан запрос sqlReaderQuery, то действие Copy выполняет этот запрос для источника Базы данных SQL Microsoft Azure для получения нужных данных. Есть и другой вариант: создать хранимую процедуру, указав ее имя в 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

База данных SQL Azure в качестве приемника

Совет

Дополнительные сведения о поддерживаемых расширениях функциональности записи, конфигурациях и рекомендациях см. в разделе Рекомендации по загрузке данных в Базу данных SQL Microsoft Azure.

Для копирования данных в Базу данных SQL Microsoft Azure в разделе sink для действия Copy поддерживаются следующие свойства.

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

Пример 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

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

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

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

Сценарий	Предлагаемые параметры
Полная загрузка из большой таблицы с физическими секциями.	Параметр секционирования. Физические секции таблицы. Во время выполнения служба автоматически определяет физические секции и копирует данные по секциям. Чтобы проверить, имеет ли таблица физическую секцию, выполните следующий запрос.
Полная загрузка из большой таблицы без физических секций, когда таблица содержит столбец целочисленного типа или типа даты и времени для секционирования данных.	Параметры секции: секция динамического диапазона. Столбец секционирования (необязательно). Укажите столбец для секционирования данных. Если значение не указано, то используется столбец с индексом или первичным ключом. Верхняя граница секционирования и Нижняя граница секционирования (необязательно). Указывайте, если необходимо определить шаг секционирования. Эти значения не предназначены для фильтрации строк в таблице. Все строки в таблице будут секционированы и скопированы. Если значения не указаны, действие 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, то в параметре Единицы интеграции данных (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, как показано ниже.

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

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

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

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

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

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

Операция upsert с данными

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

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

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

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

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

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

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

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

В следующем примере показано, как использовать хранимую процедуру для выполнения операции Upsert в таблице базы данных SQL Azure. Предположим, что и входные данные, и таблица 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 или Synapse определите раздел приемника SQL в действии копирования следующим образом.

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

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

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

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

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

Параметры, относящиеся к Базе данных SQL Microsoft Azure, находятся на вкладке Параметры источника преобразования источника.

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

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

Совет

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

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 (чтение незафиксированных данных)
• Повторяющаяся операция чтения
• Упорядочиваемый уровень изоляции
• None (игнорировать уровень изоляции)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Совет

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

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

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

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

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

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

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

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

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

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

Тип данных в Базе данных SQL Azure	Тип промежуточных данных фабрики данных
bigint	Int64
binary	Byte[]
bit	Логический
char	String, Char[]
Дата	DateTime
Datetime	DateTime
datetime2	Дата/время
Datetimeoffset	DateTimeOffset
Десятичное число	Десятичное число
FILESTREAM attribute (varbinary(max))	Byte[]
Тип с плавающей запятой	Двойной
Изображение	Byte[]
INT	Int32
money	Десятичное число
nchar	String, Char[]
ntext	String, Char[]
numeric	Десятичное число
nvarchar	String, Char[]
real	Одна
rowversion	Byte[]
smalldatetime	Дата/время
smallint	Int16
smallmoney	Десятичное число
sql_variant	Object
text	String, Char[]
Время	TimeSpan
TIMESTAMP	Byte[]
tinyint	Байт
uniqueidentifier	GUID
varbinary	Byte[]
varchar	String, Char[]
xml	Строка

Примечание.

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

Свойства действия поиска

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

Свойства действия GetMetadata

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

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

При копировании данных из Базы данных SQL Azure или в нее при помощи функции Always Encrypted выполните следующие действия.

1. Сохраните главный ключ столбца (CMK) в Azure Key Vault. Ознакомьтесь с порядком настройки функции Always Encrypted при помощи Azure Key Vault

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

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

Примечание.

Функция Always Encrypted в Базе данных SQL Azure поддерживает следующие сценарии.

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

Примечание.

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

Сбор данных об изменениях в собственном коде

Фабрика данных Azure может поддерживать собственные возможности отслеживания измененных данных для SQL Server, базы данных SQL Azure и 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

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

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

Обновление версии База данных SQL Azure

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

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

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

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

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

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