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:

  • Provisione seu armazenamento analítico do Azure Cosmos DB e quaisquer aplicativos cliente (por exemplo, Microsoft Power BI) na mesma região do pool SQL sem servidor.

    Os contêineres do Azure Cosmos DB podem ser replicados para várias regiões. Se você tiver um contêiner de várias regiões, poderá especificar um region parâmetro na cadeia de conexão OPENROWSET para garantir que as consultas sejam enviadas para uma réplica regional específica do contêiner.

  • Ao trabalhar com colunas de cadeia de caracteres, use a função OPENROWSET com a cláusula WITH explícita e especifique um comprimento de dados apropriado para os dados de cadeia de caracteres.