Génération de données synthétiques à grande échelle – Partie 2

Génération de données synthétiques à grande échelle – Partie 2


Dans le première partie de ce post, j’ai montré comment créer de grandes quantités de données dans Snowflake à l’aide d’instructions SQL standard. La création manuelle des instructions SQL demande beaucoup de travail, mais heureusement, vous pouvez automatiser la tâche. Tout ce dont vous avez besoin est une spécification qui définit les noms de table et de colonne, les types de données et des informations statistiques supplémentaires.

Avec l’aide de James Frink, ingénieur commercial chez Snowflake, j’ai créé un script Python qui automatise la génération des instructions SQL sur la base d’une spécification standardisée. Vous pouvez trouver l’ensemble du projet, y compris le script Python et la documentation, ici.

Spécification du schéma

La spécification de schéma pour l’utilitaire de génération requiert les attributs suivants en entrée :

  • Nom du schéma
  • Nom de colonne
  • Cardinalité de colonne (le nombre de valeurs distinctes dans cette colonne)
  • Cardinalité de la table (le nombre de lignes dans cette table)
  • Type de données (voir la liste des valeurs dans le tableau ci-dessous)
  • Longueur des données (valable pour les types de données char, varchar et number)
  • Précision des données (valable pour les types de données varchar et number)

L’utilisation d’Excel ou de Google Sheets est un moyen simple de gérer les spécifications, en particulier si vous souhaitez apporter des modifications en masse. Excel et Google Sheets offrent tous deux la possibilité de créer des fonctions, ce qui est pratique lorsque vous souhaitez modifier (réduire) le nombre de lignes par table pour les tests.

L’utilitaire génère un code spécifique au type de données pour les types de données les plus largement utilisés. Par exemple, si nous voulons générer 100 dates différentes, nous ajoutons un nombre aléatoire de jours (1 à 100). Ou si nous voulons créer des horodatages aléatoires à la seconde près pour une période de 24 heures, nous pouvons ajouter un nombre aléatoire de secondes (1 à 24*60*60) à une date de base.

Les types de données pris en charge et la méthode correcte pour créer des valeurs uniques sont décrits dans le tableau ci-dessous. Veuillez noter que CHAR et VARCHAR utilisent deux méthodes différentes. VARCHAR utilise le randstr() fonction. La plus grand avantage de randstr() est qu’il crée des chaînes vraiment aléatoires. Mais cet avantage a un coût. Les relations de clé étrangère sont beaucoup plus difficiles à gérer de cette façon (voir ci-dessous). Pour cette raison, les valeurs CHAR de longueur fixe sont créées en générant un nombre aléatoire (sur un ensemble de nombres de 1 à la cardinalité de la colonne), qui sont ensuite complétées par une chaîne statique.

Screen Shot 2019 08 13 at 3.01.58 PM

Génération de code

Si vous n’avez pas encore cloné ou téléchargé le GitHub dépôtprenez un moment pour le faire maintenant.

Le dépôt comprend :

  • Un script Python (utilisé pour générer des instructions SQL à partir d’une spécification)
  • Un classeur Excel (utilisé pour générer des instructions SQL à partir d’une spécification)
  • Un script awk (utilisé pour assembler les instructions SQL pour le générateur de code Excel)
  • Fichiers .txt (sortie intermédiaire pour le générateur de code Excel)
  • Fichiers .csv (spécification de schéma pour le générateur de code Python)
  • Fichiers .sql (fichiers de sortie SQL)

Il existe deux méthodes prises en charge pour générer des instructions SQL à partir de la spécification. Vous pouvez utiliser le classeur Excel ou un script Python pour générer les instructions SQL. Vous pouvez trouver une description détaillée sur la façon d’utiliser les deux méthodes sur GitHub.

Les deux méthodes ont des avantages et des inconvénients. La route Excel offre plus de flexibilité pour les situations plus complexes, car vous pouvez facilement modifier les fonctions de texte dans Excel qui génèrent le code SQL. Mais ensuite, vous devez utiliser le script awk pour créer le script SQL final. Parce que l’utilisation du script Python est un processus en une seule étape, il est plus facile de faire une démonstration et, par conséquent, c’est ce sur quoi nous nous concentrerons dans cet article.

Schéma de table unique

Nous allons commencer par créer une spécification pour la table de test, testdf, à partir de Partie 1. Toutes les colonnes sont de type de données VARCHAR et ont une longueur spécifique, qui définit la longueur maximale de la colonne. La colonne license_plate a également une précision, qui définit la longueur minimale. Enfin, nous spécifions la cardinalité de colonne, en particulier le nombre de valeurs distinctes pour chaque colonne, et la cardinalité de table, qui est le nombre de lignes dans la table.

Screen Shot 2019 08 19 at 12.34.51 PM

Vous pouvez copier la spécification ci-dessus dans Excel ou Google Sheets et l’enregistrer en tant que fichier .csv. J’ai appelé mon fichier testdf.sql.

L’étape suivante consiste à générer l’instruction SQL en exécutant le script du générateur de code Python.

python3 snowflake_python_generator.py testdf.csv 

La sortie est imprimée directement sur la console :

image3

Pour votre commodité, j’ai mis la sortie ci-dessous afin que vous puissiez la copier si nécessaire.

CREATE TRANSIENT SCHEMA IF NOT EXISTS schema1 
DATA_RETENTION_TIME_IN_DAYS=0;
USE SCHEMA schema1;
CREATE or REPLACE TABLE table1
AS
SELECT
   randstr(uniform(1,30, random(10001)), 
uniform(1,100000,random(10001)))::varchar(30) as name,
   randstr(uniform(1,30, random(10002)), 
uniform(1,10000,random(10002)))::varchar(30) as city,
   randstr(uniform(10,10, random(10003)), 
uniform(1,100000,random(10003)))::varchar(10) as license_plate,
   randstr(uniform(1,30, random(10004)), 
uniform(1,20000,random(10004)))::varchar(30) as email
from table(generator(rowcount => 1000000000));

Les instructions SQL générées incluent une instruction CREATE SCHEMA. Pour économiser de l’espace et réduire les coûts, l’instruction crée un schéma transitoire avec une durée de conservation des données de 0 jour. Cela évite de consommer de l’espace supplémentaire pour Snowflake Fail-Safe ou Time Travel. Ne pas avoir d’espace Fail-Safe n’est pas vraiment un problème ici, car nous pouvons toujours réexécuter les instructions, au cas où l’ensemble de données serait corrompu.

Si vous voulez acheminer la sortie dans un fichier, utilisez le –fichier sql paramètre et fournissez un nom de fichier.

python3 snowflake_python_generator.py testdf.csv --sqlfile testdf.sql

Importez le script SQL dans une feuille de calcul Snowflake (ou copiez-le simplement ci-dessus), choisissez une base de données et un entrepôt, puis exécutez-le. Comme prévu (en raison de notre benchmark en Partie 1), Snowflake crée l’ensemble de données de 1 milliard de lignes en 2 minutes, car nous exécutons sur un entrepôt moyen.

image4

Tables multiples et clés étrangères

La création d’un script SQL pour une table peut vous sauver la vie si vous testez la génération de données pour une table avec des centaines de colonnes. Mais le véritable pouvoir vient lorsque vous créez plusieurs schémas avec plusieurs tables qui ont des relations de clé étrangère.

Aux fins de cet article, je vais démontrer les concepts sur une base de données avec deux schémas ayant un total de quatre tables.

  • Schéma OLTP :
    • Order_master : 1 000 000 lignes
    • Order_detail : 1000000000 lignes, clés étrangères vers order_master, produit
    • Produit : 10000 lignes
  • Schéma LOG

Les définitions du tableau sont assez explicites. Bien sûr, nous pouvons rendre ces tableaux aussi larges et aussi complexes que nous le souhaitons. Ceci n’est qu’un petit exemple.

Screen Shot 2019 08 19 at 12.37.34 PM

Notez que la table order_detail a deux clés étrangères : une pour la table order_master et une pour la table product. La spécification ne définit pas explicitement les clés étrangères. Cependant, les clés fonctionnent toujours si nous assurons deux exigences :

  • Tout d’abord, le type de données de l’attribut de clé primaire et l’attribut de clé étrangère doit correspondre exactement. Par exemple, dans la spécification ci-dessus, order_master.id correspond à order_detail.order_master_id. Si les types de données ne correspondent pas, les données générées ne correspondront pas.
  • Deuxièmement, la cardinalité de table de la table principale doit être égale (ou supérieure) à la cardinalité de colonne de la colonne de clé étrangère dans la table détaillée. Par exemple, product.id (100000) = order_detail.product_id(100000).

Tant que ces deux exigences sont remplies, les relations de clés étrangères fonctionneront dans les données générées.

Comme précédemment, nous devons copier la spécification dans un fichier CSV via Excel ou Google Sheets. Enregistrez la sortie en tant que fichier .csv (j’ai appelé mon fichier online_ship.csv) et exécutez le générateur de code sur le fichier enregistré.

python3 snowflake_python_generator.py online_shop.csv --sqlfile 
online_shop.sql

Voici la sortie comme référence :

CREATE TRANSIENT SCHEMA IF NOT EXISTS oltp_schema 
DATA_RETENTION_TIME_IN_DAYS=0;
USE SCHEMA oltp_schema;
CREATE or REPLACE TABLE order_master
AS
SELECT
   (seq8()+1)::bigint as id,
   dateadd(day, uniform(1, 365, random(10002)), date_trunc(day, 
current_date))::date as order_date
from table(generator(rowcount => 50000000));

CREATE or REPLACE TABLE order_detail
AS
SELECT
   (seq8()+1)::bigint as id,
   uniform(1,50000000 , random(10004))::integer as order_master_id,
   rpad(uniform(1, 100000, random(10005))::varchar,15, 
'abcdefghifklmnopqrstuvwxyz')::char(15) as product_id
from table(generator(rowcount => 1000000000));

CREATE or REPLACE TABLE product
AS
SELECT
   rpad((seq8()+1)::varchar,15, 
'abcdefghifklmnopqrstuvwxyz')::char(15) as id,
   randstr(uniform(10,50, 
random(10007)),uniform(1,100000,random(10007)))::varchar(50) as 
product_description,
   rpad(uniform(1, 1000, random(10008))::varchar,10, 
'abcdefghifklmnopqrstuvwxyz')::char(10) as product_group
from table(generator(rowcount => 100000));

CREATE TRANSIENT SCHEMA IF NOT EXISTS log_schema 
DATA_RETENTION_TIME_IN_DAYS=0;
USE SCHEMA log_schema;
CREATE or REPLACE TABLE log
AS
SELECT
   (seq8()+1)::bigint as id,
   randstr(uniform(16,128, 
random(10010)),uniform(1,5000000000,random(10010)))::varchar(128) as 
msg
from table(generator(rowcount => 5000000000));

Enfin, nous pouvons exécuter le script SQL dans Snowflake. La création de plus de 6 milliards de lignes pour ce test prend environ 15 minutes sur un entrepôt de taille moyenne. Grâce à la facturation à la seconde et à l’évolutivité presque linéaire de Snowflake, le coût est le même, qu’il soit exécuté sur un entrepôt moyen ou sur un entrepôt X-Large. Un entrepôt X-Large est quatre fois plus grand et génère l’ensemble de données environ quatre fois plus vite. Pour cette raison, j’ai exécuté ce test sur un entrepôt X-Large, qui crée plus de 6 milliards de lignes en à peine 4 minutes environ.

image2

La durée totale d’exécution de toutes les instructions est d’environ 4 minutes et 20 secondes. Pour prouver que les clés étrangères ont été créées correctement, la capture d’écran suivante montre une requête exécutant des jointures internes entre les tables order_detail, order_master et product. Nous attendons 1 milliard de lignes car nous avons créé 1 milliard de lignes dans la table order_detail et, comme vous pouvez le voir, le résultat de la requête est de 1 milliard.

image1

Conclusion

Les fonctions de génération de données Snowflake permettent de créer très facilement des données de test pour une variété de types de données différents. Le framework implémenté dans le script Python de génération de données automatise l’ensemble du processus de génération du code SQL nécessaire pour plusieurs schémas, plusieurs tables dans un schéma et même des relations de clé étrangère entre différentes tables. Et les performances de Snowflake à une évolutivité quasi linéaire, ce qui facilite la création de milliards de lignes.

Contenu additionnel

Laisser un commentaire

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