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

10.2022 | Google CloudSQL | Google BigQuery | Budowa bazy analitycznej | Przeczytasz w 7 min.

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:

• Wykorzystać pakiet Google Cloud SDK Shell (dostępny tutaj: https://cloud.google.com/sdk/docs/install)

• 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', '[email protected]', now(), now());

INSERT INTO accounts (username, password, email, created_on, last_login) values ('robin', 'qwerty', '[email protected]', now(), now());

INSERT INTO accounts (username, password, email, created_on, last_login) values ('joker', 'qwerty', '[email protected]', 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.

6/ Po kliknięciu CREATE CONNECTION w ramach interfejsu BigQuery, pod nazwą naszego projektu, zobaczymy pozycję External connections:

Zapytanie pobierające dane - External Connections i Federated Query

7/ Zbudujmy zatem zapytanie, które będzie wykonywane w ramach External connections

Do budowy zapytania wykorzystamy tzw. Federated Query.

Dosłowna definicja brzmi następująco:

A federated query is a way to send a query statement to an external database and get the result back as a temporary table. Federated queries use the BigQuery Connection API to establish a connection with the external database.

Dokumentacja jest dostępna tutaj: https://cloud.google.com/bigquery/docs/federated-queries-intro

Federated Query jest to zapytanie wysłane i wykonywane do zewnętrznej bazy danych. Jego realizacja następuje jednak z bazy BigQuery i w tej bazie mogą być też zapisane wyniki zapytania Federated Query.

Przykład zapytania do naszej  bazy Cloud SQL o nazwie ecommerce i tabeli accounts

Aby wykonać zapytanie klikamy na pozycję External connections, wybieramy połączenie zewnętrzne (utworzone przed chwilą) i następnie klikamy ikonę QUERY (w prawym górnym rogu):

8/ W interfejsie Query wykonujemy następujące czynności:

• Wpisujemy zapytanie, które pobierze nam dane typu imię oraz email i hasło z bazy ecommerce i tabeli accounts:

Przykład zapytania:

SELECT * FROM EXTERNAL_QUERY("nazwa_projektu.europe-west3.ecommerce_sync", "SELECT username, password, email FROM accounts;");

Wynik wykonania zapytania:

9/ Cykliczne wykonanie zapytania

Niniejsze zapytanie chcielibyśmy wykonywać co godzinę, tak aby dodawać nowe rekordy np.. Dodane w przeciągu ostatniej godziny. W tym celu modyfikujemy zapytanie do poniższej postaci:

SELECT * FROM EXTERNAL_QUERY("ntfy-migration-test.europe-west3.ecommerce_sync", "SELECT username, password, email, created_on FROM accounts WHERE created_on > NOW() - '1 hour'::interval;");

Rezultatem są tylko dwa świeżo dodane rekordy (starsze rokordy nie są pobierane).

Cykliczne wykonywanie zapytania

Aby zapytanie było cyklicznie wykonywane, to wybieramy pozycję SCHEDULE i następnie Create new scheduled query:

W okienku konfiguracji wpisujemy potrzebne dane:

Tworzymy również odpowiednik - tabelę - po stronie BigQuery (jest to wpisywane w dodatkowej planszy, dostepnej w bloku Create dataset):

Klikamy SAVE.

10/ Nasze zapytanie zostaje zapisane w liście Scheduled queries. Tutaj oczekuje na wykonanie w ciągu najbliższej godziny i następnie co każdą kolejną godzinę.

Widok na zapytanie:

11/ Panel zarządzania zapytaniem

12/ Wynik wykonania zapytania.

W pierwszym wykonaniu zapytania zobaczymy nastepujące dane:

Sprawdźmy czy są obecne dane:

SELECT * FROM `nazwa_projektu.accounts_analysis_db.accounts` LIMIT 1000

Rezultat

Jako wynik mamy:

• Utworzoną bazę produkcyjną opartą o PostgreSQL
• Utworzona bazę analityczną BigQuery
• Synchronizację wybranych danych w interwale godzinnym:
  • Dodawane do bazy są tylko rekordy, które pojawiły się w ciągu ostatniej godziny

Połączenie z Microsoft Power BI

Pozostaje zatem podłączenie do bazy narzędziem BI np. Tableau, Microsoft Power BI i nastepnie mamy mozliwość swodobnej analizy danych.

Poniżej przykład połączenia z wykorzystaniem Microsoft Power BI

Po otwarciu Power BI jako źródło danych wybieramy Google BigQuery. Po wyborze i autoryzacji connectora dla BigQuery, zobaczymy projekt, wraz z tabelą accounts:

Po wgraniu danych, mamy widok na tabelę z danymi BigQuery w interfejsie Power BI:

Możemy teraz rozpocząć standardowe analizy danych w narzędziu BI m.in. tworzenie raportów, prognoz etc. Kolejne dane będą automatycznie dodawane w interwałach godzinnych.