Génération de données synthétiques à grande échelle (Partie 3)

Génération de données synthétiques à grande échelle (Partie 3)


Dans le première partie de cette série, mon estimé collègue, Robert Fehrmann, a montré comment créer de grandes quantités de données dans Snowflake à l’aide d’instructions SQL standard. Dans le deuxième partie de cette série, il a continué à développer un script Python qui automatise la création de ces instructions SQL en s’appuyant sur une spécification standardisée. Ces scripts et la documentation peuvent être trouvés ici.

J’ai récemment travaillé sur un projet qui nécessitait une représentation précise des données existantes du client afin de tester la différence de coût globale entre son architecture actuelle et un changement proposé. L’environnement se composait de plus de 2 000 tables de différentes tailles, auxquelles je n’avais pas directement accès. J’avais besoin d’un moyen de générer des statistiques précises à brancher sur la feuille de calcul de Robert, que j’utiliserais ensuite pour générer des instructions SQL sur un autre compte Snowflake. Il m’est apparu que l’effort nécessaire pour que mon client génère les statistiques, puis l’effort pour que je branche ces chiffres dans le modèle, pouvait être atténué en utilisant Python et un ordinateur portable qui faisait les deux en même temps.

Cet article fournira un aperçu de haut niveau du Python que j’ai créé. Le notebook est disponible sur GitHub ici et a été construit sur Collaboration Google (Colab). Pour ceux qui ne l’ont jamais utilisé auparavant, Colab fournit gratuitement un environnement Python Notebook hébergé. Vous pouvez charger le fichier GitHub .ipynb directement depuis l’interface de Colab.

Synthetic Gen 1

Le bloc-notes du générateur de données dynamiques

Le bloc-notes vous guide à travers les morceaux de code suivants :

  1. Code pour installer les bonnes bibliothèques nécessaires au code Python dans le notebook. Notez que cela est nécessaire pour s’exécuter dans un outil tel que Colab, mais si vous utilisez votre propre environnement Jupyter ou un autre environnement iPython, vous pouvez simplement les exécuter une fois pour configurer les choses.
  2. Code de configuration des variables de connexion pour votre compte source (le système à partir duquel vous souhaitez répliquer les données) et votre compte cible (le système sur lequel vous allez générer des données).
  3. Quatre fonctions incluses dans le notebook. La première fonction est getEngine(), qui crée simplement un moteur SQLAlchemy basé sur les variables définies à l’étape 2 . La deuxième fonction est execSQLDataFrame(), qui exécute tout SQL qui lui est transmis et renvoie les données sous forme de Pandas DataFrame. Les deux autres fonctions, execColStats()et execGenDataSQL()sont détaillés ci-dessous.
  4. La cellule principale, qui exécute les fonctions pour chaque table dans un schéma sur le compte source.

fonction execColStats()

Cette fonction génère des statistiques à partir des tables source en fonction des types de données de chaque champ. L’entrée de la fonction est un DataFrame qui contient toutes les informations de colonne d’une table, qui provient du SQL suivant :

 SELECT *
       FROM information_schema.columns
       WHERE table_name="""" + str(dfTables.name[i]) + """"
       AND table_schema="""" + sourceSchema + """"
       AND data_type NOT IN ('VARIANT','ARRAY')
       ORDER BY ordinal_position;

Étant donné que différents types de données nécessitent des statistiques différentes, j’ai créé le tableau suivant pour montrer ce que le code de cette fonction génère en plus du nom de la table, nom de colonneet Type de données des champs:

Screen Shot 2020 05 06 at 3.29.53 PM

Notez que le code de Robert ne gère pas actuellement la génération de valeurs ARRAY ou VARIANT côté cible. Le bloc-notes filtre les colonnes ARRAY et VARIANT de l’évaluation et elles seront finalement remplies avec des valeurs NULL.

La sortie de cette fonction est alors un DataFrame de ces statistiques qui peut être utilisé par le execGenDataSQL() fonction pour produire les instructions SQL de génération de données de Robert.

Fonction execGenDataSQL()

Cette fonction exploite le DataFrame de execColStats() pour créer l’instruction SQL nécessaire qui génère dynamiquement des données à l’aide du code de Robert. La plus grande modification du code de Robert à ce bloc-notes est que l’instruction SQL est une instruction INSERT, plutôt qu’une CREATE TABLE AS SELECT, puisque la cellule principale du bloc-notes générera la table pour vous à l’avance, si elle n’existe pas en utilisant le langage de définition de données (DDL) réel du système source. J’ai trouvé que c’était une façon plus précise de créer les tables. La sortie de cette fonction est une instruction SQL sous forme de chaîne, qui est ensuite utilisée dans la cellule principale au bas du bloc-notes.

Cellule principale

Pour chaque table du schéma spécifié, le code suivant est exécuté. Le code exécute le code qui a été référencé plus tôt dans ce post pour créer un dfColonnes Trame de données. Une fois le DataFrame créé, il est passé dans execColStats() pour produire un dfStats DataFrame contenant les informations nécessaires pour produire la requête SQL. dfStats est envoyé dans execGenDataSQL() (comme expliqué ci-dessus) pour créer un requête sql qui peut être exécuté. Avant d’exécuter requête sql, la cible est vérifiée pour voir si la table existe. Si la table n’existe pas, le code exécutera une get_ddl() instruction contre la source pour générer la table sur la cible. Ce code est affiché ci-dessous pour référence :

dfColumns = execSQLDataFrame(SourceEngine, sqlQuery)
 
if dfColumns.shape[0] != 0:
   dfStats = execColStats(dfColumns,str(dfTables.rows[i]))
   sqlQuery = execGenDataSQL(dfStats)
   sqlCheckQuery = """
SELECT table_name 
FROM information_schema.tables WHERE 
table_name="""" + dfTables.name[i] + """" 
and table_catalog = '""" + targetDatabase + """';"""
   target_check = execSQLDataFrame(TargetEngine, sqlCheckQuery)
 
   if target_check.shape[0] != 0:
       output = execSQLDataFrame(TargetEngine, sqlQuery)
   else:
       target_ddl_sql = """SELECT get_ddl('table','""" + dfTables.name[i] + """') as 
ctas;"""
       target_ddl = execSQLDataFrame(SourceEngine, target_ddl_sql)
       execSQLDataFrame(TargetEngine, target_ddl.ctas[0])
       output = execSQLDataFrame(TargetEngine, sqlQuery)     

Il y a quelques points à noter concernant le get_ddl() fonction qui pourrait créer des problèmes, cependant. Tout d’abord, si la définition de table comporte des contraintes pour des tables qui n’existent pas sur la cible, le DDL échouera. Deuxièmement, si la définition de table contient des commentaires contenant des apostrophes, le DDL échouera. Dans ces deux cas, vous devez générer manuellement la table sur la cible. Inclus dans le bloc-notes est une cellule optionnelle tout en bas qui permet le requête sql à imprimer sur le cahier plutôt qu’à exécuter directement. Cela vous permet d’exécuter manuellement l’instruction INSERT après avoir créé la table manuellement.

Sommaire

Si vous vous trouvez dans une situation où vous essayez de générer des données aléatoires qui ressemblent beaucoup à une base de données Snowflake existante, le bloc-notes référencé dans ce document vous aidera à répliquer un schéma source et à créer un nouveau schéma cible avec des données aléatoires. Il exploite une version légèrement modifiée du générateur de données de Robert, mais il ignore le processus de génération de statistiques, en les collectant dans une feuille de calcul CSV ou Excel, puis en utilisant ces informations pour générer des instructions SQL. Au lieu de cela, il génère ces statistiques pour vous, les exploite directement pour créer les instructions SQL, puis les exécute pour vous sur un nouveau schéma cible. Pour le client avec qui j’ai travaillé, ce processus nous a fait gagner de nombreux jours d’exécution, de collecte et de traitement de statistiques afin de générer des données aléatoires.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.