Usar SQL para consultar datos

  • Artículo

La capa empresarial de Microsoft Dataverse proporciona un punto de conexión de flujo de datos tabulares (TDS) que emula una conexión de datos SQL. La conexión SQL proporciona acceso de solo lectura a los datos de la tabla del entorno de destino de Dataverse, lo que le permite ejecutar consultas SQL en las tablas de datos de Dataverse. No se han proporcionado vistas personalizadas de los datos. La conexión SQL del punto de conexión de Dataverse usa el modelo de seguridad de Dataverse para el acceso a los datos. Se pueden obtener datos para todas las tablas de Dataverse a las que tiene acceso un usuario.

Nota

Solo la conexión de datos SQL a través de SQL Server Management Studio y las bibliotecas .NET está en versión preliminar. Power BI está disponible en general.

Requisitos previos

La configuración Habilitar TDS punto de conexión debe estar habilitada en su ambiente. Esta opción está habilitada de forma predeterminada. Más información: Administrar la configuración de las características

Soporte de aplicaciones

A continuación se describe la compatibilidad de aplicaciones de punto de conexión TDS (SQL) para Power BI y SQL Server Management Studio.

SQL Server Management Studio (versión preliminar)

También puede utilizar SQL Server Management Studio (SSMS) versión 18.12.1 o posterior con la conexión SQL punto de conexión. Dataverse Dataverse En la figura se muestran ejemplos de uso de SSMS con la conexión de datos SQL.

Tabla expandida de cuentas.

Seguridad y autenticación

Sólo se admite la autenticación Microsoft Entra ID. No se admiten la autenticación de SQL ni la autenticación de Windows. La siguiente figura muestra un ejemplo de cómo iniciar sesión en la conexión SQL en SSMS. Observe que el nombre del servidor es la dirección URL de la organización.

Diálogo de conexión.

Nota

Los puertos 1433 y/o 5558 deben estar habilitados para usar el extremo de TDS desde una aplicación cliente como SSMS. Si solo habilita el puerto 5558, el usuario debe agregar ese número de puerto al nombre del servidor en el cuadro de diálogo Conectar al servidor de SSMS, por ejemplo: myorgname.crm.dynamics.com,5558.

Información sobre el cifrado punto de conexión: Protección de datos en tránsito

Consultas de datos de tabla de ejemplo

A continuación hay un par de consultas de ejemplo compuestas en SSMS. La primera imagen muestra una consulta simple usando alias y ordenamiento de resultados.

select top 5 a.name as [VIP customer], a.address1_postalcode as [ZIP code] from account a order by a.address1_postalcode desc

Consulta simple usando alias y ordenando.

La siguiente consulta muestra un JOIN.

select name, fullname from account a inner join contact c on a.primarycontactid = c.contactid

Otra consulta usando un JOIN.

Power BI (disponibilidad general)

Puede utilizar la opción Analizar en Power BI (Datos>Tablas>Analizar en Power BI) en Power Apps (https://make.powerapps.com) para usar el conector Dataverse para analizar datos en Power BI Desktop. Más información: Ver datos de la tabla en Power BI Desktop

Nota

Para habilitar esta función, consulte la configuración TDS punto de conexión en Administrar configuraciones de funciones. Una vez habilitado, debería ver un botón Analizar en Power BI en la barra de comandos de Power Apps.

Tipos de datos y operaciones compatibles

Cualquier operación que trate de modificar datos (es decir, INSERT, UPDATE) no funciona con esta conexión de datos SQL de solo lectura. Para obtener una lista detallada de las operaciones SQL admitidas en Dataverse punto de conexión, consulte En qué se diferencia SQL de Transact-SQL Dataverse .

Los siguientes tipos de datos no son compatibles con la conexión SQL: Dataverse , binary, image, sql_variant, varbinary, virtual, HierarchyId, managedproperty, file, xml, partylist, timestamp. choices Además, los tipos de tablas 'virtual' y 'audit' no son compatibles en este momento.

Propina

partylist En cambio, los atributos se pueden consultar uniéndose a la tabla activityparty como se muestra en el siguiente ejemplo.

select act.activityid, act.subject, string_agg([to].partyidname, ', ')
from activitypointer as act
left outer join activityparty as [to] on act.activityid = [to].activityid and [to].participationtypemask = 2
group by act.activityid, act.subject

Comportamientos de tipo de columna de búsqueda

Dataverse Las columnas de búsqueda se representan como <lookup>id y <lookup>name en un conjunto de resultados.

Comportamientos de tipo de columna de elección

Dataverse Las columnas de opciones se representan como <Nombre de opción> y <Etiqueta de opción> en un conjunto de resultados.

Propina

Después de realizar cambios en las etiquetas de una columna de opciones, es necesario que se publiquen las personalizaciones de la tabla.

Nota

Incluir una gran cantidad de etiquetas de opciones en su consulta tendrá un impacto significativo en el rendimiento. Es mejor usar menos de 10 etiquetas si es posible. Debido a que las etiquetas de elección están localizadas, la cadena localizada es más costosa de devolver.

Versión SQL con informe

El punto de conexión Dataverse TDS emula las capacidades de consulta de solo lectura de Microsoft SQL Server sobre la lógica de negocios de Dataverse. Dataverse devuelve la versión actual de SQL Azure 12.0.2000.8 para select @@version.

Guía de rendimiento

Cuando recupere datos a través del punto de conexión TDS, hay algunos patrones de consulta clave que deben utilizarse. Estos patrones de consulta, que se describen en las siguientes secciones, gestionan el rendimiento y el tamaño de los conjuntos de resultados.

Solo las columnas necesarias

Al crear una consulta, devuelva solo las columnas necesarias. Esta técnica ayuda tanto a la ejecución de la consulta como a la transferencia de los resultados a la aplicación cliente. En general, se recomienda mantener una consulta de menos de 100 columnas.

Columnas de elección

Las columnas de elección se aplanan en dos columnas, lo que mejora la facilidad de uso. Sin embargo, es importante realizar agregados y filtros en la parte de valor de la columna de elección. La parte de valor puede tener índices y se almacena en la tabla base. Sin embargo, la parte de etiqueta (nombre 'choicecolumn') se almacena por separado, lo que cuesta más recuperarla y no se puede indexar. El uso de una cantidad significativa de columnas de etiquetas de elección puede generar una consulta con un rendimiento más lento.

Usar X superior

Es importante utilizar una cláusula superior en sus consultas para evitar intentar devolver toda la tabla de datos. Por ejemplo, el uso Select Top 1000 accountid,name From account Where revenue > 50000 limita los resultados a las primeras 1.000 cuentas.

No use NOLOCK

Al crear consultas, no utilice la sugerencia de tabla NOLOCK. Esta sugerencia impide que Dataverse optimice las consultas.

Limitaciones

El punto de conexión de TDS de Dataverse ya no tiene un límite de tamaño máximo estricto. En cambio, hay un tiempo de espera fijo de cinco (5) minutos. Con la introducción de la transmisión de datos, puede recuperar tantos datos como se puedan completar en el tiempo de espera fijo de cinco (5) minutos. Considere utilizar herramientas de integración de datos como Azure Synapse Link for Dataverse y flujos de datos para consultas de datos grandes que requieran más de cinco (5) minutos para completarse. Más información: Importación y exportación de datos

Propina

Para ayudar a mantener el tamaño de los datos devueltos dentro de límites aceptables, use la menor cantidad posible de columnas de texto de varias líneas y columnas de opciones.

Advertencia

El tiempo de espera de cinco (5) minutos se puede ajustar a dos (2) minutos dependiendo de la complejidad de la consulta. Por ejemplo, las consultas que contienen SELECT *, NESTED FROMs and/or JOINs automáticamente establecerán el límite de tiempo de espera en dos (2) minutos, ya que dichas consultas ejercen demasiada presión sobre el servidor cuando se dejan en ejecución durante mucho tiempo. Se recomienda evitar el uso de estos patrones en SQL para obtener el máximo rendimiento.

Las fechas devueltas en los resultados de la consulta tienen el formato de hora universal coordinada (UTC). Anteriormente, las fechas se devolvían en la hora local.

La consulta de datos mediante SQL no incluye ningún complemento registrado en los mensajes RetrieveMultipleRequest o RetrieveRequest . Por lo tanto, cualquier reescritura de la consulta o resultados que normalmente se realizarían con dicho complemento no tendrá efecto para una consulta SQL.

Las consultas que utilizan el punto de conexión TDS se ejecutan bajo los límites de la API de protección del servicio.

El TDS punto de conexión no se puede usar con tablas elásticas. Más información: Mesas elásticas

Solución de problemas de conexión

Veamos algunas condiciones de error conocidas y cómo resolverlas.

Nota

Los puertos 1433 y/o 5558 deben estar habilitados para usar el extremo de TDS desde una aplicación cliente como SSMS. Si solo habilita el puerto 5558, el usuario debe agregar ese número de puerto al nombre del servidor en el cuadro de diálogo Conectar al servidor de SSMS, por ejemplo: myorgname.crm.dynamics.com,5558.

Autenticación

Solo se admite la autenticación de Microsoft Entra ID en la conexión SQL del punto acceso de Dataverse. El mecanismo de autenticación preferido es "Microsoft Entra ID - Universal" con autenticación multifactor (MFA). Sin embargo, "Microsoft Entra ID - Contraseña" funciona si MFA no está configurado. Si intenta utilizar otras formas de autenticación, verá errores como los siguientes.

  • Error devuelto al utilizar ID – Autenticación integrada. Microsoft Entra

"Error de inicio de sesión: se prohibió la solicitud HTTP con el esquema de autenticación de cliente 'Anónimo'. RequestId: TDS;81d8a4f7-0d49-4d21-8f50-04364bddd370;2 Time: 2020-12-17T01:10:59.8628578Z (.Net SqlClient Data Provider)"

  • Se devolvió un error al utilizar la autenticación de SQL Server .

“Error de inicio de sesión. Solicitud no autenticada. RequestId: TDS;918aa372-ccc4-438a-813e-91b086355343;1 Time: 2020-12-17T01:13:14.4986739Z (.Net SqlClient Data Provider)"

  • Se devolvió un error al utilizar la autenticación de Windows.

“Error de inicio de sesión. Solicitud no autenticada. RequestId: TDS;fda17c60-93f7-4d5a-ad79-7ddfbb917979;1 Time: 2020-12-17T01:15:01.0497703Z (.Net SqlClient Data Provider)"

Puertos bloqueados

Un error de puerto bloqueado puede tener la siguiente apariencia.

Mensaje de error.

La solución es verificar que los puertos TCP 1433 o 5558 del cliente estén desbloqueados. Utilice uno de los siguientes métodos para desbloquear los puertos, tal y como se describe a continuación.

Use PowerShell para validar la conexión con punto de conexión TDS

  1. Abra una ventana de comandos de PowerShell.
  2. Ejecute el comando Test-connection.
    Test-NetConnection -ComputerName <environment>.crm.dynamics.com -port 1433

Si la conexión se realiza correctamente, se devuelve la línea "TcpTestSucceeded : True".

En algunos casos, el tráfico se puede bloquear directamente a nivel de IP. Para validar que la dirección IP también funciona, tome la dirección IP devuelta de la conexión de prueba de dominio anterior y reemplace el valor del parámetro ComputerName con la dirección IP.

  1. Tome la dirección devuelta del comando anterior como "RemoteAddress"
  2. Ejecute Test-NetConnection -ComputerName <RemoteAddress> -port 1433

Este comando debería devolver "TcpTestSucceeded: True"

Establezca una sesión de telnet con el punto de conexión TDS

  1. En ordenador Microsoft Windows,, instale o habilite Telnet.
    1. Elija Inicio.
    2. Seleccionar Panel de control.
    3. Seleccione Programas y características.
    4. Seleccionar Activar o desactivar las funciones de Windows.
    5. Seleccione la opción Cliente Telnet .
    6. Seleccione Aceptar. Aparece un cuadro de diálogo para confirmar la instalación. El comando de Telnet debería estar disponible ahora.
  2. Ejecute un comando de Telnet en una ventana de comandos.
    telnet <environmentname>.crm.dynamics.com 1433

Si se establece correctamente la conexión, se le colocará en una sesión de Telnet activa. De lo contrario, recibe el error:

"Conectándose a <nombredelentorno>.crm.dynamics.com… No se pudo abrir la conexión para el host, en el puerto 1433: error de conexión".

Este mensaje de error significa que el puerto se ha bloqueado en el cliente.

Redirección de puerto de no SSL a SSL

La conexión TDS puede fallar cuando se utilizan aplicaciones de terceros debido a la redirección de puertos del 1433/5558 al 443. Este error se produce porque la regla de inspección SSL puede bloquear la comunicación, siendo el motivo del bloqueo la "redirección de un puerto no SSL a un puerto SSL". La solución es incluir en la lista de permitidos Comunicación TDS Dataverse en servidores proxy web mediante direcciones IP.

Para obtener información sobre los valores de dirección IP oficiales para acceder al servicio, consulte Direcciones IP requeridas.

Listar los nombres de host en la lista de permitidos no es suficiente cuando se conecta a Dataverse TDS porque la redirección de puertos entre los puertos 1433/5558 y 433 se realiza a través de la dirección IP, no a través del nombre de host.

Consulte también

En qué se diferencia SQL de Transact-SQL Dataverse Comience a usar tablas virtuales (entidades)...
Consulta de datos mediante FetchXmlLímites de la API de protección de servicios