Cálculos de la aplicación en PolyBase

  • Artículo

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores

El cálculo de aplicación mejora el rendimiento de las consultas en los orígenes de datos externos. A partir de SQL Server 2016 (13.x), los cálculos de delegación han estado disponibles para los orígenes de datos externos de Hadoop. SQL Server 2019 (15.x) presentó cálculos de delegación para otros tipos de orígenes de datos externos.

Nota:

Para determinar si el cálculo de delegación de PolyBase es beneficioso o no para la consulta, consulte Cómo saber si se ha producido una delegación externa.

Habilitar el cálculo de delegación

En los artículos siguientes se incluye información sobre la configuración del cálculo de aplicación para tipos específicos de orígenes de datos externos:

En esta tabla se resume la compatibilidad del cálculo de delegación en diferentes orígenes de datos externos:

Origen de datos Combinaciones Proyecciones Agregaciones Filters Estadísticas
ODBC genérico Sí Sí Sí Sí Sí
Oracle Sí Sí Sí Sí Sí
SQL Server Sí Sí Sí Sí Sí
Teradata Sí Sí Sí Sí Sí
MongoDB* No Sí Sí*** Sí*** Sí
Hadoop No Sí Algunos** Algunos** Sí
Azure Blob Storage No N.º N.º No Sí

* La compatibilidad con la delegación de Azure Cosmos DB se habilita mediante la API de Azure Cosmos DB para MongoDB.

** Consulte Cálculo de delegación y proveedores de Hadoop.

*** La compatibilidad de delegación con agregaciones y filtros para el conector ODBC de MongoDB para SQL Server 2019 se presentó con la actualización acumulativa 18 de SQL Server 2019.

Nota:

Cierta sintaxis de T-SQL puede bloquear el cálculo de delegación. Para obtener más información, consulte Sintaxis que impide la delegación.

Cálculo de delegación y proveedores de Hadoop

PolyBase es compatible con dos proveedores de Hadoop: Hortonworks Data Platform (HDP) y Cloudera Distributed Hadoop (CDH). No hay ninguna diferencia entre las dos características en cuanto al cálculo de delegación.

Para usar la funcionalidad de delegación de cálculos con Hadoop, el clúster de Hadoop de destino debe tener los componentes principales de HDFS, YARN y MapReduce con el servidor de historial de trabajos habilitado. PolyBase envía la consulta de aplicación a través de MapReduce y extrae el estado del servidor de historial de trabajos. Si falta algún componente, se produce un error en la consulta.

Una parte de la agregación debe producirse después de que los datos alcancen SQL Server. Pero una parte de la agregación se produce en Hadoop. Este método es habitual a la hora de calcular agregaciones en sistemas de procesamiento paralelo masivo.

Los proveedores de Hadoop admiten las siguientes agregaciones y filtros.

Agregaciones Filtros (comparación binaria)
Count_Big NotEqual
Sum LessThan
Promedio LessOrEqual
Máx. GreaterOrEqual
Mín. GreaterThan
Approx_Count_Distinct Is
IsNot

Principales escenarios beneficiosos del cálculo de delegación

Con el cálculo de delegación de PolyBase, puede delegar tareas de cálculo en orígenes de datos externos. Esto reduce la carga de trabajo en la instancia de SQL Server y puede mejorar significativamente el rendimiento.

SQL Server puede enviar cambios en combinaciones, proyecciones, agregaciones y filtros a orígenes de datos externos para aprovechar el cálculo remoto y restringir los datos enviados por medio de la red.

Delegación de combinaciones

En muchos casos, PolyBase puede facilitar la delegación del operador de combinación para unir dos tablas externas en el mismo origen de datos datos externos, lo cual mejora mucho el rendimiento.

Si la combinación se puede realizar en el origen de datos externo, la cantidad de movimientos de datos se reduce y el rendimiento de la consulta mejora. Sin la delegación de la combinación, los datos de las tablas que se vayan a combinar deben colocarse localmente en una base de datos temporal y, luego, combinarse.

Tenga en cuenta que, en el caso de las combinaciones distribuidas (unir una tabla local a una tabla externa), a menos que haya algunos criterios de filtro en la tabla externa que se apliquen a la condición de combinación, todos los datos de la tabla externa deben incorporarse localmente a tempdb para realizar la operación de combinación. Por ejemplo, la consulta siguiente no tiene ningún filtro en la condición de combinación de la tabla externa, lo que hará que se lean todos los datos de la tabla externa.

SELECT * FROM LocalTable L
JOIN ExternalTable E on L.id = E.id

Dado que la combinación está en la columna E.id de la tabla externa, si se agrega una condición de filtro a esa columna, el filtro se puede delegar, lo que reduce el número de filas leídas de la tabla externa.

SELECT * FROM LocalTable L
JOIN ExternalTable E on L.id = E.id
WHERE E.id = 20000

Seleccionar un subconjunto de filas

Use la aplicación del predicado para mejorar el rendimiento de una consulta que selecciona un subconjunto de filas de una tabla externa.

En este ejemplo, SQL Server inicia un trabajo de Map Reduce para recuperar las filas que coinciden con el predicado customer.account_balance < 200000 en Hadoop. Como la consulta se puede completar correctamente sin examinar todas las filas de la tabla, solo las filas que cumplen los criterios del predicado se copian en SQL Server. Esto ahorra mucho tiempo y exige menos espacio de almacenamiento temporal cuando el número de saldos de cliente < 200 000 es pequeño en comparación con el número de clientes con saldos de cuenta >= 200 000.

SELECT * FROM customer WHERE customer.account_balance < 200000;
SELECT * FROM SensorData WHERE Speed > 65;

Seleccionar un subconjunto de columnas

Use la aplicación del predicado para mejorar el rendimiento de una consulta que selecciona un subconjunto de columnas de una tabla externa.

En esta consulta, SQL Server inicia un trabajo de asignación y reducción para preprocesar el archivo de texto delimitado por Hadoop, de tal modo que únicamente los datos de las dos columnas, customer.name y customer.zip_code, se copien en SQL Server.

SELECT customer.name, customer.zip_code
FROM customer
WHERE customer.account_balance < 200000;

Aplicación para operadores y expresiones básicas

SQL Server permite las siguientes expresiones básicas y operadores para la aplicación del predicado.

  • Operadores de comparación binarios (<, >, =, !=, <>, >= y <=) para valores de hora, fecha y numéricos.
  • Operadores aritméticos (+, -, *, / y %).
  • Operadores lógicos (AND y OR).
  • Operadores unarios (NOT, IS NULL y IS NOT NULL).

Los operadores BETWEEN, NOT, IN y LIKE se pueden insertar. El comportamiento real depende de cómo el optimizador de consultas vuelva a escribir las expresiones de operador como una serie de instrucciones que usan operadores relacionales básicos.

La consulta de este ejemplo tiene varios predicados que se pueden insertar en Hadoop. SQL Server puede insertar trabajos de Map Reduce en Hadoop para ejecutar el predicado customer.account_balance <= 200000. La expresión BETWEEN 92656 AND 92677 se compone también de operaciones binarias y lógicas que se pueden insertar en Hadoop. La operación lógica AND en customer.account_balance AND customer.zipcode es una expresión final.

Dada esta combinación de predicados, los trabajos de Map Reduce pueden ejecutar todos la cláusula WHERE. Solo los datos que cumplen los criterios SELECT se vuelven a copiar en SQL Server.

SELECT * FROM customer 
WHERE customer.account_balance <= 200000 
AND customer.zipcode BETWEEN 92656 AND 92677;

Funciones admitidas para delegación

SQL Server admite las funciones que se indican a continuación para la delegación del predicado.

Funciones de cadena

  • CONCAT
  • DATALENGTH
  • LEN
  • LIKE
  • LOWER
  • LTRIM
  • RTRIM
  • SUBSTRING
  • UPPER

Funciones matemáticas

  • ABS
  • ACOS
  • ASIN
  • ATAN
  • CEILING
  • COS
  • EXP
  • FLOOR
  • POWER
  • SIGN
  • SIN
  • SQRT
  • TAN

Funciones generales

  • COALESCE *
  • NULLIF

* El uso de COLLATE puede impedir la delegación en algunos escenarios. Para obtener más información, consulte Conflicto de intercalación.

Funciones de fecha y hora

  • DATEADD
  • DATEDIFF
  • DATEPART

Sintaxis que impide la delegación

Las siguientes funciones o sintaxis de T-SQL impiden el cálculo de delegación:

  • AT TIME ZONE
  • CONCAT_WS
  • TRANSLATE
  • RAND
  • CHECKSUM
  • BINARY_CHECKSUM
  • HASHBYTES
  • ISJSON
  • JSON_VALUE
  • JSON_QUERY
  • JSON_MODIFY
  • NEWID
  • STRING_ESCAPE
  • COMPRESS
  • DECOMPRESS
  • GREATEST
  • LEAST
  • PARSE

La compatibilidad de la delegación con la sintaxis de FORMAT y TRIM se presentó en la actualización acumulativa 10 de SQL Server 2019 (15.x).

Cláusula de filtro con variable

Si va a especificar una variable en una cláusula de filtro, de forma predeterminada esto impedirá la inserción de la cláusula de filtro. Por ejemplo, si ejecuta la consulta siguiente, la cláusula de filtro no se insertará:

DECLARE @BusinessEntityID INT

SELECT * FROM [Person].[BusinessEntity]  
WHERE BusinessEntityID = @BusinessEntityID;

Para lograr la inserción de la variable, debe habilitar la funcionalidad de revisiones del optimizador de consultas. Esto puede hacerse de una de las formas siguientes:

  • Nivel de instancia: habilite la marca de seguimiento 4199 como parámetro de inicio para la instancia
  • Nivel de base de datos: en el contexto de la base de datos que tiene los objetos externos de PolyBase, ejecute ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON
  • Nivel de consulta: use la sugerencia de consulta OPTION (QUERYTRACEON 4199) u OPTION (USE HINT ('ENABLE_QUERY_OPTIMIZER_HOTFIXES'))

Esta limitación se aplica a la ejecución de sp_executesql. La limitación también se aplica al uso de algunas funciones en la cláusula de filtro.

Nota: La capacidad de insertar la variable se incluyó por primera vez en SQL Server 2019 CU5.

Conflicto de intercalación

Al trabajar con datos con una intercalación diferente, es posible que no pueda aplicarse la delegación; los operadores como COLLATE también pueden interfierir con el resultado. Se admiten las intercalaciones iguales o las intercalaciones binarias. Para obtener más información, consulte Cómo identificar si se ha producido una delegación.

Delegación de archivos parquet

A partir de SQL Server 2022 (16.x), PolyBase introdujo compatibilidad con archivos parquet. SQL Server es capaz de realizar la eliminación de filas y columnas al realizar la delegación con parquet. Al trabajar con archivos parquet, se pueden delegar las siguientes operaciones:

  • Operadores de comparación binarios (>, >=, <= y <) para valores de hora, fecha y numéricos.
  • Combinación de operadores de comparación (> Y <, >= Y <, > Y <= y <= Y >=).
  • En el filtro de lista (col1 = val1 O col1 = val2 O vol1 = val3).
  • NO ES NULL en la columna.

La presencia de lo siguiente impide la delegación de archivos parquet:

  • Columnas virtuales.
  • Comparación de columnas.
  • Conversiones de tipos de parámetro.

Tipos de datos admitidos

  • bit
  • TinyInt
  • SmallInt
  • BigInt
  • Real
  • Float
  • VARCHAR (Bin2Collation, CodePageConversion, BinCollation)
  • NVARCHAR (Bin2Collation, BinCollation)
  • Binario
  • DateTime2 (precisión predeterminada y de 7 dígitos)
  • Date
  • Time (precisión predeterminada y de 7 dígitos)
  • Numérico *

* Se admite cuando la escala de parámetros se alinea con la escala de columnas o cuando el parámetro se convierte explícitamente en decimal.

Tipos de datos que impiden la delegación de parquet

  • Money
  • SmallMoney
  • DateTime
  • SmallDateTime

Ejemplos

Forzar aplicación

SELECT * FROM [dbo].[SensorData]
WHERE Speed > 65
OPTION (FORCE EXTERNALPUSHDOWN);

Deshabilitar aplicación

SELECT * FROM [dbo].[SensorData]
WHERE Speed > 65
OPTION (DISABLE EXTERNALPUSHDOWN);

Pasos siguientes

Consulte también