Eseguire una query in Cosmos DB con Synapse SQL

  • 5 minuti

Oltre a usare un pool di Spark, è possibile eseguire query su un contenitore analitico di Azure Cosmos DB usando un pool SQL serverless predefinito in Azure Synapse Analytics. A tale scopo, è possibile usare la funzione SQL OPENROWSET per connettersi al servizio collegato per il database Azure Cosmos DB.

Uso di OPENROWSET con una chiave di autenticazione

Per impostazione predefinita, l'accesso a un account Azure Cosmos DB viene autenticato da una chiave di autenticazione. È possibile usare questa chiave come parte di una stringa di connessione in un'istruzione OPENROWSET per connettersi attraverso un servizio collegato da un pool SQL, come illustrato nell'esempio seguente:

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

Suggerimento

È possibile trovare una chiave primaria e secondaria per l'account Cosmos DB nella relativa pagina Chiavi nel portale di Azure.

I risultati di questa query potrebbero essere simili ai seguenti, inclusi i metadati e i campi definiti dall'applicazione dagli elementi nel contenitore di Azure Cosmos DB:

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

I dati vengono recuperati dall'archivio analitico e la query non influisce sull'archivio operativo.

Uso di OPENROWSET con credenziali

Anziché includere la chiave di autenticazione in ogni chiamata a OPENROWSET, è possibile definire credenziali che incapsulano le informazioni di autenticazione per l'account Cosmos DB e usare le credenziali nelle query successive. Per creare le credenziali, usare l'istruzione CREATE CREDENTIAL come illustrato in questo esempio:

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

Con le credenziali impostate, è possibile usarle in una funzione OPENROWSET simile alla seguente:

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

Anche in questo caso, i risultati includono metadati e campi definiti dall'applicazione dall'archivio analitico:

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

Definizione di uno schema

La sintassi OPENROWSET include una clausola WITH che è possibile usare per definire uno schema per il set di righe risultante. È possibile usare questa opzione per specificare singoli campi e assegnare i tipi di dati, come illustrato nell'esempio seguente:

 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

In questo caso, presupponendo che i campi nell'archivio analitico includano productID e productName, il set di righe risultante sarà simile alla tabella seguente:

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

È naturalmente possibile specificare singoli nomi di colonna nella clausola SELECT, ad esempio SELECT productID, productName ..., quindi può sembrare che la possibilità di specificare singole colonne sia di uso limitato. Tuttavia, considerare i casi in cui i documenti JSON di origine archiviati nell'archivio operativo includono più livelli di campi, come illustrato nell'esempio seguente:

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

La clausola WITH supporta l'inclusione di percorsi JSON espliciti, consentendo di gestire campi annidati e di assegnare alias ai nomi di campo, come illustrato in questo esempio:

 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

I risultati di questa query includono la riga seguente per il prodotto 126:

ProductNo ProductName Fornitore SupplierPhoneNo
126 Sprocket Contoso 555-123-4567

Creazione di una vista in un database

Se è necessario eseguire spesso una query sugli stessi dati oppure è necessario usare gli strumenti di reporting e visualizzazione basati su istruzioni SELECT che non includono la funzione OPENROWSET, è possibile usare una vista per astrarre i dati. Per creare una vista, è necessario creare un nuovo database in cui definirla (le viste definite dall'utente nel database master non sono supportate), come illustrato nell'esempio seguente:

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

Suggerimento

Quando si crea un database che accede ai dati in Cosmos DB, è consigliabile usare regole di confronto basate su UTF-8 per garantire la compatibilità con le stringhe in Cosmos DB.

Dopo aver creato la vista, gli utenti e le applicazioni client possono eseguire una query su di essa come su qualsiasi altra vista o tabella SQL:

SELECT * FROM products;

Considerazioni relative ai pool SQL serverless e ad Azure Cosmos DB

Se si prevede di usare un pool SQL serverless per eseguire query sui dati in un archivio analitico di Azure Cosmos DB, le seguenti procedure consigliate possono essere utili:

  • Effettuare il provisioning dell'archivio analitico di Azure Cosmos DB e di qualsiasi applicazione client, ad esempio Microsoft Power BI, nella stessa area del pool SQL serverless.

    I contenitori Azure Cosmos DB possono essere replicati in più aree. Se si usa un contenitore con più aree, è possibile specificare un parametro region nella stringa di connessione OPENROWSET per assicurarsi che le query vengano inviate a una replica regionale specifica del contenitore.

  • Se si usano colonne di stringa, usare la funzione OPENROWSET con la clausola WITH esplicita e specificare una lunghezza dati appropriata per i dati stringa.