Cálculos de Pushdown en PolyBase

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 pushdown estuvieron disponibles para los orígenes de datos externos de Hadoop. SQL Server 2019 (15.x) introdujo computaciones pushdown para otros tipos de fuentes de datos externas.

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.

Habilitación del cálculo de la aplicación

Los artículos siguientes incluyen información sobre la configuración del cálculo pushdown 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 Filtros 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 No No 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.

+ Oracle admite la delegación de combinaciones, pero es posible que tenga que crear estadísticas en las columnas de combinación para lograr la delegación.

Nota:

La computación de empuje puede ser bloqueada por cierta sintaxis de T-SQL. 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 diferencias entre los dos proveedores en cuanto al cálculo de pushdown.

Para utilizar la funcionalidad de optimización de cálculo con Hadoop, el clúster de Hadoop de destino debe contar con los componentes principales: HDFS, YARN y MapReduce, y el servidor de historial de tareas habilitado. PolyBase envía la consulta pushdown a través de MapReduce y obtiene 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 No es igual
Suma 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 pushdown de PolyBase, puede delegar tareas de cálculo a 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 insertar combinaciones, proyecciones, agregaciones y filtros en orígenes de datos externos, aprovechando el proceso remoto y restringiendo los datos enviados a través de la red.

Delegación de la combinación

PolyBase puede facilitar el pushdown del operador de unión cuando se unen dos tablas externas en la misma fuente de datos externa, lo que mejora considerablemente el rendimiento.

Cuando el origen de datos externo realiza la combinación, reduce la cantidad de movimiento de datos y mejora el rendimiento de las consultas. Sin el pushdown de unión, SQL Server debe traer los datos de ambas tablas localmente a tempdb y, a continuación, realizar la unión.

En el caso de combinaciones distribuidas (unir una tabla local a una tabla externa), a menos que el filtro se aplique a la tabla externa combinada, SQL Server debe traer todos los datos de la tabla externa localmente para tempdb realizar la operación de combinación. Por ejemplo, la consulta siguiente no tiene ningún filtrado en la condición de combinación de tabla externa, lo que da como resultado la lectura de todos los datos de la tabla externa.

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

Dado que la unión utiliza la columna E.id de la tabla externa, al añadir una condición de filtro a dicha columna, SQL Server puede aplicar el filtro en la tabla externa, 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

Utiliza el predicado pushdown 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 map-reduce para preprocesar el archivo de texto delimitado en 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 admite estas expresiones y operadores básicos para la aplicación descendente de predicados:

  • 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 mover hacia abajo en función de cómo el optimizador de consultas vuelva a escribir las expresiones de operador como una serie de instrucciones mediante operadores relacionales básicos.

La consulta de este ejemplo tiene varios predicados que se pueden aplicar 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 MapReduce pueden ejecutar toda 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 estas funciones para la aplicación descendente de predicados:

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

Estas funciones o elementos sintácticos de T-SQL impiden el cálculo de la aplicación descendente:

  • 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

Cuando se especifica una variable en una cláusula de filtro, SQL Server, de forma predeterminada, no aplica la cláusula de filtro. Por ejemplo, la siguiente consulta no aplica de forma descendente la cláusula de filtro:

DECLARE @BusinessEntityID INT

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

Para habilitar la aplicación descendente de la variable, active la funcionalidad de revisiones del optimizador de consultas mediante uno de estos métodos:

  • 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: utilice la sugerencia de consulta OPTION (QUERYTRACEON 4199) o OPTION (USE HINT ('ENABLE_QUERY_OPTIMIZER_HOTFIXES')).

Esta limitación se aplica a la ejecución de sp_executesql y a algunas funciones de la cláusula filter.

SQL Server 2019 CU5 presentó por primera vez la capacidad de realizar 'push down' de la variable.

Para obtener más información, consulte sp_executesql.

Conflicto de intercalación

Es posible que la transferencia no funcione con datos que tengan colaciones diferentes. Los operadores como COLLATE también pueden interferir con el resultado. SQL Server admite intercalaciones iguales o intercalaciones binarias. Para obtener más información, vea Cómo saber si se ha producido un empuje externo.

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.

Orígenes de datos externos admitidos

La transferencia de Parquet es compatible con las siguientes fuentes de datos externas:

  • Almacenamiento de objetos compatible con S3
  • Azure Blob Storage (Servicio de almacenamiento de blobs de Azure)
  • Azure Data Lake Storage Gen2

Para obtener detalles de configuración, consulte:

Operaciones de transferencia

SQL Server puede transferir estas operaciones con archivos Parquet:

  • 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).
  • IS NOT NULL sobre una columna.

Estos elementos impiden la transferencia para archivos Parquet:

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

Supported data types (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)
  • numeric *

* 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

  • dinero
  • smallmoney
  • datetime
  • smalldatetime

Eliminación de particiones con estructuras de carpetas

PolyBase puede usar estructuras de carpetas para la eliminación de particiones, lo que reduce la cantidad de datos examinados durante las consultas. Al organizar archivos parquet en carpetas jerárquicas (por ejemplo, por año, mes u otras claves de partición), PolyBase puede omitir carpetas completas que no coincidan con los predicados de consulta.

Por ejemplo, si estructura los datos como:

/data/year=2024/month=01/*.parquet
/data/year=2024/month=02/*.parquet
/data/year=2025/month=01/*.parquet

Puede consultar particiones específicas usando caracteres comodín en OPENROWSET o en ubicaciones de tablas externas.

-- Query only January 2025 data
SELECT *
FROM OPENROWSET(
    BULK '/data/year=2025/month=01/*.parquet',
    DATA_SOURCE = 's3_ds',
    FORMAT = 'PARQUET'
) AS [data];

Para la eliminación dinámica de carpetas, realice una consulta sobre una ruta de acceso de carpeta más amplia y utilice predicados filepath() para eliminar las particiones en tiempo de ejecución.

SELECT
    r.filepath(1) AS [year],
    r.filepath(2) AS [month],
    COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
    BULK '/data/year=*/month=*/*.parquet',
    DATA_SOURCE = 's3_ds',
    FORMAT = 'PARQUET'
) WITH (
    customer_id INT,
    amount DECIMAL(10, 2)
) AS [r]
WHERE
    r.filepath(1) = '2025'
    AND r.filepath(2) = '01'
GROUP BY
    r.filepath(1),
    r.filepath(2);

Este enfoque combina la eliminación de particiones en el nivel de carpeta con el pushdown en el nivel de archivo Parquet para un rendimiento óptimo de las consultas. Para obtener un tutorial completo sobre cómo consultar archivos parquet con patrones de carpeta, consulte Virtualizar archivo parquet en un almacenamiento de objetos compatible con S3 con PolyBase.

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);

Contenido relacionado

  • Last updated on