Dotazování dat azure Cosmos DB pomocí bezserverového fondu SQL ve službě Azure Synapse Link

Bezserverový fond SQL umožňuje analyzovat data v kontejnerech Služby Azure Cosmos DB, které jsou povoleny pomocí služby Azure Synapse Link téměř v reálném čase, aniž by to ovlivnilo výkon transakčních úloh. Nabízí známou syntaxi T-SQL pro dotazování dat z analytického úložiště a integrované připojení k široké škále nástrojů pro business intelligence (BI) a ad hoc dotazování prostřednictvím rozhraní T-SQL.

Pro dotazování služby Azure Cosmos DB je úplná plocha SELECT podporovaná prostřednictvím funkce OPENROWSET , která zahrnuje většinu funkcí a operátorů SQL. Výsledky dotazu, který čte data ze služby Azure Cosmos DB, můžete také uložit spolu s daty ve službě Azure Blob Storage nebo Azure Data Lake Storage pomocí příkazu Select (CETAS). V současné době nemůžete ukládat výsledky dotazů bez serveru sql do služby Azure Cosmos DB pomocí CETAS.

V tomto článku se dozvíte, jak napsat dotaz s bezserverovým fondem SQL, který bude dotazovat data z kontejnerů Azure Cosmos DB, které jsou povolené pomocí Azure Synapse Linku. V tomto kurzu se pak můžete dozvědět více o vytváření zobrazení bezserverového fondu SQL v kontejnerech Azure Cosmos DB a jejich připojení k modelům Power BI. Tento kurz používá kontejner s dobře definovaným schématem služby Azure Cosmos DB. Můžete se také podívat na modul Learn o dotazování služby Azure Cosmos DB pomocí SQL Serverless pro Azure Synapse Analytics.

Požadavky

• Ujistěte se, že jste připravili analytické úložiště:
  • Povolte analytické úložiště v kontejnerech Azure Cosmos DB.
  • Získejte připojovací řetězec klíčem jen pro čtení, který použijete k dotazování analytického úložiště.
  • Získání klíče jen pro čtení, který se použije pro přístup ke kontejneru Azure Cosmos DB
• Ujistěte se, že jste použili všechny osvědčené postupy, například:
  • Ujistěte se, že vaše analytické úložiště Azure Cosmos DB je ve stejné oblasti jako bezserverový fond SQL.
  • Ujistěte se, že klientská aplikace (Power BI, Analysis service) je ve stejné oblasti jako bezserverový fond SQL.
  • Pokud vracíte velké množství dat (větší než 80 GB), zvažte použití vrstvy ukládání do mezipaměti, jako je Analysis Services, a načtení oddílů menších než 80 GB v modelu Analysis Services.
  • Pokud filtrujete data pomocí řetězcových sloupců, ujistěte se, že používáte OPENROWSET funkci s explicitní WITH klauzulí, která má nejmenší možné typy (například nepoužívejte VARCHAR(1000), pokud víte, že vlastnost má až 5 znaků).

Přehled

Bezserverový fond SQL umožňuje dotazovat analytické úložiště Azure Cosmos DB pomocí OPENROWSET funkce.

• OPENROWSET s vloženým klíčem. Tuto syntaxi můžete použít k dotazování kolekcí Azure Cosmos DB bez nutnosti přípravy přihlašovacích údajů.
• OPENROWSET které odkazovaly na přihlašovací údaje, které obsahují klíč účtu služby Azure Cosmos DB. Tuto syntaxi můžete použít k vytváření zobrazení v kolekcích Azure Cosmos DB.

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

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

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

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>'

Ukázková datová sada

Příklady v tomto článku vycházejí z dat Evropského střediska pro prevenci a kontrolu nemocí (ECDC) COVID-19 a otevřených výzkumných datových sad COVID-19 (CORD-19), doi:10.5281/zenodo.3715505.

Na těchto stránkách můžete zobrazit licenci a strukturu dat. Můžete si také stáhnout ukázková data pro datové sady ECDC a CORD-19.

Pokud chcete postupovat podle tohoto článku, který ukazuje, jak dotazovat data Azure Cosmos DB s bezserverovým fondem SQL, ujistěte se, že vytvoříte následující prostředky:

• Účet databáze Azure Cosmos DB, který je povolený službou Azure Synapse Link.
• Databáze Azure Cosmos DB s názvem covid.
• Dva kontejnery Azure Cosmos DB pojmenované Ecdc a Cord19 načtené s předchozími ukázkovými datovými sadami

Pro účely testování můžete použít následující připojovací řetězec: Account=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==. Upozorňujeme, že toto připojení nezaručuje výkon, protože tento účet může být ve vzdálené oblasti ve srovnání s koncovým bodem Synapse SQL.

Prozkoumání dat služby Azure Cosmos DB pomocí automatického odvozování schématu

Nejjednodušší způsob, jak prozkoumat data ve službě Azure Cosmos DB, je použití funkce automatického odvozování schématu. Vynecháním WITH klauzule z OPENROWSET příkazu můžete dát bezserverový fond SQL pokyn k automatickému rozpoznávání schématu analytického úložiště kontejneru Azure Cosmos DB.

SELECT TOP 10 *
FROM OPENROWSET( 
       'CosmosDB',
       'Account=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==',
       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 = 's5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==';
*/
SELECT TOP 10 *
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

V předchozím příkladu jsme instruovali bezserverový fond SQL, aby se připojil k covid databázi v účtu MyCosmosDbAccount azure Cosmos DB ověřeném pomocí klíče služby Azure Cosmos DB (fiktivní v předchozím příkladu). Pak jsme přistupovali k analytickému Ecdc úložišti kontejneru West US 2 v oblasti. Vzhledem k tomu, že neexistuje projekce konkrétních vlastností, OPENROWSET vrátí funkce všechny vlastnosti z položek Azure Cosmos DB.

Za předpokladu, že položky v kontejneru Azure Cosmos DB mají date_repa casesgeo_id vlastnosti, zobrazí se výsledky tohoto dotazu v následující tabulce:

Pokud potřebujete prozkoumat data z druhého kontejneru ve stejné databázi Azure Cosmos DB, můžete použít stejný připojovací řetězec a odkazovat na požadovaný kontejner jako třetí parametr:

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

Explicitní zadání schématu

I když funkce OPENROWSET automatického odvozování schématu poskytuje jednoduché snadno použitelné dotazy, obchodní scénáře můžou vyžadovat explicitní zadání schématu pro relevantní vlastnosti jen pro čtení z dat azure Cosmos DB.

Funkce OPENROWSET umožňuje explicitně určit, které vlastnosti chcete číst z dat v kontejneru, a určit jejich datové typy.

Představme si, že jsme do služby Azure Cosmos DB naimportovali nějaká data z datové sady ECDC COVID s následující strukturou:

{"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"}

Tyto ploché dokumenty JSON ve službě Azure Cosmos DB je možné reprezentovat jako sadu řádků a sloupců ve službě Synapse SQL. Funkce OPENROWSET umožňuje zadat podmnožinu vlastností, které chcete číst, a přesné typy sloupců v klauzuli WITH :

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

/*  Setup - create server-level or database scoped credential with Azure Cosmos DB account key:
    CREATE CREDENTIAL MyCosmosDbAccountCredential
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 's5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==';
*/
SELECT TOP 10 *
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
   

Výsledek tohoto dotazu může vypadat jako v následující tabulce:

Další informace o typech SQL, které by se měly použít pro hodnoty služby Azure Cosmos DB, najdete v pravidlech pro mapování typů SQL na konci článku.

Vytvořit zobrazení

Vytváření zobrazení v master nebo výchozích databázích se nedoporučuje ani nepodporuje. Proto potřebujete vytvořit uživatelskou databázi pro svá zobrazení.

Jakmile schéma identifikujete, můžete připravit zobrazení nad daty Azure Cosmos DB. Klíč účtu služby Azure Cosmos DB byste měli umístit do samostatných přihlašovacích údajů a odkazovat na tyto přihlašovací údaje od OPENROWSET funkce. Neuchovávejte klíč účtu v definici zobrazení.

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

Nepoužívejte OPENROWSET bez explicitně definovaného schématu, protože může mít vliv na výkon. Ujistěte se, že pro sloupce používáte nejmenší možné velikosti (například VARCHAR(100) místo výchozího VARCHAR(8000)). Jako výchozí kolaci databáze byste měli použít určitou kolaci UTF-8 nebo ji nastavit jako explicitní kolaci sloupců, abyste se vyhnuli problému s převodem UTF-8. Kolace Latin1_General_100_BIN2_UTF8 poskytuje nejlepší výkon při filtrování dat pomocí některých řetězců sloupců.

Při dotazování zobrazení může dojít k chybám nebo neočekávaným výsledkům. To pravděpodobně znamená, že zobrazení odkazuje na sloupce nebo objekty, které byly změněny nebo již neexistují. Definici zobrazení je potřeba upravit ručně tak, aby odpovídala změnám základního schématu. Mějte na paměti, že k tomu může dojít jak při použití automatického odvozování schématu v zobrazení, tak při explicitní specifikaci schématu.

Dotazování vnořených objektů

Se službou Azure Cosmos DB můžete reprezentovat složitější datové modely tím, že je vytvoříte jako vnořené objekty nebo pole. Funkce automatické synchronizace služby Azure Synapse Link pro Azure Cosmos DB spravuje reprezentaci schématu v analytickém úložišti, která zahrnuje zpracování vnořených datových typů, které umožňují bohaté dotazování z bezserverového fondu SQL.

Například datová sada CORD-19 obsahuje dokumenty JSON, které se řídí touto strukturou:

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

Vnořené objekty a pole ve službě Azure Cosmos DB jsou ve výsledku dotazu reprezentovány jako řetězce JSON, když je OPENROWSET funkce přečte. Při použití WITH klauzule můžete zadat cesty k vnořeným hodnotám v objektech:

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;

Výsledek tohoto dotazu může vypadat jako v následující tabulce:

Přečtěte si další informace o analýze složitých datových typů, jako jsou soubory Parquet a kontejnery ve službě Azure Synapse Link pro službu Azure Cosmos DB nebo vnořené struktury v bezserverovém fondu SQL.

Zploštěná vnořená pole

Data azure Cosmos DB můžou mít vnořené podarray, jako je pole autora z datové sady CORD-19 :

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

V některých případech může být potřeba vlastnosti "spojit" z horní položky (metadata) se všemi prvky pole (autory). Bezserverový fond SQL umožňuje zploštět vnořené struktury použitím OPENJSON funkce na vnořené pole:

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

Výsledek tohoto dotazu může vypadat jako v následující tabulce:

Mapování typů Azure Cosmos DB na SQL

I když je transakční úložiště Azure Cosmos DB nezávislé na schématu, analytické úložiště je schematizované pro optimalizaci výkonu analytických dotazů. Díky funkci automatické synchronizace služby Azure Synapse Link spravuje služba Azure Cosmos DB reprezentaci schématu v analytickém úložišti, která zahrnuje zpracování vnořených datových typů. Vzhledem k tomu, že bezserverový fond SQL dotazuje analytické úložiště, je důležité pochopit, jak mapovat vstupní datové typy Azure Cosmos DB na datové typy SQL.

Účty služby Azure Cosmos DB rozhraní SQL (Core) API podporují typy vlastností JSON typu číslo, řetězec, logická hodnota, null, vnořené objekty nebo pole. Pokud používáte WITH klauzuli v OPENROWSET, musíte zvolit typy SQL, které odpovídají těmto typům JSON. Následující tabulka ukazuje typy sloupců SQL, které by se měly používat pro různé typy vlastností ve službě Azure Cosmos DB.

Schéma úplné věrnosti

Schéma úplné věrnosti ve službě Azure Cosmos DB zaznamenává hodnoty i jejich nejlepší typy shody pro každou vlastnost v kontejneru. Funkce OPENROWSET v kontejneru s úplným schématem přesnosti poskytuje typ i skutečnou hodnotu v každé buňce. Předpokládejme, že následující dotaz čte položky z kontejneru s úplným schématem věrnosti:

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

Výsledek tohoto dotazu vrátí typy a hodnoty formátované jako text JSON:

Pro každou hodnotu uvidíte typ identifikovaný v položce kontejneru Azure Cosmos DB. Většina hodnot vlastnosti date_rep obsahuje date hodnoty, ale některé z nich jsou nesprávně uložené jako řetězce ve službě Azure Cosmos DB. Schéma úplné věrnosti vrátí správně zadané date hodnoty i nesprávně formátované string hodnoty. Počet případů jsou informace uložené jako int32 hodnota, ale existuje jedna hodnota, která je zadaná jako desetinné číslo. Tato hodnota má float64 typ. Pokud existují nějaké hodnoty, které překračují největší int32 číslo, budou uloženy jako int64 typ. Všechny geo_id hodnoty v tomto příkladu jsou uloženy jako string typy.

Pokud chcete dotazovat účty Azure Cosmos DB pro MongoDB, můžete se dozvědět více o úplném vyjádření schématu věrnosti v analytickém úložišti a o rozšířených názvech vlastností, které se mají použít v analytickém úložišti Azure Cosmos DB?.

Dotazování položek se schématem úplné věrnosti

Při dotazování schématu úplné věrnosti je nutné explicitně zadat typ SQL a očekávaný typ vlastnosti služby Azure Cosmos DB v klauzuli WITH .

V následujícím příkladu budeme předpokládat, že string je správný typ vlastnosti geo_id a int32 je správným typem cases vlastnosti:

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

Hodnoty pro geo_id a cases které mají jiné typy budou vráceny jako NULL hodnoty. Tento dotaz bude odkazovat pouze na cases zadaný typ ve výrazu (cases.int32).

Pokud máte hodnoty s jinými typy (cases.int64, cases.float64), které nelze vyčistit v kontejneru Azure Cosmos DB, budete je muset explicitně odkazovat v WITH klauzuli a kombinovat výsledky. Následující dotaz agreguje jak int32, tak int64float64 i uložené ve sloupcicases:

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

V tomto příkladu je počet případů uložen jako int32, int64nebo float64 hodnoty. Všechny hodnoty musí být extrahovány, aby se vypočítal počet případů na zemi/oblast.

Řešení problému

Na stránce samoobslužné pomoci najdete známé problémy nebo kroky pro řešení potíží, které vám můžou pomoct při řešení potenciálních problémů s dotazy azure Cosmos DB.

Další kroky

Další informace najdete v následujících článcích:

• Použití power BI a bezserverového fondu SQL se službou Azure Synapse Link
• Vytváření a používání zobrazení v bezserverovém fondu SQL
• Kurz vytváření zobrazení bezserverového fondu SQL ve službě Azure Cosmos DB a jejich připojení k modelům Power BI přes DirectQuery
• Pokud dochází k chybám nebo dochází k problémům s výkonem, navštivte stránku samoobslužné podpory služby Azure Synapse Link pro Službu Azure Cosmos DB.
• Projděte si modul Learn, jak dotazovat službu Azure Cosmos DB pomocí SQL Bezserverové služby pro Azure Synapse Analytics.