Tworzenie zapytania do zbioru danych Wikipedia w BigQuery

1. Omówienie

BigQuery to ekonomiczna, w pełni zarządzana analityczna baza danych Google typu NoOps. Dzięki BigQuery możesz wykonywać zapytania obejmujące nawet terabajty danych bez konieczności zarządzania infrastrukturą i nie potrzebujesz administratora bazy danych. Usługa BigQuery opiera się na znanej Ci bazie SQL i jest dostępna w modelu płatności według wykorzystania. Pracując w BigQuery, możesz skoncentrować się na analizowaniu danych i wyciąganiu z nich znaczących wniosków.

W ramach tego ćwiczenia w programie zapoznasz się ze zbiorem danych Wikipedii za pomocą BigQuery.

Czego się nauczysz

• Korzystanie z BigQuery
• Jak wczytać do BigQuery rzeczywisty zbiór danych
• Jak napisać zapytanie pozwalające uzyskać wgląd w duży zbiór danych

Czego potrzebujesz

• Projekt Google Cloud
• przeglądarki, na przykład Google Chrome lub Firefox;

Ankieta

Jak wykorzystasz ten samouczek?

Tylko do odczytu Przeczytaj go i wykonaj ćwiczenia

Jak oceniasz swoje doświadczenia z Google Cloud?

Początkujący Poziom średnio zaawansowany Biegły

2. Konfiguracja i wymagania

Włączanie BigQuery

Jeśli nie masz jeszcze konta Google, musisz je utworzyć.

1. Zaloguj się w konsoli Google Cloud i otwórz BigQuery. Możesz też otworzyć interfejs internetowy BigQuery bezpośrednio, wpisując w przeglądarce ten adres URL.

https://console.cloud.google.com/bigquery

2. Zaakceptuj Warunki korzystania z usługi.
3. Aby korzystać z BigQuery, musisz utworzyć projekt. Utwórz nowy projekt, postępując zgodnie z instrukcjami.

Wybierz nazwę projektu i zanotuj jego identyfikator.

Identyfikator projektu to unikalna nazwa we wszystkich projektach Google Cloud. W dalszej części tego ćwiczenia w Codelabs będzie ona określana jako PROJECT_ID.

W tym ćwiczeniu w programowaniu wykorzystywane są zasoby BigQuery w ramach limitów piaskownicy BigQuery. Konto rozliczeniowe nie jest wymagane. Jeśli zechcesz później usunąć limity piaskownicy, możesz dodać konto rozliczeniowe, rejestrując się w bezpłatnym okresie próbnym Google Cloud.

W następnej sekcji wczytasz zbiór danych z Wikipedii.

3. Tworzenie zbioru danych

Najpierw utwórz w projekcie nowy zbiór danych. Zbiór danych składa się z wielu tabel.

1. Aby utworzyć zbiór danych, kliknij nazwę projektu w panelu zasobów, a następnie kliknij Utwórz zbiór danych:

2. Wpisz lab jako identyfikator zbioru danych:

3. Aby utworzyć pusty zbiór danych, kliknij Utwórz zbiór danych.

4. Wczytywanie danych za pomocą programu wiersza poleceń bq

Aktywowanie Cloud Shell

1. W konsoli Cloud kliknij Aktywuj Cloud Shell .

Jeśli dopiero zaczynasz korzystać z Cloud Shell, wyświetli się ekran pośredni (w części strony widocznej po przewinięciu) z opisem tej funkcji. W takim przypadku kliknij Dalej (nie zobaczysz go więcej). Tak wygląda ten jednorazowy ekran:

Uzyskanie dostępu do Cloud Shell i połączenie się z nim powinno zająć tylko kilka chwil.

Ta maszyna wirtualna ma wszystkie potrzebne narzędzia dla programistów. Zawiera stały katalog domowy o pojemności 5 GB i działa w Google Cloud, co znacznie zwiększa wydajność sieci i uwierzytelnianie. Większość czynności z tego ćwiczenia z programowania można wykonać w przeglądarce lub na Chromebooku.

Po nawiązaniu połączenia z Cloud Shell powinno pojawić się informacja, że użytkownik jest już uwierzytelniony i że projekt jest już ustawiony na identyfikator Twojego projektu.

2. Uruchom to polecenie w Cloud Shell, aby potwierdzić, że jesteś uwierzytelniony:

gcloud auth list

Dane wyjściowe polecenia

 Credentialed Accounts
ACTIVE  ACCOUNT
*       <my_account>@<my_domain.com>

To set the active account, run:
    $ gcloud config set account `ACCOUNT`

Uwaga: narzędzie wiersza poleceń gcloud to zaawansowane, ujednolicone narzędzie wiersza poleceń w Google Cloud. Jest ona wstępnie zainstalowana w Cloud Shell. Zauważysz, że obsługuje ona funkcję autouzupełniania. Więcej informacji znajdziesz w artykule Omówienie narzędzia wiersza poleceń gcloud.

3. Uruchom to polecenie w Cloud Shell, aby sprawdzić, czy polecenie gcloud zna Twój projekt:

gcloud config list project

Dane wyjściowe polecenia

[core]
project = <PROJECT_ID>

Jeśli tak nie jest, możesz go ustawić za pomocą tego polecenia:

gcloud config set project <PROJECT_ID>

Dane wyjściowe polecenia

Updated property [core/project].

Wczytywanie danych do BigQuery

Dla Twojej wygody niektóre dane z 10 kwietnia 2019 r. ze zbioru danych odsłon Wikimedia są dostępne w Google Cloud Storage na stronie gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz. Plik danych jest plikiem CSV w formacie GZip. Możesz wczytać ten plik bezpośrednio za pomocą narzędzia wiersza poleceń bq. W ramach polecenia load możesz również opisać schemat pliku.

Identyfikator projektu znajdziesz w konsoli interfejsów API lub w wierszu poleceń: „gcloud config list” Znajdź wartość „project”.

Poza tym przy pierwszym uruchomieniu bq może pojawić się prośba o wybranie projektu domyślnego. Po wybraniu możesz ponownie uruchomić powyższe polecenie, aby wczytać dane.

bq load \
  --source_format CSV \
  --field_delimiter " " \
  --allow_jagged_rows \
  --quote "" \
  --max_bad_records 3 \
  $GOOGLE_CLOUD_PROJECT:lab.pageviews_20190410_140000 \
  gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz \
  wiki,title,requests:integer,zero:integer

Wczytywanie danych może potrwać kilka minut. Poczekaj na jej zakończenie.

Używasz kilku zaawansowanych opcji do wczytania pliku page-views:

• Ustaw --source_format CSV, aby wskazać, że plik ma być analizowany jako plik CSV. Ten krok jest opcjonalny, ponieważ domyślnym formatem jest CSV.
• Ustaw --field_delimiter " ", by wskazać, że do rozdzielania pól służy jedna spacja.
• Ustaw --allow_jagged_rows, aby uwzględniać wiersze z mniejszą liczbą kolumn i ignorować błędy podczas wczytywania pliku CSV.
• Ustaw --quote "", by wskazać, że ciągi znaków są bez cudzysłowów.
• Ustaw --max_bad_records 3 tak, aby podczas analizowania pliku CSV ignorował maksymalnie 3 błędy.

Więcej informacji o wierszu poleceń bq znajdziesz w dokumentacji.

5. Wyświetl podgląd zbioru danych

W konsoli BigQuery otwórz jedną z właśnie wczytanych tabel.

1. Rozwiń projekt.
2. Rozwiń zbiór danych.
3. Wybierz tabelę.

Schemat tabeli możesz zobaczyć na karcie Schemat. 4. Aby sprawdzić ilość danych w tabeli, otwórz kartę Szczegóły:

5. Aby zobaczyć wybrane wiersze z tabeli, otwórz kartę Podgląd.

6. Tworzenie zapytania

1. Kliknij Utwórz nowe zapytanie:

Wyświetli się Edytor zapytań:

2. Uzyskaj całkowitą liczbę wyświetleń w witrynie Wikimedia między godziną 14:00 a 15:00 10 kwietnia 2019 r., wpisując zapytanie:

SELECT SUM(requests)
FROM `lab.pageviews_20190410_140000`

3. Kliknij Wykonaj:

Po kilku sekundach wynik będzie widoczny na dole wraz z informacją o ilości przetworzonych danych:

To zapytanie przetworzyło 123,9 MB, mimo że tabela ma rozmiar 691,4 MB. BigQuery przetwarza bajty tylko z kolumn wykorzystywanych w zapytaniu, więc łączna ilość przetworzonych danych może być znacznie mniejsza od rozmiaru tabeli. Dzięki grupowaniu i partycjonowaniu ilość przetwarzanych danych można jeszcze bardziej zmniejszyć.

Możesz bezpłatnie przetwarzać co miesiąc do 1 TB danych i tworzyć dla nich zapytania. Więcej informacji znajdziesz na stronie z cennikiem BigQuery.

7. Bardziej zaawansowane zapytania

Sprawdzanie liczby wyświetleń stron w Wikipedii

Zbiór danych Wikimedia zawiera wyświetlenia stron ze wszystkich projektów Wikimedia (w tym z Wikipedii, Wiktorii, Wikibooków i Wikiquotes). Aby zawęzić zapytanie do angielskojęzycznych stron Wikipedii, dodaj instrukcję WHERE:

SELECT SUM(requests), wiki
FROM `lab.pageviews_20190410_140000`
WHERE wiki = "en"
GROUP BY wiki

Zwróć uwagę, że po wysłaniu zapytania do dodatkowej kolumny wiki ilość przetworzonych danych wzrosła z 124 MB do 204 MB.

BigQuery obsługuje wiele znanych klauzul SQL, takich jak CONTAINS, GROUP BY, ORDER BY, oraz wiele funkcji agregacji. Możesz też używać wyrażeń regularnych do wysyłania zapytań dotyczących pól tekstowych. Wypróbuj jedno z tych rozwiązań:

SELECT title, SUM(requests) requests
FROM `lab.pageviews_20190410_140000`
WHERE
  wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

Więcej informacji o składni BigQuery znajdziesz w dokumentacji dotyczącej zapytań BigQuery.

Wykonywanie zapytań dotyczących wielu tabel

Możesz wybrać zakres tabel, aby utworzyć połączenie za pomocą tabeli z symbolami wieloznacznymi.

1. Najpierw utwórz drugą tabelę, aby przesłać do niej zapytanie, wczytując wyświetlenia strony z następnej godziny do nowej tabeli:

bq load \
  --source_format CSV \
  --field_delimiter " " \
  --quote "" \
  $GOOGLE_CLOUD_PROJECT:lab.pageviews_20190410_150000 \
  gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-150000.gz \
  wiki,title,requests:integer,zero:integer

2. W Edytorze zapytań utwórz zapytanie do obu wczytanych tabel, wysyłając zapytanie o tabele „pageviews_2019”. jako prefiks:

SELECT title, SUM(requests) requests
FROM `lab.pageviews_2019*`
WHERE
  wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

Możesz bardziej selektywnie filtrować tabele, korzystając z pseudokolumny _TABLE_SUFFIX. To zapytanie ogranicza się do tabel odpowiadających 10 kwietnia.

SELECT title, SUM(requests) requests
FROM `lab.pageviews_2019*`
WHERE
  _TABLE_SUFFIX BETWEEN '0410' AND '0410_9999999'
  AND wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

8. Czyszczenie

Opcjonalnie usuń zbiór danych utworzony za pomocą polecenia bq rm. Aby usunąć wszystkie zawarte w nim tabele, użyj flagi -r.

bq rm -r lab

9. Gratulacje!

Udało Ci się użyć BigQuery i SQL do wysyłania zapytań do rzeczywistego zbioru danych odsłon w Wikipedii. Możesz tworzyć zapytania do zbiorów danych w petabajtach.

Więcej informacji

• Zapoznaj się z subredditem BigQuery, aby dowiedzieć się, jak inni korzystają obecnie z BigQuery.
• Znajdź publiczne zbiory danych dostępne w BigQuery.
• Dowiedz się, jak wczytać dane do BigQuery.