Mae PgGraph yn gyfleustodau ar gyfer archifo a dod o hyd i ddibyniaethau bwrdd yn PostgreSQL

Heddiw, rwyf am gyflwyno cyfleustodau wedi'i ysgrifennu yn Python i ddarllenwyr Habr ar gyfer gweithio gyda dibyniaethau bwrdd yn DBMS PostgreSQL.

Mae API y cyfleustodau yn syml ac yn cynnwys tri dull:

• archif_tabl - archifo/dileu rhesi ailadroddus gydag Allweddi Cynradd penodedig
• cael_cyfeiriadau_bwrdd — chwilio am ddibyniaethau ar gyfer tabl (bydd yn dangos tablau y mae'r un penodedig yn cyfeirio atynt a'r rhai sy'n cyfeirio ato)
• get_rows_references - chwiliwch am resi mewn tablau eraill sy'n cyfeirio at resi penodedig yn y tabl a ddymunir

cynhanes

Fy enw i yw Oleg Borzov, rwy'n ddatblygwr yn y tîm CRM ar gyfer rheolwyr benthyca morgeisi yn Domklik.

Mae prif gronfa ddata ein system CRM yn un o'r rhai mwyaf o ran cyfaint yn y cwmni. Mae hefyd yn un o'r hynaf: ymddangosodd yn lansiad y prosiect, pan oedd y coed yn fawr, roedd Domklik yn fusnes cychwynnol, ac yn lle microwasanaeth ar fframwaith asyncronig Python ffasiynol roedd monolith enfawr yn PHP.

Roedd y trosglwyddiad o PHP i Python yn hir iawn ac roedd angen cefnogaeth ar yr un pryd i'r ddwy system, a effeithiodd ar ddyluniad y gronfa ddata.

O ganlyniad, mae gennym gronfa ddata gyda nifer fawr o dablau hynod gysylltiedig ac enfawr gyda chriw o fynegeion ar gyfer gwahanol fathau o ymholiadau. Mae hyn i gyd yn effeithio'n negyddol ar berfformiad y gronfa ddata: oherwydd tablau mawr a chriw o berthnasoedd rhyngddynt, mae cymhlethdod ymholiadau yn cynyddu'n gyson, sy'n arbennig o hanfodol ar gyfer y tablau mwyaf llwythog.

Er mwyn lleihau'r llwyth ar y gronfa ddata, fe benderfynon ni ysgrifennu sgript a fyddai'n trosglwyddo hen gofnodion o'r tablau mwyaf swmpus ac wedi'u llwytho i'r rhai sydd wedi'u harchifo (er enghraifft, o task в task_archive).

Cymhlethir y dasg hon gan y nifer fawr o berthnasoedd rhwng tablau: yn syml, symudwch resi o task в task_archive Nid yw'n ddigon, cyn bod angen i chi wneud yr un peth yn gyson gyda phawb sy'n cyfeirio task byrddau.

Byddaf yn arddangos gydag enghraifft cronfa ddata demo o'r safle postgrespro.ru:

Gadewch i ni ddweud bod angen i ni ddileu cofnodion o dabl Flights. Ni fydd Postgres yn caniatáu inni wneud hyn yn union fel hyn: yn gyntaf mae angen i ni ddileu cofnodion o bob tabl cyfeirio, ac yn y blaen yn rheolaidd i lawr i dablau nad oes unrhyw un yn cyfeirio atynt.

Yn ein hesiampl yn Flights yn cyfeirio Ticket_flights, ac arni - Boarding_passes.

Felly, mae angen i chi ei ddileu yn y drefn hon:

1. Rydyn ni'n cael gwerthoedd allweddi cynradd (PK) rhesi i mewn Ticket_flights, sy'n cyfeirio at y rhesi i'w dileu yn Flights.
2. Rydym yn cael rhesi PK Boarding_passes, sy'n cyfeirio at Ticket_flights.
3. Rydym yn dileu rhesi gan PK o gam 2 yn y tabl Boarding_passes.
4. Dileu llinellau gan PK o gam 1 i mewn Ticket_flights.
5. Tynnu llinellau o Flights.

Y canlyniad oedd cyfleustodau o'r enw PgGraph, y gwnaethom benderfynu ei wneud yn ffynhonnell agored.

Sut i ddefnyddio

Mae'r cyfleustodau'n cefnogi dau ddull defnydd:

• Galwad o'r llinell orchymyn (pggraph …).
• Defnydd mewn cod Python (dosbarth PgGraphApi).

Gosod a chyfluniad

Yn gyntaf mae angen i chi osod y cyfleustodau o ystorfa Pypi:

pip3 install pggraph

Yna creu ffeil config.ini ar y peiriant lleol gyda chyfluniad y gronfa ddata a'r sgript archifo:

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

Rhedeg o'r consol

Paramedrau

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

Dadleuon safle:

• action - gweithredu gofynnol: archive_table, get_table_references neu get_rows_references.

Dadleuon a enwyd:

• --config_path - llwybr i'r ffeil ffurfweddu;
• --table — bwrdd y mae angen i chi berfformio gweithred ag ef;
• --ids — rhestr o ddulliau adnabod wedi'u gwahanu gan atalnodau, er enghraifft, 1,2,3 (paramedr dewisol);
• --log_path - llwybr i'r ffolder ar gyfer logiau (paramedr dewisol, yn ddiofyn - ffolder cartref);
• --log_level — lefel logio (paramedr dewisol, INFO yw'r rhagosodiad).

Enghreifftiau gorchymyn

Archifo bwrdd

Prif swyddogaeth y cyfleustodau yw archifo data, h.y. trosglwyddo rhesi o'r prif dabl i'r tabl archif (er enghraifft, o'r tabl llyfrau в llyfrau_archif).

Cefnogir dileu heb archifo hefyd: ar gyfer hyn mae angen i chi osod y paramedr yn config.ini to_archive = ffug).

Paramedrau gofynnol - config_path, tabl ac ID.

Ar ôl eu lansio, bydd cofnodion yn cael eu dileu dro ar ôl tro ids yn y tabl table ac yn yr holl dablau a gyfeiria ato.

$ 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

Dod o hyd i ddibyniaethau ar gyfer tabl penodol

Swyddogaeth i ddod o hyd i ddibyniaethau tabl penodol table. Paramedrau gofynnol - config_path и table.

Ar ôl ei lansio, bydd geiriadur yn cael ei arddangos ar y sgrin, lle:

• in_refs — geiriadur o dablau sy'n cyfeirio at un penodol, lle mai'r allwedd yw enw'r tabl, y gwerth yw rhestr o wrthrychau Allwedd Tramor (pk_main - allwedd gynradd yn y prif dabl, pk_ref - allwedd gynradd yn y tabl cyfeirio, fk_ref — enw'r golofn sy'n allwedd dramor i'r tabl ffynhonnell);
• out_refs — geiriadur o dablau y cyfeiria yr un hwn ato.

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

Dod o hyd i gyfeiriadau at linynnau gyda'r Allwedd Sylfaenol benodedig

Swyddogaeth i chwilio am resi mewn tablau eraill sy'n cyfeirio at resi trwy Allwedd Dramor ids byrddau table. Paramedrau gofynnol - config_path, table и ids.

Ar ôl ei lansio, bydd geiriadur gyda'r strwythur canlynol yn cael ei arddangos ar y sgrin:

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

Galwad enghreifftiol:

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

Defnydd yn y cod

Yn ogystal â'i redeg yn y consol, gellir defnyddio'r llyfrgell mewn cod Python. Mae enghreifftiau o alwadau yn amgylchedd rhyngweithiol iPython i'w gweld isod.

Archifo bwrdd

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

Dod o hyd i ddibyniaethau ar gyfer tabl penodol

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

Dod o hyd i gyfeiriadau at linynnau gyda'r Allwedd Sylfaenol benodedig

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

Mae cod ffynhonnell y llyfrgell ar gael yn GitHub o dan drwydded MIT, yn ogystal ag yn y gadwrfa PyPI.

Byddaf yn falch o gael sylwadau, ymrwymiadau ac awgrymiadau.

Byddaf yn ceisio ateb cwestiynau hyd eithaf fy ngallu yma ac yn y gadwrfa.

Ffynhonnell: hab.com