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 :-)