🥇PgGraph - utilīta arhivēšanai un tabulu atkarību meklēšanai PostgreSQL

Šodien es vēlos iepazīstināt Habr lasītājus ar Python rakstītu utilītu darbam ar tabulu atkarībām PostgreSQL DBVS.

Lietderības API ir vienkārša un sastāv no trim metodēm:

arhīvs_tabula - rekursīva rindu arhivēšana/dzēšana ar norādītajām primārajām atslēgām
get_table_references — meklēt tabulas atkarības (rādīs tabulas, uz kurām atsaucas norādītā, un tās, kas atsaucas uz to)
get_rows_references - meklēt rindas citās tabulās, kas atsaucas uz norādītajām rindām vēlamajā tabulā

Aizvēsture

Mani sauc Oļegs Borzovs, es esmu izstrādātājs CRM komandā hipotekārās kreditēšanas menedžeriem Domklikā.

Mūsu CRM sistēmas galvenā datubāze apjoma ziņā ir viena no lielākajām uzņēmumā. Tas ir arī viens no vecākajiem: tas parādījās pašā projekta uzsākšanas brīdī, kad koki bija lieli, Domklik bija starta uzņēmums, un modernā Python asinhronā ietvara mikropakalpojuma vietā PHP bija milzīgs monolīts.

Pāreja no PHP uz Python bija ļoti ilga un prasīja vienlaicīgu abu sistēmu atbalstu, kas ietekmēja datu bāzes dizainu.

Rezultātā mums ir datubāze ar lielu skaitu ļoti saistītu un milzīgu tabulu ar virkni indeksu dažāda veida vaicājumiem. Tas viss negatīvi ietekmē datu bāzes veiktspēju: lielu tabulu un daudzu attiecību dēļ starp tām nepārtraukti palielinās vaicājumu sarežģītība, kas ir īpaši svarīgi visvairāk ielādētām tabulām.

Lai samazinātu datu bāzes slodzi, nolēmām uzrakstīt skriptu, kas vecos ierakstus no apjomīgākajām un ielādētākajām tabulām pārsūtītu uz arhivētajām (piemēram, no plkst. task в task_archive).

Šo uzdevumu sarežģī lielais attiecību skaits starp tabulām: vienkārši pārvietojiet rindas no task в task_archive nav pietiekami, pirms tam jums ir jādara tas pats rekursīvi ar visiem tiem, kas atsaucas task tabulas.

Es parādīšu ar piemēru demo datu bāze no vietnes postgrespro.ru:

Pieņemsim, ka mums ir jāizdzēš ieraksti no tabulas Flights. Postgres neļaus mums to darīt tāpat vien: mums vispirms ir jāizdzēš ieraksti no visām atsauces tabulām un tā tālāk rekursīvi līdz tabulām, uz kurām neviens neatsaucas.

Mūsu piemērā plkst Flights atsaucas Ticket_flights, un uz viņas - Boarding_passes.

Tāpēc tas ir jāizdzēš šādā secībā:

Mēs iegūstam rindu primāro atslēgu (PK) vērtības Ticket_flights, kas attiecas uz dzēšamajām rindām Flights.
Mēs iegūstam PK rindas Boarding_passes, kas attiecas uz Ticket_flights.
Mēs dzēšam rindas pēc PK no tabulas 2. darbības Boarding_passes.
Dzēst rindas pēc PK no 1. darbības Ticket_flights.
Līniju noņemšana no Flights.

Rezultāts bija utilīta ar nosaukumu PgGraph, kuru mēs nolēmām izveidot atvērtā koda formātā.

Kā lietot

Lietderība atbalsta divus lietošanas veidus:

Zvanīt no komandrindas (pggraph …).
Lietošana Python kodā (klase PgGraphApi).

Uzstādīšana un konfigurēšana

Vispirms jums jāinstalē utilīta no Pypi krātuves:

pip3 install pggraph

Pēc tam vietējā datorā izveidojiet failu config.ini ar datu bāzes konfigurāciju un arhivēšanas skriptu:

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

Palaist no konsoles

Parametrus

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

Pozīcijas argumenti:

action - Nepieciešamā darbība: archive_table, get_table_references vai get_rows_references.

Nosauktie argumenti:

--config_path — ceļš uz konfigurācijas failu;
--table — tabula, ar kuru jāveic darbība;
--ids — ID saraksts, atdalīts ar komatiem, piemēram, 1,2,3 (izvēles parametrs);
--log_path — ceļš uz žurnālu mapi (izvēles parametrs, pēc noklusējuma — mājas mape);
--log_level — reģistrēšanas līmenis (izvēles parametrs, noklusējuma vērtība ir INFO).

Komandu piemēri

Tabulas arhivēšana

Lietderības galvenā funkcija ir datu arhivēšana, t.i. rindu pārsūtīšana no galvenās tabulas uz arhīva tabulu (piemēram, no tabulas grāmatas в grāmatas_arhīvs).

Tiek atbalstīta arī dzēšana bez arhivēšanas: šim parametram ir jāiestata parametrs failā config.ini to_archive = false).

Nepieciešamie parametri - config_path, tabula un ID.

Pēc palaišanas ieraksti tiks rekursīvi dzēsti ids tabulā table un visās tabulās, kas uz to attiecas.

$ 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

Atrodiet noteiktas tabulas atkarības

Funkcija noteiktas tabulas atkarību atrašanai table. Nepieciešamie parametri - config_path и table.

Pēc palaišanas ekrānā tiks parādīta vārdnīca, kurā:

in_refs — tabulu vārdnīca, kas atsaucas uz doto tabulu, kur atslēga ir tabulas nosaukums, vērtība ir svešās atslēgas objektu saraksts (pk_main - primārā atslēga galvenajā tabulā, pk_ref - primārā atslēga atsauces tabulā, fk_ref — tās kolonnas nosaukums, kas ir avota tabulas ārējā atslēga);
out_refs — tabulu vārdnīca, uz kuru šī atsaucas.

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

Atsauču atrašana uz virknēm ar norādīto primāro atslēgu

Funkcija, lai meklētu rindas citās tabulās, kas attiecas uz rindām, izmantojot ārējo atslēgu ids tabulas table. Nepieciešamie parametri - config_path, table и ids.

Pēc palaišanas ekrānā tiks parādīta vārdnīca ar šādu struktūru:

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

Zvana piemērs:

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

Lietošana kodā

Papildus tās palaišanai konsolē bibliotēku var izmantot Python kodā. Tālāk ir parādīti zvanu piemēri interaktīvajā iPython vidē.

Tabulas arhivēšana

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

Atrodiet noteiktas tabulas atkarības

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

Atsauču atrašana uz virknēm ar norādīto primāro atslēgu

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

Bibliotēkas pirmkods ir pieejams vietnē GitHub saskaņā ar MIT licenci, kā arī repozitorijā PyPI.

Priecāšos par komentāriem, saistībām un ierosinājumiem.

Es centīšos atbildēt uz jautājumiem pēc manām iespējām šeit un repozitorijā.

Avots: www.habr.com

PgGraph ir utilīta arhivēšanai un tabulu atkarību atrašanai programmā PostgreSQL

Aizvēsture

Kā lietot

Uzstādīšana un konfigurēšana

Palaist no konsoles

Parametrus

Komandu piemēri

Tabulas arhivēšana

Atrodiet noteiktas tabulas atkarības

Atsauču atrašana uz virknēm ar norādīto primāro atslēgu

Lietošana kodā

Tabulas arhivēšana

Atrodiet noteiktas tabulas atkarības

Atsauču atrašana uz virknēm ar norādīto primāro atslēgu

Pievieno komentāru Atcelt atbildi