🥇PgGraph - PostgreSQL дэх хүснэгтийн хамаарлыг архивлах, хайх хэрэгсэл

Өнөөдөр би Habr уншигчдад PostgreSQL DBMS дахь хүснэгтийн хамааралтай ажиллахад зориулсан Python хэл дээр бичигдсэн хэрэгслийг танилцуулахыг хүсч байна.

Хэрэгслийн API нь энгийн бөгөөд гурван аргаас бүрдэнэ.

архивын_хүснэгт - заасан Үндсэн түлхүүрүүдтэй рекурсив архивлах/устгах мөрүүд
Хүснэгтийн_лавлагааг авах — Хүснэгтийн хамаарлыг хайх (заасан болон түүнд хамаарах хүснэгтүүдийг харуулна)
мөрийн_лавлагааг авах - Хүссэн хүснэгтийн заасан мөрүүдийг иш татсан бусад хүснэгтийн мөрүүдийг хайх

Эрьт урьдын түүх

Намайг Олег Борзов гэдэг, би Домклик дахь моргейжийн зээлийн менежерүүдэд зориулсан CRM багийн хөгжүүлэгч юм.

Манай CRM системийн үндсэн мэдээллийн сан нь компанийн эзлэхүүний хувьд хамгийн том мэдээллийн сан юм. Энэ нь бас хамгийн эртний зүйлүүдийн нэг юм: энэ нь төслийн нээлтийн үеэр гарч ирсэн бөгөөд моднууд том байх үед Domklik нь стартап байсан бөгөөд загварлаг Python асинхрон хүрээ дээрх микро үйлчилгээний оронд PHP-д асар том цул байсан.

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 - таслалаар тусгаарлагдсан id жагсаалт, жишээлбэл, 1,2,3 (заавал биш параметр);
--log_path — бүртгэлийн хавтас руу очих зам (заавал биш параметр, анхдагчаар - гэрийн хавтас);
--log_level — бүртгэлийн түвшин (заавал биш параметр, анхдагч нь INFO).

Тушаалын жишээнүүд

Хүснэгтийг архивлаж байна

Хэрэгслийн гол үүрэг бол өгөгдлийг архивлах, i.e. үндсэн хүснэгтээс архивын хүснэгт рүү мөр шилжүүлэх (жишээлбэл, хүснэгтээс ном в номын_архив).

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

Шаардлагатай параметрүүд - config_path, хүснэгт болон ids.

Эхлүүлсний дараа бүртгэлүүд рекурсив байдлаар устах болно 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 лицензийн дагуу, түүнчлэн хадгалах газарт PyPI.

Сэтгэгдэл, амлалт, саналд би баяртай байх болно.

Би энд болон репозитороос асуултуудад чадах чинээгээрээ хариулахыг хичээх болно.

Эх сурвалж: www.habr.com

PgGraph нь PostgreSQL дэх хүснэгтийн хамаарлыг архивлах, олох хэрэгсэл юм

Эрьт урьдын түүх

Яаж хэрэглэх вэ

Суулгалт ба тохиргоо

Консолоос ажиллуулах

параметрүүд

Тушаалын жишээнүүд

Хүснэгтийг архивлаж байна

Тодорхой хүснэгтийн хамаарлыг олох

Заасан Үндсэн түлхүүр бүхий мөрүүдийн лавлагааг хайж байна

Код дахь хэрэглээ

Хүснэгтийг архивлаж байна

Тодорхой хүснэгтийн хамаарлыг олох

Заасан Үндсэн түлхүүр бүхий мөрүүдийн лавлагааг хайж байна

сэтгэгдэл нэмэх хариу цуцлах