Подключаем Jupyter Notebook к Google Bigquery

Сегодня рассмотрим такой интересный кейс, как работу с Bigquery прямо из оболочки Jupyter. Возможно, вы подумаете, а зачем козе баян? Ведь тот же код можно запускать и напрямую в веб-интерфейсе GBQ. Несомненно, веб-интерфейс позволяет отслеживать такие параметры запроса как его объем, скорость выполнения, а также правильность составления самого тела запроса, чего в Jupyter нет.
Однако есть и плюсы от такого решения. Данные запроса можно мгновенно загнать в датафрейм Pandas и начать исследовать, в то время как работая в веб-интерфейсе, вам сначала придется выгрузить их в CSV/JSON, а только потом загрузить в Pandas. При этом результаты запроса не должны превышать 16000 строк.
Что ж, достаточно для вступления, переходим к технической части.

Нам нужен сервисный аккаунт в Google Cloud

Подробно про создание сервисного аккаунта я рассказывал в этом посте. Процедура точно такая же, за исключением следующих моментов.
Во-первых, на шаге выбора API, к которому мы будем подключаться, надо выбрать BigQuery. 

Дальнейшие шаги повторяем вплоть до скачивания json-файла с credentials.
Второй момент, про который вы ничего не прочтете в справке Гугла - это необходимость вручную задать права для вашего сервисного аккаунта. То, что вы сами создали сервисный аккаунт в своем собственном GBQ еще ничего не значит. Если у него не будут заданы права/роли для работы в проекте, работать через него не получится. Поэтому, идем в левое боковое меню Google Cloud=>IAM и Администрирование=>Сервисные аккаунты и копируем адрес электронной почты нашего сервисного аккаунта. 

Затем из этого же окна, левое боковое меню=>IAM, нажимаем кнопку “Добавить”

В появившемся окне справа вставляем email нашего сервисного аккаунта и выбираем роль. 

Для тестовых целей я выбрал роль Администратор Bigquery, но на самом деле настройка ролей в Google cloud очень гибкая и можно задать параметры как простого юзера, так и редактора или просто на просмотр. Здесь можно экспериментировать.
Жмем сохранить и дальше нас ждет работа в консоли Windows.

Настраиваем Credentials для авторизованного подключения.

Как говорится в справке Гугла, нам понадобится скачать и установить клиент для подключения. 

pip install --upgrade 'google-cloud-bigquery[pandas]'

В зависимости от того, ставите вы пакет google-cloud-bigquery в новое окружение или в базовое окружение Python могут возникать проблемы с командой pip install , которая приведена в справке. Я ставил в уже существующее окружение и pip у меня она не заработала.
Если у вас установлен пакетный менеджер conda - пробуйте такую команду:

conda install -c conda-forge google-cloud-bigquery

У меня conda прошуршала по всем своим репозиториям и сама нашла тот, откуда можно установить клиент. У клиента, кстати, довольно много зависимостей и я не рекомендую ставить его в базовое окружение. После того как клиент установился, самое время вспомнить куда мы сохранили наш json-файлик, содержащий credentials от сервисного аккаунта. Нам понадобится путь к нему.

В консоли окружения вбиваем команду:

set GOOGLE_APPLICATION_CREDENTIALS=C:\Users\Vladimir\data-explorer-XXXXX-xxxxx3909a25.json;

Естественно, путь до файла прописываете свой. Никаких кавычек, никаких квадратных скобок и конечно же слэши здесь нужно ставить обратные ‘ \ ‘ (a-ka windows style)
После этой команды сразу запускаете

jupyter notebook

Установленные командой выше параметры доступа действуют только одну сессию. Если вы завершили работу в окружении, то с новой сессией их надо установить заново.

То ради чего все это затевалось

Начинаем работу в ноутбуке. Для начала подгрузим библиотеки и создадим клиент:

import pandas as pd
from google.cloud import bigquery

PROJECT_ID = 'data-explorer-xxxxx'
client = bigquery.Client(project = PROJECT_ID)

Подключимся к публичному датасету COVID-19 USA FACTS и попробуем сделать к нему простой SELECT запрос:

QUERY = ('SELECT * FROM `bigquery-public-data.covid19_usafacts.summary` LIMIT 10')
query_job = client.query(QUERY)
rows = query_job.result()

print("Started job: {}".format(query_job.job_id))
for row in query_job:
    #print(row) # вывод полных результатов
    print("{}: \t{}".format(row.state, row.deaths)) # форматированный вывод выбранных столбцов


Как видим, все работает:

Ну, писать все эти запросы в кавычках, так себе. А что если писать запрос сразу, как в консоли SQL и без этих ваших циклов? И здесь есть выход. Подключаем магическую команду:

%load_ext google.cloud.bigquery

И пишем запрос. как будто вы в SQL-консоли:

%%bigquery
#SELECT * FROM `bigquery-public-data.covid19_usafacts.summary` LIMIT 15
SELECT state, EXTRACT(MONTH FROM date) month, SUM(confirmed_cases) cases, SUM(deaths) dth
FROM `bigquery-public-data.covid19_usafacts.summary`
GROUP BY state, month
ORDER BY dth DESC
LIMIT 15


Теперь попробуем выгрузить данные сразу в датафрейм Pandas:

QUERY = ('SELECT * FROM `bigquery-public-data.covid19_usafacts.summary` LIMIT 1000')
df = client.query(QUERY).to_dataframe()


Итого, имеем Pandas-датафрейм с сырыми данными, с которым можно работать дальше: чистить, применять стат.метрики, строить визуализации.

Заключение

Сегодня мы получили практические навыки при работе с облаком Google Bigquery.
Создали сервисный аккаунт, настроили права доступа, подключились из консоли к облаку, немного поработали с SQL и выгрузили данные в датафрейм для дальнейшего исследования. Отлично!

И напоследок приведу ссылку на документацию по работе с GBQ из консоли, там масса полезной информации с примерами кода.
А это ссылка на мой ноутбук из этой статьи.