Eseguire query sui dati di Azure Cosmos DB con un pool SQL un serverless in Collegamento ad Azure Synapse

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

Per l'esecuzione di query su Azure Cosmos DB, l'intera superficie di attacco 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 Archiviazione BLOB di Azure o in Azure Data Lake Storage usando Create Table As Select (CETAS). Attualmente non è possibile archiviare i risultati delle query del pool SQL serverless in Azure Cosmos DB con CETAS.

Questo articolo illustra come scrivere una query con un pool SQL serverless che eseguirà query sui dati dai contenitori di Azure Cosmos DB abilitati con Collegamento ad Azure Synapse. È 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 ben definito di Azure Cosmos DB. È anche possibile consultare il modulo Learn su come eseguire query su Azure Cosmos DB con SQL Serverless per Azure Synapse Analytics.

Prerequisiti

• Assicurarsi di aver preparato l'archivio analitico:
  • Abilitare l'archivio analitico nei contenitori di Azure Cosmos DB.
  • Ottenere la stringa di connessione con una chiave di sola lettura da usare per eseguire query sull'archivio analitico.
  • Ottenere la chiave di sola lettura da usare per accedere al contenitore di 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 trovi nella stessa area del pool SQL serverless.
  • Se viene restituita una grande quantità di dati (maggiore 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 funzione OPENROWSET con la clausola WITH esplicita con i tipi più piccoli possibili. Ad esempio, non usare VARCHAR(1000) se si sa che la proprietà include un massimo di cinque caratteri.

Panoramica

Il pool SQL serverless consente di eseguire query nell'archiviazione analitica di Azure Cosmos DB usando la funzione OPENROWSET.

• OPENROWSET con chiave inline. È possibile usare questa sintassi per eseguire query sulle raccolte di Azure Cosmos DB senza dover preparare le credenziali.
• OPENROWSET che fa riferimento alle credenziali che contengono la chiave dell'account Azure Cosmos DB. È possibile usare questa sintassi per creare viste sulle raccolte di 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>'

Set di dati di esempio

Gli esempi in questo articolo si basano sui dati di Casi di COVID-19 del Centro europeo per la prevenzione e il controllo delle malattie (ECDC) e del Set di dati per la ricerca aperta sul COVID-19 (CORD-19), doi:10.5281/zenodo.3715505.

È possibile visualizzare la licenza e la struttura dei dati in queste pagine. È anche possibile scaricare dati di esempio per i set di dati ECDC e CORD-19.

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

• Un account di database di Azure Cosmos DB abilitato per Collegamento ad Azure Synapse.
• Un database di Azure Cosmos DB denominato covid.
• Due contenitori di Azure Cosmos DB denominati Ecdc e Cord19 caricati con i set di dati di esempio precedenti.

È possibile usare la stringa di connessione seguente a scopo di test: Account=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==. Notare che questa connessione non garantisce le prestazioni perché questo account potrebbe trovarsi in un'area remota rispetto all'endpoint Synapse SQL.

Esplorare i dati di Azure Cosmos DB con l'inferenza automatica dello schema

Il modo più semplice per esplorare i dati in Azure Cosmos DB consiste nello sfruttare la capacità di inferenza automatica dello schema. Omettendo la clausola WITH dall'istruzione OPENROWSET, è possibile indicare al pool SQL serverless di rilevare automaticamente (tramite inferenza) lo schema dell'archivio analitico del contenitore di 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

Nell'esempio precedente è stato indicato al pool SQL serverless di connettersi al database covid nell'account Azure Cosmos DB MyCosmosDbAccount autenticato usando la chiave di 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 restituirà tutte le proprietà dagli elementi di Azure Cosmos DB.

Supponendo che gli elementi nel contenitore Azure Cosmos DB abbiano le 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 di Azure Cosmos DB, è possibile usare la stessa stringa di connessione e fare riferimento al contenitore richiesto come terzo parametro:

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

Specificare lo schema in modo esplicito

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

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

Si supponga di aver importato alcuni dati dal set di dati ECDC COVID 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à da leggere e i tipi di colonna esatti nella clausola 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
   

Il risultato di questa query potrebbe essere simile alla tabella seguente:

Per altre informazioni sui tipi SQL da usare per i valori di Azure Cosmos DB, vedere le regole per i mapping dei tipi SQL alla fine dell'articolo.

Crea visualizzazione

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

Dopo aver identificato lo schema, è possibile preparare una vista basata sui dati di Azure Cosmos DB. È opportuno inserire la chiave dell'account Azure Cosmos DB in credenziali separate e fare riferimento a tali credenziali dalla funzione OPENROWSET. Non tenere la chiave dell'account nella definizione della vista.

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

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) invece di quelle predefinite VARCHAR(8000). È consigliabile usare alcune regole di confronto UTF-8 come regole di confronto predefinite del database o impostarle come regole di confronto di colonna esplicite per evitare problemi 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 vengano restituiti risultati imprevisti. Questo significa probabilmente che la vista fa riferimento a colonne oppure a oggetti modificati o che non esistono più. È necessario modificare manualmente la definizione della vista in modo che sia allineata alle modifiche sottostanti dello schema. Tenere presente che questo problema 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 matrici oppure oggetti annidati. La funzionalità di sincronizzazione automatica di Collegamento ad Azure Synapse 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
        ...
     }
     ...
}

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

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;

Il risultato di questa query potrebbe essere simile alla tabella seguente:

Altre informazioni sull'analisi dei tipi di dati complessi come i file Parquet e i contenitori in Collegamento ad Azure Synapse per Azure Cosmos DB o le strutture annidate in un 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 di livello superiore (metadati) con tutti gli elementi della matrice (autori). Un pool SQL serverless consente di rendere flat le strutture annidate applicando la funzione OPENJSON nella matrice annidata:

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

Il risultato di questa query potrebbe essere simile alla tabella seguente:

Mapping dei tipi Azure Cosmos DB ai tipi SQL

Anche se l'archivio transazionale di Azure Cosmos DB è indipendente dallo schema, l'archivio analitico viene schematizzato per ottimizzare le prestazioni delle query analitiche. Con la funzionalità di sincronizzazione automatica di Collegamento ad Azure Synapse, Azure Cosmos DB gestisce la rappresentazione dello schema nell'archivio analitico predefinito, 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 di Azure Cosmos DB ai tipi di dati SQL.

Gli account Azure Cosmos DB dell'API SQL (Core) supportano i tipi di proprietà JSON numero, stringa, Boolean, Null, oggetto annidato o matrice. Se si usa la clausola WITH in OPENROWSET, potrebbe essere necessario scegliere tipi SQL corrispondenti a questi tipi JSON. La tabella seguente illustra i tipi di colonna SQL che dovrebbero 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 restituirà tipi e valori formattati come testo JSON:

Per ogni valore è possibile visualizzare il tipo identificato in un elemento del contenitore di Azure Cosmos DB. La maggior parte dei valori per la proprietà date_rep contiene valori di date, ma alcuni di essi vengono archiviati erroneamente come stringhe in Azure Cosmos DB. Lo schema di massima fedeltà restituirà valori date di tipo corretto e valori string non formattati correttamente. Il numero di casi è costituito da informazioni archiviate come valore int32, ma esiste un valore immesso come numero decimale. Questo valore è di 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 sugli account Azure Cosmos DB for MongoDB, vedere altre informazioni sulla rappresentazione dello schema di massima fedeltà nell'archivio analitico e sui nomi delle proprietà estese da usare in Informazioni sull'archivio analitico di Azure Cosmos DB.

Eseguire query sugli elementi con schema di massima fedeltà

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

Nell'esempio seguente si presuppone che string sia il tipo corretto per la proprietà geo_id e che int32 sia 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 verranno restituiti come valori NULL. Questa query farà 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 di 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 casi per paese/area geografica.

Risoluzione dei problemi

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

Passaggi successivi

Per altre informazioni, vedere gli articoli seguenti:

• Usare Power BI e il pool SQL serverless con Collegamento ad Azure Synapse
• Creare e usare viste in un pool SQL serverless
• Esercitazione sulla creazione di viste del pool SQL serverless su Azure Cosmos DB e sulla connessione ai modelli di Power BI tramite DirectQuery
• Visitare la pagina del Supporto self-help per Collegamento ad Azure Synapse per Azure Cosmos DB se si verificano errori o problemi di prestazioni.
• Vedere il modulo Learn per informazioni su come eseguire query su Azure Cosmos DB con SQL serverless per Azure Synapse Analytics.