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
- 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.
- Habilite la creación de reflejo en el espacio de trabajo o el suscriptor de Fabric. Si la característica aún no está habilitada, habilite la creación de reflejo en el suscriptor 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
conOPENJSON
. Esta consulta utilizaOUTER 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
, utilizarvarchar(max)
para los tipos de cadena podría empeorar el rendimiento de las consultas. En su lugar, usevarchar(n)
, donden
podría ser cualquier número. Cuanto menor sean
, más probable es que mejore el rendimiento de las consultas.Utilice
CROSS APPLY
en la siguiente consulta para mostrar solo elementos con una matrizitems
.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 propiedaditems
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.