🥇PgGraph – utiliit tabelisõltuvuste arhiveerimiseks ja otsimiseks PostgreSQL-is

Täna tahan tutvustada Habri lugejatele Pythonis kirjutatud utiliiti PostgreSQL DBMS-i tabelisõltuvustega töötamiseks.

Utiliidi API on lihtne ja koosneb kolmest meetodist.

arhiiv_tabel - määratud primaarsete võtmetega ridade rekursiivne arhiveerimine/kustutamine
hanki_tabeli_viited — tabeli sõltuvuste otsimine (näitab tabeleid, millele on viidatud määratud ja mis sellele viitavad)
hanki_ridade_viited - otsige teistest tabelitest ridu, mis viitavad soovitud tabeli määratud ridadele

eelajalugu

Minu nimi on Oleg Borzov, olen Domkliki hüpoteeklaenuhaldurite CRM-i meeskonnas arendaja.

Meie CRM-süsteemi põhiandmebaas on mahult üks suurimaid ettevõttes. See on ka üks vanimaid: see ilmus kohe projekti käivitamisel, kui puud olid suured, Domklik oli startup ja moekas Pythoni asünkroonse raamistiku mikroteenuse asemel oli PHP-s tohutu monoliit.

Üleminek PHP-lt Pythonile oli väga pikk ja nõudis mõlema süsteemi samaaegset tuge, mis mõjutas andmebaasi disaini.

Selle tulemusel on meil andmebaas suure hulga tihedalt ühendatud ja tohutute tabelitega, millel on hunnik indekseid erinevat tüüpi päringute jaoks. Kõik see mõjutab negatiivselt andmebaasi jõudlust: suurte tabelite ja nendevaheliste suhete hunniku tõttu kasvab pidevalt päringute keerukus, mis on eriti oluline kõige koormatud tabelite puhul.

Andmebaasi koormuse vähendamiseks otsustasime kirjutada skripti, mis kannab vanad kirjed kõige mahukamatest ja koormatud tabelitest arhiveeritud (näiteks alates task в task_archive).

Selle ülesande teeb keeruliseks tabelitevaheliste suhete suur arv: lihtsalt liigutage ridu task в task_archive ei piisa, enne seda tuleb teha sama rekursiivselt kõigi viitajatega task tabelid.

Toon näitega demo andmebaas saidilt postgrespro.ru:

Oletame, et peame tabelist kirjed kustutama Flights. Postgres ei luba meil seda niisama teha: kõigepealt peame kustutama kirjed kõigist viitamistabelitest ja nii edasi rekursiivselt kuni tabeliteni, millele keegi ei viita.

Meie näites aadressil Flights viitab Ticket_flightsja tema peal - Boarding_passes.

Seetõttu peate selle kustutama järgmises järjekorras:

Saame ridade primaarvõtmete (PK) väärtused Ticket_flights, mis viitavad kustutatavatele ridadele Flights.
Saame PK read Boarding_passes, mis viitavad Ticket_flights.
Kustutame tabeli 2. sammust read PK kaupa Boarding_passes.
Kustutage read PK järgi 1. sammust Ticket_flights.
Joonte eemaldamine Flights.

Tulemuseks oli utiliit nimega PgGraph, mille otsustasime teha avatud lähtekoodiga.

Kuidas kasutada

Utiliit toetab kahte kasutusviisi:

Helista käsurealt (pggraph …).
Kasutamine Pythoni koodis (klass PgGraphApi).

Paigaldamine ja seadistamine

Kõigepealt peate installima utiliidi Pypi hoidlast:

pip3 install pggraph

Seejärel looge kohalikus masinas fail config.ini koos andmebaasi konfiguratsiooni ja arhiveerimisskriptiga:

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

Käivitage konsoolist

Parameetrid

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

Positsioonilised argumendid:

action - nõutav toiming: archive_table, get_table_references või get_rows_references.

Nimetatud argumendid:

--config_path — konfiguratsioonifaili tee;
--table — tabel, millega peate toimingu sooritama;
--ids — näiteks komadega eraldatud ID-de loend, 1,2,3 (valikuline parameeter);
--log_path — logide kausta tee (valikuline parameeter, vaikimisi kodukaust);
--log_level — logimise tase (valikuline parameeter, vaikimisi on INFO).

Käskude näited

Tabeli arhiveerimine

Utiliidi põhifunktsiooniks on andmete arhiveerimine, st. ridade ülekandmine põhitabelist arhiivitabelisse (näiteks tabelist raamatuid в raamatute_arhiiv).

Toetatud on ka kustutamine ilma arhiveerimiseta: selleks peate määrama parameetri saidil config.ini to_archive = vale).

Nõutavad parameetrid - config_path, tabel ja ID.

Pärast käivitamist kustutatakse kirjed rekursiivselt ids tabelis table ja kõigis tabelites, mis sellele viitavad.

$ 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

Määratud tabeli sõltuvuste leidmine

Funktsioon määratud tabeli sõltuvuste leidmiseks table. Nõutavad parameetrid - config_path и table.

Pärast käivitamist kuvatakse ekraanil sõnastik, kus:

in_refs - antud tabelite sõnastik, kus võti on tabeli nimi, väärtus on võõrvõtme objektide loend (pk_main - primaarvõti põhitabelis, pk_ref - primaarvõti viitetabelis, fk_ref — selle veeru nimi, mis on lähtetabeli võõrvõti);
out_refs — tabelite sõnastik, millele see viitab.

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

Määratud primaarvõtmega stringidele viidete otsimine

Funktsioon, et otsida ridu teistest tabelitest, mis viitavad ridadele võõrvõtme kaudu ids tabelid table. Nõutavad parameetrid - config_path, table и ids.

Pärast käivitamist kuvatakse ekraanil järgmise struktuuriga sõnastik:

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

Kõne näide:

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

Kasutamine koodis

Lisaks konsoolis käitamisele saab teeki kasutada Pythoni koodis. Allpool on toodud näited kõnedest interaktiivses iPython keskkonnas.

Tabeli arhiveerimine

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

Määratud tabeli sõltuvuste leidmine

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

Määratud primaarvõtmega stringidele viidete otsimine

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

Raamatukogu lähtekood on saadaval aadressil GitHub MIT-i litsentsi alusel, samuti hoidlas PyPI.

Hea meelega võtan vastu kommentaare, kohustusi ja ettepanekuid.

Püüan siin ja repositooriumis jõudumööda vastata küsimustele.

Allikas: www.habr.com

PgGraph on utiliit PostgreSQL-is tabelisõltuvuste arhiveerimiseks ja leidmiseks

eelajalugu

Kuidas kasutada

Paigaldamine ja seadistamine

Käivitage konsoolist

Parameetrid

Käskude näited

Tabeli arhiveerimine

Määratud tabeli sõltuvuste leidmine

Määratud primaarvõtmega stringidele viidete otsimine

Kasutamine koodis

Tabeli arhiveerimine

Määratud tabeli sõltuvuste leidmine

Määratud primaarvõtmega stringidele viidete otsimine

Lisa kommentaar Tühista vastus