Безопасная загрузка данных с помощью Synapse SQL

В этой статье описано использование безопасных способов проверки подлинности для инструкции COPY и приведены соответствующие примеры. Инструкция COPY — это наиболее гибкий и безопасный способ массовой загрузки данных в Synapse SQL.

Поддерживаемые способы проверки подлинности

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

1: для этого способа проверки подлинности требуется конечная точка .blob (.blob.core.windows.net) в пути к внешней папке.

2: для этого способа проверки подлинности требуется конечная точка .dfs (.dfs.core.windows.net) в пути к внешней папке.

А. Ключ учетной записи хранения с символами LF в качестве признака конца строки (новая строка в стиле UNIX)

--Note when specifying the column list, input field numbers start from 1
COPY INTO target_table (Col_one default 'myStringDefault' 1, Col_two default 1 3)
FROM 'https://adlsgen2account.dfs.core.windows.net/myblobcontainer/folder1/'
WITH (
    FILE_TYPE = 'CSV'
    ,CREDENTIAL=(IDENTITY= 'Storage Account Key', SECRET='<Your_Account_Key>')
    --CREDENTIAL should look something like this:
    --CREDENTIAL=(IDENTITY= 'Storage Account Key', SECRET='<secret>'),
    ,ROWTERMINATOR='0x0A' --0x0A specifies to use the Line Feed character (Unix based systems)
)

B. Подписанные URL-адреса (SAS) с символами CRLF в качестве признака конца строки (новая строка в стиле Windows)

COPY INTO target_table
FROM 'https://adlsgen2account.dfs.core.windows.net/myblobcontainer/folder1/'
WITH (
    FILE_TYPE = 'CSV'
    ,CREDENTIAL=(IDENTITY= 'Shared Access Signature', SECRET='<Your_SAS_Token>')
    --CREDENTIAL should look something like this:
    --CREDENTIAL=(IDENTITY= 'Shared Access Signature', SECRET='?sv=2018-03-28&ss=bfqt&srt=sco&sp=rl&st=2016-10-17T20%3A14%3A55Z&se=2021-10-18T20%3A19%3A00Z&sig=IEoOdmeYnE9%2FKiJDSFSYsz4AkN'),
    ,ROWTERMINATOR='\n'-- COPY command automatically prefixes the \r character when \n (newline) is specified. This results in carriage return newline (\r\n) for Windows based systems.
)

C. Управляемое удостоверение

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

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

1. Установите Azure PowerShell. См. раздел Установка PowerShell.
2. При наличии учетной записи хранения общего назначения версии 1 или учетной записи хранения BLOB-объектов необходимо сначала выполнить обновление до учетной записи хранения общего назначения версии 2. См. статью Обновление до учетной записи хранения общего назначения версии 2.
3. Необходимо включить параметр Разрешить доверенным службам Майкрософт доступ к этой учетной записи хранения в меню параметров Брандмауэры и виртуальные сети учетной записи службы хранилища Azure. См. статью Настройка брандмауэров службы хранилища Azure и виртуальных сетей.

Шаги

1. Если у вас есть автономный выделенный пул SQL, зарегистрируйте сервер SQL с помощью идентификатора Microsoft Entra с помощью PowerShell:

   Connect-AzAccount
   Select-AzSubscription -SubscriptionId <subscriptionId>
   Set-AzSqlServer -ResourceGroupName your-database-server-resourceGroup -ServerName your-SQL-servername -AssignIdentity
   

   Этот шаг не требуется для выделенных пулов SQL в рабочей области Synapse. Назначаемое системой управляемое удостоверение (SA-MI) для рабочей области является членом роли администратора Synapse и поэтому получает более высокий уровень привилегий для выделенных пулов SQL рабочей области.

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

   Примечание.

   • При наличии учетной записи хранения общего назначения версии 1 или учетной записи хранилища BLOB-объектов необходимо сначала выполнить обновление до версии 2. Дополнительные сведения см. в статье Обновление до учетной записи хранения общего назначения версии 2.
   • Сведения об известных проблемах с Azure Data Lake Storage 2-го поколения см. в статье Известные проблемы с Azure Data Lake Storage 2-го поколения.

3. В своей учетной записи хранения выберите Управление доступом (IAM).

4. Выберите Добавить>Добавить назначение ролей, чтобы открыть страницу "Добавление назначения ролей".

5. Назначьте следующую роль. Подробные инструкции см. в статье Назначение ролей Azure с помощью портала Microsoft Azure.

   Параметр	Значение
   Роль	Участник данных хранилища BLOB-объектов
   Назначить доступ для	SERVICEPRINCIPAL
   Участники	сервер или рабочая область, на котором размещен выделенный пул SQL, зарегистрированный с помощью идентификатора Microsoft Entra

   

   Примечание.

   Этот шаг могут выполнять только участники с правами владельца. Сведения о различных встроенных ролях Azure см. в этом руководстве.

   Внимание

   Укажите одну из следующих ролей Azure: владелец, участник или читатель данных BLOB-объектов хранилища. Эти роли отличаются от встроенных ролей Azure владельца, участника и читателя.

   

6. Теперь можно выполнить инструкцию COPY, указав Managed Identity.

   COPY INTO dbo.target_table
   FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/*.txt'
   WITH (
       FILE_TYPE = 'CSV',
       CREDENTIAL = (IDENTITY = 'Managed Identity'),
   )
   

D. Проверка подлинности Microsoft Entra

Шаги

1. В своей учетной записи хранения выберите Управление доступом (IAM).

2. Выберите Добавить>Добавить назначение ролей, чтобы открыть страницу "Добавление назначения ролей".

3. Назначьте следующую роль. Подробные инструкции см. в статье Назначение ролей Azure с помощью портала Microsoft Azure.

   Параметр	Значение
   Роль	Владелец данных BLOB-объектов хранилища, участник или читатель
   Назначить доступ для	Пользователь
   Участники	Пользователь Microsoft Entra

   

   Внимание

   Укажите одну из следующих ролей Azure: владелец, участник или читатель данных BLOB-объектов хранилища. Эти роли отличаются от встроенных ролей Azure владельца, участника и читателя.

   

4. Настройка проверки подлинности Microsoft Entra. Сведения о настройке проверки подлинности Microsoft Entra и управлении ими с помощью SQL Azure.

5. Подключитесь к пулу SQL с помощью Active Directory. Теперь вы можете выполнять инструкцию COPY без указания учетных данных.

   COPY INTO dbo.target_table
   FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/*.txt'
   WITH (
       FILE_TYPE = 'CSV'
   )
   

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

Шаги

1. Создайте приложение Microsoft Entra.

2. Получите идентификатор приложения.

3. Получите ключ проверки подлинности.

4. Получите конечную точку маркера OAuth 2.0 версии V1.

5. Назначьте разрешения на чтение, запись и выполнение приложению Microsoft Entra в учетной записи хранения.

6. Выполните инструкцию COPY.

   COPY INTO dbo.target_table
   FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder0/*.txt'
   WITH (
       FILE_TYPE = 'CSV'
       ,CREDENTIAL=(IDENTITY= '<application_ID>@<OAuth_2.0_Token_EndPoint>' , SECRET= '<authentication_key>')
       --CREDENTIAL should look something like this:
       --,CREDENTIAL=(IDENTITY= '92761aac-12a9-4ec3-89b8-7149aef4c35b@https://login.microsoftonline.com/72f714bf-86f1-41af-91ab-2d7cd011db47/oauth2/token', SECRET='juXi12sZ6gse]woKQNgqwSywYv]7A.M')
   )
   

Следующие шаги

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