[PYTHON] [Entwicklungsumgebung] So erstellen Sie einen Datensatz in der Nähe der Produktionsdatenbank

Überblick

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. ** ** **

Gesamtdurchfluss

  1. Erstellen Sie eine Tabelle, die eine Teilmenge der Produktions-DB-Tabelle ist
  2. Erstellen Sie einen Speicherauszug der Teilmengen-Tabelle
  3. Importieren Sie den Speicherauszug lokal

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)!

1. Erstellen Sie eine Tabelle, die eine Teilmenge der Produktions-DB-Tabelle ist

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 **

Ich möchte aus der Abhängigkeit des Fremdschlüssels erstellen, wenn ich eine Tabelle mit Teilmengen erstelle

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.

ii. Ich möchte die Fremdschlüsseleinschränkung beibehalten, wenn Daten in eine Teilmengen-Tabelle eingefügt werden

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.

2. Erstellen Sie einen Speicherauszug der Teilmengen-Tabelle

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')

3. Importieren Sie den Speicherauszug lokal

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.

Impressionen

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

[Entwicklungsumgebung] So erstellen Sie einen Datensatz in der Nähe der Produktionsdatenbank
[Hinweis] So erstellen Sie eine Ruby-Entwicklungsumgebung
[Hinweis] So erstellen Sie eine Mac-Entwicklungsumgebung
So richten Sie die Entwicklungsumgebung von ev3dev ein [Windows-Version]
So richten Sie einen lokalen Entwicklungsserver ein
So führen Sie eine Django-Anwendung auf einem Docker-Container aus (Entwicklungs- und Produktionsumgebung)
Lesen Sie die Python-Markdown-Quelle: So erstellen Sie einen Parser
So richten Sie eine Python-Umgebung mit pyenv ein
So erstellen Sie ein Untermenü mit dem Plug-In [Blender]
So legen Sie die Entwicklungsumgebung für jedes Projekt mit VSCode + Python-Erweiterung + Miniconda fest
Erstellen Sie eine Python-Umgebung und übertragen Sie Daten auf den Server
Erstellen einer Entwicklungsumgebung für die Python2.7-Serie mit Vagrant
So gelangen Sie mit Vagrant in die Python-Entwicklungsumgebung
So richten Sie WSL2 unter Windows 10 ein und erstellen eine Lernumgebung für Linux-Befehle
So erstellen Sie ein Conda-Paket
So erstellen Sie eine virtuelle Brücke
Wie erstelle ich eine Docker-Datei?
So erhalten Sie mit einer vielseitigen Methode nur die erforderlichen Daten aus der strukturierten Datengruppe
So erstellen Sie eine Konfigurationsdatei
Wie erstelle ich eine große Menge an Testdaten in MySQL? ??
So erstellen Sie einen Klon aus Github
So erstellen Sie einen Git-Klonordner
So erstellen Sie eine NVIDIA Docker-Umgebung
So erstellen Sie ein Repository aus Medien
Teilen und Verarbeiten eines Datenrahmens mithilfe der Groupby-Funktion
[Python] Was ist ein formales Argument? So stellen Sie den Anfangswert ein
So erstellen Sie einen Wrapper, der die Signatur der zu umschließenden Funktion beibehält
So berechnen Sie die Volatilität einer Marke
Erstellen von CSV-Beispieldaten mit Hypothese
Freigeben einer virtuellen Umgebung [Informationen zu den Anforderungen.txt]
Einführung in Python Bereiten wir die Entwicklungsumgebung vor
Ich möchte eine schöne Python-Entwicklungsumgebung für meinen neuen Mac erstellen
So erstellen Sie ein Funktionsobjekt aus einer Zeichenfolge
So stellen Sie Djangos DB auf mongodb visual studio 2019 ein
Erstellen Sie einen Datensatz, indem Sie zufällig MNIST-Daten auswählen
So erstellen Sie eine JSON-Datei in Python
[Zur Aufnahme] Keras-Bildsystem Teil 1: Wie erstelle ich einen eigenen Datensatz?
So erstellen Sie Daten für CNN (Chainer)
Erstellen eines Gesichtsbilddatensatzes für maschinelles Lernen (1: Erfassen von Kandidatenbildern mithilfe des Web-API-Dienstes)
So erstellen Sie ein 1-zeiliges Kivy-Eingabefeld
Verfahren zur Erstellung plattformübergreifender Apps mit kivy
Richten Sie eine Python-Entwicklungsumgebung auf Marvericks ein
Installieren Sie Jupyter Notebook und stellen Sie es so ein, dass eine Umgebung zum Erstellen von Studiennotizen erstellt wird [Mac]
So erstellen Sie eine Rest-API in Django
Erstellen Sie einen Befehl, um das Arbeitsprotokoll abzurufen
[Python] So erstellen Sie eine lokale Webserverumgebung mit SimpleHTTPServer und CGIHTTPServer
[Einführung in Python] So erhalten Sie den Datenindex mit der for-Anweisung
So bereiten Sie die Umgebung mit Courseras fortgeschrittenem Kurs für maschinelles Lernen auf Google Colab vor
CodePicnic, das eine Umgebung [Entwicklung | Ausführen | Tutorial | Demo] erstellen kann, die im Browser ausgeführt wird
Versuchen Sie, mit matplotlib aus den Daten von "Schedule-kun" eine Kampfaufzeichnungstabelle zu erstellen.
So erstellen Sie schnell eine maschinelle Lernumgebung mit Jupyter Notebook mit UbuntuServer 16.04 LTS
So stellen Sie pyenv unter Amazon Linux und Ubuntu ein, um eine Python 3.6.0-Umgebung zu erstellen
Verwendung von Docker zum Containerisieren Ihrer Anwendung und Verwenden von Docker Compose zum Ausführen Ihrer Anwendung in einer Entwicklungsumgebung
[Es ist noch nicht zu spät, Python ab 2020 zu lernen] Teil 2 Erstellen wir eine Python-Entwicklungsumgebung
Erstellen Sie eine Shogi-Score-Management-Anwendung mit Django 5 ~ DB-Daten an Vorlage übergeben ~
So erstellen Sie einen Artikel über die Befehlszeile