Consulta de datos de Azure Cosmos DB mediante un grupo SQL sin servidor

Un grupo de SQL sin servidor permite analizar datos en los contenedores de Azure Cosmos DB habilitados con Azure Synapse Link casi en tiempo real sin afectar al rendimiento de las cargas de trabajo transaccionales. Ofrece una sintaxis conocida de Transact-SQL (T-SQL) para consultar datos desde el almacén analítico analytical store y conectividad integrada con una amplia gama de herramientas de inteligencia empresarial (BI) y consulta ad hoc a través de la interfaz T-SQL.

Para consultar Azure Cosmos DB, se admite el área expuesta SELECT completa a través de la función OPENROWSET, que incluye la mayoría de las funciones y operadores SQL. También puede almacenar los resultados de la consulta que lee datos de Azure Cosmos DB junto con los datos de Azure Blob Storage o Azure Data Lake Storage mediante crear tabla externa como select (CETAS). Actualmente no se pueden almacenar los resultados de las consultas de un grupo de SQL sin servidor en Azure Cosmos DB mediante CETAS.

En este artículo se explica cómo escribir una consulta con un grupo de SQL sin servidor que consulta datos de contenedores de Azure Cosmos DB habilitados con Azure Synapse Link. A continuación, puede obtener más información sobre la creación de vistas de grupos de SQL sin servidor a través de contenedores de Azure Cosmos DB y conectarlas a modelos de Power BI en este tutorial. En este tutorial se usa un contenedor con un esquema Azure Cosmos DB bien definido. También puede consultar el módulo de Learn sobre cómo consultar Azure Cosmos DB con SQL sin servidor para Azure Synapse Analytics.

Requisitos previos

• Asegúrese de preparar el almacén analítico:
  • Habilite el almacén analítico en los contenedores de Azure Cosmos DB.
  • Obtenga la cadena de conexión con una clave de solo lectura que usará para consultar el almacén analítico.
  • Obtenga la clave de solo lectura que se usará para acceder al contenedor de Azure Cosmos DB.
• Asegúrese de que ha aplicado todos los procedimientos recomendados, por ejemplo:
  • Asegúrese de que el almacenamiento analítico de Azure Cosmos DB está en la misma región que el grupo de SQL sin servidor.
  • Asegúrese de que la aplicación cliente (Power BI, Analysis Service) esté en la misma región que el grupo de SQL sin servidor.
  • Si va a devolver una gran cantidad de datos (más de 80 GB), considere la posibilidad de usar la capa de almacenamiento en caché, como Analysis Services, y cargar las particiones menores de 80 GB en el modelo de Analysis Services.
  • Si va a filtrar datos mediante columnas de cadena, asegúrese de usar la función con la cláusula explícita que tenga los tipos más pequeños posibles. Por ejemplo, no use si sabe que la propiedad tiene hasta cinco caracteres.

Información general

El grupo SQL sin servidor permite consultar el almacenamiento analítico de Azure Cosmos DB mediante la función OPENROWSET.

OPENROWSET( 
       'CosmosDB',
       '<SQL connection string for Azure Cosmos DB>',
       <other parameters>
    )  [ < with clause > ] AS alias

El connection string de SQL para Azure Cosmos DB incluye los siguientes componentes:

• account: el nombre de la cuenta de Azure Cosmos DB de destino.
• database : el nombre del contenedor, especificado sin comillas en la sintaxis OPENROWSET. Si el nombre del contenedor tiene caracteres especiales (por ejemplo, un guion -), debe incluirse entre corchetes ([]).
• región (opcional): la región del almacenamiento analítico de Cosmos DB. Si se omite, se usa la región primaria del contenedor.
• punto de conexión (opcional): la URI del punto de conexión de Cosmos DB (por ejemplo, ) que se requiere si su cuenta de Cosmos DB no sigue el formato estándar .

Estas propiedades se pueden identificar desde el connection string estándar de Cosmos DB, por ejemplo:

AccountEndpoint=https://<database account name>.documents.azure.com:443/;AccountKey=<database account master key>;

El connection string SQL se puede dar formato de la siguiente manera:

account=<database account name>;database=<database name>;region=<region name>

Este connection string no incluye la información de autenticación necesaria para conectarse al almacenamiento analítico de Cosmos DB. Se necesita información adicional en función del tipo de autenticación utilizado:

• Si usa la identidad administrada del área de trabajo para acceder al almacén analítico, se debe agregar la propiedad .
• Si usa una clave de cuenta en línea, debe agregar la propiedad . Esto le permite consultar Azure Cosmos DB colecciones sin necesidad de preparar las credenciales.
• En lugar de incluir información de autenticación en el connection string, OPENROWSET puede hacer referencia a una credencial que contiene la clave de cuenta de Azure Cosmos DB. Este enfoque se puede usar para crear vistas en colecciones de Azure Cosmos DB.

Estas opciones se describen a continuación.

OPENROWSET( 
       'CosmosDB',
       '<SQL connection string for Azure Cosmos DB>',
       <Container name>
    )  [ < with clause > ] AS alias

OPENROWSET( 
       PROVIDER = 'CosmosDB',
       CONNECTION = '<SQL connection string for Azure Cosmos DB without account key>',
       OBJECT = '<Container name>',
       [ CREDENTIAL | SERVER_CREDENTIAL ] = '<credential name>'
    )  [ < with clause > ] AS alias

account=<database account name>;database=<database name>;region=<region name>

Conjunto de datos de ejemplo

Los ejemplos de este artículo se basan en datos del Centro Europeo de Prevención y Control de Enfermedades (ECDC) Casos de COVID-19 y Conjunto de datos de investigación abierta de COVID-19 (CORD-19) .

Para seguir este artículo que muestra cómo consultar datos Azure Cosmos DB con un grupo de SQL sin servidor, asegúrese de crear los siguientes recursos:

• Una cuenta de base de datos de Azure Cosmos DB que tiene habilitado Azure Synapse Link
• Una base de datos de Azure Cosmos DB denominada covid
• Dos contenedores de Azure Cosmos DB denominados Ecdc y Cord19 cargados con los conjuntos de datos de ejemplo anteriores

Tenga en cuenta que esta conexión no garantizará el rendimiento porque esta cuenta podría estar ubicada en una región remota en comparación con el punto de conexión de Synapse SQL.

Exploración de datos Azure Cosmos DB con inferencia automática de esquemas

La manera más fácil de explorar los datos en Azure Cosmos DB es mediante la funcionalidad de inferencia automática de esquemas. Al omitir la cláusula WITH de la instrucción OPENROWSET, puede indicar al grupo de SQL sin servidor que detecte automáticamente (infiera) el esquema del almacén analítico del contenedor de Azure Cosmos DB.

SELECT TOP 10 *
FROM OPENROWSET( 
       'CosmosDB',
       'Account=your-cosmosdb;Database=covid;Key=yourcosmosdbkey',
       Ecdc) as documents

/*  Setup - create server-level or database scoped credential with Azure Cosmos DB account key:
    CREATE CREDENTIAL MyCosmosDbAccountCredential
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'yourcosmosdbkey';
*/
SELECT TOP 10 *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=your-cosmosdb;Database=covid',
      OBJECT = 'Ecdc',
      SERVER_CREDENTIAL = 'MyCosmosDbAccountCredential'
    ) with ( date_rep varchar(20), cases bigint, geo_id varchar(6) ) as rows

En el ejemplo anterior, se ha indicado al grupo de SQL sin servidor que se conecte a la base de datos covid de la cuenta de Azure Cosmos DB MyCosmosDbAccount autenticada mediante la clave Azure Cosmos DB (el ficticio del ejemplo anterior). Después, accedimos al almacén analítico del contenedor en la región . Dado que no hay ninguna proyección de propiedades específicas, la función OPENROWSET devuelve todas las propiedades de los elementos de Azure Cosmos DB.

Suponiendo que los elementos del contenedor de Azure Cosmos DB tengan propiedades date_rep, cases y geo_id, los resultados de esta consulta se muestran en la tabla siguiente:

Si necesita explorar datos del otro contenedor en la misma base de datos de Azure Cosmos DB, puede usar la misma connection string y hacer referencia al contenedor necesario como tercer parámetro:

SELECT TOP 10 *
FROM OPENROWSET( 
       'CosmosDB',
       'Account=your-cosmosdb;Database=covid;Key=yourcosmosdbkey',
       Cord19) as cord19

Especificación explícita del esquema

Aunque la funcionalidad de inferencia de esquema automática en OPENROWSET proporciona una experiencia fácil de usar, es posible que para escenarios empresariales tenga que especificar de forma explícita el esquema para leer solo las propiedades pertinentes de los datos de Azure Cosmos DB.

La función le permite especificar explícitamente qué propiedades desea leer de los datos del contenedor y especificar sus tipos de datos.

Imaginemos que hemos importado algunos datos del conjunto de datos ECDC COVID con la siguiente estructura en Azure Cosmos DB:

{"date_rep":"2020-08-13","cases":254,"countries_and_territories":"Serbia","geo_id":"RS"}
{"date_rep":"2020-08-12","cases":235,"countries_and_territories":"Serbia","geo_id":"RS"}
{"date_rep":"2020-08-11","cases":163,"countries_and_territories":"Serbia","geo_id":"RS"}

Estos documentos JSON planos en Azure Cosmos DB se pueden representar como un conjunto de filas y columnas en Synapse SQL. La función le permite especificar un subconjunto de propiedades que quiere leer y los tipos de columna exactos en la cláusula :

SELECT TOP 10 *
FROM OPENROWSET(
      'CosmosDB',
      'Account=your-cosmosdb;Database=covid;Key=yourcosmosdbkey',
       Ecdc
    ) with ( date_rep varchar(20), cases bigint, geo_id varchar(6) ) as rows

/*  Setup - create server-level or database scoped credential with Azure Cosmos DB account key:
    CREATE CREDENTIAL MyCosmosDbAccountCredential
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'yourcosmosdbkey';
*/
SELECT TOP 10 *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=your-cosmosdb;Database=covid',
      OBJECT = 'Ecdc',
      SERVER_CREDENTIAL = 'MyCosmosDbAccountCredential'
    ) with ( date_rep varchar(20), cases bigint, geo_id varchar(6) ) as rows
   

El resultado de esta consulta podría ser similar al de la tabla siguiente:

Para obtener más información sobre los tipos SQL que se deben usar para los valores de Azure Cosmos DB, consulte Azure Cosmos DB a las asignaciones de tipos SQL al final de este artículo.

Creación de vistas

No se recomienda ni admite la creación de vistas en las bases de datos o predeterminadas. Por lo tanto, debe crear una base de datos de usuario para las vistas.

Una vez que identifique el esquema, puede preparar una vista sobre los datos de Azure Cosmos DB. Debe colocar la clave de cuenta de Azure Cosmos DB en una credencial independiente y hacer referencia a esta credencial desde la función OPENROWSET. No mantenga la clave de cuenta en la definición de vista.

CREATE CREDENTIAL MyCosmosDbAccountCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'yourcosmosdbkey';
GO
CREATE OR ALTER VIEW Ecdc
AS SELECT *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=your-cosmosdb;Database=covid',
      OBJECT = 'Ecdc',
      SERVER_CREDENTIAL = 'MyCosmosDbAccountCredential'
    ) with ( date_rep varchar(20), cases bigint, geo_id varchar(6) ) as rows

No use sin esquema definido explícitamente porque podría afectar al rendimiento. Asegúrese de usar los tamaños más pequeños posibles para las columnas (por ejemplo, en lugar de predeterminado). Debe usar una intercalación UTF-8 como intercalación de base de datos predeterminada o establecerla como intercalación de columna explícita para evitar un problema de conversión UTF-8. La intercalación proporciona el mejor rendimiento cuando se filtran los datos mediante algunas columnas de cadena.

Al consultar la vista, es posible que encuentre errores o resultados inesperados. Los objetos o columnas a los que hace referencia la vista probablemente se modificaron o ya no existen. Debe ajustar manualmente la definición de vista para alinearse con los cambios de esquema subyacentes. Tenga en cuenta que esto puede ocurrir al usar la inferencia automática de esquemas en la vista y al especificar explícitamente el esquema.

Consulta de objetos anidados

Con Azure Cosmos DB, puede representar modelos de datos más complejos componiendolos como objetos anidados o matrices. La funcionalidad de sincronización automática de Azure Synapse Link para Azure Cosmos DB administra la representación de esquema en el almacén analítico de fábrica, que incluye el control de tipos de datos anidados que permiten realizar consultas enriquecidas desde el grupo de SQL sin servidor.

Por ejemplo, el conjunto de datos CORD-19 tiene documentos JSON con esta estructura:

{
    "paper_id": <str>,                   # 40-character sha1 of the PDF
    "metadata": {
        "title": <str>,
        "authors": <array of objects>    # list of author dicts, in order
        ...
     }
     ...
}

Los objetos y matrices anidados de Azure Cosmos DB se representan como cadenas JSON en el resultado de la consulta cuando la función />

SELECT TOP 10 *
FROM OPENROWSET( 
       'CosmosDB',
       'Account=your-cosmosdb;Database=covid;Key=yourcosmosdbkey',
       Cord19)
WITH (  paper_id    varchar(8000),
        title        varchar(1000) '$.metadata.title',
        metadata     varchar(max),
        authors      varchar(max) '$.metadata.authors'
) AS docs;

El resultado de esta consulta podría ser similar al de la tabla siguiente:

Para obtener más información, consulte Analizar tipos de datos complejos en Azure Synapse Analytics o Consultar tipos anidados en archivos Parquet y JSON utilizando un grupo SQL sin servidor.

Acoplamiento de matrices anidadas

Los datos de Azure Cosmos DB pueden tener submatrices anidadas como la matriz del creador de un conjunto de datos CORD-19:

{
    "paper_id": <str>,                      # 40-character sha1 of the PDF
    "metadata": {
        "title": <str>,
        "authors": [                        # list of author dicts, in order
            {
                "first": <str>,
                "middle": <list of str>,
                "last": <str>,
                "suffix": <str>,
                "affiliation": <dict>,
                "email": <str>
            },
            ...
        ],
        ...
}

En algunos casos, puede que tenga que combinar las propiedades del elemento superior (metadata) con todos los elementos de la matriz (autores). Un grupo de SQL sin servidor permite aplanar las estructuras anidadas aplicando la función en la matriz anidada:

SELECT
    *
FROM
    OPENROWSET(
      'CosmosDB',
      'Account=your-cosmosdb;Database=covid;Key=yourcosmosdbkey',
       Cord19
    ) WITH ( title varchar(1000) '$.metadata.title',
             authors varchar(max) '$.metadata.authors' ) AS docs
      CROSS APPLY OPENJSON ( authors )
                  WITH (
                       first varchar(50),
                       last varchar(50),
                       affiliation nvarchar(max) as json
                  ) AS a

El resultado de esta consulta podría ser similar al de la tabla siguiente:

Asignaciones de Azure Cosmos DB a tipos de SQL

Aunque Azure Cosmos DB almacén transaccional es independiente del esquema, el almacén analítico se esquematiza para optimizar el rendimiento de las consultas analíticas. Con la capacidad de sincronización automática de Azure Synapse Link, Azure Cosmos DB gestiona la representación del esquema en el almacén analítico de manera predeterminada, lo que incluye el manejo de tipos de datos anidados. Dado que un grupo de SQL sin servidor consulta el almacén analítico, es importante comprender cómo asignar tipos de datos de entrada de Azure Cosmos DB a tipos de datos SQL.

Las cuentas de Azure Cosmos DB de SQL (Core) API admiten tipos de propiedad JSON de número, cadena, booleano, null, objeto anidado o matriz. Tendrá que elegir tipos SQL que coincidan con estos tipos JSON si usa la cláusula en . En la tabla siguiente se muestran los tipos de columna SQL que se deben usar para los distintos tipos de propiedad de Azure Cosmos DB.

Esquema de fidelidad completa

El esquema de fidelidad completa de Azure Cosmos DB registra los valores y sus tipos de mejor coincidencia para cada propiedad de un contenedor. La función en un contenedor con un esquema de fidelidad completa proporciona el tipo y el valor real en cada celda. Imagine que la consulta siguiente lee los elementos de un contenedor con un esquema de fidelidad completa:

SELECT *
FROM OPENROWSET(
      'CosmosDB',
      'account=MyCosmosDbAccount;database=covid;region=westus2;key=C0Sm0sDbKey==',
       Ecdc
    ) as rows

El resultado de esta consulta devuelve tipos y valores con formato de texto JSON:

Para cada valor, puede ver el tipo identificado en un elemento de contenedor de Azure Cosmos DB. La mayoría de los valores de la propiedad /> , pero algunos de ellos se almacenan incorrectamente como cadenas en Azure Cosmos DB. El esquema de fidelidad completa devuelve los valores de correctamente tipados y los valores de con formato incorrecto.

El número de casos se almacena como un valor de , pero hay un valor especificado como número decimal. Este valor tiene el tipo . Si hay algunos valores que superan el número mayor, se almacenarían como el tipo . Todos los valores de este ejemplo se almacenan como tipos .

Para consultar cuentas de Azure Cosmos DB for MongoDB, puede obtener más información sobre la representación de esquemas de fidelidad completa en el almacén analítico y los nombres de propiedad extendida que se van a usar en ¿Qué es el almacén analítico de Azure Cosmos DB?

Consulta de elementos con un esquema de fidelidad completa

Al consultar el esquema de fidelidad completa, debe especificar explícitamente el tipo SQL y el tipo de propiedad Azure Cosmos DB esperado en la cláusula WITH.

En el ejemplo siguiente, se supone que es el tipo correcto para la propiedad y es el tipo correcto para la propiedad :

SELECT geo_id, cases = SUM(cases)
FROM OPENROWSET(
      'CosmosDB'
      'account=MyCosmosDbAccount;database=covid;region=westus2;key=C0Sm0sDbKey==',
       Ecdc
    ) WITH ( geo_id VARCHAR(50) '$.geo_id.string',
             cases INT '$.cases.int32'
    ) as rows
GROUP BY geo_id

Los valores de y que tienen otros tipos se devuelven como valores . Esta consulta hace referencia solo a con el tipo especificado en la expresión ().

Si tiene valores con otros tipos (cases.int64, cases.float64) que no se pueden limpiar en un contenedor de Azure Cosmos DB, tendría que hacer referencia explícitamente a ellos en una cláusula WITH y combinar los resultados. La consulta siguiente agrega los elementos , y almacenados en la columna :

SELECT geo_id, cases = SUM(cases_int) + SUM(cases_bigint) + SUM(cases_float)
FROM OPENROWSET(
      'CosmosDB',
      'account=MyCosmosDbAccount;database=covid;region=westus2;key=C0Sm0sDbKey==',
       Ecdc
    ) WITH ( geo_id VARCHAR(50) '$.geo_id.string', 
             cases_int INT '$.cases.int32',
             cases_bigint BIGINT '$.cases.int64',
             cases_float FLOAT '$.cases.float64'
    ) as rows
GROUP BY geo_id

En este ejemplo, el número de casos se almacena como valores , o . Todos los valores deben extraerse para calcular el número de casos por país o región.

Solución de problemas

Revise la página self-help para encontrar los problemas conocidos o los pasos de solución de problemas que pueden ayudarle a resolver posibles problemas con las consultas de Azure Cosmos DB.

Contenido relacionado

• Usa Power BI e instancia de SQL sin servidor de Synapse para analizar datos de Azure Cosmos DB
• Creación y uso de vistas en un grupo de SQL sin servidor
• Tutorial: Exploración y análisis de lagos de datos con un grupo de SQL sin servidor
• Si recibe errores o experimenta problemas de rendimiento, consulte Solución de problemas del grupo de SQL sin servidor
• Module: Implementación de Azure Synapse Link con Azure Cosmos DB