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

Um pool SQL sem servidor permite que você analise dados em seus contêineres do Azure Cosmos DB habilitados com o Azure Synapse Link quase em tempo real sem afetar o desempenho de suas cargas de trabalho transacionais. Ele oferece uma sintaxe Transact-SQL (T-SQL) familiar para consultar dados do repositório analítico e conectividade integrada a uma ampla gama de business intelligence (BI) e ferramentas de consulta ad-hoc por meio da interface T-SQL.

Para consultar o Azure Cosmos DB, a área de superfície SELECT completa é suportada através da função OPENROWSET, que inclui a maioria das funções e operadores SQL. Você também pode armazenar resultados da consulta que lê dados do Azure Cosmos DB juntamente com dados no Armazenamento de Blobs do Azure ou no Armazenamento do Azure Data Lake usando criar tabela externa como seleção (CETAS). Atualmente, não é possível armazenar resultados de consulta de pool SQL sem servidor no Azure Cosmos DB usando o CETAS.

Este artigo explica como escrever uma consulta com um pool SQL sem servidor que consulta dados de contêineres do Azure Cosmos DB habilitados com o Azure Synapse Link. Em seguida, você pode saber mais sobre como criar exibições de pool SQL sem servidor em contêineres do Azure Cosmos DB e conectá-las a modelos do Power BI neste tutorial. Este tutorial usa um contêiner com um esquema bem definido do Azure Cosmos DB. Você também pode conferir o módulo Learn sobre como consultar o Azure Cosmos DB com o SQL Serverless para o Azure Synapse Analytics.

Pré-requisitos

• Certifique-se de que o repositório analítico está preparado.
  • Habilite o armazenamento analítico em seus contêineres do Azure Cosmos DB.
  • Obtenha a string de conexão com uma chave de leitura, que pode usar para consultar o armazenamento analítico.
  • Obtenha a chave de leitura única que será usada para aceder ao contentor do Azure Cosmos DB.
• Certifique-se de que aplicou todas as práticas recomendadas, tais como:
  • Verifique se o armazenamento analítico do Azure Cosmos DB está na mesma região do pool SQL sem servidor.
  • Verifique se o aplicativo cliente (Power BI, serviço de análise) está na mesma região do pool SQL sem servidor.
  • 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 sem servidor 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 o Azure Cosmos DB inclui os seguintes componentes:

• account - O nome da conta do Azure Cosmos DB que você está segmentando.
• 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 .

Essas propriedades podem ser identificadas a partir da cadeia de conexão padrão do Cosmos DB, por exemplo:

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

A cadeia de conexão SQL pode ser formatada da seguinte maneira:

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

Essa cadeia de conexão não inclui as informações de autenticação necessárias para se conectar 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. Isso permite que você consulte coleções do Azure Cosmos DB sem precisar preparar credenciais.
• Em vez de incluir informações de autenticação na cadeia de conexão, OPENROWSET pode fazer referência a uma credencial que contém a chave de conta do Azure Cosmos DB. Essa abordagem pode ser usada para criar exibições 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).

Você pode ver a licença e a estrutura dos dados nessas páginas. Também pode descarregar dados de exemplo para os conjuntos de dados ECDC e CORD-19.

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

• Uma conta de banco de dados do Azure Cosmos DB que está ativada para o Azure Synapse Link
• Um banco de dados do Azure Cosmos DB chamado covid
• Dois contêineres do Azure Cosmos DB nomeados Ecdc e Cord19 carregados com os conjuntos de dados de exemplo 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 os dados do Azure Cosmos DB com inferência automática de esquema

A maneira mais fácil de explorar dados no Azure Cosmos DB é usando o recurso de inferência automática de esquema. Ao omitir a cláusula WITH da instrução OPENROWSET, pode-se instruir o pool SQL sem servidor a detetar automaticamente o esquema do repositório analítico do contentor do 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 para se conectar ao covid banco de dados na conta MyCosmosDbAccount do Azure Cosmos DB, autenticada usando a chave do Azure Cosmos DB (o exemplo fictício 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 OPENROWSET função retorna todas as propriedades dos itens do Azure Cosmos DB.

Supondo que os itens no contêiner do Azure Cosmos DB tenham date_rep, casese geo_id propriedades, os resultados dessa consulta são mostrados na tabela a seguir:

Se precisar explorar dados do outro contêiner no mesmo banco de dados do Azure Cosmos DB, você poderá usar a mesma cadeia de conexão e fazer referência ao contêiner necessário como o terceiro parâmetro:

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

Especificar explicitamente o esquema

Embora o recurso de inferência automática de esquema OPENROWSET forneça uma experiência simples e fácil de usar, os seus cenários de negócios podem exigir que você especifique explicitamente o esquema para propriedades relevantes de leitura apenas 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.

Vamos imaginar que importamos alguns dados do conjunto de dados ECDC COVID com a seguinte estrutura para o 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"}

Esses documentos JSON simples 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 obter mais informações sobre os tipos SQL que devem ser usados para valores do Azure Cosmos DB, consulte Mapeamentos de tipo do Azure Cosmos DB para 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, podes preparar uma visualização sobre os teus dados do Azure Cosmos DB. Você deve colocar a chave da sua conta do Azure Cosmos DB em uma credencial separada e fazer referência a essa credencial na 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, você pode representar modelos de dados mais complexos compondo-os como objetos aninhados ou matrizes. O recurso de sincronização automática do Azure Synapse Link para o Azure Cosmos DB faz a gestão da representação de esquema no repositório analítico de forma integrada, o que inclui 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 matrizes no Azure Cosmos DB são representados como cadeias de caracteres JSON no resultado da consulta quando a OPENROWSET função 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 no Azure Synapse Analytics ou Consultar tipos aninhados em arquivos Parquet e JSON usando pool SQL sem servidor.

Nivelar matrizes aninhadas

Os dados do Azure Cosmos DB podem ter subarrays aninhados, como o array do autor de um conjunto de dados 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:

Mapeamentos de tipos entre Azure Cosmos DB e SQL

Embora o repositório transacional do Azure Cosmos DB seja independente do esquema, o repositório analítico é esquematizado para otimizar o desempenho da consulta analítica. Com a capacidade de sincronização automática do Azure Synapse Link, o Azure Cosmos DB é capaz de gerenciar de forma nativa a representação de esquema no repositório analítico, incluindo o tratamento de tipos de dados aninhados. Como um pool SQL sem servidor consulta o repositório analítico, é importante entender como mapear tipos de dados de entrada do Azure Cosmos DB para tipos de dados SQL.

As contas do Azure Cosmos DB da API SQL (Core) suportam tipos de propriedade JSON de número, cadeia de caracteres, booleano, nulo, objeto aninhado ou matriz. Você precisaria escolher tipos SQL que correspondam a esses tipos JSON se estiver usando a WITH cláusula em OPENROWSET. A tabela a seguir mostra os tipos de coluna SQL que devem ser usados para diferentes tipos de propriedade no Azure Cosmos DB.

Esquema de fidelidade total

O esquema de fidelidade total do Azure Cosmos DB registra os valores e seus melhores tipos de correspondência para cada propriedade em um contêiner. 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, você pode ver o tipo identificado em um item de contêiner do Azure Cosmos DB. A maioria dos valores da date_rep propriedade contém date valores, mas alguns deles são armazenados incorretamente como cadeias de caracteres no 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 o Azure Cosmos DB para contas MongoDB, pode aprender mais sobre a representação do esquema de fidelidade total na loja analítica 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, você precisa especificar explicitamente o tipo SQL e o tipo de propriedade esperado do Azure Cosmos DB na WITH cláusula.

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 você tiver valores com outros tipos (cases.int64, cases.float64) que não podem ser limpos em um contêiner do Azure Cosmos DB, precisará fazer referência explícita a eles em uma WITH cláusula 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

Revise a página de autoajuda para encontrar os problemas conhecidos ou as etapas de solução de problemas que podem ajudá-lo a resolver possíveis problemas com consultas do Azure Cosmos DB.

Conteúdos relacionados

• Usar o Power BI e o pool Synapse SQL sem servidor 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
• Módulo: Implementar o Azure Synapse Link com o Azure Cosmos DB