BigQuery dla początkujących. Jakie są sposoby przechowywania danych w BigQuery?

W ciągu mojej 15 letniej przygody z analityką i inżynierią danych miałem styczność z wieloma różnymi bazami i hurtowniami danych. Pracowałem na relacyjnych bazach danych typu MS SQL, MySQL, PostgreSQL, obiektowych bazach jak MongoDB czy rozwiązaniach hurtowni danych od SAS.

Od czasu pojawienia się rozwiązań analitycznych, w tym hurtowni danych w chmurze, miałem szczęście być częścią projektu analitycznego w pełni wdrażanego w Google Cloud Platform, co pozwoliło mi dość szybko, jak na rynek Polski, zaznajomić się z korzyściami płynącymi z tworzenia nowoczesnego stacku analitycznego przy pomocy chmury Google oraz hurtowni danych BigQuery, która leży w samym jej sercu.

Większość z was, pracujących od jakiegoś czasu z nowoczesną analityką i danymi na poziomie profesjonalnym zapewne zna już hurtownię danych BigQuery i jej zalety.

Ten artykuł jest przeznaczony głównie dla aspirujących lub początkujących analityków danych i inżynierów, myślących o rozpoczęciu swojej przygody z tzw. “Data Science”.

Zacznijmy od tego co to jest BigQuery oraz wymieńmy niektóre z kluczowych funkcjonalności hurtowni danych BigQuery i zobaczmy czym różni się ona od tradycyjnych baz danych.

Automatyczne skalowanie: Pracując z BigQuery nie musisz martwić się o konfigurację serwerów lub utrzymanie infrastruktury. Automatycznie skaluje ona możliwości chmury obliczeniowej tak, aby obsłużyć zestaw danych o dowolnym rozmiarze, dzięki czemu Ty możesz skupić się na na tym co najważniejsze - czyli analizie danych.
Bezserwerowość: BigQuery to rozwiązanie w pełni bezserwerowe, co oznacza, że nie musisz martwić się o planowanie pojemności, aktualizacje czy poprawki oprogramowania.
Przewaga kosztowa: BigQuery działa w modelu “pay as you go” znaczy to tyle, że płacisz za nią tyle ile faktycznie z niej korzystasz. Dzięki temu jest świetnym rozwiązaniem dla organizacji każdej wielkości, nie tworząc żadnych barier kosztowych dla małych biznesów.
Szybkość: BigQuery zapewnia błyskawiczną wydajność zapytań, co oznacza, że można szybko analizować dane i podejmować lepsze decyzje w oparciu o uzyskane informacje.
Integracja: BigQuery płynnie integruje się z innymi narzędziami i usługami Google Cloud Platform, dzięki czemu można go łatwo używać razem z innymi rozwiązaniami analitycznymi takimi jak Looker, Looker Studio, Cloud Storage, Vertex AI, Auto ML czy Tensorflow.Dodatkowo do BigQuery bardzo szybko uruchomisz replikację danych z programów Google, z których na co dzień korzystasz w pracy. Piszę tutaj o takich programach jak Google Ads, Google Analytics 4, Search Console, Youtube, Campaign Manager, Ads Manager i wiele innych.
Bezpieczeństwo: Dobrze skonfigurowana hurtownia danych BigQuery posiada wiele możliwości zarządzania bezpieczeństwem Twoich danych na poziomie datasetów, tabel, czy wierszy oraz monitorowaniem ich użytkowania przez każdego z waszych pracowników
Elastyczność w obsłudze: Z BigQuery możesz korzystać na wiele sposobów. Podstawowym z nich jest intuicyjny interfejs graficzny. Dla miłośników zarządzania kodem, można nią zarządzać przy pomocy Cloudshell lub innego oprogramowania typu “IaC” jak Terraform czy puppet.

Jak na klasyczną hurtownię danych przystało podstawowymi jej zadaniami są przechowywanie danych oraz umożliwianie ich dalszego wykorzystywania w celach biznesowych.

W pierwszej części napiszę więc na temat tego jakie są różne formy przechowywania tych danych oraz czym one różnią się od siebie.

Opiszę więc tabele, widoki, zmaterializowane widoki oraz funkcje i procedury ułatwiające pracę na ogromnych zapytaniach SQL.

Tabele

Tabela w hurtowni danych BigQuery to zbiór wierszy i kolumn, które mogą przechowywać ogromne ilości danych.

Tabele są nieodłączną cechą platformy BigQuery, która zapewnia przewagę nad tradycyjnymi bazami danych.

Jak Stworzyć tabelę w Google BigQuery?

Istnieje wiele różnych sposobów na utworzenie tabeli w BQ a ja przedstawię Ci dzisiaj kilka najprostszych.

Prostym sposobem na stworzenie tabeli w BigQuery jest użycie instrukcji DDL (Data Definition Language) , jak poniżej:

Innym sposobem na utworzenie tabeli w podobny sposób, ale przy użyciu bardziej GUI niż wyłącznie kodu, jest użycie wyników zapytania. Wystarczy uruchomić instrukcję SELECT, a następnie wykonać SAVE RESULTS → BigQuery Table.

Tabelę w BiGQuery stworzyć również możesz bezpośrednio przy pomocy Cloud Shell. Po zalogowaniu do Cloud Shell skorzystaj z poniższej komendy edytując swoje parametry projektu i datasetu.

				
					bq mk \
--table \
--expiration integer \
--description description \
--label key_1:value_1 \
--label key_2:value_2 \
project_id:dataset.table \
schema

Istnieją oczywiście inne sposoby tworzenia tabel w BiGQuery.
W praktyce Tabele w surowej warstwie hurtowni danych będą tworzone bezpośrednio przez procesy replikacyjne typu ELT/ETL – o których możesz poczytać więcej tutaj, lub przy pomocy DataForm – narzędzia do budowania i tworzenia tabel w warstwie produkcyjnej hurtowni danych, o których możesz poczytać tutaj.

Widoki

Widoki w GCP BigQuery to wirtualne tabele zdefiniowane przez zapytanie SQL, które mogą wyświetlać wyniki zapytania lub być używane jako baza dla innych zapytań.

Nie są one fizycznymi tabelami i nie przechowują żadnych danych a co za tym idzie nie generują kosztów.

Zamiast tego odwołują się do danych przechowywanych w innych tabelach. Wspaniałą cechą widoków jest to, że po utworzeniu widoku można nadal wykonywać zapytania do widoku w taki sam sposób, jak do tabeli.

Niektóre z dodatkowych cech widoków to:

Widoki nie są zmaterializowane, co oznacza, że nie przechowują danych, ale zamiast tego reprezentują zapytanie na górze tabeli.
Są tylko do odczytu i nie mogą być modyfikowane przez instrukcje DML.
Mogą być używane do kontrolowania dostępu do danych źródłowych.
Mogą być używane do upraszczania złożonych zapytań poprzez dzielenie ich na mniejsze, łatwiejsze w zarządzaniu części.
Mogą być używane do wyróżniania danych bazowych od użytkowników końcowych lub klientów.

W przeciwieństwie do tabel, widoki są często tworzone, aby służyć bardziej konkretnym celom, takim jak ograniczenie dostępu użytkowników do danych lub segmentacja złożonych zapytań. Dobrze zaprojektowana hurtownia danych wykorzystuje widoki we właściwym miejscu i czasie.

Jak stworzyć widok w BigQuery?

Podobnie jak w przypadku tabel najczęstszym sposobem tworzenia widoku w zbiorze danych jest instrukcja DDL – CREATE VIEW

Widoki od tabel odróżnisz ich graficzną reprezentacją w interfejsie, dzięki czemu od razu będziesz wiedział czy pracujesz na tabeli czy widoku.

Zmaterializowane widoki

Widok zmaterializowany w GCP BigQuery jest jak połączenie tabeli i widoku.

Przechowuje wyniki zapytania SQL jako strukturę podobną do tabeli w BigQuery, podobnie jak tabela przechowuje dane.

Jednak w przeciwieństwie do zwykłej tabeli, widoki zmaterializowane automatycznie aktualizują przechowywane dane wynikowe po zaktualizowaniu tabel bazowych w czasie rzeczywistym, bez konieczności ponownego uruchamiania zapytania.

Widok zmaterializowany można porównać do migawki wyniku zapytania, zapisywanej jako tabela i aktualizowanej za każdym razem, gdy dane bazowe są aktualizowane.

Może to być bardzo pomocne w przypadku dużych zestawów danych lub zapytań, których uruchomienie zajmuje dużo czasu, ponieważ można je wstępnie obliczyć w widoku zmaterializowanym, aby uzyskać do nich szybki dostęp.

Poniżej znajdziesz kilka podstawowych cech widoków zmaterializowanych w BigQuery:

Mogą być używane do poprawy wydajności zapytań dla złożonych i czasochłonnych zapytań.
Zmniejszają liczbę zapytań, które muszą być wykonywane na danych bazowych, oszczędzając w ten sposób koszty obliczeń.
Mogą być używane do buforowania zapytań na dużych zbiorach danych, które są często zadawane przez wielu użytkowników.
Są one aktualizowane okresowo lub na żądanie, a użytkownik może kontrolować częstotliwość i tryb aktualizacji.
Nie obsługują one wszystkich operacji SQL, takich jak ORDER BY lub LIMIT i mogą być tylko oparte na natywnej tabeli BigQuery (w przeciwieństwie np. do innego widoku lub tabeli zewnętrznej) dlatego ważne jest, aby znać ich ograniczenia przed ich użyciem.

Zasadniczo, widoki zmaterializowane oferują niskie lub zerowe koszty utrzymania i zwracają świeże dane za każdym razem i przy każdym zapytaniu, niezależnie od tego, jak niedawno zaktualizowano tabele bazowe.

Zmaterializowane widoki są najoptymalniejszym rozwiązaniem do zasilania Twoich narzędzi Business Intelligence jak Looker Studio, o którym więcej możesz przeczytać tutaj.

Użytkownikom końcowym zapewniają dostęp do najświeższych danych w narzędziu BI a z punktu widzenia hurtowni danych gwarantują minimalizację kosztów związanych z ich obsługą.

Jak stworzyć zmaterializowany widok w BigQuery?

Zmaterializowany widok tworzymy tak samo jak widok przy pomocy operacji DDL dodając słowo materialized przed słowem view.

Funkcje i Procedury

Funkcje i procedury (z ang. Users Defined Functions czyli UDF) to sposób na przechowywanie i wykonywanie instrukcji SQL lub JavaScript w GCP BigQuery.

Umożliwiają one napisanie fragmentu kodu wielokrotnego użytku, który można wywołać z dowolnego miejsca w zapytaniu.

Procedur składowanych można używać do obsługi złożonych procesów ETL, wykonywania wywołań API i przeprowadzania walidacji danych.

Osobiście procedury składowane są moją ulubioną funkcją BigQuery, dzięki której można automatyzować wiele codziennych zadań.

Jeśli korzystasz z Pythona lub znasz dowolny język programowania, możesz myśleć o nich jako o funkcjach zdefiniowanych przez użytkownika, w których możesz podać niestandardowe parametry, aby zwrócić żądane dane wyjściowe.

W podobny sposób procedury składowane mają następujące cechy:

Używają SQL jako języka programowania.
Są przechowywane w zestawie danych w określonym projekcie.
Ich wykonanie może być wyzwalane przez zapytanie lub funkcję zdefiniowaną przez użytkownika.
Umożliwiają parametryzację zapytań, co może być wykorzystane do uczynienia kodu bardziej modułowym jednocześnie zmniejszając jego nadmiarowość.
W procedurach można używać logiki, takiej jak instrukcje warunkowe, pętle i wyjątki.

Jak zapewne sobie wyobrażacie przypadki ich zastosowania są niezliczone.

Poniżej napiszę kilka z nich tak aby rozjaśnić Ci w jakich sytuacjach warto po nie sięgnąć.

Walidacja danychDzięki nim można walidować pola danych, typy danych i ograniczenia dotyczące wprowadzanych danych w celu utrzymania jakości danych.
Złożone transformacje danychUmożliwiają wykonywanie bardziej złożonych i powtarzalnych transformacji danych na dużych zbiorach danych.
Zautomatyzowana archiwizacja danychKorzystając z funkcji i procedur, można łatwo zaplanować powtarzalne zadania, takie jak automatyczna archiwizacja danych lub usuwanie danych w dużych bazach danych.
Poprawa wydajnościKorzystając z funkcji i procedur, można łatwo zaplanować powtarzalne zadania, takie jak automatyczna archiwizacja danych lub usuwanie danych w dużych bazach danych. Procedury składowane mogą poprawić wydajność zapytań i operacji.
Zwiększone bezpieczeństwoProcedury składowane mogą być wykorzystywane do ograniczania dostępu do wrażliwych danych, zezwalając tylko uwierzytelnionym użytkownikom na wykonywanie określonych zapytań i kodu.

Jak stworzyć funkcję w BigQuery?

Z pomocą znowu przyjdzie nam komenda DDL : Create or replace. W tym przypadku wpisujemy Create or replace function a następnie w nawiasie podajemy jej parametry. Po fragmencie tekstu AS w nawiasie opisujemy co funkcja ma robić.

Poniżej przykład, w którym raz napisany CASE może być później łatwo stosowany we wszystkich naszych zapytaniach co oszczędza nam sporo czasu podczas codziennej pracy.

W tym artykule omówiliśmy niektóre z kluczowych zalet BigQuery i kilka powszechnie używanych metod przechowywania danych.

Wiele z tych pojęć może być ci już znanych, jednak mogło to być również dobre podsumowanie tego, jaki różne obiekty danych są dostępne w BigQuery i kiedy z nich korzystać.

W kolejnej części opiszę pozostałe kluczowe funkcjonalności BigQuery.

Dokładne zapoznanie się z jej podstawami pomoże Ci zbudować optymalną kosztowo, bezpieczną i w pełni skalowalną hurtownię danych gotową by wspierać najważniejsze zadania analityczne Twojego biznesu.

Zapraszam Cię również do obejrzenia naszej serii filmów szkoleniowych na YouTube gdzie od podstaw wyjaśniam hurtownię danych BigQuery.

Arkady Zagdan

Analityk z 15 letnim doświadczeniem w branżach usługowych, e-commerce oraz SaaS. W Bigglo odpowiedzialny za strategię i wizję produktów typu Business Intelligence.