Când are sens să folosești Google BigQuery?

De-a lungul carierei mele, a trebuit să scriu interogări analitice complexe pentru diferite tipuri de rapoarte și diagrame. Cel mai adesea, era o diagramă care afișa date agregate după dată, săptămână, trimestru și așa mai departe. De obicei, astfel de rapoarte sunt create pentru a ajuta clienții să identifice tendințele și să ilustreze modul în care afacerea lor funcționează la un nivel înalt. Dar ce se întâmplă atunci când oamenii de știință de date și inginerii trebuie să creeze un raport mult mai amplu, bazat pe un set de date mari?

În cazul în care raportul se bazează pe un set mic de date, sarcina poate fi rezolvată prin scrierea unei interogări SQL într-o bază de date relațională. În acest pas, este important să cunoașteți elementele de bază pentru scrierea interogărilor și cum să le faceți mai rapide și mai eficiente. Cu toate acestea, uneori, raportul depinde de un set mai mare de date (de exemplu, milioane și mai multe rânduri dintr-un tabel), dar raportul nu depinde de variabilele de intrare (parametri) sau puteți găsi că numărul de valori este destul de mic. În astfel de scenarii, o interogare SQL poate fi lentă, deci nu ar fi optim pentru utilizatori să aștepte până când interogarea este executată. Cea mai obișnuită practică în astfel de cazuri este de a rula o interogare înainte de timp - înainte ca clientul să solicite un raport.

De asemenea, necesită implementarea unor funcționalități de stocare în cache, astfel încât clientul să poată prelua date din cache în loc să ruleze o interogare în timp real. Această abordare funcționează perfect, cu condiția să nu fie nevoie să afișați date în timp real. Poate afișa date calculate cu o oră sau chiar cu o zi mai devreme. Deci, raportul/graficul real este afișat folosind date stocate în cache, nu pe baza datelor în timp real.

Trec la Google BigQuery

În timp ce lucram la un proiect analitic în industria farmaceutică, aveam nevoie de diagrame care să ia codul poștal și numele medicamentului ca parametri de intrare. De asemenea, trebuia să arăt câteva comparații între medicamentele din anumite regiuni ale Statelor Unite.

Interogarea analitică a fost foarte complexă și a ajuns să ruleze aproximativ 50 de minute pe serverul nostru Postgres (procesor quad-core cu 16 GB RAM). Nu am reușit să-l rulez din timp și să memorez rezultatele în cache, deoarece interogarea lua coduri poștale și medicamente ca parametri de intrare, așa că existau mii de combinații și era imposibil să prezic care client va alege.

Chiar dacă aș fi vrut să încerc să execut toate combinațiile de parametri de intrare, cel mai probabil baza mea de date s-ar fi prăbușit. Așa că era timpul să alegem o abordare diferită și să alegem o soluție ușor de utilizat. Acea diagramă a fost importantă pentru client, cu toate acestea, clientul nu era pregătit să se angajeze să facă schimbări majore în arhitectură sau să migreze complet la o altă DB.

Pe acel proiect anume, am încercat câteva abordări diferite:

• Scalare verticală a serverului (adăugarea de RAM și CPU la serverul Postgres)
• Folosind baze de date alternative, cum ar fi Amazon Redshift și altele.
• Am cercetat și o soluție NoSQL, dar cele mai multe dintre ele sunt destul de complexe și necesită multe schimbări în arhitectură, dintre care multe ar fi fost prea mari pentru client.

În cele din urmă, am încercat Google BigQuery. Ne-a îndeplinit așteptările și ne-a permis să ducem treaba la bun sfârșit fără a face schimbări uriașe pe care clientul ar fi reticent să le aprobe. Dar ce este Google BigQuery și cum funcționează?

BigQuery este un serviciu web bazat pe REST, care vă permite să rulați interogări analitice complexe bazate pe SQL în seturi mari de date. După ce am încărcat datele în BigQuery și am executat aceeași interogare ca și pe Postgres (sintaxa este ciudat de similară), interogarea noastră rula mult mai rapid și a durat aproximativ un minut. În cele din urmă, am ajuns să obținem o creștere a performanței de 50 ori doar prin utilizarea unui serviciu diferit. Este demn de remarcat faptul că alte DB-uri nu au furnizat același câștig de performanță și să fim generoși și să spunem doar că nu au fost nici măcar aproape. Sincer să fiu, am fost cu adevărat impresionat de câștigul de performanță oferit de BigQuery, deoarece cifrele erau mai bune decât ne-am sperat oricare dintre noi.

În ciuda acestui fapt, nu aș face publicitate BigQuery drept cea mai bună soluție de baze de date din lume. Deși a funcționat bine pentru proiectul nostru, are încă o mulțime de limitări, cum ar fi un număr limitat de actualizări în tabel pe zi, limitări privind dimensiunea datelor per solicitare și altele. Trebuie să înțelegeți că BigQuery nu poate fi utilizat pentru a înlocui o bază de date relațională și este orientat spre executarea de interogări analitice, nu pentru operațiuni și interogări CRUD simple.

În acest articol, voi încerca să compar folosind Postgres (baza mea de date relațională preferată) și BigQuery pentru scenarii de utilizare din lumea reală. De asemenea, aș oferi câteva sugestii pe parcurs, și anume părerea mea despre când are sens să folosești BigQuery.

Eșantion de date

Pentru a compara Postgres și Google BigQuery, am luat informații demografice publice pentru fiecare țară grupate după țară, vârstă, an și sex (puteți descărca aceleași date de pe acest link).

Am adăugat datele în patru tabele:

1. populations
2. locations
3. age_groups
4. populations_aggregated

Ultimul tabel este doar date agregate din cele trei tabele anterioare. Iată schema DB:

Tabelul de populations cu care am ajuns conține mai mult de 6,9 ​​milioane de rânduri. Nu este prea mult, dar a fost suficient pentru testul meu.

Pe baza datelor eșantion, am încercat să construiesc interogări care pot fi folosite pentru a construi rapoarte și diagrame analitice din viața reală. Așa că am pregătit întrebări pentru următoarele rapoarte:

1. Populația din SUA agregată pe ani.
2. Populația în 2019 pentru toate țările, începând din cele mai mari țări.
3. Top cinci „cele mai vechi” națiuni în fiecare an. „Cea mai în vârstă” desemnează țările în care procentul persoanelor de peste 60 de ani față de numărul total de persoane este cel mai mare. Interogarea ar trebui să dea cinci rezultate în fiecare an.
4. Top cinci națiuni agregate pe an, unde diferența dintre populația masculină și cea feminină este cea mai mare.
5. Obțineți vârsta medie (medie) pe țară pentru fiecare an, începând de la țările „cele mai bătrâne” la cele mai „tinere”.
6. Găsiți primele cinci țări „pe moarte” în fiecare an. „Moarte” înseamnă țări în care populația este în scădere (depopularea este cea mai mare).

Interogările #1, #2 și #6 sunt destul de ușoare și directe, dar interogările #3, #4 și #5 nu au fost atât de ușor de scris, cel puțin pentru mine. Vă rugăm să rețineți că sunt un inginer back-end și scrierea de interogări SQL complexe nu este tocmai o specialitate de-a mea, așa că cineva cu mai multă experiență SQL ar putea probabil să construiască interogări mai inteligente. Cu toate acestea, în acest moment trebuie să verificăm modul în care Postgres și BigQuery procesează aceleași interogări cu aceleași date.

Am construit 24 de interogări în total:

• 6 pentru Postgres DB, care utilizează tabele neagregate ( populations , locations , age_groups de vârstă)
• 6 pentru Postgres DB, care utilizează tabelul populations_aggregated
• 6+6 interogări pentru BigQuery care utilizează tabele agregate și neagregate.

Permiteți-mi să trimit interogările BigQuery #1 și #5 pentru date agregate, astfel încât să puteți înțelege complexitatea interogărilor simple (#1) și complexe #5.

Populația din SUA agregată în funcție de ani de interogare:

 select sum (value), year from world_population.populations_aggregated where location_name = 'United States of America' group by 2 order by year asc

Interogare pentru vârsta medie pe țară pe fiecare an, sortată de la cel mai în vârstă la cel mai tânăr:

 --converts age to number with population_by_age_year_and_location as( select sum (value) as value, cast (regexp_replace(age_group_name, '\\+', '') as int64) as age, year, location_name from world_population.populations_aggregated where location_type = 'COUNTRY' group by 2,3,4), --calculates total population per country per year total_population_by_year_and_locations as ( select sum(value) as value, year, location_name from population_by_age_year_and_location group by 2,3 ), --calculates total number of people in country per year age_multiplied_by_population_temp as ( select sum(value * age) as value, year, location_name from population_by_age_year_and_location group by 2,3 ), median_per_year_country as ( select a.value / b.value as median, a.year, a.location_name from age_multiplied_by_population_temp a inner join total_population_by_year_and_locations b on a.location_name = b.location_name and a.year = b.year ) select * from median_per_year_country order by year desc, median desc

Notă: Puteți găsi toate interogările în depozitul meu bitbucket (linkul este la sfârșitul articolului).

Rezultatele testului

Pentru a rula interogări, am folosit două servere Postgres diferite. Primul are 1 nucleu CPU și 4 GB RAM, susținut de o unitate SSD. Al doilea are 16 nuclee CPU, 64 GB RAM și folosea și o unitate SSD (al doilea server are potențialul CPU și RAM de 16 ori mai mare).

De asemenea, rețineți că bazele de date nu au fost încărcate în timpul rulării testului. Le-am creat doar pentru a rula interogări. În situații reale, interogările vor dura mai mult, deoarece alte interogări pot rula în același timp și, în plus, acele interogări care rulează în paralel pot bloca tabele. Pentru a verifica viteza interogărilor, am folosit pgAdmin3 și interfața web BigQuery.

În testul meu, am obținut următoarele rezultate:

Permiteți-mi să arăt acele rezultate într-o diagramă cu bare pentru interogarea #1 și interogarea #5.

Notă: baza de date Postgres a fost localizată pe un server din SUA, iar eu sunt în Europa, așa că a existat o întârziere suplimentară în transmiterea datelor Postgres.

Performanța și concluziile BigQuery

Pe baza rezultatelor pe care le-am obtinut, am tras urmatoarele concluzii:

• În cazul scalarii verticale a Postgres, chiar și de 16 ori, acesta oferă doar 10-25% din performanță în rularea unei singure interogări. Cu alte cuvinte, un server Postgres cu un singur nucleu CPU și 4 GB RAM rula interogări cu un timp foarte similar cu timpul necesar pentru un server cu 16 nuclee CPU și 64 GB RAM. Desigur, serverele mai mari pot procesa seturi de date mult mai mari, cu toate acestea, acest lucru nu oferă o îmbunătățire semnificativă a timpului de execuție a interogărilor.
• Pentru alăturarea Postgres cu tabele mici (tabelul locations are aproximativ 400 de rânduri și age_groups are 100 de rânduri) nu produc o diferență uriașă în comparație cu rularea interogărilor sub date agregate situate într-un singur tabel. De asemenea, am constatat că pentru interogările care rulează una până la două secunde, interogările cu îmbinări interioare sunt mai rapide, dar pentru interogările de lungă durată, situația este diferită.
• În BigQuery situația cu îmbinări este total diferită. BigQuery nu-i plac alăturarea. Diferența de timp dintre interogări, care utilizează date agregate și neagregate, este destul de mare (pentru interogările #3 și $5 a fost de aproximativ două secunde). Înseamnă că, pentru BigQuery, puteți face câte subinterogări doriți, dar pentru o performanță bună, interogarea ar trebui să utilizeze un singur tabel.
• Postgres este mai rapid pentru interogări care folosesc agregare simplă sau filtrare sau care utilizează un set de date mic. Am descoperit că interogările care durează mai puțin de cinci secunde în Postgres funcționează mai lent în BigQuery.
• BigQuery are performanțe mult mai bune pentru interogările de lungă durată. Pe măsură ce diferența de dimensiune a setului de date crește, la fel va crește și diferența de timp pentru finalizarea acestor interogări.

Când are sens să folosiți BigQuery

Acum, să revenim la problema principală discutată în acest articol: când ar trebui să utilizați de fapt Google BigQuery? Pe baza concluziilor mele, aș sugera să utilizați BigQuery atunci când sunt îndeplinite următoarele condiții:

• Utilizați-l atunci când aveți interogări care rulează mai mult de cinci secunde într-o bază de date relațională. Ideea BigQuery este să ruleze interogări analitice complexe, ceea ce înseamnă că nu are rost să rulezi interogări care fac agregare sau filtrare simplă. BigQuery este potrivit pentru interogări „grele”, cele care funcționează folosind un set mare de date. Cu cât setul de date este mai mare, cu atât este mai probabil să obțineți performanță utilizând BigQuery. Setul de date pe care l-am folosit a fost de doar 330 MB (megaocteți, nici măcar gigaocteți).
• BigQuery nu-i plac alăturarea, așa că ar trebui să îmbinați datele într-un singur tabel pentru a obține un timp de execuție mai bun. BigQuery permite salvarea rezultatelor interogării într-un tabel nou, așa că pentru a crea un nou tabel agregat, trebuie doar să încărcați toate datele în BigQuery, să rulați o interogare care va consolida toate datele și să o salvați într-un nou tabel.
• BigQuery este bun pentru scenariile în care datele nu se modifică des și doriți să utilizați memoria cache, deoarece are cache încorporată. Ce inseamna asta? Dacă executați aceeași interogare și datele din tabele nu sunt modificate (actualizate), BigQuery va folosi doar rezultatele stocate în cache și nu va încerca să execute din nou interogarea. De asemenea, BigQuery nu percepe bani pentru interogările din cache. Notă: chiar și interogările din cache durează 1-1,2 secunde pentru a returna rezultate.
• De asemenea, puteți utiliza BigQuery atunci când doriți să reduceți încărcarea bazei de date relaționale. Interogările analitice sunt „grele” și utilizarea excesivă a acestora într-o bază de date relațională poate duce la probleme de performanță. Deci, ați putea fi forțat în cele din urmă să vă gândiți la scalarea serverului. Cu toate acestea, cu BigQuery puteți muta aceste interogări care rulează într-un serviciu terță parte, astfel încât să nu afecteze baza de date relațională principală.

În sfârșit, încă câteva cuvinte despre utilizarea BigQuery în viața reală. În proiectul nostru real, datele pentru rapoarte se schimbau săptămânal sau lunar, astfel încât să putem încărca manual date în BigQuery. Cu toate acestea, dacă datele dvs. se modifică frecvent, sincronizarea datelor între baza de date relațională și BigQuery ar putea să nu fie atât de simplă, iar acesta este un avertisment care merită reținut.

Legături

Puteți găsi exemple de date utilizate în acest articol aici, în timp ce interogările și datele în format CSV sunt accesibile aici.