Нажмите здесь, чтобы перейти к первой части этой статьи

Продолжение серии «Руководство для начинающих по GCP BigQuery»; во второй части мы рассмотрим преимущества и варианты использования ключевых функций BigQuery. Прочитав эту статью, вы получите четкое представление о секционированных таблицах, запланированных запросах, скалярных функциях и внешних таблицах, которые станут строительными блоками вашей карьеры, позволяя вам принимать правильные решения как специалисту по данным.

Секционированные таблицы

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

Ниже приведены основные характеристики секционированных таблиц:

  1. Разделение основано на столбце, который может быть целым числом или датой. В настоящее время Bigquery не поддерживает секционирование по нескольким столбцам.
  2. Данные могут быть разделены по дням, месяцам или годам, в зависимости от того, насколько детализированными должны быть ваши разделы. Столбец даты разделения должен быть скалярным столбцом DATE , TIMESTAMP или DATETIME .
  3. Секционированные таблицы могут повысить производительность запросов и снизить затраты, поскольку запросы должны сканировать только соответствующие разделы, а не всю таблицу.
  4. Вы можете создавать секционированные таблицы с нуля или путем секционирования существующей таблицы.
  5. Разделение может быть выполнено либо при создании таблицы, либо позже с использованием оператора «ALTER TABLE».

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

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

Чтобы создать секционированную таблицу в GCP BQ, вам необходимо сделать следующее:

  1. Определите схему секционирования. Вам необходимо определить схему секционирования, которая указывает, как секционировать таблицу. Вы можете создать новую таблицу со схемой секционирования или обновить существующую таблицу, чтобы добавить схему секционирования.
  2. Создание или обновление таблицы. Вы можете создать новую секционированную таблицу или обновить существующую таблицу, чтобы добавить секционирование с помощью веб-интерфейса BigQuery, инструмента командной строки bq или API BigQuery.

Как создать новую секционированную таблицу:

bq mk \
--time_partitioning_type=DAY \
--time_partitioning_field=date_column \
dataset_name.table_name \
schema_file.json

Как разделить существующую таблицу BigQuery:

CREATE OR REPLACE TABLE example_dataset.partitioned_table
PARTITION BY 
DATE_TRUNC(part_date,MONTH) 
OPTIONS(require_partition_filter=TRUE) 
AS 
(
 SELECT
 *
 FROM
 example_dataset.base_table
)

Во втором примере я включил вышеупомянутый параметр require_partition_filter, который не позволяет вам выполнять запрос без задания условия для столбца разделения. Есть и другие варианты, которые вы можете разместить, и, как обычно, я предлагаю вам обратиться к официальной документации, чтобы узнать больше.

Запланированные запросы

Запланированные запросы в GCP — это эффективный способ автоматизировать выполнение запроса через определенные промежутки времени. Вы можете запланировать выполнение запроса каждую минуту, час, день, неделю или месяц. Использование запланированных запросов — разумный выбор для регулярных отчетов, анализа данных и других задач обработки.

Основные характеристики запланированных запросов следующие:

  1. Настроить запланированный запрос легко и быстро — это можно сделать прямо из Google Cloud Console. Запланированные запросы можно настроить для записи результатов в таблицу или представление.
  2. Вы также можете настроить уведомления по электронной почте, чтобы отслеживать статус запланированных запросов.
  3. Ими могут управлять авторизованные пользователи GCP или сервисные аккаунты.
  4. Запланировать запрос можно несколькими способами, включая использование веб-интерфейса, API или инструмента командной строки bq.

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

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

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

Прежде чем создать запланированный запрос, убедитесь, что ваш SQL-код правильно введен в редакторе запросов.

Затем, используя опцию GUI, просто нажмите РАСПИСАНИЕ и Создать новый запланированный запрос.

Как только ваша панель конфигурации появится справа, дайте ей репрезентативное имя, затем установите частоту, время начала и окончания для завершения планирования.

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

Для запросов DDL и DML выберите место или регион обработки.

Процесс создания Scheduled Query демонстрирует, насколько интуитивно Google сделал пользовательский интерфейс, поскольку для этого почти не нужна надлежащая документация или обучение. Однако, чтобы разблокировать все возможности положений, я настоятельно рекомендую вам обратиться к официальной документации и держать эти маленькие хитрости в кармане до нужного времени.

Скалярные функции (пользовательские функции)

В GCP BigQuery скалярные функции — это функции, которые применяются к каждой строке таблицы для создания нового выходного значения. Пользовательская функция (UDF) позволяет пользователю создать функцию с помощью выражения SQL или кода JavaScript. Они принимают одно или несколько входных значений в качестве параметров и возвращают одно выходное значение.

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

Затем эти функции можно использовать в ваших SQL-запросах в BQ для упрощения и оптимизации анализа. Например, вы можете использовать скалярную функцию для расчета возраста ваших клиентов на основе столбца даты их рождения в вашем наборе данных.

Основные характеристики скалярных функций:

  1. Скалярные функции работают с одним входом.
  2. Они возвращают один выход.
  3. Скалярные функции детерминированы. Это означает, что для данного входа выход всегда одинаков.
  4. Их можно использовать в предложениях SELECT, WHERE и ORDER BY.
  5. Они не изменяют данные в таблице. Они только выполняют расчет или преобразование данных.

Если это все еще не кристально ясно, пожалуйста, посмотрите пример ниже, чтобы помочь вам понять. Обратите внимание, что скалярные функции можно создавать с помощью оператора CREATE TEMP FUNCTION. Я настоятельно рекомендую вам скопировать и вставить приведенный ниже фрагмент кода в редактор запросов в BigQuery.

CREATE TEMP FUNCTION SquareVal (x INT64)
RETURNS INT64
AS (
    x * x
);

SELECT
  val as orig_value, SquareVal(val) as squared_value
FROM
  UNNEST([2,3,5,8]) AS val;

Вывод:

Внешние таблицы

Внешние таблицы в BigQuery Google Cloud Platform — это таблицы, которые ссылаются на данные, хранящиеся извне в других местах, таких как Google Cloud Storage или Bigtable. Вместо того, чтобы хранить данные непосредственно в BigQuery, таблица просто указывает на место, где хранятся данные. Затем данные запрашиваются с использованием стандартного SQL, как если бы они уже были в BigQuery.

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

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

Вкратце, вот пять основных характеристик внешних таблиц в GCP BigQuery:

  1. Они указывают на данные, хранящиеся вне BigQuery в облачном хранилище (например, Google Cloud Storage).
  2. Вы можете создавать запросы и представления для внешних таблиц так же, как и для обычных таблиц.
  3. Внешние таблицы могут быть секционированы (горизонтально разделены на более мелкие, более управляемые части)
  4. Запросы к внешним таблицам могут быть медленнее, чем запросы к собственным таблицам BigQuery, но они часто являются более экономичным вариантом хранения.
  5. Изменения во внешней базовой таблице (основном хранилище) отражаются в запросах, выполняемых к внешней таблице, но вам потребуется обновить метаданные внешней таблицы (обычно с помощью сценария), чтобы сразу увидеть эти изменения.

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

  1. Архивирование.Если у вас есть данные, которые необходимо хранить в целях соблюдения нормативных требований, но к которым редко обращаются, их хранение во внешней таблице в облачном хранилище может быть экономичным решением. Сохраняя данные в облачном хранилище вместо нативных таблиц BigQuery, вы можете сократить расходы на хранение, сохраняя при этом возможность запрашивать данные.
  2. Миграция в облако. При перемещении данных из локального хранилища данных в облако вы можете использовать внешние таблицы для создания «озера данных» в облачном хранилище. Это позволяет использовать такие инструменты, как BigQuery, для запроса данных до их переноса в нативную таблицу BigQuery.
  3. Совместная работа. Если вы работаете с партнерами или поставщиками, которым нужен доступ к вашим данным, вы можете создавать внешние таблицы, указывающие на данные, хранящиеся в их учетных записях облачного хранилища. Это позволяет им запрашивать данные, не копируя их в собственные учетные записи хранения.

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

Заключение

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

А для начинающих и начинающих специалистов по данным, помните об этих идеях, чтобы укрепить свой инструментарий навыков, который окажется бесценным во время собеседований и на работе. Помните, что функции, описанные в частях 1 и 2 этой серии, не являются исчерпывающими. GCP BigQuery может предложить гораздо больше, повышая уровень анализа данных. В нашей следующей статье мы продолжим изучение расширенных функций и инновационных решений в области науки о данных. Оставайтесь с нами, пока мы погружаемся в следующий этап этого путешествия, где захватывающие технологии готовы изменить то, как мы используем потенциал данных.