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

1. Обзор

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

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

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

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

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

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

Опрос

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

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

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

Новичок Средний Опытный

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

Включить BigQuery

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

1. Войдите в Google Cloud Console и перейдите в 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 Console нажмите «Активировать Cloud Shell». .

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

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

Эта виртуальная машина оснащена всеми необходимыми инструментами разработки. Он предлагает постоянный домашний каталог объемом 5 ГБ и работает в Google Cloud, что значительно повышает производительность сети и аутентификацию. Большую часть, если не всю, работу в этой лаборатории кода можно выполнить с помощью просто браузера или 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. Вы заметите поддержку завершения табуляции. Дополнительную информацию см. в обзоре инструмента командной строки 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 года из набора данных о просмотрах страниц Викимедиа доступны в облачном хранилище Google по адресу gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz . Файл данных представляет собой CSV-файл в формате GZip. Вы можете загрузить этот файл напрямую с помощью утилиты командной строки 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

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

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

• Установите --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. Более сложные запросы

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

Набор данных Викимедиа содержит просмотры страниц для всех проектов Викимедиа (включая Википедию, Викисловарь, Викикниги и Викицитаты). Ограничьте запрос только англоязычными страницами Википедии, добавив оператор 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 .