Query's uitvoeren op Azure Cosmos DB-gegevens met behulp van een serverloze SQL-pool

Met een serverloze SQL-pool kunt u gegevens in uw Azure Cosmos DB-containers analyseren die in bijna realtime zijn ingeschakeld met Azure Synapse Link , zonder dat dit van invloed is op de prestaties van uw transactionele workloads. Het biedt een vertrouwde Transact-SQL -syntaxis (T-SQL) voor het opvragen van gegevens uit de analytische opslag en geïntegreerde connectiviteit met een breed scala aan business intelligence (BI) en ad-hoc queryhulpprogramma's via de T-SQL-interface.

Voor het uitvoeren van query's op Azure Cosmos DB wordt het volledige SELECT-oppervlak ondersteund via de functie OPENROWSET , waaronder de meeste SQL-functies en -operators. U kunt ook resultaten opslaan van de query waarmee gegevens uit Azure Cosmos DB worden gelezen, samen met gegevens in Azure Blob Storage of Azure Data Lake Storage door een externe tabel te maken als select (CETAS). U kunt momenteel geen queryresultaten voor serverloze SQL-pools opslaan in Azure Cosmos DB met behulp van CETAS.

In dit artikel wordt uitgelegd hoe u een query schrijft met een serverloze SQL-pool waarmee gegevens worden opgevraagd uit Azure Cosmos DB-containers die zijn ingeschakeld met Azure Synapse Link. Vervolgens vindt u meer informatie over het bouwen van serverloze SQL-poolweergaven via Azure Cosmos DB-containers en het verbinden met Power BI-modellen in deze zelfstudie. In deze zelfstudie wordt een container met een goed gedefinieerd Azure Cosmos DB-schema gebruikt. U kunt ook de Learn-module bekijken over het uitvoeren van query's op Azure Cosmos DB met SQL Serverless voor Azure Synapse Analytics.

Vereiste voorwaarden

• Zorg ervoor dat u de analytische opslag voorbereidt:
  • Schakel analytische opslag in voor uw Azure Cosmos DB-containers.
  • Haal de verbindingsreeks op met een alleen-lezen sleutel die u kunt gebruiken om een query uit te voeren op analytische opslag.
  • Haal de alleen-lezensleutel op die wordt gebruikt voor toegang tot de Azure Cosmos DB-container.
• Zorg ervoor dat u alle aanbevolen procedures hebt toegepast, zoals:
  • Zorg ervoor dat uw analytische opslag van Azure Cosmos DB zich in dezelfde regio bevindt als een serverloze SQL-pool.
  • Zorg ervoor dat de clienttoepassing (Power BI, Analysis Service) zich in dezelfde regio bevindt als de serverloze SQL-pool.
  • Als u een grote hoeveelheid gegevens retourneert (meer dan 80 GB), kunt u overwegen de cachelaag zoals Analysis Services te gebruiken en de partities kleiner dan 80 GB in het Analysis Services-model te laden.
  • Als u gegevens filtert met behulp van tekenreekskolommen, moet u ervoor zorgen dat u de OPENROWSET functie gebruikt met de expliciete WITH component met de kleinste mogelijke typen. Gebruik bijvoorbeeld niet VARCHAR(1000) als u weet dat de eigenschap maximaal vijf tekens heeft.

Overzicht

Met een serverloze SQL-pool kunt u query's uitvoeren op analytische opslag van Azure Cosmos DB met behulp van OPENROWSET een functie.

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

De SQL-verbindingsreeks voor Azure Cosmos DB bevat de volgende onderdelen:

• account : de naam van het Azure Cosmos DB-account waarop u zich richt.
• database : de containernaam, opgegeven zonder aanhalingstekens in de syntaxis van OPENROWSET. Als de containernaam speciale tekens bevat (bijvoorbeeld een streepje -), moet deze tussen vierkante haken ([]) worden geplaatst.
• regio (optioneel): de regio van uw Cosmos DB analytische opslag. Als u dit weglaat, wordt de primaire regio van de container gebruikt.
• eindpunt (optioneel): de URI van het Cosmos DB-eindpunt (bijvoorbeeld https://<account name>.documents.azure.us) die vereist is als uw Cosmos DB-account niet de standaardindeling *.documents.azure.com volgt.

Deze eigenschappen kunnen worden geïdentificeerd aan de hand van de standaard Cosmos DB-verbindingsreeks, bijvoorbeeld:

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

De SQL-verbindingsreeks kan als volgt worden opgemaakt:

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

Deze verbindingsreeks bevat niet de verificatiegegevens die nodig zijn om verbinding te maken met de analytische opslag van Cosmos DB. Afhankelijk van het type authenticatie dat wordt gebruikt, is aanvullende informatie nodig:

• Als OPENROWSET u een door de werkruimte beheerde identiteit gebruikt om toegang te krijgen tot het analytische archief, moet u de AuthType eigenschap toevoegen.
• Als OPENROWSET u een inline accountsleutel gebruikt, moet u de key eigenschap toevoegen. Hierdoor kunt u query's uitvoeren op Azure Cosmos DB-verzamelingen zonder dat u referenties hoeft voor te bereiden.
• In plaats van verificatiegegevens op te nemen in de verbindingsreeks, OPENROWSET kunt u verwijzen naar een referentie die de Azure Cosmos DB-accountsleutel bevat. Deze benadering kan worden gebruikt om weergaven te maken voor Azure Cosmos DB-verzamelingen.

Deze opties worden hieronder beschreven.

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>

Voorbeelddataverzameling

De voorbeelden in dit artikel zijn gebaseerd op gegevens van het Europees Centrum voor ziektepreventie en -bestrijding (ECDC) COVID-19 Cases en COVID-19 Open Research Dataset (CORD-19).

U kunt de licentie en de structuur van gegevens op deze pagina's bekijken. U kunt ook voorbeeldgegevens downloaden voor de ECDC - en CORD-19-gegevenssets.

Als u samen met dit artikel wilt zien hoe u query's uitvoert op Azure Cosmos DB-gegevens met een serverloze SQL-pool, moet u ervoor zorgen dat u de volgende resources maakt:

• Een Azure Cosmos DB-databaseaccount waarvoor Azure Synapse Link is ingeschakeld
• Een Azure Cosmos DB-database met de naam covid
• Twee Azure Cosmos DB-containers met de naam Ecdc en Cord19 geladen met de voorgaande voorbeeldgegevenssets

Houd er rekening mee dat deze verbinding geen prestaties garandeert omdat dit account zich mogelijk in een externe regio bevindt in vergelijking met uw Synapse SQL-eindpunt.

Azure Cosmos DB-gegevens verkennen met automatische schemadeductie

De eenvoudigste manier om gegevens in Azure Cosmos DB te verkennen, is door gebruik te maken van de functie voor automatische schemadeductie. Door de clausule uit de WITHOPENROWSET instructie weg te laten, kunt u de serverless SQL-pool opdracht geven om het schema van de analytische opslag van de Azure Cosmos DB-container automatisch te detecteren (afleiden).

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

In het voorgaande voorbeeld hebben we de serverloze SQL-pool geïnstrueerd om verbinding te maken met de covid database in het Azure Cosmos DB-account MyCosmosDbAccount dat is geverifieerd met behulp van de Azure Cosmos DB-sleutel (de dummy in het voorgaande voorbeeld). Vervolgens hebben we toegang tot de analytische opslag van de Ecdc container in de West US 2 regio. Omdat er geen projectie van specifieke eigenschappen is, retourneert de OPENROWSET functie alle eigenschappen uit de Azure Cosmos DB-items.

Ervan uitgaande dat de items in de Azure Cosmos DB-container de date_rep, cases, en geo_id eigenschappen hebben, worden de resultaten van deze query weergegeven in de volgende tabel:

Als u gegevens uit de andere container in dezelfde Azure Cosmos DB-database wilt verkennen, kunt u dezelfde verbindingsreeks gebruiken en verwijzen naar de vereiste container als de derde parameter:

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

Schema expliciet opgeven

Hoewel de mogelijkheid OPENROWSET voor automatische schemadeductie een eenvoudige en gebruiksvriendelijke ervaring biedt, kan het nodig zijn om in uw bedrijfsscenario's expliciet het schema op te geven om relevante eigenschappen van de Azure Cosmos DB-gegevens te lezen.

Met OPENROWSET de functie kunt u expliciet opgeven welke eigenschappen u wilt lezen uit de gegevens in de container en de bijbehorende gegevenstypen opgeven.

Stel dat we enkele gegevens uit de ECDC COVID-gegevensset hebben geïmporteerd met de volgende structuur in 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"}

Deze platte JSON-documenten in Azure Cosmos DB kunnen worden weergegeven als een set rijen en kolommen in Synapse SQL. Met OPENROWSET de functie kunt u een subset van eigenschappen opgeven die u wilt lezen en de exacte kolomtypen in de WITH component:

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
   

Het resultaat van deze query kan er als volgt uitzien:

Bekijk Azure Cosmos DB naar SQL-type toewijzingen aan het einde van dit artikel voor meer informatie over de SQL-typen die moeten worden gebruikt voor Azure Cosmos DB-waarden.

Weergave maken

Het maken van weergaven in de master of standaarddatabases wordt niet aanbevolen of ondersteund. U moet dus een gebruikersdatabase maken voor uw weergaven.

Zodra u het schema hebt geïdentificeerd, kunt u een weergave voorbereiden op uw Azure Cosmos DB-gegevens. U moet uw Azure Cosmos DB-accountsleutel in een afzonderlijke toegangsreferentie plaatsen en naar deze toegangsreferentie verwijzen vanuit de OPENROWSET functie. Bewaar uw accountsleutel niet in de weergavedefinitie.

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

Gebruik dit niet OPENROWSET zonder expliciet gedefinieerd schema, omdat dit van invloed kan zijn op uw prestaties. Zorg ervoor dat u de kleinste grootten voor uw kolommen gebruikt (bijvoorbeeld VARCHAR(100) in plaats van standaard VARCHAR(8000)). Om een UTF-8-conversieprobleem te voorkomen, gebruik een UTF-8-sortering als standaarddatabasesortering of stel deze in als expliciete kolomsortering. Sortering Latin1_General_100_BIN2_UTF8 biedt de beste prestaties wanneer u gegevens filtert met behulp van sommige tekstkolommen.

Wanneer u een query op de weergave uitvoert, kunnen er fouten of onverwachte resultaten optreden. De weergave verwijst, verwijst naar kolommen of objecten is waarschijnlijk gewijzigd of bestaat niet meer. U moet de weergavedefinitie handmatig aanpassen om af te stemmen op de onderliggende schemawijzigingen. Houd er rekening mee dat dit kan gebeuren bij het gebruik van automatische schemadeductie in de weergave en wanneer u het schema expliciet opgeeft.

Geneste objecten opvragen

Met Azure Cosmos DB kunt u complexere gegevensmodellen vertegenwoordigen door ze op te stellen als geneste structuren of reeksen. De functie voor automatisch synchroniseren van Azure Synapse Link voor Azure Cosmos DB beheert de schemaweergave in de analytische opslag, waaronder het verwerken van geneste gegevenstypen die uitgebreide query's vanuit de serverloze SQL-pool mogelijk maken.

De gegevensset CORD-19 bevat bijvoorbeeld JSON-documenten die deze structuur volgen:

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

De geneste objecten en matrices in Azure Cosmos DB worden weergegeven als JSON-tekenreeksen in het queryresultaat wanneer de OPENROWSET functie ze leest. U kunt de paden naar geneste waarden in de objecten opgeven wanneer u de WITH component gebruikt:

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;

Het resultaat van deze query kan er als volgt uitzien:

Zie Complexe gegevenstypen analyseren in Azure Synapse Analytics of geneste typen query's uitvoeren in Parquet- en JSON-bestanden met behulp van een serverloze SQL-pool voor meer informatie.

Geneste matrices plat maken

Azure Cosmos DB-gegevens hebben mogelijk geneste submatrices, zoals de matrix van de auteur uit een CORD-19-gegevensset :

{
    "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 sommige gevallen moet u mogelijk de eigenschappen uit het bovenste item (metagegevens) samenvoegen met alle elementen van de matrix (auteurs). Met een serverloze SQL-pool kunt u geneste structuren plat maken door de OPENJSON functie toe te passen op de geneste matrix:

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

Het resultaat van deze query kan er als volgt uitzien:

Toewijzingen van Azure Cosmos DB naar SQL-typen

Hoewel transactionele opslag van Azure Cosmos DB schemaneutraal is, wordt de analytische opslag geschematiseerd om te optimaliseren voor analytische queryprestaties. Met de autosynchronisatiefunctie van Azure Synapse Link beheert Azure Cosmos DB standaard de schemarepresentatie in de analytische opslag, waaronder het verwerken van geneste gegevenstypen. Omdat een serverloze SQL-pool query's op de analytische opslag uitgeeft, is het belangrijk om te begrijpen hoe u Invoergegevenstypen van Azure Cosmos DB kunt toewijzen aan SQL-gegevenstypen.

Azure Cosmos DB-accounts van SQL (Core) API ondersteunen JSON-eigenschapstypen van getal, tekenreeks, Booleaanse waarde, null, geneste objecten of matrix. U zou SQL-typen moeten kiezen die overeenkomen met deze JSON-typen als u de WITH clausule in OPENROWSET gebruikt. In de volgende tabel ziet u de SQL-kolomtypen die moeten worden gebruikt voor verschillende eigenschapstypen in Azure Cosmos DB.

Schema voor volledige betrouwbaarheid

Het Azure Cosmos DB-schema voor volledige betrouwbaarheid registreert zowel waarden als de beste overeenkomsttypen voor elke eigenschap in een container. De OPENROWSET functie in een container met een schema voor volledige betrouwbaarheid biedt zowel het type als de werkelijke waarde in elke cel. Stel dat met de volgende query de items uit een container met een volledig betrouwbaarheidsschema worden gelezen:

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

Het resultaat van deze query retourneert typen en waarden die zijn opgemaakt als JSON-tekst:

Voor elke waarde ziet u het type dat is geïdentificeerd in een Azure Cosmos DB-containeritem. De meeste waarden voor de date_rep eigenschap bevatten date waarden, maar sommige waarden worden onjuist opgeslagen als tekenreeksen in Azure Cosmos DB. Het schema voor volledige betrouwbaarheid retourneert zowel correct getypte date waarden als onjuist opgemaakte string waarden.

Het aantal gevallen wordt opgeslagen als een int32 waarde, maar er is één waarde die wordt ingevoerd als een decimaal getal. Deze waarde heeft het float64 type. Als er een aantal waarden zijn die het grootste int32 getal overschrijden, worden ze opgeslagen als het int64 type. Alle geo_id waarden in dit voorbeeld worden opgeslagen als string typen.

Als u query's wilt uitvoeren op Azure Cosmos DB voor MongoDB-accounts, vindt u meer informatie over de schemaweergave van volledige betrouwbaarheid in de analytische opslag en de uitgebreide eigenschapsnamen die moeten worden gebruikt in Wat is analytische opslag van Azure Cosmos DB?

Query's uitvoeren op items met een schema met volledige betrouwbaarheid

Tijdens het uitvoeren van query's op het schema voor volledige betrouwbaarheid moet u expliciet het SQL-type en het verwachte Azure Cosmos DB-eigenschapstype in de WITH component opgeven.

In het volgende voorbeeld wordt ervan uitgegaan dat dit string het juiste type voor de geo_id eigenschap is en int32 het juiste type is voor de cases eigenschap:

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

Waarden voor geo_id en cases met andere typen worden geretourneerd als NULL waarden. Deze query verwijst alleen naar het type dat is opgegeven in de expressie (cases).

Als u waarden hebt met andere typen (cases.int64, cases.float64) die niet kunnen worden opgeschoond in een Azure Cosmos DB-container, moet u er expliciet naar verwijzen in een WITH component en de resultaten combineren. Met de volgende query worden zowel int32, int64als float64 opgeslagen in de cases kolom samengevoegd:

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 dit voorbeeld wordt het aantal gevallen opgeslagen als int32, int64of float64 waarden. Alle waarden moeten worden geëxtraheerd om het aantal gevallen per land of regio te berekenen.

Probleemoplossingsproces

Bekijk de zelfhulppagina om de bekende problemen of stappen voor probleemoplossing te vinden waarmee u potentiële problemen met Azure Cosmos DB-query's kunt oplossen.

Gerelateerde inhoud

• Power BI en serverloze Synapse SQL-pool gebruiken om Azure Cosmos DB-gegevens te analyseren
• Weergaven maken en gebruiken met behulp van een serverloze SQL-pool
• Zelfstudie: Data Lakes verkennen en analyseren met een serverloze SQL-pool
• Als u fouten krijgt of prestatieproblemen ondervindt, raadpleegt u Problemen met een serverloze SQL-pool oplossen
• Module: Azure Synapse Link implementeren met Azure Cosmos DB