[Environnement de développement] Comment créer un ensemble de données proche de la base de données de production

Aperçu

Lors de la création d'un service Web, souhaitez-vous des données proches de la base de données dans l'environnement de production, même dans l'environnement de développement actuel? Ce sera. Dans votre environnement local, vous avez peut-être oublié d'appliquer un correctif pour maintenir l'intégrité des données exécutées dans la base de données de production, etc., et les données disponibles peuvent ne pas être dans la forme attendue. De plus, c'est une façon de préparer les données d'amorçage, mais la maintenance des données d'amorçage est inopinément coûteuse et les caractéristiques des données sont différentes des données de production, il y a donc un problème de performances lors de leur publication. Des choses arrivent aussi.

J'ai donc essayé d'extraire partiellement les données de la base de données de production et de les utiliser localement, donc j'écrirai un résumé de ce que j'ai fait avec ce script. Le script est écrit en Python, donc je suis désolé si vous ne pouvez pas lire Python ...

** En tant que prémisse très importante, je pense qu'il existe de nombreux services légalement dangereux si vous apportez les données de production localement sans rien traiter, alors assurez-vous de mettre en place un traitement de masquage approprié. La méthode de masquage dépend également des caractéristiques des données et ne sera pas abordée en détail dans cet article. ** **

Flux global

  1. Créez une table qui est un sous-ensemble de la table DB de production
  2. Créez un vidage de la table des sous-ensembles
  3. Importez le vidage localement

Mise en garde) Bien qu'il soit écrit comme une base de données de production, veuillez bien sûr aller dans un endroit qui n'affecte pas le serveur de base de données qui s'exécute en production. Imaginez que vous travaillez sur un autre serveur que vous avez restauré à partir d'un instantané. Si vous le faites en production, veuillez faire une entrée dans le [Calendrier de l'Avent pour ceux qui l'ont fait en production] de l'année prochaine (https://qiita.com/advent-calendar/2019/yarakashi-production)!

1. Créez une table qui est un sous-ensemble de la table DB de production

C'est la partie à laquelle je pense le plus. Je vais l'expliquer avec un exemple pour une compréhension facile. Supposons que vous ayez trois tables, livres, utilisateurs et favoris, et que vous ayez une clé étrangère des favoris aux livres et aux utilisateurs. La création d'une table qui est un sous-ensemble signifie les données que vous souhaitez utiliser localement lorsqu'il y a des livres: 1 million, utilisateurs: 1 million, favs: 5 millions d'enregistrements dans les livres de la base de données de production: 10 000, utilisateurs: 10 000 , favs: fait référence à la création d'une table avec 50 000. Pour plus de commodité, nous appellerons respectivement les tables de sous-ensemble books_small, users_small et favs_small.

CREATE TABLE books_small SELECT * FROM books LIMIT 10000;
CREATE TABLE users_small SELECT * FROM users LIMIT 10000;
CREATE TABLE favs_small SELECT * FROM favs LIMIT 50000;

Cependant, cela seul ne réussit pas car favs_small a une contrainte de clé étrangère sur books_small et users_small. (Parce que favs_small peut contenir des utilisateurs qui ne sont pas inclus dans users_small mais sont inclus dans les utilisateurs)

De plus, dans cet exemple, la dépendance de la table est simple, donc si vous exécutez favs après book, users, il est clair que la dépendance a déjà été créée et cela n'a pas d'importance, mais la résolution de la dépendance entre les tables est de 100 ou plus. Ce n'est pas réaliste pour les humains de faire la table.

En d'autres termes, il y a les deux problèmes suivants, et nous allons créer une table de sous-ensemble pour répondre à ces deux problèmes. i. Je souhaite créer à partir de la dépendance de la clé étrangère au moment de la ** création de la table ** du sous-ensemble ii. Je souhaite conserver la contrainte de clé étrangère lorsque ** je place des données dans une table de sous-ensemble **

i. Je souhaite créer à partir de la dépendance de la clé étrangère lors de la création d'une table de sous-ensembles

Pour y parvenir, nous devons créer une table du côté dépendant. Définissons une fonction de get_table_list () qui retourne l'ordre dans lequel les tables doivent être créées.

from typing import List

import MySQLdb.cursors

global db
db = MySQLdb.connect()

def get_table_list() -> List[str]:
    """
Renvoyer le nom de la table dans l'ordre de création de la table et insérer en faisant attention à la dépendance des données
    """
    global db

    def _get_list_of_referring_tables(table_name) -> List[str]:
        """
        `show create table`Pour obtenir une liste de noms de table avec des clés étrangères
Traiter les dépendants de manière récursive pour voir les dépendants de la table dépendante
        """
        tables = [table_name]
        cur.execute(f'show create table {table_name}')
        statement = cur.fetchone()['Create Table']
        references = re.findall(r'REFERENCES `(\w+)`', statement)
        if references:
            for referring_table in references:
                tables = _get_list_of_referring_tables(referring_table) + tables  #Avant que ça ne dépende
        return tables

    # `show tables`La liste des tableaux obtenue par_Mettez-le dans la liste.(Je me fiche des dépendances)
    cur = db.cursor()
    cur.execute("show tables")
    rows = cur.fetchall()
    table_list = []
    for row in rows:
        table_list.append(row['Tables_in_[database]'])

    #Une liste de tables qui sont significatives dans l'ordre afin que les tables dépendantes viennent toujours en premier(Nom de table en double autorisé)
    table_list_order_by_referred = []  
    for table_name in table_list:
        table_list_order_by_referred += _get_list_of_referring_tables(table_name)

    # table_list_order_by_Supprimer les doublons car référencé contient des noms de table en double
    #En effaçant les doublons dans l'ordre depuis l'avant, les dépendants viennent en premier
    unique_table_list_order_by_referred = []
    for table_name in table_list_order_by_referred:
        if table_name not in unique_table_list_order_by_referred:
            unique_table_list_order_by_referred.append(table_name)
    return unique_table_list_order_by_referred

Maintenant dans l'ordre des tables obtenu par get_table_list ()

CREATE TABLE books_small SELECT * FROM books LIMIT 10000;

Les dépendances entre les tables sont résolues si vous faites quelque chose comme ça.

ii. Je souhaite conserver la contrainte de clé étrangère lorsque je place des données dans une table de sous-ensemble

Ensuite, comment résoudre les dépendances lors de la saisie de données. Je l'ai écrit plus tôt, mais sans penser à rien

CREATE TABLE books_small SELECT * FROM books LIMIT 10000;
CREATE TABLE users_small SELECT * FROM users LIMIT 10000;
CREATE TABLE favs_small SELECT * FROM favs LIMIT 50000;

Si vous le faites, vous vous fâcherez avec l'erreur «Impossible d'ajouter ou de mettre à jour une ligne enfant: une contrainte de clé étrangère échoue». Je veux que favs_small contienne uniquement les livres et les utilisateurs de books_small et users_small.

Je pense qu'il y a deux options ci-dessous.

CREATE TABLE favs_small 
SELECT * 
FROM favs 
WHERE book_id IN (SELECT id FROM books_small) 
AND user_id IN (SELECT id FROM users_small)
LIMIT 50000;
SET FOREIGN_KEY_CHECKS = 0
CREATE TABLE favs_small SELECT * FROM favs LIMIT 50000;
DELETE FROM favs_small WHERE book_id NOT IN (SELECT id FROM books_small);
DELETE FROM favs_small WHERE user_id NOT IN (SELECT id FROM users_small);
SET FOREIGN_KEY_CHECKS = 1

L'un ou l'autre est bien, mais j'ai senti que le coût d'assemblage de l'instruction SQL était inférieur dans ce dernier, j'ai donc choisi cette approche cette fois.

Au fait

DELETE FROM favs_small WHERE book_id NOT IN (SELECT id FROM books_small);

Au moins lors de l'exécution avec MySQL, cela prend beaucoup de temps car cela peut être une mauvaise construction du plan d'exécution de DELETE ... NOT IN ...

SELECT id FROM favs_small WHERE book_id NOT IN (SELECT id FROM books_small);
DELETE FROM favs_small WHERE id IN ([Liste des identifiants obtenus ci-dessus]);

Je suis heureux que vous le décomposiez en deux requêtes et que vous l'exécutiez.

Donc, si vous vous en rendez compte avec Python, le code ressemblera à ceci.


#Définissez la limite supérieure du nombre d'enregistrements dans chaque table comme ceci
TABLE_RECORD_LIMIT = {
  'users': 10000,
  'books': 10000,
  'favs': 50000,
}


def create_small_table():
    """
    [table_name]_Créez une table appelée small et placez-y les données à vider.
    """
    global db

    table_list = get_table_list()
    cur = db.cursor()
    for table_name in table_list:
        small_table_name = get_small_table_name(table_name)
        cur.execute(f'SHOW CREATE TABLE {table_name}')
        table_meta_data = cur.fetchone()['Create Table']
        # `table_name`Obtenez une liste des noms des tables qui
        references = re.findall(r'REFERENCES `(\w+)`', table_meta_data)

        limit_statement = ''
        if table_name in TABLE_RECORD_LIMIT:
            limit_statement = f'LIMIT {TABLE_RECORD_LIMIT[table_name]}'

        cur.execute('SET FOREIGN_KEY_CHECKS = 0')
        cur.execute(f'CREATE TABLE {small_table_name} SELECT * FROM {table_name} {limit_statement}')
        for parent_table in references:
            small_parent_table = get_small_table_name(parent_table)
            reference_column_name = get_reference_column_name(table_meta_data, parent_table)
            cur.execute(f"""
            SELECT id 
            FROM {small_table_name} 
            WHERE {reference_column_name} NOT IN (SELECT id FROM {small_parent_table})
            """)
            delete_id_list = ','.join([str(row['id']) for row in cur.fetchall()])
            if delete_id_list:
                cur.execute(f'DELETE FROM {small_table_name} WHERE id IN ({delete_id_list})')
        cur.execute('SET FOREIGN_KEY_CHECKS = 1')


def get_small_table_name(original_table_name):
    """
Vous pouvez l'implémenter comme vous le souhaitez
Notez que renvoyer quelque chose de plus long que le nom de table d'origine peut enfreindre la longueur maximale du nom de table.
    """
    return original_table_name + '_small'
    # return original_table_name[:-2] + '_s'  #Je l'ai implémenté ici


def get_reference_column_name(table_meta_data, referring_table_name):
    """
    `SHOW CREATE TABLE`Métadonnées de table obtenues dans(table_meta_data)De
Table référencée(referring_table_name)Obtenez le nom de la colonne pointant vers
    """
    return re.findall(r'\(`(\w+)`\) REFERENCES `' + referring_table_name, table_meta_data)[0]

En guise de mise en garde, la limite supérieure du nombre de favs pouvant être acquis est définie à 50000 au tout début, mais comme l'enregistrement de violation de contrainte est supprimé après avoir récupéré 50000, le nombre réel qui reste est de 50000 ou moins. .. Si vous voulez obtenir exactement 50 000 cas, vous pouvez le faire en prenant la première méthode parmi les deux options expliquées précédemment.

2. Créez un vidage de la table des sous-ensembles

Maintenant que vous avez une table de sous-ensemble avec une clé étrangère cohérente, tout ce que vous avez à faire est de vider la table sans réfléchir. Si vous voulez utiliser mysqldump, grep le résultat de show tables avec _small qui est un post-correctif des tables de sous-ensembles.

$ mysqldump -u user -p [database] books_small users_small favs_small hoge_small .... > hoge.dump

Tout ce que vous avez à faire est d'assembler une commande.

Si vous voulez écrire le dump par vous-même, faites de votre mieux, vous pouvez écrire comme ça. Je pense qu'il serait pratique de mettre en place le processus de masquage ici.

from functools import lru_cache

def create_small_db_dump():
    global db

    cur = db.cursor()
    table_list = get_table_list()
    BATCH_SIZE = 30000
    for table_name in table_list:
        small_table_name = get_small_table_name(table_name)
        offset = 0
        while True:
            cur.execute(f'SELECT * FROM {small_table_name} LIMIT {BATCH_SIZE} OFFSET {offset}')
            rows = cur.fetchall()
            if not rows:
                break
            create_insert_statement(table_name, rows)
            offset += batch_size


def create_insert_statement(table_name, rows):
    """
    :param table_name:Nom de la table à insérer
    :param rows:Sélectionnez une table*Tableau de résultats
    :return:
    """
    global output_file

    statement = f'INSERT INTO {table_name} VALUES '
    for i, row in enumerate(rows):
        value_list = row.values()
        tmp = '('
        for value in value_list:
            tmp += convert_to_str(table_name, i, value)
            tmp += ','
        tmp = tmp[:-1] + '),'
        statement += tmp
    statement = statement[:-1] + ';'
    output_file.write(f'{statement}\n\n')


#Comment masquer la Nième colonne de quelle table
#Si vous voulez faire quelque chose d'un peu compliqué, vous pouvez utiliser la fonction Lambda.
MASKING_TARGET = {
    'users': {2: '***'},
}
def convert_to_str(table_name, i, value):
    """
Échapper à un formulaire importable
Le traitement de masquage est également effectué ici
    """
    if table_name in MASKING_TARGET:
        if i in MASKING_TARGET[table_name]:
            return MASKING_TARGET[table_name][i]
    elif isinstance(value, str):
        escaped = value.replace("\\", "\\\\").replace("'", "\\'")
        return f"'{escaped}'"
    elif isinstance(value, int):
        return str(value)
    elif isinstance(value, float):
        return str(value)
    elif isinstance(value, datetime.datetime):
        return f"'{str(value)}'"
    elif isinstance(value, datetime.date):
        return f"'{str(value)}'"
    elif value is None:
        return 'null'
    #Ajoutez des modèles au besoin
    else:
        raise Exception(f'Value Error. data: {value}, data class: {value._class_}')

# create_small_db_dump()Au stade de l'appel de la table d'origine_Il y a un mélange de petites tables,
#Je veux uniquement les informations de la table d'origine
# _obtenir avant de faire une petite table_table_list()Il est bon d'avoir un cache en supposant que
# (Obtenez si c'est trop implicite et effrayant_table_list()Dans_Veuillez écrire le processus de lecture d'une petite table)
@lru_cache(maxsize=1)
def get_table_list() -> List[str]:
    #Le processus écrit ci-dessus

Après cela, vous devez supprimer la base de données existante ou créer une table, alors écrivons-la rapidement. Si vous pouvez poursuivre jusqu'à ce point, vous devriez être en mesure d'écrire immédiatement.

def build_drop_and_create_database_statement(database_name):
    global output_file

    output_file.write(f'DROP DATABASE IF EXISTS {database_name};\n')
    output_file.write(f'CREATE DATABASE IF NOT EXISTS {database_name};\n')
    output_file.write(f'USE {database_name};\n\n')


def build_create_table_statement():
    global db

    table_list = get_table_list()
    cur = db.cursor()
    for table_name in table_list:
        cur.execute(f'show create table {table_name}')
        statement = cur.fetchone()['Create Table']
        output_file.write(f'{statement};\n\n')

3. Importez le vidage localement

Si vous venez ici, apportez simplement le fichier de vidage localement et importez-le.

$ mysql -u user -p database < hoge.dump

Je vous remercie pour votre travail acharné.

Impressions

Au début, ce n'était pas une politique de créer une table de sous-ensemble, mais une politique pour maintenir la cohérence des données tout en générant des instructions INSERT directement à partir de la table d'origine, mais dump (long time) -> insert (long time) Au milieu du processus, une erreur telle que "l'intégrité de la clé étrangère!" Ou "Ces données n'ont pas été échappées!" Le cycle PDCA était trop long et inefficace, je suis donc passé à une stratégie consistant à créer d'abord un tableau cohérent de petits sous-ensembles de données et à le vider honnêtement.

Puisque nous avons affaire à des données de production, il y a beaucoup de choses à faire attention, mais j'espère que l'efficacité du développement s'améliorera en étant capable de vérifier le fonctionnement avec des données plus proches de la réalité dans l'environnement de développement.

Recommended Posts

[Environnement de développement] Comment créer un ensemble de données proche de la base de données de production
[Note] Comment créer un environnement de développement Ruby
[Note] Comment créer un environnement de développement Mac
Comment configurer l'environnement de développement d'ev3dev [version Windows]
Comment configurer un serveur de développement local
Comment exécuter une application Django sur un conteneur Docker (environnement de développement et de production)
Lire la source Python-Markdown: Comment créer un analyseur
Comment configurer un environnement Python à l'aide de pyenv
Comment créer un sous-menu avec le plug-in [Blender]
Comment définir l'environnement de développement pour chaque projet avec VSCode + extension Python + Miniconda
Créer un environnement Python et transférer des données vers le serveur
Comment créer un environnement de développement de la série Python2.7 avec Vagrant
Comment entrer dans l'environnement de développement Python avec Vagrant
Comment configurer WSL2 sur Windows 10 et créer un environnement d'étude pour les commandes Linux
Comment créer un package Conda
Comment créer un pont virtuel
Comment créer un Dockerfile (basique)
Comment obtenir uniquement les données nécessaires du groupe de données structurées à l'aide d'une méthode polyvalente
Comment créer un fichier de configuration
Comment créer une grande quantité de données de test dans MySQL? ??
Comment créer un clone depuis Github
Comment créer un dossier git clone
Comment créer un environnement NVIDIA Docker
Comment créer un référentiel à partir d'un média
Comment diviser et traiter une trame de données à l'aide de la fonction groupby
[Python] Qu'est-ce qu'un argument formel? Comment définir la valeur initiale
Comment créer un wrapper qui préserve la signature de la fonction à envelopper
Comment calculer la volatilité d'une marque
Comment créer des exemples de données CSV avec hypothèse
Comment partager un environnement virtuel [About requirements.txt]
Introduction à Python Préparons l'environnement de développement
Je veux créer un bel environnement de développement Python pour mon nouveau Mac
Comment créer un objet fonction à partir d'une chaîne
Comment définir la base de données de Django sur Mongodb Visual Studio 2019
Créer un ensemble de données en échantillonnant au hasard les données MNIST
Comment créer un fichier JSON en Python
[Pour l'enregistrement] Système d'image Keras Partie 1: Comment créer votre propre ensemble de données?
Comment créer des données à mettre dans CNN (Chainer)
Comment créer un ensemble de données d'image de visage utilisé dans l'apprentissage automatique (1: Acquérir des images de candidats à l'aide du service API Web)
Comment créer une boîte de saisie Kivy 1 ligne
Procédure de création d'application multi-plateforme avec kivy
Configurer un environnement de développement Python sur Marvericks
Installer et configurer Jupyter Notebook pour créer un environnement de création de notes d'étude [Mac]
Comment créer une API Rest dans Django
Créez une commande pour obtenir le journal de travail
[Python] Comment créer un environnement de serveur Web local avec SimpleHTTPServer et CGIHTTPServer
[Introduction à Python] Comment obtenir l'index des données avec l'instruction for
Comment préparer l'environnement pour Google Colab avec le cours avancé d'apprentissage automatique de Coursera
CodePicnic qui peut créer un environnement [Développement | Exécuter | Tutoriel | Démo] qui s'exécute sur le navigateur
Essayez de créer une table d'enregistrement de bataille avec matplotlib à partir des données de "Schedule-kun"
Comment créer rapidement un environnement d'apprentissage automatique à l'aide de Jupyter Notebook avec UbuntuServer 16.04 LTS
Comment mettre Pyenv sur Amazon Linux et Ubuntu pour créer un environnement Python 3.6.0
Comment utiliser Docker pour conteneuriser votre application et comment utiliser Docker Compose pour exécuter votre application dans un environnement de développement
[Il n'est pas trop tard pour apprendre Python à partir de 2020] Partie 2 Créons un environnement de développement Python
Créer une application de gestion de partition shogi à l'aide de Django 5 ~ Passer les données de la base de données au modèle ~
Comment créer un article à partir de la ligne de commande