🥇PgGraph - כלי עזר לארכיון וחיפוש תלות בטבלה ב-PostgreSQL

היום אני רוצה להציג בפני קוראי Habr כלי עזר שנכתב ב-Python לעבודה עם תלות בטבלה ב- PostgreSQL DBMS.

ה-API של כלי השירות פשוט ומורכב משלוש שיטות:

archive_table - ארכיון/מחיקת שורות רקורסיבית עם מפתחות ראשיים שצוינו
get_table_references - חפש תלות לטבלה (יציג טבלאות שאליהם מפנה הטבלה שצוין ואלו המפנים אליה)
get_rows_references - חפש שורות בטבלאות אחרות המתייחסות לשורות שצוינו בטבלה הרצויה

פרהיסטוריה

שמי אולג בורזוב, אני מפתח בצוות 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.
אנו מוחקים שורות לפי PK משלב 2 בטבלה Boarding_passes.
מחק שורות לפי PK משלב 1 ב 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 = false).

פרמטרים נדרשים - config_path, table ומזהים.

לאחר ההשקה, הרשומות יימחקו באופן רקורסיבי 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

פרהיסטוריה

כיצד להשתמש

התקנה ותצורה

הרץ מהמסוף

פרמטרים

דוגמאות לפקודות

ארכיון טבלה

מצא תלות עבור טבלה שצוינה

מציאת הפניות למחרוזות עם המפתח הראשי שצוין

שימוש בקוד

ארכיון טבלה

מצא תלות עבור טבלה שצוינה

מציאת הפניות למחרוזות עם המפתח הראשי שצוין

הוספת תגובה ביטול תגובה