Synapse SQL を使って Cosmos DB のクエリを実行する

5 分

Spark プールを使うだけでなく、Azure Synapse Analytics に組み込まれた "サーバーレス" SQL プールを使って Azure Cosmos DB 分析コンテナーのクエリを実行することもできます。これを行うには、OPENROWSET SQL 関数を使って、Azure Cosmos DB データベースのリンクサービスに接続します。

認証キーを使った OPENROWSET の使用

既定では、Azure Cosmos DB アカウントへのアクセスは認証キーで認証されます。次の例に示すように、このキーを OPENROWSET ステートメントの接続文字列の一部として使うと、SQL プールからリンクサービスを介して接続できます。

SELECT *
FROM OPENROWSET(
    'CosmosDB',
    'Account=my-cosmos-db;Database=my-db;Key=abcd1234....==',
    [my-container]) AS products_data

ヒント

Cosmos DB アカウントのプライマリとセカンダリのキーは、Azure portal の [キー] ページで確認できます。

このクエリの結果は、Azure Cosmos DB コンテナー内の項目のメタデータとアプリケーション定義フィールドを含めて、次のようになります。

_rid	_ts	productID	productName	id	_etag
mjMaAL...==	1655414791	123	ウィジェット	7248f072-11c3-42b1-a368-...	54004b09-0000-2300-...
mjMaAL...==	1655414829	124	Wotsit	dc33131c-65c7-421a-a0f7-...	5400ca09-0000-2300-...
mjMaAL...==	1655414835	125	Thingumy	ce22351d-78c7-428a-a1h5-...	5400ca09-0000-2300-...
...	...	...	...	...	...

データは分析ストアから取得され、クエリは運用ストアに影響を与えません。

資格情報での OPENROWSET の使用

OPENROWSET の各呼び出しに認証キーを含めるのではなく、Cosmos DB アカウントの認証情報をカプセル化した "資格情報" を定義し、以降のクエリでその資格情報を使用できます。資格情報を作成するには、次の例に示すように CREATE CREDENTIAL ステートメントを使います。

 CREATE CREDENTIAL my_credential
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = 'abcd1234....==';

資格情報を設定すると、次のように OPENROWSET 関数で使用できます。

SELECT *
FROM OPENROWSET(PROVIDER = 'CosmosDB',
                CONNECTION = 'Account=my-cosmos-db;Database=my-db',
                OBJECT = 'my-container',
                SERVER_CREDENTIAL = 'my_credential'
) AS products_data

この場合も、分析ストアのメタデータとアプリケーション定義のフィールドが結果に含まれます。

_rid	_ts	productID	productName	id	_etag
mjMaAL...==	1655414791	123	ウィジェット	7248f072-11c3-42b1-a368-...	54004b09-0000-2300-...
mjMaAL...==	1655414829	124	Wotsit	dc33131c-65c7-421a-a0f7-...	5400ca09-0000-2300-...
mjMaAL...==	1655414835	125	Thingumy	ce22351d-78c7-428a-a1h5-...	5400ca09-0000-2300-...
...	...	...	...	...	...

スキーマの指定

OPENROWSET 構文には WITH 句があり、これを使って結果の行セットのスキーマを定義することができます。次の例に示すように、これを使って個々のフィールドを指定し、データ型を割り当てることができます。

 SELECT *
 FROM OPENROWSET(PROVIDER = 'CosmosDB',
                 CONNECTION = 'Account=my-cosmos-db;Database=my-db',
                 OBJECT = 'my-container',
                 SERVER_CREDENTIAL = 'my_credential'
 )
 WITH (
    productID INT,
    productName VARCHAR(20)
 ) AS products_data

この場合、分析ストアのフィールドに productID と productName が含まれているとすると、結果の行セットは次のような表になります。

productID	productName
123	ウィジェット
124	Wotsit
125	Thingumy
...	...

もちろん、SELECT 句で個別の列名を指定できるので (例: SELECT productID, productName ...)、このように個別の列を指定する機能は使い道が限られているように見えるかもしれません。一方で、次の例のように、運用ストアに格納されるソース JSON ドキュメントに、複数レベルのフィールドが含まれる場合を考えてみてください。

{
    "productID": 126,
    "productName": "Sprocket",
    "supplier": {
        "supplierName": "Contoso",
        "supplierPhone": "555-123-4567"
    }
    "id": "62588f072-11c3-42b1-a738-...",
    "_rid": "mjMaAL...==",
    ...
}

WITH 句は明示的な JSON パスを含めることをサポートしており、次の例に示すように、入れ子になったフィールドを処理し、フィールド名に別名を割り当てることができます。

 SELECT *
 FROM OPENROWSET(PROVIDER = 'CosmosDB',
                 CONNECTION = 'Account=my-cosmos-db;Database=my-db',
                 OBJECT = 'my-container',
                 SERVER_CREDENTIAL = 'my_credential'
 )
 WITH (
    ProductNo INT '$.productID',
    ProductName VARCHAR(20) '$.productName',
    Supplier VARCHAR(20) '$.supplier.supplierName',
    SupplierPhoneNo VARCHAR(15) '$.supplier.supplierPhone'
 ) AS products_data

このクエリの結果、製品 126 に対して次の行が含まれます。

ProductNo	ProductName	Supplier (仕入先)	SupplierPhoneNo
126	Sprocket	Contoso	555-123-4567

データベースでのビューの作成

同じデータに対して頻繁にクエリを実行する必要がある場合、または OPENROWSET 関数を含まない SELECT ステートメントを利用するレポート作成や視覚化のツールを使う必要がある場合は、"ビュー" を使ってデータを抽象化することができます。ビューを作成するには、次の例に示すように、それを定義する新しいデータベースを作成する必要があります ("マスター" データベース内のユーザー定義ビューはサポートされていません)。

CREATE DATABASE sales_db
   COLLATE Latin1_General_100_BIN2_UTF8;
 GO;

 USE sales_db;
 GO;

 CREATE VIEW products
 AS
 SELECT *
 FROM OPENROWSET(PROVIDER = 'CosmosDB',
                 CONNECTION = 'Account=my-cosmos-db;Database=my-db',
                 OBJECT = 'my-container',
                 SERVER_CREDENTIAL = 'my_credential'
 )
 WITH (
    ProductNo INT '$.productID',
    ProductName VARCHAR(20) '$.productName',
    Supplier VARCHAR(20) '$.supplier.supplierName',
    SupplierPhoneNo VARCHAR(15) '$.supplier.supplierPhone'
 ) AS products_data
 GO

ヒント

Cosmos DB のデータにアクセスするデータベースを作成する場合、Cosmos DB 内の文字列との互換性を確保するために、UTF-8 ベースの照合順序を使うことをお勧めします。

ビューを作成したら、ユーザーとクライアントアプリケーションは、他の SQL ビューまたはテーブルと同様にクエリを実行できます。

SELECT * FROM products;

サーバーレス SQL プールと Azure Cosmos DB に関する考慮事項

サーバーレス SQL プールを使って Azure Cosmos DB 分析ストアのデータに対してクエリを実行する予定がある場合、次のベストプラクティスを検討してください。