Instrucciones: Consulta de datos anidados en bases de datos reflejadas de Microsoft Fabric desde Azure Cosmos DB (versión preliminar)

Artículo
11/08/2024

Utilice la base de datos reflejada en Microsoft Fabric para consultar datos JSON anidados con origen en Azure Cosmos DB for NoSQL.

Importante

La creación de reflejo para Azure Cosmos DB está actualmente en versión preliminar. No se admiten cargas de trabajo de producción durante la versión preliminar. Actualmente, solo se admiten cuentas de Azure Cosmos DB for NoSQL.

Requisitos previos

Una cuenta existente de Azure Cosmos DB for NoSQL.
- Si no tiene una suscripción de Azure, pruebe Azure Cosmos DB for NoSQL gratis.
- Si tiene una suscripción de Azure existente, cree una cuenta de Azure Cosmos DB for NoSQL.
Una capacidad de Fabric existente. Si no tiene una capacidad existente, inicie una versión de prueba de Fabric.
La cuenta de Azure Cosmos DB for NoSQL debe configurarse para la creación de reflejo de Fabric. Para obtener más información, consulte Requisitos de la cuenta.

Sugerencia

En la versión preliminar pública, se recomienda usar una copia de prueba o desarrollo de los datos existentes de Azure Cosmos DB que se pueda recuperar rápidamente de una copia de seguridad.

Creación de datos anidados en la base de datos de origen

Cree elementos JSON en la cuenta de Azure Cosmos DB for NoSQL que contengan varios niveles de datos JSON anidados.

Vaya a la cuenta de Azure Cosmos DB en Azure Portal.
En el menú de recursos, seleccione Explorador de datos.
Seleccione + nuevo contenedor para crear un nuevo contenedor. En esta guía, ponga un nombre al contenedor TestC. El nombre de la base de datos correspondiente es arbitrario.

Use la opción + nuevo elemento varias veces para crear y guardar estos cinco elementos 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"
}

Configuración de la creación de reflejo y requisitos previos

Configure la creación de reflejo para la base de datos de Azure Cosmos DB for NoSQL. Si no está seguro de cómo configurar la creación de reflejo, consulte el tutorial para configurar bases de datos reflejadas.

Vaya al portal de Fabric.
Cree una nueva conexión y una base de datos reflejada con las credenciales de la cuenta de Azure Cosmos DB.
Espere a que la replicación finalice la instantánea inicial de los datos.

Consulta de datos anidados básicos

Utilice el punto de conexión de análisis SQL para crear una consulta que permita manipular datos JSON anidados simples.

Vaya a la base de datos reflejada en el portal de Fabric.
Cambie del punto de conexión Azure Cosmos DB reflejado a Análisis SQL.
Abra el menú contextual de la tabla Prueba y seleccione Nueva consulta SQL.
Ejecute esta consulta para expandir la matriz items con OPENJSON. Esta consulta utiliza OUTER APPLY para incluir elementos adicionales que podrían no tener una matriz de elementos.
```
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
```
Sugerencia

Al elegir los tipos de datos en OPENJSON, utilizar varchar(max) para los tipos de cadena podría empeorar el rendimiento de las consultas. En su lugar, use varchar(n), donde n podría ser cualquier número. Cuanto menor sea n, más probable es que mejore el rendimiento de las consultas.

Utilice CROSS APPLY en la siguiente consulta para mostrar solo elementos con una 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

Creación de datos profundamente anidados

Para describir este ejemplo de datos anidados, agregaremos un ejemplo de datos profundamente anidados.

Vaya a la cuenta de Azure Cosmos DB en Azure Portal.
En el menú de recursos, seleccione Explorador de datos.
Seleccione + nuevo contenedor para crear un nuevo contenedor. En esta guía, ponga un nombre al contenedor TestD. El nombre de la base de datos correspondiente es arbitrario.

Use la opción + nuevo elemento varias veces para crear y guardar este elemento 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"
  }
}

Consulta de datos profundamente anidados

Por último, cree una consulta de T-SQL que permita buscar datos profundamente anidados en una cadena JSON.

Abra el menú contextual de la tabla TestD y vuelva a seleccionar Nueva consulta SQL.

Ejecute esta consulta para expandir todos los niveles de datos anidados mediante OUTER APPLY con enví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:

Al expandir packages, items se representa como JSON y puede expandirse opcionalmente. La propiedad items tiene subpropiedades como JSON, que también pueden expandirse opcionalmente.

Por último, ejecute una consulta que elija cuándo expandir niveles específicos de anidamiento.

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:

Los límites de propiedades para los niveles anidados no se aplican en esta experiencia de consulta T-SQL.

Compartir vía

Instrucciones: Consulta de datos anidados en bases de datos reflejadas de Microsoft Fabric desde Azure Cosmos DB (versión preliminar)

Requisitos previos

Creación de datos anidados en la base de datos de origen

Configuración de la creación de reflejo y requisitos previos

Consulta de datos anidados básicos

Creación de datos profundamente anidados

Consulta de datos profundamente anidados

Comentarios

Recursos adicionales

Compartir vía

Instrucciones: Consulta de datos anidados en bases de datos reflejadas de Microsoft Fabric desde Azure Cosmos DB (versión preliminar)

Requisitos previos

Creación de datos anidados en la base de datos de origen

Configuración de la creación de reflejo y requisitos previos

Consulta de datos anidados básicos

Creación de datos profundamente anidados

Consulta de datos profundamente anidados

Contenido relacionado

Comentarios

Recursos adicionales