Utilisation avancée du Data Mapper

Rappel

Avant d’aborder les fonctionnalités avancées du Data Mapper, un bref rappel de son fonctionnement de base est utile.

Configuration du Data Mapper

Le Data Mapper sert à mapper les colonnes des données source vers la table de données cible.

Inspection et renseignement du mappeur

Le bouton de menu Inspecter la source offre d’autres façons de mapper les colonnes de la source vers la cible :

Renseigner les deux tables de mappage : propage toutes les valeurs de la table de données source vers la table de données cible. C’est le comportement par défaut.
Renseigner uniquement la table de mappage source : mappe uniquement toutes les valeurs de la table de données source. C’est utile lorsque vous modifiez un workflow existant dont la structure des colonnes source a changé.
Renseigner uniquement la table de mappage cible : propage toutes les valeurs vers la table de données cible uniquement.

Si les options de colonnes source et cible ne suffisent pas, vous pouvez ajouter d’autres colonnes à la table de données cible de plusieurs façons :

Tout propager insère toutes les colonnes source dans la table de données cible, qu’elles existent déjà ou non.
Propager la sélection insère uniquement les colonnes source sélectionnées.
Cliquez avec le bouton droit du côté cible et sélectionnez Insérer une ligne pour insérer une ligne juste au-dessus de la ligne actuellement sélectionnée.
Cliquez avec le bouton droit du côté cible et sélectionnez Ajouter une ligne pour insérer une ligne en bas (à l’extrême droite) de la table de données cible.

Suppression de colonnes

Pour supprimer des colonnes de la table de données cible, sélectionnez les colonnes voulues, puis cliquez avec le bouton droit et sélectionnez Supprimer.

Modification de l’ordre des colonnes

Pour réorganiser les colonnes de la table de données cible, sélectionnez les colonnes voulues. Vous pouvez utiliser l’une des méthodes suivantes :

Flèches de déplacement groupé : sélectionnez l’option de déplacement voulue avec les flèches situées en haut à droite
Menu contextuel : cliquez avec le bouton droit et sélectionnez Déplacer en haut, Monter, Descendre ou Déplacer en bas.

Réduire le résultat aux seuls enregistrements distincts

Pour ne renvoyer que les valeurs distinctes, sélectionnez l’option de menu Distinct. Un ensemble de cases à cocher s’affiche alors pour chaque colonne de la source. Il suffit de cocher la case en regard de la colonne correspondante pour ne renvoyer que des résultats distincts.

Agrégation et regroupement

Pour agréger les résultats, sélectionnez l’option de menu Résumer. Un ensemble de zones de sélection s’affiche alors pour chaque colonne de la table de données cible. Choisissez une méthode de synthèse adaptée à chaque colonne.

Le mappeur relie chaque colonne source à une colonne cible, en renommant et en convertissant les types au passage. Les colonnes source non mappées sont exclues du résultat.

Utilisation avancée

Options d’agrégation

Pour agréger les résultats, sélectionnez l’option de menu Résumer. Un ensemble de zones de sélection s’affiche alors pour chaque colonne de la table de données cible. Les options de synthèse suivantes sont disponibles :

Fonction	Description
Regrouper par	Regroupe les résultats par valeur
Nombre	Nombre d’observations non nulles dans le groupe
Nombre (valeurs nulles incluses)	Nombre d’observations dans le groupe
Somme	Somme des valeurs du groupe
Moyenne	Moyenne des valeurs du groupe
Médiane	Médiane des valeurs du groupe
Mode	Mode des valeurs du groupe
Min	Minimum des valeurs du groupe
Max	Maximum des valeurs du groupe
Première	Première valeur du groupe selon l’ordre de tri
Dernière	Dernière valeur du groupe selon l’ordre de tri
Écart type	Écart type sans biais du groupe
Écart type d’échantillon	Écart type d’échantillon du groupe
Écart type de population	Écart type de population du groupe
Variance	Variance sans biais du groupe
Variance d’échantillon	Variance d’échantillon du groupe
Variance de population	Variance de population du groupe
Avancé hors regroupement	Sélection d’agrégation spéciale lors de l’utilisation de fonctions de fenêtrage

Choisissez la méthode de synthèse adaptée à la colonne.

Clés d’agrégation pour REST et les assistants IA

Lorsque vous définissez une étape via l’API REST ou un assistant IA, chaque colonne cible porte sa synthèse dans agg, et le paramètre aggregation_type de l’étape sélectionne les mêmes choix. Toute étape dotée d’un Data Mapper accepte ces valeurs :

Clé	Synthèse
`group`	Regrouper par
`dont_group`	Avancé hors regroupement : la colonne n’est ni regroupée ni agrégée, pour les expressions qui agrègent ou utilisent une fenêtre par elles-mêmes
`count`	Nombre d’observations non nulles dans le groupe
`count_null`	Nombre, valeurs nulles incluses
`count_distinct`	Nombre de valeurs distinctes dans le groupe
`sum`	Somme
`avg`	Moyenne
`median`	Médiane
`min`	Minimum
`max`	Maximum
`stddev`	Écart type
`stddev_samp`	Écart type d’échantillon
`stddev_pop`	Écart type de population
`variance`	Variance
`var_samp`	Variance d’échantillon

Tri des lignes

Chaque colonne cible peut porter un sort : asc, desc ou none, ou un objet {ascending, order}. ascending vaut true ou false. order est un entier qui définit la priorité de la clé : les colonnes dotées d’un order sont triées en premier, en commençant par le plus petit nombre, puis les autres colonnes triées suivent selon leur position.

Lorsque vous utilisez une fonction de fenêtrage, sélectionnez Avancé hors regroupement comme méthode d’agrégation. Cette sélection spéciale est nécessaire, car la fonction de fenêtrage comporte déjà une agrégation.

Constantes

Indiquer une valeur dans la colonne Constante remplace la valeur de la colonne source, si elle est spécifiée, et renseigne la colonne avec la valeur constante indiquée.

Source, constante et expression ensemble

Une colonne cible prend sa valeur de celui de ces éléments que vous renseignez, dans cet ordre : une Constante l’emporte, puis une Expression, puis la colonne Source.

Une colonne qui calcule sa valeur n’a donc pas besoin de Source. Laissez-la vide pour une constante ou une expression : la constante ou l’expression indique déjà la valeur, et une Source qui l’accompagne est ignorée. Renseignez Source lorsque vous mappez une colonne telle quelle à partir des données entrantes, que vous la renommez ou que vous la convertissez au passage.

Une expression lit les colonnes entrantes par leur nom, avec get_column(table, 'Column Name'), plutôt que par la cellule Source.

Nettoyeurs

Le Data Mapper offre une fonction pratique de nettoyage par pointer-cliquer pour appliquer des conversions aux données d’une colonne.

Les opérations de nettoyage comprennent les catégories suivantes :

Suppression des espaces du texte
Mise en forme du texte
Transformations de texte
Conversion vers et depuis des valeurs NULL
Format des nombres
Analyse de dates

Le résultat des sélections du nettoyeur est converti en une expression consolidée, que vous pouvez consulter dans les informations de l’expression.

Expressions

Les expressions du Data Mapper comptent parmi les concepts les plus puissants et les plus souples de PlaidCloud. Elles offrent une flexibilité quasi illimitée tout en étant exceptionnellement performantes, même sur de très grands volumes de données.

Les expressions sont écrites avec la syntaxe Python SQLAlchemy, complétée de quelques fonctions d’aide propres à PlaidCloud. PlaidCloud peut ainsi exposer directement l’ensemble des capacités de l’entrepôt de données sous-jacent (par exemple Greenplum, SAP HANA, Redshift, etc.). En outre, de nombreuses ressources publiques proposent des références rapides sur l’utilisation des opérations SQLAlchemy. En utilisant la syntaxe SQLAlchemy standard, PlaidCloud évite l’écueil courant qui consiste à créer encore une autre syntaxe propre à un domaine.

L’éditeur d’expressions s’ouvre en double-cliquant sur la cellule d’expression de la colonne. Une fois ouvert, la liste des colonnes s’affiche à gauche et une vaste bibliothèque de fonctions à droite.

Il est tout à fait possible de saisir l’expression directement dans l’éditeur, mais il est généralement plus simple de commencer par la sélection des fonctions et des colonnes par pointer-cliquer. La bibliothèque de fonctions comprend les groupes suivants :

Conditions
Conditions propres aux colonnes
Conversions
Dates
Mathématiques
Texte
Synthèses
Opérations de fonctions de fenêtrage
Tableaux
JSON
PostGIS (géospatial)
Trigonométrie

Une fois l’expression terminée, enregistrez-la pour qu’elle soit appliquée à la colonne.

Consultez des exemples et les fonctions d’expression dans la section Expressions.

Conversion en Float

cast(x, Float) renvoie un nombre à double précision sur 64 bits sur tous les entrepôts de données, de sorte qu’un montant tel que 1225541207.71 conserve tous ses chiffres. Sur StarRocks, la conversion vers Float est désormais appliquée alors qu’elle était auparavant ignorée, et sur Databend elle est en double précision plutôt qu’en simple précision. Une colonne Float que PlaidCloud crée à partir de données flottantes est elle aussi en double précision. Une table qui possède déjà une colonne en simple précision passe en double précision la prochaine fois qu’une étape la reconstruit avec Écraser.

Sur StarRocks :

Une conversion vers un grand entier accepte des valeurs supérieures à environ 2,1 milliards.
Une conversion vers Numeric sans précision conserve les décimales, jusqu’à 10 positions, au lieu d’arrondir à un nombre entier.
Une conversion vers JSON est appliquée.

Sur tous les entrepôts de données, une valeur qui ne peut pas être convertie, comme un texte non numérique converti en nombre, devient nulle au lieu d’interrompre l’étape.

Lire une autre table depuis une expression

sqlalchemy.literal_column transmet tel quel un fragment de SQL à l’entrepôt de données, et accepte une valeur unique : une colonne, un calcul, un CASE, un appel de fonction, une conversion, une fonction de fenêtrage. Un literal_column contenant une sous-requête ((SELECT ... FROM ...)) est refusé, car une sous-requête accède à une table que l’étape n’a jamais déclarée.

Les règles de cette section s’appliquent partout où l’expression est écrite : ici dans le mappeur, dans le filtre d’une étape, ainsi que dans une requête assemblée ailleurs et envoyée à la plateforme. Elles sont contrôlées à l’enregistrement de l’étape, puis de nouveau lorsque l’expression est transformée en SQL.

Construisez plutôt la sous-requête avec sqlalchemy.select(...) : .scalar_subquery() lorsque vous avez besoin d’une valeur unique dans une colonne, ou .subquery() lorsque vous sélectionnez à partir de celle-ci :

# Refused
sqlalchemy.literal_column('(SELECT rate FROM fx WHERE fx.cc = t.cc)')

# Supported — a single value in a column
sqlalchemy.select(fx.c.rate).where(fx.c.cc == t.c.cc).scalar_subquery()

# Supported — a table to select from
sqlalchemy.select(fx.c.rate).subquery()

Mettre entre parenthèses un OR de premier niveau

Un literal_column dont l’opérateur le plus externe est OR est également refusé. PlaidCloud transmet le texte tel quel ; une condition ajoutée après lui ne s’appliquerait donc qu’à la moitié de l’expression : a OR b suivi de AND region = 'East' se lit a OR (b AND region = 'East'), et les lignes que vous vouliez exclure sont renvoyées.

Ajoutez des parenthèses et cela fonctionne comme avant :

# Refused
sqlalchemy.literal_column("status = 'open' OR status = 'pending'")

# Supported
sqlalchemy.literal_column("(status = 'open' OR status = 'pending')")

Une disjonction située à l’intérieur de l’expression, dans un CASE ou déjà entre parenthèses, n’est pas concernée.

XOR est refusé partout où il apparaît, avec ou sans parenthèses : les entrepôts de données ne s’accordent pas sur sa priorité, il n’existe donc aucune lecture sûre partout. Écrivez-le plutôt explicitement, sous la forme (a AND NOT b) OR (NOT a AND b).

Un literal_column contenant un commentaire est refusé pour la même raison : un commentaire s’étend jusqu’à la fin de l’instruction et efface donc tout ce que PlaidCloud ajoute après votre expression, et pas seulement ce que vous avez écrit.

-- est refusé là où il atteindrait le reste de l’instruction. # est refusé n’importe où dans le texte, sans exception, y compris à l’intérieur d’une chaîne entre guillemets et y compris dans les opérateurs de chemin JSON #> et #>> de Postgres :

# Refused
sqlalchemy.literal_column("attrs #> '{a,b}'")
sqlalchemy.literal_column("code || '#'")

# ouvre un commentaire sur les entrepôts de données compatibles MySQL tels que StarRocks, et PlaidCloud ne sait pas quel entrepôt exécutera l’expression au moment où vous l’écrivez : il n’existe donc aucune position dans le texte où il soit sûr de façon fiable, et un # qui semble entre guillemets pour un moteur est le début d’un commentaire pour un autre. Si vous avez besoin du caractère comme donnée, transmettez-le comme valeur :

# Supported
sqlalchemy.func.concat(table.code, sqlalchemy.literal('#'))

Les deux mêmes écritures sont refusées dans .op('...').

Tout le reste de ce pour quoi literal_column est normalement utilisé n’est pas concerné : literal_column('current_date'), literal_column("CASE WHEN amount > 0 THEN 'p' ELSE 'n' END") et func.count() fonctionnent tous comme avant. Les noms de colonnes contenant des espaces, des points ou des guillemets ne sont pas concernés non plus ; l’entrepôt de données les met entre guillemets pour vous.