mufassall

Konspekt · Biznes jarayonlar

Ommaviy import

13 daq · O'rta · mufassall.dtwin.uz/uz/l/bulk-import/

← Darsni ochish

Muammo

Ulgurji savdo kompaniyasi tovarlar katalogini CSV fayl qilib yuklaydi: 200 000 qator. Uni so'rov ichida o'qish timeout'ga tushadi, xotirani yeydi va bitta yomon qator uchun hammasini tashlab yuboradi. Buning o'rniga fayl object storage'ga boradi, import job uni oqim bilan o'qiydi, har qatorni tekshirib xatolar hisobotini beradi, qayta ishga tushish va qayta yuklashga chidaydi, qisqa batch'larda yozadi, jarayonni ko'rsatadi va tenant limitlari ichida qoladi.

Nimani o'rganasiz

  • Nega katta fayl pre-signed URL bilan to'g'ridan object storage'ga boradi va uni web server emas, worker o'qiydi
  • Qator raqamli har qator tekshiruvi va yuklab olinadigan xatolar hisoboti: qisman muvaffaqiyat oddiy natija
  • Fayl xeshi bo'yicha takrorni ushlash, har N qatorda checkpoint va asosiy jadvaldagi lock'larni qisqa ushlaydigan batch upsert
  • UI so'rab turadigan jarayon holati va har tenant uchun fayl hajmi, qatorlar va ishlayotgan job'lar limiti
Yakuniy sahna
30%60%45%

Diagramma kod sifatida

Darsdagi yakuniy sxemani matn ko'rinishida oling va Jira, GitLab, GitHub, Notion yoki hujjatga qo'ying: Mermaid u yerlarda o'zi chiziladi. D2 va PlantUML (Confluence, IntelliJ) uchun alohida variantlar bor.

Mermaid: sxema

flowchart LR
  n_client(["Brauzer"])
  n_api["Ilova API"]
  n_db[("Katalog bazasi")]
  n_storage[("Object storage")]
  n_queue[["Job navbati"]]
  n_worker("Import worker")
  n_client --x n_api
  n_client --> n_storage
  n_api --> n_queue
  n_queue --> n_worker
  n_worker --> n_storage
  n_worker --> n_db
  n_api --> n_db

Mermaid: so'rovlar ketma-ketligi

sequenceDiagram
  participant n_client as Brauzer
  participant n_api as Ilova API
  participant n_storage as Object storage
  participant n_worker as Import worker
  participant n_queue as Job navbati
  participant n_db as Katalog bazasi
  Note over n_client,n_api: Web server yuklangan faylni so'rov ichida o'qiydi
  n_client->>n_api: POST /products/import HTTP/1.1
  n_api-->>n_client: HTTP/1.1 200 OK
  Note over n_client,n_api: 200 000 qator: timeout, xotira, bitta yomon qator
  n_client->>n_api: 14:02:10 POST /products/import 41.9 MB, 200 000 rows
  Note over n_client,n_storage: To'g'ridan object storage'ga yuklash
  n_client->>n_storage: PUT /t_19/imp_77.csv?expires=900#38;sig=*** HTTP/1.1
  n_storage-->>n_client: HTTP/1.1 200 OK
  Note over n_client,n_api: Import job va faylni oqim bilan o'qiydigan worker
  n_client->>n_api: POST /imports HTTP/1.1
  n_api-->>n_client: HTTP/1.1 202 Accepted
  Note over n_worker,n_storage: Har qatorni tekshirish, xatolarni yig'ish, yaxshi qatorlarni saqlash
  n_worker->>n_storage: t_19/imp_77.errors.csv
  Note over n_queue,n_worker: Qayta ishga tushish boshidan boshlaydi, ikkinchi yuklash yangi job ochadi
  n_queue->>n_worker: 15:10:04 deploy: worker restarted, imp_77 was at row 122 000
  Note over n_worker,n_db: Takror uchun fayl xeshi, har 1 000 qatorda checkpoint
  n_worker->>n_db: UPDATE import_jobs
  n_db-->>n_worker: imp_77
  Note over n_worker,n_db: Bitta ulkan yozuv katalogni qulflaydi
  n_worker->>n_db: 16:00:02 imp_81: UPDATE products FROM staging
  Note over n_worker,n_db: Tabiiy kalitli batch upsert
  n_worker->>n_db: INSERT INTO products (tenant_id, sku, name, price_minor)
  n_db-->>n_worker: INSERT 0 1000
  Note over n_client,n_api: UI so'rab turadigan jarayon holati
  n_client->>n_api: GET /imports/imp_77 HTTP/1.1
  n_api-->>n_client: HTTP/1.1 200 OK
  Note over n_client,n_api: Har tenant uchun limitlar
  n_client->>n_api: POST /imports HTTP/1.1
  n_api-->>n_client: HTTP/1.1 429 Too Many Requests

D2

direction: right

n_client: "Brauzer" {
  shape: rectangle
}
n_api: "Ilova API" {
  shape: rectangle
}
n_db: "Katalog bazasi" {
  shape: cylinder
}
n_storage: "Object storage" {
  shape: stored_data
}
n_queue: "Job navbati" {
  shape: queue
}
n_worker: "Import worker" {
  shape: rectangle
  style.multiple: true
}

n_client -> n_api {
  target-arrowhead.shape: cross
}
n_client -> n_storage
n_api -> n_queue
n_queue -> n_worker
n_worker -> n_storage
n_worker -> n_db
n_api -> n_db

PlantUML: so'rovlar ketma-ketligi

@startuml
participant "Brauzer" as n_client
participant "Ilova API" as n_api
database "Object storage" as n_storage
participant "Import worker" as n_worker
queue "Job navbati" as n_queue
database "Katalog bazasi" as n_db
note over n_client, n_api : Web server yuklangan faylni so'rov ichida o'qiydi
n_client -> n_api : POST /products/import HTTP/1.1
n_api --> n_client : HTTP/1.1 200 OK
note over n_client, n_api : 200 000 qator: timeout, xotira, bitta yomon qator
n_client -> n_api : 14:02:10 POST /products/import 41.9 MB, 200 000 rows
note over n_client, n_storage : To'g'ridan object storage'ga yuklash
n_client -> n_storage : PUT /t_19/imp_77.csv?expires=900&sig=*** HTTP/1.1
n_storage --> n_client : HTTP/1.1 200 OK
note over n_client, n_api : Import job va faylni oqim bilan o'qiydigan worker
n_client -> n_api : POST /imports HTTP/1.1
n_api --> n_client : HTTP/1.1 202 Accepted
note over n_worker, n_storage : Har qatorni tekshirish, xatolarni yig'ish, yaxshi qatorlarni saqlash
n_worker -> n_storage : t_19/imp_77.errors.csv
note over n_queue, n_worker : Qayta ishga tushish boshidan boshlaydi, ikkinchi yuklash yangi job ochadi
n_queue -> n_worker : [dublikat] 15:10:04 deploy: worker restarted, imp_77 was at row 122 000
note over n_worker, n_db : Takror uchun fayl xeshi, har 1 000 qatorda checkpoint
n_worker -> n_db : UPDATE import_jobs
n_db --> n_worker : imp_77
note over n_worker, n_db : Bitta ulkan yozuv katalogni qulflaydi
n_worker -> n_db : 16:00:02 imp_81: UPDATE products FROM staging
note over n_worker, n_db : Tabiiy kalitli batch upsert
n_worker -> n_db : INSERT INTO products (tenant_id, sku, name, price_minor)
n_db --> n_worker : INSERT 0 1000
note over n_client, n_api : UI so'rab turadigan jarayon holati
n_client -> n_api : GET /imports/imp_77 HTTP/1.1
n_api --> n_client : HTTP/1.1 200 OK
note over n_client, n_api : Har tenant uchun limitlar
n_client -> n_api : POST /imports HTTP/1.1
n_api --> n_client : HTTP/1.1 429 Too Many Requests
@enduml

PlantUML: komponentlar

@startuml
left to right direction
agent "Brauzer" as n_client
component "Ilova API" as n_api
database "Katalog bazasi" as n_db
storage "Object storage" as n_storage
queue "Job navbati" as n_queue
collections "Import worker" as n_worker
n_client -[#c62828]-> n_api
n_client --> n_storage
n_api --> n_queue
n_queue --> n_worker
n_worker --> n_storage
n_worker --> n_db
n_api --> n_db
@enduml

Xulosa

Ommaviy importning olti qoidasi.

1) Katta faylni hech qachon web so'rov ichida o'qimang: uni qisqa muddatli pre-signed URL bilan object storage'ga yuklang, worker uni oqim bilan o'qisin. 2) Har qatorni tekshiring, yaxshilarini saqlang va foydalanuvchiga qator raqamli xatolar hisobotini bering. 3) Yuklashlarni fayl xeshi bo'yicha takrordan tozalang va har batch bilan checkpoint saqlang, qayta ishga tushish davom etsin. 4) Taxminan 1 000 qatorlik batch'larda, tabiiy kalit bo'yicha upsert bilan, qisqa tranzaksiyalarda yozing. 5) Jarayonni job yozuvidan ko'rsating. 6) Har tenant uchun fayl hajmi, qatorlar va ishlayotgan job'larni cheklang.

Ko'rsatkichlar: boshida va oxirida

Ko'rsatkichBoshidaOxirida
Import vaqti3 s95 s
Import qilingan qatorlar100%99,8%
API xotirasi180 MB60 MB
Checkout lock kutishi0 s0 s

Qadamlar

  1. Holat Web server yuklangan faylni so'rov ichida o'qiydi

    Buxgalter eski tizimdan tovarlar katalogini CSV qilib yuklaydi.

  2. Muammo 200 000 qator: timeout, xotira, bitta yomon qator

    Endi fayl 41.9 MB, 200 000 qator.

  3. Yechim To'g'ridan object storage'ga yuklash

    Brauzer avval API'dan yuklash URL'ini so'raydi.

  4. Yechim Import job va faylni oqim bilan o'qiydigan worker

    Brauzer API'ga aytadi: shu faylni import qil.

  5. Yechim Har qatorni tekshirish, xatolarni yig'ish, yaxshi qatorlarni saqlash

    Worker har qatorni alohida tekshiradi: majburiy maydonlar, sonlar, o'lchov birliklari, fayl ichidagi takrorlar.

  6. Muammo Qayta ishga tushish boshidan boshlaydi, ikkinchi yuklash yangi job ochadi

    Deploy worker'ni 122 000-qatorda qayta ishga tushiradi.

  7. Yechim Takror uchun fayl xeshi, har 1 000 qatorda checkpoint

    Har 1 000 qatorda worker job yozuviga o'z o'rnini saqlaydi: oxirgi tugagan qator va hisoblagichlar.

  8. Muammo Bitta ulkan yozuv katalogni qulflaydi

    Qatorma-qator yozish 200 000 marta borib-kelish, taxminan 7 daqiqa.

  9. Yechim Tabiiy kalitli batch upsert

    Worker har so'rovda 1 000 qator yozadi: yangi tovarlarni qo'shadi, mavjudlarini yangilaydi, tenant va SKU bo'yicha moslab.

  10. Yechim UI so'rab turadigan jarayon holati

    Sahifa har 2 soniyada job'ni so'raydi va chiziq ko'rsatadi: tugagan qatorlar, shu paytgacha xatolar, qolgan vaqt.

  11. Yechim Har tenant uchun limitlar

    Bitta tenant bir vaqtda 2 GB lik fayllar bilan oltita import boshlaydi, qolgan hammaning job'lari ularning ortida kutadi.

  12. Xulosa Xulosa

    Ommaviy importning olti qoidasi.

Atamalar

Upsert
Bitta so'rovda qatorni qo'shish yoki shu kalitli qator bor bo'lsa uni yangilash (ON CONFLICT ... DO UPDATE). Qayta yurgizish zararsiz.
Checkpoint
Uzun ish har bir commit qilingan batch bilan saqlaydigan joy: qayta ishga tushganda boshidan emas, shu yerdan davom etadi.