Este es el quinto de una serie de publicaciones. Nos complace compartir docenas de funciones nuevas para automatizar la conversión de su esquema; preservar su inversión en scripts, informes y aplicaciones existentes; acelerar el rendimiento de las consultas; y potencialmente simplificar sus migraciones de almacenes de datos heredados a Desplazamiento al rojo de Amazon.

Amazon Redshift es el almacén de datos en la nube líder. Ningún otro almacén de datos hace que sea tan fácil obtener nuevos conocimientos a partir de sus datos. Con Amazon Redshift, puede consultar exabytes de datos en su almacén de datos, almacenes de datos operativos y lago de datos mediante SQL estándar. También puede integrar otros servicios de AWS como EMR de Amazon, Atenea amazónica, Amazon SageMaker, Pegamento AWS, Formación del lago AWSy Kinesis amazónica para utilizar todas las capacidades analíticas en la nube de AWS.

Hasta ahora, migrar un almacén de datos a AWS ha sido una tarea compleja que implicaba una gran cantidad de esfuerzo manual. Debe remediar manualmente las diferencias de sintaxis, inyectar código para reemplazar las funciones patentadas y ajustar manualmente el rendimiento de las consultas y los informes en la nueva plataforma.

Las cargas de trabajo heredadas pueden depender de características patentadas que no son ANSI que no son compatibles directamente con las bases de datos modernas como Amazon Redshift. Por ejemplo, muchas aplicaciones de Teradata utilizan tablas SET, que imponen la exclusividad de filas completas: no puede haber dos filas en una tabla que sean idénticas en todos los valores de sus atributos.

Si es un usuario de Amazon Redshift, es posible que desee implementar la semántica SET, pero no puede confiar en una función de base de datos nativa. Puede usar los patrones de diseño en esta publicación para emular la semántica SET en su código SQL. Como alternativa, si está migrando una carga de trabajo a Amazon Redshift, puede utilizar el Herramienta de conversión de esquemas de AWS (AWS SCT) para aplicar automáticamente los patrones de diseño como parte de su conversión de código.

En esta publicación, describimos los patrones de diseño de SQL y analizamos su rendimiento, y mostramos cómo AWS SCT puede automatizar esto como parte de la migración de su almacén de datos. Comencemos por comprender cómo se comportan las tablas SET en Teradata.

Tablas SET de Teradata

A primera vista, una tabla SET puede parecer similar a una tabla que tiene una clave principal definida en todas sus columnas. Sin embargo, existen algunas diferencias semánticas importantes con respecto a las claves primarias tradicionales. Considere la siguiente definición de tabla en Teradata:

CREATE SET TABLE testschema.sales_by_month ( sales_dt DATE
, amount DECIMAL(8,2)
);

Rellenamos la tabla con cuatro filas de datos, de la siguiente manera:

select * from testschema.sales_by_month order by sales_dt; *** Query completed. 4 rows found. 2 columns returned. *** Total elapsed time was 1 second. sales_dt amount
-------- ----------
22/01/01 100.00
22/01/02 200.00
22/01/03 300.00
22/01/04 400.00

Tenga en cuenta que no definimos un ÍNDICE PRIMARIO ÚNICO (similar a una clave principal) en la tabla. Ahora, cuando intentamos insertar una nueva fila en la tabla que es un duplicado de una fila existente, la inserción falla:

INSERT IGNORE INTO testschema.sales_by_month values (20220101, 100); *** Failure 2802 Duplicate row error in testschema.sales_by_month. Statement# 1, Info =0 *** Total elapsed time was 1 second.

De manera similar, si intentamos actualizar una fila existente para que se convierta en un duplicado de otra fila, la actualización falla:

UPDATE testschema.sales_by_month SET sales_dt = 20220101, amount = 100
WHERE sales_dt = 20220104 and amount = 400; *** Failure 2802 Duplicate row error in testschema.sales_by_month. Statement# 1, Info =0 *** Total elapsed time was 1 second.

En otras palabras, las declaraciones INSERT-VALUE y UPDATE simples fallan si introducen filas duplicadas en una tabla SET de Teradata.

Hay una notable excepción a esta regla. Considere la siguiente tabla de etapas, que tiene los mismos atributos que la tabla de destino:

CREATE MULTISET TABLE testschema.sales_by_month_stg ( sales_dt DATE
, amount DECIMAL(8,2)
);

La tabla de preparación es una tabla MULTISET y acepta filas duplicadas. Rellenamos tres filas en la tabla de preparación. La primera fila es un duplicado de una fila en la tabla de destino. Las filas segunda y tercera son duplicados entre sí, pero no duplican ninguna de las filas de destino.

select * from testschema.sales_by_month_stg; *** Query completed. 3 rows found. 2 columns returned. *** Total elapsed time was 1 second. sales_dt amount
-------- ----------
22/01/01 100.00
22/01/05 500.00
22/01/05 500.00

Ahora insertamos con éxito los datos de preparación en la tabla de destino (que es una tabla SET):

INSERT IGNORE INTO testschema.sales_by_month (sales_dt, amount)
SELECT sales_dt, amount FROM testschema.sales_by_month_stg; *** Insert completed. One row added. *** Total elapsed time was 1 second.

Si examinamos la tabla de destino, podemos ver que se ha insertado una sola fila para (2022-01-05, 500) y se ha descartado la fila duplicada para (2022-01-01, 100). Básicamente, Teradata descarta silenciosamente cualquier fila duplicada cuando realiza una instrucción INSERT-SELECT. Esto incluye los duplicados que están en la tabla provisional y los duplicados que se comparten entre las tablas provisional y de destino.

select * from testschema.sales_by_month order by sales_dt; *** Query completed. 6 rows found. 2 columns returned. *** Total elapsed time was 1 second. sales_dt amount
-------- ----------
22/01/01 100.00
22/01/02 200.00
22/01/03 300.00
22/01/03 200.00
22/01/04 400.00
22/01/05 500.00

Esencialmente, las tablas SET se comportan de manera diferente según el tipo de operación que se ejecute. Una operación INSERT-VALUE o UPDATE sufre un error si introduce una fila duplicada en el destino. Una operación INSERTAR-SELECCIONAR no sufre un error si la tabla de preparación contiene una fila duplicada o si se comparte una fila duplicada entre las tablas de preparación y la tabla.

En esta publicación, no entramos en detalles sobre cómo convertir declaraciones INSERT-VALUE o UPDATE. Estas instrucciones generalmente involucran una o varias filas y tienen menos impacto en términos de rendimiento que las declaraciones INSERT-SELECT. Para las declaraciones INSERT-VALUE o UPDATE, puede materializar la fila (o filas) que se están creando y unir ese conjunto a la tabla de destino para buscar duplicados.

INSERTAR-SELECCIONAR

En el resto de esta publicación, analizamos cuidadosamente las declaraciones INSERT-SELECT. Los clientes nos han dicho que las operaciones INSERT-SELECT pueden comprender hasta el 78 % de la carga de trabajo INSERT en las tablas SET. Nos interesan las declaraciones con la siguiente forma:

INSERT into <target table> SELECT * FROM <staging table>

El esquema de la tabla provisional es idéntico al de la tabla de destino columna por columna. Como mencionamos anteriormente, una fila duplicada puede aparecer en dos circunstancias diferentes:

• La tabla de etapas no es exclusiva del conjunto, lo que significa que hay dos o más duplicados de filas completas en los datos de etapas.
• Hay una fila x en la tabla de etapas y una fila x idéntica en la tabla de destino

Debido a que Amazon Redshift admite la semántica de tablas de varios conjuntos, es posible que la tabla provisional contenga duplicados (la primera circunstancia que mencionamos). Por lo tanto, cualquier automatización debe abordar ambos casos, ya que cualquiera puede introducir un duplicado en una tabla de Amazon Redshift.

Con base en este análisis, implementamos los siguientes algoritmos:

• MENOS – Esto implementa la deduplicación de lógica de conjunto completo utilizando SQL MINUS. MINUS funciona en todos los casos, incluso cuando la tabla de preparación no es única y cuando la intersección de la tabla de preparación y la tabla de destino no está vacía. MINUS también tiene la ventaja de que los valores NULL no requieren una lógica de comparación especial para superar las comparaciones NULL a NULL. MENOS tiene la siguiente sintaxis:

  INSERT IGNORE INTO <target table> (<column list>)
  SELECT <column list> FROM <staging table> MINUS
  SELECT <column list> FROM <target table>;

• MENOS-MIN-MAX – Esta es una optimización en MENOS que incorpora un filtro para limitar el escaneo de la tabla de destino en función de los valores en la tabla de etapas. Los filtros mínimo/máximo permiten que el motor de consulta omita una gran cantidad de bloques durante los recorridos de la tabla. Ver Trabajar con claves de clasificación para más información.

  INSERT IGNORE INTO <target table>(<column list>)
  SELECT <column list> FROM <staging table> MINUS
  SELECT <column list> FROM <target table>
  WHERE <target table>.<sort key> >= (SELECT MIN(<sort key>) FROM <staging table>) AND <target table>).<sort key> <= (SELECT MAX(<sort key>) FROM <staging table>)
  );

También consideramos otros algoritmos, pero no recomendamos que los use. Por ejemplo, puede realizar un GROUP BY para eliminar duplicados en la tabla de preparación, pero este paso no es necesario si usa el operador MINUS. También puede realizar una combinación externa izquierda (o derecha) para encontrar duplicados compartidos entre las tablas de ensayo y de destino, pero luego se necesita lógica adicional para tener en cuenta las condiciones NULL = NULL.

Rendimiento

Probamos los algoritmos MINUS y MINUS-MIN-MAX en Amazon Redshift. Ejecutamos los algoritmos en dos clústeres de Amazon Redshift. La primera configuración constaba de 6 nodos x ra3.4xlarge. El segundo constaba de 12 nodos x ra3.4xlarge. Cada nodo contenía 12 CPU y 96 GB de memoria.

Creamos las tablas de etapa y de destino con claves de clasificación y distribución idénticas para minimizar el movimiento de datos. Cargamos el mismo conjunto de datos de destino en ambos clústeres. El conjunto de datos de destino constaba de 1.1 millones de filas de datos. Luego creamos conjuntos de datos provisionales que iban desde 20 millones hasta 200 millones de filas, en incrementos de 20 millones de filas.

El siguiente gráfico muestra nuestros resultados.

Los datos de prueba se generaron artificialmente y hubo cierto sesgo en los valores clave de distribución. Esto se manifiesta en las pequeñas desviaciones de la linealidad en el desempeño.

Sin embargo, puede observar el aumento de rendimiento que ofrece el algoritmo MINUS-MIN-MAX sobre el algoritmo MINUS básico (comparando líneas naranjas o líneas azules entre sí). Si está implementando tablas SET en Amazon Redshift, le recomendamos que utilice MINUS-MIN-MAX porque este algoritmo proporciona una feliz convergencia de código simple y legible y buen rendimiento.

Automatización

Todas las tablas de Amazon Redshift permiten filas duplicadas, es decir, son tablas MULTISET por defecto. Si está convirtiendo una carga de trabajo de Teradata para que se ejecute en Amazon Redshift, deberá aplicar la semántica SET fuera de la base de datos.

Nos complace compartir que AWS SCT convertirá automáticamente su código SQL que opera en las tablas SET. AWS SCT reescribirá INSERT-SELECT que carga las tablas SET para incorporar los patrones de reescritura que describimos anteriormente.

Veamos cómo funciona esto. Suponga que tiene la siguiente definición de tabla de destino en Teradata:

CREATE SET TABLE testschema.fact ( id bigint NOT NULL
, se_sporting_event_id INTEGER NOT NULL
, se_sport_type_name VARCHAR(15) NOT NULL
, se_home_team_id INTEGER NOT NULL
, se_away_team_id INTEGER NOT NULL
, se_location_id INTEGER NOT NULL
, se_start_date_time DATE NOT NULL
, se_sold_out INTEGER DEFAULT 0 NOT NULL
, stype_sport_type_name varchar(15) NOT NULL
, stype_short_name varchar(10) NOT NULL
, stype_long_name varchar(60) NOT NULL
, stype_description varchar(120)
, sd_sport_type_name varchar(15) NOT NULL
, sd_sport_league_short_name varchar(10) NOT NULL
, sd_short_name varchar(10) NOT NULL
, sd_long_name varchar(60)
, sd_description varchar(120)
, sht_id INTEGER NOT NULL
, sht_name varchar(30) NOT NULL
, sht_abbreviated_name varchar(10)
, sht_home_field_id INTEGER , sht_sport_type_name varchar(15) NOT NULL
, sht_sport_league_short_name varchar(10) NOT NULL
, sht_sport_division_short_name varchar(10)
, sat_id INTEGER NOT NULL
, sat_name varchar(30) NOT NULL
, sat_abbreviated_name varchar(10)
, sat_home_field_id INTEGER , sat_sport_type_name varchar(15) NOT NULL
, sat_sport_league_short_name varchar(10) NOT NULL
, sat_sport_division_short_name varchar(10)
, sl_id INTEGER NOT NULL
, sl_name varchar(60) NOT NULL
, sl_city varchar(60) NOT NULL
, sl_seating_capacity INTEGER
, sl_levels INTEGER
, sl_sections INTEGER
, seat_sport_location_id INTEGER
, seat_seat_level INTEGER
, seat_seat_section VARCHAR(15)
, seat_seat_row VARCHAR(10)
, seat_seat VARCHAR(10)
, seat_seat_type VARCHAR(15)
, pb_id INTEGER NOT NULL
, pb_full_name varchar(60) NOT NULL
, pb_last_name varchar(30)
, pb_first_name varchar(30)
, ps_id INTEGER NOT NULL
, ps_full_name varchar(60) NOT NULL
, ps_last_name varchar(30)
, ps_first_name varchar(30)
)
PRIMARY INDEX(id)
;

La tabla de etapa es idéntica a la tabla de destino, excepto que se crea como una tabla MULTISET en Teradata.

A continuación, creamos un procedimiento para cargar la tabla de hechos desde la tabla de etapas. El procedimiento contiene una sola instrucción INSERT-SELECT:

REPLACE PROCEDURE testschema.insert_select() BEGIN INSERT IGNORE INTO testschema.test_fact SELECT * FROM testschema.test_stg;
END;

Ahora usamos AWS SCT para convertir el procedimiento almacenado de Teradata a Amazon Redshift. Primero, seleccione el procedimiento almacenado en el árbol de la base de datos de origen, luego haga clic con el botón derecho y elija Convertir esquema.

AWS SCT convierte el procedimiento almacenado (y INSERT-SELECT incrustado) mediante el patrón de reescritura MINUS-MIN-MAX.

¡Y eso es! Actualmente, AWS SCT solo realiza reescrituras para INSERT-SELECT porque esas declaraciones son muy utilizadas por las cargas de trabajo de ETL y tienen el mayor impacto en el rendimiento. Aunque el ejemplo que usamos estaba incrustado en un procedimiento almacenado, también puede usar AWS SCT para convertir las mismas declaraciones si están en scripts, macros o programas de aplicación de BTEQ. Descargar el última versión de AWS SCT ¡y pruébalo!

Conclusión

En esta publicación, mostramos cómo implementar la semántica de tablas SET en Amazon Redshift. Puede utilizar los patrones de diseño descritos para desarrollar nuevas aplicaciones que requieran la semántica SET. O bien, si está convirtiendo una carga de trabajo de Teradata existente, puede usar AWS SCT para convertir automáticamente sus instrucciones INSERT-SELECT para que conserven la semántica de la tabla SET.

Volveremos pronto con la próxima entrega de esta serie. Vuelva a consultar para obtener más información sobre cómo automatizar sus migraciones de Teradata a Amazon Redshift. Mientras tanto, puede obtener más información sobre Desplazamiento al rojo de Amazon y SCT de AWS. ¡Feliz migración!

Acerca de los autores

michael soo es ingeniero principal de bases de datos en el equipo del servicio de migración de bases de datos de AWS. Crea productos y servicios que ayudan a los clientes a migrar sus cargas de trabajo de bases de datos a la nube de AWS.

Po Hong, PhD, es Arquitecto Principal de Datos de la Práctica Especializada Global (GSP) de Arquitectura de Datos Moderna, Servicios Profesionales de AWS. Le apasiona ayudar a los clientes a adoptar soluciones innovadoras y migrar de almacenes de datos MPP a gran escala a la arquitectura de datos moderna de AWS.

• Coinsmart. El mejor intercambio de Bitcoin y criptografía de Europa.
• Platoblockchain. Inteligencia del Metaverso Web3. Conocimiento amplificado. ACCESO LIBRE.
• CriptoHawk. Radar de altcoins. Prueba gratis.
• Fuente: https://aws.amazon.com/blogs/big-data/part-5-accelerate-your-data-warehouse-migration-to-amazon-redshift/