Vistas materializadas de Amazon Redshift le permiten mejorar significativamente el rendimiento de consultas complejas que se ejecutan con frecuencia como parte de sus aplicaciones de extracción, carga y transformación (ELT), inteligencia comercial (BI) o tableros. Las vistas materializadas precalculan y almacenan los conjuntos de resultados de la consulta SQL en la definición de la vista. Las vistas materializadas aceleran el acceso a los datos, porque la consulta no necesita volver a ejecutar el cálculo cada vez que se ejecuta la consulta, lo que también reduce el consumo de recursos.

Desplazamiento al rojo de Amazon tiene la capacidad de reescribir automáticamente sus consultas SQL que no hacen referencia explícita a las vistas materializadas existentes para usar una vista materializada existente si mejorará el rendimiento. Esta característica es valiosa y, en algunos casos, la única opción para optimizar el rendimiento. Considere aplicaciones ISV empaquetadas o incluso solo informes: los usuarios a menudo no tienen acceso al SQL para optimizar. En algunos casos, incluso si tienen acceso, el código o script es tan antiguo que nadie está familiarizado con él y no sabe qué regresiones podría introducir incluso un pequeño cambio.

En esta publicación, describimos cómo funciona la función de reescritura automática de consultas y algunos escenarios en los que podría aprovechar esta función. Para obtener información sobre la función de vista materializada en sí, consulte Acelere sus consultas de ELT y BI con las vistas materializadas de Amazon Redshift y Creación de vistas materializadas en Amazon Redshift.

Todos los ejemplos de esta publicación se ejecutan en un clúster ra8xlarge de 3.4 nodos con el Conjunto de datos de referencia en la nube TPC-DS de 3 TB.

Veamos tres escenarios diferentes en los que la función de reescritura automática de consultas podría ayudar: optimizar uniones entre dos tablas grandes, optimizar uniones para tablas que tienen múltiples rutas de unión y optimizar exploraciones de tablas.

Optimice las uniones entre dos tablas grandes

Hay muchas situaciones en las que tiene dos mesas grandes que se unen con frecuencia. En este caso, crear una vista materializada que una estas dos tablas podría ayudar a mejorar el rendimiento de esas consultas. Las vistas materializadas precalculan la unión y almacenan los resultados para que las ejecuciones posteriores solo necesiten recuperar los resultados guardados; no es necesario ejecutar los costosos JOIN cada vez. Con la reescritura automática de consultas, ninguna de las consultas del usuario final debe modificarse para hacer referencia a la vista materializada. Al crear el plan de explicación para la consulta, Amazon Redshift reemplaza la unión entre las dos tablas con la vista materializada.

De forma predeterminada, la reescritura automática de consultas usa una vista materializada solo si está actualizada y refleja todos los cambios de sus tablas base. Esto significa que la consulta no se vuelve a escribir para usar la vista materializada si las tablas base tienen actualizaciones más recientes que aún no se reflejan en la vista materializada.

Por ejemplo, considere la siguiente consulta SQL. La consulta une dos tablas: store_sales (8,639,936,081 filas) y customer (30,000,000 filas):

SELECT cust.c_customer_id FROM store_sales sales
INNER JOIN customer cust
ON sales.ss_customer_sk = cust.c_customer_sk
GROUP BY cust.c_customer_id;

La consulta se ejecuta en 545,520 milisegundos; el siguiente es el plan de explicación para la consulta:

XN HashAggregate (cost=9602679386653.98..9602679386653.98 rows=29705556 width=20) -> XN Hash Join DS_BCAST_INNER (cost=375000.00..9602659714194.54 rows=7868983773 width=20) Hash Cond: (""outer"".ss_customer_sk = ""inner"".c_customer_sk) -> XN Seq Scan on store_sales sales (cost=0.00..86399365.12 rows=8245454518 width=4) Filter: (ss_customer_sk IS NOT NULL) -> XN Hash (cost=300000.00..300000.00 rows=30000000 width=24) -> XN Seq Scan on customer cust (cost=0.00..300000.00 rows=30000000 width=24)

Vamos a crear una vista materializada que calcule previamente la unión entre el store_sales y customer tablas utilizando la siguiente instrucción SQL:

CREATE MATERIALIZED VIEW cust_store_sales
AS SELECT cust.c_customer_id
, cust.c_first_name
, cust.c_last_name
, sales.ss_item_sk
, sales.ss_quantity
, cust.c_current_addr_sk
FROM store_sales sales
INNER JOIN customer cust
ON sales.ss_customer_sk = cust.c_customer_sk;

Ahora volvamos a ejecutar la consulta original:

SELECT cust.c_customer_id FROM store_sales sales
INNER JOIN customer cust
ON sales.ss_customer_sk = cust.c_customer_sk
GROUP BY cust.c_customer_sk;

La consulta se ejecuta mucho más rápido (46,493 milisegundos). Esto se debe a la función de reescritura automática de consultas, que ha reescrito la consulta anterior para usar la vista materializada recién creada en lugar de unir ambas tablas. El plan de explicación para esta consulta muestra este cambio:

XN HashAggregate (cost=103138905.60..103138905.60 rows=29705556 width=20) -> XN Seq Scan on mv_tbl__cust_store_sales__0 derived_table1 (cost=0.00..82511124.48 rows=8251112448 width=20)

La ejecución de la consulta original también consumió 1,263 segundos de CPU y leyó 45,013 898 bloques de datos, mientras que la consulta que se ejecutó después de la creación de la vista materializada solo consumió 29,256 segundos de CPU y leyó 29 35 bloques. Eso es una reducción del XNUMX% en el consumo de CPU y del XNUMX% en los bloques leídos.

El optimizador también puede reescribir la siguiente consulta para usar la vista materializada creada previamente, que incluye la unión adicional a la customer_address mesa:

SELECT
cust.c_customer_id
,addr.ca_state
FROM store_sales sales
INNER JOIN customer cust
ON sales.ss_customer_sk = cust.c_customer_sk
INNER JOIN customer_address addr
ON cust.c_current_addr_sk = addr.ca_address_sk
GROUP BY cust.c_customer_id, addr.ca_state; XN HashAggregate (cost=30242919089.37..30242919089.37 rows=1544688912 width=26) -> XN Hash Join DS_BCAST_INNER (cost=542661.20..30201663527.13 rows=8251112448 width=26) Hash Cond: ("outer".c_current_addr_sk = "inner".ca_address_sk) -> XN Seq Scan on mv_tbl__cust_store_sales_1__0 derived_table1 (cost=0.00..82511124.48 rows=8251112448 width=24) -> XN Hash (cost=150000.00..150000.00 rows=15000000 width=10) -> XN Seq Scan on customer_address addr (cost=0.00..150000.00 rows=15000000 width=10)

Optimice las uniones para tablas que tienen múltiples rutas de unión

Para tablas grandes en Amazon Redshift, el estilo de distribución ideal sería 'KEY', siendo la clave de distribución la columna que se usa con más frecuencia en la cláusula JOIN. Hay situaciones en las que algunas tablas grandes tienen varias rutas de combinación. El 50% de las consultas pueden usar una columna en particular para unirse a la tabla, y el otro 50% de las consultas pueden usar una columna diferente para unirse a la tabla. Ambos tipos de consultas son importantes y tienen requisitos de rendimiento estrictos. En este caso, podría elegir una columna como clave de distribución para la tabla y luego crear una vista materializada con la segunda columna como clave de distribución. Esto es posible porque las vistas materializadas pueden tener su propia distribución y claves de clasificación.

He aquí un ejemplo para ilustrar cómo funciona esto.

La web_sales tabla (2,159,968,881 filas) tiene la clave de distribución ws_order_number. Esto ayuda a optimizar la mayoría de las consultas (el 70 % de las uniones a esta tabla usan ws_order_number como la columna de unión). El 30% restante utiliza la columna ws_bill_customer_sk para unirse a la tabla, como se muestra en la siguiente instrucción SQL. Esta consulta tardó 12,790 XNUMX milisegundos en ejecutarse.

SELECT c_customer_id
, c_email_address FROM web_sales ws
INNER JOIN customer cs
ON ws.ws_bill_customer_sk=cs.c_customer_sk;

Podemos crear la vista materializada para ayudar a mejorar el rendimiento del 30 % restante de las consultas. Tenga en cuenta la palabra clave DISTKEY en el siguiente código. Hemos definido una nueva clave de distribución para la vista materializada (ws_bill_customer_sk):

CREATE MATERIALIZED VIEW web_sales_cust_dist
DISTKEY (ws_bill_customer_sk)
AS
SELECT * FROM web_sales;

Volver a ejecutar la siguiente consulta devuelve filas mucho más rápido que antes (7,715 milisegundos frente a 12,790 XNUMX milisegundos):

SELECT c_customer_id
, c_email_address FROM web_sales ws
INNER JOIN customer cs
ON ws.ws_bill_customer_sk=cs.c_customer_sk;

Nuevamente, el plan de explicación de la consulta ha cambiado; ahora hace referencia a la vista materializada aunque la instrucción SQL no hace referencia explícita a la vista materializada:

XN Hash Join DS_DIST_NONE (cost=375000.00..696964927.69 rows=2159968768 width=74) Hash Cond: (""outer"".ws_bill_customer_sk = ""inner"".c_customer_sk) -> XN Seq Scan on mv_tbl__web_sales_cust_dist__0 derived_table1 (cost=0.00..21599687.68 rows=2159968768 width=4) -> XN Hash (cost=300000.00..300000.00 rows=30000000 width=78) -> XN Seq Scan on customer cs (cost=0.00..300000.00 rows=30000000 width=78)

Optimizar escaneos de tablas

Los escaneos de tablas en Amazon Redshift se hacen eficientes mediante el uso de claves de ordenación. Las claves de clasificación determinan el orden en que se almacenan las columnas en los bloques de datos. Elegir una columna que aparece con frecuencia en sus condiciones de filtrado como clave de clasificación puede mejorar significativamente el rendimiento de la consulta.

Las claves de ordenación compuestas con varias columnas se pueden definir en su tabla en caso de que varias columnas sean buenas candidatas para claves de ordenación. Pero en algunas situaciones en las que dos o más columnas de alta cardinalidad son candidatas a clave de ordenación, es posible que la clave de ordenación compuesta no proporcione un rendimiento adecuado. En estos casos, se podría crear una vista materializada con una clave de clasificación diferente para mantener esos datos en un orden de clasificación alternativo para ayudar a atender un subconjunto de las consultas.

En la siguiente consulta de ejemplo, el web_sales la tabla usa la columna ws_sold_date_sk para la clave de ordenación, porque esta es la columna que se usa comúnmente para filtrar filas. Un conjunto más pequeño de uso de consultas ws_sales_price para filtrar filas. Dado que ambos ws_sold_date_sk y ws_sales_price son columnas de alta cardinalidad con muchos valores únicos, es posible que una clave de ordenación compuesta con ambas columnas no funcione para todos los patrones de consulta.

SELECT *
FROM web_sales WHERE ws_sales_price BETWEEN 50 AND 100;

Creemos la siguiente vista materializada y veamos cómo puede ayudar a mejorar el rendimiento de la consulta anterior:

CREATE MATERIALIZED VIEW web_sales_sort_on_price
SORTKEY (ws_sales_price)
AS
SELECT * FROM web_sales;

Ejecutar la siguiente consulta devuelve filas mucho más rápido (5 milisegundos frente a 3,548 milisegundos) porque la reescritura automática de consultas utiliza la vista materializada:

SELECT *
FROM web_sales WHERE ws_sales_price BETWEEN 50 AND 100;

El siguiente es el nuevo plan de explicación:

XN Seq Scan on mv_tbl__web_sales_cust_dist__0 derived_table1 (cost=0.00..32399531.52 rows=10799844 width=260) Filter: ((ws_sales_price <= 100.00) AND (ws_sales_price >= 50.00))

Conclusión

Las vistas materializadas en Amazon Redshift pueden ser una poderosa herramienta de optimización si se usan adecuadamente. Con la reescritura automática de consultas, puede optimizar las consultas sin ningún impacto para los usuarios finales o sus consultas. Esto le permite crear vistas materializadas después de que la aplicación se haya puesto en marcha. Algunos clientes planifican esto como parte de su estrategia de optimización del rendimiento al crear nuevas aplicaciones. El valor real es que puede optimizar consultas y cargas de trabajo sin necesidad de modificar el código fuente o los scripts, y puede beneficiarse incluso con una coincidencia parcial.

Acerca de los autores

Harshida Patel es Arquitecto Sr. Especialista en Soluciones, Análisis con AWS.

Jeetesh Srivastva es gerente sénior, arquitecto especialista en soluciones en AWS. Se especializa en Amazon Redshift y trabaja con los clientes para implementar soluciones escalables mediante Amazon Redshift y otros servicios analíticos de AWS. Ha trabajado para ofrecer soluciones analíticas locales y basadas en la nube para clientes en sectores verticales de la industria bancaria, financiera y hotelera.

San Das es un arquitecto de soluciones especializado en análisis en AWS y ayuda a los clientes a crear soluciones en la nube escalables que ayuden a convertir los datos en información procesable.

Somdeb Bhattacharjee es arquitecto de soluciones empresariales en AWS.

Fuente: https://aws.amazon.com/blogs/big-data/optimize-your-analytical-workloads-using-the-automatic-query-rewrite-feature-of-amazon-redshift-materialized-views/