Abfragen Azure Cosmos DB Daten mithilfe eines serverlosen SQL-Pools

Mit einem serverlosen SQL-Pool können Sie Daten in Ihren Azure Cosmos DB Containern analysieren, die mit Azure Synapse Link in nahezu Echtzeit aktiviert sind, ohne die Leistung Ihrer Transaktionsworkloads zu beeinträchtigen. Es bietet eine vertraute Transact-SQL (T-SQL)-Syntax zum Abfragen von Daten aus dem analytical store und der integrierten Konnektivität zu einer breiten Palette von Business Intelligence (BI) und Ad-hoc-Abfragetools über die T-SQL-Schnittstelle.

Zum Abfragen von Azure Cosmos DB wird die vollständige SELECT-Abfrage durch die OPENROWSET-Funktion unterstützt, die die meisten SQL-Funktionen und -Operatoren einschließt. Sie können auch Ergebnisse der Abfrage speichern, die Daten aus Azure Cosmos DB zusammen mit Daten in Azure Blob Storage oder Azure Data Lake Storage liest, indem Sie externe Tabelle als Auswahl (CETAS) erstellen. Sie können derzeit keine serverlosen SQL-Poolabfrageergebnisse mithilfe von CETAS in Azure Cosmos DB speichern.

In diesem Artikel wird erläutert, wie Sie eine Abfrage mit einem serverlosen SQL-Pool schreiben, der Daten aus Azure Cosmos DB Containern abfragt, die mit Azure Synapse Link aktiviert sind. Weitere Informationen zum Erstellen serverloser SQL-Poolansichten über Azure Cosmos DB Container und das Verbinden mit Power BI Modellen finden Sie in dieses Lernprogramm. In diesem Lernprogramm wird ein Container mit einem Azure Cosmos DB klar definierten Schema verwendet. Sie können sich auch das Lernmodul über Query Azure Cosmos DB mit SQL Serverless für Azure Synapse Analytics anschauen.

Voraussetzungen

• Stellen Sie sicher, dass Sie den Analysespeicher vorbereitet haben:
  • Aktivieren Sie den analytischen Speicher auf your Azure Cosmos DB Container.
  • Rufen Sie die Verbindungszeichenfolge mit einem schreibgeschützten Schlüssel ab, den Sie zum Abfragen des Analysedaten-Speichers verwenden können.
  • Rufen Sie den schreibgeschützten Schlüssel ab, der für den Zugriff auf den Azure Cosmos DB Container verwendet wird.
• Stellen Sie sicher, dass Sie alle bewährten Methoden angewendet haben, z. B.:
  • Stellen Sie sicher, dass sich Ihr Azure Cosmos DB analytischer Speicher in derselben Region wie der serverlose SQL-Pool befindet.
  • Stellen Sie sicher, dass sich die Clientanwendung (Power BI, Analysedienst) in derselben Region wie der serverlose SQL-Pool befindet.
  • Wenn Sie eine große Datenmenge (mehr als 80 GB) zurückgeben, sollten Sie die Verwendung einer Zwischenspeicherungsebene wie Analysis Services erwägen und die Partitionen, die kleiner als 80 GB sind, in das Analysis Services-Modell laden.
  • Wenn Sie Daten mithilfe von Zeichenfolgenspalten filtern, stellen Sie sicher, dass Sie die OPENROWSET-Funktion mit der expliziten WITH-Klausel mit den kleinsten möglichen Typen verwenden. Verwenden Sie beispielsweise nicht VARCHAR(1000), wenn Sie wissen, dass die Eigenschaft bis zu fünf Zeichen enthält.

Übersicht

Mit serverlosen SQL-Pool können Sie Azure Cosmos DB analytischen Speicher mithilfe OPENROWSET-Funktion abfragen.

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

Die SQL-connection string für Azure Cosmos DB umfasst die folgenden Komponenten:

• account – Der Name des Azure Cosmos DB Kontos, auf das Sie abzielen.
• database - Der Containername, angegeben ohne Anführungszeichen in der OPENROWSET-Syntax. Wenn der Containername Sonderzeichen enthält (z. B. ein Gedankenstrich -), sollte er in eckige Klammern ([]) eingeschlossen werden.
• Region (optional) - Die Region Ihres Cosmos DB Analytischen Speichers. Wenn sie nicht angegeben wird, wird die primäre Region des Containers verwendet.
• endpoint (optional): Der Cosmos DB-Endpunkt-URI (zum Beispiel https://<account name>.documents.azure.us), der erforderlich ist, wenn Ihr Cosmos DB-Konto nicht dem Standardformat *.documents.azure.com entspricht.

Diese Eigenschaften können aus dem Standard Cosmos DB connection string identifiziert werden, z. B.:

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

Die SQL-Verbindungszeichenfolge kann wie folgt formatiert werden:

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

Diese connection string enthält nicht die Authentifizierungsinformationen, die zum Herstellen einer Verbindung mit Cosmos DB-Analysespeicher erforderlich sind. Je nach Art der verwendeten Authentifizierung sind zusätzliche Informationen erforderlich:

• Wenn OPENROWSET eine verwaltete Arbeitsbereichsidentität für den Zugriff auf den Analysespeicher verwendet, sollten Sie die Eigenschaft AuthType hinzufügen.
• Wenn OPENROWSET einen Inline-Kontoschlüssel verwendet, sollten Sie die Eigenschaft key hinzufügen. Auf diese Weise können Sie Azure Cosmos DB Sammlungen abfragen, ohne Anmeldeinformationen vorbereiten zu müssen.
• Anstatt Authentifizierungsinformationen in die connection string einzuschließen, kann OPENROWSET auf eine Anmeldeinformation verweisen, die den Azure Cosmos DB Kontoschlüssel enthält. Dieser Ansatz kann verwendet werden, um Ansichten für Azure Cosmos DB Sammlungen zu erstellen.

Diese Optionen werden unten beschrieben.

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

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>

Beispieldatensatz

Die Beispiele in diesem Artikel basieren auf Daten aus dem European Center for Disease Prevention and Control (ECDC) COVID-19 Cases and COVID-19 Open Research Dataset (CORD-19).

Stellen Sie sicher, dass Sie die folgenden Ressourcen erstellen, um zusammen mit diesem Artikel zu zeigen, wie Sie Azure Cosmos DB Daten mit einem serverlosen SQL-Pool abfragen:

• Ein Azure Cosmos DB Datenbankkonto, das Azure Synapse Link aktiviert ist
• Eine Azure Cosmos DB Datenbank mit dem Namen covid
• Zwei Azure Cosmos DB Container namens Ecdc und Cord19, die mit den vorherigen Beispieldatensätzen geladen wurden.

Beachten Sie, dass diese Verbindung keine Leistung garantiert, da sich dieses Konto möglicherweise in einer von Ihrem Synapse SQL-Endpunkt entfernten Region befindet.

Erkunden von Azure Cosmos DB Daten mit automatischer Schema-Ableitung

Die einfachste Möglichkeit zum Untersuchen von Daten in Azure Cosmos DB ist die Verwendung der automatischen Schema-Ableitungsfunktion. Indem Sie die WITH-Klausel aus der OPENROWSET-Anweisung weglassen, können Sie den serverlosen SQL-Pool anweisen, das Schema des analytischen Speichers des Azure Cosmos DB Containers automatisch zu definieren (ableiten).

SELECT TOP 10 *
FROM OPENROWSET( 
       'CosmosDB',
       'Account=your-cosmosdb;Database=covid;Key=yourcosmosdbkey',
       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 = 'yourcosmosdbkey';
*/
SELECT TOP 10 *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=your-cosmosdb;Database=covid',
      OBJECT = 'Ecdc',
      SERVER_CREDENTIAL = 'MyCosmosDbAccountCredential'
    ) with ( date_rep varchar(20), cases bigint, geo_id varchar(6) ) as rows

Im vorherigen Beispiel haben wir den serverlosen SQL-Pool angewiesen, eine Verbindung mit der Datenbank covid im Azure Cosmos DB-Konto herzustellen, das durch Verwendung des Azure Cosmos DB-Schlüssels authentifiziert wurde (als Dummy im vorherigen Beispiel). Danach haben wir auf den Analysespeicher des Containers Ecdc in der Region West US 2 zugegriffen. Da keine Projektion bestimmter Eigenschaften vorhanden ist, gibt die funktion OPENROWSET alle Eigenschaften aus den Azure Cosmos DB Elementen zurück.

Vorausgesetzt, dass die Elemente im container Azure Cosmos DB date_rep, cases und geo_id Eigenschaften aufweisen, werden die Ergebnisse dieser Abfrage in der folgenden Tabelle angezeigt:

Wenn Sie Daten aus dem anderen Container in derselben Azure Cosmos DB-Datenbank untersuchen müssen, können Sie denselben connection string verwenden und auf den erforderlichen Container als dritten Parameter verweisen:

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

Explizites Angeben des Schemas

Während die automatische Schema-Inferenzfunktion in OPENROWSET eine einfache, leicht zu bedienende Erfahrung bietet, könnte Ihr Geschäftsszenario erfordern, dass Sie das Schema explizit angeben, um nur relevante Eigenschaften aus den Daten der Azure Cosmos DB zu lesen.

In der OPENROWSET-Funktion können Sie die Eigenschaften, die aus den Daten im Container gelesen werden sollen, sowie deren Datentypen explizit angeben.

Angenommen, wir haben einige Daten aus dem ECDC COVID-Dataset mit der folgenden Struktur in Azure Cosmos DB importiert:

{"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"}

Diese flachen JSON-Dokumente in Azure Cosmos DB können als Eine Reihe von Zeilen und Spalten in Synapse SQL dargestellt werden. In der OPENROWSET-Klausel der WITH-Funktion können Sie eine Teilmenge von Eigenschaften, die gelesen werden sollen, sowie die genauen Spaltentypen angeben:

SELECT TOP 10 *
FROM OPENROWSET(
      'CosmosDB',
      'Account=your-cosmosdb;Database=covid;Key=yourcosmosdbkey',
       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 = 'yourcosmosdbkey';
*/
SELECT TOP 10 *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=your-cosmosdb;Database=covid',
      OBJECT = 'Ecdc',
      SERVER_CREDENTIAL = 'MyCosmosDbAccountCredential'
    ) with ( date_rep varchar(20), cases bigint, geo_id varchar(6) ) as rows
   

Das Ergebnis dieser Abfrage könnte wie in der folgenden Tabelle aussehen:

Weitere Informationen zu den SQL-Typen, die für Azure Cosmos DB Werte verwendet werden sollen, finden Sie unter Azure Cosmos DB zu SQL-Typzuordnungen am Ende dieses Artikels.

Ansicht erstellen

Das Erstellen von Sichten in master- oder Standarddatenbanken wird weder empfohlen noch unterstützt. Deshalb müssen Sie eine Benutzerdatenbank für Ihre Sichten erstellen.

Nachdem Sie das Schema identifiziert haben, können Sie eine Ansicht über den Azure Cosmos DB-Daten vorbereiten. Sie sollten Ihren Azure Cosmos DB-Kontoschlüssel in einer separaten Anmeldeinformation speichern und von der OPENROWSET-Funktion auf diese Anmeldeinformationen verweisen. Bewahren Sie Ihren Kontoschlüssel nicht in der Ansichtsdefinition auf.

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

Verwenden Sie OPENROWSET nicht ohne explizit definiertes Schema, da dies die Leistung beeinträchtigen könnte. Stellen Sie sicher, dass Sie die kleinsten möglichen Größen für Ihre Spalten (z. B. VARCHAR(100) anstelle des Standards VARCHAR(8000)) verwenden. Sie sollten eine UTF-8-Sortierung als standardmäßige Datenbanksortierung verwenden oder sie als explizite Spaltensortierung festlegen, um UTF-8-Konvertierungsprobleme zu vermeiden. Die Sortierung Latin1_General_100_BIN2_UTF8 bietet die beste Leistung, wenn Sie Daten anhand von Zeichenfolgenspalten filtern.

Wenn Sie die Ansicht abfragen, könnten Fehler oder unerwartete Ergebnisse auftreten. Die Ansicht verweist auf Spalten oder Objekte, die wahrscheinlich geändert wurden oder nicht mehr vorhanden sind. Sie müssen die Ansichtsdefinition manuell anpassen, um sie an die zugrunde liegenden Schemaänderungen anzupassen. Beachten Sie, dass dies sowohl bei Nutzung der automatischen Schemaableitung in der Ansicht als auch bei expliziter Angabe des Schemas auftreten kann.

Abfragen von geschachtelten Objekten

Mit Azure Cosmos DB können Sie komplexere Datenmodelle darstellen, indem Sie sie als geschachtelte Objekte oder Arrays erstellen. Die autosynchrone Funktion von Azure Synapse Link für Azure Cosmos DB verwaltet die Schemadarstellung im analytischen Speicher sofort, einschließlich der Behandlung geschachtelter Datentypen, die eine umfassende Abfrage aus dem serverlosen SQL-Pool ermöglichen.

Beispielsweise enthält das CORD-19-Dataset JSON-Dokumente mit folgender Struktur:

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

Die geschachtelten Objekte und Arrays in Azure Cosmos DB werden als JSON-Zeichenfolgen im Abfrageergebnis dargestellt, wenn die funktion OPENROWSET sie liest. Sie können diese Pfade zu geschachtelten Werten in den Objekten angeben, wenn Sie die WITH-Klausel verwenden:

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

Das Ergebnis dieser Abfrage könnte wie in der folgenden Tabelle aussehen:

Weitere Informationen finden Sie unter Analysieren Sie komplexe Datentypen in Azure Synapse Analytics oder Abfragen verschachtelter Typen in Parquet- und JSON-Dateien mit dem serverlosen SQL-Pool.

Vereinfachen geschachtelter Arrays

Azure Cosmos DB-Daten können verschachtelte Unterarrays wie das Autorenarray aus einem CORD-19-Datensatz enthalten.

{
    "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>
            },
            ...
        ],
        ...
}

In einigen Fällen müssen Sie möglicherweise die Eigenschaften aus dem obersten Element (metadata) mit allen Elementen des Arrays (authors) verknüpfen. Ein serverloser SQL-Pool ermöglicht Ihnen das Vereinfachen von geschachtelten Strukturen durch Anwenden der OPENJSON-Funktion auf das geschachtelte Array:

SELECT
    *
FROM
    OPENROWSET(
      'CosmosDB',
      'Account=your-cosmosdb;Database=covid;Key=yourcosmosdbkey',
       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

Das Ergebnis dieser Abfrage könnte wie in der folgenden Tabelle aussehen:

Azure Cosmos DB nach SQL-Typzuordnungen

Obwohl Azure Cosmos DB Transaktionsspeicher schemaunabhängig ist, wird der Analysespeicher schematisiert, um die Analyseabfrageleistung zu optimieren. Mit der autosynchronen Funktion von Azure Synapse Link verwaltet Azure Cosmos DB die Schemadarstellung im analytischen Speicher sofort, einschließlich der Behandlung geschachtelter Datentypen. Da ein serverloser SQL-Pool den Analysespeicher abfragt, ist es wichtig zu verstehen, wie Azure Cosmos DB Eingabedatentypen SQL-Datentypen zugeordnet werden.

Azure Cosmos DB Konten der SQL(Core)-API unterstützen JSON-Eigenschaftstypen von number, string, Boolean, null, nested-Objekt oder array. Wenn Sie die WITH-Klausel in OPENROWSET verwenden, müssen Sie SQL-Typen auswählen, die diesen JSON-Typen entsprechen. In der folgenden Tabelle sind die SQL-Spaltentypen aufgeführt, die für unterschiedliche Eigenschaftstypen in Azure Cosmos DB verwendet werden sollen.

Schema mit vollständiger Genauigkeit

Azure Cosmos DB Full Fidelity-Schema zeichnet sowohl Werte als auch ihre besten Übereinstimmungstypen für jede Eigenschaft in einem Container auf. Die OPENROWSET-Funktion in einem Container mit einem Schema mit vollständiger Genauigkeit gibt sowohl den Typ als auch den tatsächlichen Wert in jeder Zelle an. Nehmen wir an, dass die folgende Abfrage die Elemente aus einem Container mit vollständigem Genauigkeitsschema liest:

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

Im Ergebnis dieser Abfrage werden Typen und Werte zurück zurückgegeben, die als JSON-Text formatiert sind:

Für jeden Wert können Sie den in einem Azure Cosmos DB Containerelement identifizierten Typ sehen. Die meisten Werte für die eigenschaft date_rep enthalten date werte, aber einige davon werden in Azure Cosmos DB fälschlicherweise als Zeichenfolgen gespeichert. Das Schema mit vollständiger Genauigkeit gibt sowohl ordnungsgemäß typisierte date-Werte als auch falsch formatierte string-Werte zurück.

Die Anzahl der Fälle wird als int32-Wert gespeichert, allerdings gibt es einen Wert, der als Dezimalzahl eingegeben wird. Dieser Wert weist den Typ float64 auf. Werte, die die größte int32-Zahl überschreiten, werden als int64-Typ gespeichert. Alle geo_id-Werte in diesem Beispiel werden als string-Typen gespeichert.

Wenn Sie Azure Cosmos DB für MongoDB-Konten abfragen möchten, erfahren Sie mehr über die Darstellung des Schemas mit hoher Genauigkeit im Analytikspeicher und die erweiterten Eigenschaftsnamen, die in Was ist Azure Cosmos DB Analytical Store? verwendet werden sollen.

Abfragen von Elementen mit einem Schema mit vollständiger Genauigkeit

Beim Abfragen des Schemas mit vollständiger Genauigkeit müssen Sie den SQL-Typ und den erwarteten Azure Cosmos DB Eigenschaftentyp in der WITH-Klausel explizit angeben.

Im folgenden Beispiel wird davon ausgegangen, dass string der richtige Typ für die geo_id-Eigenschaft und int32 der richtige Typ für die cases-Eigenschaft ist:

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

Werte für geo_id und cases, die andere Typen aufweisen, werden als NULL-Werte zurückgegeben. Diese Abfrage verweist nur auf die cases mit dem Typ, der im Ausdruck angegeben ist (cases.int32).

Wenn Sie Werte mit anderen Typen (cases.int64, cases.float64) haben, die nicht in einem Azure Cosmos DB-Container bereinigt werden können, müssen Sie in einer WITH-Klausel explizit darauf verweisen und die Ergebnisse kombinieren. Die folgende Abfrage aggregiert sowohl int32, int64 als auch float64, die in der Spalte cases gespeichert sind:

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

In diesem Fall wird die Anzahl der Fälle als int32-, int64- oder float64-Wert gespeichert. Alle Werte müssen extrahiert werden, damit die Anzahl der Fälle pro Land bzw. Region berechnet werden kann.

Problembehandlung

Überprüfen Sie die self-Help-Seite, um die bekannten Probleme oder Schritte zur Problembehandlung zu finden, die Ihnen helfen können, potenzielle Probleme mit Azure Cosmos DB Abfragen zu beheben.

Zugehöriger Inhalt

• Verwenden Sie Power BI und den serverlosen Synapse SQL-Pool, um Azure Cosmos DB-Daten zu analysieren
• Erstellen und Verwenden von Sichten in einem serverlosen SQL-Pool
• Tutorial: Untersuchen und Analysieren von Data Lakes mit einem serverlosen SQL-Pool
• Wenn Fehler oder Leistungsprobleme auftreten, lesen Sie die Problembehandlung bei serverlosen SQL-Pools.
• Module: Implementieren von Azure Synapse Link mit Azure Cosmos DB