Blog Analityczny. Narzędzia. Techniki. Rozwiązania Analityczne.

Synchronizacja danych pomiędzy bazą produkcyjną Google Cloud SQL a bazą analityczną Google BigQuery

10.2022 | Google CloudSQL | Google BigQuery | Budowa bazy analitycznej

Tło problemu

Wyobraźmy sobie sytuacje, że w firmie z którą pracujemy, mamy dużą bazę produkcyjną, tradycyjnie oparta o SQL-a. W bazie tej są zawarte dane zarówno transakcyjne (istotne dla naszych analiz), jak i sporo danych, które dla naszych analiz nie mają znaczenia.

Praca z taką bazą jest trudna z uwagi na fakt, iż duże ilości niepotrzebnych danych (tabel) powodują, że całość nie jest czytelna, dodatkowo musimy przebijać się przez dane, które nie mają dla nas znaczenia. Nie wspominając już o fakcie, że takiej bazy produkcyjnej nie wykorzystamy jako źródła danych dla narzędzia BI typu Tableau czy Microsoft Power BI (z uwagi m.in. na wydajność, bezpieczeństwo).

Reasumując, możliwości analityczne są znacznie ograniczone i związane z nadmiarowymi kosztami.

Rozwiązaniem dla takiej sytuacji może być stworzenie bazy analitycznej, która jest zasilana wybranymi danymi z bazy produkcyjnej. Najszybciej można to zrealizować, jeżeli infrastrukturę mamy oparta o chmurę np. Google Cloud.

Rozważmy zatem następujący przypadek:

  • Jako główną bazę danych (bazę produkcyjną) wybieramy Google Cloud SQL
  • Jako bazę analityczną wybieramy Google BigQuery

Schematycznie wygląda to następująco:

W kolejnych punktach, niniejszego posta, pokażemy kolejno jak:

  • Utworzyć bazę Google Cloud SQL (opartą o silnik PostgreSQL)
  • Utworzyć bazę Google BigQuery
  • Połączyć obie bazy mechanizmem synchronizacji, który pobiera z bazy Cloud SQL do bazy BigQuery dane dodane w ostatniej godzinie (model przyrostowy)
  • Połączyć Microsoft Power BI z Google BigQuery

Utworzenie bazy głównej Google Cloud SQL

Jest to nasza baza produkcyjna, w której utrzymujemy wszystkie dane związane z naszym serwisem www.

Aby utworzyć bazę Cloud SQL, wykonujemy następujące kroki:

1/ Po zalogowaniu się do Cloud SQL, z nawigacji po lewej stronie serwisu, wybieramy pozycję SQL:

2/ Wyświetli nam się widok z dostępnymi instancjami Cloud SQL. Wybieramy w nim opcję +Create Instance. Jest to opcja utworzenia kolejnej bazy danych.

3/ W widoku Choose your database engine, dokonujemy wyboru pomiędzy dostępnymi silnikami baz danych dla naszej zakładanej instancji:

  • MySQL
  • PostgreSQL
  • SQL Server

W naszym przypadku, wybierzmy PostgreSQL.

4/ Po wyborze silnika bazy danych, w ramach naszej instancji, otrzymamy ekran ze szczegółową konfiguracją dla naszej instancji.

W tym widoku dokonujemy następujących ustawień:

  • Nazwa naszej instancji
  • Hasło do naszej instancji
  • Wersja bazy np. Dla PostgreSQL wybieramy najnowszą wersję => 14
  • Typ konfiguracji:
    • Produkcyjna => wydajne rozwiązanie do obsługi rzeczywistego ruchu, a zatem droższy koszt utrzymania takiej bazy
    • Developerska => słabsze komponenty, ale zredukowany koszt utrzymania bazy
  • Region w którym baza będzie się znajdować - w przypadku Polski / Unii Europejskiej wybieramy np. Region europe-west3 (Frankfurt)
  • Dodatkowy region (multiple zones), w którym nasza baza zostanie uruchomiona w przypadku awarii wybranego, podstawowego regionu
  • Opcjonalna dalsza konfiguracja usługi w zakresie typu maszyny, dysku etc.

Po wyborze wszystkich parametrów klikamy przycisk Create Instance.

5/ Utworzenie instancji Cloud SQL

Na ekranie pojawi się panel kontrolny, opisujący naszą instancję:

  • Statystyki serwera bazy danych
  • Dane do połączenia
  • Panel do zarządzania startem / stopem / restartem bazy danych

6/ Oczywiście na start instancja jest pusta, musimy więc przejść do utworzenia bazy danych oraz tabel z danymi.

Aby to zrobić możemy skorzystać np. z jednej z dwóch metod:

  • Aktywować Cloud Shell, dostępny z poziomu przeglądarki (wykorzystujemy do tego ikonkę konsoli widoczną w prawym górnym rogu)

7/ W rozpatrywanym przypadku wykorzystamy interfejs Google Cloud SDK Shell

W linii komend wpisujemy:

  • gcloud sql connect datawarehouse-v3 --user=postgres --quiet

Gdzie:

  • Datawarehouse-v3 to nazwa naszej bazy
  • Postgres to nazwa użytkownika

Następnie podajemy hasło (utworzone wcześniej - hasło do instancji bazy danych).

Finalnie widzimy linię komend PostgreSQL:

8/ Najważniejsze komendy, które umożliwiają poruszanie sie w linii komend PostgreSQL:

  • \l - wylistowanie dostępnych baz
  • \c - podłączenie się do konkretnej bazy
  • \dt - wylistowanie tabel w ramach danej bazy

9/ Utworzenie bazy danych

CREATE DATABASE ecommerce;

10/ Utworzenie testowej tabeli w bazie danych

\c ecommerce => łączymy się z bazą ecommerce

Następnie tworzymy tabele:

CREATE TABLE accounts (
user_id serial PRIMARY KEY,
username VARCHAR ( 50 ) UNIQUE NOT NULL,
password VARCHAR ( 50 ) NOT NULL,
email VARCHAR ( 255 ) UNIQUE NOT NULL,
created_on TIMESTAMP NOT NULL,
last_login TIMESTAMP
);

Zbiorczy widok wydanych komend:

Na bazie komendy SELECT widzimy, że w bazie brakuje danych. Dodajmy zatem trzy przykładowe rekordy:

Ogólny kształt polecenia dodania danych wygląda następująco:

INSERT INTO table_name(column1, column2, …) VALUES (value1, value2, …);

W naszym przypadku wpisujemy przykładowe komendy:

INSERT INTO accounts (username, password, email, created_on, last_login) values ('batman', 'qwerty', 'batman@batman.op', now(), now());

INSERT INTO accounts (username, password, email, created_on, last_login) values ('robin', 'qwerty', 'robin@robin.op', now(), now());

INSERT INTO accounts (username, password, email, created_on, last_login) values ('joker', 'qwerty', 'joker@joker.op', now(), now());

Jako wynik otrzymamy tabelę z trzema rekordami:

Uwaga: wykonywanie zapytań typu SELECT * na bazach produkcyjnych (i nie tylko), nie jest najlepszą praktyką, gdyż w przypadku znacznych zbiorów danych, takie zapytanie może wpłynąć widocznie na koszty oraz chwilową wydajność odpytywanej bazy danych. Niniejszy artykuł ma jednak konkretny cel edukacyjny i tym samym ignorujemy pewne "dobre praktyki", które mogą wydłużyć niniejszy wywód.

Finalnie: baza Cloud SQL jest gotowa. Możemy ją wykorzystać do zbierania danych produkcyjnych.

Utworzenie bazy analitycznej BigQuery

W drugiej części niniejszego artykułu przejdziemy do utworzenia bazy analitycznej opartej o BigQuery.

1/ W interfejsie Google Cloud przechodzimy do pozycji BigQuery

2/ Jeżeli nie ma jeszcze utworzonej żadnej bazy BigQuery w ramach rozpatrywanego projektu, to jako pierwszy zobaczymy poniższy ekran. Należy na nim wybrać opcję ENABLE.

3/ Zobaczymy widok interfejsu BigQuery - poniżej jest widok, w którym są już utworzone instancje BigQuery.

4/ W widocznym interfejsie BigQuery klikamy opcję + ADD DATA i następnie wybieramy "Connections to external data sources"

5/ Wypełniamy parametry połączenia ze źródłem zewnętrznym, w szczególności należy tu wpisać dane utworzonej przed chwilą bazy Cloud SQL. Pamiętajmy również, aby wybrać właściwy dialekt - w rozpatrywanym przypadku jest to PostgreSQL.