Consultar dados do Azure Cosmos DB usando um pool SQL sem servidor

Um pool SQL serverless permite-lhe analisar dados nos seus contentores de Azure Cosmos DB que estão ativados com Azure Synapse Link quase em tempo real, sem afetar o desempenho das suas cargas de trabalho transacionais. Oferece uma sintaxe Transact-SQL (T-SQL) familiar para consultar dados do armazenamento analítico e conectividade integrada a uma vasta gama de ferramentas de business intelligence (BI) e de consulta ad hoc através da interface T-SQL.

Para consultar o Azure Cosmos DB, toda a área funcional do SELECT é suportada através da função OPENROWSET, que inclui a maioria das funções e operadores SQL. Também pode armazenar os resultados da consulta que lê dados de Azure Cosmos DB junto com dados em Azure Blob Storage ou Azure Data Lake Storage usando create external table as select (CETAS). Atualmente, não é possível armazenar os resultados de consultas SQL sem servidor no Azure Cosmos DB usando o CETAS.

Este artigo explica como escrever uma consulta com um pool SQL serverless que consulta dados de contentores do Azure Cosmos DB ativados pelo Azure Synapse Link. Depois, pode aprender mais sobre como construir vistas de pool SQL sem servidor sobre contentores do Azure Cosmos DB e ligá-las a modelos do Power BI em este tutorial. Este tutorial utiliza um contentor com um esquema Azure Cosmos DB bem definido. Também pode consultar o módulo Learn sobre como consultar o Azure Cosmos DB com SQL Serverless para Azure Synapse Analytics.

Pré-requisitos

• Certifique-se de que o repositório analítico está preparado.
  • Ative o armazenamento analítico em os seus contentores de Azure Cosmos DB.
  • Obtenha a cadeia de ligação com uma chave de leitura que possa usar para consultar o armazenamento analítico.
  • Obtenha a chave só de leitura chave que será usada para aceder ao contentor Azure Cosmos DB.
• Certifique-se de que aplicou todas as práticas recomendadas, tais como:
  • Certifique-se de que o seu armazenamento analítico do Azure Cosmos DB está na mesma região do pool SQL serverless.
  • Certifique-se de que a aplicação cliente (Power BI, serviço de análise) está na mesma região do pool SQL serverless.
  • Se você estiver retornando uma grande quantidade de dados (mais de 80 GB), considere usar a camada de cache, como o Analysis Services, e carregue as partições menores que 80 GB no modelo do Analysis Services.
  • Se você estiver filtrando dados usando colunas de cadeia de caracteres, certifique-se de usar a OPENROWSET função com a cláusula explícita WITH que tem os menores tipos possíveis. Por exemplo, não use VARCHAR(1000) se souber que a propriedade tem até cinco caracteres.

Descrição geral

O pool SQL serverless permite-lhe consultar o armazenamento analítico do Azure Cosmos DB, usando a função OPENROWSET.

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

A cadeia de conexão SQL para Azure Cosmos DB inclui os seguintes componentes:

• conta - O nome da conta Azure Cosmos DB que está a vizar.
• database - O nome do contêiner, especificado sem aspas na sintaxe OPENROWSET. Se o nome do contêiner contiver caracteres especiais (por exemplo, um traço -), ele deverá ser colocado entre colchetes ([]).
• region (opcional) - A região do seu armazenamento analítico do Cosmos DB. Se omitido, é utilizada a região principal do contentor.
• endpoint (opcional) - O URI do ponto de extremidade do Cosmos DB (por exemplo https://<account name>.documents.azure.us) que é necessário se sua conta do Cosmos DB não seguir o formato padrão *.documents.azure.com .

Estas propriedades podem ser identificadas a partir da connection string padrão do Cosmos DB, por exemplo:

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

A connection string SQL pode ser formatada da seguinte forma:

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

Esta connection string não inclui a informação de autenticação necessária para se ligar ao armazenamento analítico do Cosmos DB. Informações adicionais são necessárias dependendo do tipo de autenticação usada:

• Se OPENROWSET usar a identidade gerida do workspace para aceder ao repositório analítico, você deverá adicionar a propriedade AuthType.
• Se OPENROWSET usar uma chave de conta embutida, deverá adicionar a propriedade key. Isto permite-lhe consultar coleções do Azure Cosmos DB sem precisar de preparar credenciais.
• Em vez de incluir a informação de autenticação no connection string, OPENROWSET pode referenciar uma credencial que contenha a chave da conta Azure Cosmos DB. Esta abordagem pode ser usada para criar vistas em coleções do Azure Cosmos DB.

Estas opções são descritas abaixo.

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>

Conjunto de dados de exemplo

Os exemplos neste artigo baseiam-se em dados do Centro Europeu de Prevenção e Controlo de Doenças (ECDC) COVID-19 Cases and COVID-19 Open Research Dataset (CORD-19).

Para acompanhar este artigo que mostra como consultar dados do Azure Cosmos DB com um pool SQL serverless, certifique-se de criar os seguintes recursos:

• Uma conta de base de dados Azure Cosmos DB que está Azure Synapse Link ativada
• Uma base de dados Azure Cosmos DB chamada covid
• Dois contentores Azure Cosmos DB chamados Ecdc e Cord19 carregados com os conjuntos de dados de amostra anteriores

Observe que essa conexão não garante o desempenho porque essa conta pode estar localizada em uma região remota em comparação com seu ponto de extremidade Synapse SQL.

Explore dados do Azure Cosmos DB com inferência automática de esquemas

A forma mais fácil de explorar dados no Azure Cosmos DB é utilizando a capacidade automática de inferência de esquemas. Ao omitir a cláusula WITH da instrução OPENROWSET, pode instruir o pool SQL serverless a detetar automaticamente (inferir) o esquema do armazenamento analítico do contentor 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

No exemplo anterior, instruímos o pool SQL sem servidor a ligar-se à base de dados covid na conta Azure Cosmos DB MyCosmosDbAccount autenticada usando a chave Azure Cosmos DB (uma chave fictícia no exemplo anterior). Em seguida, acedemos ao armazenamento analítico do Ecdc na região West US 2. Como não há projeção de propriedades específicas, a função OPENROWSET devolve todas as propriedades dos Azure Cosmos DB itens.

Assumindo que os itens no contentor de Azure Cosmos DB têm propriedades date_rep, cases e geo_id, os resultados desta consulta são apresentados na tabela seguinte:

Se precisares de explorar dados do outro contentor na mesma base de dados do Azure Cosmos DB, podes usar a mesma connection string e referenciar o contentor necessário como terceiro parâmetro:

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

Especificar explicitamente o esquema

Embora a capacidade automática de inferência de esquema em OPENROWSET proporcione uma experiência simples e fácil de usar, os seus cenários empresariais podem exigir que especifique explicitamente o esquema para ler apenas as propriedades relevantes dos dados do Azure Cosmos DB.

A OPENROWSET função permite especificar explicitamente quais propriedades você deseja ler dos dados no contêiner e especificar seus tipos de dados.

Imaginemos que importámos alguns dados do conjunto de dados COVID ECDC com a seguinte estrutura para 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"}

Estes documentos JSON planos no Azure Cosmos DB podem ser representados como um conjunto de linhas e colunas no Synapse SQL. A OPENROWSET função permite especificar um subconjunto de propriedades que você deseja ler e os tipos de coluna exatos na WITH cláusula:

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
   

O resultado dessa consulta pode se parecer com a tabela a seguir:

Para mais informações sobre os tipos SQL que devem ser usados para valores de Azure Cosmos DB, veja Azure Cosmos DB para mapeamentos de tipos SQL no final deste artigo.

Criar vista

A criação de vistas nos bancos de dados master ou padrão não é recomendada ou suportada. Portanto, você precisa criar um banco de dados de usuários para suas visualizações.

Depois de identificar o esquema, pode preparar uma visão sobre os seus dados do Azure Cosmos DB. Deve colocar a chave da sua conta de Azure Cosmos DB numa credencial separada e referenciar essa credencial a partir da função OPENROWSET. Não mantenha a chave da sua conta na definição de visualização.

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

Não use OPENROWSET sem esquema explicitamente definido, pois isso pode afetar seu desempenho. Certifique-se de usar os menores tamanhos possíveis para suas colunas (por exemplo, VARCHAR(100) em vez de padrão VARCHAR(8000)). Você deve usar algum agrupamento UTF-8 como agrupamento de banco de dados padrão ou defini-lo como agrupamento de coluna explícito para evitar um problema de conversão UTF-8. A ordenação Latin1_General_100_BIN2_UTF8 proporciona o melhor desempenho ao filtrar dados utilizando algumas colunas de texto.

Ao consultar a vista, poderá encontrar erros ou resultados inesperados. As referências de visualização, colunas ou objetos foram provavelmente modificados ou já não existem. Você precisa ajustar manualmente a definição de exibição para alinhar com as alterações de esquema subjacentes. Lembre-se de que isso pode acontecer ao usar a inferência automática de esquema na exibição e ao especificar explicitamente o esquema.

Consultar objetos aninhados

Com o Azure Cosmos DB, pode representar modelos de dados mais complexos compondo-os como objetos aninhados ou arrays. A capacidade de autosync do Azure Synapse Link para Azure Cosmos DB gere automaticamente a representação do esquema no armazenamento analítico, incluindo o tratamento de tipos de dados aninhados que permitem consultas avançadas a partir do pool SQL sem servidor.

Por exemplo, o conjunto de dados CORD-19 tem documentos JSON que seguem esta estrutura:

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

Os objetos aninhados e arrays no Azure Cosmos DB são representados como strings JSON no resultado da consulta quando a função OPENROWSET os lê. Pode especificar os caminhos para os valores aninhados nos objetos quando utiliza a cláusula 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;

O resultado dessa consulta pode se parecer com a tabela a seguir:

Para saber mais, consulte Analisar tipos de dados complexos em Azure Synapse Analytics ou Consultar tipos aninhados em ficheiros Parquet e JSON usando pools SQL sem servidor.

Nivelar matrizes aninhadas

Os dados do Azure Cosmos DB podem ter subarrays aninhados, como um array do autor de um dataset 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>
            },
            ...
        ],
        ...
}

Em alguns casos, talvez seja necessário unir as propriedades do item superior (metadados) com todos os elementos da matriz (autores). Um pool SQL sem servidor permite nivelar estruturas aninhadas aplicando a OPENJSON função na matriz aninhada:

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

O resultado dessa consulta pode se parecer com a tabela a seguir:

Azure Cosmos DB para mapeamentos de tipo SQL

Embora o armazenamento transacional do Azure Cosmos DB seja independente do esquema, o armazenamento analítico é esquematizado para otimizar o desempenho das consultas analíticas. Com a capacidade de sincronização automática do Azure Synapse Link, o Azure Cosmos DB gere a representação do esquema no armazenamento analítico logo de fábrica, o que inclui o tratamento dos tipos de dados aninhados. Como um pool SQL sem servidor consulta o armazenamento analítico, é importante compreender como mapear os tipos de dados de entrada do Azure Cosmos DB para os tipos SQL.

Azure Cosmos DB contas da API SQL (Core) suportam tipos de propriedades JSON como número, string, Boolean, null, objeto aninhado ou array. Você precisaria escolher tipos SQL que correspondam a esses tipos JSON se estiver usando a WITH cláusula em OPENROWSET. A tabela seguinte mostra os tipos de colunas SQL que devem ser usados para diferentes tipos de propriedades no Azure Cosmos DB.

Esquema de fidelidade total

O esquema full fidelity do Azure Cosmos DB regista ambos os valores e os seus melhores tipos de correspondência para cada propriedade num contentor. A função OPENROWSET num contentor com esquema de alta fidelidade fornece tanto o tipo como o valor real em cada célula. Vamos supor que a consulta a seguir leia os itens de um contêiner com esquema de fidelidade total:

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

O resultado dessa consulta retorna tipos e valores formatados como texto JSON:

Para cada valor, pode ver o tipo identificado num item do container do Azure Cosmos DB. A maioria dos valores da propriedade date_rep contém valores date, mas alguns deles estão armazenados incorretamente como cadeias em Azure Cosmos DB. O esquema de fidelidade total retorna valores digitados date corretamente e valores formatados string incorretamente.

O número de casos é armazenado como um int32 valor, mas há um valor que é inserido como um número decimal. Este valor tem o float64 tipo. Se houver alguns valores que excedam o maior int32 número, eles serão armazenados como o int64 tipo. Todos os geo_id valores neste exemplo são armazenados como string tipos.

Para consultar Azure Cosmos DB contas MongoDB, pode aprender mais sobre a representação do esquema de fidelidade total na Analytical Store e os nomes de propriedades estendidas a serem usados em O que é Azure Cosmos DB Analytical Store?

Consultar itens com esquema de fidelidade total

Ao consultar o esquema de fidelidade total, é necessário especificar explicitamente o tipo SQL e o tipo de propriedade Azure Cosmos DB esperado na cláusula WITH.

No exemplo a seguir, assumimos que string é o tipo correto para a geo_id propriedade e int32 é o tipo correto para a cases propriedade:

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

Os valores para geo_id e cases que têm outros tipos são retornados como NULL valores. Esta consulta faz referência apenas ao cases com o tipo especificado na expressão (cases.int32).

Se tiver valores com outros tipos (cases.int64, cases.float64) que não podem ser limpos num contentor Azure Cosmos DB, terá de os referenciar explicitamente numa cláusula WITH e combinar os resultados. A consulta a seguir agrega , int32int64e float64 armazenada na cases coluna:

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

Neste exemplo, o número de casos é armazenado como int32, int64ou float64 valores. Todos os valores devem ser extraídos para calcular o número de casos por país ou região.

Resolução de Problemas

Consulte a página autoajuda para encontrar os problemas conhecidos ou passos de resolução que o possam ajudar a resolver possíveis problemas com Azure Cosmos DB questões.

Conteúdos relacionados

• Use o Power BI e um pool Synapse SQL serverless para analisar dados do Azure Cosmos DB
• Criar e usar modos de exibição usando o pool SQL sem servidor
• Tutorial: Explore e analise lagoas de dados com SQL pool sem servidor
• Se você estiver recebendo erros ou enfrentando problemas de desempenho, consulte Solucionar problemas do pool SQL sem servidor
• Module: Implementar Azure Synapse Link com Azure Cosmos DB