Kako ubrzati izvještaj u Redshiftu kad ne smijete mijenjati ni upit ni šemu

Većina savjeta za spor upit počinje od samog upita: prepišite join, filtrirajte ranije, izbacite iz SELECT-a kolone koje niko ne čita. Ostali savjeti počinju od šeme: denormalizujte, podijelite tabelu, napravite tabelu sa sažecima i usmjerite izvještaj na nju.

Imao sam izvještaj kojem je trebalo više od petnaest minuta da se otvori, a nijedan od tih poteza nije dolazio u obzir. Sada se otvara za dvije desetinke sekunde, a sve što ga je ubrzalo promijenjeno je ispod nivoa upita i šeme.

Klijent isporučuje modne dodatke velikim američkim maloprodajnim lancima, a ti lanci podatke o vlastitoj prodaji gledaju u BI alatu: kako se njihovi proizvodi prodaju, gdje i kada. To je izvještavanje za kupce, a sam izvještaj dio je onoga što klijent prodaje.

Time su mi bile vezane obje ruke. Upiti pripadaju BI alatu, koji SQL generiše iz onoga na šta korisnik klikne. Nema fajla koji biste otvorili ni naredbe koju biste prepisali. Upite možete čitati, ali ih ne možete mijenjati.

Šema u praksi pripada kupcima, iako ne pravno, jer su na njoj napravljeni njihovi sačuvani izvještaji. Ako preimenujete kolonu, podijelite tabelu ili promijenite tip, svaki kupac sa sačuvanim izvještajem mora ga ponovo napraviti. Za proizvod čija je vrijednost to što kupci mogu otvoriti svoje brojke kad god hoće, to nije trošak migracije, nego razgovor koji podrška mora obaviti sa svakim kupcem.

Dakle, SQL koji stiže do baze morao je ostati identičan, kao i imena i struktura tabela i kolona na koje se odnosi. Sve ispod te linije smjelo se mijenjati, a ispostavilo se da je toga mnogo. „Ne možemo ništa mijenjati“ skoro nikad nije tačno kad se precizno zapiše šta se ne smije dirati.

Upit navodi tabele i kolone. Ne kaže ništa o tome na kojem se čvoru nalazi koji red, kojim su redoslijedom redovi zapisani na disku, da li je agregat izračunat unaprijed i koji engine odgovara na upit. Upit ostaje isti, ali se skoro sve u načinu na koji se na njega odgovara može promijeniti.

Podaci su dolazili iz MySQL-a, a teški izvještaji agregirali su podatke o više od 200 miliona proizvoda. To je posao za kolonsko skladište podataka, a pitanje je bilo koje izabrati.

Izabrao sam Redshift, i to ne na osnovu poređenja mogućnosti. Snowflake i BigQuery po mogućnostima nisu bili slabiji. Presudilo je to kuda klijent ide: sve servise je selio na AWS, a skladište u istom cloudu kao i ostatak sistema znači jednostavniji prenos, jedan AWS račun, jedan model pristupa i jedan tim koji ga održava.

Kad su kandidati ovako blizu po mogućnostima, razliku koju osjetite godinu kasnije rijetko vidite u tabeli mogućnosti. Vidite je u broju cloud računa, modela pristupa i timova koji su uključeni.

Ni migracije nije bilo u uobičajenom smislu. Postojeći jobovi izvoze podatke iz izvornog sistema u object storage, a moj sloj preuzima izvezene fajlove i učitava ih:

1COPY sales.product_sales2FROM 's3://example-exports/product_sales/'3IAM_ROLE 'arn:aws:iam::123456789012:role/warehouse-load'4FORMAT AS CSV5GZIP6IGNOREHEADER 1;

Jobove na izvornoj strani ostavio sam na miru, a to je bilo isto ograničenje na drugom mjestu: što se izvorna strana manje morala mijenjati, to se manje ljudi moralo složiti prije nego što išta postane brže.

Prvo što se još moglo mijenjati jeste fizički raspored podataka. U Redshiftu su to dvije odluke: distribucijski ključ, koji određuje na kojem se čvoru nalazi koji red, i sort ključ, koji određuje kojim se redoslijedom redovi čuvaju, pa se cijeli blokovi mogu preskočiti kad ih filter isključi.

Primamljivo je izabrati ih čitajući šemu: ovo je primarni ključ, ono strani ključ, ova kolona liči na datum. Tako nastaje skladište koje izgleda razumno, a samo je skupo.

Ključeve treba birati prema upitima koje sistem izvršava. Kad je ispred baze BI alat, to je lakše nego što zvuči, jer se generisani SQL po prirodi ponavlja. Istih nekoliko oblika upita vraća se iznova, a historija upita pokazuje koji koštaju najviše:

1-- what the BI tool actually sends, most expensive first2SELECT3  LEFT(query_text, 200)       AS query_start,4  COUNT(*)                    AS runs,5  AVG(elapsed_time) / 1000000 AS avg_seconds6FROM sys_query_history7WHERE user_id = (SELECT usesysid FROM pg_user WHERE usename = 'bi_reader')8  AND query_type = 'SELECT'9  AND start_time > DATEADD(day, -14, GETDATE())10GROUP BY 111ORDER BY COUNT(*) * AVG(elapsed_time) DESC12LIMIT 20;

Iz te liste vidite po kojim kolonama teški upiti rade join, a po kojim filtriraju.

Kolone po kojima se radi join kandidati su za distribucijski ključ. Kad su obje strane čestog joina distribuirane po istoj koloni, redovi koji se poklapaju već su na istom čvoru i join ne prebacuje podatke kroz klaster.

Kolone po kojima se filtrira kandidati su za sort ključ. U izvještajima za kupce to je obično ko je kupac i koji period gleda. Ako sortirate po njima, upit za prošli kvartal jednog lanca čita samo blokove u kojima su ti podaci, a ostale preskače.

Nijedna od ovih promjena ne dira šemu onako kako je vidi BI alat:

1ALTER TABLE sales.product_sales ALTER DISTKEY product_id;2ALTER TABLE sales.product_sales ALTER COMPOUND SORTKEY (retailer_id, sale_date);

Tabela, njene kolone i njihovi tipovi ostaju isti, pa svaki sačuvani izvještaj i dalje radi. Redovi su samo na boljem mjestu, a izbor tog mjesta nije koštao ništa osim vremena potrebnog da se pročita historija upita.

Raspored pomaže svakom upitu, ali sam po sebi ne pretvara petnaest minuta u dvije desetinke sekunde. Najteži izvještaji agregirali su cijelu historiju svaki put kad bi ih neko otvorio, pa je trebalo spriječiti da se isti posao ponavlja pri svakom otvaranju.

Zato sam najteže izvještaje prebacio na materijalizovane view-ove. Agregat se izračuna unaprijed i sačuva, a izvještaj čita gotov rezultat umjesto da ga ponovo računa. View-ovi se osvježavaju inkrementalno, pa osvježavanje obrađuje samo ono što se promijenilo. Neki su napravljeni kao zavisni view-ovi nad drugima, pa se zajednički međukoraci izračunaju samo jednom.

1CREATE MATERIALIZED VIEW reporting.mv_retailer_weekly2DISTKEY (retailer_id)3SORTKEY (retailer_id, week_start)4AS5SELECT6  s.retailer_id,7  DATE_TRUNC('week', s.sale_date) AS week_start,8  s.product_id,9  SUM(s.units)   AS units,10  SUM(s.revenue) AS revenue11FROM sales.product_sales s12GROUP BY 1, 2, 3;13 14-- after the daily load15REFRESH MATERIALIZED VIEW reporting.mv_retailer_weekly;

Osvježavanje se pokreće jednom dnevno, jer podaci stižu jednom dnevno. Češće osvježavanje samo bi ponovo izračunalo iste brojke.

Problem je u tome što SQL iz BI alata čita originalne tabele. Ako napravite materijalizovani view, a ništa ne upućuje na njega, dobili ste brz objekat koji nijedan upit ne čita.

Rješenje počiva na istoj ideji kao i ostatak posla: imena koja vide alat i kupci ostaju netaknuta, a mijenja se ono na šta upućuju. U Redshiftu postoje dva opšta načina da se to uradi bez izmjene ijednog upita. Engine može prepisati dolazni upit tako da čita iz materijalizovanog view-a koji može odgovoriti na njega. Druga mogućnost je da ime tabele koje alat koristi u upitu i samo bude view s potpuno istim kolonama, koji podatke uzima iz nečeg drugog. U oba slučaja kupac vidi iste tabele i kolone, ali iza njih je sada nešto drugo.

Kojim god putem krenete, provjerite da li radi. Pokrenite EXPLAIN nad SQL-om samog alata, kopiranim iz historije upita, a ne nad očišćenom verzijom koju ste sami napisali. Ako plan ne čita unaprijed izračunate podatke, view-ovi ne služe ničemu.

Najviše sam se pribojavao brzog izvještaja čije su brojke odstupile od izvora. To je gore od sporog izvještaja, jer niko ne primijeti, a kupac na osnovu njega donese odluku.

Računanje unaprijed stvara taj rizik. View je kopija, a kopija se s originalom može razići na više načina: osvježavanje padne, a to niko ne primijeti; kasno učitavanje stigne nakon što je osvježavanje već prošlo; definicija se promijeni na jednoj strani, a na drugoj ne.

Zato validaciona skripta dokazuje da se view i izvor i dalje slažu. Provjera je jednostavna: isti agregat izračuna se iz izvora i iz view-a, a zatim se izlista svako mjesto na kojem se razlikuju.

1WITH src AS (2  SELECT retailer_id, SUM(units) AS units, SUM(revenue) AS revenue3  FROM sales.product_sales4  GROUP BY 15),6mv AS (7  SELECT retailer_id, SUM(units) AS units, SUM(revenue) AS revenue8  FROM reporting.mv_retailer_weekly9  GROUP BY 110)11SELECT12  COALESCE(src.retailer_id, mv.retailer_id) AS retailer_id,13  src.units   AS source_units,   mv.units   AS view_units,14  src.revenue AS source_revenue, mv.revenue AS view_revenue15FROM src16FULL OUTER JOIN mv ON mv.retailer_id = src.retailer_id17WHERE src.retailer_id IS NULL18   OR mv.retailer_id IS NULL19   OR src.units   <> mv.units20   OR src.revenue <> mv.revenue;

Ako upit ne vrati nijedan red, slažu se. Svaki vraćeni red znači da izvještaju ne treba vjerovati dok ga neko ne pogleda. Pošto se provjera pokreće svaki dan, niko ne mora vjerovati na riječ da se view poklapa s izvorom.

Kad se izvještaj otvara za manje od sekunde, ljudi ga više ne traže pa čekaju, nego ga istražuju: mijenjaju filter, upoređuju periode i postavljaju sljedeće pitanje u istom razgovoru. To je važnije od same brojke, jer od izvještaja pravi drugi proizvod.

Dnevno osvježavanje bilo je ispravno za ove podatke, koji ionako stižu jednom dnevno. Ali čim neko zatraži svježije brojke, dizajn nema drugog odgovora osim ponovne izrade postupka osvježavanja.

Agregaciju bih napravio tako da se interval može promijeniti bez prepravljanja. Svježina podataka je odluka o proizvodu koja izgleda kao tehnička, a osoba koja će jednog dana tražiti drugi interval nije ona koja je izabrala prvi.