Como consultar dados aninhados em bancos de dados espelhados do Microsoft Fabric do Azure Cosmos DB (Visualização)

Artigo
11/08/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 visualização. Não há suporte para cargas de trabalho de produção durante a visualização. Atualmente, apenas contas do Azure Cosmos DB para NoSQL são suportadas.

Pré-requisitos

Uma conta existente do Azure Cosmos DB para NoSQL.
- Se você não tiver uma assinatura do Azure, experimente o Azure Cosmos DB para NoSQL gratuitamente.
- Se você tiver uma assinatura existente do Azure, crie uma nova conta do Azure Cosmos DB para NoSQL.
Uma capacidade de malha existente. Se você não tiver uma capacidade existente, inicie uma avaliação do Fabric.
A conta do Azure Cosmos DB para NoSQL deve ser configurada para espelhamento de malha. Para obter mais informações, consulte Requisitos da conta.

Gorjeta

Durante a visualização 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 TestCcomo . 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"
}

Configuração de 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 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 termine o instantâneo inicial dos dados.

Consultar dados aninhados básicos

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

Navegue até o banco de dados espelhado no portal da malha.
Mude do Azure Cosmos DB espelhado para o ponto de extremidade de análise SQL.
Abra o menu de contexto da tabela de teste e selecione Nova Consulta SQL.
Execute esta consulta para expandir a items matriz com OPENJSON. Esta 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
```
Gorjeta

Ao escolher os tipos de dados no OPENJSON, o uso de varchar(max) tipos de cadeia de caracteres pode piorar o desempenho da consulta. Em vez disso, usar varchar(n) wher n pode ser qualquer número. Quanto mais baixo n , maior a probabilidade de ver um melhor desempenho da consulta.

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

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

Crie dados profundamente aninhados

Para desenvolver este 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 TestDcomo . 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 TestD tabela 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

Nota

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

Por fim, 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

Nota

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

Partilhar via

Como consultar dados aninhados em bancos de dados espelhados do Microsoft Fabric do Azure Cosmos DB (Visualização)

Pré-requisitos

Criar dados aninhados no banco de dados de origem

Configuração de espelhamento e pré-requisitos

Consultar dados aninhados básicos

Crie dados profundamente aninhados

Consultar dados profundamente aninhados

Comentários

Recursos adicionais

Partilhar via

Como consultar dados aninhados em bancos de dados espelhados do Microsoft Fabric do Azure Cosmos DB (Visualização)

Pré-requisitos

Criar dados aninhados no banco de dados de origem

Configuração de espelhamento e pré-requisitos

Consultar dados aninhados básicos

Crie dados profundamente aninhados

Consultar dados profundamente aninhados

Conteúdos relacionados

Comentários

Recursos adicionais