Jak na optimalizaci výkonu v BigQuery | Mňamka #86

Podklady k tomuto článku už dlouho leží v redakci Bizztro mňamek. Jejich autorem je naše kolegyně Elena Jánošíková. Akorát stále nebyl čas, aby Elena přetavila podklady do uceleného materiálu a tak s laskavým svolením jsem se ujal této úlohy já.

1. Méně je vždycky více

Tohle je základní pravidlo, když chcete optimalizovat výkon na BigQuery - a nejen tam. Platí i pro Redshift nebo Snowflake. Dá se aplikovat celkem univerzálně. Např. lepší, když váš supergeniální 6x do sebe vnořený SELECT rozdělíte na několik menších a jednodušších.

Rychlost (a také cenu) každé query ovlivňuje základní věc: kolik dat musí daná query zpracovat. BigQuery vám tyto informace snadno sdělí, když jí požádáte o prováděcí plán užitím klíčového slova EXPLAIN na začátku vaší query. Ta se neprovede, ale BQ udělá analýzu a řekne vám, co je jak náročné.

2. Používejte interní úložiště

BigQuery, podobně jako jiné analytické databáze, umějí realizovat operace jak nad daty ve vlastním úložišti, tak v externí storage. Interní úložiště je sice dražší, ale z hlediska rychlosti přístupu násobně rychlejší.

3. Vyhýbejte se SELECT *

Dejte si práci s tím, vyjmenovat sloupce, které chcete vidět. Ano, když na začátku děláte exploraci jakéhokoli datasetu, tak něco jako “SELECT hvězdička” potřebujete. K tomu má BigQuery možnosti preview dat a to jak v konzoli, tak např. v command line utilitě “bq”. Někdy může pomoct SELECT * EXCEPT, kde vyjmenujete sloupce, které nechcete vidět.

POZOR, často se při exploraci používá klíčové slovo LIMIT, které omezuje velikost výstupu dané query. Použití LIMIT však neznamená, že BigQuery zpracuje méně dat. To omezíte jedině užitím filtrace WHERE nebo HAVING případně správným JOINem.

4. Materializujte jednotlivé mezivýstupy

To souvisí s prvním bodem. Vytvářejte tabulky s mezivýstupy užitím CREATE TABLE … AS SELECT … Tahle technika umožní, že jednotlivé kroky zpracování opravdu máte pod kontrolou, včetně toho, kolik dat vstupuje kam. Má to i druhý důsledek - mnohem jednodušeji se takové skripty ladí z hlediska správnosti.

BigQuery mj. sama dělá partitioning vašich dat, pokud tabulka obsahuje alespoň jeden datumový / časový sloupec. K tomu vytváří i systémový atribut _PARTITIONDATE, který můžete využít jako součást vaší WHERE klauzule.

5. Denormalizuj data

JOINy jsou prostě drahé. Všude, nejen v BigQuery. Je lepší data denormalizovat a např. hodnoty číselníkových atributů (středisko, odpovědný obchodník apod.) rovnou při vytváření tabulky najoinovat k dané tabulce. BigQuery umí i tzv. Neste Fields (STRUCT datový typ a Repeated Fields (ARRAY datový typ), které tuto úlohu ještě zjednodušují. Jejich zpracování je méně náročné, než použití JOINů.

6. Opatrně na wildcard tables

BigQuery umí v rámci jedné query přistupovat do více tabulek současně s využitím hvězdičkové konvence. Představte si, že máte (typicky v účetnictví) pro každý rok samostatnou tabulku s deníkem dokladů: DOKLADY2020, DOKLADY 2019, DOKLADY 2018. BigQuery umožňuje napsat SELECT … FROM DOKLADY*, což funguje tak, že nejprve se provede UNION jednotlivých tabulek a pak se nad nimi dělá vlastní projekce. Je třeba být tedy velmi opatrný, kam napíšete hvězdičku, protože tím. mj. určujete, kolik dat vstupuje do query. Takže DOKLADY20* je méně dat než DOKLADY*.

7. Pro opravdu velké datasety používej aproximované agregační funkce.

SELECT APPROX_COUNT_DISTINCT() je výrazně levnější než SELECT COUNT(DISTINCT …) BigQuery má řadu podobných funkcí, které dokážou poměrně přesně odhadnout výsledek dané agregace a nemusejí scanovat celou tabulku.

8. Řaď za sebe správně jednotlivé operace

To s sebou nese několik jednoduchých pravidel:

  • Filtruj nejdřív, co to jde, ať dál jdou jen data, které je třeba. A hlavně dřív, než budeš JOINovat.

  • ORDER BY používej až na konci, případně jako součást window funkcí.

  • Matematické, konverzní a jiné funkce, stejně tak jako regulární výrazy a funkce používej až úplně nakonec.

  • JOINy optimalizuj od největší tabulky po nejmenší. Nikdy ne naopak.

  • Vyhni se opakovaným JOINům a funkcím.

9. Vyhni se používání anti patternů

  • Nepoužívej WITH klauzuli. Data se nedají dopřed materializovat, taže se provádí vždy dokola. WITH je dobrá na čitelnost kódu ale platí se výkonem.
  • Namísto self-joinů používej window funkce
  • Vyhni se použití CROSS JOINů. Když to jinak nejde, aspoň data předtím zagreguj.
  • Vyhni se použití DML (INSERT / UPDATE), které aktualizují jediný řádek. Když potřebuješ transakční zpracování, použij jinou databázi, např. Cloud SQL.

Máte k článku nějaké otázky nebo připomínky? Klidně mi napište, rád to s Vámi proberu :-)

Radovan Jirka
datový detektiv
LinkedIn

Keboola a Kai PromtLab | Mňamka #524

Keboola a Kai PromtLab | Mňamka #524

Objavte PromptLab, sofistikované riešenie od Kebooly a Kai PromtLab na zlepšenie interakcií s umelou inteligenciou. V tomto článku sa dozviete, ako PromptLab využíva technológiu Streamlit na automatické prispôsobovanie výziev za účelom dosiahnutia lepšej jasnosti a presnosti vo vašich projektoch. Oboznámte sa s intuitívnym rozhraním, ktoré vám umožní porovnávať výsledky a optimalizovať pracovné postupy.

Základní pojmy v datovém modelování | Mňamka #457

Základní pojmy v datovém modelování | Mňamka #457

Co je to datový model? Jaký je rozdíl mezi konceptuálním a logickým modelem? A k čemu slouží proces tzv. normalizace? Bez datového modelování se dnes v BI obejdete už jen stěží, Kuba si o něm proto připravil krátkou minisérii, ve které si vše probereme od úplných základů. V prvním díle se seznámíme s nejdůležitějšími pojmy, které byste v této souvislosti měli znát, a na jednoduchém příkladu z oblasti sales si ukážeme, jak takový datový model vlastně vypadá. Tak pojďme na to!

MAQL II. - MAQL Reuse factů & Nesting metrik | Mňamka #454

MAQL II. - MAQL Reuse factů & Nesting metrik | Mňamka #454

Proč se vyplatí recyklovat metriky v MAQL? Máme tady pokračování naší krátké minisérie o dotazovacím jazyku MAQL od Péti. V minulém díle jsme si osvětlili základní rozdíl mezi SQL a MAQL a dnes se zaměříme na výhody metrik vytvořených pomocí MAQL a jejich recyklaci. Funguje to přitom podobně jako v případě klasické recyklace surovin. Pokud ji dělat nebudete, ušetříte si možná půl minutky práce, v budoucnu se vám to ale může velmi nepříjemně vrátit. Tak se na to pojďte podívat!