Query's uitvoeren op Cosmos DB met Synapse SQL

  • 5 minuten

Naast het gebruik van een Spark-pool kunt u ook een query uitvoeren op een analytische Container van Azure Cosmos DB met behulp van een ingebouwde serverloze SQL-pool in Azure Synapse Analytics. Hiervoor kunt u de OPENROWSET SQL-functie gebruiken om verbinding te maken met de gekoppelde service voor uw Azure Cosmos DB-database.

OPENROWSET gebruiken met een verificatiesleutel

Standaard wordt de toegang tot een Azure Cosmos DB-account geverifieerd door een verificatiesleutel. U kunt deze sleutel gebruiken als onderdeel van een verbindingsreeks in een OPENROWSET instructie om verbinding te maken via een gekoppelde service vanuit een SQL-pool, zoals wordt weergegeven in het volgende voorbeeld:

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

Tip

U vindt een primaire en secundaire sleutel voor uw Cosmos DB-account op de pagina Sleutels in Azure Portal.

De resultaten van deze query kunnen er ongeveer als volgt uitzien, waaronder metagegevens en toepassingsgedefinieerde velden uit de items in de 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-...
... ... ... ... ... ...

De gegevens worden opgehaald uit de analytische opslag en de query heeft geen invloed op het operationele archief.

OPENROWSET gebruiken met een referentie

In plaats van de verificatiesleutel in elke aanroep naar OPENROWSET op te slaan, kunt u een referentie definiëren die de verificatiegegevens voor uw Cosmos DB-account inkapselt en de referentie in volgende query's gebruikt. Als u een referentie wilt maken, gebruikt u de CREATE CREDENTIAL instructie zoals wordt weergegeven in dit voorbeeld:

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

Als de referentie is ingesteld, kunt u deze in een OPENROWSET functie als volgt gebruiken:

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

De resultaten bevatten opnieuw metagegevens en toepassingsgedefinieerde velden uit de analytische opslag:

_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-...
... ... ... ... ... ...

Een schema opgeven

De OPENROWSET syntaxis bevat een WITH component die u kunt gebruiken om een schema te definiëren voor de resulterende rijenset. U kunt dit gebruiken om afzonderlijke velden op te geven en gegevenstypen toe te wijzen, zoals wordt weergegeven in het volgende voorbeeld:

 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

In dit geval, ervan uitgaande dat de velden in de analytische opslag product-id en productName bevatten, lijkt de resulterende rijenset op de volgende tabel:

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

U kunt natuurlijk afzonderlijke kolomnamen opgeven in de SELECT component (bijvoorbeeld SELECT productID, productName ...), zodat deze mogelijkheid om afzonderlijke kolommen op te geven mogelijk beperkt gebruik lijkt. Overweeg echter gevallen waarin de bron-JSON-documenten die zijn opgeslagen in het operationele archief meerdere niveaus van velden bevatten, zoals wordt weergegeven in het volgende voorbeeld:

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

De WITH component ondersteunt het opnemen van expliciete JSON-paden, zodat u geneste velden kunt verwerken en aliassen kunt toewijzen aan veldnamen, zoals wordt weergegeven in dit voorbeeld:

 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

De resultaten van deze query bevatten de volgende rij voor product 126:

ProductNo ProductName Leverancier LeverancierPhoneNo
126 Sprocket Contoso 555-123-4567

Een weergave maken in een database

Als u regelmatig query's op dezelfde gegevens wilt uitvoeren of als u hulpprogramma's voor rapportage en visualisatie wilt gebruiken die afhankelijk zijn van SELECT instructies die de OPENROWSET functie niet bevatten, kunt u een weergave gebruiken om de gegevens te abstraheren. Als u een weergave wilt maken, moet u een nieuwe database maken waarin deze moet worden gedefinieerd (door de gebruiker gedefinieerde weergaven in de hoofddatabase worden niet ondersteund), zoals wordt weergegeven in het volgende voorbeeld:

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

Tip

Wanneer u een database maakt die toegang heeft tot gegevens in Cosmos DB, kunt u het beste een op UTF-8 gebaseerde sortering gebruiken om compatibiliteit met tekenreeksen in Cosmos DB te garanderen.

Nadat de weergave is gemaakt, kunnen gebruikers en clienttoepassingen er query's op uitvoeren zoals elke andere SQL-weergave of -tabel:

SELECT * FROM products;

Overwegingen voor serverloze SQL-pools en Azure Cosmos DB

Wanneer u van plan bent om een serverloze SQL-pool te gebruiken om query's uit te voeren op gegevens in een analytische opslag van Azure Cosmos DB, moet u rekening houden met de volgende aanbevolen procedures:

  • Richt uw analytische opslag van Azure Cosmos DB en eventuele clienttoepassingen (bijvoorbeeld Microsoft Power BI) in dezelfde regio in als een serverloze SQL-pool.

    Azure Cosmos DB-containers kunnen worden gerepliceerd naar meerdere regio's. Als u een container met meerdere regio's hebt, kunt u een region parameter opgeven in de OPENROWSET-verbindingsreeks om ervoor te zorgen dat query's worden verzonden naar een specifieke regionale replica van de container.

  • Wanneer u met tekenreekskolommen werkt, gebruikt u de functie OPENROWSET met de expliciete WITH-component en geeft u een geschikte gegevenslengte op voor de tekenreeksgegevens.