Uso del controlador ODBC de Azure Cosmos DB para conectarse a las herramientas de BI y análisis de datos

SE APLICA A: NoSQL

En este artículo se explica cómo instalar y usar el controlador ODBC de Azure Cosmos DB para crear tablas y vistas normalizadas para los datos de Azure Cosmos DB. Puede consultar los datos normalizados con consultas de SQL o importar los datos en Power BI u otro software de BI y análisis para crear informes y visualizaciones.

Azure Cosmos DB es una base de datos sin esquema, lo que permite el desarrollo rápido de aplicaciones y permite iterar en los modelos de datos sin que se limite a un esquema estricto. Una sola base de datos de Azure Cosmos DB puede contener documentos JSON de varias estructuras. Para analizar estos datos o informar sobre estos, es posible que tenga que acoplar los datos para ajustarse a un esquema.

El controlador ODBC normaliza datos de Azure Cosmos DB en tablas y vistas que se ajusten a sus necesidades de informes y análisis de datos. Los esquemas normalizados permiten usar herramientas compatibles con ODBC para acceder a los datos. Los esquemas no tienen ningún efecto en los datos subyacentes y no es necesario que los desarrolladores se ajusten a ellos. El controlador ODBC ayuda a que las bases de datos de Azure Cosmos DB sean útiles para los analistas de datos, así como para los equipos de desarrollo.

Puede realizar operaciones de SQL en las tablas y vistas normalizadas, así como agrupar por consultas, inserciones, actualizaciones y eliminaciones. El controlador es compatible con ODBC 3.8 y admite la sintaxis de ANSI SQL-92.

Importante

Considere la posibilidad de usar Azure Synapse Link para Azure Cosmos DB para crear tablas y vistas para los datos. Synapse Link tiene ventajas de rendimiento distintivas para grandes conjuntos de datos sobre el controlador ODBC. También puede conectar los datos normalizados de Azure Cosmos DB a otras soluciones de software, como SQL Server Integration Services (SSIS), QlikSense, Tableau y otras herramientas de integración de datos, BI y software de análisis. Puede usar esas soluciones para analizar, mover, transformar y crear visualizaciones con los datos de Azure Cosmos DB.

Importante

• La conexión a Azure Cosmos DB con el controlador ODBC actualmente solo se admite para la API de Azure Cosmos DB solo para NoSQL.
• El controlador ODBC actual no admite las delegaciones agregadas y tiene problemas conocidos con algunas herramientas de análisis. Hasta que se publique una nueva versión, puede usar una de las siguientes alternativas:
  • Azure Synapse Link es la solución de análisis preferida para Azure Cosmos DB. Con Azure Synapse Link y Azure Synapse SQL grupos sin servidor, puede usar cualquier herramienta de BI para extraer información casi en tiempo real de los datos de Azure Cosmos DB, SQL o API para MongoDB.
  • En el caso de Power BI, puede usar el conector de Azure Cosmos DB para Power BI.
  • Para Qlik Sense, consulte Conexión de Qlik Sense a Azure Cosmos DB.

Instalación del controlador ODBC y conexión a la base de datos

1. Descargue los controladores para su entorno:

   Instalador	Sistemas operativos admitidos
   Microsoft Azure Cosmos DB ODBC 64-bit.msi para Windows de 64 bits	Versiones de 64 bits de Windows 8.1 o posteriores, Windows 8, Windows 7. Versiones de 64 bits de Windows Server 2012 R2, Windows Server 2012 y Windows Server 2008 R2
   Microsoft Azure Cosmos DB ODBC 32x64-bit.msi para Windows de 32 bits en 64 bits	Versiones de 64 bits de Windows 8.1 o posterior, Windows 8, Windows 7, Windows XP y Windows Vista. Versiones de 64 bits de Windows Server 2012 R2, Windows Server 2012, Windows Server 2008 R2 y Windows Server 2003.
   Microsoft Azure Cosmos DB ODBC 32-bit.msi para Windows de 32 bits	Versiones de 32 bits de Windows 8.1 o posterior, Windows 8, Windows 7, Windows XP y Windows Vista.

2. Ejecute el archivo .msi localmente, lo que iniciará el Asistente para la instalación del controlador ODBC de Microsoft Azure Cosmos DB.

3. Complete el asistente para la instalación con la entrada predeterminada.

4. Una vez instalado el controlador, escriba Orígenes de datos ODBC en el cuadro de búsqueda de Windows y abra el Administrador de orígenes de datos ODBC.

5. Asegúrese de que el controlador ODBC de Microsoft Azure DocumentDB aparece en la pestaña Controladores.

   

6. Seleccione la pestaña DSN de usuario y, luego, seleccione Agregar para crear un nuevo nombre de origen de datos (DSN). También puede crear un DNS del sistema.

7. En la ventana Crear un nuevo origen de datos, seleccione Microsoft Azure DocumentDB ODBC Driver (Controlador ODBC de Microsoft Azure DocumentDB) y seleccione Finalizar.

8. En la ventana DocumentDB ODBC Driver DSN Setup (Configuración de DSN del controlador ODBC de DocumentDB), rellene la información siguiente:

   

   • Nombre del origen de datos: nombre descriptivo para el DSN de ODBC. Este nombre es único para esta cuenta de Azure Cosmos DB.
   • Descripción: Una breve descripción del origen de datos.
   • Host: URI de la cuenta de Azure Cosmos DB. Puede obtener esta información en la página Claves de la cuenta de Azure Cosmos DB en Azure Portal.
   • Clave de acceso: clave principal o secundaria, de solo escritura o solo lectura de la página Claves de Azure Cosmos DB de Azure Portal. Es mejor usar las claves de solo lectura si usa el DSN para los informes y el procesamiento de datos de solo lectura.

   Para evitar un error de autenticación, use los botones para copiar el URI y la clave de Azure Portal.

   

   • Encrypt Access Key for (Cifrar clave de acceso para): seleccione la mejor opción en función de quien usa la máquina.

9. Seleccione Probar para asegurarse de que puede conectarse a su cuenta de Azure Cosmos DB.

10. Seleccione Opciones avanzadas y establezca los siguientes valores:

    • Versión de API REST: Seleccione la versión de la API REST de las operaciones. El valor predeterminado es 2015-12-16.

      Si tiene contenedores con claves de partición grandes que necesitan la versión 2018-12-31 de la API de REST, escriba 2018-12-31 y siga los pasos al final de este procedimiento.

    • Coherencia de consultas: seleccione el nivel de coherencia para las operaciones. El valor predeterminado es Sesión.

    • Número de reintentos: escriba el número de veces que se volverá a intentar una operación si la solicitud inicial no se completa debido a una limitación de la velocidad del servicio.

    • Archivo de esquema: si no selecciona un archivo de esquema, el controlador examina la primera página de datos de cada contenedor para determinar su esquema, denominado asignación de contenedores, de cada sesión. Este proceso puede provocar un tiempo de inicio largo para las aplicaciones que usan el DSN. Se recomienda asociar un archivo de esquema al DSN.

      • Si ya tiene un archivo de esquema, seleccione Examinar, vaya al archivo, seleccione Guardar y, luego, seleccione Aceptar.

      • Si aún no tiene un archivo de esquema, seleccione Aceptar y, luego, siga los pasos de la sección siguiente para crear una definición de esquema. Después de crear el esquema, vuelva a esta ventana Opciones avanzadas para agregar el archivo de esquema.

Después de seleccionar Aceptar para completar y cerrar la ventana Configuración de DSN del controlador ODBC de DocumentDB, aparece el nuevo DSN de usuario en la pestaña DSN de usuario de la ventana Administrador de orígenes de datos ODBC.

Edite el Registro de Windows para admitir la versión 2018-12-31 de la API de REST

Si tiene contenedores con claves de partición grandes que necesitan la versión 2018-12-31 de la API de REST, siga estos pasos para actualizar el Registro de Windows para admitir esta versión.

1. En el menú Inicio de Windows, escriba regedit para buscar y abrir Editor del Registro.

2. En el Editor del Registro, vaya a la ruta de acceso Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI.

3. Cree una nueva subclave con el mismo nombre que su DSN, por ejemplo, DSN de ODBC de cuenta de la Contoso.

4. Vaya a la nueva subclave de DSN de ODBC de la cuenta de Contoso y haga clic con el botón derecho para agregar un nuevo valor de cadena:

   • Nombre del valor: IgnoreSessionToken

   • Datos del valor: 1

   

Creación de una definición de esquema

Hay dos tipos de métodos de muestreo que puede usar para crear un esquema, asignación de contenedores o asignación de delimitador de tabla. Una sesión de muestreo puede usar ambos métodos, pero cada contenedor solo puede usar uno. El método que se va a usar depende de las características de los datos.

• La asignación de contenedores recupera los datos de una página del contenedor para determinar la estructura de los datos y transpone el contenedor a una tabla en el lado ODBC. Este método de muestreo es rápido y eficaz cuando los datos de un contenedor son homogéneos.

• La asignación de delimitador de tabla proporciona un muestreo más sólido para los datos heterogéneos. Este método limita el muestreo a un conjunto de atributos y valores correspondientes.

  Por ejemplo, si un documento contiene una propiedad Tipo, puede limitar el muestreo a los valores de esta propiedad. El resultado final del muestreo es un conjunto de tablas para cada uno de los valores Tipo que ha especificado. Tipo = Coche produce una tabla Coche, mientras que Tipo = Avión produce una tabla Avión.

Para definir un esquema, siga estos pasos. Para el método de asignación de delimitador de tabla, se siguen pasos adicionales para definir los atributos y los valores del esquema.

1. En la pestaña DSN de usuario de la ventana Administrador de orígenes de datos ODBC, seleccione el nombre de DSN de usuario de Azure Cosmos DB y, luego, seleccione Configurar.

2. En la ventana DocumentDB ODBC Driver DSN Setup (Configuración de DSN del controlador ODBC de DocumentDB), seleccione Editor de esquema.

   

3. En la ventana Editor de esquemas, seleccione Crear nuevo.

4. La ventana Generate Schema (Generar esquema) muestra todas las colecciones de la cuenta de Azure Cosmos DB. Seleccione las casillas situadas junto a los contenedores de los que quiere hacer el muestreo.

5. Para usar el método de asignación de contenedores, seleccione Muestra.

   O bien, para usar la asignación de delimitador de tabla, siga estos pasos para definir los atributos y los valores para determinar el ámbito del ejemplo.

   1. Seleccione Editar en la columna Definición de asignación del DSN.

   2. En la ventana Definición de asignación, en Método de asignación, seleccione Delimitadores de tabla.

   3. En el cuadro Atributos, escriba el nombre de una propiedad del delimitador en el documento en el que quiere definir el ámbito del muestreo, por ejemplo, Ciudad. Presiona Entrar.

   4. Si quiere definir el ámbito del muestreo a valores determinados para el atributo que escribió, seleccione dicho atributo y, luego, escriba un valor en el cuadro Valor, como Seattle y presione Entrar. Puede agregar más valores para los atributos. Solo asegúrese de que se selecciona el atributo correcto cuando escriba los valores.

   5. Cuando haya terminado de escribir los atributos y los valores, seleccione Aceptar.

   6. En la ventana Generar esquema, seleccione Muestra.

6. En la pestaña Vista de diseño, refina el esquema. La Vista de diseño representa la base de datos, el esquema y la tabla. En la vista de tabla se muestra el conjunto de propiedades asociadas a los nombres de columna, como Nombre SQL y Nombre de origen.

   Para cada columna, puede modificar nombre SQL, tipo de SQL, longitud SQL, Escala, Precisión y Que admite un valor NULL según sea necesario.

   Puede establecer Ocultar columna en true si desea excluir esa columna de los resultados de la consulta. Las columnas marcadas con Ocultar columna = true no se devuelven para la selección y proyección, aunque aún forman parte del esquema. Por ejemplo, puede ocultar todas las propiedades necesarias del sistema de Azure Cosmos DB que comienzan con _. La columna id es el único campo que no se puede ocultar, ya que es la clave principal del esquema normalizado.

7. Una vez que haya terminado de definir el esquema, seleccione Archivo>Guardar, vaya al directorio en el que lo va a guardar y seleccione Guardar.

8. Para usar este esquema con un DSN, en la ventana Configuración de DSN del controlador ODBC de DocumentDB (DocumentDB ODBC Driver DSN Setup), seleccione Opciones avanzadas. Seleccione el cuadro Archivo de esquema, vaya al esquema guardado, seleccione Aceptar y, luego, seleccione Aceptar de nuevo. Al guardar el archivo de esquema se modifica la conexión de DSN para tener como ámbito los datos y la estructura definidos por el esquema.

Creación de vistas

Opcionalmente, puede definir y crear vistas en el Editor de esquemas como parte del proceso de muestreo. Estas vistas son equivalentes a las vistas SQL. Las vistas son de solo lectura y tienen el ámbito de las selecciones y proyecciones de la consulta SQL de Azure Cosmos DB definida.

Siga estos pasos para crear una vista para los datos:

1. En la pestaña Vista de muestra de la ventana Editor de esquemas, seleccione los contenedores de los que quiere hacer el muestreo y, luego, seleccione Agregar en la columna Ver definición.

   

2. En la ventana Ver definiciones, seleccione Nuevo. Escriba un nombre para la vista, por ejemplo, EmployeesfromSeattleView y, luego, seleccione Aceptar.

3. En la ventana Editar vista, escriba una consulta de Azure Cosmos DB, por ejemplo,

   SELECT c.City, c.EmployeeName, c.Level, c.Age, c.Manager FROM c WHERE c.City = "Seattle"

4. Seleccione Aceptar.

   

Puede crear las vistas que quiera. Una vez que haya terminado de definir las vistas, seleccione Muestra para hacer un muestreo de los datos.

Importante

El texto de consulta de la definición de vista no debe contener saltos de línea. De lo contrario, se producirá un error genérico al obtener una vista previa de la vista.

Consultas con SQL Server Management Studio

Una vez configurado un DSN de usuario del controlador ODBC de Azure Cosmos DB, puede consultar Azure Cosmos DB desde SQL Server Management Studio (SSMS) mediante la configuración de una conexión de servidor vinculado.

1. Instale SQL Server Management Studio y conéctese al servidor.

2. En el editor de consultas de SSMS, cree un objeto de servidor vinculado para el origen de datos mediante la ejecución de los siguientes comandos. Reemplace DEMOCOSMOS por el nombre del servidor vinculado, y SDS Name por el nombre del origen de datos.

   USE [master]
   GO
   
   EXEC master.dbo.sp_addlinkedserver @server = N'DEMOCOSMOS', @srvproduct=N'', @provider=N'MSDASQL', @datasrc=N'SDS Name'
   
   EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DEMOCOSMOS', @useself=N'False', @locallogin=NULL, @rmtuser=NULL, @rmtpassword=NULL
   
   GO
   

Para ver el nuevo nombre del servidor vinculado, actualice la lista de servidores vinculados.

Para consultar la base de datos vinculada, escriba una consulta SSMS. En este ejemplo, la consulta selecciona los datos de la tabla del contenedor customers:

SELECT * FROM OPENQUERY(DEMOCOSMOS, 'SELECT *  FROM [customers].[customers]')

Ejecutar la consulta. Los resultados deben tener un aspecto similar a la siguiente salida:

attachments/  1507476156    521 Bassett Avenue, Wikieup, Missouri, 5422   "2602bc56-0000-0000-0000-59da42bc0000"   2015-02-06T05:32:32 +05:00 f1ca3044f17149f3bc61f7b9c78a26df
attachments/  1507476156    167 Nassau Street, Tuskahoma, Illinois, 5998   "2602bd56-0000-0000-0000-59da42bc0000"   2015-06-16T08:54:17 +04:00 f75f949ea8de466a9ef2bdb7ce065ac8
attachments/  1507476156    885 Strong Place, Cassel, Montana, 2069       "2602be56-0000-0000-0000-59da42bc0000"   2015-03-20T07:21:47 +04:00 ef0365fb40c04bb6a3ffc4bc77c905fd
attachments/  1507476156    515 Barwell Terrace, Defiance, Tennessee, 6439     "2602c056-0000-0000-0000-59da42bc0000"   2014-10-16T06:49:04 +04:00      e913fe543490432f871bc42019663518
attachments/  1507476156    570 Ruby Street, Spokane, Idaho, 9025       "2602c156-0000-0000-0000-59da42bc0000"   2014-10-30T05:49:33 +04:00 e53072057d314bc9b36c89a8350048f3

Visualización de los datos en Power BI Desktop

Puede usar el DSN para conectar Azure Cosmos DB con cualquier herramienta compatible con ODBC. Este procedimiento muestra cómo conectarse a Power BI Desktop para crear una visualización de Power BI.

1. En Power BI Desktop, seleccione Obtener datos.

   

2. En la ventana Obtener datos, seleccione Otros>ODBC y, luego, seleccione Conectar.

   

3. En la ventana Desde ODBC, seleccione el DSN que ha creado y, luego, seleccione Aceptar.

   

4. En la ventana Acceder a un origen de datos con un controlador ODBC, seleccione Predeterminado o personalizado y, luego, seleccione Conectar.

5. En la ventana Navegador, en el panel izquierdo, expanda la base de datos y el esquema y, luego, seleccione la tabla. El panel de resultados incluye los datos que usan el esquema que ha creado.

   

6. Para visualizar los datos en Power BI Desktop, active la casilla situada junto al nombre de la tabla y, luego, seleccione Cargar.

7. En Power BI Desktop, seleccione la pestaña Datos a la izquierda de la pantalla para confirmar que los datos se han importado.

8. Seleccione la pestaña Informe a la izquierda de la pantalla, seleccione Nuevo objeto visual en la cinta de opciones y, luego, personalice el objeto visual.

Solución de problemas

• Problema: obtiene el siguiente error al intentar conectarse:

  [HY000]: [Microsoft][Azure Cosmos DB] (401) HTTP 401 Authentication Error: {"code":"Unauthorized","message":"The input authorization token can't serve the request. Please check that the expected payload is built as per the protocol, and check the key being used. Server used the following payload to sign: 'get\ndbs\n\nfri, 20 jan 2017 03:43:55 gmt\n\n'\r\nActivityId: 9acb3c0d-cb31-4b78-ac0a-413c8d33e373"}
  

  Solución: Asegúrese de que los valores host y clave de acceso que copió de Azure Portal son correctos y vuelva a intentarlo.

• Problema: aparece el siguiente error en SSMS al intentar crear un servidor de Azure Cosmos DB vinculado:

  Msg 7312, Level 16, State 1, Line 44
  
  Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "DEMOCOSMOS". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
  

  Solución: un servidor de Azure Cosmos DB vinculado no admite nombres de cuatro partes.

Pasos siguientes

• Para más información sobre Azure Cosmos DB, consulte Bienvenido a Azure Cosmos DB.
• Para más información sobre cómo crear visualizaciones en Power BI Desktop, consulte Tipos de visualización en Power BI.