🥇PgGraph - tól til að geyma og leita að töfluháðum í PostgreSQL

Í dag vil ég kynna Habr lesendum tól skrifað í Python til að vinna með töfluháðar í PostgreSQL DBMS.

API tólsins er einfalt og samanstendur af þremur aðferðum:

skjalasafn_tafla - endurtekið geymslu / eyða línum með tilgreindum aðallykla
fá_töflutilvísanir - leitaðu að ósjálfstæði fyrir töflu (birtir töflur sem tilgreindur vísar til og þá sem vísa til hennar)
fá_raðir_tilvísanir - leitaðu að línum í öðrum töflum sem vísa til tiltekinna raðir í viðkomandi töflu

Forsaga

Ég heiti Oleg Borzov, ég er þróunaraðili í CRM teyminu fyrir húsnæðislánastjóra í Domklik.

Aðalgagnagrunnur CRM kerfisins okkar er einn sá stærsti miðað við magn í fyrirtækinu. Það er líka eitt það elsta: það kom fram við upphaf verkefnisins, þegar trén voru stór, var Domklik gangsetning, og í stað örþjónustu á smart Python ósamstilltri ramma var risastór einlitur í PHP.

Umskiptin frá PHP yfir í Python voru mjög löng og krafðist samtímis stuðnings beggja kerfa, sem hafði áhrif á hönnun gagnagrunnsins.

Fyrir vikið höfum við gagnagrunn með miklum fjölda af mjög tengdum og risastórum töflum með fullt af vísitölum fyrir mismunandi gerðir fyrirspurna. Allt þetta hefur neikvæð áhrif á frammistöðu gagnagrunnsins: vegna stórra taflna og fjölda tengsla þeirra á milli eykst flókið fyrirspurna stöðugt, sem er sérstaklega mikilvægt fyrir töflurnar sem eru mest hlaðnar.

Til að draga úr álagi á gagnagrunninn ákváðum við að skrifa skriftu sem myndi flytja gamlar færslur úr umfangsmestu og hlaðnustu töflunum yfir í þær sem eru í geymslu (td frá kl. task в task_archive).

Þetta verkefni er flókið vegna mikils fjölda tengsla milli tafla: einfaldlega færðu línur frá task в task_archive er ekki nóg, áður en þú þarft að gera það sama afturkvæmt með öllum þeim sem vísa til task borðum.

Ég mun sýna það með dæmi kynningargagnagrunnur frá síðunni postgrespro.ru:

Segjum að við þurfum að eyða skrám úr töflu Flights. Postgres mun ekki leyfa okkur að gera þetta bara svona: við þurfum fyrst að eyða færslum úr öllum tilvísunartöflum og svo framvegis endurkvæmt niður í töflur sem enginn vísar til.

Í dæmi okkar kl Flights vísar Ticket_flights, og á hana - Boarding_passes.

Þess vegna þarftu að eyða því í þessari röð:

Við fáum aðallykla (PK) gildi raða inn Ticket_flights, sem vísa til raðanna sem á að eyða í Flights.
Við fáum PK raðir Boarding_passes, sem vísa til Ticket_flights.
Við eyðum línum eftir PK úr skrefi 2 í töflunni Boarding_passes.
Eyða línum eftir PK úr skrefi 1 í Ticket_flights.
Að fjarlægja línur úr Flights.

Niðurstaðan var tól sem heitir PgGraph, sem við ákváðum að gera opinn uppspretta.

Hvernig á að nota

Tækið styður tvo notkunarmáta:

Hringdu frá skipanalínunni (pggraph …).
Notkun í Python kóða (class PgGraphApi).

Uppsetning og stillingar

Fyrst þarftu að setja upp tólið frá Pypi geymslunni:

pip3 install pggraph

Búðu síðan til config.ini skrá á staðbundinni vél með stillingum gagnagrunnsins og skjalavistunarforskriftinni:

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

Keyra frá vélinni

Breytur

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

Afstöðurök:

action - nauðsynleg aðgerð: archive_table, get_table_references eða get_rows_references.

Nefnd rök:

--config_path — slóð að stillingarskránni;
--table - borð sem þú þarft að framkvæma aðgerð með;
--ids — lista yfir auðkenni aðskilin með kommum, til dæmis, 1,2,3 (valfrjáls færibreyta);
--log_path — slóð að möppunni fyrir logs (valfrjáls færibreyta, sjálfgefið — heimamöppu);
--log_level — skráningarstig (valfrjáls færibreyta, sjálfgefið er INFO).

Skipunardæmi

Geymsla töflu

Meginhlutverk veitunnar er gagnageymslu, þ.e. að flytja línur úr aðaltöflunni yfir í geymslutöfluna (til dæmis úr töflunni bækur в bækur_skjalasafn).

Eyðing án geymslu er einnig studd: til þess þarftu að stilla færibreytuna í config.ini to_archive = rangt).

Nauðsynlegar færibreytur - config_path, tafla og auðkenni.

Eftir ræsingu verður færslum eytt afturkvæmt ids í töflunni table og í öllum töflum sem vísa til þess.

$ 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

Finndu ósjálfstæði fyrir tiltekna töflu

Virkni til að finna ósjálfstæði tiltekinnar töflu table. Nauðsynlegar færibreytur - config_path и table.

Eftir ræsingu birtist orðabók á skjánum, þar sem:

in_refs — orðabók yfir töflur sem vísa til ákveðinnar, þar sem lykillinn er nafn töflunnar, gildið er listi yfir erlenda lykilhluti (pk_main - aðallykill í aðaltöflunni, pk_ref - aðallykill í tilvísunartöflunni, fk_ref — heiti dálksins sem er erlendur lykill upprunatöflunnar);
out_refs — orðabók með töflum sem þessi vísar til.

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

Að finna tilvísanir í strengi með tilgreindum aðallykil

Virkni til að leita að línum í öðrum töflum sem vísa í línur með erlendum lykli ids borðum table. Nauðsynlegar færibreytur - config_path, table и ids.

Eftir ræsingu birtist orðabók með eftirfarandi uppbyggingu á skjánum:

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

Dæmi um símtal:

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

Notkun í kóða

Auk þess að keyra það í stjórnborðinu er hægt að nota bókasafnið í Python kóða. Dæmi um símtöl í iPython gagnvirka umhverfinu eru sýnd hér að neðan.

Geymsla töflu

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

Finndu ósjálfstæði fyrir tiltekna töflu

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

Að finna tilvísanir í strengi með tilgreindum aðallykil

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

Frumkóði bókasafnsins er fáanlegur á GitHub undir MIT leyfi, sem og í geymslunni PyPI.

Ég mun vera ánægður með athugasemdir, skuldbindingar og tillögur.

Ég mun reyna að svara spurningum eftir bestu getu hér og í geymslunni.

Heimild: www.habr.com

PgGraph er tól til að geyma og finna töflufíkn í PostgreSQL

Forsaga

Hvernig á að nota

Uppsetning og stillingar

Keyra frá vélinni

Breytur

Skipunardæmi

Geymsla töflu

Finndu ósjálfstæði fyrir tiltekna töflu

Að finna tilvísanir í strengi með tilgreindum aðallykil

Notkun í kóða

Geymsla töflu

Finndu ósjálfstæði fyrir tiltekna töflu

Að finna tilvísanir í strengi með tilgreindum aðallykil

Bæta við athugasemd Hætta við svar