Consultar o Cosmos DB com o SQL do Synapse

5 minutos

Além de usar um pool do Spark, você também pode consultar um contêiner analítico do Azure Cosmos DB usando um pool de SQL sem servidor interno no Azure Synapse Analytics. Para fazer isso, você pode usar a função SQL OPENROWSET para se conectar ao serviço vinculado para o banco de dados do Azure Cosmos DB.

Usar OPENROWSET com uma chave de autenticação

Por padrão, o acesso a uma conta do Azure Cosmos DB é autenticado por uma chave de autenticação. Você pode usar essa chave como parte de uma cadeia de conexão em uma instrução OPENROWSET para se conectar por meio de um serviço vinculado de um pool de SQL, conforme mostrado no seguinte exemplo:

SELECT *
FROM OPENROWSET(
    'CosmosDB',
    'Account=my-cosmos-db;Database=my-db;Key=abcd1234....==',
    [my-container]) AS products_data

Dica

Você pode encontrar uma chave primária e uma secundária para sua conta do Cosmos DB na página Chaves no portal do Azure.

Os resultados dessa consulta podem ser semelhantes aos seguintes, incluindo metadados e campos definidos pelo aplicativo dos itens no contêiner do Azure Cosmos DB:

_rid	_ts	productID	productName	id	_etag
mjMaAL...==	1655414791	123	Widget	7248f072-11c3-42b1-a368-...	54004b09-0000-2300-...
mjMaAL...==	1655414829	124	Wotsit	dc33131c-65c7-421a-a0f7-...	5400ca09-0000-2300-...
mjMaAL...==	1655414835	125	Thingumy	ce22351d-78c7-428a-a1h5-...	5400ca09-0000-2300-...
...	...	...	...	...	...

Os dados são recuperados do repositório analítico e a consulta não afeta o repositório operacional.

Usar OPENROWSET com uma credencial

Em vez de incluir a chave de autenticação em cada chamada para OPENROWSET, você pode definir uma credencial que encapsula as informações de autenticação para sua conta do Cosmos DB e usar a credencial em consultas subsequentes. Para criar uma credencial, use a instrução CREATE CREDENTIAL conforme mostrado neste exemplo:

 CREATE CREDENTIAL my_credential
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = 'abcd1234....==';

Com a credencial definida, você pode usá-la em uma função OPENROWSET desta maneira:

SELECT *
FROM OPENROWSET(PROVIDER = 'CosmosDB',
                CONNECTION = 'Account=my-cosmos-db;Database=my-db',
                OBJECT = 'my-container',
                SERVER_CREDENTIAL = 'my_credential'
) AS products_data

Mais uma vez, os resultados incluem metadados e campos definidos pelo aplicativo do repositório analítico:

_rid	_ts	productID	productName	id	_etag
mjMaAL...==	1655414791	123	Widget	7248f072-11c3-42b1-a368-...	54004b09-0000-2300-...
mjMaAL...==	1655414829	124	Wotsit	dc33131c-65c7-421a-a0f7-...	5400ca09-0000-2300-...
mjMaAL...==	1655414835	125	Thingumy	ce22351d-78c7-428a-a1h5-...	5400ca09-0000-2300-...
...	...	...	...	...	...

Especificar um esquema

A sintaxe OPENROWSET inclui uma cláusula WITH que você pode usar para definir um esquema para o conjunto de linhas resultante. Você pode usar isso para especificar campos individuais e atribuir tipos de dados, conforme mostrado no seguinte exemplo:

 SELECT *
 FROM OPENROWSET(PROVIDER = 'CosmosDB',
                 CONNECTION = 'Account=my-cosmos-db;Database=my-db',
                 OBJECT = 'my-container',
                 SERVER_CREDENTIAL = 'my_credential'
 )
 WITH (
    productID INT,
    productName VARCHAR(20)
 ) AS products_data

Nesse caso, supondo que os campos no repositório analítico incluam productID e productName, o conjunto de linhas resultante será semelhante à seguinte tabela:

productID	productName
123	Widget
124	Wotsit
125	Thingumy
...	...

É claro que você pode especificar nomes de coluna individuais na cláusula SELECT (por exemplo, SELECT productID, productName ...), portanto, pode parecer que a utilidade dessa capacidade de especificar colunas individuais é limitada. No entanto, considere os casos em que os documentos JSON de origem armazenados no repositório operacional incluem vários níveis de campos, conforme mostrado no seguinte exemplo:

{
    "productID": 126,
    "productName": "Sprocket",
    "supplier": {
        "supplierName": "Contoso",
        "supplierPhone": "555-123-4567"
    }
    "id": "62588f072-11c3-42b1-a738-...",
    "_rid": "mjMaAL...==",
    ...
}

A cláusula WITH dá suporte à inclusão de caminhos JSON explícitos, permitindo que você lide com campos aninhados e atribua aliases a nomes de campo; conforme mostrado neste exemplo:

 SELECT *
 FROM OPENROWSET(PROVIDER = 'CosmosDB',
                 CONNECTION = 'Account=my-cosmos-db;Database=my-db',
                 OBJECT = 'my-container',
                 SERVER_CREDENTIAL = 'my_credential'
 )
 WITH (
    ProductNo INT '$.productID',
    ProductName VARCHAR(20) '$.productName',
    Supplier VARCHAR(20) '$.supplier.supplierName',
    SupplierPhoneNo VARCHAR(15) '$.supplier.supplierPhone'
 ) AS products_data

Os resultados dessa consulta incluem a seguinte linha para o produto 126:

ProductNo	ProductName	Fornecedor	SupplierPhoneNo
126	Sprocket	Contoso	555-123-4567

Criar uma exibição em um banco de dados

Se você precisar consultar os mesmos dados com frequência ou precisar usar ferramentas de relatório e visualização que dependem de instruções SELECT que não incluem a função OPENROWSET, você pode usar uma exibição para abstrair os dados. Para criar uma exibição, você deve criar um banco de dados no qual defini-la (não há suporte para exibições definidas pelo usuário no banco de dados mestre), conforme mostrado no seguinte exemplo:

CREATE DATABASE sales_db
   COLLATE Latin1_General_100_BIN2_UTF8;
 GO;

 USE sales_db;
 GO;

 CREATE VIEW products
 AS
 SELECT *
 FROM OPENROWSET(PROVIDER = 'CosmosDB',
                 CONNECTION = 'Account=my-cosmos-db;Database=my-db',
                 OBJECT = 'my-container',
                 SERVER_CREDENTIAL = 'my_credential'
 )
 WITH (
    ProductNo INT '$.productID',
    ProductName VARCHAR(20) '$.productName',
    Supplier VARCHAR(20) '$.supplier.supplierName',
    SupplierPhoneNo VARCHAR(15) '$.supplier.supplierPhone'
 ) AS products_data
 GO

Dica

Ao criar um banco de dados que acessará dados no Cosmos DB, é melhor usar uma ordenação baseada em UTF-8 para garantir a compatibilidade com cadeias de caracteres no Cosmos DB.

Depois que a exibição tiver sido criada, usuários e aplicativos cliente poderão consultá-la como qualquer outra exibição ou tabela SQL:

SELECT * FROM products;

Considerações sobre pools de SQL sem servidor e Azure Cosmos DB

Ao planejar usar um pool de SQL sem servidor para consultar dados em um repositório analítico do Azure Cosmos DB, considere as seguintes práticas recomendadas: