Consultar o Cosmos DB com Synapse SQL

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 SQL interno sem servidor no Azure Synapse Analytics. Para fazer isso, você pode usar a OPENROWSET função SQL para se conectar ao serviço vinculado para seu banco de dados do Azure Cosmos DB.

Usando 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 OPENROWSET instrução para se conectar por meio de um serviço vinculado de um pool SQL, conforme mostrado no exemplo a seguir:

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

Gorjeta

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

Os resultados dessa consulta podem ser parecidos com os 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.

Usando 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 CREATE CREDENTIAL instrução como mostrado neste exemplo:

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

Com a credencial instalada, você pode usá-la em uma OPENROWSET função como esta:

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

Especificando um esquema

A OPENROWSET sintaxe inclui uma WITH cláusula 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 exemplo a seguir:

 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 à tabela a seguir:

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

É claro que você pode especificar nomes de colunas individuais na SELECT cláusula (por exemplo, ), portanto, SELECT productID, productName ...essa capacidade de especificar colunas individuais pode parecer de uso limitado. No entanto, considere os casos em que os documentos JSON de origem armazenados no repositório operacional incluem vários níveis de campos, como mostra o exemplo a seguir:

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

A WITH cláusula suporta a inclusão de caminhos JSON explícitos, permitindo que você manipule campos aninhados e atribua aliases a nomes de campos, 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 incluiriam a seguinte linha para o produto 126:

ProdutoNão	ProductName	Fornecedor	FornecedorTelefoneNão
126	Roda dentada	Contoso	555-123-4567

Criando um modo de exibição em um banco de dados

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

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

Gorjeta

Ao criar um banco de dados que acessará dados no Cosmos DB, é melhor usar um agrupamento baseado em UTF-8 para garantir a compatibilidade com cadeias de caracteres no Cosmos DB.

Depois que o modo de exibição for criado, os usuários e aplicativos cliente poderão consultá-lo como qualquer outro modo de exibição ou tabela SQL:

SELECT * FROM products;

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

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