Поделиться через

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

  • Статья

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

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

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

CSV Parquet ORC
Хранилище BLOB-объектов Azure SAS/MSI/SERVICE PRINCIPAL/KEY/AAD SAS/KEY SAS/KEY
Azure Data Lake 2-го поколения SAS/MSI/SERVICE PRINCIPAL/KEY/AAD SAS (blob1)/MSI (dfs2)/SERVICE PRINCIPAL/KEY/AAD SAS (blob1)/MSI (dfs2)/SERVICE PRINCIPAL/KEY/AAD

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)
)

Внимание

  • Символ перевода строки или новой строки указывается с помощью шестнадцатеричного значения (0x0A). Обратите внимание, что инструкция COPY интерпретирует строку \n как \r\n (возврат каретки и новая строка).

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.
)

Внимание

Не указывайте ROWTERMINATOR в виде «\r\n», поскольку эта строка будет интерпретирована как «\r\r\n», что может привести к ошибкам синтаксического анализа. При выполнении команды COPY к символу \n (новая строка) автоматически добавляется префикс \r. В результате системы на основе Windows определяют символ новой строки (\r\n).

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. Дополнительные сведения см. в разделе Создание учетной записи хранения.

    Примечание.

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

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

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

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

    Страница

    Примечание.

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

    Внимание

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

    Предоставление разрешения Azure RBAC на загрузку

  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 владельца, участника и читателя.

    Предоставление разрешения Azure RBAC на загрузку

  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')
)

Внимание

Используйте версию V1 конечной точки маркера OAuth 2.0.

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