Секционирование и кластеризация в 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 . Веб-интерфейс BigQuery открывается в консоли GCP и отображает информацию о наборе данных Stackoverflow.
  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 на основе существующей таблицы . Вы создадите новую таблицу с данными, выбранными из таблицы posts_questions общедоступного набора данных Stack Overflow, а затем запросите эту таблицу.

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

Чтобы создать и загрузить данные таблицы в 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 .

Запрос новой таблицы с сообщениями о переполнении стека 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. Мы запросили этот набор данных с отключенным кэшированием и наблюдали за производительностью запроса. В этом разделе вы создадите новую секционированную таблицу на основе таблицы posts_questions общедоступного набора данных Stack Overflow и понаблюдаете за производительностью запроса.

Секционированная таблица — это специальная таблица, разделенная на сегменты, называемые разделами, которые упрощают управление данными и запросы к ним. Обычно большие таблицы можно разделить на множество более мелких секций, используя время приема данных, столбец 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

Запрос секционированной таблицы с сообщениями о переполнении стека 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. Мы запросили эту таблицу с отключенным кэшированием и наблюдали за производительностью запросов как с несекционированными, так и с секционированными таблицами. В этом разделе вы создадите новую кластеризованную таблицу на основе таблицы posts_questions общедоступного набора данных Stack Overflow и понаблюдаете за производительностью запроса.

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

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

Создайте новую кластеризованную таблицу с сообщениями о переполнении стека 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 . Новая таблица секционируется по дате создания и кластеризуется по столбцу тегов.

Запрос к кластеризованной таблице с сообщениями о переполнении стека 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 для создания новой таблицы на основе существующих таблиц.
  • Как создавать и запрашивать секционированные и кластеризованные таблицы
  • Как секционирование и кластеризация оптимизируют производительность запросов и затраты

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