Безопасная загрузка данных с помощью 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. Подписи общего доступа (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.
)

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

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

Предварительные условия

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
   Назначить доступ к	СервисPrincipal
   Участники	Сервер или рабочая область, на котором размещен выделенный пул 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 в Azure SQL.

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. Получите конечную точку токена версии V1 для OAuth 2.0.

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.
• Прочитайте обзорную статью о загрузке данных, где приведены рекомендации по загрузке.