Köra frågor mot Azure Cosmos DB-data med hjälp av en serverlös SQL-pool

Med en serverlös SQL-pool kan du analysera data i dina Azure Cosmos DB-containrar som är aktiverade med Azure Synapse Link nästan i realtid utan att påverka prestandan för dina transaktionsarbetsbelastningar. Den erbjuder en välbekant Transact-SQL-syntax (T-SQL) för att fråga efter data från analysarkivet och integrerad anslutning till en mängd olika business intelligence-verktyg (BI) och ad hoc-frågeverktyg via T-SQL-gränssnittet.

För att köra frågor mot Azure Cosmos DB stöds hela SELECT-ytan via funktionen OPENROWSET, som innehåller de flesta SQL-funktioner och -operatorer. Du kan också lagra resultatet av frågan som hämtar data från Azure Cosmos DB tillsammans med data i Azure Blob Storage eller Azure Data Lake Storage med hjälp av 'create external table as select' (CETAS). Du kan för närvarande inte lagra serverlösa SQL-poolfrågeresultat till Azure Cosmos DB med hjälp av CETAS.

Den här artikeln beskriver hur du skriver en fråga med en serverlös SQL-pool som frågar efter data från Azure Cosmos DB-containrar som är aktiverade med Azure Synapse Link. Du kan sedan lära dig mer om att skapa serverlösa SQL-poolvyer över Azure Cosmos DB-containrar och ansluta dem till Power BI-modeller i den här självstudien. I den här handledningen används en container med ett väldefinierat schema för Azure Cosmos DB. Du kan också kolla in Learn-modulen om hur du frågar Azure Cosmos DB med SQL Serverless i Azure Synapse Analytics.

Förutsättningar

• Se till att du förbereder det analytiska lagret:
  • Aktivera analysarkiv på dina Azure Cosmos DB-containrar.
  • Hämta anslutningssträng med en skrivskyddad nyckel som du kan använda för att söka i analytiskt lager.
  • Hämta den skrivskyddade nyckel som ska användas för att komma åt Azure Cosmos DB-containern.
• Kontrollera att du har tillämpat alla metodtips, till exempel:
  • Kontrollera att azure Cosmos DB-analyslagringen finns i samma region som en serverlös SQL-pool.
  • Kontrollera att klientprogrammet (Power BI, Analysis Service) finns i samma region som en serverlös SQL-pool.
  • Om du returnerar en stor mängd data (mer än 80 GB) bör du överväga att använda cachelagringslagret, till exempel Analysis Services, och läsa in partitionerna som är mindre än 80 GB i Analysis Services-modellen.
  • Om du filtrerar data med hjälp av strängkolumner kontrollerar du att du använder OPENROWSET funktionen med den explicita WITH satsen som har de minsta möjliga typerna. Använd VARCHAR(1000) till exempel inte om du vet att egenskapen har upp till fem tecken.

Översikt

Med en serverlös SQL-pool kan du köra frågor mot Azure Cosmos DB-analyslagring med hjälp av OPENROWSET funktionen .

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

SQL-anslutningssträngen för Azure Cosmos DB innehåller följande komponenter:

• account – namnet på det Azure Cosmos DB-konto som du riktar in dig på.
• database – Containernamnet, som anges utan citattecken i OPENROWSET-syntaxen. Om containernamnet innehåller specialtecken (till exempel ett bindestreck -) bör det omges av hakparenteser ([]).
• region (valfritt) – Regionen för cosmos DB-analyslagringen. Om den utelämnas kommer containerns primära region att användas.
• endpoint (valfritt) – Den Cosmos DB-slutpunkts-URI (till exempel https://<account name>.documents.azure.us) som krävs om ditt Cosmos DB-konto inte följer standardformatet *.documents.azure.com .

Dessa egenskaper kan identifieras från standardanslutningssträngen för Cosmos DB, till exempel:

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

SQL-anslutningssträngen kan formateras på följande sätt:

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

Den här anslutningssträngen innehåller inte den autentiseringsinformation som krävs för att ansluta till Cosmos DB-analyslagringen. Ytterligare information krävs beroende på vilken typ av autentisering som används:

• Om du använder arbetsytehanterad identitet för att få åtkomst till analyslagret OPENROWSET, bör du lägga till egenskapen AuthType.
• Om OPENROWSET du använder en infogad kontonyckel bör du lägga till egenskapen key . På så sätt kan du köra frågor mot Azure Cosmos DB-samlingar utan att behöva förbereda autentiseringsuppgifter.
• I stället för att inkludera autentiseringsinformation i anslutningssträngen OPENROWSET kan du referera till en autentiseringsuppgift som innehåller Azure Cosmos DB-kontonyckeln. Den här metoden kan användas för att skapa vyer i Azure Cosmos DB-samlingar.

De här alternativen beskrivs nedan.

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>

Exempeldatauppsättning

Exemplen i den här artikeln baseras på data från European Center for Disease Prevention and Control (ECDC) COVID-19 Cases och COVID-19 Open Research Dataset (CORD-19).

Du kan se licensen och strukturen för data på dessa sidor. Du kan också ladda ned exempeldata för ECDC - och CORD-19-datauppsättningarna.

Om du vill följa med i den här artikeln som visar hur du kör frågor mot Azure Cosmos DB-data med en serverlös SQL-pool måste du skapa följande resurser:

• Ett Azure Cosmos DB-databaskonto som är Azure Synapse Link aktiverat
• En Azure Cosmos DB-databas med namnet covid
• Två Azure Cosmos DB-containrar med namnet Ecdc och Cord19 inlästa med föregående exempeldatauppsättningar

Observera att den här anslutningen inte garanterar prestanda eftersom det här kontot kan finnas i en fjärrregion jämfört med synapse SQL-slutpunkten.

Utforska Azure Cosmos DB-data med automatisk schemainferens

Det enklaste sättet att utforska data i Azure Cosmos DB är att använda funktionen för automatisk schemainferens. Genom att utelämna WITH klausulen från OPENROWSET satsen kan du instruera den serverlösa SQL-poolen att autodetektera (härleda) schemat för den analytiska lagringen av Azure Cosmos DB-containern.

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

I föregående exempel instruerade vi den serverlösa SQL-poolen att ansluta till covid databasen i Azure Cosmos DB-kontot MyCosmosDbAccount som autentiserats med hjälp av Azure Cosmos DB-nyckeln (dummy i föregående exempel). Därefter fick vi åtkomst till Ecdc-behållarens analytiska lagring i regionen West US 2. Eftersom det inte finns någon projektion av specifika egenskaper OPENROWSET returnerar funktionen alla egenskaper från Azure Cosmos DB-objekten.

Förutsatt att objekten i Azure Cosmos DB-containern har date_repegenskaperna , casesoch geo_id visas resultatet av den här frågan i följande tabell:

Om du behöver utforska data från den andra containern i samma Azure Cosmos DB-databas kan du använda samma anslutningssträng och referera till den nödvändiga containern som den tredje parametern:

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

Ange uttryckligen schema

Även om funktionen för automatisk schemainferens i OPENROWSET ger en enkel och lättanvänd upplevelse, kan dina affärsscenarier kräva att du uttryckligen anger schemat för att endast läsa relevanta egenskaper från Azure Cosmos DB-data.

Med OPENROWSET funktionen kan du uttryckligen ange vilka egenskaper som du vill läsa från data i containern och ange deras datatyper.

Anta att vi har importerat vissa data från ECDC COVID-datauppsättningen med följande struktur till 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"}

Dessa platta JSON-dokument i Azure Cosmos DB kan representeras som en uppsättning rader och kolumner i Synapse SQL. Med OPENROWSET funktionen kan du ange en delmängd av egenskaper som du vill läsa och de exakta kolumntyperna WITH i -satsen:

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
   

Resultatet av den här frågan kan se ut som i följande tabell:

Mer information om de SQL-typer som ska användas för Azure Cosmos DB-värden finns i Azure Cosmos DB till SQL-typmappningar i slutet av den här artikeln.

Skapa vy

Att skapa vyer i master eller standarddatabaser rekommenderas inte eller stöds inte. Därför måste du skapa en användardatabas för dina vyer.

När du har identifierat schemat kan du förbereda en vy ovanpå dina Azure Cosmos DB-data. Du bör placera din Azure Cosmos DB-kontonyckel i en separat autentiseringsuppgift och referera till den här autentiseringsuppgiften från OPENROWSET funktionen. Behåll inte kontonyckeln i vydefinitionen.

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

Använd inte OPENROWSET utan explicit definierat schema eftersom det kan påverka dina prestanda. Se till att du använder de minsta möjliga storlekarna för dina kolumner (till exempel VARCHAR(100) i stället för standard VARCHAR(8000)). Du bör använda viss UTF-8-sortering som standarddatabassortering eller ange den som explicit kolumnsortering för att undvika ett UTF-8-konverteringsproblem. Teckenkodning Latin1_General_100_BIN2_UTF8 ger bästa prestanda när du filtrerar data med hjälp av vissa strängkolumner.

När du gör en förfrågan mot vyn kan det uppstå fel eller oväntade resultat. Vyn refererar till kolumner eller objekt som förmodligen har ändrats eller inte längre finns. Du måste justera vydefinitionen manuellt så att den överensstämmer med de underliggande schemaändringarna. Tänk på att detta kan inträffa både när du använder automatisk schemainferens i vyn och när du uttryckligen anger schemat.

Hämta kapslade objekt

Med Azure Cosmos DB kan du representera mer komplexa datamodeller genom att skapa dem som kapslade objekt eller matriser. Funktionen autosynkronisering i Azure Synapse Link för Azure Cosmos DB hanterar schemarepresentationen i analysarkivet, vilket inkluderar hantering av kapslade datatyper som möjliggör omfattande frågor från den serverlösa SQL-poolen.

Till exempel har CORD-19-datauppsättningen JSON-dokument som följer den här strukturen:

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

Kapslade objekt och matriser i Azure Cosmos DB representeras som JSON-strängar i frågeresultatet OPENROWSET när funktionen läser dem. Du kan ange sökvägarna till kapslade värden i objekten när du använder WITH -satsen:

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;

Resultatet av den här frågan kan se ut som i följande tabell:

Mer information finns i Analysera komplexa datatyper i Azure Synapse Analytics eller Fråga kapslade typer i Parquet- och JSON-filer med hjälp av en serverlös SQL-pool.

Platta ut kapslade matriser

Azure Cosmos DB-data kan ha kapslade undermatriser såsom författarens array från en CORD-19-datauppsättning:

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

I vissa fall kan du behöva koppla egenskaperna från det översta objektet (metadata) med alla element i matrisen (författare). Med en serverlös SQL-pool kan du platta ut kapslade strukturer genom att använda OPENJSON funktionen på den kapslade matrisen:

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

Resultatet av den här frågan kan se ut som i följande tabell:

Azure Cosmos DB för SQL-typmappningar

Även om Azure Cosmos DB-transaktionslagret är schemaagnostiskt, är analyslagret schemalagt för att optimera analysfrågeprestanda. Med funktionen autosynkronisering i Azure Synapse Link hanterar Azure Cosmos DB schemarepresentationen i analysarkivet, vilket inkluderar hantering av kapslade datatyper. Eftersom en serverlös SQL-pool kör frågor mot analysarkivet är det viktigt att förstå hur du mappar indatatyper för Azure Cosmos DB till SQL-datatyper.

Azure Cosmos DB-konton för SQL -API (Core) stöder JSON-egenskapstyper för tal, sträng, boolesk, null, kapslat objekt eller matris. Du skulle behöva välja SQL-typer som matchar dessa JSON-typer om du använder WITH -satsen i OPENROWSET. I följande tabell visas de SQL-kolumntyper som ska användas för olika egenskapstyper i Azure Cosmos DB.

Fullständigt noggrannhetsschema

Azure Cosmos DB:s fullständiga återgivningsschema registrerar båda värdena och deras bästa matchningstyper för varje egenskap i en container. Funktionen OPENROWSET i en container med fullständigt återgivningsschema ger både typen och det faktiska värdet i varje cell. Anta att följande fråga läser objekten från en container med schema med fullständig noggrannhet.

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

Resultatet av den här frågan returnerar typer och värden som är formaterade som JSON-text:

För varje värde kan du se den typ som identifieras i ett Azure Cosmos DB-containerobjekt. De flesta värden för date_rep egenskapen innehåller date värden, men vissa av dem lagras felaktigt som strängar i Azure Cosmos DB. Fullständigt återgivningsschema returnerar både korrekt inskrivna date värden och felaktigt formaterade string värden.

Antalet fall lagras som ett int32 värde, men det finns ett värde som anges som ett decimaltal. Det här värdet har float64 typen . Om det finns några värden som överskrider det största int32 talet lagras de som int64 typ. Alla geo_id värden i det här exemplet lagras som string typer.

För att göra förfrågningar mot Azure Cosmos DB för MongoDB-konton kan du läsa mer om den fullständiga schemarepresentationen i analyslagret och de utökade egenskapsnamnen som ska användas i Vad är Azure Cosmos DB Analytical Store?.

Fråga efter objekt med fullständigt återgivningsschema

När du kör frågor mot fullständigt detaljschema måste du uttryckligen ange SQL-typen och den förväntade Azure Cosmos DB-egenskapstypen i WITH-satsen.

I följande exempel antar vi att det string är rätt typ för geo_id egenskapen och int32 är rätt typ för egenskapen cases :

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

Värden för geo_id och cases som har andra typer returneras som NULL värden. Den här frågan refererar endast till cases med den angivna typen i uttrycket (cases.int32).

Om du har värden med andra typer (cases.int64, cases.float64) som inte kan rensas i en Azure Cosmos DB-container måste du uttryckligen referera till dem i en WITH -sats och kombinera resultaten. Följande fråga aggregerar både int32, int64 och float64 som lagras i cases-kolumnen.

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

I det här exemplet lagras antalet fall antingen som int32, int64eller float64 värden. Alla värden måste extraheras för att beräkna antalet ärenden per land eller region.

Felsökning

Gå igenom självhjälpssidan för att hitta kända problem eller felsökningssteg som kan hjälpa dig att lösa potentiella problem med Azure Cosmos DB-frågor.

Relaterat innehåll

• Använda Power BI och en serverlös Synapse SQL-pool för att analysera Azure Cosmos DB-data
• Skapa och använda vyer med hjälp av en serverlös SQL-pool
• Självstudie: Utforska och analysera datasjöar med en serverlös SQL-pool
• Om du får fel eller prestandaproblem kan du läsa Felsök serverlös SQL-pool
• Modul: Implementera Azure Synapse Link med Azure Cosmos DB