Möchten Sie beim Erstellen eines Webdienstes Daten, die sich in der Produktionsumgebung in der Nähe der Datenbank befinden, auch in Ihrer Entwicklungsumgebung? Es wird sein. In Ihrer lokalen Umgebung haben Sie möglicherweise vergessen, einen Patch anzuwenden, um die Integrität der in der Produktionsdatenbank usw. ausgeführten Daten zu gewährleisten, sodass die vorliegenden Daten möglicherweise nicht in der erwarteten Form vorliegen. Das Vorbereiten von Saatgutdaten ist ebenfalls eine Möglichkeit, aber die Wartung von Saatgutdaten ist unerwartet kostspielig, und die Eigenschaften der Daten unterscheiden sich von den Produktionsdaten. Wenn Sie also versuchen, sie freizugeben, treten Leistungsprobleme auf. Dinge passieren auch.
Also habe ich versucht, die Daten teilweise aus der Produktionsdatenbank zu extrahieren und lokal zu verwenden, damit ich eine Zusammenfassung dessen schreibe, was ich mit diesem Skript gemacht habe. Das Skript ist in Python geschrieben. Es tut mir leid, wenn Sie Python nicht lesen können ...
** Als sehr wichtige Voraussetzung denke ich, dass es viele rechtlich gefährliche Dienste gibt, wenn Sie die Produktionsdaten lokal bringen, ohne etwas zu verarbeiten. Stellen Sie daher sicher, dass Sie eine angemessene Maskierungsverarbeitung durchführen. Die Maskierungsmethode hängt auch von den Eigenschaften der Daten ab und wird in diesem Artikel nicht im Detail erläutert. ** ** **
Hinweis) Obwohl es als Produktions-DB geschrieben ist, gehen Sie bitte an einen Ort, der den DB-Server, der in der Produktion ausgeführt wird, nicht beeinflusst. Stellen Sie sich vor, Sie arbeiten auf einem anderen Server, den Sie aus einem Snapshot wiederhergestellt haben. Wenn Sie dies in der Produktionsumgebung tun, machen Sie bitte einen Eintrag im [Adventskalender für diejenigen, die es in der Produktionsumgebung getan haben] im nächsten Jahr (https://qiita.com/advent-calendar/2019/yarakashi-production)!
Dies ist der Teil, über den ich am meisten nachdenke. Ich werde es mit einem Beispiel zum leichteren Verständnis erklären. Angenommen, Sie haben drei Tabellen, Bücher, Benutzer und Favoriten und einen Fremdschlüssel von Favoriten bis zu Büchern und Benutzern. Das Erstellen einer Tabelle, die eine Teilmenge ist, bedeutet Daten, die Sie lokal verwenden möchten, wenn Bücher vorhanden sind: 1 Million, Benutzer: 1 Million, Favoriten: 5 Millionen Datensätze in den DB-Produktionsbüchern: 10.000, Benutzer: 10.000 , favs: Bezieht sich auf das Erstellen einer Tabelle mit 50.000. Der Einfachheit halber werden wir die Teilmengen-Tabellen books_small, users_small und favs_small aufrufen.
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;
Dies allein ist jedoch nicht erfolgreich, da für favs_small eine Fremdschlüsseleinschränkung für books_small und users_small gilt. (Da favs_small möglicherweise Benutzer enthält, die nicht in users_small enthalten sind, aber in Benutzern enthalten sind)
Darüber hinaus ist in diesem Beispiel die Abhängigkeit der Tabelle einfach. Wenn Sie also Favoriten nach Buch, Benutzer, ausführen, ist klar, dass die Abhängigkeit bereits erstellt wurde und es keine Rolle spielt, aber die Auflösung der Abhängigkeit zwischen Tabellen beträgt 100 oder mehr. Es ist für Menschen nicht realistisch, an den Tisch zu gehen.
Mit anderen Worten, es gibt die folgenden zwei Probleme, und wir werden eine Teilmengen-Tabelle erstellen, um diese beiden Probleme zu lösen. Ich möchte aus der Abhängigkeit des Fremdschlüssels beim ** Tabellenerstellen ** der Teilmenge erstellen ii. Ich möchte die Fremdschlüsseleinschränkung beibehalten, wenn ** Daten in eine Teilmengen-Tabelle eingefügt werden **
Um dies zu erreichen, müssen wir eine Tabelle von der abhängigen Seite erstellen. Definieren wir eine Funktion von get_table_list (), die die Reihenfolge zurückgibt, in der die Tabellen erstellt werden sollen.
from typing import List
import MySQLdb.cursors
global db
db = MySQLdb.connect()
def get_table_list() -> List[str]:
"""
Geben Sie den Tabellennamen in der Reihenfolge "Tabelle erstellen" zurück und fügen Sie ihn ein, während Sie auf die Datenabhängigkeit achten
"""
global db
def _get_list_of_referring_tables(table_name) -> List[str]:
"""
`show create table`So erhalten Sie eine Liste mit Tabellennamen mit Fremdschlüsseln
Verarbeiten Sie Abhängige rekursiv, um die Abhängigen der abhängigen Tabelle anzuzeigen
"""
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 #Bevor es darauf ankommt
return tables
# `show tables`Die Tabellenliste erhalten von_Setzen Sie es in die Liste.(Abhängigkeiten sind mir egal)
cur = db.cursor()
cur.execute("show tables")
rows = cur.fetchall()
table_list = []
for row in rows:
table_list.append(row['Tables_in_[database]'])
#Eine Liste von Tabellen, die aussagekräftig sind, damit die abhängigen Tabellen immer an erster Stelle stehen(Doppelter Tabellenname erlaubt)
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_Entfernen Sie Duplikate, da verwiesen doppelte Tabellennamen enthält
#Durch Löschen der Duplikate in der Reihenfolge von vorne kommen die abhängigen zuerst
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
Jetzt in der von get_table_list () erhaltenen Tabellenreihenfolge
CREATE TABLE books_small SELECT * FROM books LIMIT 10000;
Abhängigkeiten zwischen Tabellen werden aufgelöst, wenn Sie so etwas tun.
Als Nächstes erfahren Sie, wie Sie Abhängigkeiten bei der Dateneingabe auflösen. Ich habe es früher geschrieben, aber ohne an irgendetwas zu denken
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;
Wenn Sie dies tun, werden Sie wütend auf den Fehler "Eine untergeordnete Zeile kann nicht hinzugefügt oder aktualisiert werden: Eine Fremdschlüsseleinschränkung schlägt fehl". Ich möchte, dass favs_small nur die Bücher und Benutzer in books_small und users_small enthält.
Ich denke, es gibt zwei Möglichkeiten unten.
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
Beides ist in Ordnung, aber ich hatte das Gefühl, dass die Kosten für das Zusammenstellen der SQL-Anweisung in letzterem niedriger waren, und habe mich diesmal für diesen Ansatz entschieden.
Apropos
DELETE FROM favs_small WHERE book_id NOT IN (SELECT id FROM books_small);
Zumindest bei der Ausführung mit MySQL nimmt dies viel Zeit in Anspruch, da der Ausführungsplan von "DELETE ... NOT IN ..." möglicherweise schlecht erstellt wurde
SELECT id FROM favs_small WHERE book_id NOT IN (SELECT id FROM books_small);
DELETE FROM favs_small WHERE id IN ([Liste der oben erhaltenen IDs]);
Ich bin froh, wenn Sie es in zwei Abfragen aufteilen und ausführen.
Wenn Sie es also mit Python realisieren, sieht der Code so aus.
#Definieren Sie die Obergrenze für die Anzahl der Datensätze in jeder Tabelle wie folgt
TABLE_RECORD_LIMIT = {
'users': 10000,
'books': 10000,
'favs': 50000,
}
def create_small_table():
"""
[table_name]_Erstellen Sie eine Tabelle mit dem Namen small und legen Sie die zu sichernden Daten darin ab.
"""
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`Holen Sie sich eine Liste der Namen der Tabellen, die
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):
"""
Sie können es nach Belieben implementieren
Beachten Sie, dass die Rückgabe von etwas längerem als dem ursprünglichen Tabellennamen die maximale Länge des Tabellennamens verletzen kann.
"""
return original_table_name + '_small'
# return original_table_name[:-2] + '_s' #Ich habe es hier implementiert
def get_reference_column_name(table_meta_data, referring_table_name):
"""
`SHOW CREATE TABLE`Tabellenmetadaten erhalten in(table_meta_data)Von
Referenzierte Tabelle(referring_table_name)Holen Sie sich den Spaltennamen, der auf zeigt
"""
return re.findall(r'\(`(\w+)`\) REFERENCES `' + referring_table_name, table_meta_data)[0]
Als Einschränkung wird die Obergrenze der Anzahl der Favoriten, die erfasst werden können, zu Beginn als 50000 definiert. Da jedoch der Datensatz, der gegen die Einschränkung verstößt, nach dem Abrufen von 50.000 gelöscht wird, beträgt die tatsächlich verbleibende Anzahl 50.000 oder weniger. .. Wenn Sie genau 50.000 Fälle erhalten möchten, können Sie dies tun, indem Sie die erste Methode aus den beiden zuvor erläuterten Optionen herausnehmen.
Nachdem Sie nun eine Teilmengen-Tabelle mit einem konsistenten Fremdschlüssel haben, müssen Sie die Tabelle nur noch ohne nachzudenken sichern. Wenn Sie "mysqldump" verwenden möchten, überprüfen Sie das Ergebnis von "show tables" mit "_small", einem Post-Fix für Teilmengen-Tabellen.
$ mysqldump -u user -p [database] books_small users_small favs_small hoge_small .... > hoge.dump
Sie müssen lediglich einen Befehl zusammenstellen.
Wenn Sie den Dump selbst schreiben möchten, geben Sie Ihr Bestes, Sie können so schreiben. Ich denke, es wäre bequem, hier den Maskierungsprozess durchzuführen.
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:Tabellenname zum Einfügen
:param rows:Tabelle auswählen*Ergebnisarray
: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')
#So maskieren Sie die N-te Spalte welcher Tabelle
#Wenn Sie etwas Kompliziertes tun möchten, können Sie die Lambda-Funktion verwenden.
MASKING_TARGET = {
'users': {2: '***'},
}
def convert_to_str(table_name, i, value):
"""
Escape zu einem importierbaren Formular
Auch hier erfolgt die Maskierungsverarbeitung
"""
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'
#Fügen Sie nach Bedarf Muster hinzu
else:
raise Exception(f'Value Error. data: {value}, data class: {value._class_}')
# create_small_db_dump()In der Phase des Aufrufs der ursprünglichen Tabelle_Es gibt eine Mischung aus kleinen Tischen,
#Ich möchte nur die Informationen aus der Originaltabelle
# _Holen Sie sich, bevor Sie einen kleinen Tisch machen_table_list()Es ist gut, einen Cache unter der Annahme zu haben, dass
# (Holen Sie sich, wenn es zu implizit und beängstigend ist_table_list()Im_Bitte schreiben Sie den Prozess des Spielens eines kleinen Tisches)
@lru_cache(maxsize=1)
def get_table_list() -> List[str]:
#Der oben beschriebene Prozess
Danach müssen Sie die vorhandene Datenbank löschen oder eine Tabelle erstellen. Schreiben Sie sie also schnell. Wenn Sie bis zu diesem Punkt nachverfolgen können, sollten Sie sofort schreiben können.
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')
Wenn Sie hierher kommen, bringen Sie die Dump-Datei einfach lokal und importieren Sie sie.
$ mysql -u user -p database < hoge.dump
Danke für deine harte Arbeit.
Zunächst war es keine Richtlinie zum Erstellen einer Teilmengen-Tabelle, sondern eine Richtlinie zum Aufrechterhalten der Datenkonsistenz beim Generieren von INSERT-Anweisungen direkt aus der Originaltabelle, sondern zum Speichern (lange Zeit) -> Einfügen (lange Zeit). In der Mitte des Prozesses ein Fehler wie "Fremdschlüsselintegrität!" Oder "Diese Daten wurden nicht maskiert!" Der PDCA-Zyklus war zu lang und ineffizient, daher wechselte ich zu einer Strategie, zunächst eine konsistente Tabelle mit kleinen Teilmengen von Daten zu erstellen und diese ehrlich zu speichern.
Da es sich um Produktionsdaten handelt, sind viele Dinge zu beachten, aber ich hoffe, dass sich die Entwicklungseffizienz verbessern wird, indem der Betrieb mit Daten überprüft werden kann, die in der Entwicklungsumgebung realitätsnaher sind.
Recommended Posts