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

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

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

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

Pré-requisitos

• Prepare o repositório analítico:
  • Habilite o armazenamento analítico em seus contêineres do Azure Cosmos DB.
  • Obtenha a cadeia de conexão com uma chave somente leitura que você pode usar para consultar o repositório analítico.
  • Obtenha a chave somente leitura que será usada para acessar o contêiner do Azure Cosmos DB.
• Aplique todas as práticas recomendadas, como:
  • Certifique-se de que o armazenamento analítico do Azure Cosmos DB esteja na mesma região que o pool de SQL sem servidor.
  • Verifique se o aplicativo cliente (Power BI, Analysis Services) está na mesma região que o pool de SQL sem servidor.
  • Ao retornar uma grande quantidade de dados (mais de 80 GB), considere usar uma camada de cache, como Analysis Services, e carregue as partições com menos de 80 GB no modelo de Analysis Services.
  • Ao filtrar dados usando colunas de strings, use a função OPENROWSET com a cláusula WITH explícita que tenha os tipos mais simples possíveis. Por exemplo, não use VARCHAR(1000) se você souber que a propriedade tem até cinco caracteres.

Visão geral

O pool de SQL sem servidor permite consultar o repositório 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 do Azure Cosmos DB inclui os seguintes componentes:

• conta – o nome da conta do Azure Cosmos DB que você está direcionando.
• banco de dados – 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 ([]).
• região (opcional) – a região do armazenamento analítico do Cosmos DB. Caso omitida, a região primária do contêiner será usada.
• ponto de extremidade (opcional) – o URI do ponto de extremidade do Cosmos DB (por exemplo https://<account name>.documents.azure.us) que será necessário se sua conta do Cosmos DB não seguir o formato padrão *.documents.azure.com .

Essas propriedades podem ser identificadas na 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 usado:

• Se OPENROWSET usar a identidade gerenciada do workspace para acessar o repositório analítico, você deverá adicionar a propriedade AuthType.
• Se OPENROWSET usar uma chave de conta embutida, você 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.

Essas 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>

Amostra do conjunto de dados

Os exemplos neste artigo são baseados em dados do Centro Europeu de Prevenção e Controle de Doenças (ECDC) Casos de COVID-19 e do Conjunto de Dados de Pesquisa Aberta COVID-19 (CORD-19).

Você pode ver a licença e a estrutura dos dados nessas páginas. Você também pode baixar uma amostra de dados para os conjuntos ECDC e CORD-19.

Para acompanhar este artigo que mostra como consultar dados do Azure Cosmos DB usando um pool de SQL sem servidor, não deixe de criar os seguintes recursos:

• Uma conta de banco de dados Azure Cosmos DB que esteja habilitada para Azure Synapse Link
• Um banco de dados Azure Cosmos DB chamado covid
• Dois contêineres do Azure Cosmos DB chamados Ecdc e Cord19 carregados com os conjuntos de dados do exemplo anterior

Observe que essa conexão não garante o desempenho, pois a conta pode estar localizada em uma região remota em relação ao ponto de extremidade SQL do Synapse.

Explorar 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, você pode instruir o pool de SQL sem servidor a detectar (inferir) automaticamente o esquema do repositório analítico do contêiner 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 de SQL sem servidor que se conecte ao banco de dados covid na conta do Azure Cosmos DB MyCosmosDbAccount autenticada usando a chave do Azure Cosmos DB (o exemplo fictício no exemplo anterior). Em seguida, acessamos o repositório analítico do contêiner Ecdc na West US 2 região. Como não há projeção de propriedades específicas, a função OPENROWSET retorna todas as propriedades dos itens do Azure Cosmos DB.

Supondo que os itens no contêiner do Azure Cosmos DB tenham as propriedades date_rep, cases e geo_id, os resultados dessa consulta são mostrados na seguinte tabela:

Se você precisar explorar os dados de outro contêiner no mesmo banco de dados do Azure Cosmos DB, poderá usar a mesma cadeia de conexão e referenciar o 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

Apesar de a capacidade de inferência automática de esquema no OPENROWSET fornecer uma experiência simples e fácil de usar, seus cenários de negócios podem exigir que você especifique explicitamente o esquema para ler somente as propriedades relevantes dos dados do Azure Cosmos DB.

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

Suponha que tenhamos importado alguns dados do conjunto de dados sobre COVID do ECDC com a seguinte estrutura no 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 SQL do Synapse. A função OPENROWSET permite especificar um subconjunto de propriedades que você deseja ler e os tipos de coluna exatos na cláusula 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
   

O resultado dessa consulta pode ser semelhante à seguinte tabela:

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

Criar visualização

A criação de exibições nos bancos de dados padrão ou master não é recomendada nem tem suporte. Você precisa criar um banco de dados de usuário para suas visualizações.

Depois de identificar o esquema, você pode preparar uma exibição sobre seus dados do Azure Cosmos DB. Você deve adicionar a chave de conta do Azure Cosmos DB a uma credencial separada e referenciar essa credencial na função OPENROWSET. Não mantenha a chave de conta na definição de exibiçã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 um esquema explicitamente definido, pois isso pode afetar seu desempenho. Use os menores tamanhos possíveis para suas colunas (por exemplo, VARCHAR(100) em vez de VARCHAR(8000) padrão). Você deve usar um agrupamento UTF-8 como agrupamento de banco de dados padrão ou defini-lo como um agrupamento de coluna explícito para evitar um problema de conversão UTF-8. O agrupamento Latin1_General_100_BIN2_UTF8 fornece o melhor desempenho ao filtrar os dados usando algumas colunas de cadeia de caracteres.

Ao consultar a exibição, você pode encontrar erros ou resultados inesperados. O modo de exibição faz referência a colunas ou objetos que provavelmente foram modificados ou não existem mais. Você precisa ajustar manualmente a definição de exibição para se alinhar com as alterações de esquema subjacentes. Tenha em mente que isso pode acontecer ao usar a inferência automática de esquema na exibição 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 ou matrizes aninhados. O recurso de sincronização automática do Link do Azure Synapse para o Azure Cosmos DB gerencia a representação do esquema no repositório analítico pronto para uso, que inclui o tratamento de tipos de dados aninhados que permitem a consulta avançada no pool de 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 as matrizes no Azure Cosmos DB são representados como cadeias de caracteres JSON no resultado da consulta quando a função OPENROWSET os lê. Você pode especificar os caminhos aos valores aninhados nos objetos usando 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 ser semelhante à seguinte tabela:

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

Mesclar matrizes aninhadas

Os dados do Azure Cosmos DB podem ter subconjuntos aninhados como a matriz 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 de SQL sem servidor permite nivelar estruturas aninhadas aplicando a função OPENJSON 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 ser semelhante à seguinte tabela:

Mapeamentos do tipo SQL para o Azure Cosmos DB

Embora o repositório transacional do Azure Cosmos DB seja independente de esquema, o repositório analítico é esquematizado para otimizar o desempenho das consultas analíticas. Com o recurso de sincronização automática do Link do Azure Synapse, o Azure Cosmos DB gerencia a representação do esquema no repositório analítico pronto para uso, que inclui o tratamento de tipos de dados aninhados. Como um pool de SQL sem servidor consulta o repositório analítico, é importante entender como mapear os tipos de dados de entrada do Azure Cosmos DB aos tipos de dados SQL.

As contas do Azure Cosmos DB da API do SQL (Core) dão suporte aos tipos de propriedade JSON de número, cadeia de caracteres, booliano, nulo, objeto aninhado ou matriz. Você precisaria escolher tipos SQL que correspondam a esses tipos JSON se estiver usando a cláusula WITH 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 total fidelidade

O esquema de total fidelidade 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 em um contêiner com esquema de total fidelidade fornece o tipo e o valor real em cada célula. Suponha que a seguinte consulta leia os itens de um contêiner com esquema de total fidelidade:

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 propriedade date_rep contém valores date, mas alguns deles estão armazenados incorretamente como cadeias de caracteres no Azure Cosmos DB. O esquema de fidelidade total retorna os valores date digitados corretamente e os valores string formatados incorretamente.

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

Para consultar as contas do Azure Cosmos DB for Mongo DB, você pode saber mais sobre a representação do esquema com fidelidade total no repositório analítico e os nomes de propriedade estendida a serem usados em O que é o armazenamento analítico do Azure Cosmos DB?.

Itens de consulta com esquema de total fidelidade

Ao consultar o esquema de total fidelidade, você precisa especificar explicitamente o tipo SQL e o tipo de propriedade do Azure Cosmos DB esperado na cláusula WITH.

No exemplo a seguir, presumimos que string é o tipo correto para a propriedade geo_id e int32 é o tipo correto para a propriedade 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

Os valores de geo_id e cases que têm outros tipos são retornados como valores NULL. Essa consulta faz referência somente a 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á referenciá-los explicitamente em uma cláusula WITH e combinar os resultados. A consulta a seguir agrega int32, int64 e float64 armazenados na coluna 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

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

Solução de problemas

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

Conteúdo relacionado

• Usar o Power BI e o pool de SQL do Synapse sem servidor para analisar dados do Azure Cosmos DB
• Criar e usar exibições usando o pool de SQL sem servidor
• Tutorial: Explore e analise lagos de dados com um pool de SQL sem servidor
• Se você estiver recebendo erros ou enfrentando problemas de desempenho, consulte Solucionar problemas de pool de SQL sem servidor
• Módulo: Implementar o Link do Azure Synapse com o Azure Cosmos DB