Synapse SQL を使用してデータを安全に読み込むSecurely load data using Synapse SQL

この記事では、COPY ステートメントのセキュリティで保護された認証メカニズムについて説明し、例を示します。This article highlights and provides examples on the secure authentication mechanisms for the COPY statement. COPY ステートメントは、Synapse SQL でデータを一括読み込みするための最も柔軟で安全な方法です。The COPY statement is the most flexible and secure way of bulk loading data in Synapse SQL.

サポートされている認証メカニズムSupported authentication mechanisms

次の表は、サポートされている認証方法をファイルの種類別およびストレージ アカウント別にまとめたものです。The following matrix describes the supported authentication methods for each file type and storage account. これはソース ストレージの場所とエラー ファイルの場所に適用されます。This applies to the source storage location and the error file location.

CSVCSV ParquetParquet ORCORC
Azure Blob StorageAzure blob storage SAS/MSI/サービス プリンシパル/キー/AADSAS/MSI/SERVICE PRINCIPAL/KEY/AAD SAS/キーSAS/KEY SAS/キーSAS/KEY
Azure Data Lake Gen2Azure Data Lake Gen2 SAS/MSI/サービス プリンシパル/キー/AADSAS/MSI/SERVICE PRINCIPAL/KEY/AAD SAS (BLOB エンドポイント)/MSI (dfs エンドポイント)/サービス プリンシパル/キー/AADSAS (blob endpoint)/MSI (dfs endpoint)/SERVICE PRINCIPAL/KEY/AAD SAS (BLOB エンドポイント)/MSI (dfs エンドポイント)/サービス プリンシパル/キー/AADSAS (blob endpoint)/MSI (dfs endpoint)/SERVICE PRINCIPAL/KEY/AAD

A.A. 行を終止させるものとして LF が与えられたストレージ アカウント キー (Unix スタイルの改行)Storage account key with LF as the row terminator (Unix-style new line)

--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='x6RWv4It5F2msnjelv3H4DA80n0QW0daPdw43jM0nyetx4c6CpDkdj3986DX5AHFMIf/YN4y6kkCnU8lb+Wx0Pj+6MDw=='),
    ,ROWTERMINATOR='0x0A' --0x0A specifies to use the Line Feed character (Unix based systems)
)

重要

  • 16 進数の値 (0x0A) を使用し、改行文字を指定します。Use the hexadecimal value (0x0A) to specify the Line Feed/Newline character. COPY ステートメントでは "\n" 文字列が "\r\n" (復帰改行) として解釈されます。Note the COPY statement will interpret the '\n' string as '\r\n' (carriage return newline).

B.B. 行を終止させるものとして CRLF が与えられた Shared Access Signatures (SAS) (Windows スタイルの改行)Shared Access Signatures (SAS) with CRLF as the row terminator (Windows style new line)

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%2FKiJDSFSYsz4AkNa%2F%2BTx61FuQ%2FfKHefqoBE%3D'),
    ,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" として解釈され、構文解析の問題が引き起こされる可能性がありますDo not specify the ROWTERMINATOR as '\r\n' which will be interpreted as '\r\r\n' and can result in parsing issues

C.C. マネージド IDManaged Identity

ストレージ アカウントが VNet に関連付けられるとき、マネージド ID 認証が必要です。Managed Identity authentication is required when your storage account is attached to a VNet.

前提条件Prerequisites

  1. このガイドを使用して、Azure PowerShell をインストールします。Install Azure PowerShell using this guide.
  2. 汎用 v1 または BLOB ストレージ アカウントを使用している場合は、このガイドを使用して、最初に汎用 v2 にアップグレードする必要があります。If you have a general-purpose v1 or blob storage account, you must first upgrade to general-purpose v2 using this guide.
  3. Azure ストレージ アカウントの [Firewalls and Virtual networks](ファイアウォールと仮想ネットワーク) 設定メニューで、 [Allow trusted Microsoft services to access this storage account](信頼された Microsoft サービスによるこのストレージ アカウントに対するアクセスを許可します) をオンにする必要があります。You must have Allow trusted Microsoft services to access this storage account turned on under Azure Storage account Firewalls and Virtual networks settings menu. 詳しくは、このガイドをご覧ください。Refer to this guide for more information.

手順Steps

  1. PowerShell で、Azure Active Directory にお使いの SQL サーバーを登録しますIn PowerShell, register your SQL server with Azure Active Directory:

    Connect-AzAccount
    Select-AzSubscription -SubscriptionId your-subscriptionId
    Set-AzSqlServer -ResourceGroupName your-database-server-resourceGroup -ServerName your-database-servername -AssignIdentity
    
  2. このガイドを使用して、汎用 v2 ストレージ アカウントを作成します。Create a general-purpose v2 Storage Account using this guide.

    注意

    汎用 v1 または BLOB ストレージ アカウントを使用している場合は、このガイドを使用して、最初に v2 にアップグレードする必要があります。If you have a general-purpose v1 or blob storage account, you must first upgrade to v2 using this guide.

  3. お使いのストレージ アカウントで、 [アクセス制御 (IAM)] に移動し、 [ロール割り当ての追加] を選択します。Under your storage account, navigate to Access Control (IAM), and select Add role assignment. ストレージ BLOB データ所有者、共同作成者、または閲覧者の Azure ロールを SQL サーバーに割り当てます。Assign Storage Blob Data Owner, Contributor, or Reader Azure role to your SQL server.

    注意

    所有者特権を持つメンバーのみが、この手順を実行できます。Only members with Owner privilege can perform this step. さまざまな Azure の組み込みロールについては、こちらのガイドを参照してください。For various Azure built-in roles, refer to this guide.

    重要

    ストレージ BLOB データ所有者、共同作成者、または閲覧者の Azure ロールを指定します。Specify the Storage Blob Data Owner, Contributor, or Reader Azure role. これらのロールは、所有者、共同作成者、閲覧者の Azure 組み込みロールとは異なります。These roles are different than the Azure built-in roles of Owner, Contributor, and Reader.

    読み込みのための RBAC アクセス許可の付与

  4. これで "マネージド ID" を指定して COPY ステートメントを実行できます。You can now run the COPY statement specifying "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.D. Azure Active Directory 認証Azure Active Directory Authentication

手順Steps

  1. お使いのストレージ アカウントで、 [アクセス制御 (IAM)] に移動し、 [ロール割り当ての追加] を選択します。Under your storage account, navigate to Access Control (IAM), and select Add role assignment. ストレージ BLOB データ所有者、共同作成者、または閲覧者の Azure ロールを Azure AD ユーザーに割り当てます。Assign Storage Blob Data Owner, Contributor, or Reader Azure role to your Azure AD user.

    重要

    ストレージ BLOB データ所有者、共同作成者、または閲覧者の Azure ロールを指定します。Specify the Storage Blob Data Owner, Contributor, or Reader Azure role. これらのロールは、所有者、共同作成者、閲覧者の Azure 組み込みロールとは異なります。These roles are different than the Azure built-in roles of Owner, Contributor, and Reader.

    読み込みのための RBAC アクセス許可の付与

  2. 次のドキュメントを進め、Azure AD 認証を構成します。Configure Azure AD authentication by going through the following documentation.

  3. Active Directory を使用し、SQL プールに接続します。そこでは資格情報を指定せずに COPY ステートメントを実行できます。Connect to your SQL pool using Active Directory where you can now run the COPY statement without specifying any credentials:

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

E.E. サービス プリンシパル認証Service Principal Authentication

手順Steps

  1. Azure Active Directory アプリケーションを作成するCreate an Azure Active Directory application

  2. アプリケーション ID を取得するGet application ID

  3. 認証キーを取得するGet the authentication key

  4. V1 OAuth 2.0 トークン エンドポイントを取得するGet the V1 OAuth 2.0 token endpoint

  5. ストレージ アカウントで Azure AD アプリケーションに読み取り、書き込み、実行のアクセス許可を割り当てるAssign read, write, and execution permissions to your Azure AD application on your storage account

  6. これで COPY ステートメントを実行できます。You can now run the COPY statement:

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

重要

  • OAuth 2.0 トークン エンドポイント V1 バージョンを使用しますUse the V1 version of the OAuth 2.0 token endpoint

次のステップNext steps