Azure Synapse Analytics のサーバーレス SQL プールを使用して外部ストレージにアクセスする

  • [アーティクル]

この記事では、ユーザーがサーバーレス SQL プールで、Azure Storage に格納されているファイルからデータを読み取る方法について説明します。 ユーザーは、ストレージにアクセスするための次のオプションを使用できます。

  • OPENROWSET 関数。Azure Storage 内のファイルに対するアドホック クエリを有効にします。
  • 外部テーブル。外部ファイル セット上に構築された定義済みのデータ構造です。

ユーザーは、Microsoft Entra パススルー認証 (Microsoft Entra プリンシパルの既定値) や SAS 認証 (SQL プリンシパルの既定値) などのさまざまな認証方法を使用できます。

OPENROWSET を使用してファイルに対してクエリを実行する

OPENROWSET を使用すると、ユーザーはストレージにアクセスできる場合に、Azure Storage 上の外部ファイルに対してクエリを実行できます。 サーバーレス SQL プールに接続されているユーザーは、次のクエリを使用して、Azure Storage 上のファイルの内容を読み取る必要があります。

SELECT * FROM
 OPENROWSET(BULK 'https://<storage_account>.dfs.core.windows.net/<container>/<path>/*.parquet', format= 'parquet') as rows

ユーザーは、次のアクセス ルールを使用してストレージにアクセスできます。

  • Microsoft Entra ユーザー - OPENROWSET は、呼び出し元の Microsoft Entra ID を使用して、Azure Storage にアクセスするか、匿名アクセスでストレージにアクセスします。
  • SQL ユーザー - OPENROWSET は、匿名アクセスでストレージにアクセスするか、ワークスペースのマネージド ID または SAS トークンを使用して偽装することができます。

SQL プリンシパルでは、OPENROWSET を使用して、ワークスペースのマネージド ID または SAS トークンで保護されたファイルに対して、直接クエリを実行することもできます。 SQL ユーザーがこの関数を実行する場合、ALTER ANY CREDENTIAL 権限を持つパワー ユーザーは、(ストレージ名とコンテナーを使用して) 関数内の URL に一致するサーバースコープの資格情報を作成し、この資格情報に対する REFERENCES 権限を OPENROWSET 関数の呼び出し元に付与する必要があります。

EXECUTE AS somepoweruser

CREATE CREDENTIAL [https://<storage_account>.dfs.core.windows.net/<container>]
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sas token';

GRANT REFERENCES ON CREDENTIAL::[https://<storage_account>.dfs.core.windows.net/<container>] TO sqluser

URL に一致するサーバーレベルの資格情報がない場合、または SQL ユーザーがこの資格情報に対する参照権限を持っていない場合は、エラーが返されます。 SQL プリンシパルでは、何かの Microsoft Entra ID を使用して偽装することはできません。

注意

このバージョンの OPENROWSET は、既定の認証を使用して迅速かつ簡単にデータを探索できるように設計されています。 偽装またはマネージド ID を活用するには、次のセクションで説明されているように OPENROWSET を DATA_SOURCE と共に使用します。

OPENROWSET を使用してデータ ソースのクエリを実行する

OPENROWSET を使用すると、ユーザーは外部データ ソースに配置されたファイルに対してクエリを実行できます。

SELECT * FROM
 OPENROWSET(BULK 'file/path/*.parquet',
 DATA_SOURCE = MyAzureInvoices,
 FORMAT= 'parquet') as rows

このクエリを実行するユーザーは、ファイルにアクセスできる必要があります。 ユーザーがその Microsoft Entra ID または匿名アクセスを使用して直接ファイルにアクセスすることができない場合、ワークスペースのマネージド ID または SAS トークンを使用してユーザーを偽装する必要があります。

DATABASE SCOPED CREDENTIAL では、参照されるデータ ソース上のファイルへのアクセス方法 (現在は SAS およびマネージド ID) を指定します。 CONTROL DATABASE 権限を持つパワー ユーザーは、ストレージにアクセスするために使用される DATABASE SCOPED CREDENTIAL、および使用する必要がある資格情報とデータ ソースの URL を指定する EXTERNAL DATA SOURCE を作成する必要があります。

EXECUTE AS somepoweruser;

-- Create MASTER KEY if it doesn't exists in database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'some very strong password';

CREATE DATABASE SCOPED CREDENTIAL AccessAzureInvoices
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = '******srt=sco&amp;sp=rwac&amp;se=2017-02-01T00:55:34Z&amp;st=201********' ;

CREATE EXTERNAL DATA SOURCE MyAzureInvoices
 WITH ( LOCATION = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>/' ,
 CREDENTIAL = AccessAzureInvoices) ;

呼び出し元には、OPENROWSET 関数を実行するための次のいずれかの権限が必要です。

  • OPENROWSET を実行するための次のいずれかの権限:
    • ADMINISTER BULK OPERATIONS を使用すると、ログインで OPENROWSET 関数を実行できます。
    • ADMINISTER DATABASE BULK OPERATIONS を使用すると、データベース スコープ ユーザーは OPENROWSET 関数を実行できます。
  • EXTERNAL DATA SOURCE で参照される資格情報に対する REFERENCES DATABASE SCOPED CREDENTIAL

EXTERNAL TABLE

テーブルを読み取る権限を持つユーザーは、Azure Storage のフォルダーとファイルのセット上に作成された EXTERNAL TABLE を使用して外部ファイルにアクセスできます。

外部テーブルを作成するための権限 (CREATE TABLE、ALTER ANY CREDENTIAL、REFERENCES DATABASE SCOPED CREDENTIAL など) を持つユーザーは、次のスクリプトを使用して、Azure Storage データソース上にテーブルを作成できます。

CREATE EXTERNAL TABLE [dbo].[DimProductexternal]
( ProductKey int, ProductLabel nvarchar, ProductName nvarchar )
WITH
(
LOCATION='/DimProduct/year=*/month=*' ,
DATA_SOURCE = AzureDataLakeStore ,
FILE_FORMAT = TextFileFormat
) ;

このテーブルからデータを読み取るユーザーは、ファイルにアクセスできる必要があります。 ユーザーがその Microsoft Entra ID または匿名アクセスを使用して直接ファイルにアクセスすることができない場合、ワークスペースのマネージド ID または SAS トークンを使用してユーザーを偽装する必要があります。

DATABASE SCOPED CREDENTIAL では、参照されるデータ ソース上のファイルへのアクセス方法を指定します。 CONTROL DATABASE 権限を持つユーザーは、ストレージにアクセスするために使用される DATABASE SCOPED CREDENTIAL、および使用する必要がある資格情報とデータ ソースの URL を指定する EXTERNAL DATA SOURCE を作成する必要があります。

EXECUTE AS somepoweruser;

-- Create MASTER KEY if it doesn't exists in database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'some very strong password';

CREATE DATABASE SCOPED CREDENTIAL cred
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=201********' ;

CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
 WITH ( LOCATION = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>' ,
 CREDENTIAL = cred
 ) ;

EXTERNAL TABLE を使用して外部ファイルを読み取る

EXTERNAL TABLE では、標準的な SQL SELECT ステートメントを使用して、データ ソースを介して参照されるファイルからデータを読み取ることができます。

SELECT *
FROM dbo.DimProductsExternal

呼び出し元には、データを読み取るための次の権限が必要です。

  • 外部テーブルに対する SELECT 権限
  • DATA SOURCE に CREDENTIAL がある場合は、REFERENCES DATABASE SCOPED CREDENTIAL 権限

アクセス許可

次の表には、上に一覧表示されている操作に必要な権限が一覧表示されています。

クエリ 必要なアクセス許可
OPENROWSET(BULK) (データソースを指定しない) ADMINISTER BULK OPERATIONS、ADMINISTER DATABASE BULK OPERATIONS、または SQL ログインには、SAS で保護されたストレージに対する REFERENCES CREDENTIAL::<URL> が必要です
OPENROWSET (BULK) (資格情報なしのデータソースを指定) ADMINISTER BULK OPERATIONS または ADMINISTER DATABASE BULK OPERATIONS
OPENROWSET (BULK) (資格情報ありのデータソースを指定) REFERENCES DATABASE SCOPED CREDENTIAL と、ADMINISTER BULK OPERATIONS または ADMINISTER DATABASE BULK OPERATIONS のいずれか
CREATE EXTERNAL DATA SOURCE ALTER ANY EXTERNAL DATA SOURCE および REFERENCES DATABASE SCOPED CREDENTIAL
CREATE EXTERNAL TABLE CREATE TABLE、ALTER ANY SCHEMA、ALTER ANY EXTERNAL FILE FORMAT、および ALTER ANY EXTERNAL DATA SOURCE
SELECT FROM EXTERNAL TABLE SELECT TABLE および REFERENCES DATABASE SCOPED CREDENTIAL
CETAS テーブルを作成する場合 - CREATE TABLE、ALTER ANY SCHEMA、ALTER ANY DATA SOURCE、および ALTER ANY EXTERNAL FILE FORMAT。 データを読み取る場合: クエリのテーブル、ビューまたは関数ごとの ADMINISTER BULK OPERATIONS、REFERENCES CREDENTIAL または SELECT TABLE + ストレージに対する読み取りと書き込み権限

次のステップ

これで、操作方法に関する次の記事に進む準備ができました。