Bugün Habr okuyucularına PostgreSQL DBMS'deki tablo bağımlılıklarıyla çalışmak için Python'da yazılmış bir yardımcı program sunmak istiyorum.
Yardımcı programın API'si basittir ve üç yöntemden oluşur:
- arşiv_tablosu - belirtilen Birincil Anahtarlarla satırları yinelemeli olarak arşivleme/silme
- get_table_references — bir tablonun bağımlılıklarını arayın (belirtilen tablonun referans verdiği tabloları ve ona referans veren tabloları gösterir)
- get_rows_references - istenen tabloda belirtilen satırlara referans veren diğer tablolardaki satırları arayın
tarih öncesi
Adım Oleg Borzov, Domklik'teki ipotek kredisi yöneticilerinin CRM ekibinde geliştiriciyim.
CRM sistemimizin ana veri tabanı, şirketteki hacim açısından en büyük veri tabanlarından biridir. Aynı zamanda en eskilerinden biri: projenin başlangıcında, ağaçlar büyükken ortaya çıktı, Domklik bir başlangıçtı ve modaya uygun Python asenkron çerçevesindeki bir mikro hizmet yerine PHP'de devasa bir monolit vardı.
PHP'den Python'a geçiş çok uzun sürdü ve her iki sistemin eş zamanlı desteklenmesini gerektiriyordu, bu da veritabanının tasarımını etkiledi.
Sonuç olarak, farklı sorgu türleri için çok sayıda indeks içeren, yüksek düzeyde bağlantılı ve devasa tablolardan oluşan bir veritabanımız var. Tüm bunlar veritabanının performansını olumsuz yönde etkiler: Büyük tablolar ve aralarındaki bir dizi ilişki nedeniyle sorguların karmaşıklığı sürekli artmaktadır ve bu, özellikle en çok yüklenen tablolar için kritik öneme sahiptir.
Veritabanındaki yükü azaltmak için, eski kayıtları en hacimli ve yüklü tablolardan arşivlenmiş tablolara aktaracak bir komut dosyası yazmaya karar verdik (örneğin, task
в task_archive
).
Bu görev, tablolar arasındaki çok sayıda ilişki nedeniyle karmaşık hale gelir: yalnızca satırları task
в task_archive
yeterli değil, ondan önce tüm bu referanslarla aynı şeyi yinelemeli olarak yapmanız gerekir. task
tablolar.
Bir örnekle göstereceğim
Diyelim ki bir tablodaki kayıtları silmemiz gerekiyor Flights
. Postgres bunu bu şekilde yapmamıza izin vermez: Öncelikle tüm referans tablolarından kayıtları silmemiz gerekir ve bu şekilde, hiç kimse tarafından referans verilmeyen tablolara kadar yinelemeli olarak devam etmeliyiz.
Bizim örneğimizde Flights
ifade eder Ticket_flights
ve onun üzerinde - Boarding_passes
.
Bu nedenle, bu sırayla silmeniz gerekir:
- Satırların birincil anahtar (PK) değerlerini alıyoruz
Ticket_flights
silinecek satırlara atıfta bulunanFlights
. - PK satırları alıyoruz
Boarding_passes
, atıfta bulunanTicket_flights
. - Tablodaki 2. adımdaki satırları PK'ye göre sileriz
Boarding_passes
. - 1. adımdan itibaren satırları PK'ye göre silin
Ticket_flights
. - Çizgileri kaldırmak
Flights
.
Sonuç olarak, açık kaynak yapmaya karar verdiğimiz PgGraph adında bir yardımcı program ortaya çıktı.
Nasıl kullanılır?
Yardımcı program iki kullanım modunu destekler:
- Komut satırından arayın (
pggraph …
). - Python kodunda kullanım (sınıf
PgGraphApi
).
Kurulum ve konfigürasyon
Öncelikle yardımcı programı Pypi deposundan yüklemeniz gerekir:
pip3 install pggraph
Ardından yerel makinede veritabanının yapılandırmasını ve arşivleme komut dosyasını içeren bir config.ini dosyası oluşturun:
[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'
Konsoldan çalıştır
Parametreler
$ 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)
Konumsal argümanlar:
action
- gerekli eylem:archive_table
,get_table_references
veyaget_rows_references
.
Adlandırılmış bağımsız değişkenler:
--config_path
— yapılandırma dosyasının yolu;--table
— bir eylem gerçekleştirmeniz gereken bir tablo;--ids
— virgülle ayrılmış kimlik listesi, örneğin,1,2,3
(isteğe bağlı parametre);--log_path
— günlükler klasörünün yolu (isteğe bağlı parametre, varsayılan olarak — ana klasör);--log_level
— kayıt düzeyi (isteğe bağlı parametre, varsayılan INFO'dur).
Komut örnekleri
Bir tablonun arşivlenmesi
Yardımcı programın ana işlevi veri arşivlemedir, yani. satırların ana tablodan arşiv tablosuna aktarılması (örneğin tablodan) kitaplar в kitaplar_arşiv).
Arşivlemeden silme de desteklenmektedir: bunun için config.ini dosyasındaki parametreyi ayarlamanız gerekir. to_archive = yanlış).
Gerekli parametreler - config_path, tablo ve kimlikler.
Başlatma sonrasında kayıtlar yinelemeli olarak silinecek ids
masada table
ve buna atıfta bulunan tüm tablolarda.
$ 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
Belirtilen tablo için bağımlılıkları bulun
Belirtilen tablonun bağımlılıklarını bulma işlevi table
. Gerekli parametreler - config_path
и table
.
Başlattıktan sonra ekranda bir sözlük görüntülenecektir; burada:
in_refs
— belirli bir tabloya referans veren tabloların sözlüğü; burada anahtar tablonun adıdır, değer ise Yabancı Anahtar nesnelerinin bir listesidir (pk_main
- ana tablodaki birincil anahtar,pk_ref
- referans tablosundaki birincil anahtar,fk_ref
— kaynak tablonun yabancı anahtarı olan sütunun adı);out_refs
— bunun atıfta bulunduğu tabloların sözlüğü.
$ 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')]}}
Belirtilen Birincil Anahtara sahip dizelere başvuruları bulma
Yabancı Anahtar aracılığıyla diğer tablolardaki satırlara başvuran satırları arama işlevi ids
tablolar table
. Gerekli parametreler - config_path
, table
и ids
.
Başlattıktan sonra ekranda aşağıdaki yapıya sahip bir sözlük görüntülenecektir:
{
pk_id_1: {
reffering_table_name_1: {
foreign_key_1: [
{row_pk_1: value, row_pk_2: value},
...
],
...
},
...
},
pk_id_2: {...},
...
}
Örnek çağrı:
$ 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'}]}}}
Kodda kullanım
Kütüphane, konsolda çalıştırmanın yanı sıra Python kodunda da kullanılabilir. iPython etkileşimli ortamındaki çağrı örnekleri aşağıda gösterilmektedir.
Bir tablonun arşivlenmesi
>>> 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
Belirtilen tablo için bağımlılıkları bulun
>>> 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')]}}
Belirtilen Birincil Anahtara sahip dizelere başvuruları bulma
>>> 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'}]}}}
Kütüphane kaynak kodu şu adreste mevcuttur:
Yorumlardan, taahhütlerden ve önerilerden memnuniyet duyacağım.
Soruları burada ve depoda elimden geldiğince cevaplamaya çalışacağım.
Kaynak: habr.com