Accélérez la migration de votre entrepôt de données vers Amazon Redshift – Partie 2

Ceci est le deuxième article d'une série en plusieurs parties. Nous sommes ravis de partager des dizaines de nouvelles fonctionnalités pour automatiser la conversion de votre schéma ; préservez votre investissement dans les scripts, rapports et applications existants ; accélérer les performances des requêtes ; et potentiellement réduire le coût global de votre migration vers Redshift d'Amazon. Découvrez le premier post Accélérez la migration de votre entrepôt de données vers Amazon Redshift – Partie 1 pour en savoir plus sur la conversion automatisée des macros, la comparaison de chaînes insensibles à la casse, les identifiants sensibles à la casse et d'autres nouvelles fonctionnalités intéressantes.

Amazon Redshift est le principal entrepôt de données cloud. Aucun autre entrepôt de données ne permet d'obtenir aussi facilement de nouvelles informations à partir de vos données. Avec Amazon Redshift, vous pouvez interroger des exaoctets de données dans votre entrepôt de données, vos magasins de données opérationnels et votre lac de données à l'aide du SQL standard. Vous pouvez également intégrer d'autres services comme Amazon DME, Amazone Athénaet Amazon Sage Maker d'utiliser toutes les capacités d'analyse du cloud AWS.

De nombreux clients ont demandé de l'aide pour migrer leurs moteurs d'entrepôt de données autogérés vers Amazon Redshift. Dans ces cas, vous pouvez disposer de téraoctets (ou pétaoctets) de données historiques, d’une forte dépendance à l’égard de fonctionnalités propriétaires et de milliers de processus et de rapports d’extraction, de transformation et de chargement (ETL) construits au fil des années (ou décennies) d’utilisation.

Jusqu'à présent, la migration d'un entrepôt de données vers AWS était complexe et impliquait une quantité importante d'efforts manuels.

Aujourd'hui, nous sommes heureux de partager des améliorations supplémentaires Outil de conversion de schéma AWS (AWS SCT) pour automatiser vos migrations vers Amazon Redshift. Ces améliorations réduisent le recodage nécessaire pour vos tables de données et, plus important encore, le travail manuel nécessaire pour les vues, procédures stockées, scripts et autres codes d'application qui utilisent ces tables.

Dans cet article, nous présentons l'automatisation des types de données INTERVAL et PERIOD, la conversion de type automatique, la prise en charge des données binaires et quelques autres améliorations demandées par les clients. Nous vous montrons comment utiliser AWS SCT pour convertir des objets à partir d'un entrepôt de données Teradata et fournissons des liens vers la documentation pertinente afin que vous puissiez continuer à explorer ces nouvelles fonctionnalités.

Types de données INTERVALLE

Un INTERVALLE est une durée non ancrée, comme « 1 an » ou « 2 heures », qui n'a pas d'heure de début ou de fin spécifique. Dans Teradata, les données INTERVAL sont implémentées sous forme de 13 types de données distincts en fonction de la granularité du temps représenté. Le tableau suivant résume ces types.

Amazon Redshift ne prend pas en charge les types de données INTERVAL de manière native. Auparavant, si vous utilisiez des types INTERVAL dans votre entrepôt de données, vous deviez développer du code personnalisé dans le cadre du processus de conversion de base de données.

Désormais, AWS SCT convertit automatiquement les types de données INTERVAL pour vous. AWS SCT convertit une colonne INTERVAL en colonne CHARACTER VARYING dans Amazon Redshift. Ensuite, AWS SCT convertit le code de votre application qui utilise la colonne pour émuler la sémantique INTERVAL.

Par exemple, considérons la table Teradata suivante, qui comporte une colonne d'intervalle MOIS. Ce tableau stocke différents types de congés et la durée autorisée pour chacun.

CREATE TABLE testschema.loa_durations ( loa_type_id INTEGER
, loa_name VARCHAR(100) CHARACTER SET LATIN
, loa_duration INTERVAL MONTH(2))
PRIMARY INDEX (loa_type_id);

AWS SCT convertit la table en Amazon Redshift comme suit. Étant donné qu'Amazon Redshift ne dispose pas de type de données INTERVAL natif, AWS SCT le remplace par un type de données VARCHAR.

CREATE TABLE testschema.loa_durations( loa_type_id INTEGER
, loa_name VARCHAR(100)
, loa_duration VARCHAR(64)
)
DISTSTYLE KEY
DISTKEY
(
loa_type_id
)
SORTKEY
(
loa_type_id
);

Supposons maintenant que votre code d'application utilise le loa_duration colonne, comme la vue Teradata suivante. Ici, le champ MOIS INTERVALLE est ajouté à la date actuelle pour calculer la fin d'un congé s'il commence aujourd'hui.

REPLACE VIEW testschema.loa_projected_end_date AS
SELECT loa_type_id loa_type_id
, loa_name loa_name
, loa_duration
, current_date AS today
, current_date + loa_duration AS end_date
FROM
testschema.loa_durations
;

Étant donné que les données sont stockées sous la forme CHARACTER VARYING, AWS SCT injecte le type CAST approprié dans le code Amazon Redshift pour interpréter les valeurs de chaîne comme un intervalle de MOIS. Il convertit ensuite l'arithmétique à l'aide des fonctions de date Amazon Redshift.

CREATE OR REPLACE VIEW testschema.loa_projected_end_date (loa_type_id, loa_name, loa_duration, today, end_date) AS
SELECT loa_type_id AS loa_type_id
, loa_name AS loa_name
, loa_duration
, CURRENT_DATE AS today
, dateadd(MONTH, CAST (loa_duration AS INTEGER),CURRENT_DATE)::DATE AS end_date
FROM testschema.loa_durations
;

De plus, en prime, AWS SCT convertit automatiquement toutes les valeurs littérales INTERVAL que vous pourriez utiliser dans votre code.

Par exemple, considérons la table Teradata suivante. La table contient une colonne DATE, qui enregistre la dernière date à laquelle un employé a été promu.

CREATE TABLE TESTSCHEMA.employees ( id INTEGER
, name VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC
, manager_id INTEGER
, last_promo_date DATE FORMAT 'YY/MM/DD'
)
UNIQUE PRIMARY INDEX ( id );

Supposons maintenant que la base de données contienne une vue qui calcule la prochaine date à laquelle un employé sera éligible à une promotion. Nous mettons en œuvre une règle commerciale selon laquelle les employés qui n'ont jamais été promus sont éligibles à une promotion dans un délai d'un an et demi. Tous les autres salariés deviennent éligibles 1.5 ans après leur dernière promotion. Voir le code suivant :

REPLACE VIEW testschema.eligible_for_promo AS
SELECT id
, name
, last_promo_date
, CASE WHEN last_promo_date is NULL THEN current_date + INTERVAL '18' MONTH ELSE last_promo_date + INTERVAL '2-06' YEAR TO MONTH END eligible_date
FROM employees
;

AWS SCT convertit les valeurs INTERVAL utilisées dans l'instruction CASE et traduit les expressions de date en conséquence :

CREATE OR REPLACE VIEW testschema.eligible_for_promo (id, name, last_promo_date, eligible_date) AS
SELECT id
, name
, last_promo_date
, CASE WHEN last_promo_date IS NULL THEN dateadd(MONTH, 18, CURRENT_DATE)::DATE ELSE dateadd(MONTH, 30, last_promo_date)::DATE END AS eligible_date
FROM testschema.employees
;

Nous sommes enthousiasmés par l'automatisation INTERVAL dans AWS SCT et vous encourageons à l'essayer. Pour plus d'informations sur la mise en route d'AWS SCT, consultez Installation, vérification et mise à jour d'AWS SCT.

Type de données PÉRIODE

Une valeur de données PERIOD représente une durée avec un début et une fin spécifiés. Par exemple, le littéral Teradata “(2021-01-01 to 2021-01-31)” est une période d’une durée de 31 jours qui commence et se termine respectivement le premier et le dernier jour de janvier 2021. Les types de données PERIOD peuvent avoir trois granularités différentes : DATE, TIME ou TIMESTAMP. Le tableau suivant fournit quelques exemples.

Comme pour INTERVAL, le type de données PERIOD n'est pas pris en charge de manière native par Amazon Redshift. Auparavant, si vous utilisiez ces types de données dans vos tables, vous deviez écrire du code personnalisé dans le cadre du processus de conversion de la base de données.

Désormais, AWS SCT convertit automatiquement les types de données PERIOD pour vous. AWS SCT convertit une colonne PERIOD en deux colonnes DATE (ou TIME ou TIMESTAMP), selon le cas sur Amazon Redshift. Ensuite, AWS SCT convertit le code de votre application qui utilise la colonne pour émuler la sémantique du moteur source.

Par exemple, considérons la table Teradata suivante :

CREATE SET TABLE testschema.period_table ( id INTEGER
, period_col PERIOD(timestamp)) UNIQUE PRIMARY INDEX (id);

AWS SCT convertit la colonne PERIOD(TIMESTAMP) en deux colonnes TIMESTAMP dans Amazon Redshift :

CREATE TABLE IF NOT EXISTS testschema.period_table( id INTEGER
, period_col_begin TIMESTAMP
, period_col_end TIMESTAMP
)
DISTSTYLE KEY
DISTKEY
(id)
SORTKEY
(id);

Examinons maintenant un exemple simple de la façon dont vous pouvez utiliser AWS SCT pour convertir le code de votre application. Une opération courante dans Teradata consiste à extraire les horodatages de début (ou de fin) dans une valeur PERIOD à l'aide des fonctions intégrées BEGIN et END :

REPLACE VIEW testschema.period_view_begin_end AS SELECT BEGIN(period_col) AS period_start
, END(period_col) AS period_end FROM testschema.period_table
;

AWS SCT convertit la vue pour référencer les colonnes de la table transformée :

CREATE OR REPLACE VIEW testschema.period_view_begin_end (period_start, period_end) AS
SELECT period_col_begin AS period_start
, period_col_end AS period_end
FROM testschema.period_table;

Nous continuerons à automatiser la conversion des données PERIOD, alors restez à l'écoute pour d'autres améliorations. En attendant, vous pouvez dès maintenant essayer les fonctionnalités de conversion de type de données PERIOD dans AWS SCT. Pour plus d'informations, voir Installation, vérification et mise à jour d'AWS SCT.

Moulage de caractères

Certains moteurs d'entrepôt de données, comme Teradata, fournissent un ensemble complet de règles pour convertir les valeurs des données en expressions. Ces règles permettent conversions implicites, où le type de données cible est déduit de l'expression, et moulages explicites, qui utilisent généralement une fonction pour effectuer la conversion de type.

Auparavant, vous deviez convertir manuellement les opérations de conversion implicites dans votre code SQL. Nous sommes désormais heureux de partager qu'AWS SCT convertit automatiquement les conversions implicites selon les besoins. Cette fonctionnalité est désormais disponible pour l'ensemble suivant de types de données Teradata à fort impact.

Voyons comment convertir des nombres en DATE. De nombreuses applications Teradata traitent les nombres et DATE comme des valeurs équivalentes. En interne, Teradata stocke les valeurs DATE sous la forme INTEGER. Les règles de conversion entre un INTEGER et une DATE sont bien connues et les développeurs ont couramment exploité ces informations pour effectuer des calculs de date à l'aide de l'arithmétique INTEGER.

Par exemple, considérons la table Teradata suivante :

CREATE TABLE testschema.employees ( id INTEGER
, name VARCHAR(20) CHARACTER SET LATIN
, manager_id INTEGER
, last_promo_date DATE FORMAT 'YY/MM/DD')
UNIQUE PRIMARY INDEX ( id );

Nous insérons une seule ligne de données dans le tableau :

select * from employees; *** Query completed. One row found. 4 columns returned. *** Total elapsed time was 1 second. id name manager_id last_promo_date
----------- -------------------- ----------- --------------- 112 Britney 201 ?

Nous utilisons une macro pour mettre à jour le last_promo_date champ pour id = 112. La macro accepte un paramètre BIGINT pour remplir le champ DATE.

replace macro testschema.set_last_promo_date(emp_id integer, emp_promo_date bigint) AS (
update testschema.employees
set last_promo_date = :emp_promo_date
where id = :emp_id;
);

Maintenant, nous exécutons la macro et vérifions la valeur de last_promo_date attribut:

exec testschema.set_last_promo_date(112, 1410330); *** Update completed. One row changed. *** Total elapsed time was 1 second. select * from employees; *** Query completed. One row found. 4 columns returned. *** Total elapsed time was 1 second. id name manager_id last_promo_date
----------- -------------------- ----------- --------------- 112 Britney 201 41/03/30

Tu peux voir la last_promo_date L'attribut est défini sur la date du 30 mars 2041.

Utilisons maintenant AWS SCT pour convertir la table et la macro en Amazon Redshift. Comme nous l'avons vu dans Partie 1 de cette série, AWS SCT convertit la macro Teradata en une procédure stockée Amazon Redshift :

CREATE TABLE IF NOT EXISTS testschema.employees( id INTEGER
, name CHARACTER VARYING(20) , manager_id INTEGER
, last_promo_date DATE
)
DISTSTYLE KEY
DISTKEY
(id)
SORTKEY
(id); CREATE OR REPLACE PROCEDURE testschema.set_last_promo_date(par_emp_id INTEGER, par_emp_promo_date BIGINT)
AS $BODY$
BEGIN UPDATE testschema.employees SET last_promo_date = TO_DATE((par_emp_promo_date + 19000000), 'YYYYMMDD') WHERE id = par_emp_id;
END;
$BODY$
LANGUAGE plpgsql;

Notez que 20410330 = 1410330 + 19000000 ; donc l'ajout de 19,000,000 2041 03 à l'entrée renvoie la valeur de date correcte 30-XNUMX-XNUMX.

Désormais, lorsque nous exécutons la procédure stockée, elle met à jour le last_promo_date comme prévu:

myredshift=# select * from testschema.employees; id | name | manager_id | last_promo_date 112 | Britney | 201 |
(1 row) myredshift=# call testschema.set_last_promo_date(112, 1410330);
CALL myredshift=# select * from testschema.employees; id | name | manager_id | last_promo_date 112 | Britney | 201 | 2041-03-30
(1 row)

La conversion automatique du type de données est désormais disponible dans AWS SCT. Tu peux télécharger la dernière version et l'essayer.

Données BLOB

Amazon Redshift ne prend pas en charge nativement les colonnes BLOB, que vous utilisez pour stocker des objets binaires volumineux tels que du texte ou des images.

Auparavant, si vous migriez une table avec une colonne BLOB, vous deviez déplacer manuellement les valeurs BLOB vers le stockage de fichiers, comme Service de stockage simple Amazon (Amazon S3), puis ajoutez une référence au fichier S3 dans le tableau. L'utilisation d'Amazon S3 comme cible de stockage pour les objets binaires est une bonne pratique, car ces objets sont volumineux et ont généralement une faible valeur analytique.

Nous sommes heureux de vous annoncer qu'AWS SCT automatise désormais ce processus pour vous. AWS SCT remplace la colonne BLOB par une colonne CHARACTER VARYING sur la table cible. Ensuite, lorsque vous utilisez le Extracteurs de données AWS SCT pour migrer vos données, les extracteurs téléchargent la valeur BLOB sur Amazon S3 et insèrent une référence au BLOB dans la table cible.

Par exemple, créons une table dans Teradata et remplissons-la avec des données :

CREATE SET TABLE TESTSCHEMA.blob_table ( id INTEGER
, blob_col BLOB(10485760))
PRIMARY INDEX ( id ); select * from blob_table; *** Query completed. 2 rows found. 2 columns returned. *** Total elapsed time was 1 second. id blob_col
----------- --------------------------------------------------------------- 1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 2 BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB

Maintenant, nous convertissons la table avec AWS SCT et la construisons sur Amazon Redshift :

myredshift=# d testschema.blob_table; Table "testschema.blob_table"
Column | Type | Collation | Nullable | Default id | integer |. | | blob_col | character varying(1300) | | |

Ensuite, nous utilisons les extracteurs de données AWS SCT pour migrer les données des tables de Teradata vers Amazon Redshift.

Lorsque nous examinons le tableau dans Amazon Redshift, vous pouvez voir les chemins d'accès aux fichiers S3 qui contiennent les valeurs BLOB :

myredshift=# select * from testschema.blob_table;
(2 rows) id | blob_col 2 | s3://<bucket name>/data/c12f53330dd3427a845a77f143d4a1a1/dbdee8e0485c481dad601fd6170fbfb4_lobs/2/308b6f0a902941e793212058570cdda5.dat 1 | s3://<bucket name>/data/c12f53330dd3427a845a77f143d4a1a1/dbdee8e0485c481dad601fd6170fbfb4_lobs/2/a7686067af5549479b52d81e83c3871e.dat

Et sur Amazon S3, vous pouvez voir les fichiers de données réels. Il y en a deux, un pour chaque valeur BLOB :

$ aws s3 ls s3://<bucket name>/data/c12f53330dd3427a845a77f143d4a1a1/dbdee8e0485c481dad601fd6170fbfb4_lobs/2/
2021-05-13 23:59:47 23 522fee54fda5472fbae790f43e36cba1.dat
2021-05-13 23:59:47 24 5de6c53831f741629476e2c2cbc6b226.dat

La prise en charge des BLOB est désormais disponible dans AWS SCT et les extracteurs de données AWS SCT. Télécharger la dernière version de l'application et essayez-la dès aujourd'hui.

Conversion de CARACTÈRE multi-octets

Teradata prend en charge les caractères multioctets dans les colonnes de données CHARACTER, qui sont des champs de longueur fixe. Amazon Redshift prend en charge les caractères multioctets dans les champs CHARACTER VARYING, mais pas dans les colonnes CHARACTER de longueur fixe.

Auparavant, si vous aviez des colonnes CHARACTER de longueur fixe, vous deviez déterminer si elles contenaient des données de caractères multi-octets et augmenter la taille de la colonne cible le cas échéant.

AWS SCT comble désormais cette lacune pour vous. Si vos tables Teradata contiennent des colonnes CHARACTER avec des caractères multi-octets, AWS SCT convertit automatiquement ces colonnes en champs Amazon Redshift CHARACTER VARYING et définit la taille des colonnes en conséquence. Prenons l'exemple suivant, qui contient quatre colonnes, une colonne LATIN contenant uniquement des caractères à un octet et des colonnes UNICODE, GRAPHIC et KANJISJIS pouvant contenir des caractères multi-octets :

create table testschema.char_table ( latin_col char(70) character set latin
, unicode_col char(70) character set unicode
, graphic_col char(70) character set graphic
, kanjisjis_col char(70) character set kanjisjis
);

AWS SCT traduit la colonne LATIN en colonne CHARACTER de longueur fixe. Les colonnes multi-octets sont agrandies et converties en CHARACTER VARYING :

CREATE TABLE IF NOT EXISTS testschema.char_table ( latin_col CHARACTER(70)
, unicode_col CHARACTER VARYING(210)
, graphic_col CHARACTER VARYING(210)
, kanjisjis_col CHARACTER VARYING(210)
)
DISTSTYLE KEY
DISTKEY
(col1)
SORTKEY
(col1);

La conversion automatique des colonnes CHARACTER multi-octets est désormais disponible dans AWS SCT.

Taille du type de données GEOMETRY

Amazon Redshift prend depuis longtemps en charge les données géospatiales avec un GEOMETRIE type de données et fonctions spatiales associées.

Auparavant, Amazon Redshift limitait la taille maximale d'une colonne GEOMETRY à 64 Ko, ce qui limitait certains clients disposant d'objets volumineux. Nous sommes désormais heureux de vous annoncer que la taille maximale des objets GEOMETRY a été augmentée à un peu moins de 1 Mo (plus précisément, 1,048,447 XNUMX XNUMX octets).

Par exemple, considérons la table Teradata suivante :

create table geometry_table ( id INTEGER
, geometry_col1 ST_GEOMETRY , geometry_col2 ST_GEOMETRY(1000)
, geometry_col3 ST_GEOMETRY(1048447) , geometry_col4 ST_GEOMETRY(10484470)
, geometry_col5 ST_GEOMETRY INLINE LENGTH 1000
)
;

Vous pouvez utiliser AWS SCT pour le convertir en Amazon Redshift. La définition de la table convertie est la suivante. Une spécification de taille n'est pas nécessaire sur les colonnes converties car Amazon Redshift définit implicitement la taille de la colonne.

CREATE TABLE IF NOT EXISTS testschema.geometry_table(
id INTEGER,
geometry_col1 GEOMETRY,
geometry_col2 GEOMETRY,
geometry_col3 GEOMETRY,
geometry_col4 GEOMETRY,
geometry_col5 GEOMETRY
)
DISTSTYLE KEY
DISTKEY
(
id
)
SORTKEY
(
id
);
ALTER TABLE testschema.geometry_table ALTER DISTSTYLE AUTO;
ALTER TABLE testschema.geometry_table ALTER SORTKEY AUTO;

Les grandes colonnes GEOMETRY sont désormais disponibles dans Amazon Redshift. Pour plus d'informations, voir Interrogation de données spatiales dans Amazon Redshift.

Conclusion

Nous sommes heureux de partager ces nouvelles fonctionnalités avec vous. Si vous envisagez une migration vers Amazon Redshift, ces fonctionnalités peuvent vous aider à automatiser votre conversion de schéma et à préserver votre investissement dans les rapports, applications et ETL existants, ainsi qu'à accélérer les performances de vos requêtes.

Cet article décrit quelques-unes des dizaines de nouvelles fonctionnalités que nous avons récemment introduites pour automatiser les migrations de vos entrepôts de données vers Amazon Redshift. Nous en partagerons davantage dans les prochains articles. Vous entendrez parler d'une automatisation SQL supplémentaire, d'un langage de script spécialement conçu pour Amazon Redshift avec compatibilité BTEQ et d'une prise en charge automatisée des fonctionnalités SQL propriétaires.

Revenez bientôt pour plus d'informations. D'ici là, vous pouvez en savoir plus sur Redshift d'Amazon et par Outil de conversion de schéma AWS sur le site Web d'AWS. Bonne migration !

À propos de l’auteur

michael soo est ingénieur de bases de données au sein de l'équipe AWS DMS et AWS SCT chez Amazon Web Services.

Source : https://aws.amazon.com/blogs/big-data/part-2-accelerate-your-data-warehouse-migration-to-amazon-redshift/