Azure Synapse Link でサーバーレス SQL プールを使用して Azure Cosmos DB データのクエリを実行する

サーバーレス SQL プールを使用すると、トランザクション ワークロードのパフォーマンスに影響を与えることなく、Azure Synapse Link で有効になっている Azure Cosmos DB コンテナー内のデータをほぼリアルタイムで分析できます。 T-SQL インターフェイスを使用して分析ストアおよび統合された接続からさまざまなビジネス インテリジェンス (BI) やアドホック クエリ ツールへのデータのクエリを実行するために、使い慣れた T-SQL 構文が用意されています。

Azure Cosmos DB のクエリを実行する場合、SELECT のすべての機能が OPENROWSET 関数によってサポートされます。これには、SQL の関数や演算子の大部分が含まれます。 また、Azure Blob Storage または Azure Data Lake Storage のデータと共に Azure Cosmos DB からデータを読み取るクエリの結果を、create external table as select (CETAS) を使用して格納することもできます。 現在は、CETAS を使用して、サーバーレス SQL プールのクエリの結果を Azure Cosmos DB に格納することはできません。

この記事では、Azure Synapse Link が有効になっている Azure Cosmos DB コンテナーのデータのクエリを実行する、サーバーレス SQL プールを使用するクエリの作成方法について説明します。 その後は、このチュートリアルで、Azure Cosmos DB コンテナーに対するサーバーレス SQL プールのビューを構築し、それらを Power BI モデルに接続することの詳細を学習できます。 このチュートリアルでは、Azure Cosmos DB の適切に定義されたスキーマを持つコンテナーを使用します。 また、Azure Synapse Analytics の SQL サーバーレスを使用して Azure Cosmos DB をクエリする方法に関する学習モジュールもご覧ください。

前提条件

• 分析ストアが準備されていることを、次のようにして確認します。
  • 分析ストアを自分の Azure Cosmos DB コンテナーで有効にします。
  • 分析ストアのクエリに使用する読み取り専用キーを使用して接続文字列を取得します。
  • Azure Cosmos DB コンテナーへのアクセスに使用される読み取り専用キーを取得します
• 次のようなすべてのベストプラクティスが適用されていることを確認します。
  • Azure Cosmos DB 分析ストレージがサーバーレス SQL プールと同じリージョンにあることを確認します。
  • クライアントアプリケーション (Power BI、分析サービス) が、サーバーレス SQL プールと同じリージョンにあることを確認します。
  • 大量のデータ (80 GB を超える) を返す場合は、分析サービスなどのキャッシュ層を使用し、分析サービス モデルで 80 GB より小さいパーティションを読み込むことを検討してください。
  • 文字列型の列を使用してデータをフィルタリングしている場合は、使用している OPENROWSET 関数に明示的な WITH 句があり、可能な型の種類が最も少ないことを確認してください (たとえば、プロパティの値が最大 5 文字であることがわかっている場合は、VARCHAR (1000) を使用しないでください)。

概要

サーバーレス SQL プールを使用すると、OPENROWSET 関数を使用して Azure Cosmos DB 分析ストレージに対してクエリを実行できます。

• OPENROWSET とインライン キー。 この構文を使用すると、資格情報を準備せずに Azure Cosmos DB コレクションに対してクエリを実行できます。
• Azure Cosmos DB アカウント キーを含む資格情報を参照する OPENROWSET。 この構文を使用すると、Azure Cosmos DB コレクションに対してビューを作成できます。

OPENROWSET( 
       'CosmosDB',
       '<SQL connection string for Azure Cosmos DB>',
       <Container name>
    )  [ < with clause > ] AS alias

AccountEndpoint=https://<database account name>.documents.azure.com:443/;AccountKey=<database account master key>;

'account=<database account name>;database=<database name>;region=<region name>;key=<database account master key>'

OPENROWSET( 
       PROVIDER = 'CosmosDB',
       CONNECTION = '<SQL connection string for Azure Cosmos DB without account key>',
       OBJECT = '<Container name>',
       [ CREDENTIAL | SERVER_CREDENTIAL ] = '<credential name>'
    )  [ < with clause > ] AS alias

'account=<database account name>;database=<database name>;region=<region name>'

サンプル データセット

この記事の例は、「European Centre for Disease Prevention and Control (ECDC) COVID-19 Cases」および「COVID-19 Open Research Dataset (CORD-19) (doi:10.5281/zenodo.3715505)」のデータに基づいています。

これらのページでは、ライセンスとデータの構造を確認できます。 ECDC および CORD-19 データセットのサンプル データをダウンロードすることもできます。

サーバーレス SQL プールを使用して Azure Cosmos DB データのクエリを実行する方法を示すこの記事の内容をたどるには、以下のリソースを作成してください。

• Azure Synapse Link が有効にされている Azure Cosmos DB データベース アカウント。
• covid という名前の Azure Cosmos DB データベース。
• 前述のサンプル データセットが読み込まれた、Ecdc および Cord19 という名前の 2 つの Azure Cosmos DB コンテナー。

テスト目的で次の接続文字列を使用できます: Account=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==。 この接続ではパフォーマンスが保証されないことに注意してください。これは、このアカウントが Synapse SQL エンドポイントと比較してリモート リージョンに配置される可能性があるためです。

自動スキーマ推論を使用して Azure Cosmos DB のデータを探索する

Azure Cosmos DB のデータを探索する最も簡単な方法は、自動スキーマ推論機能を使用することです。 OPENROWSET ステートメントで WITH 句を省略すると、Azure Cosmos DB コンテナーの分析ストアのスキーマを自動検出 (推論) するように、サーバーレス SQL プールに指示できます。

SELECT TOP 10 *
FROM OPENROWSET( 
       'CosmosDB',
       'Account=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==',
       Ecdc) as documents

/*  Setup - create server-level or database scoped credential with Azure Cosmos DB account key:
    CREATE CREDENTIAL MyCosmosDbAccountCredential
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 's5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==';
*/
SELECT TOP 10 *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=synapselink-cosmosdb-sqlsample;Database=covid',
      OBJECT = 'Ecdc',
      SERVER_CREDENTIAL = 'MyCosmosDbAccountCredential'
    ) with ( date_rep varchar(20), cases bigint, geo_id varchar(6) ) as rows

この例では、Azure Cosmos DB キー (この例ではダミー) を使用して認証された Azure Cosmos DB アカウント MyCosmosDbAccount の covid データベースに接続するように、サーバーレス SQL プールに指示しています。 その後、West US 2 リージョンのコンテナー Ecdc の分析ストアにアクセスします。 特定のプロパティのプロジェクションはないため、OPENROWSET 関数からは、Azure Cosmos DB の項目のすべてのプロパティが返されます。

Azure Cosmos DB コンテナー内の項目に date_rep、cases、および geo_id プロパティがあると仮定すると、このクエリの結果は次の表のようになります。

同じ Azure Cosmos DB データベース内の他のコンテナーのデータを探索する必要がある場合は、同じ接続文字列を使用し、3 番目のパラメーターとして必要なコンテナーを参照することができます。

SELECT TOP 10 *
FROM OPENROWSET( 
       'CosmosDB',
       'Account=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==',
       Cord19) as cord19

スキーマを明示的に指定する

OPENROWSET の自動スキーマ推論機能では、簡単で使いやすいクエリが提供されますが、ビジネス シナリオでは、Azure Cosmos DB データの関連する読み取り専用プロパティに対するスキーマの明示的指定が必要な場合があります。

OPENROWSET 関数を使用すると、コンテナー内のデータから読み取るプロパティを明示的に指定したり、データ型を指定したりすることができます。

次のような構造の ECDC COVID データセットから、一部のデータを Azure Cosmos DB にインポートしたとします。

{"date_rep":"2020-08-13","cases":254,"countries_and_territories":"Serbia","geo_id":"RS"}
{"date_rep":"2020-08-12","cases":235,"countries_and_territories":"Serbia","geo_id":"RS"}
{"date_rep":"2020-08-11","cases":163,"countries_and_territories":"Serbia","geo_id":"RS"}

Azure Cosmos DB のこのようなフラットな JSON ドキュメントは、Synapse SQL では行と列のセットとして表すことができます。 OPENROWSET 関数を使用すると、読み取り対象のプロパティのサブセットと列の厳密な型を、WITH 句で指定できます。

SELECT TOP 10 *
FROM OPENROWSET(
      'CosmosDB',
      'Account=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==',
       Ecdc
    ) with ( date_rep varchar(20), cases bigint, geo_id varchar(6) ) as rows

/*  Setup - create server-level or database scoped credential with Azure Cosmos DB account key:
    CREATE CREDENTIAL MyCosmosDbAccountCredential
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 's5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==';
*/
SELECT TOP 10 *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=synapselink-cosmosdb-sqlsample;Database=covid',
      OBJECT = 'Ecdc',
      SERVER_CREDENTIAL = 'MyCosmosDbAccountCredential'
    ) with ( date_rep varchar(20), cases bigint, geo_id varchar(6) ) as rows
   

このクエリの結果は次の表のようになります。

Azure Cosmos DB の値に使用する必要がある SQL 型の詳細については、記事の最後にある SQL 型のマッピングの規則を参照してください。

ビューを作成する

master または既定のデータベースにビューを作成することは推奨されず、サポートもされていません。 そのため、ビューのためのユーザー データベースを作成する必要があります。

スキーマを特定したら、Azure Cosmos DB データの上にビューを準備できます。 Azure Cosmos DB アカウント キーを別の資格情報に配置し、OPENROWSET 関数からこの資格情報を参照する必要があります。 ビュー定義でアカウント キーを保持しないでください。

CREATE CREDENTIAL MyCosmosDbAccountCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 's5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==';
GO
CREATE OR ALTER VIEW Ecdc
AS SELECT *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=synapselink-cosmosdb-sqlsample;Database=covid',
      OBJECT = 'Ecdc',
      SERVER_CREDENTIAL = 'MyCosmosDbAccountCredential'
    ) with ( date_rep varchar(20), cases bigint, geo_id varchar(6) ) as rows

パフォーマンスに影響する可能性があるため、明示的に定義されたスキーマなしで OPENROWSET を使用しないでください。 列のサイズは可能な限り小さくしてください (たとえば、既定の VARCHAR(8000) ではなく VARCHAR(100))。 UTF-8 の変換に関する問題を回避するために、何らかの UTF-8 照合順序を既定のデータベース照合順序として使用するか、明示的な列の照合順序として設定する必要があります。 照合順序 Latin1_General_100_BIN2_UTF8 は、文字列型の列を使用してデータをフィルター処理する場合に最も高いパフォーマンスを提供します。

ビューに対してクエリを実行すると、エラーや予期しない結果が発生する可能性があります。 これはおそらく、ビューが、変更されたか、もはや存在しない列またはオブジェクトを参照していることを意味する可能性があります。 基になるスキーマの変更に合わせてビュー定義を手動で調整する必要があります。 これは、ビューで自動スキーマ推論を使用する場合と、スキーマを明示的に指定する場合の両方で発生する場合があることに注意してください。

入れ子になったオブジェクトに対してクエリを実行する

Azure Cosmos DB を使用すると、入れ子になったオブジェクトまたは配列として構成することで、より複雑なデータ モデルを表すことができます。 Azure Cosmos DB に対する Azure Synapse Link の自動同期機能により、何もしなくても分析ストアでのスキーマ表現が管理されます。これには、サーバーレス SQL プールからの高度なクエリを可能にする、入れ子になったデータ型の処理が含まれます。

たとえば、CORD-19 データ セットには、次の構造に従った JSON ドキュメントが含まれています。

{
    "paper_id": <str>,                   # 40-character sha1 of the PDF
    "metadata": {
        "title": <str>,
        "authors": <array of objects>    # list of author dicts, in order
        ...
     }
     ...
}

Azure Cosmos DB 内の入れ子になったオブジェクトと配列は、OPENROWSET 関数で読み取ると、クエリ結果で JSON 文字列として表されます。 WITH 句を使用するときに、オブジェクト内の入れ子になった値へのパスを指定できます。

SELECT TOP 10 *
FROM OPENROWSET( 
       'CosmosDB',
       'Account=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==',
       Cord19)
WITH (  paper_id    varchar(8000),
        title        varchar(1000) '$.metadata.title',
        metadata     varchar(max),
        authors      varchar(max) '$.metadata.authors'
) AS docs;

このクエリの結果は次の表のようになります。

Azure Synapse Link for Azure Cosmos DB 内の Parquet ファイルやコンテナーなどの複合データ型またはサーバーレス SQL プール内の入れ子構造の分析について説明します。

入れ子になった配列のフラット化

Azure Cosmos DB のデータには、CORD-19 データ セットの authors 配列のように、入れ子になったサブ配列が存在する場合があります。

{
    "paper_id": <str>,                      # 40-character sha1 of the PDF
    "metadata": {
        "title": <str>,
        "authors": [                        # list of author dicts, in order
            {
                "first": <str>,
                "middle": <list of str>,
                "last": <str>,
                "suffix": <str>,
                "affiliation": <dict>,
                "email": <str>
            },
            ...
        ],
        ...
}

場合によっては、最上位の項目 (メタデータ) のプロパティを、配列 (authors) のすべての要素と "結合" することが必要になります。 サーバーレス SQL プールを使用すると、入れ子になった配列に対して OPENJSON 関数を適用することで、入れ子構造をフラット化できます。

SELECT
    *
FROM
    OPENROWSET(
      'CosmosDB',
      'Account=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==',
       Cord19
    ) WITH ( title varchar(1000) '$.metadata.title',
             authors varchar(max) '$.metadata.authors' ) AS docs
      CROSS APPLY OPENJSON ( authors )
                  WITH (
                       first varchar(50),
                       last varchar(50),
                       affiliation nvarchar(max) as json
                  ) AS a

このクエリの結果は次の表のようになります。

Azure Cosmos DB から SQL 型へのマッピング

Azure Cosmos DB のトランザクション ストアはスキーマに依存しませんが、分析ストアは分析クエリのパフォーマンスを最適化するためにスキーマ化されています。 Azure Synapse Link の自動同期機能により、Azure Cosmos DB では何もしなくても分析ストアでのスキーマ表現が管理されます。これには、入れ子になったデータ型の処理が含まれます。 サーバーレス SQL プールでは分析ストアのクエリが実行されるため、Azure Cosmos DB の入力データ型を SQL データ型にマップする方法を理解することが重要です。

SQL (Core) API の Azure Cosmos DB アカウントでは、数値、文字列、ブール値、null、入れ子になったオブジェクトまたは配列の JSON プロパティの型がサポートされています。 OPENROWSET で WITH 句を使用する場合は、これらの JSON 型に一致する SQL 型を選択する必要があります。 次の表は、Azure Cosmos DB のさまざまなプロパティの型に対して使用する必要がある SQL 列の型を示しています。

完全に忠実なスキーマ

Azure Cosmos DB の完全に忠実なスキーマを使用すると、コンテナー内のすべてのプロパティについて、値と最も一致する型の両方を記録できます。 完全に忠実なスキーマを持つコンテナーで OPENROWSET 関数を使用すると、各セルに型と実際の値の両方が提供されます。 次のクエリでは、完全に忠実なスキーマでコンテナーから項目を読み取ると仮定します。

SELECT *
FROM OPENROWSET(
      'CosmosDB',
      'account=MyCosmosDbAccount;database=covid;region=westus2;key=C0Sm0sDbKey==',
       Ecdc
    ) as rows

このクエリの結果は、JSON テキストとしてフォーマットされた型と値を返します。

すべての値について、Azure Cosmos DB コンテナーの項目で識別された型を確認できます。 date_rep プロパティの値のほとんどに date 値が含まれていますが、一部の値が Azure Cosmos DB に誤って文字列として格納されています。 完全に忠実なスキーマによって、正しく型指定された date 値と正しくフォーマットされていない string 値の両方が返されます。 cases の数値は int32 値として格納される情報ですが、小数として入力されている値が 1 つあります。 この値の型は float64 です。 int32 の最大値を超える値がある場合は int64 型として格納されます。 この例のすべての geo_id 値は string 型として格納されます。

Azure Cosmos DB for MongoDB アカウントに対してクエリを実行する場合は、分析ストア内の完全に忠実なスキーマ表現と、使用される拡張プロパティ名の詳細を、「Azure Cosmos DB の分析ストアとは」で確認してください。

完全に忠実なスキーマの項目に対してクエリを実行する

完全に忠実なスキーマにクエリを実行する際は、WITH 句で SQL 型と、想定される Azure Cosmos DB のプロパティ型を明示的に指定する必要があります。

次の例では、string が geo_id プロパティの正しい型であり、int32 が cases プロパティの正しい型であると仮定します。

SELECT geo_id, cases = SUM(cases)
FROM OPENROWSET(
      'CosmosDB'
      'account=MyCosmosDbAccount;database=covid;region=westus2;key=C0Sm0sDbKey==',
       Ecdc
    ) WITH ( geo_id VARCHAR(50) '$.geo_id.string',
             cases INT '$.cases.int32'
    ) as rows
GROUP BY geo_id

他の型を持つ geo_id および cases の値は NULL 値として返されます。 このクエリは、式 (cases.int32) で指定された型の cases のみを参照します。

Azure Cosmos DB コンテナーでクリーンアップできない他の型 (cases.int64、cases.float64) を持つ値がある場合は、WITH 句で明示的に参照し、その結果を結合する必要があります。 次のクエリでは、cases 列に格納されている int32、int64、および float64 の両方を集約します。

SELECT geo_id, cases = SUM(cases_int) + SUM(cases_bigint) + SUM(cases_float)
FROM OPENROWSET(
      'CosmosDB',
      'account=MyCosmosDbAccount;database=covid;region=westus2;key=C0Sm0sDbKey==',
       Ecdc
    ) WITH ( geo_id VARCHAR(50) '$.geo_id.string', 
             cases_int INT '$.cases.int32',
             cases_bigint BIGINT '$.cases.int64',
             cases_float FLOAT '$.cases.float64'
    ) as rows
GROUP BY geo_id

この例では、cases の数値は int32、 int64、 float64 のいずれかの値として格納されます。 国およびリージョンごとの cases の数値を計算するには、すべての値を抽出する必要があります。

トラブルシューティング

セルフヘルプページを参照して、Azure Cosmos DB クエリで発生する可能性のある問題の解決に役立つ既知の問題やトラブルシューティングの手順を確認してください。

次の手順

詳細については、次の記事を参照してください。

• Azure Synapse Link で Power BI とサーバーレス SQL プールを使用する
• サーバーレス SQL プールでビューを作成および使用する
• Azure Cosmos DB に対するサーバーレス SQL プール ビューの作成と、DirectQuery による Power BI モデルへのそれらの接続に関するチュートリアル
• エラーが発生したり、パフォーマンスに問題がある場合は、Azure Cosmos DB のセルフヘルプ ページの Azure Synapse リンクにアクセスしてください。
• SQL Serverless for Azure Synapse Analytics を使用して Azure Cosmos DB のクエリを実行する方法に関する学習モジュールを確認してください。