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

サーバーレス SQL プールを使用すると、トランザクション ワークロードのパフォーマンスに影響を与えることなく、Azure Synapse Link で有効になっている Azure Cosmos DB コンテナー内のデータをほぼリアルタイムで分析できます。A serverless SQL pool allows you to analyze data in your Azure Cosmos DB containers that are enabled with Azure Synapse Link in near real time without affecting the performance of your transactional workloads. T-SQL インターフェイスを使用して分析ストアおよび統合された接続からさまざまなビジネス インテリジェンス (BI) やアドホック クエリ ツールへのデータのクエリを実行するために、使い慣れた T-SQL 構文が用意されています。It offers a familiar T-SQL syntax to query data from the analytical store and integrated connectivity to a wide range of business intelligence (BI) and ad-hoc querying tools via the T-SQL interface.

Azure Cosmos DB のクエリを実行する場合、SELECT のすべての機能が OPENROWSET 関数によってサポートされます。これには、SQL の関数や演算子の大部分が含まれます。For querying Azure Cosmos DB, the full SELECT surface area is supported through the OPENROWSET function, which includes the majority of SQL functions and operators. また、Azure Blob Storage または Azure Data Lake Storage のデータと共に Azure Cosmos DB からデータを読み取るクエリの結果を、create external table as select (CETAS) を使用して格納することもできます。You can also store results of the query that reads data from Azure Cosmos DB along with data in Azure Blob Storage or Azure Data Lake Storage by using create external table as select (CETAS). 現在は、CETAS を使用して、サーバーレス SQL プールのクエリの結果を Azure Cosmos DB に格納することはできません。You can't currently store serverless SQL pool query results to Azure Cosmos DB by using CETAS.

この記事では、Azure Synapse Link が有効になっている Azure Cosmos DB コンテナーのデータのクエリを実行する、サーバーレス SQL プールを使用するクエリの作成方法について説明します。In this article, you'll learn how to write a query with a serverless SQL pool that will query data from Azure Cosmos DB containers that are enabled with Azure Synapse Link. その後は、このチュートリアルで、Azure Cosmos DB コンテナー上にサーバーレス SQL プール ビューを構築し、それらを Power BI モデルに接続する方法について詳しく学ぶことができます。このチュートリアルには、Azure Cosmos DB の定義済みスキーマを使ったコンテナーが使用されています。You can then learn more about building serverless SQL pool views over Azure Cosmos DB containers and connecting them to Power BI models in this tutorial.This tutorial uses a container with an Azure Cosmos DB well-defined schema.

概要Overview

サーバーレス SQL プールを使用すると、OPENROWSET 関数を使用して Azure Cosmos DB 分析ストレージに対してクエリを実行できます。Serverless SQL pool enables you to query Azure Cosmos DB analytical storage using OPENROWSET function.

  • OPENROWSET とインライン キー。OPENROWSET with inline key. この構文を使用すると、資格情報を準備せずに Azure Cosmos DB コレクションに対してクエリを実行できます。This syntax can be used to query Azure Cosmos DB collections without need to prepare credentials.
  • Cosmos DB アカウント キーを含む資格情報を参照する OPENROWSETOPENROWSET that referenced credential that contains the Cosmos DB account key. この構文を使用すると、Azure Cosmos DB コレクションに対してビューを作成できます。This syntax can be used to create views on Azure Cosmos DB collections.

Azure Cosmos DB 分析ストア内のデータのクエリと分析をサポートするため、サーバーレス SQL プールでは次の OPENROWSET 構文を使用します。To support querying and analyzing data in an Azure Cosmos DB analytical store, a serverless SQL pool uses the following OPENROWSET syntax:

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

Azure Cosmos DB の接続文字列には、Azure Cosmos DB のアカウント名、データベース名、データベース アカウント マスター キー、および OPENROWSET 関数に対するオプションのリージョン名を指定します。The Azure Cosmos DB connection string specifies the Azure Cosmos DB account name, database name, database account master key, and an optional region name to the OPENROWSET function.

接続文字列は次のような形式です。The connection string has the following format:

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

OPENROWSET の構文では、引用符を使用しないで Azure Cosmos DB のコンテナー名を指定します。The Azure Cosmos DB container name is specified without quotation marks in the OPENROWSET syntax. コンテナー名に特殊文字 (ダッシュ "-" など) が含まれている場合、OPENROWSET 構文では名前を角かっこ ([]) で囲む必要があります。If the container name has any special characters, for example, a dash (-), the name should be wrapped within square brackets ([]) in the OPENROWSET syntax.

重要

Azure Cosmos DB 分析ストア内の文字列値は UTF-8 テキストとしてエンコードされているため、必ず何らかの UTF-8 データベース照合順序 (Latin1_General_100_CI_AS_SC_UTF8 など) を使用してください。Make sure that you're using some UTF-8 database collation, for example, Latin1_General_100_CI_AS_SC_UTF8, because string values in an Azure Cosmos DB analytical store are encoded as UTF-8 text. ファイル内のテキスト エンコードと照合順序が一致しないと、予期しないテキスト変換エラーが発生する可能性があります。A mismatch between text encoding in the file and collation might cause unexpected text conversion errors. 現在のデータベースの既定の照合順序は、alter database current collate Latin1_General_100_CI_AI_SC_UTF8 という T-SQL ステートメントを使用して簡単に変更できます。You can easily change default collation of the current database by using the T-SQL statement alter database current collate Latin1_General_100_CI_AI_SC_UTF8.

注意

サーバーレス SQL プールでは、Azure Cosmos DB トランザクション ストアのクエリはサポートされていません。A serverless SQL pool doesn't support querying an Azure Cosmos DB transactional store.

サンプル データセットSample dataset

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

これらのページでは、ライセンスとデータの構造を確認できます。You can see the license and the structure of data on these pages. ECDC および CORD-19 データセットのサンプル データをダウンロードすることもできます。You can also download sample data for the ECDC and CORD-19 datasets.

サーバーレス SQL プールを使用して Azure Cosmos DB データのクエリを実行する方法を示すこの記事の内容をたどるには、以下のリソースを作成してください。To follow along with this article showcasing how to query Azure Cosmos DB data with a serverless SQL pool, make sure that you create the following resources:

  • Azure Synapse Link が有効にされている Azure Cosmos DB データベース アカウント。An Azure Cosmos DB database account that's Azure Synapse Link enabled.
  • covid という名前の Azure Cosmos DB データベース。An Azure Cosmos DB database named covid.
  • 前述のサンプル データセットが読み込まれた、Ecdc および Cord19 という名前の 2 つの Azure Cosmos DB コンテナー。Two Azure Cosmos DB containers named Ecdc and Cord19 loaded with the preceding sample datasets.

テスト目的で次の接続文字列を使用できます: Account=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==You can use the following connection string for testing purpose: Account=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==. この接続ではパフォーマンスが保証されないことに注意してください。これは、このアカウントが Synapse SQL エンドポイントと比較してリモート リージョンに配置される可能性があるためです。Note that this connection will not guarantee performance because this account might be located in remote region compared to your Synapse SQL endpoint.

自動スキーマ推論を使用して Azure Cosmos DB のデータを探索するExplore Azure Cosmos DB data with automatic schema inference

Azure Cosmos DB のデータを探索する最も簡単な方法は、自動スキーマ推論機能を使用することです。The easiest way to explore data in Azure Cosmos DB is by using the automatic schema inference capability. OPENROWSET ステートメントで WITH 句を省略すると、Azure Cosmos DB コンテナーの分析ストアのスキーマを自動検出 (推論) するように、サーバーレス SQL プールに指示できます。By omitting the WITH clause from the OPENROWSET statement, you can instruct the serverless SQL pool to autodetect (infer) the schema of the analytical store of the Azure Cosmos DB container.

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

この例では、Azure Cosmos DB キー (この例ではダミー) を使用して認証された Azure Cosmos DB アカウント MyCosmosDbAccountcovid データベースに接続するように、サーバーレス SQL プールに指示しています。In the preceding example, we instructed the serverless SQL pool to connect to the covid database in the Azure Cosmos DB account MyCosmosDbAccount authenticated by using the Azure Cosmos DB key (the dummy in the preceding example). その後、West US 2 リージョンのコンテナー Ecdc の分析ストアにアクセスします。We then accessed the Ecdc container's analytical store in the West US 2 region. 特定のプロパティのプロジェクションはないため、OPENROWSET 関数からは、Azure Cosmos DB の項目のすべてのプロパティが返されます。Since there's no projection of specific properties, the OPENROWSET function will return all properties from the Azure Cosmos DB items.

Azure Cosmos DB コンテナー内の項目に date_repcases、および geo_id プロパティがあると仮定すると、このクエリの結果は次の表のようになります。Assuming that the items in the Azure Cosmos DB container have date_rep, cases, and geo_id properties, the results of this query are shown in the following table:

date_repdate_rep casescases geo_idgeo_id
2020-08-132020-08-13 254254 RSRS
2020-08-122020-08-12 235235 RSRS
2020-08-112020-08-11 163163 RSRS

同じ Azure Cosmos DB データベース内の他のコンテナーのデータを探索する必要がある場合は、同じ接続文字列を使用し、3 番目のパラメーターとして必要なコンテナーを参照することができます。If you need to explore data from the other container in the same Azure Cosmos DB database, you can use the same connection string and reference the required container as the third parameter:

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

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

OPENROWSET の自動スキーマ推論機能では、簡単で使いやすいクエリが提供されますが、ビジネス シナリオでは、Azure Cosmos DB データの関連する読み取り専用プロパティに対するスキーマの明示的指定が必要な場合があります。While automatic schema inference capability in OPENROWSET provides a simple, easy-to-use querience, your business scenarios might require you to explicitly specify the schema to read-only relevant properties from the Azure Cosmos DB data.

OPENROWSET 関数を使用すると、コンテナー内のデータから読み取るプロパティを明示的に指定したり、データ型を指定したりすることができます。The OPENROWSET function enables you to explicitly specify what properties you want to read from the data in the container and to specify their data types.

次のような構造の ECDC COVID データセットから、一部のデータを Azure Cosmos DB にインポートしたとします。Let's imagine that we've imported some data from the ECDC COVID dataset with the following structure into 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 では行と列のセットとして表すことができます。These flat JSON documents in Azure Cosmos DB can be represented as a set of rows and columns in Synapse SQL. OPENROWSET 関数を使用すると、読み取り対象のプロパティのサブセットと列の厳密な型を、WITH 句で指定できます。The OPENROWSET function enables you to specify a subset of properties that you want to read and the exact column types in the WITH clause:

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

このクエリの結果は次の表のようになります。The result of this query might look like the following table:

date_repdate_rep casescases geo_idgeo_id
2020-08-132020-08-13 254254 RSRS
2020-08-122020-08-12 235235 RSRS
2020-08-112020-08-11 163163 RSRS

Azure Cosmos DB の値に使用する必要がある SQL 型の詳細については、記事の最後にある SQL 型のマッピングの規則を参照してください。For more information about the SQL types that should be used for Azure Cosmos DB values, see the rules for SQL type mappings at the end of the article.

ビューを作成するCreate view

マスターまたは既定のデータベースにビューを作成することは推奨されず、サポートもされていません。Creating views in the master or default databases is not recommended or supported. そのため、ビューのためのユーザー データベースを作成する必要があります。So you need to create an user database for your views.

スキーマを特定したら、Azure Cosmos DB データの上にビューを準備できます。Once you identify the schema, you can prepare a view on top of your Azure Cosmos DB data. Azure Cosmos DB アカウント キーを別の資格情報に配置し、OPENROWSET 関数からこの資格情報を参照する必要があります。You should place your Azure Cosmos DB account key in a separate credential and reference this credential from OPENROWSET function. ビュー定義でアカウント キーを保持しないでください。Do not keep your account key in the view definition.

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 を使用しないでください。Do not use OPENROWSET without explicitly defined schema because it might impact your performance. 列のサイズは可能な限り小さくしてください (たとえば、既定の VARCHAR(8000) ではなく VARCHAR(100))。Make sure that you use the smallest possible sizes for your columns (for example VARCHAR(100) instead of default VARCHAR(8000)). UTF-8 の変換に関する問題を回避するために、何らかの UTF-8 照合順序を既定のデータベース照合順序として使用するか、明示的な列の照合順序として設定する必要があります。You should use some UTF-8 collation as default database collation or set it as explicit column collation to avoid UTF-8 conversion issue. 照合順序 Latin1_General_100_BIN2_UTF8 は、文字列型の列を使用してデータをフィルター処理する場合に最も高いパフォーマンスを提供します。Collation Latin1_General_100_BIN2_UTF8 provides best performance when yu filter data using some string columns.

入れ子になったオブジェクトと配列のクエリQuery nested objects and arrays

Azure Cosmos DB を使用すると、入れ子になったオブジェクトまたは配列として構成することで、より複雑なデータ モデルを表すことができます。With Azure Cosmos DB, you can represent more complex data models by composing them as nested objects or arrays. Azure Cosmos DB に対する Azure Synapse Link の自動同期機能により、何もしなくても分析ストアでのスキーマ表現が管理されます。これには、サーバーレス SQL プールからの高度なクエリを可能にする、入れ子になったデータ型の処理が含まれます。The autosync capability of Azure Synapse Link for Azure Cosmos DB manages the schema representation in the analytical store out of the box, which includes handling nested data types that allow for rich querying from the serverless SQL pool.

たとえば、CORD-19 データ セットには、次の構造に従った JSON ドキュメントが含まれています。For example, the CORD-19 dataset has JSON documents that follow this structure:

{
    "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 文字列として表されます。The nested objects and arrays in Azure Cosmos DB are represented as JSON strings in the query result when the OPENROWSET function reads them. WITH 句を使用するときに、オブジェクト内の入れ子になった値へのパスを指定できます。You can specify the paths to nested values in the objects when you use the WITH clause:

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;

このクエリの結果は次の表のようになります。The result of this query might look like the following table:

paper_idpaper_id titletitle metadatametadata 作成者authors
bb11206963e831f…bb11206963e831f… Supplementary Information An eco-epidemi…Supplementary Information An eco-epidemi… {"title":"Supplementary Informati… [{"first":"Julien","last":"Mélade","suffix":"","af…
bb1206963e831f1…bb1206963e831f1… The Use of Convalescent Sera in Immune-E…The Use of Convalescent Sera in Immune-E… {"title":"The Use of Convalescent… [{"first":"Antonio","last":"Lavazza","suffix":"", …
bb378eca9aac649…bb378eca9aac649… Tylosema esculentum (Marama) Tuber and B…Tylosema esculentum (Marama) Tuber and B… {"title":"Tylosema esculentum (Ma… [{"first":"Walter","last":"Chingwaru","suffix":"",…

詳細については、Azure Synapse Link での複雑なデータ型の分析に関するページと、サーバーレス SQL プールでの入れ子構造の分析に関するページを参照してください。Learn more about analyzing complex data types in Azure Synapse Link and nested structures in a serverless SQL pool.

重要

Mélade ではなく Mélade のような予期しない文字がテキストで表示される場合は、データベースの照合順序が UTF-8 の照合順序に設定されていません。If you see unexpected characters in your text like Mélade instead of Mélade, then your database collation isn't set to UTF-8 collation. ALTER DATABASE MyLdw COLLATE LATIN1_GENERAL_100_CI_AS_SC_UTF8 のような SQL ステートメントを使用して、UTF-8 照合順序にデータベースの照合順序を変更しますChange collation of the database to UTF-8 collation by using a SQL statement like ALTER DATABASE MyLdw COLLATE LATIN1_GENERAL_100_CI_AS_SC_UTF8.

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

Azure Cosmos DB のデータには、CORD-19 データ セットの authors 配列のように、入れ子になったサブ配列が存在する場合があります。Azure Cosmos DB data might have nested subarrays like the author's array from a CORD-19 dataset:

{
    "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) のすべての要素と "結合" することが必要になります。In some cases, you might need to "join" the properties from the top item (metadata) with all elements of the array (authors). サーバーレス SQL プールを使用すると、入れ子になった配列に対して OPENJSON 関数を適用することで、入れ子構造をフラット化できます。A serverless SQL pool enables you to flatten nested structures by applying the OPENJSON function on the nested array:

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

このクエリの結果は次の表のようになります。The result of this query might look like the following table:

titletitle 作成者authors firstfirst lastlast affiliationaffiliation
Supplementary Information An eco-epidemi…Supplementary Information An eco-epidemi… [{"first":"Julien","last":"Mélade","suffix":"","affiliation":{"laboratory":"Centre de Recher… JulienJulien MéladeMélade {"laboratory":"Centre de Recher…
Supplementary Information An eco-epidemi…Supplementary Information An eco-epidemi… [{"first":"Nicolas","last":"4#","suffix":"","affiliation":{"laboratory":"","institution":"U… NicolasNicolas 4#4# {"laboratory":"","institution":"U…
Supplementary Information An eco-epidemi…Supplementary Information An eco-epidemi… [{"first":"Beza","last":"Ramazindrazana","suffix":"","affiliation":{"laboratory":"Centre de Recher… BezaBeza RamazindrazanaRamazindrazana {"laboratory":"Centre de Recher…
Supplementary Information An eco-epidemi…Supplementary Information An eco-epidemi… [{"first":"Olivier","last":"Flores","suffix":"","affiliation":{"laboratory":"UMR C53 CIRAD, … OlivierOlivier FloresFlores {"laboratory":"UMR C53 CIRAD, …

重要

Mélade ではなく Mélade のような予期しない文字がテキストで表示される場合は、データベースの照合順序が UTF-8 の照合順序に設定されていません。If you see unexpected characters in your text like Mélade instead of Mélade, then your database collation isn't set to UTF-8 collation. ALTER DATABASE MyLdw COLLATE LATIN1_GENERAL_100_CI_AS_SC_UTF8 のような SQL ステートメントを使用して、UTF-8 照合順序にデータベースの照合順序を変更しますChange collation of the database to UTF-8 collation by using a SQL statement like ALTER DATABASE MyLdw COLLATE LATIN1_GENERAL_100_CI_AS_SC_UTF8.

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

Azure Cosmos DB のトランザクション ストアはスキーマに依存しませんが、分析ストアは分析クエリのパフォーマンスを最適化するためにスキーマ化されています。Although Azure Cosmos DB transactional store is schema-agnostic, the analytical store is schematized to optimize for analytical query performance. Azure Synapse Link の自動同期機能により、Azure Cosmos DB では何もしなくても分析ストアでのスキーマ表現が管理されます。これには、入れ子になったデータ型の処理が含まれます。With the autosync capability of Azure Synapse Link, Azure Cosmos DB manages the schema representation in the analytical store out of the box, which includes handling nested data types. サーバーレス SQL プールでは分析ストアのクエリが実行されるため、Azure Cosmos DB の入力データ型を SQL データ型にマップする方法を理解することが重要です。Since a serverless SQL pool queries the analytical store, it's important to understand how to map Azure Cosmos DB input data types to SQL data types.

SQL (Core) API の Azure Cosmos DB アカウントでは、数値、文字列、ブール値、null、入れ子になったオブジェクトまたは配列の JSON プロパティの型がサポートされています。Azure Cosmos DB accounts of SQL (Core) API support JSON property types of number, string, Boolean, null, nested object, or array. OPENROWSETWITH 句を使用する場合は、これらの JSON 型に一致する SQL 型を選択する必要があります。You would need to choose SQL types that match these JSON types if you're using the WITH clause in OPENROWSET. 次の表は、Azure Cosmos DB のさまざまなプロパティの型に対して使用する必要がある SQL 列の型を示しています。The following table shows the SQL column types that should be used for different property types in Azure Cosmos DB.

Azure Cosmos DB のプロパティの型Azure Cosmos DB property type SQL 列の型SQL column type
BooleanBoolean bitbit
IntegerInteger bigintbigint
DecimalDecimal floatfloat
StringString varchar (UTF-8 データベース照合順序)varchar (UTF-8 database collation)
日付と時刻 (ISO 形式の文字列)Date time (ISO-formatted string) varchar(30)varchar(30)
日付と時刻 (UNIX タイムスタンプ)Date time (UNIX timestamp) bigintbigint
[Null]Null any SQL type
入れ子になったオブジェクトまたは配列Nested object or array varchar(max) (UTF-8 データベース照合順序)、JSON テキストとしてシリアル化varchar(max) (UTF-8 database collation), serialized as JSON text

完全に忠実なスキーマFull fidelity schema

Azure Cosmos DB の完全に忠実なスキーマを使用すると、コンテナー内のすべてのプロパティについて、値と最も一致する型の両方を記録できます。Azure Cosmos DB full fidelity schema records both values and their best match types for every property in a container. 完全に忠実なスキーマを持つコンテナーで OPENROWSET 関数を使用すると、各セルに型と実際の値の両方が提供されます。The OPENROWSET function on a container with full fidelity schema provides both the type and the actual value in each cell. 次のクエリでは、完全に忠実なスキーマでコンテナーから項目を読み取ると仮定します。Let's assume that the following query reads the items from a container with full fidelity schema:

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

このクエリの結果は、JSON テキストとしてフォーマットされた型と値を返します。The result of this query will return types and values formatted as JSON text:

date_repdate_rep casescases geo_idgeo_id
{"date":"2020-08-13"}{"date":"2020-08-13"} {"int32":"254"}{"int32":"254"} {"string":"RS"}{"string":"RS"}
{"date":"2020-08-12"}{"date":"2020-08-12"} {"int32":"235"}{"int32":"235"} {"string":"RS"}{"string":"RS"}
{"date":"2020-08-11"}{"date":"2020-08-11"} {"int32":"316"}{"int32":"316"} {"string":"RS"}{"string":"RS"}
{"date":"2020-08-10"}{"date":"2020-08-10"} {"int32":"281"}{"int32":"281"} {"string":"RS"}{"string":"RS"}
{"date":"2020-08-09"}{"date":"2020-08-09"} {"int32":"295"}{"int32":"295"} {"string":"RS"}{"string":"RS"}
{"string":"2020/08/08"}{"string":"2020/08/08"} {"int32":"312"}{"int32":"312"} {"string":"RS"}{"string":"RS"}
{"date":"2020-08-07"}{"date":"2020-08-07"} {"float64":"339.0"}{"float64":"339.0"} {"string":"RS"}{"string":"RS"}

すべての値について、Azure Cosmos DB コンテナーの項目で識別された型を確認できます。For every value, you can see the type identified in an Azure Cosmos DB container item. date_rep プロパティの値のほとんどに date 値が含まれていますが、一部の値が Azure Cosmos DB に誤って文字列として格納されています。Most of the values for the date_rep property contain date values, but some of them are incorrectly stored as strings in Azure Cosmos DB. 完全に忠実なスキーマによって、正しく型指定された date 値と正しくフォーマットされていない string 値の両方が返されます。Full fidelity schema will return both correctly typed date values and incorrectly formatted string values. cases の数値は int32 値として格納される情報ですが、小数として入力されている値が 1 つあります。The number of cases is information stored as an int32 value, but there's one value that's entered as a decimal number. この値の型は float64 です。This value has the float64 type. int32 の最大値を超える値がある場合は int64 型として格納されます。If there are some values that exceed the largest int32 number, they would be stored as the int64 type. この例のすべての geo_id 値は string 型として格納されます。All geo_id values in this example are stored as string types.

重要

WITH 句が指定されていない OPENROWSET 関数では、予期される型を持つ値と、入力された型が正しくない値の両方が公開されます。The OPENROWSET function without a WITH clause exposes both values with expected types and the values with incorrectly entered types. この関数は、レポート用ではなく、データの探索用に設計されています。This function is designed for data exploration and not for reporting. この関数から返された JSON 値を解析してレポートを作成しないでください。Don't parse JSON values returned from this function to build reports. レポートを作成するには、明示的な WITH 句 を使用します。Use an explicit WITH clause to create your reports. 完全に忠実な分析ストアで補正を適用するためには、Azure Cosmos DB コンテナー内の型が正しくない値をクリーンアップする必要があります。You should clean up the values that have incorrect types in the Azure Cosmos DB container to apply corrections in the full fidelity analytical store.

Mongo DB API の種類の Azure Cosmos DB アカウントに対してクエリを実行する必要がある場合は、分析ストア内の完全に忠実なスキーマ表現と、使用される拡張プロパティ名の詳細を、「Azure Cosmos DB の分析ストアとは」で確認してください。If you need to query Azure Cosmos DB accounts of the Mongo DB API kind, you can learn more about the full fidelity schema representation in the analytical store and the extended property names to be used in What is Azure Cosmos DB Analytical Store?.

完全に忠実なスキーマの項目に対してクエリを実行するQuery items with full fidelity schema

完全に忠実なスキーマにクエリを実行する際は、WITH 句で SQL 型と、想定される Azure Cosmos DB のプロパティ型を明示的に指定する必要があります。While querying full fidelity schema, you need to explicitly specify the SQL type and the expected Azure Cosmos DB property type in the WITH clause.

次の例では、stringgeo_id プロパティの正しい型であり、int32cases プロパティの正しい型であると仮定します。In the following example, we'll assume that string is the correct type for the geo_id property and int32 is the correct type for the cases property:

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 値として返されます。Values for geo_id and cases that have other types will be returned as NULL values. このクエリは、式 (cases.int32) で指定された型の cases のみを参照します。This query will reference only the cases with the specified type in the expression (cases.int32).

Azure Cosmos DB コンテナーでクリーンアップできない他の型 (cases.int64cases.float64) を持つ値がある場合は、WITH 句で明示的に参照し、その結果を結合する必要があります。If you have values with other types (cases.int64, cases.float64) that can't be cleaned in an Azure Cosmos DB container, you would need to explicitly reference them in a WITH clause and combine the results. 次のクエリでは、cases 列に格納されている int32int64、および float64 の両方を集約します。The following query aggregates both int32, int64, and float64 stored in the cases column:

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 の数値は int32int64float64 のいずれかの値として格納されます。In this example, the number of cases is stored either as int32, int64, or float64 values. 国ごとの cases の数値を計算するには、すべての値を抽出する必要があります。All values must be extracted to calculate the number of cases per country.

既知の問題Known issues

  • OPENROWSET 列の照合順序のエンコードが UTF-8 でない場合、サーバーレス SQL プールからコンパイル時警告が返されます。A serverless SQL pool will return a compile-time warning if the OPENROWSET column collation doesn't have UTF-8 encoding. 現在のデータベースで実行されるすべての OPENROWSET 関数の既定の照合順序は、alter database current collate Latin1_General_100_CI_AS_SC_UTF8 という T-SQL ステートメントを使用して簡単に変更できます。You can easily change the default collation for all OPENROWSET functions running in the current database by using the T-SQL statement alter database current collate Latin1_General_100_CI_AS_SC_UTF8.

次の表に、考えられるエラーとトラブルシューティングの操作を示します。Possible errors and troubleshooting actions are listed in the following table.

エラーError 根本原因Root cause
構文エラー:Syntax errors:
- Openrowset 付近に不適切な構文があります。- Incorrect syntax near Openrowset
- ... は、BULK OPENROWSET プロバイダー オプションとして認識されません。- ... is not a recognized BULK OPENROWSET provider option.
- ... 付近に不適切な構文があります。- Incorrect syntax near ...
考えられる根本原因:Possible root causes:
- 最初のパラメーターとして CosmosDB を使用していません。- Not using CosmosDB as the first parameter.
- 3 番目のパラメーターで識別子の代わりに文字列リテラルを使用しています。- Using a string literal instead of an identifier in the third parameter.
- 3 番目のパラメーター (コンテナー名) が指定されていません。- Not specifying the third parameter (container name).
CosmosDB 接続文字列でエラーが発生しました。There was an error in the CosmosDB connection string. - アカウント、データベース、またはキーが指定されていません。- The account, database, or key isn't specified.
- 接続文字列に認識されないオプションがいくつかあります。- There's some option in a connection string that isn't recognized.
- 接続文字列の末尾にセミコロン ; が記述されています。- A semicolon (;) is placed at the end of a connection string.
CosmosDB パスを解決できませんでした。エラー: "アカウント名が正しくありません" または "データベース名が正しくありません"。Resolving CosmosDB path has failed with the error "Incorrect account name" or "Incorrect database name." 指定されたアカウント名、データベース名、またはコンテナーが見つからないか、指定されたコレクションで分析ストレージが有効になっていません。The specified account name, database name, or container can't be found, or analytical storage hasn't been enabled to the specified collection.
CosmosDB パスを解決できませんでした。エラー: "シークレット値が正しくありません" または "シークレットが null または空です"。Resolving CosmosDB path has failed with the error "Incorrect secret value" or "Secret is null or empty." アカウント キーが無効であるか、存在しません。The account key isn't valid or is missing.
type name の列 column name は外部データ型 type name と互換性がありません。Column column name of the type type name isn't compatible with the external data type type name. WITH 句に指定された列の型が Azure Cosmos DB コンテナーの型と一致しません。The specified column type in the WITH clause doesn't match the type in the Azure Cosmos DB container. セクション「Azure Cosmos DB から SQL 型へのマッピング」で説明されているように列の型を変更するか、または VARCHAR 型を使用してください。Try to change the column type as it's described in the section Azure Cosmos DB to SQL type mappings, or use the VARCHAR type.
すべてのセルで、この列には NULL 値が含まれます。Column contains NULL values in all cells. WITH 句の列名またはパス式が間違っている可能性があります。Possibly a wrong column name or path expression in the WITH clause. WITH 句の列名 (または列の型の後のパス式) は、Azure Cosmos DB コレクションの一部のプロパティ名と一致する必要があります。The column name (or path expression after the column type) in the WITH clause must match some property name in the Azure Cosmos DB collection. 比較では、"大文字と小文字が区別されます"。Comparison is case-sensitive. たとえば、productCodeProductCode は異なるプロパティです。For example, productCode and ProductCode are different properties.

Azure Synapse Analytics のフィードバック ページで、提案や問題を報告できます。You can report suggestions and issues on the Azure Synapse Analytics feedback page.

次のステップNext steps

詳細については、次の記事を参照してください。For more information, see the following articles: