Секционирование и кластеризация в BigQuery

1. Введение

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

Набор данных BigQuery находится в проекте GCP и содержит одну или несколько таблиц. Вы можете запрашивать эти наборы данных с помощью SQL.

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

Для получения дополнительной информации о BigQuery см. документацию BigQuery .

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

  • Как создавать и запрашивать данные из секционированных и кластеризованных таблиц
  • Сравните производительность запросов с использованием секционированных и кластеризованных таблиц.

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

Для выполнения этой лабораторной работы вам потребуется:

  • Последняя версия Google Chrome
  • Платежный аккаунт Google Cloud Platform

2. Настройка

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

Создать проект

Для создания нового проекта выполните следующие действия:

  1. Если у вас еще нет учетной записи Google (Gmail или Google Apps), создайте ее .
  2. Войдите в консоль Google Cloud Platform ( console.cloud.google.com ) и создайте новый проект.
  3. Если у вас нет проектов, нажмите кнопку «Создать проект»:

870a3cbd6541ee86.png

В противном случае создайте новый проект из меню выбора проектов:

f6dff3437a20cf2.png

  1. Введите название проекта и выберите «Создать» . Обратите внимание, что идентификатор проекта — это уникальное имя для всех проектов Google Cloud.

1884405a64ce5765.png

3. Работа с общедоступными наборами данных

BigQuery позволяет работать с общедоступными наборами данных, включая данные BBC News, репозитории GitHub, Stack Overflow и данные Национального управления океанических и атмосферных исследований США (NOAA). Вам не нужно загружать эти наборы данных в BigQuery. Вам просто нужно открыть наборы данных, чтобы просматривать и запрашивать их в BigQuery. В этом практическом занятии вы будете работать с общедоступным набором данных Stack Overflow.

Просмотрите набор данных Stack Overflow

Набор данных Stack Overflow содержит информацию о сообщениях, тегах, значках, комментариях, пользователях и многом другом. Чтобы просмотреть набор данных Stack Overflow в веб-интерфейсе BigQuery, выполните следующие действия:

  1. Откройте набор данных Stack Overflow . В консоли GCP откроется веб-интерфейс BigQuery, отображающий информацию о наборе данных Stack Overflow.
  2. В панели навигации выберите bigquery-public-data . Меню развернется, отобразив список общедоступных наборов данных. Каждый набор данных содержит одну или несколько таблиц.
  3. Прокрутите вниз и выберите stackoverflow . Меню развернется, отобразив список таблиц из набора данных Stack Overflow.
  4. Выберите «Значки» , чтобы просмотреть схему таблицы значков. Обратите внимание на названия полей в таблице.
  5. Над названиями полей нажмите «Предварительный просмотр» , чтобы увидеть примеры данных для таблицы значков.

Для получения более подробной информации обо всех общедоступных наборах данных, доступных в BigQuery, см. раздел «Общедоступные наборы данных Google BigQuery» .

Запрос к набору данных Stackoverflow

Просмотр набора данных — хороший способ понять, с какими данными вы работаете, но именно в работе с запросами к наборам данных BigQuery проявляет себя наилучшим образом. В этом разделе вы узнаете, как выполнять запросы BigQuery. На данном этапе вам не нужно знать SQL. Вы можете скопировать и вставить запросы, приведенные ниже.

Для выполнения запроса выполните следующие шаги:

  1. В правом верхнем углу консоли GCP выберите пункт «Составить новый запрос» .
  2. В текстовом поле редактора запросов скопируйте и вставьте следующий SQL-запрос. BigQuery проверит запрос, и веб-интерфейс отобразит зеленую галочку под текстовым полем, указывающую на правильность синтаксиса. 
SELECT
  EXTRACT(YEAR FROM creation_date) AS creation_year,
  COUNT(*) AS total_posts
FROM `bigquery-public-data.stackoverflow.posts_questions`
GROUP BY creation_year
ORDER BY total_posts DESC
LIMIT 10
  1. Выберите «Выполнить» . Запрос вернет количество сообщений или вопросов на Stack Overflow, опубликованных каждый год.

4. Создание новой таблицы

В предыдущем разделе вы выполняли запросы к общедоступным наборам данных, которые предоставляет BigQuery. В этом разделе вы создадите новую таблицу в BigQuery на основе существующей таблицы . Вы создадите новую таблицу с данными, взятыми из общедоступного набора данных Stack Overflow — таблицы posts_questions , а затем выполните запрос к этой таблице.

Создать новый набор данных

Для создания и загрузки табличных данных в BigQuery сначала создайте набор данных BigQuery, который будет содержать эти данные, выполнив следующие шаги:

  1. В панели навигации консоли GCP выберите имя проекта, созданного в процессе настройки.
  2. В правой части экрана, в панели сведений, выберите «Создать набор данных» .

acc6378c49622323.png

  1. В диалоговом окне «Создать набор данных» в поле «Идентификатор набора данных» введите stackoverflow . Оставьте все остальные настройки по умолчанию и нажмите «ОК» .

7a2dfd8bcb8f259a.png

Создайте новую таблицу с сообщениями StackOverflow за 2018 год.

Теперь, когда вы создали набор данных BigQuery, вы можете создать новую таблицу в BigQuery. Чтобы создать таблицу с данными из существующей таблицы, вам нужно будет выполнить запрос к набору данных «Сообщения Stack Overflow за 2018 год» и записать результаты в новую таблицу, выполнив следующие шаги:

  1. В правом верхнем углу консоли GCP выберите пункт «Составить новый запрос» .

9ca55f544e8da8bd.png

  1. В текстовой области редактора запросов скопируйте и вставьте следующий SQL-запрос для создания новой таблицы, который представляет собой оператор DDL . 
CREATE OR REPLACE TABLE `stackoverflow.questions_2018` AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';
  1. Выберите «Выполнить» . Запрос создаст новую таблицу questions_2018 в наборе данных stackoverflow в вашем проекте, содержащую данные, полученные в результате выполнения запроса к набору данных BigQuery Stack Overflow bigquery-public-data.stackoverflow.posts_questions .

Запрос к новой таблице с использованием сообщений Stack Overflow за 2018 год.

Теперь, когда вы создали таблицу BigQuery, давайте выполним запрос, чтобы получить сообщения со Stack Overflow с вопросами и заголовками, а также некоторую другую статистику, такую ​​как количество ответов, комментариев, просмотров и добавлений в избранное. Выполните следующие шаги:

  1. В правом верхнем углу консоли GCP выберите пункт «Составить новый запрос» .
  2. В текстовом поле редактора запросов скопируйте и вставьте следующий SQL-запрос. 
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';
  1. Выберите «Выполнить» . Запрос вернет вопросы Stack Overflow, созданные в январе 2018 года и помеченные тегом android а также сам вопрос и некоторые другие статистические данные.
  2. По умолчанию BigQuery кэширует результаты запроса . Выполните тот же запрос, и вы увидите, что BigQuery потребовалось гораздо меньше времени для возврата результатов, потому что он возвращает результаты из кэша.
  3. Выполните тот же запрос еще раз, но на этот раз с отключенным кэшированием BigQuery. Мы отключим кэширование на протяжении всей оставшейся части лабораторной работы, чтобы обеспечить объективность сравнения производительности с секционированными и кластеризованными таблицами, которые будут запущены в следующих разделах. В редакторе запросов нажмите «Дополнительно» и выберите «Настройки запроса» . Настройки запроса
  4. В разделе «Кэширование» снимите флажок «Использовать кэшированные результаты» . опция кэшированных результатов
  5. В результатах запроса вы должны увидеть время, затраченное на выполнение запроса, и объем обработанных данных для получения результатов.

f197b022b4276338.png

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

В предыдущем разделе вы создали новую таблицу в BigQuery с данными из таблицы posts_questions , используя общедоступный набор данных Stack Overflow. Мы выполнили запрос к этому набору данных с отключенным кэшированием и оценили производительность запроса. В этом разделе вы создадите новую секционированную таблицу из той же общедоступной таблицы Stack Overflow posts_questions и оцените производительность запроса.

Разделенная таблица — это особая таблица, которая разделена на сегменты, называемые разделами, что упрощает управление данными и выполнение запросов к ним. Как правило, большие таблицы можно разделить на множество более мелких разделов, используя время загрузки данных, столбец TIMESTAMP/DATE или столбец типа INTEGER. Мы создадим таблицу с разделением по типу DATE.

Подробнее о секционированных таблицах можно узнать здесь .

Создание новой секционированной таблицы с использованием сообщений StackOverflow за 2018 год.

Чтобы создать секционированную таблицу с данными из существующей таблицы или запроса, вам нужно будет запросить набор данных «Сообщения Stackoverflow за 2018 год» и записать результаты в новую таблицу, выполнив следующие шаги:

b9d0ca4df0881f58.png

  1. В правом верхнем углу консоли GCP выберите пункт «Составить новый запрос» .

9ca55f544e8da8bd.png

  1. В текстовой области редактора запросов скопируйте и вставьте следующий SQL-запрос для создания новой таблицы, который представляет собой оператор DDL . 
CREATE OR REPLACE TABLE `stackoverflow.questions_2018_partitioned` 
PARTITION BY DATE(creation_date) AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';
  1. Выберите «Выполнить» . Запрос создаст новую таблицу questions_2018_partitioned в наборе данных stackoverflow в вашем проекте с данными, полученными в результате выполнения запроса к набору данных BigQuery Stack Overflow bigquery-public-data.stackoverflow.posts_questions

Запрос к секционированной таблице с использованием сообщений Stack Overflow 2018 года

Теперь, когда вы создали секционированную таблицу в BigQuery, давайте выполним тот же запрос, на этот раз к секционированной таблице, чтобы получить сообщения со Stack Overflow с вопросами и заголовками, а также некоторую другую статистику, такую ​​как количество ответов, комментариев, просмотров и добавлений в избранное. Выполните следующие шаги:

  1. В правом верхнем углу консоли GCP выберите пункт «Составить новый запрос» .
  2. В текстовом поле редактора запросов скопируйте и вставьте следующий SQL-запрос. 
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_partitioned` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';
  1. Выберите «Запустить с отключенным кэшированием BigQuery» (см. предыдущий раздел для отключения кэширования BigQuery). Запрос вернет вопросы Stack Overflow, созданные в январе 2018 года и помеченные тегом android а также сам вопрос и некоторые другие статистические данные.
  2. В результатах запроса вы должны увидеть время, затраченное на выполнение запроса, и объем обработанных данных для получения результатов.

ef01144374069823.png

Вы заметите, что производительность запроса с секционированной таблицей выше, чем с несекционированной, поскольку BigQuery удаляет разделы, то есть сканирует только необходимые разделы, обрабатывая меньше данных и работая быстрее. Это оптимизирует затраты и производительность запроса.

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

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

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

Подробнее о работе с кластерными таблицами можно узнать здесь .

Создание новой кластерной таблицы с сообщениями Stack Overflow за 2018 год.

В этом разделе вы создадите новую таблицу, секционированную по столбцу creation_date и кластеризованную по столбцу tags в соответствии с шаблоном доступа к запросу. Чтобы создать кластеризованную таблицу с данными из существующей таблицы или запроса, вам нужно будет запросить таблицу сообщений Stack Overflow за 2018 год и записать результаты в новую таблицу, выполнив следующие шаги:

e7d9acc0dc3b9d79.png

  1. В правом верхнем углу консоли GCP выберите пункт «Составить новый запрос» .

9ca55f544e8da8bd.png

  1. В текстовой области редактора запросов скопируйте и вставьте следующий SQL-запрос для создания новой таблицы, который представляет собой оператор DDL . 
#standardSQL
CREATE OR REPLACE TABLE `stackoverflow.questions_2018_clustered`
PARTITION BY
  DATE(creation_date)
CLUSTER BY
  tags AS
SELECT
  id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM
  `bigquery-public-data.stackoverflow.posts_questions`
WHERE
  creation_date BETWEEN '2018-01-01' AND '2019-01-01';
  1. Выберите «Выполнить». Запрос создаст новую таблицу questions_2018_clustered в наборе данных stackoverflow в вашем проекте с данными, полученными в результате выполнения запроса к таблице BigQuery Stack Overflow bigquery-public-data.stackoverflow.posts_questions . Новая таблица будет секционирована по столбцу creation_date и кластеризована по столбцу tags.

Запрос к кластерной таблице с использованием сообщений Stack Overflow за 2018 год.

Теперь, когда вы создали кластеризованную таблицу BigQuery, давайте снова выполним тот же запрос, на этот раз к секционированной и кластеризованной таблице, чтобы получить сообщения Stack Overflow с вопросами и заголовками, а также некоторую другую статистику, такую ​​как количество ответов, комментариев, просмотров и добавлений в избранное. Выполните следующие шаги:

  1. В правом верхнем углу консоли GCP выберите пункт «Составить новый запрос» .
  2. В текстовом поле редактора запросов скопируйте и вставьте следующий SQL-запрос. 
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_clustered` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';
  1. Выберите «Запустить с отключенным кэшированием BigQuery» (см. предыдущий раздел для отключения кэширования BigQuery). Запрос вернет вопросы Stack Overflow, созданные в январе 2018 года и помеченные тегом android а также сам вопрос и некоторые другие статистические данные.
  2. В результатах запроса вы должны увидеть время, затраченное на выполнение запроса, и объем обработанных данных для получения результатов.

85e3c30d6fb3d547.png

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

7. Уборка

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

Удалите набор данных BigQuery.

Для удаления набора данных BigQuery выполните следующие действия:

  1. Выберите набор данных StackOverflow на левой боковой панели навигации в BigQuery.
  2. В панели сведений выберите «Удалить набор данных» . 67b0f5cb740cb2ec.png
  3. В диалоговом окне «Удалить набор данных» введите stackoverflow и выберите «Удалить» , чтобы подтвердить удаление набора данных.

Удалить проект

Чтобы удалить проект GCP, созданный для этого практического занятия, выполните следующие действия:

  1. В навигационном меню GCP выберите IAM & Admin .
  2. В панели навигации выберите «Настройки» .
  3. В панели сведений убедитесь, что ваш текущий проект — это тот же проект, который вы создали для этого практического занятия, и выберите «Завершить» .
  4. В диалоговом окне «Завершить проект» введите идентификатор проекта (не название проекта) и выберите «Завершить» для подтверждения.

Поздравляем! Теперь вы узнали...

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

Обратите внимание, что для работы с наборами данных вам не нужно было настраивать или управлять кластерами.