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.
Observação
Você não pode usar a identidade gerenciada para acessar um contêiner do Azure Cosmos DB do pool de SQL sem servidor.
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 cadeia de caracteres, use a função
OPENROWSETcom a cláusulaWITHexplícita que tem os menores tipos possíveis. Por exemplo, não useVARCHAR(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.
-
OPENROWSETcom chave em linha. Essa sintaxe pode ser usada para consultar coleções do Azure Cosmos DB sem a necessidade de preparar as credenciais. -
OPENROWSETque faz referência a uma credencial que contém a chave de conta do Azure Cosmos DB. Essa sintaxe pode ser usada para criar exibições nas coleções do Azure Cosmos DB.
Para dar suporte à consulta e à análise de dados em um repositório analítico do Azure Cosmos DB é usado um pool de SQL sem servidor. O pool de SQL sem servidor usa a sintaxe SQL OPENROWSET, portanto, você deve primeiro converter sua cadeia de conexão do Azure Cosmos DB para esse formato:
OPENROWSET(
'CosmosDB',
'<SQL connection string for Azure Cosmos DB>',
<Container name>
) [ < with clause > ] AS alias
A cadeia de conexão SQL para o Azure Cosmos DB especifica o nome da conta do Azure Cosmos DB, o nome do banco de dados, a chave mestra da conta do banco de dados e um nome de região opcional para a função OPENROWSET. Algumas dessas informações podem ser obtidas da cadeia de conexão do Azure Cosmos DB padrão.
Converter do formato de cadeia de conexão do Azure Cosmos DB padrão:
AccountEndpoint=https://<database account name>.documents.azure.com:443/;AccountKey=<database account master key>;
A cadeia de conexão SQL tem o seguinte formato:
'account=<database account name>;database=<database name>;region=<region name>;key=<database account master key>'
A região é opcional. Se omitida, a região primária do contêiner será usada.
Importante
Há outro parâmetro opcional na cadeia de conexão chamada endpoint. O parâmetro endpoint é necessário para contas que não correspondem ao formato padrão *.documents.azure.com. Por exemplo, se a sua conta do Azure Cosmos DB terminar com .documents.azure.us, adicione endpoint=<account name>.documents.azure.us na cadeia de conexão.
O nome do contêiner do Azure Cosmos DB é especificado sem aspas na sintaxe OPENROWSET. Se o nome do contêiner tiver caracteres especiais, por exemplo, um traço (-), o nome deverá ser encapsulado em colchetes ([]) na sintaxe OPENROWSET.
Importante
Verifique se você está usando algum agrupamento de banco de dados UTF-8, por exemplo, Latin1_General_100_CI_AS_SC_UTF8, porque os valores de cadeia de caracteres em um repositório analítico do Azure Cosmos DB são codificados como texto UTF-8.
Uma incompatibilidade entre a codificação de texto no arquivo e no agrupamento pode causar erros inesperados de conversão de texto.
Você pode alterar facilmente o agrupamento padrão do banco de dados atual usando a instrução T-SQL alter database current collate Latin1_General_100_CI_AI_SC_UTF8.
Observação
Um pool de SQL sem servidor não dá suporte à consulta de um repositório transacional do Azure Cosmos DB.
Amostra do conjunto de dados
Os exemplos neste artigo baseiam-se nos dados dos Casos de COVID-19 do Centro Europeu de Prevenção e Controle de Doenças (ECDC) e Conjunto de Dados de Pesquisa Aberta sobre a 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 o Link do Azure Synapse
- Um banco de dados Azure Cosmos DB chamado
covid - Dois contêineres do Azure Cosmos DB chamados
EcdceCord19carregados 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.
Importante
No script, substitua esses valores por seus próprios valores:
- your-cosmosdb - o nome da sua conta do Cosmos DB
- yourcosmosdbkey - sua chave de conta do Cosmos DB
SELECT TOP 10 *
FROM OPENROWSET(
'CosmosDB',
'Account=your-cosmosdb;Database=covid;Key=yourcosmosdbkey',
Ecdc) as documents
No exemplo anterior, instruímos o pool de SQL sem servidor para se conectar ao banco de dados covid na conta do Azure Cosmos DB MyCosmosDbAccount autenticada usando a chave do Azure Cosmos DB (a cópia 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:
| date_rep | cases | geo_id |
|---|---|---|
| 2020-08-13 | 254 | RS |
| 2020-08-12 | 235 | RS |
| 2020-08-11 | 163 | RS |
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 propriedades relevantes somente leitura 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
O resultado dessa consulta pode ser semelhante à seguinte tabela:
| date_rep | cases | geo_id |
|---|---|---|
| 2020-08-13 | 254 | RS |
| 2020-08-12 | 235 | RS |
| 2020-08-11 | 163 | RS |
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 exibiçã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 exibiçõ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. A 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:
| paper_id | título | metadata | authors |
|---|---|---|---|
| bb11206963e831f… | Supplementary Information An eco-epidemi… | {"title":"Supplementary Informati… |
[{"first":"Julien","last":"Mélade","suffix":"","af… |
| bb1206963e831f1… | The Use of Convalescent Sera in Immune-E… | {"title":"The Use of Convalescent… |
[{"first":"Antonio","last":"Lavazza","suffix":"", … |
| bb378eca9aac649… | Tylosema esculentum (Marama) Tuber and B… | {"title":"Tylosema esculentum (Ma… |
[{"first":"Walter","last":"Chingwaru","suffix":"",… |
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.
Importante
Se você vir caracteres inesperados em seu texto, como Mélade em vez de Mélade, o agrupamento do banco de dados não será definido como um agrupamento Mélade. Você pode alterar o agrupamento do banco de dados para um agrupamento UTF-8 usando uma instrução SQL como ALTER DATABASE MyLdw COLLATE LATIN1_GENERAL_100_CI_AS_SC_UTF8.
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:
| título | authors | first | last | affiliation |
|---|---|---|---|---|
| Supplementary Information An eco-epidemi… | [{"first":"Julien","last":"Mélade","suffix":"","affiliation":{"laboratory":"Centre de Recher… |
Julien | Mélade | {"laboratory":"Centre de Recher… |
| Supplementary Information An eco-epidemi… | [{"first":"Nicolas","last":"4#","suffix":"","affiliation":{"laboratory":"","institution":"U… |
Nicolas | 4# | {"laboratory":"","institution":"U… |
| Supplementary Information An eco-epidemi… | [{"first":"Beza","last":"Ramazindrazana","suffix":"","affiliation":{"laboratory":"Centre de Recher… |
Beza | Ramazindrazana | {"laboratory":"Centre de Recher… |
| Supplementary Information An eco-epidemi… | [{"first":"Olivier","last":"Flores","suffix":"","affiliation":{"laboratory":"UMR C53 CIRAD, … |
Olivier | Flores | {"laboratory":"UMR C53 CIRAD, … |
Importante
Se você vir caracteres inesperados em seu texto, como Mélade em vez de Mélade, o agrupamento do banco de dados não será definido como um agrupamento Mélade. Você pode alterar o agrupamento do banco de dados para um agrupamento UTF-8 usando uma instrução SQL como ALTER DATABASE MyLdw COLLATE LATIN1_GENERAL_100_CI_AS_SC_UTF8.
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.
| Tipo de propriedade do Azure Cosmos DB | Tipo de coluna SQL |
|---|---|
| Booliano | bit |
| Integer | BIGINT |
| Decimal | FLOAT |
| String | varchar (agrupamento do banco de dados UTF-8) |
| Data e hora (cadeia de caracteres formatada em ISO) | varchar(30) |
| Data e hora (carimbo de data/hora UNIX) | BIGINT |
| Nulo | any SQL type |
| Objeto ou matriz aninhada | varchar(max) (agrupamento do banco de dados UTF-8), serializado como texto JSON |
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:
| date_rep | cases | geo_id |
|---|---|---|
| {"date":"2020-08-13"} | {"int32":"254"} | {"string":"RS"} |
| {"date":"2020-08-12"} | {"int32":"235"} | {"string":"RS"} |
| {"date":"2020-08-11"} | {"int32":"316"} | {"string":"RS"} |
| {"date":"2020-08-10"} | {"int32":"281"} | {"string":"RS"} |
| {"date":"2020-08-09"} | {"int32":"295"} | {"string":"RS"} |
| {"string":"2020/08/08"} | {"int32":"312"} | {"string":"RS"} |
| {"date":"2020-08-07"} | {"float64":"339.0"} | {"string":"RS"} |
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.
Importante
A função OPENROWSET sem uma cláusula WITH expõe os valores com os tipos esperados e os valores com tipos inseridos incorretamente. Essa função foi projetada para exploração de dados e não para geração de relatórios. Não analise os valores JSON retornados dessa função para gerar relatórios. Use uma cláusula WITH explícita para gerar relatórios. Você deve limpar os valores com tipos incorretos no contêiner do Azure Cosmos DB para aplicar correções no repositório analítico de fidelidade total.
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: Explorar e analisar data lakes com o 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