Migrer de Teradata vers BigQuery : Présentation de la traduction de requête

Dernière mise à jour : septembre 2020
Notes de version

Ce document fait partie d'une série traitant de la migration de schéma et de données de Teradata vers BigQuery. Il offre un aperçu de la traduction des instructions SQL écrites de façon à assurer la compatibilité de Teradata avec BigQuery.

La série consacrée aux spécificités de la transition depuis Teradata comprend les articles suivants :

Pour une présentation de la transition d'un entrepôt de données sur site vers BigQuery sur Google Cloud, consultez la série commençant par l'article Migrer des entrepôts de données vers BigQuery : Introduction et présentation.

Présentation

BigQuery et Teradata Database sont tous deux conformes à la norme ANSI/ISO SQL:2011. De plus, Teradata a créé des extensions du standard SQL permettant l'utilisation de fonctionnalités spécifiques à Teradata.

En revanche, BigQuery n'est pas compatible avec ces extensions propriétaires. Par conséquent, certaines requêtes peuvent nécessiter une refactorisation lors de la migration de Teradata vers BigQuery. Les requêtes n'utilisant que le standard SQL ANSI/ISO compatible avec BigQuery présentent l'avantage supplémentaire de la portabilité et de la non-dépendance à l'entrepôt de données sous-jacent.

Ce document aborde certains des défis que vous pourriez rencontrer lors de la migration de requêtes SQL de Teradata vers BigQuery. Il décrit dans quelles circonstances les traductions doivent être appliquées dans le contexte d'une migration par étapes de bout en bout.

Différences SQL Teradata

Cette section présente un aperçu des principales différences entre les langages SQL de Teradata et SQL standard de BigQuery, ainsi que certaines stratégies de traduction entre les deux dialectes. La liste des différences présentée ici n'est pas exhaustive. Pour plus d'informations, consultez la documentation de référence sur la traduction du langage SQL de Teradata vers BigQuery.

Langage de définition de données

Le langage de définition de données (DDL) permet de définir le schéma de votre base de données. Il comprend un sous-ensemble d'instructions SQL telles que CREATE, ALTER et DROP.

Ces instructions sont pour la plupart équivalentes dans SQL Teradata et SQL standard. Voici une liste non exhaustive d'exceptions notables :

  • Les options de manipulation des index qui ne sont pas compatibles avec BigQuery, telles que CREATE INDEX et PRIMARY INDEX. BigQuery n'utilise pas d'index lors de l'interrogation des données. Les résultats sont produits rapidement grâce à un modèle sous-jacent utilisant Dremel, à des techniques de stockage faisant appel à Capacitor et à une architecture massivement parallèle.
  • Les contraintes, qui sont des vérifications appliquées à des colonnes individuelles ou à une table entière. BigQuery n'accepte que les contraintes NOT NULL.
  • L'instruction MULTISET, qui permet d'autoriser les lignes en double dans Teradata.
  • L'instruction CASESPECIFIC, qui spécifie la sensibilité à la casse pour les comparaisons des données de caractères et les classements.

Types de données

BigQuery accepte un ensemble de types de données plus concis que Teradata, avec des groupes de types Teradata mappés sous la forme d'un seul type de données SQL standard. Exemples :

  • INTEGER, SMALLINT, BYTEINT et BIGINT correspondent à INT64.
  • CLOB, JSON, XML, UDT et d'autres types contenant des champs de caractères importants correspondent à STRING.
  • Les types BLOB, BYTE et VARBYTE contenant des informations binaires correspondent à BYTES.

Pour les dates, les types principaux (DATE, TIME et TIMESTAMP) sont équivalents dans Teradata et BigQuery. Cependant, d'autres types de dates spécialisés de Teradata doivent être mis en correspondance, tels que ceux-ci :

  • TIME_WITH_TIME_ZONE à TIME.
  • TIMESTAMP_WITH_TIME_ZONE à TIMESTAMP.
  • INTERVAL_HOUR, INTERVAL_MINUTE et d'autres types INTERVAL_* doivent correspondre à INT64 dans BigQuery.
  • Les types PERIOD(DATE) et PERIOD(TIME), ainsi que les autres périodes (PERIOD(*)) doivent correspondre à STRING.

Les tableaux multidimensionnels ne sont pas directement compatibles avec BigQuery. À la place, vous créez un tableau de structures, chaque structure contenant un champ de type ARRAY.

Instruction SELECT

La syntaxe de l'instruction SELECT est généralement compatible entre Teradata et BigQuery. Cette section décrit les différences qui doivent souvent être corrigées lors de la migration.

Identifiants

BigQuery vous permet d'utiliser les éléments suivants comme identifiants : projets, ensembles de données, tables, vues ou colonnes.

En tant que produit sans serveur, BigQuery n'obéit pas aux concepts de cluster, d'environnement ou de point de terminaison fixe. Par conséquent, le projet spécifie la hiérarchie des ressources de l'ensemble de données.

Dans une instruction SELECT de Teradata, il est possible d'utiliser des noms de colonne complets. BigQuery référence toujours les noms de colonne des tables ou des alias, mais jamais des projets ou des ensembles de données.

Voici quelques exemples d'options pour traiter les identifiants dans BigQuery.

Colonnes implicites de la table :

SELECT
 c
FROM
 project.dataset.table

Ou au moyen d'une référence de table explicite :

SELECT
 table.c
FROM
 project.dataset.table

Ou au moyen d'un alias de table explicite :

SELECT
 t.c
FROM
 project.dataset.table t
Références d'alias

Dans une instruction SELECT de Teradata, les alias peuvent être définis et référencés dans la même requête. Par exemple, dans l'extrait suivant, l'option flag est définie comme un alias de colonne, puis référencée immédiatement dans l'instruction CASE jointe.

SELECT
 F AS flag,
 CASE WHEN flag = 1 THEN ...

En langage SQL standard, les références entre colonnes au sein d'une même requête ne sont pas autorisées. Pour les traduire, vous transférez la logique dans une requête imbriquée :

SELECT
 q.*,
 CASE WHEN q.flag = 1 THEN ...
FROM (
 SELECT
   F AS flag,
   ...
) AS q

L'exemple d'espace réservé F peut lui-même être une requête imbriquée qui renvoie une seule colonne.

Filtrer avec LIKE

Dans Teradata, l'opérateur LIKE ANY permet de filtrer les résultats selon un ensemble donné d'options possibles. Exemple :

SELECT*
FROM t1
WHERE a LIKE ANY ('string1', 'string2')

Pour traduire des instructions comportant cet opérateur en langage SQL standard, vous pouvez scinder la liste après ANY en plusieurs prédicats OR :

SELECT*
FROM t1
WHERE a LIKE 'string1' OR a LIKE 'string2'
La clause QUALIFY

La clause QUALIFY de Teradata est une clause conditionnelle de l'instruction SELECT qui filtre les résultats d'une fonction analytique calculée et ordonnée précédemment en fonction des conditions de recherche spécifiées par l'utilisateur. Sa syntaxe est constituée de la clause QUALIFY suivie de la fonction d'analyse, telle que ROW_NUMBER ou RANK, et des valeurs que vous souhaitez rechercher :

SELECT a, b
FROM t1
QUALIFY ROW_NUMBER() OVER (PARTITION BY a ORDER BY b) = 1

Les clients Teradata utilisent généralement cette fonction pour simplifier le classement et l'envoi des résultats, sans faire appel à une autre sous-requête.

La clause QUALIFY est traduite dans BigQuery par l'ajout d'une condition WHERE à une requête englobante :

SELECT a, b
FROM (
 SELECT a, b,
 ROW_NUMBER() OVER (PARTITION BY A ORDER BY B) row_num
 FROM t1
) WHERE row_num = 1

Pour consulter un exemple, reportez-vous au guide de démarrage rapide associé.

Langage de manipulation de données

Le DML (Data Manipulation Language) permet de répertorier, d'ajouter, de supprimer et de modifier des données dans une base de données. Il inclut les instructions SELECT, INSERT, DELETE et UPDATE.

Bien que les formes de base de ces instructions soient identiques entre SQL Teradata et SQL standard, Teradata inclut des clauses supplémentaires non standards et des constructions d'instructions spéciales que vous devez convertir lors de la migration. Les sections suivantes présentent une liste non exhaustive des instructions les plus courantes, des principales différences et des traductions recommandées.

Instruction INSERT

BigQuery est un entrepôt de données d'entreprise axé sur le traitement analytique en ligne (OLAP). L'utilisation d'instructions DML spécifiques à un point, telles que l'exécution d'un script avec de nombreuses instructions INSERT, constitue une tentative d'aborder BigQuery comme un système de traitement de transactions en ligne (OLTP), ce qui n'est pas une approche correcte.

Les instructions DML de BigQuery sont destinées aux mises à jour groupées. Par conséquent, chaque instruction LMD qui modifie des données initie une transaction implicite. Vous devez regrouper vos instructions LMD autant que possible pour éviter de générer une surcharge de transactions inutile.

Par exemple, si vous disposez de l'ensemble d'instructions suivant de Teradata, leur exécution en l'état dans BigQuery est un anti-modèle :

INSERT INTO t1 (...) VALUES (...);
INSERT INTO t1 (...) VALUES (...);

Vous pouvez traduire le script précédent en une seule instruction INSERT, qui effectue à la place une opération de transfert groupé :

INSERT INTO t1 VALUES (...), (...)

Un scénario classique est la création d'une table à partir d'une table existante, qui génère un grand nombre d'instructions INSERT. Dans BigQuery, au lieu d'utiliser plusieurs instructions INSERT, créez une table et insérez toutes les lignes en une seule opération à l'aide de l'instruction CREATE TABLE ... AS SELECT.

Instruction UPDATE

Les instructions UPDATE de Teradata sont semblables aux instructions UPDATE en langage SQL standard. Voici les principales différences :

  • L'ordre des clauses SET et FROM est inversé.
  • Tous les noms de corrélation Teradata utilisés en tant qu'alias de table dans l'instruction UPDATE doivent être supprimés.
  • En langage SQL standard, chaque instruction UPDATE doit inclure le mot clé WHERE, suivi d'une condition. Pour mettre à jour toutes les lignes de la table, utilisez WHERE true.

L'exemple suivant montre une instruction UPDATE de Teradata utilisant des jointures :

UPDATE t1
FROM t1, t2
SET
 b = t2.b
WHERE a = t2.a;

L'instruction équivalente en langage SQL standard est la suivante :

UPDATE t1
SET
 b = t2.b
FROM t2
WHERE a = t2.a;

Les considérations de la section précédente concernant l'exécution d'un grand nombre d'instructions DML dans BigQuery s'appliquent également dans ce cas. Nous vous recommandons d'utiliser une seule instruction MERGE au lieu de plusieurs instructions UPDATE.

Instruction DELETE

Dans le langage SQL standard, les instructions DELETE doivent contenir une clause WHERE. Dans Teradata, les clauses WHERE sont facultatives dans les instructions DELETE si vous supprimez toutes les lignes d'une table. (Si des lignes spécifiques sont supprimées, le DML de Teradata requiert également une clause WHERE.) Lors de la traduction, les clauses WHERE manquantes doivent être ajoutées aux scripts. Cette modification n'est nécessaire que lorsque toutes les lignes d'une table sont supprimées.

Par exemple, l'instruction ci-dessous dans SQL Teradata supprime toutes les lignes d'une table. La clause ALL est facultative :

DELETE t1 ALL;

La traduction en langage SQL standard est la suivante :

DELETE FROM t1 WHERE TRUE;

Procédures stockées

Les procédures stockées dans Teradata sont une combinaison d'instructions de syntaxe SQL et de contrôle. Des paramètres permettant de créer une interface personnalisée de la base de données Teradata peuvent être spécifiés.

Les procédures stockées sont compatibles avec la fonctionnalité de création de scripts BigQuery.

Cependant, il peut arriver que d'autres fonctionnalités soient plus appropriées. Ces alternatives dépendent de la façon dont vos procédures stockées sont utilisées. Exemple :

  • Remplacez les déclencheurs utilisés pour exécuter des requêtes périodiques par des requêtes planifiées.
  • Remplacez les procédures stockées qui contrôlent l'exécution complexe des requêtes et leurs interdépendances par des workflows définis dans Cloud Composer.
  • Refactorisez les procédures stockées utilisées comme API dans votre entrepôt de données à l'aide de requêtes paramétrées et de l'API BigQuery. Cette modification impose de recréer la logique de la procédure stockée dans un autre langage de programmation, tel que Java ou Go, puis à appeler les requêtes SQL avec les paramètres contenus dans le code.

Refactoriser et remplacer la logique métier qui réside dans les procédures stockées est une tâche délicate, qui nécessite une expertise en matière de couche de données et de bonnes pratiques d'architecture pour la plate-forme cible. Selon la complexité de votre migration, vous avez la possibilité de faire appel aux services de nos partenaires spécialisés.

Traduire pendant la migration

Dans le cadre de votre migration, vous devez examiner les instructions et les scripts SQL de Teradata, puis déterminer si vous devez convertir ces instructions en langage SQL standard, comme dans BigQuery. À l'instar de notre recommandation générale d'utiliser une migration itérative, nous vous conseillons d'aborder cette tâche de façon systématique.

Choisir un cas d'utilisation

Nous avons précédemment regroupé dans un cas d'utilisation tous les ensembles de données, les opérations de traitement des données, ainsi que les interactions système et utilisateur permettant de générer une valeur métier ou commerciale. L'un des cas d'utilisation inclut un groupe de tables dans l'entrepôt de données, les processus en amont qui fournissent les données à ces tables, et les processus en aval qui consomment ces données, comme illustré dans le schéma ci-dessous :

Flux de cas d'utilisation en amont (sur site) vers l'ancien entrepôt de données et vers les processus en aval.

Certains exemples de processus en amont, également appelés "pipelines de données en amont", sont constitués de flux provenant de lacs de données, de systèmes OLTP, d'un CRM et d'applications de journalisation. Voici quelques exemples de processus en aval : tableaux de bord, rapports, flux vers d'autres systèmes, applications métier et requêtes ad hoc utilisées par les analystes.

Lorsque vous sélectionnez des cas d'utilisation pour la migration, choisissez ceux dont la part principale de processus en aval est constituée de rapports internes ou de sorties de données bien définies, telles que des flux ou des API. Choisir ces types de cas d'utilisation dans les itérations de migration initiales présente plusieurs avantages :

  • Ils permettent de familiariser votre personnel avec les traductions requises, de sorte que vous puissiez estimer l'effort nécessaire pour les itérations suivantes.
  • Ils facilitent la configuration de tests automatisés pour la précision des données, car leurs résultats peuvent être comparés à l'aide de scripts.
  • Ils permettent d'effectuer des comparaisons visuelles à la volée, qui sont plus faciles à présenter aux parties prenantes non techniques que les données brutes.

Étapes de traduction

Pour transférer un cas d'utilisation de Teradata vers BigQuery, suivez les recommandations du document Présentation du transfert de schéma et de données. Lorsque vous transférez une table vers BigQuery et que des processus en aval ou en amont doivent être modifiés, vous devez également effectuer une évaluation des requêtes et des procédures stockées concernées par la modification afin de déterminer s'il y a lieu de les traduire.

Si des traductions sont nécessaires, suivez les consignes de la section Différences SQL Teradata et de la documentation de référence sur la traduction du langage SQL de Teradata vers BigQuery pour créer des requêtes en langage SQL standard conformes à la norme ISO SQL:2011.

Une fois les requêtes traduites, testez les processus en aval ou en amont dans un environnement contrôlé, en suivant les bonnes pratiques de votre entreprise en matière de tests et de déploiement continu. Vous pouvez également rechercher comment créer des pipelines de versions logicielles dans Google Cloud.

Nous vous recommandons de créer différents éléments pour vous aider dans la traduction, en commençant dès les premières itérations de la migration de votre entrepôt de données. Par exemple, pensez à développer, si possible, des bibliothèques de logiciels qui proposent des traductions de requête courantes et adaptées à vos cas d'utilisation. Ces bibliothèques faciliteront les itérations suivantes. Vous pouvez également accompagner ces bibliothèques de guides et de documents pour familiariser votre personnel avec le langage SQL standard et comprendre les meilleures options à appliquer dans la syntaxe BigQuery pour Teradata qui n'est pas utilisée dans BigQuery.

Étapes suivantes