Google BiqQuery Część II. 7 Kroków Wtajemniczenia do Poziomu (Prawie) Mistrzowskiego

W poprzednim artykule omówiliśmy, czym jest BigQuery i jak zacząć pracę z tym narzędziem. Czas przejść do następnego poziomu wtajemniczenia i załadować trochę prawdziwych danych!

W tej części zaczniemy od pobrania danych ze strony fundacji Wikimedia, która udostępnia wiele użytecznych zbiorów, zainstalujemy Google Cloud SDK, utworzymy własny zbiór danych Google Cloud Storage, gdzie następnie załadujemy dane i przejdziemy do akcji w BigQuery. Na koniec poznacie alternatywne sposoby ładowania danych, takie jak  Matillion ETL czy Apache Airflow oraz dowiecie się, jak zintegrować BigQuery z zewnętrznymi narzędziami.

Dane, na których będziemy pracować pobierzcie stąd: https://dumps.wikimedia.org/other/pageviews/. Jest to zbiór Pageviews podzielony na pliki godzinowe.

Do dzieła!

Krok 1: Instalacja Google Cloud SDK

Chociaż Google Cloud Platform pozwala nam wykonać większość poleceń z poziomu konsoli webowej, warto ściągnąć i zainstalować narzędzia linii poleceń dostępne w ramach Google Cloud SDK.

Zestaw narzędzi Google Cloud oparty jest o Pythona. Dlatego zanim przystąpimy do pobrania SDK, musimy najpierw się upewnić, że zainstalowaliśmy odpowiednią wersję Pythona. „Dobra” będzie wersja 2.7.9 lub wyższa, ale uwaga – Python 3 NIE jest wspierany!

Dla ułatwienia podaję odpowiednie linki do instalacji:

• Python 2.7 – https://www.python.org/download/releases/2.7/
• Google Cloud SDK – https://cloud.google.com/sdk/

Krok 2: Utworzenie Google Cloud Storage bucket

Podobnie jak w przypadku większości chmurowych silników analitycznych (AWS Redshift, Azure SQL Data Warehouse), tak i tu najszybszym sposobem na załadowanie danych jest ich import z plików tekstowych. W takim scenariuszu możemy liczyć na wydajne, równoległe ładowanie.

Zaczynamy od założenia nowego zbioru danych  usługi Google Cloud Storage.

Logujemy się do konsoli Google Cloud Platform i z menu głównego przechodzimy do Storage, a następnie do Create a bucket. W ten sposób tworzymy tak zwany bucket, czyli podstawowy zbiór danych w nomenklaturze Google Cloud Storage (więcej informacji na ten temat w języku angielskim znajdziecie w oficjalnej dokumentacji Google’a).

Tworząc bucket, pamiętajmy, że jego nazwa musi być globalnie unikalna. Do naszych zastosowań wystarczy klasa Regional obejmująca wszystkie kopie danych przechowywane w jednym regionie. Same dane umieścimy w belgijskim centrum danych europe-west1.

Teraz możemy przystąpić do załadowania plików.

Krok 3: Załadowanie Plików z Danymi na Google Cloud Storage

Do załadowania poprzednio pobranych plików Pageviews na Cloud Storage możemy użyć zarówno GUI (przycisk UPLOAD FILES) jak i następujących komend z SDK:

gcloud auth login

gsutil -m cp pageviews* gs://bqsourcebucket

Pierwsza z nich wywołuje okienko logowania do konsoli Google Cloud Platform. Po wpisaniu naszych poświadczeń możemy już wykonywać właściwe operacje – w tym przypadku z użyciem narzędzia gsutil służącego do zarządzania Google Cloud Storage.

• Po prefiksie gs:// podajemy pełną ścieżkę do umieszczenia danych – w naszym przypadku jest to po prostu główny katalog zbioru bqsourcebucket.
• Uzupełnienie komendy cp o opcję -m wymusza z kolei równoległe wykonanie operacji ładowania.

Kiedy pliki będą już dostępne w Google Cloud Storage, możemy przystąpić do ich właściwego ładowania do BigQuery.

Krok 4: Ładowanie Danych w Google BigQuery

Pora na działanie w BigQuery. Przechodzimy do interfejsu użytkownika, wpisując adres https://bigquery.cloud.google.com/ lub wybierając odpowiednią pozycję w głównym menu Google Cloud Platform.

Naszą pracę w BigQuery zaczynamy od utworzenia nowego datasetu – kolekcji tabel, którą nazwiemy bq_load_demo. Klikając na symbol trójkąta obok nazwy naszego projektu, wybieramy opcję Create new dataset. Po utworzeniu kolekcji klikamy na analogiczny symbol obok nazwy datasetu i wybieramy opcję Create new table.

Główny ekran Create Table to miejsce, gdzie definiujemy proces ładowania danych.

Podajemy tu po kolei źródło danych (Location = Google Cloud Storage) wraz z odpowiednią ścieżką, nazwę docelowej tabeli oraz jej schemat.

Wyłączamy opcję Schema -> Automatically detect. Zamiast tego dokładnie podamy schemat naszych danych. W tym celu klikamy na znajdujący się nieco niżej przycisk Edit as Text i wprowadzamy listę kolumn wraz z typami danych:

domain_code:STRING,page_title:STRING,count_views:INTEGER,total_response_size:INTEGER

Ponieważ w źródłowych plikach kolumny są rozdzielone spacją, a nie przecinkiem czy średnikiem, musimy jawnie wskazać spację jako separator (Field Delimiter -> Other).

Wydawało by się, że to już wszystko – wiemy, skąd ładujemy dane i w jakim schemacie, a także jakim znakiem rozdzielone są poszczególne kolumny. Jednak jeśli uruchomimy zadanie, zakończy się ono niepowodzeniem – część wierszy wyłamuje się bowiem z podanego schematu.

Gdy pracujemy z logami czy innego rodzaju danymi tekstowymi, musimy być przygotowani na taki scenariusz.

Na szczęście w BigQuery mamy opcję Number of errors allowed – przy wstępnym ładowaniu danych polecam podanie tutaj całkiem dużej wartości, np. 10000.

Wreszcie wciskamy przycisk Start table – uruchomi on odpowiednie zadanie ładujące dane. Po jego zakończeniu możemy już odpytywać świeżo utworzoną tabelę pageviews.

Oto wynik naszej pracy:

A co z automatyzacją?

Gdybyśmy chcieli zautomatyzować ładowanie danych za pomocą skryptów linii poleceń, z pomocą przyjdzie nam jeszcze jedno polecenie z Google Cloud SDK – bq. Jego dokumentacja zawiera wiele pożytecznych przykładów, od których możemy zacząć: https://cloud.google.com/bigquery/docs/bq-command-line-tool.

Krok 5: Zapytania na Utworzonej Tabeli

Czas na odpytanie naszej tabeli. Poniżej zamieściłem proste zapytanie, które wyświetla 10 najpopularniejszych wersji Wikipedii.

Zwróćmy uwagę na konstrukcję #legacySQL w pierwszym wierszu. Otóż BigQuery wspiera dwa dialekty SQL – włączony domyślnie tzw. Legacy SQL, z konstrukcjami specyficznymi dla BigQuery, oraz Standard SQL – zgodny ze standardem ANSI SQL:2011. Wspomniałem już o tym w pierwszej części artykułu.

Na szczęście możemy się między nimi łatwo przełączać, odznaczając w opcjach zapytania (Show Options) pole wyboru SQL Dialect: Use Legacy SQL lub (jak to zrobiliśmy) jawnie podając na początku zapytania odpowiednio #legacySQL lub #standardSQL.

Krok 6: Inne Sposoby Ładowania Danych

Wiemy już jak ładować dane za pomocą interfejsu graficznego, wspomnieliśmy też o komendzie linii poleceń bq. Jednak to oczywiście nie wszystko, co oferuje w tym zakresie Google Cloud Platform. Nie jestem w stanie opowiedzieć o wszystkich możliwościach narzędzia (jeśli chcielibyście wiedzieć więcej, zapraszam na nasze mistrzowskie szkolenie Google Cloud Platform), ale zaprezentuję kilka z nich, które uważam za najciekawsze.

DataFlow

Dla osób o zacięciu programistycznym ciekawą opcją będzie DataFlow – oparte o projekt Apache Beam rozwiązanie Google’a łączące w jednym modelu przetwarzanie wsadowe i strumieniowe. Wymaga ono dobrej znajomości Javy lub Pythona. Po przebrnięciu przez koncepcje takie jak PCollection czy PTransforms zyskujemy do dyspozycji potężne i skalowalne narzędzie.

Początek przygody z DataFlow możemy sobie trochę ułatwić, korzystając z przygotowanych przez Google szablonów. Nas interesować będzie zwłaszcza jeden z nich – GCS Text to BigQuery. Po jego wybraniu (Dataflow -> Create jobs from template) musimy podać całkiem sporo parametrów – m.in. lokalizację źródłowych danych, pliku z ich schematem oraz, co ważne, funkcję w języku JavaScript przekształcającą wiersze danych do docelowej, gotowej do ładowania do BigQuery, postaci.

W najprostszym scenariuszu funkcja ta może polegać na parsowaniu wiersza danych i zwróceniu wyniku w formacie JSON z  wartościami kolumn w postaci typów. W bardziej zaawansowanych zastosowaniach możemy użyć jej do dodatkowej transformacji czy do czyszczenia danych.

Apache Airflow i Matillion ETL

DataFlow nie jest najłatwiejszym narzędziem w obsłudze, zwłaszcza dla osób ze świata SQL niebędących programistami. Dlatego też warto mieć na uwadze inne opcje – darmowy Apache Airflow, który możemy z łatwością uruchomić na maszynie wirtualnej Google Compute Engine, oraz komercyjny Matillion ETL for BigQuery, dostępny z Cloud Launchera, wbudowanego w GCP „sklepu” z gotowymi do użycia rozwiązaniami.

Wspomnę tylko, że uruchamiając Airflow na GCP warto wybrać maszynę wirtualną z Ubuntu 16.04 LTS, a przy samej instalacji podać dodatkową opcję [gcp_api], aby doinstalować pakiety odpowiadające za integrację z chmurą Google:

pip install airflow[gcp_api]

Przykładowy scenariusz użycia Airflow z BigQuery znajdziemy w tutorialu Google’a: https://cloud.google.com/blog/big-data/2017/07/how-to-aggregate-data-for-bigquery-using-apache-airflow.

Jeśli jesteśmy przyzwyczajeni do klasycznych narzędzi ETL/ELT, gdzie całość zadań możemy wykonać z użyciem graficznego interfejsu, warto spróbować wspomnianego już wcześniej Matillion. Osoby mające doświadczenie np. z SQL Server Integration Services od razu poczują się jak w domu – mamy tu do dyspozycji pokaźny zbiór transformacji, które układamy w graficzne przepływy i w razie potrzeby zagnieżdżamy.

Sporą wadą Matillion jest jego koszt. Jest to rozwiązanie komercyjne, a więc do ceny maszyny wirtualnej doliczana jest automatycznie dodatkowa kwota za licencję. Z drugiej strony, nic nie stoi na przeszkodzie, żeby obniżyć koszt przez automatyzację uruchamianie środowiska tylko w określonych godzinach, np. w nocy, kiedy wykonujemy codzienne ładowanie przyrostowe do hurtowni.

Matillion ETL for BigQuery jest dostępny z Google Cloud Launchera pod adresem: https://console.cloud.google.com/launcher/details/matillion-etl-for-bq-public/matillion-etl-for-bigquery.

Krok 7: Integracja Google BigQuery z Zewnętrznymi Narzędziami

Kiedy mamy już odpowiednio przygotowane i zamodelowane dane, chcemy oczywiście udostępnić je naszym użytkownikom biznesowym. Nie możemy oczekiwać, że będą samodzielnie pisać zapytania SQL do hurtowni – skorzystamy raczej z odpowiedniego narzędzia raportowo-analitycznego.

• W pierwszej części artykułu wspomnieliśmy o Google Data Studio – i jest to pierwsze, najbardziej oczywiste narzędzie, które powinniśmy wypróbować w zakresie wizualizacji danych z BigQuery.
• Integracja z innymi zewnętrznymi narzędziami jest możliwa przede wszystkim z użyciem sterowników ODBC i JDBC. Dla BigQuery zostały one przygotowane przez Simba Technologies i są dostępne do pobrania z https://cloud.google.com/bigquery/partners/simba-drivers/.
• Sterownik JDBC będzie bardzo przydatny, jeśli będziemy chcieli podpiąć BigQuery z zewnętrznym środowiskiem programistycznym opartym na Javie – np. DataGrip. Ponieważ korzystanie z ODBC wiąże się z pewnymi ograniczeniami, jeśli nasze docelowe narzędzie ma natywny konektor do BigQuery, to warto w pierwszej kolejności skorzystać z niego. Konektor taki posiada na pokładzie np. Tableau oraz Power BI Desktop.
• Co ciekawe, chmurowy ETL Microsoftu, Azure Data Factory, również ma wbudowany konektor do BigQuery. Nic więc nie stoi na przeszkodzie żeby włączyć dane przetwarzane na BigQuery do istniejących procesów ETL/ELT uruchamianych w Azure – i cieszyć się w ten sposób prawdziwym Multi-Cloud!

Gratulacje!! Poziom (Prawie) Mistrzowski został odblokowany

Mamy nadzieję, że zainspirowaliśmy Was do wypróbowania Google BigQuery. W oparciu o przykłady, którymi się podzieliliśmy, możecie śmiało zacząć eksperymentować z własnymi danymi. Można powiedzieć, że jesteście na dobrej drodze do osiągnięcia poziomu prawie mistrzowskiego w obsłudze hurtowni danych Google’a? .

Jeśli jednak uważacie, że 7 kroków wtajemniczenia to stanowczo za mało i chcielibyście lepiej poznać tę usługę, zapraszamy Was na SUPER SZKOLENIE, na którym dowiecie się WSZY-STKIE-GO o Google Cloud Platform, w tym BigQuery.

A jak się zapisywać? Ano tak.