Fråga Azure Cosmos DB-data med en serverlös SQL-pool i Azure Synapse Link

  • Artikel

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 T-SQL-syntax 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 läser data från Azure Cosmos DB tillsammans med data i Azure Blob Storage eller Azure Data Lake Storage med hjälp av skapa extern tabell som välj (CETAS). Du kan för närvarande inte lagra serverlösa SQL-poolfrågeresultat till Azure Cosmos DB med hjälp av CETAS.

I den här artikeln får du lära dig hur du skriver en fråga med en serverlös SQL-pool som kör frågor mot 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 självstudien används en container med ett väldefinierat Azure Cosmos DB-schema. Du kan också kolla in Learn-modulen om hur du kör frågor mot Azure Cosmos DB med SQL Serverless för Azure Synapse Analytics

Förutsättningar

  • Kontrollera att du har förberett analysarkivet:
  • 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 (större än 80 GB) bör du överväga att använda cachelagringslager som 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 till exempel inte VARCHAR(1000) om du vet att egenskapen har upp till 5 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 med infogad nyckel. Den här syntaxen kan användas för att köra frågor mot Azure Cosmos DB-samlingar utan att behöva förbereda autentiseringsuppgifter.
  • OPENROWSET som refererade till autentiseringsuppgifter som innehåller Azure Cosmos DB-kontonyckeln. Den här syntaxen kan användas för att skapa vyer i Azure Cosmos DB-samlingar.

För att stödja frågekörning och analys av data i ett Azure Cosmos DB-analysarkiv används en serverlös SQL-pool. Den serverlösa SQL-poolen använder OPENROWSET SQL-syntaxen, så du måste först konvertera din Azure Cosmos DB-anslutningssträng till det här formatet:

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

SQL-anslutningssträng för Azure Cosmos DB anger Azure Cosmos DB-kontonamnet, databasnamnet, huvudnyckeln för databaskontot och ett valfritt regionnamn för OPENROWSET funktionen. En del av den här informationen kan hämtas från Standard Azure Cosmos DB-anslutningssträng.

Konvertera från standardformatet för Azure Cosmos DB anslutningssträng:

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

SQL-anslutningssträng har följande format:

'account=<database account name>;database=<database name>;region=<region name>;key=<database account master key>'

Regionen är valfri. Om den utelämnas används containerns primära region.

Viktigt!

Det finns en annan valfri parameter i anslutningssträng som heter endpoint. Param endpoint krävs för konton som inte matchar standardformatet *.documents.azure.com . Om ditt Azure CosmosDB-konto till exempel slutar med .documents.azure.uskontrollerar du att du lägger till endpoint=<account name>.documents.azure.us i anslutningssträng.

Azure Cosmos DB-containernamnet anges utan citattecken i syntaxen OPENROWSET . Om containernamnet innehåller specialtecken, till exempel ett bindestreck (-), ska namnet omslutas inom hakparenteser ([]) i syntaxen OPENROWSET .

Viktigt!

Kontrollera att du använder viss UTF-8-databassortering, Latin1_General_100_CI_AS_SC_UTF8till exempel , eftersom strängvärden i ett Azure Cosmos DB-analysarkiv kodas som UTF-8-text. Ett matchningsfel mellan textkodning i filen och sortering kan orsaka oväntade textkonverteringsfel. Du kan enkelt ändra standardsortering av den aktuella databasen med hjälp av T-SQL-instruktionen alter database current collate Latin1_General_100_CI_AI_SC_UTF8.

Kommentar

En serverlös SQL-pool stöder inte frågor mot ett Azure Cosmos DB-transaktionslager.

Exempeldatauppsättning

Exemplen i den här artikeln baseras på data från European Centre for Disease Prevention and Control (ECDC) COVID-19 Cases och COVID-19 Open Research Dataset (CORD-19), doi:10.5281/zenodo.3715505.

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.

Du kan använda följande anslutningssträng i testsyfte: Account=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==. 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 WITH utelämna satsen från -instruktionen OPENROWSET kan du instruera den serverlösa SQL-poolen att automatiskt identifiera (härled) schemat för analysarkivet för Azure Cosmos DB-containern.

SELECT TOP 10 *
FROM OPENROWSET( 
       'CosmosDB',
       'Account=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==',
       Ecdc) as documents

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). Sedan kom Ecdc vi åt containerns analysarkiv i West US 2 regionen. 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:

date_rep cases geo_id
2020-08-13 254 RS
2020-08-12 235 RS
2020-08-11 163 RS

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=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==',
       Cord19) as cord19

Ange uttryckligen schema

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

Med OPENROWSET funktionen kan du uttryckligen ange vilka egenskaper 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=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==',
       Ecdc
    ) 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:

date_rep cases geo_id
2020-08-13 254 RS
2020-08-12 235 RS
2020-08-11 163 RS

Mer information om de SQL-typer som ska användas för Azure Cosmos DB-värden finns i reglerna för SQL-typmappningar i slutet av 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 = 's5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==';
GO
CREATE OR ALTER VIEW Ecdc
AS SELECT *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=synapselink-cosmosdb-sqlsample;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 standardVARCHAR(8000)). Du bör använda viss UTF-8-sortering som standarddatabassortering eller ange den som explicit kolumnsortering för att undvika utf-8-konverteringsproblem. Sortering Latin1_General_100_BIN2_UTF8 ger bästa prestanda när du filtrerar data med hjälp av vissa strängkolumner.

När du frågar vyn kan det uppstå fel eller oväntade resultat. Det innebär förmodligen att vyn refererar till kolumner eller objekt som 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.

Fråga 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=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==',
       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:

paper_id rubrik metadata Författare
bb11206963e831f... Kompletterande information En ekoepidemi... {"title":"Supplementary Informati… [{"first":"Julien","last":"Mélade","suffix":"","af…
bb1206963e831f1... Användningen av konvalescent sera i Immun-E ... {"title":"The Use of Convalescent… [{"first":"Antonio","last":"Lavazza","suffix":"", …
bb378eca9aac649... Tylosema esculentum (Marama) Tuber och B... {"title":"Tylosema esculentum (Ma… [{"first":"Walter","last":"Chingwaru","suffix":"",…

Läs mer om att analysera komplexa datatyper som Parquet-filer och -containrar i Azure Synapse Link för Azure Cosmos DB eller kapslade strukturer i en serverlös SQL-pool.

Viktigt!

Om du ser oväntade tecken i texten som MÃÂ&copy;lade i stället för Mélade, är databassorteringen inte inställd på UTF-8-sortering . Ändra sortering av databasen till UTF-8-sortering med hjälp av en SQL-instruktion som ALTER DATABASE MyLdw COLLATE LATIN1_GENERAL_100_CI_AS_SC_UTF8.

Platta ut kapslade matriser

Azure Cosmos DB-data kan ha kapslade underrockar som författarens matris 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=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==',
       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:

rubrik Författare Första senaste Anslutningen
Kompletterande information En ekoepidemi... [{"first":"Julien","last":"Mélade","suffix":"","affiliation":{"laboratory":"Centre de Recher… Julien Mélade {"laboratory":"Centre de Recher…
Kompletterande information En ekoepidemi... [{"first":"Nicolas","last":"4#","suffix":"","affiliation":{"laboratory":"","institution":"U… Nicolas Nr 4 {"laboratory":"","institution":"U…
Kompletterande information En ekoepidemi... [{"first":"Beza","last":"Ramazindrazana","suffix":"","affiliation":{"laboratory":"Centre de Recher… Beza Ramazindrazana {"laboratory":"Centre de Recher…
Kompletterande information En ekoepidemi... [{"first":"Olivier","last":"Flores","suffix":"","affiliation":{"laboratory":"UMR C53 CIRAD, … Olivier Flores {"laboratory":"UMR C53 CIRAD, …

Viktigt!

Om du ser oväntade tecken i texten som MÃÂ&copy;lade i stället för Mélade, är databassorteringen inte inställd på UTF-8-sortering . Ändra sortering av databasen till UTF-8-sortering med hjälp av en SQL-instruktion som ALTER DATABASE MyLdw COLLATE LATIN1_GENERAL_100_CI_AS_SC_UTF8.

Azure Cosmos DB till SQL-typmappningar

Även om Azure Cosmos DB-transaktionsarkivet är schemaagnostiskt schemaförs analysarkivet för att optimera för analysfrågasprestanda. 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 frågar 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.

Egenskapstyp för Azure Cosmos DB SQL-kolumntyp
Booleskt bit
Integer bigint
Decimal flyttal
String varchar (UTF-8-databassortering)
Datumtid (ISO-formaterad sträng) varchar(30)
Datumtid (UNIX-tidsstämpel) bigint
Null any SQL type
Kapslat objekt eller matris varchar(max) (UTF-8-databassortering), serialiserad som JSON-text

Fullständigt återgivningsschema

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 fullständigt återgivningsschema:

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:

date_rep cases geo_id
{"date":"2020-08-13"} {"int32":"254"} {"sträng":"RS"}
{"date":"2020-08-12"} {"int32":"235"} {"sträng":"RS"}
{"date":"2020-08-11"} {"int32":"316"} {"sträng":"RS"}
{"date":"2020-08-10"} {"int32":"281"} {"sträng":"RS"}
{"date":"2020-08-09"} {"int32":"295"} {"sträng":"RS"}
{"sträng":"2020/08/08"} {"int32":"312"} {"sträng":"RS"}
{"date":"2020-08-07"} {"float64":"339.0"} {"sträng":"RS"}

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 är information som 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.

Viktigt!

Funktionen OPENROWSET utan en WITH sats exponerar både värden med förväntade typer och värden med felaktigt angivna typer. Den här funktionen är utformad för datautforskning och inte för rapportering. Parsa inte JSON-värden som returneras från den här funktionen för att skapa rapporter. Använd en explicit WITH-sats för att skapa dina rapporter. Du bör rensa de värden som har felaktiga typer i Azure Cosmos DB-containern för att tillämpa korrigeringar i det fullständiga analysarkivet för återgivning.

Om du vill fråga Azure Cosmos DB om MongoDB-konton kan du läsa mer om den fullständiga återgivningsschemarepresentationen i analysarkivet och de utökade egenskapsnamn 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 återgivningsschema 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 cases till 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, int64och float64 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/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.

Nästa steg

Mer information finns i följande artiklar: