Korzystanie z BigQuery w Pythonie

1. Przegląd

BigQuery to w pełni zarządzana, ekonomiczna hurtownia danych analitycznych Google w skali petabajtowej. BigQuery to usługa typu NoOps – nie musisz zarządzać infrastrukturą ani zatrudniać administratora bazy danych, więc możesz skupić się na analizowaniu danych w celu uzyskania przydatnych informacji, korzystać ze znanego Ci języka SQL i wykorzystywać nasz model płatności według wykorzystania.

W tym module dowiesz się, jak używać bibliotek klienta Google Cloud w Pythonie do wykonywania zapytań w publicznych zbiorach danych BigQuery za pomocą Pythona.

Czego się nauczysz

• Jak korzystać z Cloud Shell
• Włączanie interfejsu BigQuery API
• Uwierzytelnianie żądań do interfejsu API
• Instalowanie biblioteki klienta Python
• Jak wyszukiwać dzieła Szekspira
• Jak wysyłać zapytania do zbioru danych GitHub
• Jak dostosować statystyki buforowania i wyświetlania

Czego potrzebujesz

• Projekt Google Cloud
• przeglądarka, np. Chrome lub Firefox;
• znajomość Pythona,

Ankieta

Jak zamierzasz korzystać z tego samouczka?

Tylko przeczytaj Przeczytaj i wykonaj ćwiczenia

Jak oceniasz swoje doświadczenie z Pythonem?

Początkujący Średnio zaawansowany Zaawansowany

Jak oceniasz korzystanie z usług Google Cloud?

Początkujący Średnio zaawansowany Zaawansowany

2. Konfiguracja i wymagania

Samodzielne konfigurowanie środowiska

1. Zaloguj się w konsoli Google Cloud i utwórz nowy projekt lub użyj istniejącego. Jeśli nie masz jeszcze konta Gmail ani Google Workspace, musisz je utworzyć.

• Nazwa projektu to wyświetlana nazwa uczestników tego projektu. Jest to ciąg znaków, który nie jest używany przez interfejsy API Google. Możesz go w dowolnym momencie zaktualizować.
• Identyfikator projektu musi być unikalny we wszystkich projektach Google Cloud i jest niezmienny (nie można go zmienić po ustawieniu). Konsola Cloud automatycznie generuje unikalny ciąg znaków. Zwykle nie musisz się nim przejmować. W większości modułów z kodem musisz odwoływać się do identyfikatora projektu (zwykle oznaczanego jako PROJECT_ID). Jeśli Ci się nie podoba, wygeneruj inny losowy identyfikator lub spróbuj użyć własnego i sprawdź, czy jest dostępny. Po utworzeniu projektu jest on „zamrażany”.
• Istnieje też trzecia wartość, czyli numer projektu, którego używają niektóre interfejsy API. Więcej informacji o tych 3 wartościach znajdziesz w dokumentacji.

Ostrzeżenie: identyfikator projektu musi być unikalny na całym świecie. Po jego wybraniu nie może go używać nikt inny. Jesteś jedynym użytkownikiem tego identyfikatora. Jeśli projekt zostanie usunięty, nie będzie można ponownie użyć jego identyfikatora.

Uwaga: jeśli używasz konta Gmail, możesz pozostawić domyślną lokalizację ustawioną na Brak organizacji. Jeśli używasz konta Google Workspace, wybierz lokalizację odpowiednią dla Twojej organizacji.

2. Następnie musisz włączyć płatności w konsoli Cloud, aby korzystać z zasobów i interfejsów API Google Cloud. Ukończenie tego laboratorium nie powinno wiązać się z dużymi kosztami, a nawet z żadnymi. Aby wyłączyć zasoby i uniknąć naliczenia opłat po zakończeniu tego samouczka, postępuj zgodnie z instrukcjami „czyszczenia” na końcu ćwiczenia. Nowi użytkownicy Google Cloud mogą skorzystać z bezpłatnego okresu próbnego, w którym mają do dyspozycji środki w wysokości 300 USD.

Uruchamianie Cloud Shell

Z Google Cloud można korzystać zdalnie na laptopie, ale w tym module praktycznym będziesz używać Google Cloud Shell, czyli środowiska wiersza poleceń działającego w chmurze.

Aktywowanie Cloud Shell

1. W konsoli Cloud kliknij Aktywuj Cloud Shell .

Jeśli uruchamiasz Cloud Shell po raz pierwszy, zobaczysz ekran pośredni (część strony widoczna po przewinięciu) z opisem tego środowiska. W takim przypadku kliknij Dalej, a ten ekran nie będzie się już wyświetlać. Ten wyświetlany jednorazowo ekran wygląda tak:

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

Ta maszyna wirtualna zawiera wszystkie potrzebne narzędzia dla programistów. Zawiera również stały katalog domowy o pojemności 5 GB i działa w Google Cloud, co znacznie zwiększa wydajność sieci i usprawnia proces uwierzytelniania. Większość zadań w tym module, a być może wszystkie, możesz wykonać w przeglądarce lub na Chromebooku.

Po połączeniu z Cloud Shell zobaczysz, że uwierzytelnianie zostało już przeprowadzone, a projekt jest już ustawiony na Twój identyfikator projektu.

2. Aby potwierdzić, że uwierzytelnianie zostało przeprowadzone, uruchom w Cloud Shell to polecenie:

gcloud auth list

Wynik 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 i ujednolicone narzędzie wiersza poleceń w Google Cloud. Jest już zainstalowane w Cloud Shell. Zauważysz, że obsługuje on uzupełnianie za pomocą klawisza Tab. Więcej informacji znajdziesz w omówieniu narzędzia wiersza poleceń gcloud.

3. Aby potwierdzić, że polecenie gcloud zna Twój projekt, uruchom w Cloud Shell to polecenie:

gcloud config list project

Wynik polecenia

[core]
project = <PROJECT_ID>

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

gcloud config set project <PROJECT_ID>

Wynik polecenia

Updated property [core/project].

3. Włącz API

Interfejs BigQuery API powinien być domyślnie włączony we wszystkich projektach Google Cloud. Możesz to sprawdzić za pomocą tego polecenia w Cloud Shell: powinna się wyświetlić usługa BigQuery:

gcloud services list

Powinna się wyświetlić lista BigQuery:

NAME                              TITLE
bigquery.googleapis.com           BigQuery API

...

Jeśli interfejs BigQuery API nie jest włączony, możesz go włączyć za pomocą tego polecenia w Cloud Shell:

gcloud services enable bigquery.googleapis.com

Uwaga: w przypadku błędu wróć do poprzedniego kroku i sprawdź konfigurację.

4. Uwierzytelnianie żądań do interfejsu API

Aby wysyłać żądania do interfejsu BigQuery API, musisz użyć konta usługi. Konto usługi należy do Twojego projektu i jest używane przez bibliotekę klienta Google Cloud Python do wysyłania żądań interfejsu BigQuery API. Podobnie jak każde inne konto użytkownika, konto usługi jest reprezentowane przez adres e-mail. W tej sekcji użyjesz pakietu SDK Cloud, aby utworzyć konto usługi, a następnie utworzysz dane logowania, które będą potrzebne do uwierzytelnienia się jako konto usługi.

Najpierw ustaw zmienną środowiskową PROJECT_ID:

export PROJECT_ID=$(gcloud config get-value core/project)

Następnie utwórz nowe konto usługi, aby uzyskać dostęp do interfejsu BigQuery API, używając:

gcloud iam service-accounts create my-bigquery-sa \
  --display-name "my bigquery service account"

Następnie utwórz dane logowania, których kod w Pythonie będzie używać do logowania się na nowe konto usługi. Utworzone dane zapisz w pliku JSON ~/key.json przy użyciu tego polecenia:

gcloud iam service-accounts keys create ~/key.json \
  --iam-account my-bigquery-sa@${PROJECT_ID}.iam.gserviceaccount.com

Na koniec ustaw zmienną środowiskową GOOGLE_APPLICATION_CREDENTIALS, która jest używana przez bibliotekę klienta BigQuery w Pythonie (omówioną w następnym kroku) do znajdowania Twoich danych logowania. Zmienna środowiskowa powinna zawierać pełną ścieżkę do utworzonego przez Ciebie pliku JSON z danymi logowania. Aby to zrobić, użyj tego polecenia:

export GOOGLE_APPLICATION_CREDENTIALS=~/key.json

Więcej informacji o uwierzytelnianiu BigQuery API

5. Konfigurowanie kontroli dostępu

BigQuery używa usługi Identity and Access Management (IAM) do zarządzania dostępem do zasobów. BigQuery ma kilka predefiniowanych ról (użytkownik, właściciel danych, przeglądający dane itp.), które możesz przypisać do konta usługi utworzonego w poprzednim kroku. Więcej informacji o kontroli dostępu znajdziesz w dokumentacji BigQuery.

Zanim zaczniesz wysyłać zapytania do publicznych zbiorów danych, musisz się upewnić, że konto usługi ma co najmniej rolę roles/bigquery.user. Aby przypisać rolę użytkownika do konta usługi, uruchom w Cloud Shell to polecenie:

gcloud projects add-iam-policy-binding ${PROJECT_ID} \
  --member "serviceAccount:my-bigquery-sa@${PROJECT_ID}.iam.gserviceaccount.com" \
  --role "roles/bigquery.user"

Aby sprawdzić, czy konto usługi ma rolę użytkownika, możesz uruchomić to polecenie:

gcloud projects get-iam-policy $PROJECT_ID

Strona powinna wyglądać tak:

bindings:
- members:
  - serviceAccount:my-bigquery-sa@<PROJECT_ID>.iam.gserviceaccount.com
  role: roles/bigquery.user
...

6. Instalowanie biblioteki klienta

Zainstaluj bibliotekę klienta BigQuery w Pythonie:

pip3 install --user --upgrade google-cloud-bigquery

Możesz już pisać kod z użyciem interfejsu BigQuery API.

7. Wyszukiwanie w dziełach Szekspira

Publiczny zbiór danych to dowolny zbiór danych przechowywany w BigQuery i udostępniany ogółowi użytkowników. Możesz wysyłać zapytania do wielu innych publicznych zbiorów danych. Niektóre zbiory danych są hostowane przez Google, ale większość jest hostowana przez inne firmy. Więcej informacji znajdziesz na stronie Publiczne zbiory danych.

Oprócz publicznych zbiorów danych BigQuery udostępnia ograniczoną liczbę przykładowych tabel, do których możesz wysyłać zapytania. Tabele te znajdują się w zbiorze danych bigquery-public-data:samples. Tabela shakespeare w zbiorze danych samples zawiera indeks słów z dzieł Szekspira. Zawiera liczbę wystąpień każdego słowa w poszczególnych korpusach.

W tym kroku utworzysz zapytanie do tabeli shakespeare.

Uwaga: szczegóły tabeli shakespeare możesz wyświetlić w konsoli BigQuery tutaj.

Najpierw w Cloud Shell utwórz prostą aplikację w Pythonie, której użyjesz do uruchomienia przykładowych kodów interfejsu Translation API.

mkdir bigquery-demo
cd bigquery-demo
touch app.py

Otwórz edytor kodu w prawym górnym rogu Cloud Shell:

Otwórz plik app.py w folderze bigquery-demo i zastąp kod tym kodem:

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT corpus AS title, COUNT(word) AS unique_words
    FROM `bigquery-public-data.samples.shakespeare`
    GROUP BY title
    ORDER BY unique_words
    DESC LIMIT 10
"""
results = client.query(query)

for row in results:
    title = row['title']
    unique_words = row['unique_words']
    print(f'{title:<20} | {unique_words}')

Poświęć minutę lub dwie na przeanalizowanie kodu i sprawdzenie, jak wysyłane są zapytania do tabeli.

Wróć do Cloud Shell i uruchom aplikację:

python3 app.py

Powinna się wyświetlić lista słów i ich wystąpień:

hamlet               | 5318
kinghenryv           | 5104
cymbeline            | 4875
troilusandcressida   | 4795
kinglear             | 4784
kingrichardiii       | 4713
2kinghenryvi         | 4683
coriolanus           | 4653
2kinghenryiv         | 4605
antonyandcleopatra   | 4582

Uwaga: jeśli pojawi się błąd PermissionDenied (403), sprawdź czynności wykonane podczas uwierzytelniania żądań do interfejsu API.

• Sprawdź, czy zdefiniowana jest zmienna środowiskowa danych logowania: echo $GOOGLE_APPLICATION_CREDENTIALS
• Powinna się wyświetlić pełna ścieżka do pliku z danymi logowania: /home/$USER/key.json
• Następnie sprawdź, czy dane logowania zostały utworzone: cat $GOOGLE_APPLICATION_CREDENTIALS
• Powinien pojawić się ekran podobny do tego: {"type": "service_account","project_id": "PROJECT_ID",...}

Jeśli coś jest nie tak, wróć do kroku Uwierzytelnianie żądań interfejsu API.

8. Tworzenie zapytania dotyczącego zbioru danych GitHub

Aby lepiej poznać BigQuery, wykonasz teraz zapytanie dotyczące publicznego zbioru danych GitHub. Najczęstsze komunikaty zatwierdzenia znajdziesz na GitHubie. Użyjesz też konsoli internetowej BigQuery, aby wyświetlać podgląd zapytań doraźnych i je uruchamiać.

Aby zobaczyć, jak wyglądają dane, otwórz zbiór danych GitHub w internetowym interfejsie BigQuery:

Otwórz github_repos tabelę.

Kliknij przycisk Podgląd, aby zobaczyć, jak wyglądają dane:

Otwórz plik app.py w folderze bigquery_demo i zastąp kod tym kodem:

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT subject AS subject, COUNT(*) AS num_duplicates
    FROM bigquery-public-data.github_repos.commits
    GROUP BY subject
    ORDER BY num_duplicates
    DESC LIMIT 10
"""
results = client.query(query)

for row in results:
    subject = row['subject']
    num_duplicates = row['num_duplicates']
    print(f'{subject:<20} | {num_duplicates:>9,}')

Poświęć chwilę na przeanalizowanie kodu i sprawdź, jak w tabeli wyszukiwane są najczęstsze komunikaty o zatwierdzeniu.

Wróć do Cloud Shell i uruchom aplikację:

python3 app.py

Powinna pojawić się lista komunikatów o zatwierdzeniu i ich wystąpień:

Update README.md     | 1,685,515
Initial commit       | 1,577,543
update               |   211,017
                     |   155,280
Create README.md     |   153,711
Add files via upload |   152,354
initial commit       |   145,224
first commit         |   110,314
Update index.html    |    91,893
Update README        |    88,862

Uwaga: ten zbiór danych jest okresowo aktualizowany, więc wyniki mogą się różnić.

9. Pamięć podręczna i statystyki

BigQuery buforuje wyniki zapytań. Dzięki temu kolejne zapytania będą wykonywane szybciej. Pamięć podręczną można wyłączyć za pomocą opcji zapytania. BigQuery śledzi też statystyki zapytań, takie jak czas utworzenia, czas zakończenia i łączna liczba przetworzonych bajtów.

W tym kroku wyłączysz buforowanie i wyświetlisz statystyki zapytań.

Otwórz plik app.py w folderze bigquery_demo i zastąp kod tym kodem:

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT subject AS subject, COUNT(*) AS num_duplicates
    FROM bigquery-public-data.github_repos.commits
    GROUP BY subject
    ORDER BY num_duplicates
    DESC LIMIT 10
"""
job_config = bigquery.job.QueryJobConfig(use_query_cache=False)
results = client.query(query, job_config=job_config)

for row in results:
    subject = row['subject']
    num_duplicates = row['num_duplicates']
    print(f'{subject:<20} | {num_duplicates:>9,}')

print('-'*60)
print(f'Created: {results.created}')
print(f'Ended:   {results.ended}')
print(f'Bytes:   {results.total_bytes_processed:,}')

Kilka uwag na temat kodu. Najpierw wyłącz buforowanie, wprowadzając QueryJobConfig i ustawiając wartość use_query_cache na false. Po drugie, statystyki zapytania zostały pobrane z obiektu zadania.

Wróć do Cloud Shell i uruchom aplikację:

python3 app.py

Podobnie jak wcześniej powinna się wyświetlić lista komunikatów o zatwierdzeniu i ich wystąpień. Na końcu powinny się też pojawić statystyki dotyczące zapytania:

Update README.md     | 1,685,515
Initial commit       | 1,577,543
update               |   211,017
                     |   155,280
Create README.md     |   153,711
Add files via upload |   152,354
initial commit       |   145,224
first commit         |   110,314
Update index.html    |    91,893
Update README        |    88,862
------------------------------------------------------------
Created: 2020-04-03 13:30:08.801000+00:00
Ended:   2020-04-03 13:30:15.334000+00:00
Bytes:   2,868,251,894

10. Wczytywanie danych do BigQuery

Jeśli chcesz wysyłać zapytania do własnych danych, musisz wczytać je do BigQuery. BigQuery obsługuje wczytywanie danych z wielu źródeł, w tym Cloud Storage, innych usług Google i innych źródeł, z których można odczytywać dane. Możesz nawet przesyłać strumieniowo dane za pomocą wstawiania strumieniowego. Więcej informacji znajdziesz na stronie Wczytywanie danych do BigQuery.

W tym kroku wczytasz plik JSON przechowywany w Cloud Storage do tabeli BigQuery. Plik JSON znajduje się w lokalizacji gs://cloud-samples-data/bigquery/us-states/us-states.json

Jeśli chcesz sprawdzić zawartość pliku JSON, możesz go pobrać w Cloud Shell za pomocą narzędzia wiersza poleceń gsutil:

gsutil cp gs://cloud-samples-data/bigquery/us-states/us-states.json .

Jak widać, zawiera on listę stanów USA, a każdy stan jest dokumentem JSON w osobnym wierszu:

head us-states.json

{"name": "Alabama", "post_abbr": "AL"}
{"name": "Alaska", "post_abbr":  "AK"}
...

Aby wczytać ten plik JSON do BigQuery, otwórz plik app.py w folderze bigquery_demo i zastąp kod tym kodem:

from google.cloud import bigquery

client = bigquery.Client()

gcs_uri = 'gs://cloud-samples-data/bigquery/us-states/us-states.json'

dataset = client.create_dataset('us_states_dataset')
table = dataset.table('us_states_table')

job_config = bigquery.job.LoadJobConfig()
job_config.schema = [
    bigquery.SchemaField('name', 'STRING'),
    bigquery.SchemaField('post_abbr', 'STRING'),
]
job_config.source_format = bigquery.SourceFormat.NEWLINE_DELIMITED_JSON

load_job = client.load_table_from_uri(gcs_uri, table, job_config=job_config)

print('JSON file loaded to BigQuery')

Poświęć chwilę na zapoznanie się z tym, jak kod wczytuje plik JSON i tworzy tabelę ze schematem w zbiorze danych.

Wróć do Cloud Shell i uruchom aplikację:

python3 app.py

W BigQuery zostaną utworzone zbiór danych i tabela.

Aby sprawdzić, czy zbiór danych został utworzony, otwórz konsolę BigQuery. Powinny pojawić się nowy zbiór danych i tabela. Aby wyświetlić dane, przełącz się na kartę podglądu tabeli:

11. Gratulacje!

Wiesz już, jak korzystać z BigQuery w Pythonie.

Czyszczenie danych

Aby uniknąć obciążenia konta Google Cloud opłatami za zasoby zużyte w tym samouczku:

• W Cloud Console otwórz stronę Zarządzanie zasobami.
• Na liście projektów wybierz projekt, a następnie kliknij Usuń.
• W oknie wpisz identyfikator projektu i kliknij Wyłącz, aby usunąć projekt.

Więcej informacji

• Google BigQuery: https://cloud.google.com/bigquery/docs/
• Python w Google Cloud: https://cloud.google.com/python/
• Biblioteki klienta Google Cloud do Pythona: https://googleapis.github.io/google-cloud-python/

Licencja

To zadanie jest licencjonowane na podstawie ogólnej licencji Creative Commons Attribution 2.0.