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

この記事では、COPY ステートメントのセキュリティで保護された認証メカニズムについて説明し、例を示します。 COPY ステートメントは、Synapse SQL でデータを一括読み込みするための最も柔軟で安全な方法です。

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

次の表は、サポートされている認証方法をファイルの種類別およびストレージ アカウント別にまとめたものです。 これはソース ストレージの場所とエラー ファイルの場所に適用されます。

CSV Parquet ORC
Azure Blob Storage SAS/MSI/サービス プリンシパル/キー/AAD SAS/キー SAS/キー
Azure Data Lake Gen2 SAS/MSI/サービス プリンシパル/キー/AAD SAS (BLOB1)/MSI (dfs2)/サービス プリンシパル/キー/AAD SAS (BLOB1)/MSI (dfs2)/サービス プリンシパル/キー/AAD

1: この認証方法には、外部ロケーション パスの .blob エンドポイント (.blob.core.windows.net) が必要です。

2: この認証方法には、外部ロケーション パスの .dfs エンドポイント (.dfs.core.windows.net) が必要です。

A. 行を終止させるものとして 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)
)

重要

  • 16 進数の値 (0x0A) を使用し、改行文字を指定します。 COPY ステートメントでは、\n 文字列は \r\n (復帰改行) として解釈されることに注意してください。

B. 行を終止させるものとして CRLF が与えられた Shared Access Signatures (SAS) (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" として解釈されて、構文解析の問題が発生する可能性があります。 \n (改行) が指定されると、COPY コマンドによって自動的に \r 文字が接頭部に追加されます。 これにより、Windows ベースのシステムでは復帰改行 (\r\n) が指定されます。

C. マネージド ID

ストレージ アカウントが VNet に関連付けられるとき、マネージド ID 認証が必要です。

前提条件

  1. Azure PowerShell をインストールします。 PowerShell のインストールに関する記事を参照してください。
  2. 汎用 v1 または BLOB ストレージ アカウントを使用している場合は、最初に汎用 v2 にアップグレードする必要があります。 「汎用 v2 ストレージ アカウントにアップグレードする」を参照してください。
  3. Azure ストレージ アカウントの [Firewalls and Virtual networks](ファイアウォールと仮想ネットワーク) 設定メニューで、 [Allow trusted Microsoft services to access this storage account](信頼された Microsoft サービスによるこのストレージ アカウントに対するアクセスを許可します) をオンにする必要があります。 「Azure Storage ファイアウォールおよび仮想ネットワークを構成する」を参照してください。

手順

  1. スタンドアロンの専用 SQL プールがある場合は、PowerShell を使用して SQL サーバーを Azure Active Directory (Azure AD) に登録します。

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

    Synapse ワークスペース内の専用 SQL プールでは、この手順は必要ありません。

  2. Synapse ワークスペースがある場合は、以下のようにワークスペースのシステム マネージド ID を登録します。

    1. Azure portal で Synapse ワークスペースにアクセスします。
    2. [マネージド ID] ページに移動します。
    3. [パイプラインの許可] オプションが有効になっていることを確認します。

    Register workspace system msi

  3. 汎用 v2 ストレージ アカウントを作成します。 詳しくは、「ストレージ アカウントの作成」をご覧ください。

    注意

  4. 自分のストレージ アカウントで [アクセス制御 (IAM)] を選択します。

  5. [追加][ロールの割り当ての追加] を選択して、[ロールの割り当ての追加] ページを開きます。

  6. 次のロールを割り当てます。 詳細な手順については、「Azure portal を使用して Azure ロールを割り当てる」を参照してください。

    設定
    Role ストレージ BLOB データ共同作成者
    アクセスの割り当て先 サービス プリンシパル
    メンバー Azure Active Directory (Azure AD) に登録した専用 SQL プールをホストしているサーバーまたはワークスペース

    Add role assignment page in Azure portal.

    Note

    所有者特権を持つメンバーのみが、この手順を実行できます。 さまざまな Azure の組み込みロールについては、「Azure 組み込みロール」を参照してください。

    重要

    ストレージBLOB データ所有者、共同作成者、または閲覧者の Azure ロールを指定します。 これらのロールは、所有者、共同作成者、閲覧者の Azure 組み込みロールとは異なります。

    Granting Azure RBAC permission to load

  7. これで "マネージド ID" を指定して COPY ステートメントを実行できます。

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

D. Azure Active Directory 認証

手順

  1. 自分のストレージ アカウントで [アクセス制御 (IAM)] を選択します。

  2. [追加][ロールの割り当ての追加] を選択して、[ロールの割り当ての追加] ページを開きます。

  3. 次のロールを割り当てます。 詳細な手順については、「Azure portal を使用して Azure ロールを割り当てる」を参照してください。

    設定
    Role ストレージ BLOB のデータ所有者、共同作成者、閲覧者
    アクセスの割り当て先 User
    メンバー Azure AD ユーザー

    Add role assignment page in Azure portal.

    重要

    ストレージBLOB データ所有者、共同作成者、または閲覧者の Azure ロールを指定します。 これらのロールは、所有者、共同作成者、閲覧者の Azure 組み込みロールとは異なります。

    Granting Azure RBAC permission to load

  4. Azure AD 認証を構成します。 「Azure SQL での Azure AD 認証を構成して管理する」を参照してください。

  5. Active Directory を使用し、SQL プールに接続します。そこでは資格情報を指定せずに COPY ステートメントを実行できます。

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

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

手順

  1. Azure Active Directory アプリケーションを作成します

  2. アプリケーション ID を取得します

  3. 認証キーを取得します

  4. V1 OAuth 2.0 トークン エンドポイントを取得します

  5. ストレージ アカウントで Azure AD アプリケーションに読み取り、書き込み、実行のアクセス許可を割り当てます

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

重要

OAuth 2.0 トークン エンドポイント V1 バージョンを使用します

次のステップ