Fråga Cosmos DB med Synapse SQL

5 minuter

Förutom att använda en Spark-pool kan du även köra frågor mot en Azure Cosmos DB-analyscontainer med hjälp av en inbyggd serverlös SQL-pool i Azure Synapse Analytics. För att göra detta kan du använda OPENROWSET SQL-funktionen för att ansluta till den länkade tjänsten för din Azure Cosmos DB-databas.

Använda OPENROWSET med en autentiseringsnyckel

Som standard autentiseras åtkomsten till ett Azure Cosmos DB-konto av en autentiseringsnyckel. Du kan använda den här nyckeln som en del av en anslutningssträng i en OPENROWSET -instruktion för att ansluta via en länkad tjänst från en SQL-pool, som du ser i följande exempel:

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

Dricks

Du hittar en primär och sekundär nyckel för ditt Cosmos DB-konto på sidan Nycklar i Azure Portal.

Resultatet av den här frågan kan se ut ungefär så här, inklusive metadata och programdefinierade fält från objekten i Azure Cosmos DB-containern:

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

Data hämtas från analysarkivet och frågan påverkar inte driftlagret.

Använda OPENROWSET med en autentiseringsuppgift

I stället för att inkludera autentiseringsnyckeln i varje anrop till OPENROWSET kan du definiera en autentiseringsuppgift som kapslar in autentiseringsinformationen för ditt Cosmos DB-konto och använda autentiseringsuppgifterna i efterföljande frågor. Om du vill skapa en autentiseringsuppgift använder du -instruktionen CREATE CREDENTIAL enligt följande exempel:

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

Med autentiseringsuppgifterna på plats kan du använda den i en OPENROWSET funktion som den här:

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

Återigen innehåller resultaten metadata och programdefinierade fält från analysarkivet:

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

Ange ett schema

Syntaxen OPENROWSET innehåller en WITH sats som du kan använda för att definiera ett schema för den resulterande raduppsättningen. Du kan använda detta för att ange enskilda fält och tilldela datatyper enligt följande exempel:

 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

I det här fallet, förutsatt att fälten i analysarkivet innehåller productID och productName, liknar den resulterande raduppsättningen följande tabell:

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

Du kan naturligtvis ange enskilda kolumnnamn i SELECT -satsen (till exempel SELECT productID, productName ...), så den här möjligheten att ange enskilda kolumner kan verka vara begränsad. Tänk dock på fall där JSON-källdokumenten som lagras i driftarkivet innehåller flera fältnivåer, vilket visas i följande exempel:

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

- WITH satsen stöder inkludering av explicita JSON-sökvägar, så att du kan hantera kapslade fält och tilldela alias till fältnamn, som du ser i det här exemplet:

 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

Resultatet av den här frågan skulle innehålla följande rad för produkt 126:

ProductNo	ProductName	Leverantör	SupplierPhoneNo
126	Drev	Contoso	555-123-4567

Skapa en vy i en databas

Om du behöver köra frågor mot samma data ofta, eller om du behöver använda rapporterings- och visualiseringsverktyg som förlitar sig på SELECT instruktioner som inte innehåller OPENROWSET funktionen, kan du använda en vy för att abstrahera data. Om du vill skapa en vy bör du skapa en ny databas där du kan definiera den (användardefinierade vyer i huvuddatabasen stöds inte), som du ser i följande exempel:

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

Dricks

När du skapar en databas som kommer åt data i Cosmos DB är det bäst att använda en UTF-8-baserad sortering för att säkerställa kompatibilitet med strängar i Cosmos DB.

När vyn har skapats kan användare och klientprogram köra frågor mot den som vilken annan SQL-vy eller tabell som helst:

SELECT * FROM products;

Överväganden för serverlösa SQL-pooler och Azure Cosmos DB

När du planerar att använda en serverlös SQL-pool för att fråga efter data i ett Azure Cosmos DB-analysarkiv bör du överväga följande metodtips: