Jak jsme si vytvořili vlastní univerzální konektor GDS pro interní data a uvolnili ho k vašemu použití

Co se v tomto článku dozvíte?

• zalíbilo se nám GDS a chtěli jsme v něm mít vlastní interní data (ekonomika, používání naší aplikace apod.)
• tak jsme si vytvořili konektor, který bude univerzální a umožní nám velmi rychle napojovat jakákoliv data z naší databáze
• chceme ale, aby to vše bylo bezpečné a nemuseli jsme zpřístupňovat databázi GDSku, resp. Googlu
• napsali jsme si tedy vlastní rychlé PHP API, které je konfigurovatelné přímo z databáze
• nové vytvoření datového zdroje pro GDS už tedy obnáší pouze napsání SQL query, která vytáhne data
• a pak už jen kreslíme grafy v GOOGLE DATA STUDIO
• no a pak jsme se rozhodli, že veškeré zdrojáky uvolníme pro vás, třeba řešíte stejný problém

Pojďme ale postupně. Začátkem roku jsme absolvovali parádní E-shop summit & expo, kde nás Lukáš Palkovič ze společnosti eVisions konečně dopřemlouval, abychom dali dohromady GDS konektory pro Collabim. Během zpracování těchto konektorů a několika bezesných nocí při ladění se nám konečně GDS začalo líbit (dříve jsme totiž používali POWERBI pro interní reporting a také jsme pro vás připravili článek, jak si v něm velmi jednoduše naklikat Report obrátkovosti skladu e-shopu).

Rozhodli jsme se tedy pro přepsání interního reportingu do GDS, avšak narazili jsme na problém, že naše databáze (ze které chceme čerpat) je kompletně v AWD RDS, zabarikádována a nelze k ní přistupovat odkudkoliv (pouze z našich interních serverů). Navíc také nechceme žádná data (zejména osobní) pouštět někam ven, kde bychom nad nimi neměli plnou kontrolu.

Nakonec jsme to vymysleli takto:

• napíšeme si univerzální API endpoint do Collabimu, který budeme umět rychle konfigurovat skrze a pouze přes MYSQL (právě kvůli rychlosti)
• ideálně jen připravíme SQL query v editoru a až budeme spokojeni s připraveným zdrojem, uložíme si ho na sprosťáka do databáze
• napíšeme si univerzální konektor pro GDS, tomu vždy předhodíme pouze konfiguraci a metodu našeho virtuálního API která se má volat a dva klíče pro zabezpečení (jeden identifikuje uživatele, druhý je pro přístup k vybrané metodě)

Jak to tedy přesně funguje?

• v databázi máme tabulku, která obsahuje 4 sloupce:
  • název metody
  • zabezpečovací klíč k dané metodě
  • SQL pro přípravu dat (ano, opravdu máme SQL v SQL sloupci :D)
  • SQL pro finální získání dat
• v aplikaci máme kus PHP kódu, který když se zavolá, tak se ověří identita uživatele, vytáhne se z databáze požadovaná metoda, zkontroluje se bezpečnostní klíč dané metody a pokud je vše ok, zavolá se SQL s přípravou dat a poté se data vytáhnou, ty se vrací zpět jako JSON
• náš PHP endpoint volá GDS konektor, který se konfiguruje 3mi parametry (metoda, klíč metody, klíč uživatele)
• no a pak už máme data v GDS a můžeme kreslit grafy

Možná se teď ptáte, proč v MYSQL máme dvě SQL syntaxe. Před časem jsme se totiž zamilovali do TEMPORARY TABLE v mysql, které nám umožňují řešit extrémně komplexní databázové problémy v jediné query a celý proces probíhá výhradně na MYSQL serveru, zatímco naše aplikace odpočívá. Možná znáte náš Svatý grál, tak například ten celý počítá jedna velká SQL query právě touto metodou, která je mimochodem cca 10x rychlejší než konvenční JOINy (testováno na lidech).

První SQL je tedy určené pro přípravu dat (například složité přepočty přes TEMPORARY TABLE), druhá už jen vrací výsledná data pro finální zpracování do JSONu, který náš univerzální konektor pro Goole Data Studio vyžaduje.

Jak to vypadá v praxi?

Pro přístup k databázi používáme Sequel PRO, v něm si odladíme query, která nám vytahuje data z databáze:

Například tato metoda nám vytahuje ,kolik klientů každý den přibylo do naší databáze. Všimněte si, že výstupem jsou pouhá statistická data, žádné osobní údaje ani nic co bychom nepotřebovali pro nakreslení grafu v GDS.

Když máme metodu odladěnou, vložíme ji jako novou metodu do tabulky internal_api:

INSERT INTO `internal_api` (`methodName`, `securityKey`, `prepareDataStatement`, `getDataStatement`)
VALUES
(‘getClients’, ‘MOST_SECRET_HASH’, ‘set @num=1;’, ‘SELECT \nadded, COUNT(id) clientCount__number,promoCode\nFROM clients c\nGROUP BY added,promoCode;’);

pozn.: set @num=1; je zde jen jako slepá výplň, tento příklad nepotřebuje žádnou složitější přípravu dat, suffix __number za názvem sloupce napovídá našemu GDS konektoru, že se jedná o číslo (GDS k němu pak přistupuje jako k číslu)

Následně již jen nakonfigurujeme konektor v GDS:

No a tím dostaneme nový datový zdroj který obsahuje přesně ty informace, které potřebujeme vizualizovat. Můžeme pak nakreslit hezký graf, jak například využíváte naše jednorázové analýzy (navíc podle typu analýzy):

Pokud jste dočetli článek až sem, zasloužíte si samozřejmě slibovanou odměnu. Ano, všechny zdroje vám dáváme k dispozici, abyste si mohli toto vytvořit sami doma 😀

Co tedy budete potřebovat:

1. strukturu pro tabulku, ve které budete konfigurovat jednotlivé metody (https://github.com/collabim/gds-connector-php-backend/blob/master/internalApiTable.sql)
2. příklad metody: https://github.com/collabim/gds-connector-php-backend/blob/master/internalApiTableQueryExample.sql
3. PHP endpoint (controller + data mapper pro získávání dat z databáze): https://github.com/collabim/gds-connector-php-backend/tree/master/InternalApi
4. zdrojový kód konektoru: https://script.google.com/d/1VHXTruU_GKByjRlVl2Zp-8ULVccnkqkMDLlsxiP0BAI7HZZnxoW2_EY4/edit?usp=sharing

No a pokud chcete ještě trošku poradit, připravili jsme pro vás návod Jak si vytvořit vlastní konektor pro GDS.

Co teď s tím?

Bude stačit trochu programování a uvidíte, že hravě nahradíte absenci ETL funkcí v GDS a práce s ním vás začne extrémně bavit!