Compartir a través de


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

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

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.

  1. Vaya a la cuenta de Azure Cosmos DB en Azure Portal.

  2. En el menú de recursos, seleccione Explorador de datos.

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

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

  1. Vaya al portal de Fabric.

  2. Cree una nueva conexión y una base de datos reflejada con las credenciales de la cuenta de Azure Cosmos DB.

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

  1. Vaya a la base de datos reflejada en el portal de Fabric.

  2. Cambie del punto de conexión Azure Cosmos DB reflejado a Análisis SQL.

    Captura de pantalla del selector para cambiar entre elementos en el portal de Fabric.

  3. Abra el menú contextual de la tabla Prueba y seleccione Nueva consulta SQL.

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

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

  1. Vaya a la cuenta de Azure Cosmos DB en Azure Portal.

  2. En el menú de recursos, seleccione Explorador de datos.

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

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

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

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

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