1. Обзор
BigQuery — это полностью управляемое, масштабируемое до петабайтов и недорогое хранилище аналитических данных от Google. BigQuery не требует управления инфраструктурой и не нуждается в администраторе базы данных, поэтому вы можете сосредоточиться на анализе данных для получения ценных выводов, использовать привычный SQL и воспользоваться преимуществами модели оплаты по мере использования.
В этом практическом занятии вы будете использовать клиентские библиотеки Google Cloud для Python, чтобы выполнять запросы к общедоступным наборам данных BigQuery с помощью Python.
Что вы узнаете
- Как использовать Cloud Shell
- Как включить API BigQuery
- Как аутентифицировать запросы API
- Как установить клиентскую библиотеку Python
- Как задавать вопросы по поводу произведений Шекспира
- Как выполнить запрос к набору данных GitHub
- Как настроить кэширование и отображение статистики
Что вам понадобится
Опрос
Как вы будете использовать этот учебный материал?
Как бы вы оценили свой опыт работы с Python?
Как бы вы оценили свой опыт использования сервисов Google Cloud?
2. Настройка и требования
Настройка среды для самостоятельного обучения
- Войдите в консоль Google Cloud и создайте новый проект или используйте существующий. Если у вас еще нет учетной записи Gmail или Google Workspace, вам необходимо ее создать .
- Название проекта — это отображаемое имя участников данного проекта. Это строка символов, не используемая API Google, и вы можете изменить её в любое время.
- Идентификатор проекта должен быть уникальным для всех проектов Google Cloud и неизменяемым (его нельзя изменить после установки). Консоль Cloud автоматически генерирует уникальную строку; обычно вам неважно, какая она. В большинстве практических заданий вам потребуется указать идентификатор проекта (обычно он обозначается как
PROJECT_ID), поэтому, если он вам не нравится, сгенерируйте другой случайный идентификатор или попробуйте свой собственный и посмотрите, доступен ли он. Затем он "замораживается" после создания проекта. - Существует третье значение — номер проекта , который используется некоторыми API. Подробнее обо всех трех значениях можно узнать в документации .
- Далее вам потребуется включить оплату в консоли Cloud, чтобы использовать ресурсы/API Cloud. Выполнение этого практического задания не должно стоить дорого, если вообще что-либо. Чтобы отключить ресурсы и избежать дополнительных расходов после завершения этого урока, следуйте инструкциям по «очистке», приведенным в конце практического задания. Новые пользователи Google Cloud имеют право на бесплатную пробную версию стоимостью 300 долларов США .
Запустить Cloud Shell
Хотя Google Cloud можно управлять удаленно с ноутбука, в этом практическом занятии вы будете использовать Google Cloud Shell — среду командной строки, работающую в облаке.
Активировать Cloud Shell
- В консоли Cloud нажмите «Активировать Cloud Shell» .
.
Если вы никогда раньше не запускали Cloud Shell, вам будет показан промежуточный экран (внизу), описывающий его назначение. В этом случае нажмите «Продолжить» (и вы больше никогда его не увидите). Вот как выглядит этот одноразовый экран:
Подготовка и подключение к Cloud Shell займут всего несколько минут.
Эта виртуальная машина оснащена всеми необходимыми инструментами разработки. Она предоставляет постоянный домашний каталог размером 5 ГБ и работает в облаке Google, что значительно повышает производительность сети и аутентификацию. Большая часть, если не вся, работа в этом практическом задании может быть выполнена с помощью обычного браузера или вашего Chromebook.
После подключения к Cloud Shell вы увидите, что ваша аутентификация пройдена и что проект уже настроен на ваш идентификатор проекта.
- Выполните следующую команду в Cloud Shell, чтобы подтвердить свою аутентификацию:
gcloud auth list
вывод команды
Credentialed Accounts
ACTIVE ACCOUNT
* <my_account>@<my_domain.com>
To set the active account, run:
$ gcloud config set account `ACCOUNT`
- Выполните следующую команду в Cloud Shell, чтобы убедиться, что команда gcloud знает о вашем проекте:
gcloud config list project
вывод команды
[core] project = <PROJECT_ID>
Если это не так, вы можете установить это с помощью следующей команды:
gcloud config set project <PROJECT_ID>
вывод команды
Updated property [core/project].
3. Включите API
API BigQuery должен быть включен по умолчанию во всех проектах Google Cloud. Проверить это можно с помощью следующей команды в Cloud Shell: Ваш проект должен отображаться в списке проектов BigQuery:
gcloud services list
В списке должен отображаться BigQuery:
NAME TITLE bigquery.googleapis.com BigQuery API ...
Если API BigQuery не включен, вы можете включить его с помощью следующей команды в Cloud Shell:
gcloud services enable bigquery.googleapis.com
4. Аутентификация API-запросов
Для отправки запросов к API BigQuery вам потребуется использовать учетную запись службы . Учетная запись службы принадлежит вашему проекту и используется клиентской библиотекой Google Cloud Python для отправки запросов к API BigQuery. Как и любая другая учетная запись пользователя, учетная запись службы представлена адресом электронной почты. В этом разделе вы будете использовать Cloud SDK для создания учетной записи службы, а затем создадите учетные данные, необходимые для аутентификации в качестве учетной записи службы.
Сначала установите переменную среды PROJECT_ID :
export PROJECT_ID=$(gcloud config get-value core/project)
Далее создайте новую учетную запись службы для доступа к API BigQuery, используя:
gcloud iam service-accounts create my-bigquery-sa \ --display-name "my bigquery service account"
Далее создайте учетные данные, которые ваш код на Python будет использовать для входа в систему под вашей новой учетной записью службы. Создайте эти учетные данные и сохраните их в виде JSON-файла ~/key.json , используя следующую команду:
gcloud iam service-accounts keys create ~/key.json \
--iam-account my-bigquery-sa@${PROJECT_ID}.iam.gserviceaccount.com
Наконец, установите переменную среды GOOGLE_APPLICATION_CREDENTIALS , которая используется клиентской библиотекой BigQuery для Python (см. следующий шаг), чтобы найти свои учетные данные. Переменная среды должна быть установлена на полный путь к созданному вами JSON-файлу с учетными данными, используя:
export GOOGLE_APPLICATION_CREDENTIALS=~/key.json
Вы можете узнать больше об аутентификации в API BigQuery .
5. Настройте контроль доступа.
BigQuery использует систему управления идентификацией и доступом (IAM) для управления доступом к ресурсам. В BigQuery есть ряд предопределенных ролей (пользователь, владелец данных, пользователь данных и т. д.), которые вы можете назначить своей учетной записи службы, созданной на предыдущем шаге. Подробнее о контроле доступа можно узнать в документации BigQuery.
Прежде чем запрашивать общедоступные наборы данных, необходимо убедиться, что у учетной записи службы есть как минимум роль roles/bigquery.user . В Cloud Shell выполните следующую команду, чтобы назначить роль пользователя учетной записи службы:
gcloud projects add-iam-policy-binding ${PROJECT_ID} \
--member "serviceAccount:my-bigquery-sa@${PROJECT_ID}.iam.gserviceaccount.com" \
--role "roles/bigquery.user"
Для проверки наличия у учетной записи службы роли пользователя можно выполнить следующую команду:
gcloud projects get-iam-policy $PROJECT_ID
Вы должны увидеть следующее:
bindings: - members: - serviceAccount:my-bigquery-sa@<PROJECT_ID>.iam.gserviceaccount.com role: roles/bigquery.user ...
6. Установите клиентскую библиотеку.
Установите клиентскую библиотеку BigQuery для Python:
pip3 install --user --upgrade google-cloud-bigquery
Теперь вы готовы к работе с API BigQuery!
7. Проанализируйте произведения Шекспира.
Общедоступный набор данных — это любой набор данных, хранящийся в BigQuery и доступный для широкой публики. Существует множество других общедоступных наборов данных, к которым вы можете обращаться с запросами. Хотя некоторые наборы данных размещаются Google, большинство размещаются сторонними организациями. Для получения дополнительной информации см. страницу «Общедоступные наборы данных» .
Помимо общедоступных наборов данных, BigQuery предоставляет ограниченное количество таблиц-примеров , к которым можно обращаться с запросами. Эти таблицы содержатся в наборе bigquery-public-data:samples . Таблица shakespeare в наборе данных samples содержит указатель слов произведений Шекспира. В ней указано количество раз, которое каждое слово встречается в каждом корпусе.
На этом этапе вы выполните запрос к таблице shakespeare .
Сначала в Cloud Shell создайте простое приложение на Python, которое вы будете использовать для запуска примеров API перевода.
mkdir bigquery-demo cd bigquery-demo touch app.py
Откройте редактор кода в правом верхнем углу Cloud Shell:
Перейдите к файлу app.py в папке bigquery-demo и замените код следующим.
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}')
Уделите минуту-две изучению кода и посмотрите, как выполняется запрос к таблице.
Вернувшись в Cloud Shell, запустите приложение:
python3 app.py
Вы должны увидеть список слов и количество их вхождений:
hamlet | 5318 kinghenryv | 5104 cymbeline | 4875 troilusandcressida | 4795 kinglear | 4784 kingrichardiii | 4713 2kinghenryvi | 4683 coriolanus | 4653 2kinghenryiv | 4605 antonyandcleopatra | 4582
8. Выполните запрос к набору данных GitHub.
Чтобы лучше познакомиться с BigQuery, вы сейчас выполните запрос к общедоступному набору данных GitHub . Вы найдете наиболее часто встречающиеся сообщения коммитов на GitHub. Вы также будете использовать веб-консоль BigQuery для предварительного просмотра и выполнения произвольных запросов.
Чтобы увидеть, как выглядят данные, откройте набор данных GitHub в веб-интерфейсе BigQuery:
Нажмите кнопку «Предварительный просмотр», чтобы увидеть, как выглядят данные:
Перейдите к файлу app.py в папке bigquery_demo и замените код следующим.
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,}')
Уделите минуту-две изучению кода и посмотрите, как выполняется запрос к таблице для получения наиболее часто встречающихся сообщений коммитов.
Вернувшись в Cloud Shell, запустите приложение:
python3 app.py
Вы должны увидеть список сообщений коммитов и их количество:
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
9. Кэширование и статистика
BigQuery кэширует результаты запросов. В результате последующие запросы выполняются быстрее. Кэширование можно отключить с помощью параметров запроса. BigQuery также отслеживает статистику запросов, такую как время создания, время завершения и общее количество обработанных байтов.
На этом этапе вы отключите кэширование, а также отобразите статистику по запросам.
Перейдите к файлу app.py в папке bigquery_demo и замените код следующим.
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:,}')
Несколько замечаний по поводу кода. Во-первых, кэширование отключено путем добавления QueryJobConfig и установки use_query_cache в значение false. Во-вторых, вы получаете доступ к статистике запроса из объекта задания.
Back in Cloud Shell, run the app:
python3 app.py
Как и прежде, вы должны увидеть список сообщений коммитов и количество их вхождений. Кроме того, в конце вы также должны увидеть некоторую статистику по запросу:
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. Загрузка данных в BigQuery
Если вы хотите выполнять запросы к собственным данным, вам необходимо загрузить их в BigQuery. BigQuery поддерживает загрузку данных из множества источников, включая Cloud Storage, другие сервисы Google и другие доступные для чтения источники. Вы даже можете передавать данные потоком, используя потоковые вставки. Для получения дополнительной информации см. страницу «Загрузка данных в BigQuery» .
На этом шаге вы загрузите JSON-файл, хранящийся в Cloud Storage, в таблицу BigQuery. JSON-файл находится по адресу gs://cloud-samples-data/bigquery/us-states/us-states.json
Если вам интересно содержимое JSON-файла, вы можете использовать инструмент командной строки gsutil для его загрузки в Cloud Shell:
gsutil cp gs://cloud-samples-data/bigquery/us-states/us-states.json .
Как видите, здесь представлен список штатов США, и каждый штат представляет собой JSON-документ на отдельной строке:
head us-states.json
{"name": "Alabama", "post_abbr": "AL"}
{"name": "Alaska", "post_abbr": "AK"}
...
Чтобы загрузить этот JSON-файл в BigQuery, перейдите к файлу app.py в папке bigquery_demo и замените код следующим.
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')
Уделите минуту-две изучению того, как код загружает JSON-файл и создает таблицу со схемой в рамках набора данных.
Вернувшись в Cloud Shell, запустите приложение:
python3 app.py
В BigQuery создаются набор данных и таблица.
Чтобы убедиться, что набор данных создан, перейдите в консоль BigQuery . Вы должны увидеть новый набор данных и таблицу. Перейдите на вкладку предварительного просмотра таблицы, чтобы увидеть свои данные:
11. Поздравляем!
Вы научились использовать BigQuery с Python!
Уборка
Чтобы избежать списания средств с вашего аккаунта Google Cloud за ресурсы, использованные в этом руководстве:
- В консоли Cloud перейдите на страницу «Управление ресурсами» .
- В списке проектов выберите свой проект и нажмите «Удалить» .
- В диалоговом окне введите идентификатор проекта, а затем нажмите «Завершить» , чтобы удалить проект.
Узнать больше
- Google BigQuery: https://cloud.google.com/bigquery/docs/
- Python в Google Cloud: https://cloud.google.com/python/
- Клиентские библиотеки для облачных сервисов на Python: https://googleapis.github.io/google-cloud-python/
Лицензия
Данная работа распространяется под лицензией Creative Commons Attribution 2.0 Generic.