Eseguire query sui dati di Azure Cosmos DB utilizzando un pool SQL serverless

Un pool SQL serverless consente di analizzare i dati nei contenitori di Azure Cosmos DB abilitati con Azure Synapse Link quasi in tempo reale senza influire sulle prestazioni dei carichi di lavoro transazionali. Offre una sintassi di Transact-SQL (T-SQL) familiare per eseguire query sui dati dall'archivio analytical store e la connettività integrata a un'ampia gamma di strumenti di business intelligence (BI) e di query ad hoc tramite l'interfaccia T-SQL.

Per interrogare Azure Cosmos DB, l'intera capacità del SELECT è supportata tramite la funzione OPENROWSET, che include la maggior parte delle funzioni e degli operatori SQL. È anche possibile archiviare i risultati della query che legge i dati da Azure Cosmos DB insieme ai dati in Azure Blob Storage o Azure Data Lake Storage usando creare una tabella esterna come select (CETAS). Non è attualmente possibile archiviare i risultati delle query del pool SQL serverless per Azure Cosmos DB usando CETAS.

Questo articolo illustra come scrivere una query con un pool SQL serverless che esegue query sui dati da contenitori Azure Cosmos DB abilitati con Azure Synapse Link. È quindi possibile ottenere altre informazioni sulla creazione di viste di pool SQL serverless sui contenitori di Azure Cosmos DB e sulla connessione ai modelli di Power BI in questa esercitazione. Questa esercitazione usa un contenitore con uno schema Azure Cosmos DB ben definito. È anche possibile consultare il modulo Learn su come eseguire query su Azure Cosmos DB con SQL Serverless per Azure Synapse Analytics.

Prerequisiti

• Assicurarsi di preparare l'archivio analitico:
  • Abilitare l'archivio analitico nei contenitori Azure Cosmos DB.
  • Ottieni la stringa di connessione con una chiave di sola lettura che puoi usare per eseguire query negli archivi analitici.
  • Ottenere la chiave di sola lettura che verrà usata per accedere al contenitore Azure Cosmos DB.
• Assicurarsi di aver applicato tutte le procedure consigliate, ad esempio:
  • Assicurarsi che l'archiviazione analitica di Azure Cosmos DB si trovi nella stessa area del pool SQL serverless.
  • Assicurarsi che l'applicazione client (Power BI, Analysis Service) si trova nella stessa area del pool SQL serverless.
  • Se si restituisce una grande quantità di dati (più di 80 GB), è consigliabile usare il livello di memorizzazione nella cache, ad esempio Analysis Services, e caricare le partizioni inferiori a 80 GB nel modello di Analysis Services.
  • Se si filtrano i dati usando colonne stringa, assicurarsi di usare la OPENROWSET funzione con la clausola esplicita WITH con i tipi più piccoli possibili. Ad esempio, non usare VARCHAR(1000) se si sa che la proprietà ha fino a cinque caratteri.

Panoramica

Il pool SQL serverless consente di eseguire query sugli storage analitici di Azure Cosmos DB utilizzando la funzione OPENROWSET.

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

Il connection string SQL per Azure Cosmos DB include i componenti seguenti:

• account : nome dell'account Azure Cosmos DB di destinazione.
• database: nome del contenitore, specificato senza virgolette nella sintassi OPENROWSET. Se il nome del contenitore contiene caratteri speciali (ad esempio, un trattino -), deve essere racchiuso tra parentesi quadre ([]).
• region (facoltativo): area dell'archiviazione analitica di Cosmos DB. Se omessa, verrà usata l'area primaria del contenitore.
• endpoint (facoltativo): URI dell'endpoint Cosmos DB (ad esempio https://<account name>.documents.azure.us) necessario se l'account Cosmos DB non segue il formato standard *.documents.azure.com .

Queste proprietà possono essere identificate dal connection string standard di Cosmos DB, ad esempio:

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

Il connection string SQL può essere formattato come segue:

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

Questo connection string non include le informazioni di autenticazione necessarie per connettersi all'archiviazione analitica di Cosmos DB. Sono necessarie ulteriori informazioni a seconda del tipo di autenticazione utilizzata:

• Se OPENROWSET usa l'identità gestita dell'area di lavoro per accedere all'archivio analitico, è necessario aggiungere la proprietà AuthType.
• Se OPENROWSET utilizza una chiave dell'account in linea, è necessario aggiungere la proprietà key. In questo modo è possibile interrogare le raccolte di Azure Cosmos DB senza la necessità di preparare le credenziali.
• Anziché includere le informazioni di autenticazione nella connection string, OPENROWSET può fare riferimento a una credenziale contenente la chiave dell'account Azure Cosmos DB. Questo approccio può essere usato per creare visualizzazioni in Azure Cosmos DB raccolte.

Queste opzioni sono descritte di seguito.

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>

Set di dati di esempio

Gli esempi in questo articolo si basano sui dati del Centro europeo per la prevenzione e il controllo delle malattie (ECDC) COVID-19 Cases and COVID-19 Open Research Dataset (CORD-19).

Per seguire questo articolo che illustra come eseguire query sui dati Azure Cosmos DB con un pool SQL serverless, assicurarsi di creare le risorse seguenti:

• Un account di database Azure Cosmos DB con funzione Azure Synapse Link abilitata
• Un database di Azure Cosmos DB denominato covid
• Due contenitori Azure Cosmos DB denominati Ecdc e Cord19 caricati con i set di dati di esempio precedenti

Si noti che questa connessione non garantisce prestazioni perché questo account potrebbe trovarsi in un'area remota rispetto all'endpoint Synapse SQL.

Esplorare Azure Cosmos DB dati con inferenza automatica dello schema

Il modo più semplice per esplorare i dati in Azure Cosmos DB consiste nell'usare la funzionalità di inferenza automatica dello schema. Omettendo la clausola WITH dall'istruzione OPENROWSET, è possibile indicare al pool SQL serverless di rilevare automaticamente (dedurre) lo schema dell'archivio analitico del contenitore di Azure Cosmos DB.

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

Nell'esempio precedente è stato indicato al pool SQL serverless di connettersi al database covid nell'account Azure Cosmos DB MyCosmosDbAccount autenticato usando la chiave Azure Cosmos DB (fittizia nell'esempio precedente). È stato quindi eseguito l'accesso all'archivio analitico del contenitore Ecdc nell'area West US 2. Poiché non esiste alcuna proiezione di proprietà specifiche, la funzione OPENROWSET restituisce tutte le proprietà degli elementi Azure Cosmos DB.

Supponendo che gli elementi nel contenitore Azure Cosmos DB abbiano proprietà date_rep, cases e geo_id, i risultati di questa query vengono visualizzati nella tabella seguente:

Se è necessario esplorare i dati dall'altro contenitore nello stesso database Azure Cosmos DB, è possibile usare lo stesso connection string e fare riferimento al contenitore richiesto come terzo parametro:

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

Specificare uno schema in modo esplicito

Anche se la funzionalità di inferenza automatica dello schema in OPENROWSET offre un'esperienza semplice e facile da usare, gli scenari aziendali potrebbero richiedere di specificare in modo esplicito lo schema per le proprietà pertinenti di sola lettura dai dati Azure Cosmos DB.

La funzione OPENROWSET consente di specificare in modo esplicito le proprietà da leggere dai dati nel contenitore e di specificare i relativi tipi di dati.

Si supponga di aver importato alcuni dati dal set di dati COVID ECDC con la struttura seguente in 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"}

Questi documenti JSON flat in Azure Cosmos DB possono essere rappresentati come un set di righe e colonne in Synapse SQL. La funzione OPENROWSET consente di specificare un subset di proprietà che si desidera leggere e i tipi di colonna esatti nella clausola WITH:

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
   

Il risultato di questa query può essere simile alla tabella seguente:

Per ulteriori informazioni sui tipi SQL da utilizzare per i valori di Azure Cosmos DB, consultare i mapping dei tipi SQL per Azure Cosmos DB alla fine di questo articolo.

Crea visualizzazione

La creazione di viste nei database master o predefiniti non è consigliata o supportata. È quindi necessario creare un database utente per le viste.

Dopo aver identificato lo schema, è possibile preparare una visualizzazione sopra i dati Azure Cosmos DB. È necessario inserire la chiave dell'account Azure Cosmos DB in una credenziale separata e fare riferimento a questa credenziale dalla funzione OPENROWSET. Non mantenere la chiave dell'account nella definizione di visualizzazione.

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

Non usare OPENROWSET senza uno schema definito in modo esplicito perché potrebbe influire sulle prestazioni. Assicurarsi di usare le dimensioni più piccole possibili per le colonne, ad esempio VARCHAR(100) anziché il valore predefinito VARCHAR(8000). È consigliabile usare alcune regole di confronto UTF-8 come regole di confronto predefinite del database o impostarla come regole di confronto di colonna esplicite per evitare un problema di conversione UTF-8. Le regole di confronto Latin1_General_100_BIN2_UTF8 offrono prestazioni ottimali quando si filtrano i dati usando alcune colonne stringa.

Quando si esegue una query sulla vista, è possibile che si verifichino errori o risultati imprevisti. La vista fa riferimento, le colonne o gli oggetti sono stati probabilmente modificati o non esistono più. È necessario modificare manualmente la definizione della vista in modo che sia allineata alle modifiche sottostanti dello schema. Tenere presente che questa situazione può verificarsi sia quando si usa l'inferenza automatica dello schema nella vista che quando si specifica in modo esplicito lo schema.

Eseguire query su oggetti annidati

Con Azure Cosmos DB, è possibile rappresentare modelli di dati più complessi componendoli come oggetti o matrici annidati. La funzionalità di sincronizzazione automatica di Azure Synapse Link per Azure Cosmos DB gestisce la rappresentazione dello schema nell'archivio analitico predefinito, che include la gestione dei tipi di dati annidati che consentono l'esecuzione di query avanzate dal pool SQL serverless.

Ad esempio, il set di dati CORD-19 include documenti JSON conformi a questa struttura:

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

Gli oggetti annidati e le matrici in Azure Cosmos DB sono rappresentati come stringhe JSON nel risultato della query quando la funzione OPENROWSET le legge. È possibile specificare i percorsi dei valori annidati negli oggetti quando si usa la clausola WITH:

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;

Il risultato di questa query può essere simile alla tabella seguente:

Per altre informazioni, vedere Analizzare i tipi di dati complessi in Azure Synapse Analytics o Eseguire query sui tipi annidati in file Parquet e JSON usando il pool SQL serverless.

Rendere flat le matrici annidate

I dati di Azure Cosmos DB potrebbero presentare matrici secondarie annidate, come la matrice dell'autore di un set di dati 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>
            },
            ...
        ],
        ...
}

In alcuni casi, potrebbe essere necessario unire le proprietà dall'elemento superiore (metadati) con tutti gli elementi della matrice (autori). Un pool SQL serverless consente di rendere flat le strutture annidate applicando la funzione OPENJSON alla matrice nidificata:

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

Il risultato di questa query può essere simile alla tabella seguente:

Azure Cosmos DB per le mappature dei tipi SQL

Sebbene Azure Cosmos DB archivio transazionale sia indipendente dallo schema, l'archivio analitico viene schematizzato per ottimizzare le prestazioni delle query analitiche. Con la funzionalità di sincronizzazione automatica di Azure Synapse Link, Azure Cosmos DB gestisce la rappresentazione dello schema nell'archivio analitico, che include la gestione dei tipi di dati annidati. Poiché un pool SQL serverless esegue query sull'archivio analitico, è importante comprendere come eseguire il mapping dei tipi di dati di input Azure Cosmos DB ai tipi di dati SQL.

gli account Azure Cosmos DB dell'API SQL (Core) supportano i tipi di proprietà JSON di number, string, Boolean, null, nested object o array. Se si usa la clausola WITH in OPENROWSET, potrebbe essere necessario scegliere tipi SQL corrispondenti a questi tipi JSON. Nella tabella seguente vengono illustrati i tipi di colonna SQL che devono essere usati per tipi di proprietà diversi in Azure Cosmos DB.

Schema di massima fedeltà

Lo schema di massima fedeltà di Azure Cosmos DB registra sia i valori che i tipi di corrispondenza migliori per ogni proprietà in un contenitore. La funzione OPENROWSET in un contenitore con schema di massima fedeltà fornisce sia il tipo che il valore effettivo in ogni cella. Si supponga che la query seguente legga gli elementi da un contenitore con schema di massima fedeltà:

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

Il risultato di questa query restituisce tipi e valori formattati come testo JSON:

Per ogni valore, è possibile visualizzare il tipo identificato in un elemento contenitore Azure Cosmos DB. La maggior parte dei valori per la proprietà date_rep contiene valori date, ma alcuni di essi vengono archiviati erroneamente come stringhe in Azure Cosmos DB. Lo schema di fedeltà completa restituisce valori tipizzati date correttamente e valori formattati string in modo non corretto.

Il numero di case viene archiviato int32 come valore, ma esiste un valore immesso come numero decimale. Questo valore ha il tipo float64. Se sono presenti alcuni valori che superano il numero massimo di int32, verranno archiviati come tipo int64. Tutti i valori geo_id in questo esempio vengono archiviati come tipi string.

Per eseguire query su Azure Cosmos DB per gli account MongoDB, è possibile ottenere ulteriori informazioni sulla rappresentazione completa dello schema con fedeltà nell'archivio analitico e sui nomi delle proprietà estese da usare in Che cos'è l'archivio analitico di Azure Cosmos DB?

Eseguire query sugli elementi con schema di massima fedeltà

Durante l'esecuzione di query sullo schema di fedeltà completa, è necessario specificare in modo esplicito il tipo di proprietà SQL e il tipo di proprietà Azure Cosmos DB previsto nella clausola WITH.

Nell'esempio seguente si presuppone che string sia il tipo corretto per la proprietà geo_id e int32 è il tipo corretto per la proprietà 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

I valori per geo_id e cases con altri tipi vengono restituiti come valori NULL. Questa query fa riferimento solo a cases con il tipo specificato nell'espressione (cases.int32).

Se sono presenti valori con altri tipi (cases.int64, cases.float64) che non possono essere puliti in un contenitore Azure Cosmos DB, è necessario farvi riferimento in modo esplicito in una clausola WITH e combinare i risultati. La query seguente aggrega int32, int64 e float64 archiviati nella colonna cases:

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

In questo esempio il numero di casi viene archiviato come valori int32, int64 o float64. Tutti i valori devono essere estratti per calcolare il numero di case per paese o area geografica.

Risoluzione dei problemi

Esaminare la pagina self-help per trovare i problemi noti o i passaggi per la risoluzione dei problemi che consentono di risolvere potenziali problemi relativi alle query Azure Cosmos DB.

Contenuti correlati

• Utilizza Power BI e il pool SQL Synapse serverless per analizzare i dati di Azure Cosmos DB
• Creare e usare le viste nel pool SQL serverless
• Esplorare e analizzare data lake con un pool SQL serverless
• Se si verificano errori o si verificano problemi di prestazioni, vedere Risolvere i problemi del pool SQL serverless
• Module: implementare Synapse Link di Azure con Azure Cosmos DB