🥇PgGraph - utilita pro archivaci a vyhledávání závislostí tabulek v PostgreSQL

Dnes chci čtenářům Habr představit utilitu napsanou v Pythonu pro práci se závislostmi tabulek v PostgreSQL DBMS.

API nástroje je jednoduché a skládá se ze tří metod:

archivní_tabulka - rekurzivní archivace/mazání řádků se zadanými primárními klíči
get_table_references — hledat závislosti pro tabulku (zobrazí tabulky, na které zadaná tabulka odkazuje, a ty, které na ni odkazují)
get_rows_references - hledat řádky v jiných tabulkách, které odkazují na zadané řádky v požadované tabulce

pravěk

Jmenuji se Oleg Borzov, jsem vývojář v CRM týmu pro manažery hypotečních úvěrů v Domkliku.

Hlavní databáze našeho CRM systému je objemově jedna z největších ve společnosti. Je také jedním z nejstarších: objevil se při samotném spuštění projektu, kdy byly velké stromy, Domklik byl startup a místo mikroslužby na módním asynchronním frameworku Python byl v PHP obrovský monolit.

Přechod z PHP na Python byl velmi dlouhý a vyžadoval současnou podporu obou systémů, což ovlivnilo návrh databáze.

Výsledkem je databáze s velkým počtem vysoce propojených a obrovských tabulek s hromadou indexů pro různé typy dotazů. To vše negativně ovlivňuje výkon databáze: kvůli velkým tabulkám a hromadě vztahů mezi nimi se neustále zvyšuje složitost dotazů, což je kritické zejména u nejvíce zatížených tabulek.

Abychom snížili zátěž databáze, rozhodli jsme se napsat skript, který by přenesl staré záznamy z nejobjemnějších a načtených tabulek do archivovaných (např. task в task_archive).

Tento úkol je komplikován velkým počtem vztahů mezi tabulkami: jednoduše přesuňte řádky z task в task_archive nestačí, předtím musíte udělat totéž rekurzivně se všemi těmi odkazy task tabulky.

Ukážu na příkladu demo databáze ze stránky postgrespro.ru:

Řekněme, že potřebujeme odstranit záznamy z tabulky Flights. Postgres nám to nedovolí udělat jen tak: nejprve musíme smazat záznamy ze všech referenčních tabulek a tak dále rekurzivně dolů k tabulkám, na které nikdo neodkazuje.

V našem příkladu na Flights odkazuje Ticket_flightsa na ní - Boarding_passes.

Proto jej musíte odstranit v tomto pořadí:

Získáme hodnoty primárních klíčů (PK) řádků v Ticket_flights, které odkazují na řádky, které mají být smazány Flights.
Získáme řádky PK Boarding_passes, které odkazují na Ticket_flights.
Odstraníme řádky podle PK z kroku 2 v tabulce Boarding_passes.
Odstraňte řádky podle PK z kroku 1 Ticket_flights.
Odstranění řádků z Flights.

Výsledkem byla utilita s názvem PgGraph, kterou jsme se rozhodli vytvořit jako open source.

Jak používat

Nástroj podporuje dva režimy použití:

Volání z příkazového řádku (pggraph …).
Použití v kódu Pythonu (class PgGraphApi).

Instalace a konfigurace

Nejprve musíte nainstalovat nástroj z úložiště Pypi:

pip3 install pggraph

Poté na místním počítači vytvořte soubor config.ini s konfigurací databáze a archivačním skriptem:

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

Spustit z konzole

Parametry

$ 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á akce: archive_table, get_table_references nebo get_rows_references.

Pojmenované argumenty:

--config_path — cesta ke konfiguračnímu souboru;
--table — tabulka, se kterou musíte provést akci;
--ids — seznam id oddělených čárkami, např. 1,2,3 (volitelný parametr);
--log_path — cesta ke složce pro protokoly (volitelný parametr, ve výchozím nastavení — domovská složka);
--log_level — úroveň protokolování (volitelný parametr, výchozí je INFO).

Příklady příkazů

Archivace tabulky

Hlavní funkcí utility je archivace dat, tzn. přenos řádků z hlavní tabulky do archivní tabulky (například z tabulky knihy в archiv_knih).

Podporováno je také mazání bez archivace: k tomu je třeba nastavit parametr v config.ini to_archive = nepravda).

Požadované parametry - config_path, tabulka a id.

Po spuštění budou záznamy rekurzivně smazány ids v tabulce table a ve všech tabulkách, které na něj odkazují.

$ 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

Najděte závislosti pro zadanou tabulku

Funkce pro nalezení závislostí zadané tabulky table. Požadované parametry - config_path и table.

Po spuštění se na obrazovce zobrazí slovník, kde:

in_refs — slovník tabulek odkazujících na danou tabulku, kde klíč je název tabulky, hodnota je seznam objektů cizího klíče (pk_main - primární klíč v hlavní tabulce, pk_ref - primární klíč v referenční tabulce, fk_ref — název sloupce, který je cizím klíčem zdrojové tabulky);
out_refs — slovník tabulek, na který se 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')]}}

Hledání odkazů na řetězce se zadaným primárním klíčem

Funkce pro vyhledávání řádků v jiných tabulkách, které odkazují na řádky pomocí cizího klíče ids tabulky table. Požadované parametry - config_path, table и ids.

Po spuštění se na obrazovce zobrazí slovník s následující strukturou:

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

Pří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žití v kódu

Kromě spuštění v konzole lze knihovnu použít v kódu Pythonu. Příklady volání v interaktivním prostředí iPython jsou uvedeny níže.

Archivace tabulky

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

Najděte závislosti pro zadanou tabulku

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

Hledání odkazů na řetězce se zadaným primárním klíčem

>>> 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 knihovny je dostupný na GitHub pod licencí MIT a také v úložišti PyPI.

Budu rád za připomínky, závazky a návrhy.

Pokusím se odpovědět na otázky podle svých nejlepších schopností zde a v úložišti.

Zdroj: www.habr.com

PgGraph je nástroj pro archivaci a hledání závislostí tabulek v PostgreSQL

pravěk

Jak používat

Instalace a konfigurace

Spustit z konzole

Parametry

Příklady příkazů

Archivace tabulky

Najděte závislosti pro zadanou tabulku

Hledání odkazů na řetězce se zadaným primárním klíčem

Použití v kódu

Archivace tabulky

Najděte závislosti pro zadanou tabulku

Hledání odkazů na řetězce se zadaným primárním klíčem

Přidat komentář Zrušit odpověď na komentář