Запросить набор данных Википедии в BigQuery

1. Обзор

BigQuery — это полностью управляемая, не требующая дополнительных операций (NoOps) и недорогая аналитическая база данных от Google. С BigQuery вы можете запрашивать терабайты данных без необходимости управления инфраструктурой и без администратора базы данных. BigQuery использует привычный SQL и может использовать модель оплаты по мере использования. BigQuery позволяет вам сосредоточиться на анализе данных для получения ценных выводов.

В этом практическом занятии вы изучите набор данных Википедии с помощью BigQuery.

Что вы узнаете

• Как использовать BigQuery
• Как загрузить реальный набор данных в BigQuery
• Как написать запрос для получения информации из большого набора данных

Что вам понадобится

• Проект Google Cloud
• Браузер, например, Google Chrome или Firefox.

Опрос

Как вы будете использовать этот учебный материал?

Только для чтения Прочитайте текст и выполните упражнения.

Как бы вы оценили свой опыт работы с Google Cloud?

Новичок Средний Профессионал

2. Настройка и требования

Включить BigQuery

Если у вас еще нет учетной записи Google, вам необходимо ее создать .

1. Войдите в консоль Google Cloud и перейдите в раздел BigQuery. Вы также можете открыть веб-интерфейс BigQuery напрямую, введя следующий URL-адрес в свой браузер.

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

2. Примите условия предоставления услуг.
3. Прежде чем начать использовать BigQuery, необходимо создать проект. Следуйте инструкциям, чтобы создать новый проект.

Выберите название проекта и запишите его идентификатор.

Идентификатор проекта — это уникальное имя для всех проектов Google Cloud. В дальнейшем в этом практическом занятии он будет обозначаться как PROJECT_ID .

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

В следующем разделе вы загрузите набор данных Википедии.

3. Создайте набор данных.

Сначала создайте новый набор данных в проекте. Набор данных состоит из нескольких таблиц.

1. Чтобы создать набор данных, щелкните имя проекта в панели ресурсов, а затем нажмите «Создать набор данных» :

2. В качестве идентификатора набора данных укажите lab :

3. Нажмите «Создать набор данных» , чтобы создать пустой набор данных.

4. Загрузите данные с помощью программы командной строки bq.

Активировать Cloud Shell

1. В консоли Cloud нажмите «Активировать Cloud Shell» . .

Если вы никогда раньше не запускали Cloud Shell, вам будет показан промежуточный экран (внизу), описывающий его назначение. В этом случае нажмите «Продолжить» (и вы больше никогда его не увидите). Вот как выглядит этот одноразовый экран:

Подготовка и подключение к Cloud Shell займут всего несколько минут.

Эта виртуальная машина оснащена всеми необходимыми инструментами разработки. Она предоставляет постоянный домашний каталог размером 5 ГБ и работает в облаке Google, что значительно повышает производительность сети и аутентификацию. Большая часть, если не вся, работа в этом практическом задании может быть выполнена с помощью обычного браузера или вашего Chromebook.

После подключения к Cloud Shell вы увидите, что ваша аутентификация пройдена и что проект уже настроен на ваш идентификатор проекта.

2. Выполните следующую команду в 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`

Примечание: Инструмент командной строки gcloud — это мощный и унифицированный инструмент командной строки в Google Cloud. Он предустановлен в Cloud Shell. Вы заметите, что он поддерживает автодополнение по клавише Tab. Для получения дополнительной информации см. обзор инструмента командной строки gcloud .

3. Выполните следующую команду в Cloud Shell, чтобы убедиться, что команда gcloud знает о вашем проекте:

gcloud config list project

вывод команды

[core]
project = <PROJECT_ID>

Если это не так, вы можете установить это с помощью следующей команды:

gcloud config set project <PROJECT_ID>

вывод команды

Updated property [core/project].

Загрузите данные в BigQuery.

Для вашего удобства часть данных за 10 апреля 2019 года из набора данных о просмотрах страниц Wikimedia доступна в Google Cloud Storage по gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz . Файл данных представляет собой сжатый с помощью GZip CSV-файл. Вы можете загрузить этот файл напрямую, используя утилиту командной строки bq . В команде загрузки вы также указываете схему файла.

Идентификатор проекта можно найти в консоли API или в командной строке: выполните команду ` gcloud config list и найдите значение параметра ` project .

Кроме того, при первом запуске bq может потребоваться выбрать проект по умолчанию. После выбора вы можете повторно запустить указанную выше команду для загрузки данных.

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

Загрузка данных может занять несколько минут. Дождитесь завершения процесса.

Вы использовали несколько расширенных параметров для загрузки файла page-views:

• Установите параметр --source_format CSV , чтобы указать, что файл должен быть обработан как CSV-файл. Этот шаг необязателен, поскольку CSV является форматом по умолчанию.
• Установите параметр --field_delimiter " " , чтобы указать, что для разделения полей используется один пробел.
• Установите параметр --allow_jagged_rows , чтобы включить строки с меньшим количеством столбцов и игнорировать ошибки при загрузке CSV-файла.
• Установите параметр --quote "" , чтобы указать, что строки не заключены в кавычки.
• Установите параметр --max_bad_records 3 , чтобы игнорировать не более 3 ошибок при разборе CSV-файла.

Более подробную информацию о команде bq можно найти в документации .

5. Предварительный просмотр набора данных

В консоли BigQuery откройте одну из таблиц, которые вы только что загрузили.

1. Расширить проект.
2. Расширьте набор данных.
3. Выберите таблицу.

Схему таблицы можно увидеть на вкладке «Схема» . 4. Чтобы узнать объем данных в таблице, перейдите на вкладку «Подробности» :

5. Откройте вкладку «Предварительный просмотр» , чтобы увидеть выбранные строки из таблицы.

6. Составьте запрос.

1. Нажмите «Создать новый запрос» :

Это открывает редактор запросов :

2. Чтобы узнать общее количество просмотров на Викимедиа в период с 14:00 до 15:00 10 апреля 2019 года, напишите следующий запрос:

SELECT SUM(requests)
FROM `lab.pageviews_20190410_140000`

3. Нажмите «Выполнить» :

Через несколько секунд результат отобразится внизу, и там же будет указано, сколько данных было обработано:

Этот запрос обработал 123,9 МБ данных, хотя размер таблицы составляет 691,4 МБ. BigQuery обрабатывает только те байты, которые используются в запросе, поэтому общий объем обработанных данных может быть значительно меньше размера таблицы. С помощью кластеризации и секционирования объем обрабатываемых данных можно еще больше сократить.

Вы можете бесплатно обрабатывать/запрашивать до 1 ТБ данных каждый месяц. Подробнее см. на странице с ценами BigQuery .

7. Более сложные запросы

Найти количество просмотров страниц Википедии

Набор данных Wikimedia содержит информацию о просмотрах страниц всех проектов Wikimedia (включая Википедию, Викисловарь, Викикниги и Викицитаты). Чтобы сузить запрос и оставить только страницы англоязычной Википедии, добавьте оператор WHERE :

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

Обратите внимание, что при добавлении дополнительного столбца wiki объем обрабатываемых данных увеличился со 124 МБ до 204 МБ.

BigQuery поддерживает множество распространенных SQL-запросов, таких как CONTAINS , GROUP BY, ORDER BY , а также ряд агрегатных функций. Кроме того, вы можете использовать регулярные выражения для запроса текстовых полей! Попробуйте одно из них:

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

Подробнее о синтаксисе запросов BigQuery можно узнать в справочной документации по запросам BigQuery .

Запрос к нескольким таблицам

Для объединения таблиц можно выбрать диапазон таблиц, используя таблицу с подстановочным знаком .

1. Сначала создайте вторую таблицу для выполнения запросов, загрузив в нее данные о просмотрах страниц за следующий час:

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. В редакторе запросов выполните запрос к обеим загруженным таблицам, используя префикс " pageviews_2019 ":

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

Вы можете более избирательно фильтровать таблицы с помощью псевдостолбца _TABLE_SUFFIX . Этот запрос ограничивает выборку таблицами, соответствующими 10 апреля.

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. Уборка

При желании удалите набор данных, созданный с помощью команды bq rm . Используйте флаг -r , чтобы удалить все содержащиеся в нем таблицы.

bq rm -r lab

9. Поздравляем!

Вы использовали BigQuery и SQL для запроса к реальному набору данных о просмотрах страниц Википедии. У вас есть возможность обрабатывать данные петабайтного масштаба!

Узнать больше

• Посмотрите сабреддит BigQuery, чтобы узнать, как другие используют BigQuery сегодня.
• Найдите общедоступные наборы данных, доступные в BigQuery .
• Узнайте, как загружать данные в BigQuery .