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

[アーティクル]
10/25/2023

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

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

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

	CSV	Parquet	ORC
Azure Blob Storage	SAS/MSI/サービスプリンシパル/キー/AAD	SAS/キー	SAS/キー
Azure Data Lake Gen2	SAS/MSI/サービスプリンシパル/キー/AAD	SAS (BLOB¹)/MSI (dfs²)/サービスプリンシパル/キー/AAD	SAS (BLOB¹)/MSI (dfs²)/サービスプリンシパル/キー/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 認証が必要です。

前提条件

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

手順

スタンドアロンの専用 SQL プールがある場合は、PowerShell を使用して SQL サーバーを Microsoft Entra ID に登録します。
```
Connect-AzAccount
Select-AzSubscription -SubscriptionId <subscriptionId>
Set-AzSqlServer -ResourceGroupName your-database-server-resourceGroup -ServerName your-SQL-servername -AssignIdentity
```
Synapse ワークスペース内の専用 SQL プールでは、この手順は必要ありません。ワークスペースのシステム割り当てマネージド ID (SA-MI) は、Synapse 管理者ロールのメンバーであるため、ワークスペースの専用 SQL プールに対する昇格された特権を持っています。
汎用 v2 ストレージアカウントを作成します。詳しくは、「ストレージアカウントの作成」をご覧ください。
注意
- 汎用 v1 または BLOB ストレージアカウントを使用している場合は、最初に v2 にアップグレードする必要があります。詳細については、「汎用 v2 ストレージアカウントにアップグレードする」を参照してください。
- Azure Data Lake Storage Gen2 の既知の問題については、「Azure Data Lake Storage Gen2 に関する既知の問題」を参照してください。
自分のストレージアカウントで [アクセス制御 (IAM)] を選択します。
[追加]>[ロールの割り当ての追加] を選択して、[ロールの割り当ての追加] ページを開きます。

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

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

Add role assignment page in Azure portal.

Note

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

重要

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

Granting Azure RBAC permission to load

これで "マネージド 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. Microsoft Entra 認証

手順

自分のストレージアカウントで [アクセス制御 (IAM)] を選択します。
[追加]>[ロールの割り当ての追加] を選択して、[ロールの割り当ての追加] ページを開きます。
次のロールを割り当てます。詳細な手順については、「Azure portal を使用して Azure ロールを割り当てる」を参照してください。

設定値

Role ストレージ BLOB のデータ所有者、共同作成者、閲覧者

アクセスの割り当て先 User

メンバー Microsoft Entra ユーザー

重要

ストレージBLOB データ所有者、共同作成者、または閲覧者の Azure ロールを指定します。これらのロールは、所有者、共同作成者、閲覧者の Azure 組み込みロールとは異なります。
Microsoft Entra 認証を構成します。「Azure SQL での Microsoft Entra 認証を構成して管理する」を参照してください。
Active Directory を使用し、SQL プールに接続します。そこでは資格情報を指定せずに COPY ステートメントを実行できます。
```
COPY INTO dbo.target_table
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/*.txt'
WITH (
    FILE_TYPE = 'CSV'
)
```

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

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

手順

Microsoft Entra アプリケーションを作成します。
アプリケーション ID を取得します。
認証キーを取得します。
V1 OAuth 2.0 トークンエンドポイントを取得します。
ストレージアカウントで Microsoft Entra アプリケーションに読み取り、書き込み、実行のアクセス許可を割り当てます。

これで 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 バージョンを使用します

次のステップ

詳しい構文については、COPY ステートメントに関する記事をご覧ください
読み込みのベストプラクティスについては、データ読み込みの概要記事をご覧ください