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

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

Совет

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

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

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

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

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

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

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

В частности, этот соединитель SQL Server поддерживает:

• SQL Server версии 2005 и выше.
• Копирование данных посредством использования проверки подлинности SQL или Windows.
• В качестве источника — извлечение данных с использованием SQL-запроса или хранимой процедуры. Можно также выбрать параллельное копирование из источника базы данных SQL Server. Дополнительные сведения см. в разделе Параллельное копирование из базы данных SQL.
• В качестве приемника автоматическое создание таблицы назначения, если таковая не существует, на основе схемы источника; добавление данных в таблицу или вызов хранимой процедуры с пользовательской логикой во время копирования.

SQL Server Express LocalDB не поддерживается.

Внимание

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

Требования

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

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

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

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

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

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

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

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

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

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

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

   

   Azure Synapse

   

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

   

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

   

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

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

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

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

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

Совет

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

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

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

Свойство	Описание:	Обязательное поле
тип	Для свойства type необходимо задать значение SqlServer.	Да
server	Имя или сетевой адрес экземпляра SQL Server, к которому требуется подключиться.	Да
база данных	Имя базы данных.	Да
тип аутентификации	Тип, используемый для проверки подлинности. Допустимые значения — SQL (по умолчанию), Windows и UserAssignedManagedIdentity (только для SQL Server на виртуальных машинах Azure). Перейдите в соответствующий раздел проверки подлинности по определенным свойствам и предварительным требованиям.	Да
настройки всегда зашифрованы	Укажите информацию alwaysencryptedsettings, которая необходима, чтобы включить Always Encrypted и защитить конфиденциальные данные, хранящиеся на сервере SQL, используя Managed Identity или Service Principal. Дополнительные сведения см. в примере JSON после таблицы и в разделе Использование Always Encrypted. Если не указано иное, параметр Always Encrypted по умолчанию отключен.	Нет
шифрование	Указывает, требуется ли шифрование TLS для всех данных, отправляемых между клиентом и сервером. Параметры: обязательный (для true, по умолчанию)/необязательный (для false)/строгий.	Нет
доверятьСертификатуСервера	Укажите, будет ли канал зашифрован при обходе цепочки сертификатов для проверки доверия.	Нет
hostNameInCertificate	Имя узла, используемое при проверке сертификата сервера для подключения. Если он не указан, имя сервера используется для проверки сертификата.	Нет
connectVia	Это среда выполнения интеграции для подключения к хранилищу данных. Дополнительные сведения см. в разделе Предварительные условия. Если не указано другое, используется среда выполнения интеграции 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": "SqlServerLinkedService",
    "properties": {
        "type": "SqlServer",
        "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"
        }
    }
}

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

{
    "name": "SqlServerLinkedService",
    "properties": {
        "type": "SqlServer",
        "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": "SqlServerLinkedService",
    "properties": {
        "type": "SqlServer",
        "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"
        }
    }
}

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

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

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

Примечание.

Проверка подлинности Windows в потоке данных не поддерживается.

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

{
    "name": "SqlServerLinkedService",
    "properties": {
        "type": "SqlServer",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "Windows",
            "userName": "<domain\\username>",
            "password": {
                "type": "SecureString",
                "value": "<password>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Пример. Использование проверки подлинности Windows с паролем в Azure Key Vault

{
    "name": "SqlServerLinkedService",
    "properties": {
        "annotations": [],
        "type": "SqlServer",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "Windows",
            "userName": "<domain\\username>",
            "password": {
                "type": "AzureKeyVaultSecret",
                "store": {
                    "referenceName": "<Azure Key Vault linked service name>",
                    "type": "LinkedServiceReference"
                },
                "secretName": "<secretName>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

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

Примечание.

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

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

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

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

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

1. Предоставьте разрешения управляемому удостоверению, назначаемого пользователем.

2. Включите проверку подлинности Microsoft Entra для вашего SQL Server на виртуальных машинах Azure.

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

   CREATE USER [your_resource_name] FROM EXTERNAL PROVIDER;
   

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

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

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

6. Настройте связанную службу SQL Server.

Пример

{
    "name": "SqlServerLinkedService",
    "properties": {
        "type": "SqlServer",
        "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 Server, если применяется версия устаревшая:

Свойство	Описание:	Обязательное поле
тип	Для свойства type необходимо задать значение SqlServer.	Да
настройки всегда зашифрованы	Укажите информацию alwaysencryptedsettings, которая необходима, чтобы включить Always Encrypted и защитить конфиденциальные данные, хранящиеся на сервере SQL, используя Managed Identity или Service Principal. Дополнительные сведения см. в статье Использование функции Always Encrypted. Если не указано иное, параметр Always Encrypted по умолчанию отключен.	Нет
connectVia	Это среда выполнения интеграции для подключения к хранилищу данных. Дополнительные сведения см. в разделе Предварительные условия. Если не указано другое, используется среда выполнения интеграции Azure по умолчанию.	Нет

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

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

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

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

Свойство	Описание:	Обязательное поле
connectionString	Укажите информацию connectionString, необходимую для соединения с базой данных SQL Server. Укажите имя входа в качестве имени пользователя и убедитесь, что база данных, которую требуется подключить, сопоставляется с этим именем входа.	Да
пароль	Если вы хотите создать пароль в Azure Key Vault, извлеките конфигурацию password из строки подключения. Дополнительные сведения см. в разделе "Хранение учетных данных" в Azure Key Vault.	Нет

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

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

Свойство	Описание:	Обязательное поле
connectionString	Укажите информацию connectionString, необходимую для соединения с базой данных SQL Server.	Да
userName	Укажите имя пользователя. Например, имя_домена\имя_пользователя.	Да
пароль	Введите пароль для учетной записи пользователя, указанной для выбранного имени пользователя. Пометьте это поле как SecureString для безопасного хранения. Вы можете также указать секрет, хранящийся в Azure Key Vault.	Да

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

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

Поддерживаются следующие свойства для копирования данных из базы данных SQL Server и обратно.

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

Пример

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

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

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

SQL Server в качестве источника

Совет

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

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

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

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

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

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

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

Пример. Использование хранимой процедуры

"activities":[
    {
        "name": "CopyFromSQLServer",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<SQL Server input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "SqlSource",
                "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 Server в качестве приемника

Совет

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

Чтобы скопировать данные в базу данных SQL Server, установите тип приемника SqlSink в действии копирования. В разделе sink для действия копирования поддерживаются следующие свойства.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

"source": {
    "type": "SqlSource",
    "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 Server

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примечание.

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

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

В таблице ниже перечислены свойства, поддерживаемые источником SQL Server. Изменить эти свойства можно на вкладке Source options (Параметры источника).

Имя	Описание:	Обязательное поле	Допустимые значения	Свойство скрипта потоков данных
Таблица	Если выбрать таблицу в качестве входных данных, поток данных извлекает все данные из таблицы, указанной в наборе данных.	Нет	-	-
Запрос	При выборе запроса в качестве входных данных укажите SQL-запрос для выборки данных из источника, переопределяющий любую таблицу, указанную в наборе данных. Использование запросов — отличный способ сокращения количества строк для тестирования или поиска. Предложение Order By не поддерживается, но можно задать полную инструкцию SELECT FROM. Кроме того, можно использовать табличные функции, определяемые пользователем. select * from udfGetData() — это определяемая пользователем функция в SQL, которая возвращает таблицу для использования в потоке данных. Пример запроса: Select * from MyTable where customerId > 1000 and customerId < 2000	Нет	Строка	запрос
Размер пакета	Укажите размер пакета для разделения больших наборов данных на блоки для чтения.	Нет	Целое число	batchSize
Уровень изоляции	Выберите один из следующих уровней изоляции: - Read Committed (чтение зафиксированных данных) - Read Uncommitted (по умолчанию) - Repeatable Read (повторяющаяся операция чтения) - Serializable (сериализуемый) - Нет (игнорировать уровень изоляции)	Нет	READ_COMMITTED Чтение без фиксации REPEATABLE_READ (повторяемое чтение) Сериализуемый нет	уровень изоляции
Включение добавочного извлечения	Используйте этот параметр, чтобы указать ADF обрабатывать только те строки, которые изменились с момента последнего выполнения конвейера.	Нет	-	-
Столбец добавочной даты	При использовании функции добавочного извлечения необходимо выбрать столбец даты и времени, который вы хотите использовать в качестве подложки в исходной таблице.	Нет	-	-
Активировать встроенную фиксацию изменений данных (предварительная версия)	Используйте этот параметр, чтобы сообщить ADF об обработке только разностных данных, захваченных технологией отслеживания изменений SQL с момента последнего выполнения конвейера. С помощью этого параметра разностные данные, включая вставку строк, обновление и удаление, будут автоматически загружаться без каких-либо добавочных столбцов дат. Перед использованием этого параметра в ADF необходимо включить запись измененных данных в SQL Server. Дополнительные сведения об этом параметре в ADF см., в сборе собственных данных об изменениях.	Нет	-	-
Начните читать с начала.	Если задать этот параметр с добавочным извлечением, ADF будет настроен считывать все строки во время первого выполнения конвейера с включенным добавочным извлечением.	Нет	-	-

Совет

Обобщенное табличное выражение (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 обычным способом.

Пример скрипта исходного кода для SQL Server

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

source(allowSchemaDrift: true,
    validateSchema: false,
    isolationLevel: 'READ_UNCOMMITTED',
    query: 'select * from MYTABLE',
    format: 'query') ~> SQLSource

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

В таблице ниже перечислены свойства, поддерживаемые приемником SQL Server. Эти свойства можно изменить на вкладке Параметры приемника.

Имя	Описание:	Обязательное поле	Допустимые значения	Свойство скрипта потоков данных
Метод обновления	Укажите, какие операции допускаются в месте назначения базы данных. По умолчанию разрешены только операции вставки. Чтобы обновить, вставить или удалить строки, требуется преобразование изменения строк, чтобы отметить строки для этих действий.	Да	true или false	удаляемый Вставляемый обновляемый обновляемый либо вставляемый
Ключевые столбцы	Для выполнения обновлений, вставок и удалений должны быть установлены ключевые столбцы, чтобы определить, какие строки необходимо изменить. Имя столбца, которое вы выберете в качестве ключа, будет использоваться при выполнении последующих операций обновления, upsert и удаления. Поэтому необходимо выбрать столбец, существующий в сопоставлении приемника.	Нет	Массив	ключи
Пропустить ввод ключевых столбцов	Если вы не хотите записывать значение в ключевой столбец, выберите "Skip writing key columns" (Пропустить запись ключевых столбцов).	Нет	true или false	skipKeyWrites
Операция с таблицей	Определяет, следует ли повторно создавать или удалять все строки в целевой таблице перед записью. - Нет: никаких действий с таблицей не будет произведено. - Создать повторно: таблица будет удалена и создана повторно. Это действие необходимо, если новая таблица создается динамически. - Усечь: все строки в таблице назначения будут удалены.	Нет	true или false	воссоздать усекать
Размер пакета	Укажите, сколько строк записывается в каждый пакет. Более крупные размеры пакетов улучшают сжатие и оптимизацию памяти, но при кэшировании данных возникает риск нехватки памяти.	Нет	Целое число	batchSize
Скрипты SQL предобработки и постобработки	Укажите многострочные скрипты SQL, которые будут выполняться до (предобработка) и после (постобработка) записи данных в базу данных-приемник.	Нет	Строка	preSQLs postSQLs

Совет

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

Пример скрипта для приемника SQL Server

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

IncomingStream sink(allowSchemaDrift: true,
    validateSchema: false,
    deletable:false,
    insertable:true,
    updateable:true,
    upsertable:true,
    keys:['keyColumn'],
    format: 'table',
    skipDuplicateMapInputs: true,
    skipDuplicateMapOutputs: true) ~> SQLSink

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

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

Тип данных SQL Server	Тип промежуточных данных службы 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	Int16
уникальный идентификатор	GUID
varbinary	Byte[]
varchar	Строка, символ[]
xml	Строка

Примечание.

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

При копировании данных из SQL Server с помощью Фабрики данных Azure битовый тип данных сопоставляется с логическим промежуточным типом данных. Если у вас есть данные, которые должны храниться в качестве битового типа данных, используйте запросы с T-SQL CAST или CONVERT.

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

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

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

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

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

Копируя данные с SQL Server или на него при помощи функции Always Encrypted, выполняйте следующие действия.

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

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

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

Примечание.

Функция Always Encrypted SQL Server поддерживает следующие сценарии:

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

Примечание.

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

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

Фабрика данных 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

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

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

Устранение неполадок с подключением

1. Настройте экземпляр SQL Server для приема удаленных подключений. Запустите SQL Server Management Studio, щелкните правой кнопкой мыши по своему серверу и выберите пункт Свойства. Выберите в списке пункт Подключения и поставьте галочку в поле Разрешить удаленные подключения к этому серверу.

   

   Подробные инструкции см. в статье Настройка параметра конфигурации сервера удаленного доступа.

2. Запустите диспетчер конфигурации SQL Server. Разверните узел Сетевая конфигурация SQL Server для нужного экземпляра и выберите пункт Protocols for MSSQLSERVER (Протоколы для MSSQLSERVER). Протоколы отображаются в правой панели. Включите TCP/TP, щелкнув правой кнопкой мыши по имени протокола TCP/IP и выбрав пункт Включить.

   

   Подробные сведения и альтернативные способы включения протокола TCP/IP см. в статье Включение или отключение сетевого протокола сервера.

3. В этом же окне дважды щелкните TCP/IP, чтобы открыть окно TCP/IP Properties (Свойства TCP/IP).

4. Перейдите на вкладку IP-адреса. Прокрутите вниз до раздела IPAll. Запишите порт TCP. Значение по умолчанию — 1433.

5. Создайте на компьютере правило брандмауэра Windows , чтобы разрешить входящий трафик через этот порт.

6. Проверьте подключение. Чтобы подключиться к SQL Server, используя полное имя, используйте SQL Server Management Studio с другого компьютера. Например, "<machine>.<domain>.corp.<company>.com,1433".

Обновление версии SQL Server

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

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

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

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

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

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