Google BigQuery a Reporty v Google Data Studio

Jak už víte z našeho minulého článku na našem blogu, Daktela nabízí celou řadu reportovacích nástrojů. Zjednodušeně lze reportovací a analytické nástroje rozdělit na dvě skupiny:

1. Daktela Statistiky a Analytiky
2. Google řešení

Daktela jako komplexní webová aplikace také poskytuje robustní analytické nástroje (Statistiky, Analytiky, Reporting), ale pro následující případy je vhodnější použít externí řešení:

• Velké množství dat
• Jeden zákazník používá více ústředen
• Propojování („join“) tabulek
• Pokročilé filtrování reportovaných dat
• Potřeba nezatěžovat ústřednu
• Co nejrychlejší načítání dat

Proto využíváme moderní a super-výkonné řešení od Googlu, na které se tentokrát zaměříme.

Všeobecně se jedná o kombinaci úložiště (Google BigQuery), reportovacího nástroje (Google DataStudio), přidružených služeb (Google Cloud Platform – zabezpečení, správa, údržba, monitoring) a datových toků (Daktela), které dohromady tvoří ETL (z angl. extract-transform-load) a BI (z angl. Business Intelligence) řešení.

Nespornou výhodou celého řešení je přístupnost kompletně přes webový prohlížeč, není tedy potřeba dedikovaný software a vše je dostupné
z jakéhokoliv počítače přes Google účet s dostatečnými právy. Operabilita systému je odvislá pouze od rychlosti připojení k internetu.

Daktela

Na serveru bigquery.daktela.com jsou nastavené pravidelné úlohy zajišťující transformaci a nahrání dat z ústředen do BigQuery (dále jen BQ)
v pravidelných intervalech. Tyto tzv. „run skripty“ jsou napsány v programovacím jazyce java.

Interval synchronizace je 120 minut a 24 hodin. Data se tedy pravidelně nahrávají každou druhou hodinu a na konci dne (o půlnoci) je ještě nahrána konsolidační dávka kvůli zajištění integrity. Dvouhodinový interval lze u vybraných ústředen snížit na jednohodinový.

V ojedinělých případech mohou vzniknout v BQ zdánlivě duplicitní záznamy – o jejich odhalení se stará samostatný skript, jehož výstup je sledován
a notifikován v monitorovacím softwaru. Duplicity jsou automaticky opravovány během denní synchronizace.

Jelikož na serveru bigquery.daktela.com běží „run skripty“ všech projektů napojených na BQ (k prosinci 2021 je to 21 ústředen), existuje rozvržení
po minutách, díky kterému si nahrávání jednotlivých instancí nekonkuruje a využívá se tak maximum systémových prostředků. I to přispívá k relativně malé době zpracování, kdy jeden synchronizační krok (nahrání hodinové nebo denní dávky pro všechny instance projektu) trvá i u největších ústředen v řádech minut, až desítek minut, typicky 10-30 minut, v extrémních případech cca 60-90 minut. Synchronizaci lze také spustit okamžitě manuálně
pro případ řešení problémů.

Průběh všech skriptů je logován do textového souboru.

Přístup k serveru bigquery.daktela.com je zabezpečen SSH klíčem. Server je plně pod správou IT administrátorů Daktela, stejnětak jako udělování uživatelských přístupů.

Google BigQuery

BQ je plně řízené, bezserverové datového úložiště (DWH – data warehouse), které umožňuje škálovatelnou analýzu extrémně velkého množství dat
(v řádu petabytu, 1PB ≈ 1024 TB ≈ 1 milion GB). Jedná se o platformu ve formě služby (dostupné přes web), jejíž součástí je i dotazovací rozhraní
(na bázi ANSI SQL).

Dále Google poskytuje přidruženou funkcionalitu, jako je strojové učení, geografické nástroje, analytické a statistické nástroje a správu uživatelských práv a přístupů.

Architektura je kompletně pod správou Googlu, uživatelé/konzumenti se tedy nemusí zabývat otázkami, jako je škálování, distribuce výpočetních prostředků, zálohování, údržba atd. Jejich unikátní řešení je navrženo tak, aby bylo možné dostávat výsledky dotazů nad terabyty dat v řádu sekund
a nad petabyty dat v řádu minut.

Díky tomu lze reporty načítat (a interaktivně filtrovat/třídit) v reálném čase.

Ačkoliv se nejedná čistě o databázi jako takovou, lze o BQ mluvit jako o nerelačním databázovém OLAP systému (z angl. Online Analytical Processing), optimalizovaném pro čtení a analýzu. Vkládání (insert) je ale také možný, stejně jako napojení BQ na externí datové zdroje.

Mezi klíčové vlastnosti patří škálovatelnost, spolehlivost, dostupnost, nízkonákladovost, samo-opravitelnost a decentralizovanost. Je alternativou
pro DWH systémy jako například Amazon Redshift nebo Microsoft Azure SQL.

Data jsou v BQ uspořádána do tzv. data setů (obdoba databáze), které se skládají z tabulek, pohledů a případně dalších entit (například modelů strojového učení). Práva (přístup, úpravy, mazání atd.) lze nastavovat jak pro dataset, tak pro dílčí tabulky.

Řízení uživatelských účtů a práv je realizováno prostřednictvím webu platformy Google Cloud. Jednotliví uživatelé služby musí mít funkční Google účet. Kontrola a udělování přístupů je plně v kompetenci Daktela administrátorů.

Více informací lze najít na oficiálních stránkách Google Cloud [anglicky].

Kvóty a limity

Google do určité míry omezuje vytížitelnost systému nastavením limitů a kvót za určité období (např. počet dotazů, velikost přenesených dat atd.).
BQ je určeno pro opravdu velké a datově náročné projekty, proto z pohledu Big Data nejsou ani ty největší a nejsložitější Daktela ústředny ničím než malou a jednoduchou datovou sadou.

Více o kvótách a limitech lze nalézt na oficiálních stránkách Google [anglicky].

Google Data Studio

Google Data Studio (DS) je nástroj pro vizualizaci a reportování dat podobně jako Microsoft Power BI nebo Tableau. Jedná se o webovou aplikaci, pomocí které lze v uživatelsky přívětivém prostředí vytvářet reporty skládáním tabulek a grafů doplněných o textové a vizuální prvky.

Je to tedy současně nástroj pro:

• Vytváření/úpravy jednotlivých reportů
• Správu více reportů přístupných danému uživateli
• Zobrazování jednotlivých reportů
• Vytváření/úpravy/správu datových zdrojů (viz dále)
• Správu přístupů k těmto reportům a datovým zdrojům

Vlastní report se vždy skládá z:

• Datového zdroje
• Obsahu
• Nastavení přístupů

Jeden datový zdroj může být sdílen napříč více reporty. Jeden report může obsahovat více datových zdrojů.

V rámci prostředí Google se řídí přístupová práva v každé jednotlivé oblasti zvlášť:

Big Query

• Přístupy a práva k projektu (interní přístupy v rámci Daktela)

• Přístupy a práva k datasetu/datasetům

Data Studio

• Přístupy a práva ke sdíleným datovým zdrojům

• (automaticky) přístupy a práva k vlastněným datovým zdrojům

• (automaticky) přístupy a práva k vlastněným reportům

• Přístupy a práva ke sdíleným reportům

Pro všechny oblasti (BQ, DataStudio) platí princip, že uživatel vidí pouze entity, ke kterým má udělený přístup. Neví o existenci entit (datasety, tabulky, datové zdroje, reporty) ostatních uživatelů, dokud se k nim nepokusí přistoupit přes URL.

DataStudio – datové zdroje

Termínem „datový zdroj“ (data source) se označuje souhrn:

• Nastavení připojení ke zdroji dat
• Pravidel transformace a formátování získaných dat
• Frekvence synchronizace dat
• Přístupových práv

Tyto položky společně tvoří entitu v Google DS, se kterou uživatel pracuje. Nejedná se tedy přímo o zdroj dat, ale spíše o datový model/schema, nebo „konektor“ ke zdroji dat, typicky k DWH.

Google DS se pomocí „datového zdroje“ umí připojit k mnoha druhům DWH, od vlastních (Google BQ, Google Sheets, Google Analytics, Google Ads, Google Surveys, Google Cloud ad.), přes běžné (Microsoft SQL Server, MySQL, PostgreSQL, soubory ve formátu CSV ad.), až ke konektorům třetích stran. Kompletní výčet lze nalézt na oficiálních stránkách. Daktela má také vlastní konektor.

Vytvořením „datového zdroje“ vznikne entita, která určuje:

• Připojení k BiqQuery nebo zdroji dat („data origin“)
• Určení dotazované tabulky nebo definice SQL dotazu
• Formátování „dimenzí“ (viz dále)
• Agregované „metriky“ (viz dále)
• Parametry – obvykle časové období pro data
• Přístup – který uživatel může datový zdroj upravovat a/nebo zobrazovat jeho definici
• Frekvence synchronizace dat („data freshness“) – obvykle 1 hodina, může být ale až 1 minuta či naopak 4 nebo 12 hodin
• Sdílení datového zdroje – buď omezeno na aktuální report, nebo umožněno sdílení zdroje přes několik reportů

K zúžení výběru dat v reportu se kromě datových zdrojů používají také „ovládací prvky“ a „skryté filtry“ popsané dále. Jednotlivé datové zdroje lze také
v reportech propojovat mezi sebou (tzv. blend), čímž lze docílit komplexní agregace dat, která by bez dobré znalosti jazyka SQL byla pro některé uživatele nedosažitelná.

Za každým požadavkem na data z reportu do BQ stojí SQL dotaz, jehož výsledek BQ dočasně ukládá (tzv. cash) pro další použití (v rámci časového intervalu „data freshness“), a tím lze docílit okamžité odezvy a nízkých nákladů na dotaz.

Vlastník reportu se automaticky stává vlastníkem datového zdroje, který vytváří v daném reportu. Sdílené datové zdroje z vlastních reportů je možné přidávat do jakéhokoliv dalšího vlastního reportu. Pro používání a editaci datových zdrojů jiných uživatelů je nutné požádat o přístup k nim a jejich vlastník musí přístup potvrdit.

Odebráním „vestavěného“ (embedded) datového zdroje z reportu dojde k jeho odstranění. Odebráním sdíleného datového zdroje z reportu k jeho odstranění nedojde.

DataStudio – reporty

Po nastavení datového zdroje je možné vytvořit report skládáním grafů, v nichž uživatel vybírá dimenze a/nebo metriky z datového zdroje. Pro lepší orientaci je možné report rozdělovat do stránek a přidávat další doprovodné prvky jako prostý text a grafiku.

Pole

Polem se rozumí datový sloupec. Principielně existují dva druhy – dimenze a metriky. Dimenze obsahují entity, které chceme měřit, nebo kategorie. Metriky jsou veličiny, které číselně popisují dimenze.

Kromě polí automaticky načtených z datového zdroje lze vytvořit vlastní pole:

• Kombinováním dimenzí vytvoříme novou dimenzi
• Kombinováním metrik mezi sebou, nebo s dimenzemi, vytvoříme novou metriku

Dimenze

Dimenzí se rozumí kategorie informací/dat, respektive datové řady. Například při měření návštěvnosti webové stránky mohou být dimenzemi Prohlížeč, Webová stránka, Jazyk atd.

V DS potom lze změnit název, datový typ (text, číslo, datum atp.) a popis dimenzí.

Dimenze je obdobou sloupce v tabulce (pokud používáme databázový nebo tabulkový datový zdroj).

Metriky

Metrikou se zde rozumí agregovaný výpočet/operace, aplikovaný na sadu dat, například počet záznamů, suma, maximum, minimum, průměrná hodnota atd. Je tedy obdobou agregované funkce v SQL dotazu.

V grafech a tabulkách je možné dimenze a metriky kombinovat.

Grafy

K dispozici jsou:

• Tabulky (běžné a pivotní)
• Grafy
• Tzv. skórkarty (prvek s jednou metrikou)

Filtry

Filtry jsou reálně na pozadí DS interpretovány jako úprava SQL datového zdroje, čili nezáleží na tom, zda tvůrce reportu data filtruje již na vstupu do DS (definice SQL dotazu), na úrovni elementu reportu (skrytý filtr grafu nebo tabulky), nebo až při interakci návštěvníka reportu (uživatelský filtr = kontrolní prvek), ve výsledku se provádí SQL dotazy do BQ.

Filtrováním lze také dosáhnout rychlejšího načtení dat (načítá se filtrovaná sada), proto je vhodnější u datových zdrojů přesně formulovat konkrétní SQL dotaz spíše než požadovat celou tabulku (a filtrovat až na úrovni reportu).

Další prvky

Kromě výše uvedeného lze také do reportů vkládat:

• Hypertextové odkazy
• Obrázky
• Externí obsah („embedded URL“)
• Parametry (časové období, e-mailová adresa návštěvníka nebo vlastní parametry)

Tím, že se report nachází na konkrétní URL, je naopak možné celý interaktivní obsah vkládat jinam (do online dokumentů, webových stránek apod.).

DataStudio – přístupy

Všeobecně lze přístup rozdělit na dvě úrovně:

1. Zobrazení
2. Úpravy

Uživatelé s přístupem (1) nemohou provádět žádné úpravy rozložení reportu ani jeho nastavení, mohou pouze s reportem interagovat a obvykle jeho obsah exportovat do PDF, tisknout, či vytvořit kopii (u té lze pak vstoupit do režimu úprav a de facto zjistit, jak byl report konstruován).

Uživatelé s přístupem (2) mohou upravovat obsah reportu a mohou měnit veškerá nastavení. Vlastník (autor) reportu může řídit další dílčí vlastnosti, například ostatním uživatelům zrušit možnost přidávat další uživatele.

Reporty je také možné zpřístupnit pomocí odkazu, díky čemuž lze upravit výše uvedené na:

1. Sdílení přes odkaz vypnuto = pouze uvedení uživatelé mají přístup
2. Sdílení přes odkaz zapnuto = přístup mají uvedení uživatelé plus:

• 2.1. kdokoliv s odkazem v rámci společnosti (např. Daktela s.r.o.) může report zobrazit
• 2.2. kdokoliv s odkazem v rámci společnosti může report upravovat
• 2.3. kdokoliv s odkazem může report zobrazit
• 2.4. kdokoliv s odkazem může report upravit
• 2.5. kdokoliv na internetu může report najít a zobrazit (i bez odkazu)
• 2.6. kdokoliv na internetu může report najít a upravit (i bez odkazu)

Je tedy možné vyhnout se problematice řízení a obsluhování přístupů k reportu tím, že vlastník zapne sdílení přes odkaz s úrovní (2.3) nebo (2.5), hrozí ale nebezpečí zpronevěření reportu/dat. Sledovat návštěvnost reportu na úrovni konkrétního uživatele nikde nelze (přes Google Analytics lze sledovat pouze anonymizované informace).

Přístupy k datovým zdrojům jsou popsány výše.

Pro získání přístupu k interaktivní a aktuální podobě reportu je potřeba znát buď:

1. URL, na které se report nachází nebo
2. Přesný název reportu a Google účet jeho vlastníka. PDF verze reportu může obsahovat odkaz na online nebo vnořenou verzi reportu.

V případě (1) se na webové adrese buď zobrazí report (pokud k němu uživatel má práva), nebo informace o chybějících právech a formulář
pro kontaktování vlastníka reportu. Při chybějícím přístupu tak stačí nastavit požadovanou úroveň oprávnění a odeslat formulář – vlastníkovi přijde emailové upozornění. Po udělení přístupu k reportu potom žadateli opět přijde emailové upozornění (pokud toto upozornění není vlastníkem vypnuto). V případě (2) je potřeba kontaktovat vlastníka jinou cestou a ten potom musí daný report najít a manuálně přidat žadateli oprávnění.

Reporty ve formátu PDF mohou být zabezpečené pomocí hesla, lze je ale sdílet jako jakékoli jiné soubory. Vlastník reportu může u online reportu zrušit možnost exportu do PDF (společně se zruší export do PDF, možnost Tisk a možnost Kopírovat report).

Nakonec existuje ještě distribuovaná forma sdílení reportu – pravidelné zasílání reportu na email. V tomto případě je pravidelně vygenerována
e-mailová zpráva obsahující:

• Odkaz na aktuální podobu reportu
• Náhled reportu (zpravidla prvních několik stránek)
• PDF soubor reportu k danému datu v příloze

Tento email je možné dále přeposílat. Pokud ale nový příjemce nemá k reportu přístup, na odkazované stránce se mu zobrazí formulář s žádostí
o přístup, ale stále bude schopný otevřít přiložený PDF soubor.

Takto lze umožnit distribuci na mnoho emailových adres a mezi externí uživatele.

Přečtěte si více z našeho blogu: