Consulta de datos de Azure Cosmos DB con un grupo de SQL sin servidor en Azure Synapse Link

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

Para consultar Azure Cosmos DB, se admite el área expuesta de SELECT completa mediante la función OPENROWSET, que incluye la mayoría de las funciones y los operadores de SQL. También puede almacenar los resultados de la consulta que lee los datos de Azure Cosmos DB junto con los de Azure Blob Storage o Azure Data Lake Storage mediante create external table as 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, aprenderá a escribir una consulta con un grupo de SQL sin servidor que consultará datos de contenedores de Azure Cosmos DB que están habilitados con Azure Synapse Link. Después, puede obtener más información sobre la creación de vistas de un grupo de SQL sin servidor en contenedores de Azure Cosmos DB y cómo conectarlas a modelos de Power BI en este tutorial. En este tutorial se usa un contenedor con un esquema bien definido de Azure Cosmos DB. 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 que ha preparado 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) se encuentra 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 una capa de almacenamiento en caché como Analysis Services y cargar las particiones de menos de 80 GB en el modelo de Analysis Services.
  • Si va a filtrar datos mediante columnas de cadena, asegúrese de que usa la función OPENROWSET con la cláusula explícita WITH que tiene los tipos más pequeños posibles (por ejemplo, no use VARCHAR(1000) si sabe que la propiedad tiene hasta 5 caracteres).

Información general

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

• OPENROWSET con clave insertada. Esta sintaxis se puede usar para consultar colecciones de Azure Cosmos DB sin necesidad de preparar las credenciales.
• OPENROWSET que hizo referencia a las credenciales que contiene la clave de cuenta de Azure Cosmos DB. Esta sintaxis se puede usar para crear vistas en las colecciones de Azure Cosmos DB.

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

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

'account=<database account name>;database=<database name>;region=<region name>;key=<database account master key>'

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 de European Centre for Disease Prevention and Control (ECDC) COVID-19 Cases (Casos de COVID-19 del Centro Europeo para la Prevención y Control de Enfermedades) y COVID-19 Open Research Dataset (CORD-19), doi:10.5281/zenodo.3715505.

Puede ver la licencia y la estructura de los datos en estas páginas. También puede descargar datos de ejemplo para los conjuntos de datos ECDC y CORD-19.

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

• Una cuenta de base de datos de Azure Cosmos DB habilitada para Azure Synapse Link
• Una base de datos de Azure Cosmos DB con el nombre covid
• Dos contenedores de Azure Cosmos DB denominados Ecdc y Cord19 cargados con los conjuntos de datos de ejemplo anteriores

Puede usar la cadena de conexión siguiente para fines de pruebas: Account=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==. 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 de Azure Cosmos DB con inferencia automática del esquema

La forma más fácil de explorar datos en Azure Cosmos DB consiste en usar la funcionalidad de inferencia automática del esquema. 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=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==',
       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 = 's5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==';
*/
SELECT TOP 10 *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=synapselink-cosmosdb-sqlsample;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 indica a un grupo de SQL sin servidor que se conecte a la base de datos covid en la cuenta de Azure Cosmos DB MyCosmosDbAccount autenticada con la clave de Azure Cosmos DB (en el ejemplo anterior es ficticia). Después, accedimos al almacén analítico del contenedor Ecdc en la región West US 2. Como no se proyectan propiedades específicas, la función OPENROWSET devolverá todas las propiedades de los elementos de Azure Cosmos DB.

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

Si tiene que explorar datos del otro contenedor de la misma base de datos de Azure Cosmos DB, puede usar la misma cadena de conexión y hacer referencia al contenedor necesario como tercer parámetro:

SELECT TOP 10 *
FROM OPENROWSET( 
       'CosmosDB',
       'Account=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==',
       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 OPENROWSET le permite especificar de forma explícita las propiedades que quiere leer de los datos del contenedor, junto a sus tipos de datos.

Imagine que ha 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 OPENROWSET le permite especificar un subconjunto de propiedades que quiere leer y los tipos de columna exactos en la cláusula WITH:

SELECT TOP 10 *
FROM OPENROWSET(
      'CosmosDB',
      'Account=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==',
       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 = 's5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==';
*/
SELECT TOP 10 *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=synapselink-cosmosdb-sqlsample;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 de SQL que se deben usar para los valores de Azure Cosmos DB, revise las reglas de asignaciones de tipos SQL al final del artículo.

Creación de vistas

No se recomienda ni se ofrece asistencia para la creación de vistas en las bases de datos master o predeterminada. 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 unas credenciales independientes y hacer referencia a ellas a partir de la función OPENROWSET. No mantenga su clave de cuenta en la definición de la vista.

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

No utilice OPENROWSET sin un esquema definido explícitamente, ya que podría afectar al rendimiento. Asegúrese de usar los tamaños más pequeños posibles para las columnas; por ejemplo, VARCHAR(100) en lugar de la opción VARCHAR(8000) predeterminada. 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 Latin1_General_100_BIN2_UTF8 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. Esto probablemente significa que la vista hace referencia a columnas u objetos modificados o que 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 si se crean como matrices u objetos anidados. La funcionalidad de sincronización automática de Azure Synapse Link para Azure Cosmos DB administra la representación del esquema en el almacén analítico de forma integrada, lo que incluye el control de los 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 las matrices anidados de Azure Cosmos DB se representan como cadenas JSON en el resultado de la consulta cuando los lee la función OPENROWSET. Puede especificar las rutas de acceso a los valores anidados en los objetos cuando use la cláusula WITH:

SELECT TOP 10 *
FROM OPENROWSET( 
       'CosmosDB',
       'Account=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==',
       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:

Obtenga más información sobre el análisis de tipos de datos complejos como archivos y contenedores de Parquet en Azure Synapse Link para Azure Cosmos DB o como estructuras anidadas en un grupo de 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 (authors). Un grupo de SQL sin servidor permite acoplar estructuras anidadas mediante la aplicación de la función OPENJSON en la matriz anidada:

SELECT
    *
FROM
    OPENROWSET(
      'CosmosDB',
      'Account=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==',
       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 el almacén transaccional de Azure Cosmos DB es independiente del esquema, el almacén analítico está esquematizado para optimizar el rendimiento de las consultas analíticas. Con la funcionalidad de sincronización automática de Azure Synapse Link, Azure Cosmos DB administra la representación del esquema en el almacén analítico de forma integrada, lo que incluye el control de los 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, nulo, objeto anidado o matriz. Tendrá que elegir tipos SQL que coincidan con estos tipos JSON si usa la cláusula WITH en OPENROWSET. En la tabla siguiente se muestran los tipos de columna SQL que se deben usar para los distintos tipos de propiedad en 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 OPENROWSET 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 devolverá tipos y valores con formato de texto JSON:

En cada valor, puede ver el tipo identificado en un elemento del contenedor de Azure Cosmos DB. La mayoría de los valores de la propiedad date_rep contienen valores date, pero algunos de ellos se almacenan incorrectamente como cadenas en Azure Cosmos DB. El esquema de fidelidad completa devolverá valores date con el tipo correcto y valores string con formato incorrecto. El número de casos es información almacenada como un valor int32, pero hay un valor que se especifica como un número decimal. Este valor tiene el tipo float64. Si hay algunos valores que superan el número int32 mayor, se almacenarían como el tipo int64. Todos los valores geo_id de este ejemplo se almacenan como tipos string.

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 de forma explícita el tipo SQL y el tipo de propiedad de Azure Cosmos DB que se espera en la cláusula WITH.

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

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 para geo_id y cases que tienen otros tipos se devolverán como valores NULL. Esta consulta solo hará referencia a cases con el tipo especificado en la expresión (cases.int32).

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

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 int32, int64 o float64. 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 de autoauyda para localizar los problemas conocidos o los pasos de solución de problemas que pueden ayudarle a resolver posibles problemas con Azure Cosmos DB.

Pasos siguientes

Para más información, consulte los siguientes artículos.

• Uso de Power BI y el grupo de SQL sin servidor con Azure Synapse Link
• Creación y uso de vistas en el grupo de SQL sin servidor
• Tutorial sobre la creación de vistas del grupo de SQL sin servidor en Azure Cosmos DB y su conexión a modelos de Power BI a través de DirectQuery
• Visite el vínculo de Azure Synapse de la página de autoayuda de Azure Cosmos DB si recibe errores o experimenta problemas de rendimiento.
• Consulte el módulo de información de Learn sobre cómo consultar Azure Cosmos DB con SQL sin servidor para Azure Synapse Analytics.