Como consultar dados aninhados em bancos de dados espelhados no Microsoft Fabric do Azure Cosmos DB (Versão prévia)

Artigo
04/24/2024

Use o banco de dados espelhado no Microsoft Fabric para consultar dados JSON aninhados provenientes do Azure Cosmos DB para NoSQL.

Importante

O espelhamento para o Azure Cosmos DB está atualmente em versão prévia. Não há suporte para cargas de trabalho de produção durante a versão prévia. No momento, há suporte apenas para contas do Azure Cosmos DB for NoSQL.

Pré-requisitos

Uma conta do Azure Cosmos DB for NoSQL.
- Se não tiver uma assinatura do Azure, Experimente o Azure Cosmos DB for NoSQL gratuitamente.
- Se tiver uma assinatura existente do Azure, crie uma conta nova do Azure Cosmos DB for NoSQL.
Uma capacidade do Fabric existente. Se você não tiver uma capacidade existente, inicie uma avaliação do Fabric.
Habilite o espelhamento em seu locatário ou workspace do Fabric. Se o recurso ainda não estiver habilitado, habilite o espelhamento no seu locatário do Fabric.
A conta do Azure Cosmos DB for NoSQL deve ser configurada para espelhamento do Fabric. Para saber mais, veja Requisitos da conta.

Dica

Durante a versão prévia pública, é recomendável usar uma cópia de teste ou desenvolvimento dos dados existentes do Azure Cosmos DB que podem ser recuperados rapidamente de um backup.

Criar dados aninhados no banco de dados de origem

Crie itens JSON em sua conta do Azure Cosmos DB para NoSQL que contenham níveis variados de dados JSON aninhados.

Navegue até sua conta do Azure Cosmos DB no portal do Azure.
Selecione Data Explorer no menu de recursos.
Use + Novo contêiner para criar um novo contêiner. Para este guia, nomeie o contêiner TestC. O nome do banco de dados correspondente é arbitrário.

Use a opção + Novo item várias vezes para criar e salvar esses cinco itens JSON.

{
  "id": "123-abc-xyz",
  "name": "A 13",
  "country": "USA",
  "items": [
    {
      "purchased": "11/23/2022",
      "order_id": "3432-2333-2234-3434",
      "item_description": "item1"
    },
    {
      "purchased": "01/20/2023",
      "order_id": "3431-3454-1231-8080",
      "item_description": "item2"
    },
    {
      "purchased": "02/20/2023",
      "order_id": "2322-2435-4354-2324",
      "item_description": "item3"
    }
  ]
}

{
  "id": "343-abc-def",
  "name": "B 22",
  "country": "USA",
  "items": [
    {
      "purchased": "01/20/2023",
      "order_id": "2431-2322-1545-2322",
      "item_description": "book1"
    },
    {
      "purchased": "01/21/2023",
      "order_id": "3498-3433-2322-2320",
      "item_description": "book2"
    },
    {
      "purchased": "01/24/2023",
      "order_id": "9794-8858-7578-9899",
      "item_description": "book3"
    }
  ]
}

{
  "id": "232-abc-x43",
  "name": "C 13",
  "country": "USA",
  "items": [
    {
      "purchased": "04/03/2023",
      "order_id": "9982-2322-4545-3546",
      "item_description": "clothing1"
    },
    {
      "purchased": "05/20/2023",
      "order_id": "7989-9989-8688-3446",
      "item_description": "clothing2"
    },
    {
      "purchased": "05/27/2023",
      "order_id": "9898-2322-1134-2322",
      "item_description": "clothing3"
    }
  ]
}

{
  "id": "677-abc-yuu",
  "name": "D 78",
  "country": "USA"
}

{
  "id": "979-abc-dfd",
  "name": "E 45",
  "country": "USA"
}

Configurar espelhamento e pré-requisitos

Configure o espelhamento para o banco de dados do Azure Cosmos DB para NoSQL. Se você não tiver certeza de como configurar o espelhamento, consulte o tutorial para configurar banco de dados espelhado.

Navegue até o portal do Fabric.
Crie uma nova conexão e um banco de dados espelhado usando as credenciais da sua conta do Azure Cosmos DB.
Aguarde até que a replicação conclua o instantâneo inicial dos dados.

Consultar dados aninhados básicos

Agora, use o ponto de extremidade de análise do SQL para criar uma consulta que possa manipular dados JSON aninhados simples.

Navegue até o banco de dados espelhado no portal do Fabric.
Alterne do Azure Cosmos DB espelhado para o ponto de extremidade de análise do SQL.
Abra o menu de contexto da tabela de teste e selecione Nova consulta SQL.
Execute esta consulta para expandir a matriz items com OPENJSON. Essa consulta usa OUTER APPLY para incluir itens extras que podem não ter uma matriz de itens.
```
SELECT 
    t.name, 
    t.id, 
    t.country, 
    P.purchased, 
    P.order_id, 
    P.item_description 
FROM OrdersDB_TestC AS t
OUTER APPLY OPENJSON(t.items) WITH
(
    purchased datetime '$.purchased',
    order_id varchar(100) '$.order_id',
    item_description varchar(200) '$.item_description'
) as P
```
Dica

Ao escolher os tipos de dados em OPENJSON, usar varchar(max) para tipos de cadeia de caracteres pode piorar o desempenho da consulta. Em vez disso, use varchar(n) em que n poderia ser qualquer número. Quanto mais baixo for o n, maior a probabilidade de você ver um melhor desempenho da consulta.

Use CROSS APPLY na próxima consulta para mostrar apenas itens com uma matriz items.

SELECT
    t.name,
    t.id,
    t.country,
    P.purchased,
    P.order_id,
    P.item_description 
FROM
    OrdersDB_TestC as t CROSS APPLY OPENJSON(t.items) WITH (
        purchased datetime '$.purchased',
        order_id varchar(100) '$.order_id',
        item_description varchar(200) '$.item_description' 
    ) as P

Criar dados profundamente aninhados

Para aproveitar esse exemplo de dados aninhados, vamos adicionar um exemplo de dados profundamente aninhados.

Navegue até sua conta do Azure Cosmos DB no portal do Azure.
Selecione Data Explorer no menu de recursos.
Use + Novo contêiner para criar um novo contêiner. Para este guia, nomeie o contêiner TestD. O nome do banco de dados correspondente é arbitrário.

Use a opção + Novo item várias vezes para criar e salvar este item JSON.

{
  "id": "eadca09b-e618-4090-a25d-b424a26c2361",
  "entityType": "Package",
  "packages": [
    {
      "packageid": "fiwewsb-f342-jofd-a231-c2321",
      "storageTemperature": "69",
      "highValue": true,
      "items": [
        {
          "id": "1",
          "name": "Item1",
          "properties": {
            "weight": "2",
            "isFragile": "no"
          }
        },
        {
          "id": "2",
          "name": "Item2",
          "properties": {
            "weight": "4",
            "isFragile": "yes"
          }
        }
      ]
    },
    {
      "packageid": "d24343-dfdw-retd-x414-f34345",
      "storageTemperature": "78",
      "highValue": false,
      "items": [
        {
          "id": "3",
          "name": "Item3",
          "properties": {
            "weight": "12",
            "isFragile": "no"
          }
        },
        {
          "id": "4",
          "name": "Item4",
          "properties": {
            "weight": "12",
            "isFragile": "no"
          }
        }
      ]
    }
  ],
  "consignment": {
    "consignmentId": "ae21ebc2-8cfc-4566-bf07-b71cdfb37fb2",
    "customer": "Humongous Insurance",
    "deliveryDueDate": "2020-11-08T23:38:50.875258Z"
  }
}

Consultar dados profundamente aninhados

Finalmente, crie uma consulta T-SQL que possa localizar dados profundamente aninhados em uma cadeia de caracteres JSON.

Abra o menu de contexto da tabela TestD e selecione Nova consulta SQL novamente.

Execute esta consulta para expandir todos os níveis de dados aninhados usando OUTER APPLY com consignação.

SELECT
    P.id,
    R.packageId,
    R.storageTemperature,
    R.highValue,
    G.id,
    G.name,
    H.weight,
    H.isFragile,
    Q.consignmentId,
    Q.customer,
    Q.deliveryDueDate 
FROM
    OrdersDB_TestD as P CROSS APPLY OPENJSON(P.packages) WITH ( packageId varchar(100) '$.packageid',
    storageTemperature INT '$.storageTemperature',
    highValue varchar(100) '$.highValue',
    items nvarchar(MAX) AS JSON ) as R 
OUTER APPLY OPENJSON (R.items) WITH (
    id varchar(100) '$.id',
    name varchar(100) '$.name',
    properties nvarchar(MAX) as JSON 
) as G OUTER APPLY OPENJSON(G.properties) WITH  (
    weight INT '$.weight',
    isFragile varchar(100) '$.isFragile'
) as H OUTER APPLY OPENJSON(P.consignment) WITH  (
    consignmentId varchar(200) '$.consignmentId',
    customer varchar(100) '$.customer',
    deliveryDueDate Date '$.deliveryDueDate'
) as Q

Observação

Ao expandir packages, items é representado como JSON, que pode expandir opcionalmente. A propriedade items tem subpropriedades como JSON, que também podem ser expandidas opcionalmente.

Finalmente, execute uma consulta que escolha quando expandir níveis específicos de aninhamento.

SELECT
    P.id,
    R.packageId,
    R.storageTemperature,
    R.highValue,
    R.items,
    Q.consignmentId,
    Q.customer,
    Q.deliveryDueDate 
FROM
    OrdersDB_TestD as P CROSS APPLY OPENJSON(P.packages) WITH (
        packageId varchar(100) '$.packageid',
        storageTemperature INT '$.storageTemperature',
        highValue varchar(100) '$.highValue',
        items nvarchar(MAX) AS JSON
    ) as R 
OUTER APPLY OPENJSON(P.consignment) WITH  (
    consignmentId varchar(200) '$.consignmentId',
    customer varchar(100) '$.customer',
    deliveryDueDate Date '$.deliveryDueDate'
) as Q

Observação

Os limites de propriedade para níveis aninhados não são impostos nesta experiência de consulta T-SQL.

Compartilhar via

Como consultar dados aninhados em bancos de dados espelhados no Microsoft Fabric do Azure Cosmos DB (Versão prévia)

Pré-requisitos

Criar dados aninhados no banco de dados de origem

Configurar espelhamento e pré-requisitos

Consultar dados aninhados básicos

Criar dados profundamente aninhados

Consultar dados profundamente aninhados

Comentários

Recursos adicionais

Compartilhar via

Como consultar dados aninhados em bancos de dados espelhados no Microsoft Fabric do Azure Cosmos DB (Versão prévia)

Pré-requisitos

Criar dados aninhados no banco de dados de origem

Configurar espelhamento e pré-requisitos

Consultar dados aninhados básicos

Criar dados profundamente aninhados

Consultar dados profundamente aninhados

Conteúdo relacionado

Comentários

Recursos adicionais