Partycjonowanie i grupowanie w BigQuery

1. Wprowadzenie

BigQuery to w pełni zarządzana, ekonomiczna, w pełni zarządzana hurtownia danych analitycznych dla firm, która może obsługiwać petabajty danych. BigQuery to usługa bezserwerowa. Nie musisz konfigurować klastrów ani nimi zarządzać.

Zbiór danych BigQuery znajduje się w projekcie GCP i zawiera co najmniej 1 tabelę. Możesz tworzyć zapytania do tych zbiorów danych za pomocą SQL.

W ramach tego ćwiczenia w programie użyjesz interfejsu internetowego BigQuery w konsoli GCP, aby poznać partycjonowanie i grupowanie w BigQuery. Partycjonowanie i grupowanie tabel w BigQuery ułatwia tworzenie struktury danych zgodnej z często spotykanymi wzorcami dostępu do danych. Partycjonowanie i grupowanie ma kluczowe znaczenie dla pełnego maksymalizacji wydajności i kosztów BigQuery przy wykonywaniu zapytań dotyczących określonego zakresu danych. Pozwala to skanować mniej danych na zapytanie, a przycinanie jest określane przed czasem rozpoczęcia zapytania.

Uwaga: oprócz interfejsu internetowego BigQuery do operacji na zbiorach danych BigQuery możesz używać narzędzia wiersza poleceń bq. Więcej informacji o narzędziu wiersza poleceń bq znajdziesz w artykule bq Narzędzie wiersza poleceń bq.

Więcej informacji o BigQuery znajdziesz w dokumentacji BigQuery.

Czego się nauczysz

• Jak tworzyć tabele partycjonowane i klastrowane oraz wykonywać dotyczące ich zapytania
• Porównaj wydajność zapytań z użyciem tabel partycjonowanych i klastrowanych

Czego potrzebujesz

Do ukończenia modułu potrzebne będą:

• Najnowsza wersja Google Chrome
• Konto rozliczeniowe Google Cloud Platform

2. Przygotowanie

Aby korzystać z BigQuery, musisz utworzyć projekt GCP lub wybrać istniejący projekt.

Tworzenie projektu

Aby utworzyć nowy projekt, wykonaj te czynności:

1. Jeśli nie masz jeszcze konta Google (Gmaila lub Google Apps), utwórz je.
2. Zaloguj się w konsoli Google Cloud Platform ( console.cloud.google.com) i utwórz nowy projekt.
3. Jeśli nie masz żadnych projektów, kliknij przycisk Utwórz projekt:

W przeciwnym razie utwórz nowy projekt, korzystając z menu wyboru projektu:

4. Wpisz nazwę projektu i kliknij Utwórz. Pamiętaj, że identyfikator projektu jest unikalną nazwą we wszystkich projektach Google Cloud.

3. Praca z publicznymi zbiorami danych

BigQuery umożliwia pracę z publicznymi zbiorami danych, takimi jak BBC News, repozytorium GitHub, Stack Overflow czy zbiory danych amerykańskiej National Oceanic and Atmospheric Administration (NOAA). Nie musisz wczytywać tych zbiorów danych do BigQuery. Wystarczy otworzyć zbiór danych, aby przeglądać je w BigQuery i tworzyć dotyczące ich zapytania. W tym ćwiczeniu w Codelabs będziesz pracować z publicznym zbiorem danych Stack Overflow.

Przeglądaj zbiór danych Stack Overflow

Zbiór danych Stack Overflow zawiera informacje o postach, tagach, plakietkach, komentarzach, użytkownikach i innych elementach. Aby przeglądać zbiór danych Stack Overflow w interfejsie internetowym BigQuery, wykonaj te czynności:

1. Otwórz zbiór danych Stack Overflow. W konsoli GCP otworzy się interfejs internetowy BigQuery z informacjami o zbiorze danych Stackoverflow.
2. W panelu użytkownika wybierz bigquery-public-data. Rozwinie się menu, aby wyświetlić listę publicznych zbiorów danych. Każdy zbiór danych zawiera co najmniej 1 tabelę.
3. Przewiń w dół i wybierz stackoverflow. Rozwinie się menu, aby wyświetlić listę tabel w zbiorze danych Stack Overflow.
4. Wybierz plakietki, aby zobaczyć schemat tabeli odznak. Zanotuj nazwy pól w tabeli.
5. Nad nazwami pól kliknij Podgląd, aby zobaczyć przykładowe dane do tabeli z plakietkami.

Więcej informacji o wszystkich publicznych zbiorach danych dostępnych w BigQuery znajdziesz w artykule Publiczne zbiory danych Google BigQuery.

Wysyłanie zapytania do zbioru danych Stackoverflow

Przeglądanie zbioru danych to dobry sposób na zrozumienie danych, z którymi pracujesz, ale wysyłanie zapytań do zbiorów danych to jedna z najważniejszych funkcji BigQuery. Z tej sekcji dowiesz się, jak uruchamiać zapytania BigQuery. Na tym etapie nie musisz znać żadnego języka SQL. Możesz skopiować i wkleić poniższe zapytania.

Aby uruchomić zapytanie, wykonaj te czynności:

1. W prawym górnym rogu konsoli GCP kliknij Utwórz nowe zapytanie.
2. W obszarze tekstowym Edytora zapytań skopiuj poniższe zapytanie SQL i wklej je. BigQuery sprawdza poprawność zapytania, a w interfejsie internetowym pod polem tekstowym wyświetla się zielony znacznik wyboru, który wskazuje, że składnia jest prawidłowa.

SELECT
  EXTRACT(YEAR FROM creation_date) AS creation_year,
  COUNT(*) AS total_posts
FROM `bigquery-public-data.stackoverflow.posts_questions`
GROUP BY creation_year
ORDER BY total_posts DESC
LIMIT 10

3. Wybierz Uruchom. Zapytanie zwraca liczbę postów lub pytań opublikowanych w Stack Overflow w każdym roku.

Uwaga: w tym zapytaniu używana jest pełna nazwa tabeli w tabeli posts_questions, w tym nazwa projektu, zbioru danych i nazwy tabeli: bigquery-public-data.stackoverflow.posts_questions.

4. Tworzenie nowej tabeli

W poprzedniej sekcji wykonaliśmy zapytanie na publicznych zbiorach danych, które udostępnia Ci BigQuery. W tej sekcji utworzysz w BigQuery nową tabelę na podstawie istniejącej tabeli. Utworzysz nową tabelę z próbkowanymi danymi z tabeli posts_questions publicznego zbioru danych Stack Overflow, a następnie wyślesz zapytanie do tej tabeli.

Tworzenie nowego zbioru danych

Aby utworzyć dane tabeli i wczytać je do BigQuery, najpierw utwórz zbiór danych BigQuery, w którym będą przechowywane dane, wykonując te czynności:

1. W panelu użytkownika konsoli GCP wybierz nazwę projektu utworzonego w ramach konfiguracji.
2. Po prawej stronie w panelu szczegółów kliknij Utwórz zbiór danych.

3. W oknie Utwórz zbiór danych jako Identyfikator zbioru danych wpisz stackoverflow. Pozostaw inne ustawienia domyślne bez zmian i kliknij OK.

Utwórz nową tabelę dzięki postom StackOverflow z 2018 r.

Po utworzeniu zbioru danych BigQuery możesz utworzyć nową tabelę w BigQuery. Aby utworzyć tabelę z danymi z istniejącej tabeli, wykonaj zapytanie na zbiorze danych Stack Overflow z 2018 r. i zapisz wyniki w nowej tabeli. W tym celu wykonaj te czynności:

1. W prawym górnym rogu konsoli GCP kliknij Utwórz nowe zapytanie.

2. W obszarze tekstowym Edytora zapytań skopiuj poniższe zapytanie SQL i wklej je, aby utworzyć nową tabelę, która jest instrukcją DDL.

CREATE OR REPLACE TABLE `stackoverflow.questions_2018` AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';

3. Wybierz Uruchom. Zapytanie utworzy nową tabelę questions_2018 w zbiorze danych stackoverflow w Twoim projekcie z danymi powstałymi w wyniku uruchomienia zapytania w zbiorze danych BigQuery Stack Overflow bigquery-public-data.stackoverflow.posts_questions.

Uwaga: utworzenie nowej tabeli może potrwać około 2 minut. Odczekaj przed przejściem do następnego kroku.

Wysyłanie zapytań dotyczących nowej tabeli dzięki postom na stronie Stack Overflow z 2018 r.

Masz już utworzoną tabelę BigQuery, więc wykonajmy zapytanie, które zwróci posty w Stack Overflow z pytaniami i tytułami oraz kilkoma innymi statystykami, takimi jak liczba odpowiedzi, komentarzy, wyświetleń i ulubionych. Wykonaj te czynności:

1. W prawym górnym rogu konsoli GCP kliknij Utwórz nowe zapytanie.
2. W obszarze tekstowym Edytora zapytań skopiuj i wklej następujące zapytanie SQL

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

3. Wybierz Uruchom. Zapytanie zwróci pytania ze Stack Overflow, które zostały utworzone w styczniu 2018 roku, i zostały oznaczone tagiem android, a także pytanie i kilka innych statystyk.
4. Domyślnie BigQuery zapisuje wyniki zapytania w pamięci podręcznej. Jeśli uruchomisz to samo zapytanie, zobaczysz, że zwracanie wyników przez BigQuery zajmuje znacznie mniej czasu, ponieważ zwraca ono wyniki z pamięci podręcznej.
5. Uruchom ponownie to samo zapytanie, ale tym razem z wyłączonym buforowaniem BigQuery. W dalszej części modułu wyłączymy pamięć podręczną, aby porównać wydajność z tabelami partycjonowanymi i sgrupowanymi. Ten proces zostanie uruchomiony w następnych sekcjach. W Edytorze zapytań kliknij Więcej i wybierz Ustawienia zapytania.
6. W sekcji Ustawienie pamięci podręcznej odznacz opcję Używaj wyników z pamięci podręcznej.
7. W wynikach zapytania powinien być widoczny czas potrzebny na wykonanie zapytania oraz ilość danych przetworzonych w celu uzyskania wyników.

5. Tworzenie tabeli partycjonowanej i wykonywanie na niej zapytań

W poprzedniej sekcji utworzyliśmy w BigQuery nową tabelę z danymi z tabeli posts_questions przy użyciu publicznego zbioru danych Stack Overflow. Zbadaliśmy ten zbiór danych z wyłączoną pamięcią podręczną i zaobserwowaliśmy wydajność zapytania. W tej sekcji utworzysz nową tabelę partycjonowaną z tego samego publicznego zbioru danych w Stack Overflow (posts_questions) i będziesz obserwować wydajność zapytania.

Tabela partycjonowana to specjalna tabela podzielona na segmenty nazywane partycjami, która ułatwia zarządzanie danymi i wykonywanie dotyczących ich zapytań. Duże tabele można zwykle podzielić na wiele mniejszych partycji, korzystając z kolumny czasu pozyskiwania danych, kolumny TIMESTAMP/DATE lub kolumny INTEGER. Utworzymy tabelę partycjonowaną DATE.

Więcej informacji o tabelach partycjonowanych znajdziesz tutaj.

Tworzenie nowej tabeli partycjonowanej przy użyciu postów StackOverflow z 2018 r.

Aby utworzyć partycjonowaną tabelę z danymi z istniejącej tabeli lub zapytania, wyślij zapytanie do zbioru danych Stackoverflow z 2018 r. i zapisz wyniki w nowej tabeli. Wykonaj te czynności:

1. W prawym górnym rogu konsoli GCP kliknij Utwórz nowe zapytanie.

2. W obszarze tekstowym Edytora zapytań skopiuj poniższe zapytanie SQL i wklej je, aby utworzyć nową tabelę, która jest instrukcją DDL.

CREATE OR REPLACE TABLE `stackoverflow.questions_2018_partitioned` 
PARTITION BY DATE(creation_date) AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';

3. Wybierz Uruchom. Zapytanie utworzy nową tabelę questions_2018_partitioned w zbiorze danych stackoverflow w Twoim projekcie z danymi uzyskanymi w wyniku uruchomienia zapytania w zbiorze danych BigQuery Stack Overflow bigquery-public-data.stackoverflow.posts_questions

Uwaga: utworzenie nowej tabeli może potrwać około 2 minut. Odczekaj przed przejściem do następnego kroku.

Wysyłanie zapytań dotyczących tabeli partycjonowanej za pomocą postów Stack Overflow z 2018 r.

Masz już utworzoną tabelę partycjonowaną BigQuery, więc wykonajmy to samo zapytanie, tym razem w tabeli partycjonowanej, aby zwrócić posty ze Stack Overflow z pytaniami i tytułami oraz kilkoma innymi statystykami, takimi jak liczba odpowiedzi, komentarzy, wyświetleń i ulubionych. Wykonaj te czynności:

1. W prawym górnym rogu konsoli GCP kliknij Utwórz nowe zapytanie.
2. W obszarze tekstowym Edytora zapytań skopiuj i wklej następujące zapytanie SQL

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_partitioned` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

3. Wybierz Uruchom przy wyłączonej pamięci podręcznej BigQuery (informacje o wyłączaniu pamięci podręcznej BigQuery znajdziesz w poprzedniej sekcji). Zapytanie zwróci pytania ze Stack Overflow, które zostały utworzone w styczniu 2018 roku, i zostały oznaczone tagiem android, a także pytanie i kilka innych statystyk.
4. W wynikach zapytania powinien być widoczny czas potrzebny na wykonanie zapytania oraz ilość danych przetworzonych w celu uzyskania wyników.

Zobaczysz, że wydajność zapytania z tabelą partycjonowaną jest lepsza niż tabeli bez partycjonowania, ponieważ BigQuery przycina partycje, tzn. skanuje tylko wymagane partycje, które przetwarzają mniej danych i działają szybciej. Pozwala to zoptymalizować koszty i wydajność zapytań.

6. Tworzenie tabeli klastra i wykonywanie na niej zapytań

W poprzedniej sekcji utworzyliśmy w BigQuery tabelę partycjonowaną z danymi z tabeli posts_questions w publicznym zbiorze danych Stack Overflow. Zbadaliśmy tę tabelę z wyłączoną pamięcią podręczną i zaobserwowaliśmy wydajność zapytania zarówno w przypadku tabel niepartycjonowanych, jak i partycjonowanych. W tej sekcji utworzysz nową tabelę klastrowaną z tego samego publicznego zbioru danych w Stack Overflow (posts_questions) i będziesz obserwować wydajność zapytań.

Gdy tabela jest grupowana w BigQuery, dane tabeli są automatycznie porządkowane na podstawie zawartości co najmniej 1 kolumny w schemacie tabeli. Wskazane kolumny służą do kolokacji powiązanych danych. Gdy dane są zapisywane w tabeli klastrowanej, BigQuery sortuje je zgodnie z wartościami w kolumnach klastrowania. Wartości te służą do porządkowania danych w kilku blokach w pamięci BigQuery. Kolejność klastrowanych kolumn określa kolejność sortowania danych. Gdy nowe dane są dodawane do tabeli lub konkretnej partycji, BigQuery automatycznie przeprowadza w tle ponowne grupowanie, aby przywrócić właściwość sortowania tabeli lub partycji.

Więcej informacji o pracy z tabelami klastrowanymi znajdziesz tutaj.

Tworzenie nowej tabeli grupowanej za pomocą postów w Stack Overflow z 2018 r.

W tej sekcji utworzysz nową tabelę partycjonowaną w dniu creation_date i pogrupowaną w kolumnie tags na podstawie wzorca dostępu do zapytania. Aby utworzyć tabelę klastrowaną z danymi z istniejącej tabeli lub zapytania, wykonaj zapytanie na tabeli postów w Stack Overflow z 2018 r. i zapisz wyniki w nowej tabeli. W tym celu wykonaj te czynności:

1. W prawym górnym rogu konsoli GCP kliknij Utwórz nowe zapytanie.

2. W obszarze tekstowym Edytora zapytań skopiuj poniższe zapytanie SQL i wklej je, aby utworzyć nową tabelę, która jest instrukcją DDL.

#standardSQL
CREATE OR REPLACE TABLE `stackoverflow.questions_2018_clustered`
PARTITION BY
  DATE(creation_date)
CLUSTER BY
  tags AS
SELECT
  id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM
  `bigquery-public-data.stackoverflow.posts_questions`
WHERE
  creation_date BETWEEN '2018-01-01' AND '2019-01-01';

3. Wybierz Uruchom. Zapytanie utworzy nową tabelę questions_2018_clustered w zbiorze danych stackoverflow w Twoim projekcie z danymi powstałymi w wyniku uruchomienia zapytania do tabeli BigQuery Stack Overflowbigquery-public-data.stackoverflow.posts_questions. Nowa tabela jest partycjonowana w dniu create_date i pogrupowana w kolumnie tagów.

Uwaga: utworzenie nowej tabeli może potrwać około 2 minut. Odczekaj przed przejściem do następnego kroku.

Wysyłanie zapytań dotyczących tabeli klastrów przy użyciu postów Stack Overflow z 2018 r.

Masz już utworzoną tabelę klastrowaną BigQuery, więc wykonajmy jeszcze raz to samo zapytanie, tym razem w tabeli partycjonowanej i grupowanej, aby zwrócić posty w Stack Overflow z pytaniami i tytułami oraz kilkoma innymi statystykami, takimi jak liczba odpowiedzi, komentarzy, wyświetleń i ulubionych. Wykonaj te czynności:

1. W prawym górnym rogu konsoli GCP kliknij Utwórz nowe zapytanie.
2. W obszarze tekstowym Edytora zapytań skopiuj i wklej następujące zapytanie SQL

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_clustered` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

3. Wybierz Uruchom przy wyłączonej pamięci podręcznej BigQuery (informacje o wyłączaniu pamięci podręcznej BigQuery znajdziesz w poprzedniej sekcji). Zapytanie zwróci pytania ze Stack Overflow, które zostały utworzone w styczniu 2018 roku, i zostały oznaczone tagiem android, a także pytanie i kilka innych statystyk.
4. W wynikach zapytania powinien być widoczny czas potrzebny na wykonanie zapytania oraz ilość danych przetworzonych w celu uzyskania wyników.

W przypadku tabeli partycjonowanej i klastrowanej zapytanie przeskanowało mniej danych niż tabela partycjonowana lub bez partycjonowania. Sposób porządkowania danych przez partycjonowanie i grupowanie minimalizuje ilość danych skanowanych przez instancje robocze przedziałów, co poprawia wydajność zapytań i optymalizuje koszty.

7. Czyszczenie

Jeśli nie zamierzasz dalej korzystać ze zbioru danych stackoverflow, usuń go i projekt utworzony na potrzeby tego ćwiczenia z programowania.

Usuwanie zbioru danych BigQuery

Aby usunąć zbiór danych BigQuery, wykonaj te czynności:

1. Wybierz zbiór danych stackoverflow z panelu użytkownika po lewej stronie w BigQuery .
2. W panelu szczegółów kliknij Usuń zbiór danych.
3. W oknie Usuń zbiór danych wpisz stackoverflow i wybierz Usuń, by potwierdzić, że chcesz usunąć zbiór danych.

Usuwanie projektu

Aby usunąć projekt GCP utworzony na potrzeby tego ćwiczenia z programowania, wykonaj te czynności:

1. W menu nawigacyjnym GCP kliknij Administracja Administracja
2. W panelu użytkownika kliknij Ustawienia.
3. W panelu szczegółów sprawdź, czy Twoim bieżącym projektem jest projekt utworzony na potrzeby tego ćwiczenia z programowania, i kliknij Wyłącz.
4. W oknie Wyłącz projekt wpisz jego identyfikator (nie nazwę) i kliknij Wyłącz, aby potwierdzić tę czynność.

Gratulacje! Wiesz już,

• Jak utworzyć nową tabelę na podstawie istniejących tabel za pomocą interfejsu internetowego BigQuery
• Jak tworzyć tabele partycjonowane i klastrowane oraz wykonywać dotyczące ich zapytania
• Jak partycjonowanie i grupowanie optymalizuje wydajność i koszty zapytań

Pamiętaj, że praca ze zbiorami danych nie wymaga konfigurowania klastrów ani zarządzania nimi.