Struktura danych BigQuery w Google: jak zacząć korzystać z przechowywania w chmurze

Google BigQuery to usługa przechowywania w chmurze, która pozwala gromadzić wszystkie dane w jednym systemie i łatwo je analizować za pomocą zapytań SQL. Aby praca z danymi była wygodna, powinna mieć odpowiednią strukturę. W tym artykule wyjaśnimy, jak tworzyć tabele i zbiory danych do przesłania do Google BigQuery.

Aby korzystać z Google BigQuery, musisz utworzyć projekt w Google Cloud Platform (GCP). Po rejestracji otrzymasz dostęp do wszystkich produktów Cloud Platform podczas bezpłatnego okresu próbnego i 300 USD do wydania na te produkty w ciągu najbliższych 12 miesięcy.

Po utworzeniu projektu w Google Cloud Platform musisz dodać co najmniej jeden zbiór danych do Google BigQuery.

Zestaw danych to kontener najwyższego poziomu, który służy do organizowania i kontrolowania dostępu do danych. Mówiąc prościej, jest to rodzaj folderu, w którym przechowywane są Twoje informacje w postaci tabel i widoków.

Otwórz projekt w GCP, przejdź na kartę BigQuery i kliknij Utwórz zbiór danych :

W oknie, które zostanie otwarte, określ nazwę zestawu danych i okres trwałości tabeli. Jeśli chcesz, aby tabele z danymi były usuwane automatycznie, określ dokładnie kiedy. Lub pozostaw domyślną opcję Bezterminowe, aby tabele można było usuwać tylko ręcznie.

Pole Lokacja przetwarzania jest opcjonalne. Domyślnie jest ustawiony na wieloregion USA. Więcej informacji o regionach do przechowywania danych znajdziesz w sekcji pomocy.

Po utworzeniu zbioru danych należy dodać tabelę, do której będą gromadzone dane. Tabela to zestaw wierszy. Każdy wiersz składa się z kolumn, które są również nazywane polami. W zależności od źródła danych istnieje kilka sposobów tworzenia tabeli w BigQuery:

• Ręcznie utwórz pustą tabelę i skonfiguruj dla niej schemat danych
• Utwórz tabelę na podstawie wyniku wcześniej obliczonego zapytania SQL
• Prześlij plik ze swojego komputera (w formacie CSV, AVRO, JSON, Parquet, ORC lub Arkusze Google)
• Zamiast pobierać lub przesyłać strumieniowo dane, możesz utworzyć tabelę odwołującą się do zewnętrznego źródła: Cloud Bigtable, Cloud Storage lub Google Drive.

W tym artykule przyjrzymy się bliżej pierwszej metodzie: ręcznemu tworzeniu tabeli.

Krok 1 . Wybierz zbiór danych, do którego chcesz dodać tabelę, a następnie kliknij Utwórz tabelę :

Krok 2. W polu Źródło wybierz Pusta tabela , a w polu Typ tabeli wybierz Tabela w natywnym formacie obiektu docelowego. Wymyśl nazwę stołu.

Ważne : nazwy zestawów danych, tabel i pól muszą być zapisane znakami łacińskimi i zawierać tylko litery, cyfry i podkreślenia.

Krok 3 . Określ schemat tabeli. Schemat składa się z czterech komponentów: dwóch obowiązkowych (nazwa kolumny i typ danych) i dwóch opcjonalnych (tryb kolumny i opis). Odpowiednio dobrane typy i tryby pól ułatwią pracę z danymi.

Przykładowy schemat w BigQuery:

Nazwy kolumn
W nazwie kolumny należy określić parametr, za który odpowiada każda kolumna: data, user_id, produkty itp. Tytuły mogą zawierać tylko litery łacińskie, cyfry i podkreślenia (maksymalnie 128 znaków). Identyczne nazwy pól nie są dozwolone, nawet jeśli ich wielkość liter jest inna.

Typ danych
Tworząc tabelę w BigQuery, możesz użyć następujących typów pól:

Tryby
BigQuery obsługuje następujące tryby kolumn tabeli:

Uwaga : Nie trzeba wypełniać pola Tryb. Jeśli nie określono trybu, domyślną kolumną jest NULLABLE.

Opisy kolumn
Jeśli chcesz, możesz dodać krótki opis (nie więcej niż 1024 znaki) dla każdej kolumny w tabeli, aby wyjaśnić, co oznacza dany parametr.

Gdy tworzysz pustą tabelę w BigQuery, musisz ustawić schemat ręcznie. Można to zrobić na dwa sposoby:
1. Kliknij przycisk Dodaj pole i wpisz nazwę, typ i tryb dla każdej kolumny.

2. Wprowadź schemat tabeli jako tablicę JSON, używając przełącznika Edytuj jako tekst .

Ponadto Google BigQuery może korzystać z automatycznego wykrywania schematu podczas ładowania danych z plików CSV i JSON.

Ta opcja działa na następującej zasadzie: BigQuery wybiera losowy plik z określonego przez Ciebie źródła, skanuje w nim do 100 wierszy danych i wykorzystuje wyniki jako reprezentatywną próbkę. Następnie sprawdza każde pole w przesłanym pliku i próbuje przypisać mu typ danych na podstawie wartości w próbce.

Podczas ładowania plików Google BigQuery może zmienić nazwę kolumny, aby była zgodna z własną składnią SQL. Dlatego zalecamy przesyłanie tabel z angielskimi nazwami pól; na przykład, jeśli nazwy są w języku rosyjskim, system zmieni je automatycznie. Na przykład:

Jeśli podczas ładowania danych nazwy kolumn zostały wprowadzone niepoprawnie lub chcesz zmienić nazwy i typy kolumn w istniejącej tabeli, możesz to zrobić ręcznie. Powiemy Ci jak.

Po załadowaniu danych do Google BigQuery układ tabeli może nieznacznie różnić się od oryginalnego. Na przykład nazwa pola mogła się zmienić z powodu znaku, który nie jest obsługiwany w BigQuery, lub typem pola może być INTEGER zamiast STRING. W takim przypadku możesz ręcznie dostosować schemat.

Za pomocą zapytania SQL zaznacz wszystkie kolumny w tabeli i określ nową nazwę kolumny, której nazwę chcesz zmienić. W takim przypadku możesz nadpisać istniejącą tabelę lub utworzyć nową. Przykłady zapytań:

Korzystając z zapytania SQL, wybierz wszystkie dane z tabeli i przekonwertuj odpowiednią kolumnę na inny typ danych. Możesz użyć wyników zapytania, aby zastąpić istniejącą tabelę lub utworzyć nową. Przykład żądania:

Możesz zmienić tryb kolumny z REQUIRED na NULLABLE zgodnie z opisem w dokumentacji pomocy. Drugą opcją jest wyeksportowanie danych do Cloud Storage, a następnie zwrócenie ich do BigQuery z odpowiednim trybem dla wszystkich kolumn.

Użyj zapytania SELECT * EXCEPT, aby wykluczyć kolumnę (lub kolumny), a następnie zapisz wyniki zapytania do starej tabeli lub utwórz nową. Przykład żądania:

Ponadto istnieje drugi sposób zmiany schematu, który jest odpowiedni dla wszystkich opisanych powyżej zadań: wyeksportuj dane i załaduj je do nowej tabeli. Aby zmienić nazwę kolumny, możesz przesłać dane z BigQuery do Cloud Storage, a następnie wyeksportować je z Cloud Storage do BigQuery w nowej tabeli lub zastąpić dane w starej tabeli za pomocą parametrów zaawansowanych:

Możesz przeczytać o innych sposobach zmiany struktury tabeli w dokumentacji pomocy Google Cloud Platform.

Możesz pobierać dane z BigQuery i przesyłać je do BigQuery bez pomocy programistów za pośrednictwem interfejsu lub specjalnego dodatku z OWOX BI. Rozważmy szczegółowo każdą metodę.

Aby przesłać niezbędne informacje do magazynu – na przykład dane o użytkownikach i zamówieniach offline – otwórz swój zbiór danych, kliknij Utwórz tabelę i wybierz źródło danych: Cloud Storage, Twój komputer, Dysk Google lub Cloud Bigtable. Określ ścieżkę do pliku, jego format oraz nazwę tabeli, do której zostaną załadowane dane:

Po kliknięciu przycisku Utwórz tabelę w zestawie danych pojawi się tabela.

Możliwe jest również przesyłanie przetworzonych danych z BigQuery – na przykład w celu utworzenia raportu przez interfejs systemu. Aby to zrobić, otwórz żądaną tabelę z danymi i kliknij przycisk Eksportuj:

System zaoferuje dwie opcje: przeglądanie danych w Google Data Studio lub przesyłanie ich do Google Cloud Storage. Jeśli wybierzesz pierwszą opcję, od razu przejdziesz do Studia danych, gdzie możesz zapisać raport.

Wybór eksportu do Google Cloud Storage otworzy nowe okno. W nim musisz określić, gdzie zapisać dane i w jakim formacie.

Bezpłatny dodatek OWOX BI BigQuery Reports umożliwia szybkie i wygodne przesyłanie danych bezpośrednio z Google BigQuery do Arkuszy Google i odwrotnie. Dzięki temu nie musisz przygotowywać plików CSV ani korzystać z płatnych usług innych firm.

Załóżmy na przykład, że chcesz przesłać dane zamówień offline do BigQuery, aby utworzyć raport ROPO. W tym celu musisz:

1. Zainstaluj dodatek BigQuery Reports w swojej przeglądarce.
2. Otwórz plik danych w Arkuszach Google i w zakładce Dodatki wybierz Raporty OWOX BI BigQuery → Prześlij dane do BigQuery.
3. W oknie, które się otworzy, wybierz swój projekt i zbiór danych w BigQuery i wpisz żądaną nazwę tabeli. Wybierz również pola, których wartości chcesz załadować. Domyślnie typem wszystkich pól jest STRING, ale zalecamy wybór typu danych zgodnie z kontekstem (np. dla pól z identyfikatorami numerycznymi wybierz INTEGER; dla cen FLOAT):
   

4. Kliknij przycisk Rozpocznij przesyłanie, a Twoje dane zostaną załadowane do Google BigQuery​

Możesz też użyć tego dodatku do eksportowania danych z BigQuery do Arkuszy Google – na przykład do wizualizacji danych lub udostępniania ich współpracownikom, którzy nie mają dostępu do BigQuery. Dla tego:

1. Otwórz Arkusze Google. W zakładce Dodatki wybierz Raporty OWOX BI BigQuery → Dodaj nowy raport:
   

2. Następnie wpisz swój projekt w Google BigQuery i wybierz Dodaj nowe zapytanie.
3. W nowym oknie wstaw zapytanie SQL. Może to być zapytanie, które przesyła dane z tabeli do BigQuery, lub zapytanie, które pobiera i oblicza niezbędne dane.
4. Zmień nazwę zapytania, aby ułatwić jego znalezienie i uruchomienie, klikając przycisk Zapisz i uruchom.
   

Aby regularnie przesyłać dane z BigQuery do Arkuszy Google, możesz włączyć zaplanowane aktualizacje danych:

1. Na karcie Dodatki wybierz Raporty OWOX BI BigQuery → Zaplanuj raport :
   

2. W oknie, które się otworzy, ustaw czas i częstotliwość aktualizacji raportu i kliknij Zapisz :
   

Jeśli nie doceniłeś jeszcze zalet przestrzeni dyskowej w chmurze Google BigQuery, zalecamy jej wypróbowanie. Za pomocą OWOX BI możesz połączyć dane ze swojej witryny, źródeł reklam i wewnętrznych systemów CRM w BigQuery w celu:

• Skonfiguruj kompleksowe analizy i dowiedz się, jaki jest rzeczywisty zwrot z marketingu, biorąc pod uwagę zamówienia offline, zwroty i wszystkie kroki użytkownika na drodze do zakupu;
• Twórz raporty na temat kompletnych niespróbkowanych danych z dowolnymi parametrami i wskaźnikami;
• Oceń kanały pozyskiwania klientów za pomocą analizy kohortowej;
• Dowiedz się, jak Twoja reklama online wpływa na sprzedaż offline;
• Zmniejsz udział kosztów reklamy, wydłuż cykl życia klientów i zwiększ LTV całej bazy klientów;
• Segmentuj klientów w zależności od ich aktywności i personalizuj komunikację z nimi.

OWOX BI oferuje bezpłatny okres próbny, podczas którego możesz wypróbować wszystkie funkcje usługi.