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. ** **
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)!
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 **
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.
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.
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')
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é.
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