Kiedy warto korzystać z Google BigQuery?

W swojej karierze musiałem pisać złożone zapytania analityczne do różnego rodzaju raportów i wykresów. Najczęściej był to jakiś wykres, który wyświetlał dane zagregowane według daty, tygodnia, kwartału i tak dalej. Zazwyczaj takie raporty są tworzone, aby pomóc klientom zidentyfikować trendy i zilustrować, jak ich firma radzi sobie na wysokim poziomie. Ale co się stanie, gdy analitycy i inżynierowie danych będą musieli stworzyć znacznie obszerniejszy raport na podstawie zbioru dużych zbiorów danych?

W przypadku, gdy raport opiera się na małym zestawie danych, zadanie można rozwiązać, pisząc zapytanie SQL w relacyjnej bazie danych. Na tym etapie ważne jest, aby znać podstawy pisania zapytań oraz wiedzieć, jak sprawić, by były szybsze i wydajne. Czasami jednak raport zależy od większego zestawu danych (np. miliony i więcej wierszy w tabeli), ale raport nie jest zależny od zmiennych wejściowych (parametrów) lub może się okazać, że liczba wartości jest dość mała. W takich scenariuszach zapytanie SQL może być powolne, więc czekanie na wykonanie zapytania nie byłoby optymalne dla użytkowników. Najczęstszą praktyką w takich przypadkach jest uruchomienie zapytania z wyprzedzeniem — zanim klient zażąda raportu.

Ponadto wymaga zaimplementowania pewnej funkcji buforowania, aby klient mógł pobierać dane z pamięci podręcznej zamiast uruchamiać zapytanie w czasie rzeczywistym. To podejście działa idealnie, pod warunkiem, że nie musisz pokazywać danych w czasie rzeczywistym. Może pokazywać dane obliczone godzinę lub nawet dzień wcześniej. Tak więc rzeczywisty raport/wykres jest wyświetlany przy użyciu danych z pamięci podręcznej, a nie danych w czasie rzeczywistym.

Przechodzenie do Google BigQuery

Kiedy pracowałem nad projektem analitycznym w branży farmaceutycznej, potrzebowałem wykresów, które jako parametry wejściowe przyjmowały kod pocztowy i nazwę leku. Musiałem też pokazać kilka porównań leków w określonych regionach Stanów Zjednoczonych.

Zapytanie analityczne było bardzo złożone i trwało około 50 minut na naszym serwerze Postgres (czterordzeniowy procesor z 16 GB RAM). Nie byłem w stanie uruchomić go z wyprzedzeniem i buforować wyników, ponieważ zapytanie pobierało kody pocztowe i leki jako parametry wejściowe, więc były tysiące kombinacji i nie można było przewidzieć, który klient wybierze.

Nawet gdybym chciał spróbować wykonać wszystkie kombinacje parametrów wejściowych, moja baza danych najprawdopodobniej uległaby awarii. Nadszedł więc czas, aby wybrać inne podejście i wybrać proste w użyciu rozwiązanie. Ten wykres był ważny dla klienta, jednak klient nie był gotowy do zobowiązania się do wprowadzenia dużych zmian w architekturze lub całkowitej migracji do innej bazy danych.

W tym konkretnym projekcie wypróbowaliśmy kilka różnych podejść:

• Skalowanie pionowe serwera (dodanie pamięci RAM i procesora do serwera Postgres)
• Korzystanie z alternatywnych baz danych, takich jak Amazon Redshift i inne.
• Zbadaliśmy również rozwiązanie NoSQL, ale większość z nich jest dość złożona i wymaga wielu zmian w architekturze, z których wiele byłoby zbyt dużych dla klienta.

Wreszcie wypróbowaliśmy Google BigQuery. Spełniło to nasze oczekiwania i pozwoliło nam wykonać zadanie bez wprowadzania dużych zmian, których klient nie chciałby zaakceptować. Ale czym jest Google BigQuery i jak działa?

BigQuery to usługa internetowa oparta na REST, która umożliwia uruchamianie złożonych zapytań analitycznych opartych na SQL na dużych zestawach danych. Po przesłaniu danych do BigQuery i wykonaniu tego samego zapytania, które wykonaliśmy w Postgresie (składnia jest niesamowicie podobna), nasze zapytanie działało znacznie szybciej i trwało około minuty. Ostatecznie uzyskaliśmy 50-krotny wzrost wydajności, korzystając tylko z innej usługi. Warto zauważyć, że inne DB nie zapewniały takiego samego przyrostu wydajności i bądźmy hojni i po prostu powiedzmy, że nie były nawet blisko. Szczerze mówiąc, byłem pod wrażeniem wzrostu wydajności zapewnianego przez BigQuery, ponieważ liczby były lepsze, niż ktokolwiek z nas miał nadzieję.

Mimo to nie reklamowałbym BigQuery jako najlepszego rozwiązania bazodanowego na świecie. Chociaż w naszym projekcie zadziałało to dobrze, nadal ma wiele ograniczeń, takich jak ograniczona liczba aktualizacji w tabeli dziennie, ograniczenia dotyczące rozmiaru danych na żądanie i inne. Musisz zrozumieć, że BigQuery nie może zastąpić relacyjnej bazy danych i jest zorientowane na uruchamianie zapytań analitycznych, a nie na proste operacje i zapytania CRUD.

W tym artykule postaram się porównać użycie Postgresa (moja ulubiona relacyjna baza danych) i BigQuery dla rzeczywistych scenariuszy użycia. Ponadto chciałbym przedstawić kilka sugestii, a mianowicie moją opinię na temat tego, kiedy faktycznie warto korzystać z BigQuery.

Przykładowe dane

Aby porównać Postgres i Google BigQuery, zebrałem publiczne dane demograficzne dla każdego kraju pogrupowane według kraju, wieku, roku i płci (te same dane można pobrać z tego linku).

Dodałem dane do czterech tabel:

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

Ostatnia tabela to tylko zagregowane dane z poprzednich trzech tabel. Oto schemat bazy danych:

Tabela populations , na której się skończyłem, zawiera ponad 6,9 miliona wierszy. To nie za dużo, ale na mój test wystarczyło.

Na podstawie przykładowych danych próbowałem budować zapytania, które można wykorzystać do budowania rzeczywistych raportów analitycznych i wykresów. Przygotowałem więc zapytania do kolejnych raportów:

1. Ludność w USA zagregowana według lat.
2. Populacja w 2019 roku dla wszystkich krajów począwszy od krajów największych.
3. Każdego roku pięć najlepszych „najstarszych” narodów. „Najstarsze” oznacza kraje, w których odsetek osób powyżej 60. roku życia w ogólnej liczbie osób jest najwyższy. Zapytanie powinno dawać pięć wyników w każdym roku.
4. Pięć największych krajów zagregowanych według roku, w których różnica między populacją mężczyzn i kobiet jest największa.
5. Uzyskaj medianę (średnią) wieku na kraj dla każdego roku, zaczynając od „najstarszych” do „najmłodszych” krajów.
6. Znajdź pięć największych „umierających” krajów każdego roku. „Umieranie” oznacza kraje, w których populacja spada (wyludnienie jest największe).

Zapytania #1, #2 i #6 są dość łatwe i proste, ale zapytania #3, #4 i #5 nie były tak łatwe do napisania — przynajmniej dla mnie. Pamiętaj, że jestem inżynierem back-endu i pisanie złożonych zapytań SQL nie jest moją specjalnością, więc ktoś z większym doświadczeniem SQL mógłby prawdopodobnie tworzyć inteligentniejsze zapytania. Jednak w tej chwili musimy sprawdzić, jak Postgres i BigQuery przetwarzają te same zapytania z tymi samymi danymi.

Łącznie zbudowałem 24 zapytania:

• 6 dla Postgres DB, które używają tabel niezagregowanych ( populations , locations , age_groups )
• 6 dla Postgres DB, które korzystają z tabeli populations_aggregated
• 6+6 zapytań dla BigQuery korzystających z tabel zagregowanych i niezagregowanych.

Pozwól, że podzielę się zapytaniami BigQuery nr 1 i nr 5 dotyczącymi danych zagregowanych, dzięki czemu zrozumiesz złożoność prostych (nr 1) i złożonych zapytań nr 5.

Zapytanie o ludności w Stanach Zjednoczonych według lat:

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

Zapytanie o medianę wieku na kraj na każdy rok, posortowane od najstarszego do najmłodszego:

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

Uwaga: wszystkie zapytania można znaleźć w moim repozytorium bitbucket (link na końcu artykułu).

Wyniki testów

Do uruchamiania zapytań użyłem dwóch różnych serwerów Postgres. Pierwsza ma 1 rdzeń procesora i 4 GB pamięci RAM, wspierane przez dysk SSD. Drugi ma 16 rdzeni procesora, 64 GB RAM i również korzystał z dysku SSD (drugi serwer ma 16-krotny potencjał procesora i pamięci RAM).

Należy również zwrócić uwagę, że podczas wykonywania testu bazy danych nie były obciążone. Stworzyłem je tylko do uruchamiania zapytań. W rzeczywistych sytuacjach zapytania będą trwać dłużej, ponieważ inne zapytania mogą być uruchamiane w tym samym czasie, a ponadto zapytania działające równolegle mogą blokować tabele. Do sprawdzania szybkości zapytań używałem pgAdmin3 i interfejsu internetowego BigQuery.

W moim teście uzyskałem te wyniki:

Pokażę te wyniki na wykresie słupkowym dla zapytania nr 1 i zapytania nr 5.

Uwaga: baza danych Postgres znajdowała się na serwerze w USA, a ja mieszkam w Europie, więc wystąpiło dodatkowe opóźnienie w przesyłaniu danych przez Postgres.

Wydajność BigQuery i wnioski

Na podstawie uzyskanych wyników doszedłem do następujących wniosków:

• W przypadku skalowania Postgresa w pionie, nawet 16x daje to tylko 10-25% wydajności przy uruchamianiu pojedynczego zapytania. Innymi słowy, serwer Postgres z tylko jednym rdzeniem CPU i 4 GB RAM wykonywał zapytania w czasie bardzo zbliżonym do czasu potrzebnego dla serwera z 16 rdzeniami CPU i 64 GB RAM. Oczywiście większe serwery mogą przetwarzać znacznie większe zestawy danych, jednak nie zapewnia to znacznej poprawy czasu wykonywania zapytań.
• Dla Postgresa sprzężenia z małymi tabelkami (tabela locations ma około 400 wierszy, a age_groups ma 100 wierszy) nie dają dużej różnicy w porównaniu z uruchamianiem zapytań pod zagregowanymi danymi znajdującymi się w jednej tabeli. Odkryłem również, że dla zapytań, które trwają od jednej do dwóch sekund, zapytania z wewnętrznymi sprzężeniami są szybsze, ale w przypadku zapytań długotrwałych sytuacja jest inna.
• W przypadku BigQuery z połączeniami jest zupełnie inaczej. BigQuery nie lubi sprzężeń. Różnica czasu między zapytaniami, które używają danych zagregowanych i niezagregowanych, jest dość duża (dla zapytań nr 3 i 5 USD wynosiła około dwóch sekund). Oznacza to, że w przypadku BigQuery możesz wykonać dowolną liczbę podzapytań, ale w celu uzyskania dobrej wydajności zapytanie powinno używać jednej tabeli.
• Postgres jest szybszy w przypadku zapytań korzystających z prostej agregacji lub filtrowania albo używających małego zestawu danych. Zauważyłem, że zapytania, które w Postgresie zajmują mniej niż pięć sekund, działają wolniej w BigQuery.
• BigQuery działa znacznie lepiej w przypadku długich zapytań. Wraz ze wzrostem różnicy w rozmiarze zbioru danych będzie wzrastać różnica w czasie wykonywania tych zapytań.

Kiedy korzystanie z BigQuery ma sens

Wróćmy teraz do głównego zagadnienia omówionego w tym artykule: kiedy należy używać Google BigQuery? Na podstawie moich wniosków sugerowałbym korzystanie z BigQuery, gdy spełnione są następujące warunki:

• Użyj go, gdy masz zapytania, które działają dłużej niż pięć sekund w relacyjnej bazie danych. Idea BigQuery polega na uruchamianiu złożonych zapytań analitycznych, co oznacza, że ​​nie ma sensu uruchamiać zapytań, które wykonują prostą agregację lub filtrowanie. BigQuery nadaje się do „ciężkich” zapytań, takich, które operują na dużym zbiorze danych. Im większy zbiór danych, tym większe prawdopodobieństwo uzyskania wydajności przy użyciu BigQuery. Zestaw danych, którego użyłem, miał tylko 330 MB (megabajty, nawet gigabajty).
• BigQuery nie lubi łączeń, więc powinieneś scalić swoje dane w jedną tabelę, aby uzyskać lepszy czas wykonania. BigQuery umożliwia zapisywanie wyników zapytania w nowej tabeli, więc aby utworzyć nową tabelę zagregowaną, po prostu prześlij wszystkie dane do BigQuery, uruchom zapytanie, które skonsoliduje wszystkie dane, i po prostu zapisz je w nowej tabeli.
• BigQuery jest dobre w scenariuszach, w których dane nie zmieniają się często i chcesz używać pamięci podręcznej, ponieważ ma wbudowaną pamięć podręczną. Co to znaczy? Jeśli uruchomisz to samo zapytanie, a dane w tabelach nie zostaną zmienione (zaktualizowane), BigQuery użyje tylko wyników z pamięci podręcznej i nie będzie próbowało ponownie wykonać zapytania. Ponadto BigQuery nie pobiera opłat za zapytania z pamięci podręcznej. Uwaga: zwracanie wyników nawet w przypadku zapytań w pamięci podręcznej zajmuje 1-1,2 sekundy.
• Możesz także użyć BigQuery, jeśli chcesz zmniejszyć obciążenie relacyjnej bazy danych. Zapytania analityczne są „ciężkie” i nadużywanie ich w relacyjnej bazie danych może prowadzić do problemów z wydajnością. Możesz więc w końcu zostać zmuszony do myślenia o skalowaniu serwera. Jednak dzięki BigQuery możesz przenieść te uruchomione zapytania do usługi innej firmy, aby nie wpływały na Twoją główną relacyjną bazę danych.

Na koniec jeszcze kilka słów o używaniu BigQuery w prawdziwym życiu. W naszym rzeczywistym projekcie dane do raportów zmieniały się co tydzień lub co miesiąc, więc mogliśmy ręcznie przesyłać dane do BigQuery. Jeśli jednak Twoje dane często się zmieniają, synchronizacja danych między relacyjną bazą danych a BigQuery może nie być taka prosta i warto o tym pamiętać.

Spinki do mankietów

Przykładowe dane użyte w tym artykule można znaleźć tutaj, natomiast zapytania i dane w formacie CSV są dostępne tutaj.