Abfragen von Cosmos DB mit Synapse SQL

5 Minuten

Zusätzlich zu einem Spark-Pool können Sie auch einen Azure Cosmos DB-Analysecontainer abfragen, indem Sie in Azure Synapse Analytics einen integrierten serverlosen SQL-Pool verwenden. Dazu können Sie mit der SQL-Funktion OPENROWSET eine Verbindung mit dem verknüpften Dienst für Ihre Azure Cosmos DB-Datenbank herstellen.

Verwenden von OPENROWSET mit einem Authentifizierungsschlüssel

Standardmäßig wird der Zugriff auf ein Azure Cosmos DB-Konto mittels eines Authentifizierungsschlüssels authentifiziert. Sie können diesen Schlüssel in einer OPENROWSET-Anweisung als Teil einer Verbindungszeichenfolge angeben, um eine Verbindung über einen mit einem SQL-Pool verknüpften Dienst herzustellen, wie im folgenden Beispiel gezeigt:

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

Tipp

Sie finden den Primär- und Sekundärschlüssel Ihres Cosmos DB-Kontos auf dessen Seite Schlüssel im Azure-Portal.

Die Ergebnisse dieser Abfrage könnten etwa wie folgt aussehen, einschließlich der Metadaten und der von der Anwendung definierten Felder der Elemente im Azure Cosmos DB-Container:

_rid	_ts	productID	ProductName	id	_etag
mjMaAL...==	1655414791	123	Widget	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-...
...	...	...	...	...	...

Die Daten werden aus dem Analysespeicher abgerufen. Die Abfrage hat keinen Einfluss auf den Betriebsspeicher.

Verwenden von OPENROWSET mit Anmeldeinformationen

Anstatt den Authentifizierungsschlüssel in jeden Aufruf von OPENROWSET aufzunehmen, können Sie Anmeldeinformationen definieren, die die Authentifizierungsinformationen für Ihr Cosmos DB-Konto kapseln, und diese Anmeldeinformationen in nachfolgenden Abfragen verwenden. Um Anmeldeinformationen zu erstellen, verwenden Sie die CREATE CREDENTIAL-Anweisung wie in diesem Beispiel gezeigt:

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

Wenn die Anmeldeinformationen eingerichtet sind, können Sie sie in einer OPENROWSET-Funktion wie dieser verwenden:

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

Auch hier enthalten die Ergebnisse Metadaten und von der Anwendung definierte Felder aus dem Analysespeicher:

_rid	_ts	productID	ProductName	id	_etag
mjMaAL...==	1655414791	123	Widget	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-...
...	...	...	...	...	...

Angeben eines Schemas

Die OPENROWSET-Syntax enthält eine WITH-Klausel, mit der Sie ein Schema für das resultierende Rowset definieren können. Sie können damit einzelne Felder angeben und Datentypen zuweisen, wie im folgenden Beispiel gezeigt:

 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

Angenommen, die Felder des Analysespeichers enthalten productID und productName. Dann sieht das resultierende Rowset ähnlich aus wie die folgende Tabelle:

productID	ProductName
123	Widget
124	Wotsit
125	Thingumy
...	...

Sie können zwar einzelne Spaltennamen in der SELECT-Klausel angeben (z. B. SELECT productID, productName ...), aber diese Möglichkeit der Angabe einzelner Spalten ist ggf. nur von begrenztem Nutzen. Denken Sie jedoch an Fälle, in denen die im Betriebsspeicher gespeicherten JSON-Quelldokumente mehrere Ebenen von Feldern enthalten, wie im folgenden Beispiel gezeigt:

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

Die WITH-Klausel unterstützt die Einbeziehung expliziter JSON-Pfade, sodass Sie geschachtelte Felder behandeln und den Feldnamen Aliase zuweisen können, wie in diesem Beispiel gezeigt:

 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

Die Ergebnisse dieser Abfrage enthalten die folgende Zeile für Produkt 126:

ProductNo	ProductName	Supplier	SupplierPhoneNo
126	Sprocket	Contoso	555-123-4567

Erstellen einer Sicht in eine Datenbank

Wenn Sie dieselben Daten häufig abfragen oder Berichts- und Visualisierungstools verwenden müssen, die auf SELECT-Anweisungen ohne die OPENROWSET-Funktion angewiesen sind, können Sie die Daten mit einer Sicht abstrahieren. Um eine Sicht zu erstellen, sollten Sie eine neue Datenbank anlegen, in der Sie sie definieren (benutzerdefinierte Sichten in der Datenbank master werden nicht unterstützt), wie im folgenden Beispiel gezeigt:

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

Tipp

Wenn Sie eine Datenbank erstellen, die auf Daten in Cosmos DB zugreift, wählen Sie am besten eine UTF-8-basierte Sortierung, um Kompatibilität mit Zeichenfolgen in Cosmos DB zu gewährleisten.

Nachdem die Sicht erstellt wurde, können Benutzer und Clientanwendungen sie wie jede andere SQL-Sicht oder -Tabelle abfragen:

SELECT * FROM products;

Überlegungen für serverlose SQL-Pools und Azure Cosmos DB

Wenn Sie vorhaben, einen serverlosen SQL-Pool zur Abfrage von Daten in einem Azure Cosmos DB-Analysespeicher zu verwenden, sollten Sie die folgenden bewährten Methoden beachten: