🥇PgGraph - PostgreSQL жүйесінде кесте тәуелділіктерін мұрағаттау және іздеуге арналған утилита

Бүгін мен Habr оқырмандарына PostgreSQL ДҚБЖ кестелік тәуелділіктермен жұмыс істеуге арналған Python тілінде жазылған қызметтік бағдарламаны ұсынғым келеді.

Утилитаның API интерфейсі қарапайым және үш әдістен тұрады:

мұрағаттық_кесте - көрсетілген Бастапқы кілттері бар жолдарды рекурсивті мұрағаттау/жою
кестенің_анықтамаларын алу — кестеге тәуелділіктерді іздеу (көрсетілген және оған сілтеме жасайтын кестелерді көрсетеді)
жолдар_анықтамаларын алу - керекті кестедегі көрсетілген жолдарға сілтеме жасайтын басқа кестелердегі жолдарды іздеу

тарихын

Менің атым Олег Борзов, мен Домкликте ипотекалық несиелеу менеджерлеріне арналған CRM командасының әзірлеушісімін.

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

РНР-ден Python-ға көшу өте ұзақ болды және екі жүйені де бір уақытта қолдауды қажет етті, бұл мәліметтер базасының дизайнына әсер етті.

Нәтижесінде бізде әртүрлі сұрау түрлеріне арналған индекстер жиынтығы бар жоғары байланысқан және үлкен кестелерден тұратын мәліметтер базасы бар. Мұның бәрі деректер қорының жұмысына теріс әсер етеді: үлкен кестелер мен олардың арасындағы байланыстардың жиынтығына байланысты сұраулардың күрделілігі үнемі артып отырады, бұл әсіресе ең жүктелген кестелер үшін өте маңызды.

Дерекқорға жүктемені азайту үшін біз ескі жазбаларды ең көлемді және жүктелген кестелерден мұрағатталғандарға (мысалы, task в task_archive).

Бұл тапсырма кестелер арасындағы қарым-қатынастардың көптігімен қиындайды: жай ғана жолдарды жылжытыңыз task в task_archive жеткіліксіз, бұған дейін барлық сілтемелермен бірдей рекурсивті орындау керек task кестелер.

Мен мысалмен көрсетемін 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 — үтірмен бөлінген идентификаторлар тізімі, мысалы, 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 лицензиясы бойынша, сондай-ақ репозиторийде ППИ.

Пікірлерге, міндеттемелерге және ұсыныстарға қуаныштымын.

Мен мұнда және репозиторийде мүмкіндігімше сұрақтарға жауап беруге тырысамын.

Ақпарат көзі: www.habr.com

PgGraph — PostgreSQL жүйесінде кестелік тәуелділіктерді мұрағаттау және табуға арналған утилита

тарихын

Қалай пайдалануға болады

Орнату және конфигурация

Консольден іске қосыңыз

параметрлері

Команда мысалдары

Кестені мұрағаттау

Көрсетілген кесте үшін тәуелділіктерді табыңыз

Көрсетілген Негізгі кілті бар жолдарға сілтемелерді табу

Кодта қолдану

Кестені мұрағаттау

Көрсетілген кесте үшін тәуелділіктерді табыңыз

Көрсетілген Негізгі кілті бар жолдарға сілтемелерді табу

пікір қалдыру Жауапты болдырмау