Consulta de Cosmos DB con Synapse SQL

  • 5 minutos

Además de usar un grupo de Spark, también puede consultar un contenedor analítico de Azure Cosmos DB mediante un grupo de SQL sin servidor integrado en Azure Synapse Analytics. Para ello, puede usar la función SQL OPENROWSET para conectarse al servicio vinculado de la base de datos de Azure Cosmos DB.

Uso de OPENROWSET con una clave de autenticación

De manera predeterminada, el acceso a una cuenta de Azure Cosmos DB se autentica mediante una clave de autenticación. Puede usar esta clave como parte de una cadena de conexión en una instrucción OPENROWSET para conectarse mediante un servicio vinculado desde un grupo de SQL, como se muestra en el ejemplo siguiente:

SELECT *
FROM OPENROWSET(​
    'CosmosDB',
    'Account=my-cosmos-db;Database=my-db;Key=abcd1234....==',
    [my-container]) AS products_data

Sugerencia

Puede encontrar una clave principal y secundaria para la cuenta de Cosmos DB en la página Claves de Azure Portal.

Los resultados de esta consulta pueden tener un aspecto similar al siguiente, incluidos los metadatos y los campos definidos por la aplicación de los elementos del contenedor de Azure Cosmos DB:

_rid _ts productID ProductName id _etag
mjMaAL...== 1655414791 123 Widget 7248f072-11c3-42b1-a368-... 54004b09-0000-2300-...
mjMaAL...== 1655414829 124 Wotsit dc33131c-65c7-421a-a0f7-... 5400ca09-0000-2300-...
mjMaAL...== 1655414835 125 Thingumy ce22351d-78c7-428a-a1h5-... 5400ca09-0000-2300-...
... ... ... ... ... ...

Los datos se recuperan del almacén analítico y la consulta no afecta al almacén operativo.

Uso de OPENROWSET con una credencial

En lugar de incluir la clave de autenticación en cada llamada a OPENROWSET, puede definir una credencial que encapsula la información de autenticación de la cuenta de Cosmos DB y usar la credencial en consultas posteriores. Para crear una credencial, use la instrucción CREATE CREDENTIAL como se muestra en este ejemplo:

 CREATE CREDENTIAL my_credential
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = 'abcd1234....==';

Con la credencial en contexto, puede usarla en una función OPENROWSET como esta:

SELECT *
FROM OPENROWSET(​PROVIDER = 'CosmosDB',
                CONNECTION = 'Account=my-cosmos-db;Database=my-db',
                OBJECT = 'my-container',
                SERVER_CREDENTIAL = 'my_credential'
) AS products_data

Una vez más, los resultados incluyen metadatos y campos definidos por la aplicación del almacén analítico:

_rid _ts productID ProductName id _etag
mjMaAL...== 1655414791 123 Widget 7248f072-11c3-42b1-a368-... 54004b09-0000-2300-...
mjMaAL...== 1655414829 124 Wotsit dc33131c-65c7-421a-a0f7-... 5400ca09-0000-2300-...
mjMaAL...== 1655414835 125 Thingumy ce22351d-78c7-428a-a1h5-... 5400ca09-0000-2300-...
... ... ... ... ... ...

Especificación de un esquema

La sintaxis OPENROWSET incluye una cláusula WITH que puede usar para definir un esquema para el conjunto de filas resultante. Puede usarlo para especificar campos individuales y asignar tipos de datos como se muestra en el ejemplo siguiente:

 SELECT *
 FROM OPENROWSET(​PROVIDER = 'CosmosDB',
                 CONNECTION = 'Account=my-cosmos-db;Database=my-db',
                 OBJECT = 'my-container',
                 SERVER_CREDENTIAL = 'my_credential'
 )
 WITH (
    productID INT,
    productName VARCHAR(20)
 ) AS products_data

En este caso, suponiendo que los campos del almacén analítico incluyan productID y productName, el conjunto de filas resultante será similar a la tabla siguiente:

productID ProductName
123 Widget
124 Wotsit
125 Thingumy
... ...

Por supuesto, puede especificar nombres de columna individuales en la cláusula SELECT (por ejemplo, SELECT productID, productName ...), por lo que esta capacidad para especificar columnas individuales puede parecer de uso limitado. Sin embargo, considere los casos en los que los documentos JSON de origen almacenados en el almacén operativo incluyen varios niveles de campos, como se muestra en el ejemplo siguiente:

{
    "productID": 126,
    "productName": "Sprocket",
    "supplier": {
        "supplierName": "Contoso",
        "supplierPhone": "555-123-4567"
    }
    "id": "62588f072-11c3-42b1-a738-...",
    "_rid": "mjMaAL...==",
    ...
}

La cláusula WITH admite la inclusión de rutas de acceso JSON explícitas, lo que le permite controlar los campos anidados y asignar alias a nombres de campo; como se muestra en este ejemplo:

 SELECT *
 FROM OPENROWSET(​PROVIDER = 'CosmosDB',
                 CONNECTION = 'Account=my-cosmos-db;Database=my-db',
                 OBJECT = 'my-container',
                 SERVER_CREDENTIAL = 'my_credential'
 )
 WITH (
    ProductNo INT '$.productID',
    ProductName VARCHAR(20) '$.productName',
    Supplier VARCHAR(20) '$.supplier.supplierName',
    SupplierPhoneNo VARCHAR(15) '$.supplier.supplierPhone'
 ) AS products_data

Los resultados de esta consulta incluirían la siguiente fila para el producto 126:

ProductNo ProductName Supplier SupplierPhoneNo
126 Sprocket Contoso 555-123-4567

Creación de una vista en una base de datos

Si necesita consultar los mismos datos con frecuencia o necesita usar herramientas de creación de informes y visualización que dependen de instrucciones SELECT que no incluyen la función OPENROWSET, puede usar una vista para abstraer los datos. Para crear una vista, debe crear una nueva base de datos en la que definirla (no se admiten vistas definidas por el usuario en la base de datos maestra), como se muestra en el ejemplo siguiente:

CREATE DATABASE sales_db
   COLLATE Latin1_General_100_BIN2_UTF8;
 GO;

 USE sales_db;
 GO;

 CREATE VIEW products
 AS
 SELECT *
 FROM OPENROWSET(​PROVIDER = 'CosmosDB',
                 CONNECTION = 'Account=my-cosmos-db;Database=my-db',
                 OBJECT = 'my-container',
                 SERVER_CREDENTIAL = 'my_credential'
 )
 WITH (
    ProductNo INT '$.productID',
    ProductName VARCHAR(20) '$.productName',
    Supplier VARCHAR(20) '$.supplier.supplierName',
    SupplierPhoneNo VARCHAR(15) '$.supplier.supplierPhone'
 ) AS products_data
 GO

Sugerencia

Al crear una base de datos que vaya a acceder a los datos de Cosmos DB, es mejor usar una intercalación basada en UTF-8 para garantizar la compatibilidad con cadenas en Cosmos DB.

Una vez creada la vista, los usuarios y las aplicaciones cliente pueden consultarla como cualquier otra vista o tabla de SQL:

SELECT * FROM products;

Consideraciones sobre los grupos de SQL sin servidor y Azure Cosmos DB

Al planear el uso de un grupo de SQL sin servidor para consultar datos en un almacén analítico de Azure Cosmos DB, tenga en cuenta estos procedimientos recomendados:

  • Aprovisione el almacenamiento analítico de Azure Cosmos DB y todas las aplicaciones cliente (por ejemplo, Microsoft Power BI) en la misma región que el grupo de SQL sin servidor.

    Los contenedores de Azure Cosmos DB se pueden replicar en varias regiones. Si tiene un contenedor de varias regiones, puede especificar un parámetro region en la cadena de conexión OPENROWSET para asegurarse de que las consultas se envían a una réplica regional específica del contenedor.

  • Al trabajar con columnas de cadena, use la función OPENROWSET con la cláusula WITH explícita y especifique una longitud de datos adecuada para los datos de cadena.