Notre activité nous amène à faire beaucoup de formations et nous essayons ( je reste prudent) d’être pédagogue. Cet article inaugure un nouveau format un peu plus technique sur des sujets divers, que l’on peut rencontrer de manière un peu récurrente et qui peuvent avoir un intérêt.
Les outils que nous allons utiliser : du très low code (presque no code)
Nous allons utiliser Baserow et n8n, des outils open source de référence.. Il est possible pour l’un et l’autre de créer des comptes en ligne, et ils sont auto hébergeables.
- Baserow : gestion de données.
- n8n : ETL et automatisation (mais nous allons l’utiliser que pour la partie ETL).
- En langage : Javascript (presque rien)/python (basique).
Les notions abordées :
- Mise en place d’un flux simple dans n8n
- Utilisation des noeuds code de n8n pour traitement.
Les fichiers et ressources pour simuler tout cela : en fin d’article.
Position du problème
Parmi les outils que nous utilisons il y a Baserow. Baserow est un outil no code, open source puissant pour gérer les données. Il permet très rapidement de mettre en place des bases de données ‘relationnelles’, de mettre des des contraintes entre les tables etc. (par exemple Entreprise et Contact). Son équivalent propriétaire est airtable.
Un cas que nous avons eu est le suivant : ayant une base de données relationnelle (sqlite, mysql….) avec des contraintes déjà mises sur les tables, comment importer ce schéma dans baserow sans perdre les liens entre les tables ?
En effet si Baserow permet d’importer très efficacement une table à partir d’un export csv, il ne permet pas d’importer les contraintes entre les tables (clés étrangères)
Ce tuto montre une façon de le faire en utilisant n8n.
Les contraintes dans les bases de données relationnelles
Alors, pour les anciens comme moi (je suis Stéphane – nettement plus vieux que mon associé Aurélien, qui pourrait mal le prendre si je ne précisais pas), une base de données relationnelle est le standard quand on veut ranger et exploiter sa donnée. On les retrouve absolument partout. Ce sont des logiciels comme PostgreSQL, MariaDB ou Oracle qui le permettent.
Un des concepts fondamental est la notion de clé : clé primaire et clé étrangère qui permet de faire le lien entre des tables.
Prenons un exemple simple avec deux tables :
- une table ORGANISATION (entreprises, association…)
- une table CONTACT (les gens qui travaillent dans une organisation)
Chaque organisation possède une clé primaire (ici ORG_ID). Cette clé est ensuite utilisée dans la table CONTACT sous forme de clé étrangère pour créer un lien entre les deux tables.
Imaginons qu’on ait une organisation appelée « Café des sports », qui emploie deux personnes : Patrick et Yassin. On obtiendrait une structure comme ceci :
Ce lien via ORG_ID permet de savoir à quelle organisation appartient chaque contact. C’est la base des bases quand on parle de modèle relationnel.
Et le modèle physique complet de données ressemblerait à cela :
Pour exporter cette base de données, il suffit d’exporter chacune des tables, sous forme de fichiers csv (cf organisation.csv et contact.csv)
Importer la base sous BASEROW
Prérequis : vous avez créé une base de données sous baserow, vous avez un compte n8n et n8n et baserow peuvent communiquer grace aux credentials que vous avez mis en place;
Etape 1 : créer les tables dans Baserow par import de CSV
Pour le tuto nous allons imaginer un lien entre deux tables. Ces deux tables ont été exportées au format csv pour le besoin du tuto, elles sont disponibles là : contenu csv CONTACT et ORGANISATION.
Donc :
- Créer les deux tables ORGANISATION et CONTACT par import de csv
- Pour chacune des tables préciser le champ primaire (la première colonne), CONT_ID pour contact et ORG_ID pour organisation.
On doit avoir cela pour ORGANISATION
Et pour CONTACT
On voit qu’à ce niveau on a bien nos deux tables, mais bien que les champs d’association (org_id dans la table contact qui renvoie vers la table ORGANISATION ) soient materialisés il n n’y a aucun lien physique entre les deux mis en place par Baserow.
Etape 2 : Créer le lien dont la valeur restera à initialiser
Dans la table CONTACT créer une colonne qui va être vide pour l’instant de table liée entre contact et organisation.
A ce niveau déja manuellement on peut faire des liens en jouant avec Baserow mais les anciens liens ne sont pas mis.
On pourrait aussi le faire à la main, mais ça peut être long
Remarque :
Dans les colonnes liées (là dans la colonne Organisation Liée) Baserow stock l’identifiant unique de ligne, le row_id de la ligne de la table cible, mais affiche l’identifiant unique de la table.(première colonne)
Comment recréer les liens ?
L’idée est assez simple. Il faut avoir la correspondance des identifiants row_id,org_id dans ORGANISATION.
En gros un tableau comme ça
Row_id org_id
1 18
2 90
3 89(dans les faits ce seront les mêmes chiffres car les org_id sont numérotés eux aussi de 1 à 10)
L’algorithme est le suivant:
Pour chaque ligne dans CONTACT :
Récuperer le org_id (par exemple 18) dans la table de correspondance
Chercher sa correspondance dans la colonne row id (1 dans l’exemple)
Mettre à jour le champs ‘Organisation liée’ avec le row_id obtenu (1)
C’est ce que nous allons faire sous n8n
Mettre à jour Baserow avec n8n
Ce que l’on va faire est assez simple et résumé dans le flux suivant :
Etape 1 : On part des tables ORGANISATION et CONTACT pour rajouter une colonne : ‘source’ cela va permettre sur chaque ligne de rajouter l’origine.
Nous sommes passé par un petit bout de code python pour cela :
Même chose pour ‘CONTACT’
Etape 2 : faire un merge pour avoir un flux uniquer à traiter
Puis on fait un merge et cela permet d’avoir un format unique de données en ne perdant pas l’origine des informations
Pourquoi faire cela ? Dans n8n, tous les flux entrants dans un nœud Code sont regroupés dans items, sans information de provenance.
Pour identifier l’origine, il faut ajouter un champ source avant le merge.
Etape 3 : Mise à jour de CONTACT dans Baserow
A ce niveau c’est très simple , au nouveau contact id de CONTACT correspond quel nouvel org_id d’ORGANISATION ? Puis ensuite on mettra à jour la table.
Donc première chose on va construire cette table de correspondance sur la base de toutes les données regroupées et sourcées dans items (car on a fusionnné les deux sources grace à un merge)
Le code Python à utiliser dans le noeud
# création des listes contacts et organisations
contacts=[]
organisations=[]
for elt in items:
if elt["json"]["source"]=="contact":
contacts.append(elt)
elif elt["json"]["source"]=="organisation":
organisations.append(elt)
'''
1-avoir une table de correspondance org_id/id pour pour organisation. Histoire de savoir à quel nouvel id est associé l'ancien org_id
--> on doit créer un dictionnaire pour avoir la correspondance org_id --> new_org_id
'''
new_org_id={}
for elt in organisations:
new_org_id[elt["json"]["ORG_ID"]]=elt["json"]["id"]
'''
2- pour contact on doit sortir un dictionnaire. On avait contact_id -->org_id on doit sortir un
dictionnaire pour avoir la correspondance new_contact_id --> new_org_id
'''
new_organisation_liee_de_contact={}
for elt in contacts:
new_organisation_liee_de_contact[elt["json"]["id"]]=new_org_id[elt["json"]["ORG_ID"]]
results = []
for contact_id, org_id in new_organisation_liee_de_contact.items():
results.append({
"json": {
"new_contact_id": contact_id,
"new_org_id": org_id
}
})
return resultsOn obtient celà :
Et ensuite il suffit de mettre à jour CONTACT.
Attention pour la mise à jour, quand on utilise le noeud ‘update’ le champs ROW ID correspond implicitement à la colonne de clé primaire de CONTACT. Dans baserow, c’est la premiere colonne, donc dans mon cas CONT_ID. Donc ce que ça signifie “fait une mise à jour de CONTACT où CONT_ID = {{ $json.new_contact_id )) et on met à jour le champs ‘organisation liée’ avec new_org_id.
Résultat final
On obtient ce que l’on souhaitait (c’est heureux) :
