Nástroj BigQuery pro zpracování velkých datasetů je poslední dobou celosvětově čím dál tím populárnější. Není divu – díky propojení s Google Cloud Platform totiž nabízí efektivní cloudové řešení, které může co do rychlosti a možností škálování směle konkurovat celé řadě tradičních (relační databáze typu Oracle) i novějších (NoSQL, Hadoop/MapReduce) technologií.
BigQuery je totiž vlastně tou samou mohutnou platformou, kterou Google vyvinul interně pod názvem Dremel pro vlastní služby (včetně svého vyhledávače). Jak se zmiňuje Petr Šimeček ve svém článku, BigQuery je zřejmě také nejdostupnějším nástrojem, kterým si obyčejný smrtelník může jednoduše sáhnout na skutečná BIG data.
Pro organizace využívající Google Analytics Premium je navíc připravená automatická integrace obou nástrojů a díky tomu je možné se dostat i k těm nejpodrobnějším datům až na úroveň jednotlivých hitů. Navíc lze data hned využívat, jelikož hned po integraci obou nástrojů získáte bezplatně spolu s měsíčním 500$ kreditem v BigQuery celých 13 měsíců vašich historických nesamplovaných webových dat.
Naučit se s tímto nástrojem není pro uživatele se základní znalostí SQL žádný problém. S BigQuery se můžete rychle a snadno zaškolit například v online tréninku na vzdělávacím webu CBT Nuggets. V tomto článku se proto podíváme na několik základních tipů a doporučení pro efektivnější práci s BigQuery:
- Regulární výrazy
V rámci SQL syntaxe BigQuery je možné jednoduše používat regulární výrazy, podobně jako je tomu v Google Analytics. Především jde o příkaz REGEXP_MATCH(), který lze používat typicky v klauzulích SELECT i WHERE. Pomocí regulárních výrazů lze také vybírat části textových řetězců pomocí REGEXP_EXTRACT() nebo je alternovat za jiné řetězce použitím výrazu REGEXP_REPLACE(). - Používejte API
BigQuery lze jednoduše ovládat přes uživatelské rozhraní, přesto vřele doporučuji naučit se ovládat BigQuery i Google Cloud Storage (slouží jako úložiště pro export a import dat) přes API. Některé funkce (například nastavení expirace tabulek) lze totiž ovládat výhradně touto cestou a přes UI se k nim nedostanete. K tomu je možné použít Google API explorer (v prohlížeči) nebo lépe příkazovou řádku pomocí připraveného SDK balíčku. Při každém API přístupu je ovšem nutné se opakovaně autentifikovat (OAuth 2.0), což je místy poměrně otravné. Nezapomeňte také API rozhraní aktivovat v rámci Google Developers Console. - JSON i CSV
Export i import dat je možný v obou formátech. V tabulkách můžete narazit na atributy s datovým typem “RECORD”, které obsahují vnořené (nested) záznamy. Při dotazování ovšem dochází defaultně k automatickému rozbalení do ploché struktury. Toho je možné pro komplikovanější kombinace více vnořených záznamů dosáhnout pomocí příkazu FLATTEN. - Uložení dat je levnější než dotazování
BigQuery je placené jako služba – proto zapomeňte na bezdůvodné vytahování všech dat (SELECT *), vybírejte jen to, co opravdu potřebujete. Nezapomeňte, že budete platit nikoliv za velikost výsledku, ale za to, jak velkou datovou strukturu svým dotazem propátráte. Tento objem zjistíte jednoduše kliknutím na zelenou “fajfku” v pravé části pod dotazovacím oknem. Používejte kešování (caching) často používaných dotazů (spuštění nakešovaného dotazu je zdarma). Z velkých datasetů si uložte ty nejpoužívanější data do samostatné tabulky. Storage je totiž relativně levný (aktuálně 0.02$ za 1GB měsíčně). Prvních dotazů se ale stejně bát nemusíte, jelikož máte měsíčně vyhrazenou kapacitu 1TB na dotazování zcela zdarma a nepovedené query (končící chybovou hláškou) se stejně jako ty nakešované nezpoplatňují. - Prakticky uložené datasety
To souvisí s bodem č.4. Místo jedné velké tabulky rozdělte vaše data do BigQuery na menší části. Doporučeným best practice je ukládání po časových jednotkách (např. 1 tabulka = jeden den) a/nebo dle obsahových rozdílů (např. 1 tabulka = 1 den/ČR). Joinovat lze i přes různé datasety, pokud tedy mají stejnou strukturu. Tabulky v jednom datasetu lze pak jednoduše spojovat i bez klauzule UNION prostým výčtem tabulek v klauzuli FROM (oddělené čárkou). Pokud je nechcete vypisovat, lze použít wildcards, které vám umožní elegantně sloučit libovolné množství tabulek (proto je také důležité je rozdělit a pojmenovat). - Rozsáhle dotazy = EACH
Pokud se snažíte joinovat velké tabulky, může dojít k tomu, že váš dotaz skončí chybovou hláškou. V takovém případě musíte využít funkci JOIN EACH, která sice zpomalí průběh query, ale s velkou pravděpodobností dotaz doběhne do konce. Data jsou uložena ve sloupcové struktuře, což znamená, že jednoduché dotazy trvají i nad gigantickými datasety jen pár vteřin. Pokud ale začnete tabulky joinovat, doba zpracování se může mnohonásobně navýšit. Podobná logika platí s klauzulí GROUP BY, pokud má agregovat velké množství různých unikátních hodnot. Proto použijte GROUP EACH BY. Pokud v těchto případech nepomůže ani EACH, zkuste váš dotaz na konci doplnit ještě omezující funkcí LIMIT. Pozor na počítání unikátních hodnot pomocí count(distinct XXX). V tomto případě je počet unikátních hodnot aproximován, tudíž je nepřesný! Alternativně je možné spočítat unikáty pomocí GROUP EACH BY nebo doplnit agregační výraz o volitelný číselný parametr, který udává počet řádků použitých pro odhad hodnoty např. count(distinct XXX, 100000). - Používejte doplňkové funkce
Vedle klasické SQL syntaxe a zmíněných regulárních výrazů je možné v BigQuery použít celou řadu funkcí, které vám zjednoduší život. Především se jedná o funkce, které umožňují pohodlný parsing URL a zpracování vnořených JSON záznamů (např. REPEATED COUNT). Mimo to je možné použít tzv. window funkce, které se odkazují na konkrétní “partitions” v datech (např. RANK funkce). V agregacích je pak možné využít pro vytvoření samostatné kategorie z null hodnot šikovnou funkci IFNULL. - Konektory
BigQuery je připravené k propojení s celou řadou nástrojů, které umožní významně rozšířit jeho použitelnost. Základem jsou jednoduché propojení do Google Docs spreadsheetů nebo Excelu, odkud lze psát dotazy i zobrazovat výsledky dotazů. Nechybí ale ani například připravený konektor pro Hadoop. Na BigQuery jsou ovšem připraveny i populární vizualizačně-analytické nástroje jako je Tableau nebo QlikView. Zároveň jsou k dispozici ETL nástroje pro streamování dat SQLstream nebo ODBC ovladače pro BigQuery s názvem Simba. Podrobný seznam nástrojů spolupracujících s BigQuery najdete zde.
