🥇PgGraph - PostgreSQLде таблицадан көз карандылыктарды архивдөө жана издөө үчүн утилита

Бүгүн мен Habr окурмандарына PostgreSQL DBMSдеги таблицанын көз карандылыгы менен иштөө үчүн Python тилинде жазылган утилитаны тартуулагым келет.

Утилитанын API'и жөнөкөй жана үч ыкмадан турат:

архив_таблица - белгиленген Негизги ачкычтар менен рекурсивдүү архивдөө/жок кылуу саптары
таблицанын_маалыматтарын алуу — таблица үчүн көз карандылыктарды издөө (көрсөтүлгөн жана ага шилтеме берген таблицаларды көрсөтөт)
алуу_саптар_шилтемелер - керектүү таблицадагы көрсөтүлгөн саптарга шилтеме берген башка таблицалардагы саптарды издөө

prehistory

Менин атым Олег Борзов, мен Домкликте ипотекалык насыялоо боюнча менеджерлер үчүн CRM командасынын иштеп чыгуучусумун.

Биздин CRM тутумубуздун негизги маалымат базасы компаниянын көлөмү боюнча эң чоңдордун бири. Бул ошондой эле эң эскилердин бири: ал долбоордун эң башталышында пайда болгон, бак-дарактар чоң болуп турганда, Domklik стартап болгон жана модалуу Python асинхрондук алкагындагы микросервистин ордуна PHPде чоң монолит пайда болгон.

PHPден Pythonго өтүү өтө узакка созулган жана эки системанын бир убакта колдоосун талап кылган, бул маалымат базасынын дизайнына таасирин тийгизген.

Натыйжада, бизде ар кандай типтеги суроо-талаптар үчүн бир топ индекстери бар көп сандагы жогорку туташтырылган жана чоң таблицалардан турган маалымат базасы бар. Мунун баары маалымат базасынын иштешине терс таасирин тийгизет: чоң таблицалардан жана алардын ортосундагы байланыштардын тутумунан улам, суроо-талаптардын татаалдыгы тынымсыз өсүүдө, бул эң көп жүктөлгөн таблицалар үчүн өзгөчө маанилүү.

Маалыматтар базасына жүктөөнү азайтуу үчүн биз эски жазууларды эң көлөмдүү жана жүктөлгөн таблицалардан архивделгендерге (мисалы, task в task_archive).

Бул милдет таблицалардын ортосундагы байланыштардын көптүгү менен татаалданат: жөн гана саптарды жылдыруу task в task_archive жетиштүү эмес, ага чейин бардык шилтемелер менен рекурсивдүү түрдө ушундай кылышыңыз керек task столдор.

Мен бир мисал менен көрсөтөм demo базасы postgrespro.ru сайтынан:

Келгиле, биз таблицадан жазууларды жок кылышыбыз керек дейли Flights. Postgres бизге муну ушундай кылууга жол бербейт: биз адегенде бардык шилтеме таблицаларынан жазууларды жок кылышыбыз керек, жана башка эч ким шилтеме кылбаган таблицаларга чейин рекурсивдүү түрдө.

Биздин мисалда Flights билдирет Ticket_flights, жана ага - Boarding_passes.

Ошондуктан, сиз аны бул тартипте жок кылышыңыз керек:

Биз катарлардын негизги ачкычтарын (PK) алабыз Ticket_flights, алар жок кылынуучу саптарга тиешелүү Flights.
Биз PK катарларын алабыз Boarding_passes, дегенге кайрылат Ticket_flights.
Таблицадагы 2-кадамдан баштап PK боюнча саптарды өчүрөбүз Boarding_passes.
1-кадамдан баштап PK боюнча саптарды жок кылыңыз Ticket_flights.
сызыктарды алып салуу Flights.

Натыйжада PgGraph деп аталган утилита пайда болду, биз аны ачык булак кылууну чечтик.

Кантип пайдалануу керек

Утилита эки колдонуу режимин колдойт:

буйрук сабынан чалуу (pggraph …).
Python кодунда колдонуу (класс PgGraphApi).

Орнотуу жана тарам

Алгач сиз Pypi репозиторийинен утилитаны орнотуу керек:

pip3 install pggraph

Андан кийин маалымат базасынын конфигурациясы жана архивдөө скрипти менен локалдык машинада config.ini файлын түзүңүз:

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

Консолдон иштетүү

параметрлер

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

Позициялык аргументтер:

action - талап кылынган иш-аракет: archive_table, get_table_references же get_rows_references.

Аты аталган аргументтер:

--config_path — конфигурация файлына жол;
--table — бир иш-аракетти аткаруу керек болгон таблица;
--ids — үтүр менен бөлүнгөн id тизмеси, мисалы, 1,2,3 (кошумча параметр);
--log_path — журналдар үчүн папкага жол (кошумча параметр, демейки боюнча — үй папкасы);
--log_level — каттоо деңгээли (кошумча параметр, демейки INFO).

Буйрук мисалдары

Таблицаны архивдөө

Утилитанын негизги функциясы маалыматтарды архивдөө, б.а. негизги таблицадан архивдик таблицага саптарды өткөрүү (мисалы, таблицадан китептер в китептердин_архиви).

Архивдөөсүз жок кылуу да колдоого алынат: бул үчүн config.iniде параметрди коюу керек to_archive = жалган).

Керектүү параметрлер - config_path, таблица жана идентификаторлор.

Ишке киргизгенден кийин, жазуулар рекурсивдүү түрдө жок кылынат ids стол table жана ага тиешелүү бардык таблицаларда.

$ 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

Белгиленген таблица үчүн көз карандылыктарды табыңыз

Белгиленген таблицадан көз карандылыкты табуу функциясы table. Керектүү параметрлер - config_path и table.

Ишке киргизгенден кийин экранда сөздүк пайда болот, мында:

in_refs — берилгенге шилтеме берүүчү таблицалардын сөздүгү, мында ачкыч таблицанын аталышы, мааниси – Тышкы негизги объекттердин тизмеси (pk_main - негизги таблицадагы негизги ачкыч, pk_ref - шилтеме таблицасында негизги ачкыч, fk_ref — булак таблицасынын тышкы ачкычы болгон тилкенин аталышы);
out_refs - бул тиешелүү таблицалардын сөздүгү.

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

Белгиленген Негизги ачкыч менен саптарга шилтемелерди табуу

Тышкы ачкыч аркылуу саптарга кайрылган башка таблицалардагы саптарды издөө функциясы ids столдор table. Керектүү параметрлер - config_path, table и ids.

Ишке киргизгенден кийин экранда төмөнкү структурадагы сөздүк пайда болот:

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

Мисал чалуу:

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

Коддо колдонуу

Аны консолдо иштетүүдөн тышкары, китепкананы Python кодунда колдонсо болот. iPython интерактивдүү чөйрөсүндөгү чалуулардын мисалдары төмөндө көрсөтүлгөн.

Таблицаны архивдөө

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

Белгиленген таблица үчүн көз карандылыктарды табыңыз

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

Белгиленген Негизги ачкыч менен саптарга шилтемелерди табуу

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

Китепкананын булак коду төмөнкү даректе GitHub MIT лицензиясы боюнча, ошондой эле репозиторийде P&IP.

Мен комментарийлерге, милдеттенмелерге жана сунуштарга кубанычта болом.

Мен бул жерде жана репозиторийде мүмкүн болушунча суроолорго жооп берүүгө аракет кылам.

Source: www.habr.com

PgGraph - PostgreSQLде таблицадан көз карандылыктарды архивдөө жана табуу үчүн программа

prehistory

Кантип пайдалануу керек

Орнотуу жана тарам

Консолдон иштетүү

параметрлер

Буйрук мисалдары

Таблицаны архивдөө

Белгиленген таблица үчүн көз карандылыктарды табыңыз

Белгиленген Негизги ачкыч менен саптарга шилтемелерди табуу

Коддо колдонуу

Таблицаны архивдөө

Белгиленген таблица үчүн көз карандылыктарды табыңыз

Белгиленген Негизги ачкыч менен саптарга шилтемелерди табуу

Комментарий кошуу жокко жооп