🥇PgGraph - utilita na archiváciu a vyhľadávanie závislostí tabuliek v PostgreSQL

Dnes chcem čitateľom Habr predstaviť utilitu napísanú v Pythone na prácu so závislosťami tabuliek v PostgreSQL DBMS.

Rozhranie API nástroja je jednoduché a pozostáva z troch metód:

archív_tabuľky - rekurzívna archivácia/vymazanie riadkov so špecifikovanými primárnymi kľúčmi
get_table_references — hľadať závislosti pre tabuľku (zobrazí tabuľky, na ktoré odkazuje zadaná, a tie, ktoré na ňu odkazujú)
get_rows_references - vyhľadajte riadky v iných tabuľkách, ktoré odkazujú na zadané riadky v požadovanej tabuľke

pravek

Volám sa Oleg Borzov, som vývojár v CRM tíme pre manažérov hypotekárnych úverov v Domkliku.

Hlavná databáza nášho CRM systému patrí objemovo k najväčším v spoločnosti. Je tiež jedným z najstarších: objavil sa pri samotnom spustení projektu, keď boli stromy veľké, Domklik bol startup a namiesto mikroslužby na módnom asynchrónnom frameworku Python bol v PHP obrovský monolit.

Prechod z PHP na Python bol veľmi dlhý a vyžadoval si súčasnú podporu oboch systémov, čo ovplyvnilo návrh databázy.

Výsledkom je, že máme databázu s veľkým počtom vysoko prepojených a obrovských tabuliek s množstvom indexov pre rôzne typy dopytov. To všetko negatívne ovplyvňuje výkon databázy: kvôli veľkým tabuľkám a množstvu vzťahov medzi nimi sa zložitosť dopytov neustále zvyšuje, čo je obzvlášť dôležité pre najviac zaťažené tabuľky.

Aby sme znížili zaťaženie databázy, rozhodli sme sa napísať skript, ktorý by preniesol staré záznamy z najobjemnejších a načítaných tabuliek do archivovaných (napr. z task в task_archive).

Táto úloha je komplikovaná veľkým počtom vzťahov medzi tabuľkami: jednoducho presuňte riadky z task в task_archive nestačí, predtým musíte urobiť to isté rekurzívne so všetkými tými odkazmi task tabuľky.

Ukážem na príklade demo databáza zo stránky postgrespro.ru:

Povedzme, že potrebujeme odstrániť záznamy z tabuľky Flights. Postgres nám to nedovolí urobiť len tak: najprv musíme vymazať záznamy zo všetkých referenčných tabuliek a tak ďalej rekurzívne až na tabuľky, na ktoré nikto neodkazuje.

V našom príklade na Flights odkazuje Ticket_flightsa na nej - Boarding_passes.

Preto ho musíte odstrániť v tomto poradí:

Získame hodnoty primárnych kľúčov (PK) riadkov Ticket_flights, ktoré odkazujú na riadky, ktoré sa majú vymazať Flights.
Získame riadky PK Boarding_passes, ktoré odkazujú na Ticket_flights.
Vymažeme riadky podľa PK z kroku 2 v tabuľke Boarding_passes.
Odstrániť riadky podľa PK z kroku 1 in Ticket_flights.
Odstraňovanie riadkov z Flights.

Výsledkom bola utilita s názvom PgGraph, ktorú sme sa rozhodli vytvoriť ako open source.

Ako používať

Nástroj podporuje dva režimy použitia:

Zavolajte z príkazového riadku (pggraph …).
Použitie v kóde Python (trieda PgGraphApi).

Inštalácia a konfigurácia

Najprv musíte nainštalovať pomôcku z úložiska Pypi:

pip3 install pggraph

Potom vytvorte súbor config.ini na lokálnom počítači s konfiguráciou databázy a archivačným skriptom:

[db]
host = localhost
port = 5432
user = postgres
password = postgres
dbname = postgres
schema = public ; Необязательный параметр, указано значение по умолчанию

[archive]  ; Данный раздел заполнять необязательно, ниже указаны значения по умолчанию
is_debug = false
chunk_size = 1000
max_depth = 20
to_archive = true
archive_suffix = 'archive'

Spustite z konzoly

Parametre

$ pggraph -h
usage: pggraph action [-h] --table TABLE [--ids IDS] [--config_path CONFIG_PATH]
positional arguments:
  action        required action: archive_table, get_table_references, get_rows_references

optional arguments:
  -h, --help                    show this help message and exit
  --table TABLE                 table name
  --ids IDS                     primary key ids, separated by comma, e.g. 1,2,3
  --config_path CONFIG_PATH     path to config.ini
  --log_path LOG_PATH           path to log dir
  --log_level LOG_LEVEL         log level (debug, info, error)

Pozičné argumenty:

action - požadovaná akcia: archive_table, get_table_references alebo get_rows_references.

Pomenované argumenty:

--config_path — cesta ku konfiguračnému súboru;
--table — tabuľka, s ktorou musíte vykonať akciu;
--ids — zoznam ID oddelených čiarkami, napr. 1,2,3 (voliteľný parameter);
--log_path — cesta k priečinku pre protokoly (voliteľný parameter, predvolene — domovský priečinok);
--log_level — úroveň protokolovania (voliteľný parameter, predvolená hodnota je INFO).

Príklady príkazov

Archivácia tabuľky

Hlavnou funkciou utility je archivácia dát, t.j. prenos riadkov z hlavnej tabuľky do archívnej tabuľky (napríklad z tabuľky knihy в books_archive).

Podporované je aj mazanie bez archivácie: na to je potrebné nastaviť parameter v config.ini do_archive = nepravda).

Požadované parametre - config_path, tabuľka a identifikátory.

Po spustení budú záznamy rekurzívne vymazané ids v tabulke table a vo všetkých tabuľkách, ktoré sa naň vzťahujú.

$ pggraph archive_table --config_path config.hw.local.ini --table flights --ids 1,2,3
2020-06-20 19:27:44 INFO: flights - START
2020-06-20 19:27:44 INFO: flights - start archive_recursive 3 rows (depth=0)
2020-06-20 19:27:44 INFO:       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:       ticket_flights - start archive_recursive 3 rows (depth=1)
2020-06-20 19:27:44 INFO:               START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - start archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:                       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - start archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:                       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - start archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:                       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - start archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:                       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:               END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:       ticket_flights - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO: flights - archive_by_ids 3 rows by id
2020-06-20 19:27:44 INFO: flights - END

Nájdite závislosti pre zadanú tabuľku

Funkcia na nájdenie závislostí zadanej tabuľky table. Požadované parametre - config_path и table.

Po spustení sa na obrazovke zobrazí slovník, kde:

in_refs — slovník tabuliek odkazujúcich na danú tabuľku, kde kľúčom je názov tabuľky, hodnota je zoznam objektov cudzieho kľúča (pk_main - primárny kľúč v hlavnej tabuľke, pk_ref - primárny kľúč v tabuľke odkazov, fk_ref — názov stĺpca, ktorý je cudzím kľúčom zdrojovej tabuľky);
out_refs — slovník tabuliek, na ktoré sa tento odkazuje.

$ pggraph get_table_references --config_path config.hw.local.ini --table flights
{'in_refs': {'ticket_flights': [ForeignKey(pk_main='flight_id', pk_ref='ticket_no, flight_id', fk_ref='flight_id')]},
 'out_refs': {'aircrafts': [ForeignKey(pk_main='aircraft_code', pk_ref='flight_id', fk_ref='aircraft_code')],
              'airports': [ForeignKey(pk_main='airport_code', pk_ref='flight_id', fk_ref='arrival_airport'),
                           ForeignKey(pk_main='airport_code', pk_ref='flight_id', fk_ref='departure_airport')]}}

Hľadanie odkazov na reťazce so zadaným primárnym kľúčom

Funkcia na vyhľadávanie riadkov v iných tabuľkách, ktoré odkazujú na riadky prostredníctvom cudzieho kľúča ids stoly table. Požadované parametre - config_path, table и ids.

Po spustení sa na obrazovke zobrazí slovník s nasledujúcou štruktúrou:

{
	pk_id_1: {
		reffering_table_name_1: {
			foreign_key_1: [
				{row_pk_1: value, row_pk_2: value},
				...
			], 
			...
		},
		...
	},
	pk_id_2: {...},
	...
}

Príklad hovoru:

$ pggraph get_rows_references --config_path config.hw.local.ini --table flights --ids 1,2,3
{1: {'ticket_flights': {'flight_id': [{'flight_id': 1,
                                       'ticket_no': '0005432816945'},
                                      {'flight_id': 1,
                                       'ticket_no': '0005432816941'}]}},
 2: {'ticket_flights': {'flight_id': [{'flight_id': 2,
                                       'ticket_no': '0005433101832'},
                                      {'flight_id': 2,
                                       'ticket_no': '0005433101864'},
                                      {'flight_id': 2,
                                       'ticket_no': '0005432919715'}]}},
 3: {'ticket_flights': {'flight_id': [{'flight_id': 3,
                                       'ticket_no': '0005432817560'},
                                      {'flight_id': 3,
                                       'ticket_no': '0005432817568'},
                                      {'flight_id': 3,
                                       'ticket_no': '0005432817559'}]}}}

Použitie v kóde

Okrem spustenia v konzole je možné knižnicu použiť v kóde Python. Príklady hovorov v interaktívnom prostredí iPython sú uvedené nižšie.

Archivácia tabuľky

>>> from pg_graph.main import setup_logging
>>> setup_logging(log_level='DEBUG')
>>> from pg_graph.api import PgGraphApi
>>> api = PgGraphApi('config.hw.local.ini')
>>> api.archive_table('flights', [4,5])
2020-06-20 23:12:08 INFO: flights - START
2020-06-20 23:12:08 INFO: flights - start archive_recursive 2 rows (depth=0)
2020-06-20 23:12:08 INFO: 	START ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 DEBUG: 	ticket_flights - ForeignKey(pk_main='flight_id', pk_ref='flight_id, ticket_no', fk_ref='flight_id')
2020-06-20 23:12:08 DEBUG: 	SQL('SELECT flight_id, ticket_no FROM bookings.ticket_flights WHERE (flight_id) IN (%s, %s)')
2020-06-20 23:12:08 INFO: 	ticket_flights - start archive_recursive 30 rows (depth=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 DEBUG: 		boarding_passes - ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 INFO: 		boarding_passes - archive_by_fk 30 rows by ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 DEBUG: 		SQL('CREATE TABLE IF NOT EXISTS bookings.boarding_passes_archive (LIKE bookings.boarding_passes)')
2020-06-20 23:12:08 DEBUG: 		DELETE FROM boarding_passes by FK flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 INFO: 		END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 rows by flight_id, ticket_no
2020-06-20 23:12:08 DEBUG: 	SQL('CREATE TABLE IF NOT EXISTS bookings.ticket_flights_archive (LIKE bookings.ticket_flights)')
2020-06-20 23:12:08 DEBUG: 	DELETE FROM ticket_flights by flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 rows
2020-06-20 23:12:08 INFO: 	ticket_flights - start archive_recursive 30 rows (depth=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 DEBUG: 		boarding_passes - ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 INFO: 		boarding_passes - archive_by_fk 30 rows by ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 DEBUG: 		SQL('CREATE TABLE IF NOT EXISTS bookings.boarding_passes_archive (LIKE bookings.boarding_passes)')
2020-06-20 23:12:08 DEBUG: 		DELETE FROM boarding_passes by FK flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 INFO: 		END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 rows by flight_id, ticket_no
2020-06-20 23:12:08 DEBUG: 	SQL('CREATE TABLE IF NOT EXISTS bookings.ticket_flights_archive (LIKE bookings.ticket_flights)')
2020-06-20 23:12:08 DEBUG: 	DELETE FROM ticket_flights by flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 rows
2020-06-20 23:12:08 INFO: 	ticket_flights - start archive_recursive 30 rows (depth=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 DEBUG: 		boarding_passes - ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 INFO: 		boarding_passes - archive_by_fk 30 rows by ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 DEBUG: 		SQL('CREATE TABLE IF NOT EXISTS bookings.boarding_passes_archive (LIKE bookings.boarding_passes)')
2020-06-20 23:12:08 DEBUG: 		DELETE FROM boarding_passes by FK flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 INFO: 		END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 rows by flight_id, ticket_no
2020-06-20 23:12:08 DEBUG: 	SQL('CREATE TABLE IF NOT EXISTS bookings.ticket_flights_archive (LIKE bookings.ticket_flights)')
2020-06-20 23:12:08 DEBUG: 	DELETE FROM ticket_flights by flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 rows
2020-06-20 23:12:08 INFO: 	ticket_flights - start archive_recursive 3 rows (depth=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 DEBUG: 		boarding_passes - ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 INFO: 		boarding_passes - archive_by_fk 3 rows by ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 DEBUG: 		SQL('CREATE TABLE IF NOT EXISTS bookings.boarding_passes_archive (LIKE bookings.boarding_passes)')
2020-06-20 23:12:08 DEBUG: 		DELETE FROM boarding_passes by FK flight_id, ticket_no - 3 rows
2020-06-20 23:12:08 INFO: 		END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 3 rows by flight_id, ticket_no
2020-06-20 23:12:08 DEBUG: 	SQL('CREATE TABLE IF NOT EXISTS bookings.ticket_flights_archive (LIKE bookings.ticket_flights)')
2020-06-20 23:12:08 DEBUG: 	DELETE FROM ticket_flights by flight_id, ticket_no - 3 rows
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 3 rows
2020-06-20 23:12:08 INFO: 	END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: flights - archive_by_ids 2 rows by flight_id
2020-06-20 23:12:09 DEBUG: SQL('CREATE TABLE IF NOT EXISTS bookings.flights_archive (LIKE bookings.flights)')
2020-06-20 23:12:09 DEBUG: DELETE FROM flights by flight_id - 2 rows
2020-06-20 23:12:09 DEBUG: INSERT INTO flights_archive - 2 rows
2020-06-20 23:12:09 INFO: flights - END

Nájdite závislosti pre zadanú tabuľku

>>> from pg_graph.api import PgGraphApi
>>> from pprint import pprint
>>> api = PgGraphApi('config.hw.local.ini')
>>> res = api.get_table_references('flights')
>>> pprint(res)
{'in_refs': {'ticket_flights': [ForeignKey(pk_main='flight_id', pk_ref='flight_id, ticket_no', fk_ref='flight_id')]},
 'out_refs': {'aircrafts': [ForeignKey(pk_main='aircraft_code', pk_ref='flight_id', fk_ref='aircraft_code')],
              'airports': [ForeignKey(pk_main='airport_code', pk_ref='flight_id', fk_ref='arrival_airport'),
                           ForeignKey(pk_main='airport_code', pk_ref='flight_id', fk_ref='departure_airport')]}}

Hľadanie odkazov na reťazce so zadaným primárnym kľúčom

>>> from pg_graph.api import PgGraphApi
>>> from pprint import pprint
>>> api = PgGraphApi('config.hw.local.ini')
>>> rows = api.get_rows_references('flights', [1,2,3])
>>> pprint(rows)
{1: {'ticket_flights': {'flight_id': [{'flight_id': 1,
                                       'ticket_no': '0005432816945'},
                                      {'flight_id': 1,
                                       'ticket_no': '0005432816941'}]}},
 2: {'ticket_flights': {'flight_id': [{'flight_id': 2,
                                       'ticket_no': '0005433101832'},
                                      {'flight_id': 2,
                                       'ticket_no': '0005433101864'},
                                      {'flight_id': 2,
                                       'ticket_no': '0005432919715'}]}},
 3: {'ticket_flights': {'flight_id': [{'flight_id': 3,
                                       'ticket_no': '0005432817560'},
                                      {'flight_id': 3,
                                       'ticket_no': '0005432817568'},
                                      {'flight_id': 3,
                                       'ticket_no': '0005432817559'}]}}}

Zdrojový kód knižnice je dostupný na GitHub pod licenciou MIT, ako aj v úložisku PyPI.

Budem rád za komentáre, záväzky a návrhy.

Pokúsim sa odpovedať na otázky podľa svojich najlepších schopností tu a v úložisku.

Zdroj: hab.com

PgGraph je nástroj na archiváciu a vyhľadávanie závislostí tabuliek v PostgreSQL

pravek

Ako používať

Inštalácia a konfigurácia

Spustite z konzoly

Parametre

Príklady príkazov

Archivácia tabuľky

Nájdite závislosti pre zadanú tabuľku

Hľadanie odkazov na reťazce so zadaným primárnym kľúčom

Použitie v kóde

Archivácia tabuľky

Nájdite závislosti pre zadanú tabuľku

Hľadanie odkazov na reťazce so zadaným primárnym kľúčom

Pridať komentár Zrušiť odpoveď na komentár