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

Republié par Platon

Suiveurs: 0

Ceci est le troisiè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éserver votre investissement dans les scripts, rapports et applications existants ; accélérer les performances des requêtes ; et réduisez votre coût global de migration vers Redshift d'Amazon.

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érationnelles et votre lac de données à l'aide de SQL standard. Vous pouvez également intégrer d'autres services tels que 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 des moteurs d'entrepôt de données autogérés, comme Teradata, vers Amazon Redshift. Dans ces cas, vous pouvez avoir des téraoctets (ou pétaoctets) de données historiques, une forte dépendance à l'égard de fonctionnalités propriétaires et des milliers de processus et de rapports d'extraction, de transformation et de chargement (ETL) construits au fil des années (ou des décennies) d'utilisation.

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

Aujourd'hui, nous sommes heureux de partager les récentes améliorations apportées à Amazon Redshift et au Outil de conversion de schéma AWS (AWS SCT) qui facilitent l'automatisation de vos migrations Teradata vers Amazon Redshift.

Dans cet article, nous introduisons une nouvelle automatisation pour les instructions de fusion, une fonction native pour prendre en charge la conversion de caractères ASCII, une vérification améliorée des erreurs pour la conversion de chaîne en date, une prise en charge améliorée des curseurs Teradata et des colonnes d'identité, une automatisation pour les prédicats ANY et SOME, une automatisation pour RESET WHEN clauses, automatisation pour deux fonctions propriétaires Teradata (TD_NORMALIZE_OVERLAP et TD_UNPIVOT) et automatisation pour prendre en charge les fonctions analytiques (QUANTILE et QUALIFY).

Fusionner l'instruction

Comme son nom l'indique, l'instruction merge prend un ensemble d'entrées et le fusionne dans une table cible. Si une ligne d'entrée existe déjà dans la table cible (une ligne de la table cible a la même valeur de clé primaire), la ligne cible est mise à jour. S'il n'y a pas de ligne cible correspondante, la ligne d'entrée est insérée dans la table.

Jusqu'à présent, si vous utilisiez des instructions de fusion dans votre charge de travail, vous étiez obligé de réécrire manuellement l'instruction de fusion pour qu'elle s'exécute sur Amazon Redshift. Maintenant, nous sommes heureux de vous annoncer qu'AWS SCT automatise cette conversion pour vous. AWS SCT décompose une instruction de fusion en une mise à jour sur les enregistrements existants suivie d'une insertion pour les nouveaux enregistrements.

Prenons un exemple. Nous créons deux tables dans Teradata : une table cible, employee, et une table delta, employee_delta, où nous organisons les lignes d'entrée :

CREATE TABLE testschema.employee( id INTEGER
, name VARCHAR(20)
, manager INTEGER)
UNIQUE PRIMARY INDEX (id)
; CREATE TABLE testschema.employee_delta ( id INTEGER
, name VARCHAR(20)
, manager INTEGER)
UNIQUE PRIMARY INDEX(id)
;

Nous créons maintenant une instruction de fusion Teradata qui met à jour une ligne si elle existe dans la cible, sinon elle insère la nouvelle ligne. Nous intégrons cette instruction de fusion dans une macro afin de pouvoir vous montrer le processus de conversion ultérieurement.

REPLACE MACRO testschema.merge_employees AS ( MERGE INTO testschema.employee tgt USING testschema.employee_delta delta ON delta.id = tgt.id WHEN MATCHED THEN UPDATE SET name = delta.name, manager = delta.manager WHEN NOT MATCHED THEN INSERT (delta.id, delta.name, delta.manager);
);

Nous utilisons maintenant AWS SCT pour convertir la macro. (Voir Accélérez la migration de votre entrepôt de données vers Amazon Redshift – Partie 1 pour plus de détails sur la conversion de macro.) AWS SCT crée une procédure stockée qui contient une mise à jour (pour implémenter la condition WHEN MATCHED) et une insertion (pour implémenter la condition WHEN NOT MATCHED).

CREATE OR REPLACE PROCEDURE testschema.merge_employees()
AS $BODY$
BEGIN UPDATE testschema.employee SET name = "delta".name, manager = "delta".manager FROM testschema.employee_delta AS delta JOIN testschema.employee AS tgt ON "delta".id = tgt.id; INSERT INTO testschema.employee SELECT "delta".id , "delta".name , "delta".manager FROM testschema.employee_delta AS delta WHERE NOT EXISTS ( SELECT 1 FROM testschema.employee AS tgt WHERE "delta".id = tgt.id );
END;
$BODY$
LANGUAGE plpgsql;

Cet exemple montre comment utiliser l'automatisation de la fusion pour les macros, mais vous pouvez convertir des instructions de fusion dans n'importe quel contexte d'application : procédures stockées, scripts BTEQ, code Java, etc. Télécharger le dernière version d'AWS SCT et essayez-le.

Fonction ASCII()

La fonction ASCII prend en entrée une chaîne et renvoie le code ASCII, ou plus précisément le point de code UNICODE, du premier caractère de la chaîne. Auparavant, Amazon Redshift prenait en charge ASCII en tant que fonction de nœud principal uniquement, ce qui empêchait son utilisation avec des tables définies par l'utilisateur.

Nous sommes heureux d'annoncer que la fonction ASCII est désormais disponible sur les nœuds de calcul Amazon Redshift et peut être utilisée avec des tables définies par l'utilisateur. Dans le code suivant, nous créons une table avec des données de chaîne :

CREATE TABLE testschema.char_table ( id INTEGER
, char_col CHAR(10)
, varchar_col VARCHAR(10)
); INSERT INTO testschema.char_table VALUES (1, 'Hello', 'world');

Vous pouvez maintenant utiliser la fonction ASCII sur les colonnes de chaîne :

# SELECT id, char_col, ascii(char_col), varchar_col, ascii(varchar_col) FROM testschema.char_table; id | char_col | ascii | varchar_col | ascii 1 | Hello | 72 | world | 119

Enfin, si votre code d'application utilise la fonction ASCII, AWS SCT convertit automatiquement ces appels de fonction vers Amazon Redshift.

Les Fonction ASCII est disponible dès maintenant. Essayez-le dans votre propre cluster.

Fonction TO_DATE()

La fonction TO_DATE convertit une chaîne de caractères en une valeur DATE. Une particularité de cette fonction est qu'elle peut accepter une valeur de chaîne qui n'est pas une date valide et la traduire en une date valide.

Par exemple, considérons la chaîne 2021-06-31. Cette date n'est pas valide car le mois de juin ne compte que 30 jours. Cependant, la fonction TO_DATE accepte cette chaîne et renvoie le « 31 » jour de juin (1er juillet) :

# SELECT to_date('2021-06-31', 'YYYY-MM-DD'); to_date 2021-07-01
(1 row)

Les clients ont demandé une vérification stricte des entrées pour TO_DATE, et nous sommes heureux de partager cette nouvelle fonctionnalité. Maintenant, vous pouvez inclure une valeur booléenne dans l'appel de fonction qui active la vérification stricte :

# SELECT to_date('2021-06-31', 'YYYY-MM-DD', TRUE);
ERROR: date/time field date value out of range: 2021-6-31

Vous pouvez également désactiver explicitement la vérification stricte :

# SELECT to_date('2021-06-31', 'YYYY-MM-DD', FALSE); to_date 2021-07-01
(1 row)

De plus, la valeur booléenne est facultative. Si vous ne l'incluez pas, la vérification stricte est désactivée et vous constatez le même comportement qu'avant le lancement de la fonctionnalité.

Vous pouvez en apprendre davantage sur le À CE JOUR fonction et essayez dès maintenant la vérification stricte de la date dans Amazon Redshift.

Ensembles de résultats CURSOR

Un curseur est une construction de langage de programmation que les applications utilisent pour manipuler un jeu de résultats une ligne à la fois. Les curseurs sont plus pertinents pour les applications OLTP, mais certaines applications héritées basées sur des entrepôts de données les utilisent également.

Teradata fournit un ensemble varié de configurations de curseur. Amazon Redshift prend en charge un ensemble plus rationalisé de fonctionnalités de curseur.

Sur la base des commentaires des clients, nous avons ajouté l'automatisation pour prendre en charge les curseurs Teradata AVEC RETOUR. Ces types de curseurs sont ouverts dans les procédures stockées et renvoyés à l'appelant pour le traitement du jeu de résultats. AWS SCT convertira un curseur WITH RETURN en Amazon Redshift REFCURSEUR.

Par exemple, considérez la procédure suivante, qui contient un curseur WITH RETURN. La procédure ouvre le curseur et renvoie le résultat à l'appelant sous la forme d'un JEU DE RÉSULTATS DYNAMIQUE :

REPLACE PROCEDURE testschema.employee_cursor (IN p_mgrid INTEGER) DYNAMIC RESULT SETS 1
BEGIN DECLARE result_set CURSOR WITH RETURN ONLY FOR SELECT id, name, manager FROM testschema.employee WHERE manager = to_char(p_mgrid); OPEN result_set;
END;

AWS SCT convertit la procédure comme suit. Un paramètre supplémentaire est ajouté à la signature de la procédure pour passer le REFCURSOR :

CREATE OR REPLACE PROCEDURE testschema.employee_cursor(par_p_mgrid IN INTEGER, dynamic_return_cursor INOUT refcursor)
AS $BODY$
DECLARE
BEGIN OPEN dynamic_return_cursor FOR SELECT id, name, manager FROM testschema.employee WHERE manager = to_char(par_p_mgrid, '99999');
END;
$BODY$
LANGUAGE plpgsql;

Colonnes IDENTITY

Teradata prend en charge plusieurs fonctionnalités non conformes à la norme ANSI pour les colonnes IDENTITY. Nous avons amélioré AWS SCT pour convertir automatiquement ces fonctionnalités vers Amazon Redshift, dans la mesure du possible.

Plus précisément, AWS SCT convertit désormais les clauses Teradata START WITH et INCREMENT BY en Clauses SEED et STEP d'Amazon Redshift, respectivement. Par exemple, considérez la table Teradata suivante :

CREATE TABLE testschema.identity_table ( a2 BIGINT GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 20 )
);

La clause GENERATED ALWAYS indique que la colonne est toujours remplie automatiquement : une valeur ne peut pas être explicitement insérée ou mise à jour dans la colonne. La clause START WITH définit la première valeur à insérer dans la colonne et la clause INCREMENT BY définit la valeur suivante à insérer dans la colonne.

Lorsque vous convertissez cette table à l'aide d'AWS SCT, le DDL Amazon Redshift suivant est produit. Notez que les valeurs START WITH et INCREMENT BY sont conservées dans la syntaxe cible :

CREATE TABLE IF NOT EXISTS testschema.identity_table ( a2 BIGINT IDENTITY(1, 20)
)
DISTSTYLE KEY
DISTKEY
(a2)
SORTKEY
(a2);

De plus, par défaut, une colonne IDENTITY dans Amazon Redshift ne contient que des valeurs générées automatiquement, de sorte que la propriété GENERATED ALWAYS dans Teradata est préservée :

# INSERT INTO testschema.identity_table VALUES (100);
ERROR: cannot set an identity column to a value

Les colonnes IDENTITY dans Teradata peuvent également être spécifiées comme GENERATED BY DEFAULT. Dans ce cas, une valeur peut être explicitement définie dans une instruction INSERT. Si aucune valeur n'est spécifiée, la colonne est remplie avec une valeur générée automatiquement comme d'habitude. Auparavant, AWS SCT ne prenait pas en charge la conversion des colonnes GENERATED BY DEFAULT. Maintenant, nous sommes heureux de partager qu'AWS SCT convertit automatiquement ces colonnes pour vous.

Par exemple, le tableau suivant contient une colonne IDENTITY qui est GENERATED BY DEFAULT :

CREATE TABLE testschema.identity_by_default ( a1 BIGINT GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 INCREMENT BY 20 )
PRIMARY INDEX (a1);

La colonne IDENTITY est convertie par AWS SCT comme suit. La colonne convertie utilise la clause Amazon Redshift GENERATED BY DEFAULT :

CREATE TABLE testschema.identity_by_default ( a1 BIGINT GENERATED BY DEFAULT AS IDENTITY(1,20) DISTKEY
) DISTSTYLE KEY SORTKEY (a1);

Il existe un problème de syntaxe supplémentaire qui requiert votre attention. Dans Teradata, une valeur générée automatiquement est insérée lorsque NULL est spécifié pour la valeur de la colonne :

INSERT INTO identity_by_default VALUES (null);

Amazon Redshift utilise une syntaxe différente dans le même but. Ici, vous incluez le mot clé DEFAULT dans la liste des valeurs pour indiquer que la colonne doit être générée automatiquement :

INSERT INTO testschema.identity_by_default VALUES (default);

Nous sommes heureux de partager qu'AWS SCT convertit automatiquement la syntaxe Teradata pour les instructions INSERT comme dans l'exemple précédent. Par exemple, considérez la macro Teradata suivante :

REPLACE MACRO testschema.insert_identity_by_default AS ( INSERT INTO testschema.identity_by_default VALUES (NULL);
);

AWS SCT supprime le NULL et le remplace par DEFAULT :

CREATE OR REPLACE PROCEDURE testschema.insert_identity_by_default() LANGUAGE plpgsql
AS $$ BEGIN INSERT INTO testschema.identity_by_default VALUES (DEFAULT);
END; $$

L'automatisation de la colonne IDENTITY est désormais disponible dans AWS SCT. Vous pouvez télécharger le dernière version et l'essayer.

Filtres ANY et SOME avec prédicats d'inégalité

Les filtres ANY et SOME déterminent si un prédicat s'applique à une ou plusieurs valeurs d'une liste. Par exemple, dans Teradata, vous pouvez utiliser <> ANY pour rechercher tous les employés qui ne travaillent pas pour un certain responsable :

REPLACE MACRO testschema.not_in_103 AS ( SELECT * FROM testschema.employee WHERE manager <> ANY (103)
;
);

Bien sûr, vous pouvez réécrire cette requête à l'aide d'un simple filtre non égal, mais vous voyez souvent des requêtes provenant de générateurs SQL tiers qui suivent ce modèle.

Amazon Redshift ne prend pas en charge cette syntaxe de manière native. Auparavant, toutes les requêtes utilisant cette syntaxe devaient être converties manuellement. Maintenant, nous sommes heureux de partager qu'AWS SCT convertit automatiquement les clauses ANY et SOME avec des prédicats d'inégalité. La macro ci-dessus est convertie en une procédure stockée comme suit.

CREATE OR REPLACE PROCEDURE testschema.not_in_103(macro_out INOUT refcursor)
AS $BODY$
BEGIN OPEN macro_out FOR SELECT * FROM testschema.employee WHERE ((manager <> 103));
END;
$BODY$
LANGUAGE plpgsql;

Si la liste de valeurs suivant ANY contient deux valeurs supplémentaires, AWS SCT les convertira en une série de conditions OR, une pour chaque élément de la liste.

La conversion de filtre ANY/SOME est désormais disponible dans AWS SCT. Vous pouvez l'essayer dans le dernière version de la demande.

Fonctions analytiques avec RESET WHEN

RESET WHEN est une fonctionnalité Teradata utilisée dans les fonctions de fenêtre analytique SQL. C'est une extension de la norme ANSI SQL. RESET WHEN détermine la partition sur laquelle une fonction de fenêtre SQL opère en fonction d'une condition spécifiée. Si la condition est vraie, une nouvelle sous-partition dynamique est créée à l'intérieur de la partition de fenêtre existante.

Par exemple, la vue suivante utilise RESET WHEN pour calculer un total cumulé par magasin. Le total cumulé s'accumule tant que les ventes augmentent d'un mois à l'autre. Si les ventes chutent d'un mois à l'autre, le total cumulé est réinitialisé.

CREATE TABLE testschema.sales ( store_id INTEGER
, month_no INTEGER
, sales_amount DECIMAL(9,2)
)
; REPLACE VIEW testschema.running_total ( store_id
, month_no
, sales_amount
, cume_sales_amount
)
AS
SELECT store_id
, month_no
, sales_amount
, SUM(sales_amount) OVER ( PARTITION BY store_id ORDER BY month_no RESET WHEN sales_amount < SUM(sales_amount) OVER ( PARTITION BY store_id ORDER BY month_no ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) ROWS UNBOUNDED PRECEDING )
FROM testschema.sales;

Pour démontrer, nous insérons quelques données de test dans le tableau :

INSERT INTO testschema.sales VALUES (1001, 1, 35000.00);
INSERT INTO testschema.sales VALUES (1001, 2, 40000.00);
INSERT INTO testschema.sales VALUES (1001, 3, 45000.00);
INSERT INTO testschema.sales VALUES (1001, 4, 25000.00);
INSERT INTO testschema.sales VALUES (1001, 5, 30000.00);
INSERT INTO testschema.sales VALUES (1001, 6, 30000.00);
INSERT INTO testschema.sales VALUES (1001, 7, 50000.00);
INSERT INTO testschema.sales VALUES (1001, 8, 35000.00);
INSERT INTO testschema.sales VALUES (1001, 9, 60000.00);
INSERT INTO testschema.sales VALUES (1001, 10, 80000.00);
INSERT INTO testschema.sales VALUES (1001, 11, 90000.00);
INSERT INTO testschema.sales VALUES (1001, 12, 100000.00);

Les montants des ventes chutent après les mois 3 et 7. Le total cumulé est réinitialisé en conséquence aux mois 4 et 8.

SELECT * FROM testschema.running_total; store_id month_no sales_amount cume_sales_amount
----------- ----------- ------------ ----------------- 1001 1 35000.00 35000.00 1001 2 40000.00 75000.00 1001 3 45000.00 120000.00 1001 4 25000.00 25000.00 1001 5 30000.00 55000.00 1001 6 30000.00 85000.00 1001 7 50000.00 135000.00 1001 8 35000.00 35000.00 1001 9 60000.00 95000.00 1001 10 80000.00 175000.00 1001 11 90000.00 265000.00 1001 12 100000.00 365000.00

AWS SCT convertit la vue comme suit. Le code converti utilise une sous-requête pour émuler le RESET WHEN. Essentiellement, un attribut de marqueur est ajouté au résultat qui signale une baisse des ventes d'un mois sur l'autre. L'indicateur est ensuite utilisé pour déterminer la plus longue série précédente d'augmentation des ventes à agréger.

CREATE OR REPLACE VIEW testschema.running_total ( store_id
, month_no
, sales_amount
, cume_sales_amount) AS
SELECT store_id
, month_no
, sales_amount
, sum(sales_amount) OVER (PARTITION BY k1, store_id ORDER BY month_no NULLS FIRST ROWS UNBOUNDED PRECEDING)
FROM ( SELECT store_id , month_no , sales_amount , SUM(CASE WHEN k = 1 THEN 0 ELSE 1 END) OVER (PARTITION BY store_id ORDER BY month_no NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS k1 FROM ( SELECT store_id , month_no , sales_amount , CASE WHEN sales_amount < SUM(sales_amount) OVER (PARTITION BY store_id ORDER BY month_no ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) OR sales_amount IS NULL THEN 0 ELSE 1 END AS k FROM testschema.sales )
);

Nous nous attendons à ce que la conversion RESET WHEN soit un grand succès auprès des clients. Vous pouvez l'essayer maintenant dans AWS SCT.

Fonction TD_NORMALIZE_OVERLAP()

La fonction TD_NORMALIZE_OVERLAP combine les lignes dont les valeurs PERIOD se chevauchent. La ligne normalisée résultante contient la limite de départ la plus ancienne et la limite de fin la plus récente à partir des valeurs PERIOD de toutes les lignes concernées.

Par exemple, nous créons une table Teradata qui enregistre les salaires des employés avec le code suivant. Chaque ligne du tableau est horodatée avec la période pendant laquelle l'employé a reçu le salaire donné.

CREATE TABLE testschema.salaries ( emp_id INTEGER
, salary DECIMAL(8,2)
, from_to PERIOD(DATE)
);

Maintenant, nous ajoutons des données pour deux employés. Pour emp_id = 1 ainsi que salary = 2000, il y a deux lignes qui se chevauchent. De même, les deux lignes avec emp_id = 2 ainsi que salary = 3000 se superposent.

SELECT * FROM testschema.salaries ORDER BY emp_id, from_to; emp_id salary from_to
----------- ---------- ------------------------ 1 1000.00 ('20/01/01', '20/05/31') 1 2000.00 ('20/06/01', '21/02/28') 1 2000.00 ('21/01/01', '21/06/30') 2 3000.00 ('20/01/01', '20/03/31') 2 3000.00 ('20/02/01', '20/04/30')

Nous créons maintenant une vue qui utilise la fonction TD_NORMALIZE_OVERLAP pour normaliser les données qui se chevauchent :

REPLACE VIEW testschema.normalize_salaries AS WITH sub_table(emp_id, salary, from_to) AS ( SELECT emp_id , salary , from_to FROM testschema.salaries
)
SELECT *
FROM TABLE(TD_SYSFNLIB.TD_NORMALIZE_OVERLAP (NEW VARIANT_TYPE(sub_table.emp_id, sub_table.salary), sub_table.from_to) RETURNS (emp_id INTEGER, salary DECIMAL(8,2), from_to PERIOD(DATE)) HASH BY emp_id LOCAL ORDER BY emp_id, salary, from_to ) AS DT(emp_id, salary, duration)
;

Nous pouvons vérifier que les données de la vue sont réellement normalisées :

select * from testschema.normalize_salaries order by emp_id, duration; emp_id salary duration
----------- ---------- ------------------------ 1 1000.00 ('20/01/01', '20/05/31') 1 2000.00 ('20/06/01', '21/06/30') 2 3000.00 ('20/01/01', '20/04/30')

Vous pouvez désormais utiliser AWS SCT pour convertir toutes les instructions TD_NORMALIZE_OVERLAP. Nous convertissons d'abord le tableau des salaires en Amazon Redshift (voir Accélérez la migration de votre entrepôt de données vers Amazon Redshift – Partie 2 pour plus de détails sur l'automatisation du type de données de période) :

CREATE TABLE testschema.salaries ( emp_id integer distkey
, salary numeric(8,2) ENCODE az64
, from_to_begin date ENCODE az64
, from_to_end date ENCODE az64 ) DISTSTYLE KEY SORTKEY (emp_id); # SELECT * FROM testschema.salaries ORDER BY emp_id, from_to_begin; emp_id | salary | from_to_begin | from_to_end 1 | 1000.00 | 2020-01-01 | 2020-05-31 1 | 2000.00 | 2020-06-01 | 2021-02-28 1 | 2000.00 | 2021-01-01 | 2021-06-30 2 | 3000.00 | 2020-01-01 | 2020-03-31 2 | 3000.00 | 2020-02-01 | 2020-04-30

Nous utilisons maintenant AWS SCT pour convertir la vue normalize_salaries. AWS SCT ajoute une colonne qui marque le début d'un nouveau groupe de lignes. Il produit ensuite une seule ligne pour chaque groupe avec un horodatage normalisé.

CREATE VIEW testschema.normalize_salaries (emp_id, salary, from_to_begin, from_to_end) AS
WITH sub_table AS ( SELECT emp_id , salary , from_to_begin AS start_date , from_to_end AS end_date , CASE WHEN start_date <= lag(end_date) OVER (PARTITION BY emp_id, salary ORDER BY start_date, end_date) THEN 0 ELSE 1 END AS GroupStartFlag FROM testschema.salaries )
SELECT t2.emp_id
, t2.salary
, min(t2.start_date) AS from_to_begin
, max(t2.end_date) AS from_to_end
FROM ( SELECT emp_id , salary , start_date , end_date , sum(GroupStartFlag) OVER (PARTITION BY emp_id, salary ORDER BY start_date ROWS UNBOUNDED PRECEDING) AS GroupID FROM sub_table
) AS t2
GROUP BY t2.emp_id
, t2.salary
, t2.GroupID;

Nous pouvons vérifier que la vue convertie renvoie les données correctement normalisées :

# SELECT * FROM testschema.normalize_salaries ORDER BY emp_id; emp_id | salary | from_to_begin | from_to_end 1 | 1000.00 | 2020-01-01 | 2020-05-31 1 | 2000.00 | 2020-06-01 | 2021-06-30 2 | 3000.00 | 2020-01-01 | 2020-04-30

Vous pouvez essayer la conversion TD_NORMALIZE_OVERLAP dans la dernière version d'AWS SCT. Télécharger maintenant.

Fonction TD_UNPIVOT()

La fonction TD_UNPIVOT transforme les colonnes en lignes. Essentiellement, nous l'utilisons pour prendre une ligne de métriques similaires sur différentes périodes et créer une ligne distincte pour chaque métrique.

Par exemple, considérez la table Teradata suivante. Le tableau enregistre les visites des clients par année et par mois pour les petits magasins kiosque :

CREATE TABLE TESTSCHEMA.kiosk_monthly_visits ( kiosk_id INTEGER
, year_no INTEGER
, jan_visits INTEGER
, feb_visits INTEGER
, mar_visits INTEGER
, apr_visits INTEGER
, may_visits INTEGER
, jun_visits INTEGER
, jul_visits INTEGER
, aug_visits INTEGER
, sep_visits INTEGER
, oct_visits INTEGER
, nov_visits INTEGER
, dec_visits INTEGER)
PRIMARY INDEX (kiosk_id);

Nous insérons quelques exemples de données dans le tableau :

INSERT INTO testschema.kiosk_monthly_visits VALUES (100, 2020, 1100, 1200, 1300, 1400, 1500, 1600, 1700, 1800, 1900, 2000, 2100, 2200);

Ensuite, nous créons une vue qui dé-pivote le tableau afin que les visites mensuelles apparaissent sur des lignes séparées. La ligne unique dans le tableau croisé crée 12 lignes dans le tableau non croisé, une ligne par mois.

REPLACE VIEW testschema.unpivot_kiosk_monthly_visits ( kiosk_id
, year_no
, month_name
, month_visits
)
AS
SELECT kiosk_id
, year_no
, month_name (FORMAT 'X(10)')
, month_visits
FROM TD_UNPIVOT ( ON (SELECT * FROM testschema.kiosk_monthly_visits) USING VALUE_COLUMNS ('month_visits') UNPIVOT_COLUMN('month_name') COLUMN_LIST( 'jan_visits' , 'feb_visits' , 'mar_visits' , 'apr_visits' , 'may_visits' , 'jun_visits' , 'jul_visits' , 'aug_visits' , 'sep_visits' , 'oct_visits' , 'nov_visits' , 'dec_visits' ) COLUMN_ALIAS_LIST ( 'jan' , 'feb' , 'mar' , 'apr' , 'may' , 'jun' , 'jul' , 'aug' , 'sep' , 'oct' , 'nov' , 'dec' )
) a;

Lorsque vous sélectionnez dans la vue, les ventes mensuelles ne sont pas pivotées en 12 lignes distinctes :

SELECT * FROM testschema.unpivot_monthly_sales; id yr mon mon_sales
----------- ----------- ---------- ----------
100 2021 jan 1100.00
100 2021 feb 1200.00
100 2021 mar 1300.00
100 2021 apr 1400.00
100 2021 may 1500.00
100 2021 jun 1600.00
100 2021 jul 1700.00
100 2021 aug 1800.00
100 2021 sep 1900.00
100 2021 oct 2000.00
100 2021 nov 2100.00
100 2021 dec 2200.00

Nous utilisons maintenant AWS SCT pour convertir la vue en ANSI SQL qui peut être exécuté sur Amazon Redshift. La conversion crée une expression de table commune (CTE) à placer chaque mois dans une ligne distincte. Il joint ensuite le CTE et les attributs restants du tableau croisé dynamique d'origine.

REPLACE VIEW testschema.unpivot_kiosk_monthly_visits (kiosk_id, year_no, month_name, month_visits) AS
WITH cols
AS (SELECT 'jan' AS col
UNION ALL
SELECT 'feb' AS col
UNION ALL
SELECT 'mar' AS col
UNION ALL
SELECT 'apr' AS col
UNION ALL
SELECT 'may' AS col
UNION ALL
SELECT 'jun' AS col
UNION ALL
SELECT 'jul' AS col
UNION ALL
SELECT 'aug' AS col
UNION ALL
SELECT 'sep' AS col
UNION ALL
SELECT 'oct' AS col
UNION ALL
SELECT 'nov' AS col
UNION ALL
SELECT 'dec' AS col)
SELECT t1.kiosk_id, t1.year_no, col AS "month_name", CASE col WHEN 'jan' THEN "jan_visits" WHEN 'feb' THEN "feb_visits" WHEN 'mar' THEN "mar_visits" WHEN 'apr' THEN "apr_visits" WHEN 'may' THEN "may_visits" WHEN 'jun' THEN "jun_visits" WHEN 'jul' THEN "jul_visits" WHEN 'aug' THEN "aug_visits" WHEN 'sep' THEN "sep_visits" WHEN 'oct' THEN "oct_visits" WHEN 'nov' THEN "nov_visits" WHEN 'dec' THEN "dec_visits" ELSE NULL END AS "month_visits" FROM testschema.kiosk_monthly_visits AS t1 CROSS JOIN cols WHERE month_visits IS NOT NULL;

Vous pouvez vérifier que la vue convertie produit le même résultat que la version Teradata :

# SELECT * FROM testschema.unpivot_kiosk_monthly_visits; kiosk_id | year_no | month_name | month_visits 100 | 2020 | oct | 2000 100 | 2020 | nov | 2100 100 | 2020 | jul | 1700 100 | 2020 | feb | 1200 100 | 2020 | apr | 1400 100 | 2020 | aug | 1800 100 | 2020 | sep | 1900 100 | 2020 | jan | 1100 100 | 2020 | mar | 1300 100 | 2020 | may | 1500 100 | 2020 | jun | 1600 100 | 2020 | dec | 2200

Vous pouvez tester la prise en charge de la conversion pour TD_UNPIVOT dans le dernière version d'AWS SCT.

Fonction QUANTILE

QUANTILE est une fonction de classement. Il partitionne l'ensemble d'entrées en un nombre spécifié de groupes, chacun contenant une part égale de la population totale. QUANTILE est une extension Teradata propriétaire de la fonction NTILE trouvée dans ANSI SQL.

Par exemple, nous pouvons calculer les quartiles des données de visites mensuelles à l'aide de la vue Teradata suivante :

REPLACE VIEW testschema.monthly_visit_rank AS
SELECT kiosk_id
, year_no
, month_name
, month_visits
, QUANTILE(4, month_visits) qtile
FROM testschema.unpivot_kiosk_monthly_visits
;

Lorsque vous sélectionnez dans la vue, la fonction QUANTILE calcule le quartile et l'applique en tant qu'attribut sur la sortie :

SELECT * FROM monthly_visit_rank; kiosk_id year_no month_name month_visits qtile
----------- ----------- ---------- ------------ ----------- 100 2020 jan 1100 0 100 2020 feb 1200 0 100 2020 mar 1300 0 100 2020 apr 1400 1 100 2020 may 1500 1 100 2020 jun 1600 1 100 2020 jul 1700 2 100 2020 aug 1800 2 100 2020 sep 1900 2 100 2020 oct 2000 3 100 2020 nov 2100 3 100 2020 dec 2200 3

Amazon Redshift prend en charge une fonction NTILE généralisée, qui peut implémenter QUANTILE et est conforme à la norme ANSI. Nous avons amélioré AWS SCT pour convertir automatiquement les appels de fonction QUANTILE en appels de fonction NTILE équivalents.

Par exemple, lorsque vous convertissez la vue Teradata précédente, AWS SCT produit le code Amazon Redshift suivant :

SELECT unpivot_kiosk_monthly_visits.kiosk_id
, unpivot_kiosk_monthly_visits.year_no
, unpivot_kiosk_monthly_visits.month_name
, unpivot_kiosk_monthly_visits.month_visits
, ntile(4) OVER (ORDER BY unpivot_kiosk_monthly_visits.month_visits ASC NULLS FIRST) - 1) AS qtile FROM testschema.unpivot_kiosk_monthly_visits
;

La prise en charge de la conversion QUANTILE est désormais disponible dans AWS SCT.

QUALIFIER le filtre

La clause QUALIFY dans Teradata filtre les lignes produites par une fonction analytique. Prenons un exemple. Nous utilisons le tableau suivant, qui contient les revenus du magasin par mois. Notre objectif est de trouver les cinq premiers mois par chiffre d'affaires :

CREATE TABLE testschema.sales ( store_id INTEGER
, month_no INTEGER
, sales_amount DECIMAL(9,2))
PRIMARY INDEX (store_id); SELECT * FROM sales; store_id month_no sales_amount
----------- ----------- ------------ 1001 1 35000.00 1001 2 40000.00 1001 3 45000.00 1001 4 25000.00 1001 5 30000.00 1001 6 30000.00 1001 7 50000.00 1001 8 35000.00 1001 9 60000.00 1001 10 80000.00 1001 11 90000.00 1001 12 100000.00

Les données montrent que juillet, septembre, octobre, novembre et décembre ont été les cinq meilleurs mois de ventes.

Nous créons une vue qui utilise la fonction RANK pour classer chaque mois par ventes, puis utilisons la fonction QUALIFY pour sélectionner les cinq premiers mois :

REPLACE VIEW testschema.top_five_months( store_id
, month_no
, sales_amount
, month_rank
) as
SELECT store_id
, month_no
, sales_amount
, RANK() OVER (PARTITION BY store_id ORDER BY sales_amount DESC) month_rank
FROM testschema.sales
QUALIFY RANK() OVER (PARTITION by store_id ORDER BY sales_amount DESC) <= 5
;

Avant, si vous utilisiez la clause QUALIFY, vous deviez recoder manuellement vos instructions SQL. Désormais, AWS SCT convertit automatiquement QUALIFY en SQL compatible Amazon Redshift et conforme ANSI. Par exemple, AWS SCT réécrit la vue précédente comme suit :

CREATE OR REPLACE VIEW testschema.top_five_months ( store_id
, month_no
, sales_amount
, month_rank) AS
SELECT qualify_subquery.store_id
, qualify_subquery.month_no
, qualify_subquery.sales_amount
, month_rank
FROM ( SELECT store_id , month_no , sales_amount , rank() OVER (PARTITION BY store_id ORDER BY sales_amount DESC NULLS FIRST) AS month_rank , rank() OVER (PARTITION BY store_id ORDER BY sales_amount DESC NULLS FIRST) AS qualify_expression_1 FROM testschema.sales) AS qualify_subquery WHERE qualify_expression_1 <= 5;

AWS SCT convertit la requête d'origine en une sous-requête et applique l'expression QUALIFY en tant que filtre sur la sous-requête. AWS SCT ajoute une colonne supplémentaire à la sous-requête à des fins de filtrage. Ce n'est pas strictement nécessaire, mais simplifie le code lorsque les alias de colonne ne sont pas utilisés.

Vous pouvez essayer la conversion QUALIFY dans le dernière version d'AWS SCT.

Résumé

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 la conversion de votre schéma et à préserver votre investissement dans les rapports et applications existants. Si vous souhaitez vous lancer dans une migration d'entrepôt de données, vous pouvez en savoir plus sur Amazon Redshift et AWS SCT dans notre documentation publique.

Cet article décrit quelques-unes des dizaines de nouvelles fonctionnalités que nous introduisons pour automatiser vos migrations Teradata vers Amazon Redshift. Nous partagerons plus d'informations dans les prochains articles sur l'automatisation des fonctionnalités propriétaires de Teradata et d'autres nouvelles fonctionnalités intéressantes.

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. Bonne migration !

À propos des auteurs

michael soo est un ingénieur de base de données senior au sein de l'équipe AWS Database Migration Service. Il crée des produits et des services qui aident les clients à migrer leurs charges de travail de base de données vers le cloud AWS.

Raza Hafeez est un architecte de données au sein de la pratique spécialisée mondiale Lake House des services professionnels AWS. Il a plus de 10 ans d'expérience professionnelle dans la construction et l'optimisation d'entrepôts de données d'entreprise et se passionne pour permettre aux clients de réaliser la puissance de leurs données. Il est spécialisé dans la migration des entrepôts de données d'entreprise vers AWS Lake House Architecture.

Po Hong, PhD, est un architecte de données principal de Lake House Global Specialty Practice, AWS Professional Services. Il est passionné par le fait d'aider les clients à adopter des solutions innovantes pour réduire le temps d'analyse. Po est spécialisé dans la migration d'entrepôts de données MPP sur site à grande échelle vers l'architecture AWS Lake House.

Entong Shen est responsable du développement logiciel d'Amazon Redshift. Il travaille sur les bases de données MPP depuis plus de 9 ans et s'est concentré sur l'optimisation des requêtes, les statistiques et les fonctionnalités du langage SQL liées à la migration, telles que les procédures stockées et les types de données.

Sumit Singh est ingénieur de base de données au sein de l'équipe Database Migration Service d'Amazon Web Services. Il travaille en étroite collaboration avec les clients et fournit une assistance technique pour migrer leur charge de travail sur site vers le cloud AWS. Il contribue également à l'amélioration continue de la qualité et des fonctionnalités des produits de migration de données AWS.

Nelly Susanto est un spécialiste principal de la migration de bases de données d'AWS Database Migration Accelerator. Elle possède plus de 10 ans d'expérience technique axée sur la migration et la réplication de bases de données ainsi que sur les charges de travail d'entrepôt de données. Elle se passionne pour aider les clients dans leur parcours vers le cloud.