5 полезных советов, как изменить работу с BigQuery

Советы, которые изменили правила игры. Я бы хотел, чтобы кто-нибудь рассказал мне их 5 лет назад.

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

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

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

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

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

То, чем я хотел бы поделиться сегодня, как раз в этом духе. За годы работы Data Scientist я наткнулся на несколько приемов, которые, безусловно, облегчили мне жизнь. Хотя некоторые из них могут быть вам знакомы, я считаю, что поделиться тем, что я знаю, может быть полезно для некоторых из вас и, в то же время, весело для меня.

Совет № 1. Оптимизируйте загрузку больших таблиц: сохраняйте в облачном хранилище Google перед загрузкой

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

Как показано на изображении выше, BigQuery установил ограничение размера в 10 МБ при экспорте результатов запроса в локальное хранилище, Google Таблицы и т. д., что делает этот вариант непрактичным для экспорта данных. Чтобы обойти это ограничение, обходной путь включает в себя сохранение результатов в виде таблицы BigQuery, экспорт данных в Google Cloud Storage (GCS) и последующую загрузку данных в формате csv в локальное место назначения без каких-либо ограничений по размеру.

1. Сохранение результатов запроса в виде таблицы BigQuery

2. Откройте таблицу, затем экспортируйте в GCS

3. Загрузить данные в формате csv из GCS

Совет № 2. Управляйте рисками: защитите существующие вставки таблиц с помощью транзакций

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

Ранее в моей карьере Data Scientist были времена, когда я совершал ошибку, не используя транзакцию для набора операций SQL. Вместо этого я выполнял операции по отдельности, не заключая их в транзакцию. Неудивительно, что во время одного из обновлений произошла непредвиденная ошибка, вызвавшая сбой последующих операций и оставившая базу данных в несогласованном состоянии. Затем мне пришлось вручную отслеживать и отменять внесенные изменения. Это был болезненный урок, который подчеркнул важность использования транзакций.

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

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

На высоком уровне запись транзакции обычно состоит из следующих шагов:

1. Начать транзакцию. Начните транзакцию с помощью инструкции BEGIN TRANSACTION. Это отмечает начало блока транзакции.
2. Выполнение операций. Выполнение различных операций с базой данных, таких как вставка, обновление или удаление данных в блоке транзакции. Эти операции будут сгруппированы вместе как часть транзакции.
3. Зафиксировать транзакцию. Если все операции в рамках транзакции выполнены успешно, вы можете зафиксировать транзакцию с помощью оператора COMMIT TRANSACTION. Это применяет изменения, сделанные в рамках транзакции, к базе данных, делая их постоянными.
4. Откат транзакции. Если во время транзакции возникает ошибка или если вы хотите отменить сделанные изменения, вы можете откатить транзакцию с помощью инструкции ROLLBACK. Это отменяет все операции, выполненные в рамках транзакции, и возвращает базу данных в ее состояние до начала транзакции.

И как FYI, вот конкретные характеристики транзакций, о которых вам нужно знать при их планировании и реализации:

• Транзакции поддерживаются только для операторов DML (язык манипулирования данными), таких как операции INSERT, UPDATE, DELETE и MERGE. Они не поддерживаются для операторов DDL (язык определения данных), которые изменяют структуру таблицы.
• Транзакции имеют ограничения по размеру и не могут превышать определенное количество строк или размер данных (10 ГБ в GCP BQ). Конкретные ограничения зависят от типа операции и базового формата хранения.
• Транзакции влекут за собой дополнительные расходы по сравнению с обычными запросами, поскольку требуют дополнительной обработки и ресурсов.

Наконец, для лучшего понимания я написал простой пример транзакции, включающей TRUNCATE, INSERT INTO, and UPDATE операций вместе с обработчиками ошибок.

BEGIN TRANSACTION;

-- Step 1: Truncate Table
TRUNCATE TABLE `project_id.dataset_id.table_id`;

-- Step 2: Insert Data
BEGIN TRY
  INSERT INTO `project_id.dataset_id.table_id` (column1, column2)
  VALUES ('val1', 'val2'),
         ('val3', 'val4');
EXCEPTION WHEN OTHERS THEN
  ROLLBACK;
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error occurred during insert.';
END TRY;

-- Step 3: Update Data
BEGIN TRY
  UPDATE `project_id.dataset_id.table_id`
  SET column1 = 'val2_new'
  WHERE column2 = 'val2';
EXCEPTION WHEN OTHERS THEN
  ROLLBACK;
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error occurred during update.';
END TRY;

COMMIT;

Совет № 3. Повысьте эффективность запросов: выбирайте CTE, а не подзапросы

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

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

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

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

Давайте продемонстрируем разницу между CTE и подзапросом на примере. Рассмотрим пример сценария, в котором мы хотим найти сотрудников с зарплатой выше средней зарплаты в соответствующих отделах.

При использовании подзапроса SQL-запрос будет выглядеть следующим образом:

SELECT employee_id
, full_name
, department
, salary
FROM tbl_employee 
WHERE salary > (
    SELECT AVG(salary)
    FROM tbl_employee a 
    WHERE a.department = tbl_employee.department
);

При использовании CTE запрос SQL будет выглядеть следующим образом:

WITH cte_department_avg AS (
    SELECT department
    , AVG(salary) AS avg_salary
    FROM tbl_employee
    GROUP BY department
)
SELECT full_name
, department
, salary
FROM tbl_employee a 
INNER JOIN cte_department_avg b 
ON a.department = b.department
WHERE a.salary > b.avg_salary;

В этом примере использование CTE дает несколько преимуществ по сравнению с подзапросом.

1. Читаемость. CTE обеспечивает более чистую и удобочитаемую структуру кода за счет разделения логики расчета средней цены категории.
2. Повторное использование: на CTE можно ссылаться несколько раз в запросе, что позволяет эффективно повторно использовать рассчитанную среднюю цену категории.
3. Производительность. CTE вычисляет среднюю цену категории один раз, а затем объединяет ее с таблицей "Товары". Это позволяет избежать избыточных вычислений для каждой строки, что повышает производительность запросов.

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

Совет № 4. Улучшите расчет дат: используйте CROSS JOIN для добавления дат

В жизни Data Scientist расчеты даты очень распространены, когда дело доходит до разработки функций. Например, используя приведенную ниже таблицу, чтобы рассчитать срок пребывания клиента в компании по состоянию на 31 декабря 2022 года с момента первоначальной регистрации, мы часто делаем следующее.

SELECT customer_id
, first_name
, last_name
, DATE_DIFF(DATE(2022, 12, 31), enrollment_date, DAY) as tenure_in_days
FROM tbl_customer 

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

SELECT customer_id
, first_name
, last_name
, DATE_DIFF(DATE(2022, 12, 31), enrollment_date, DAY) as tenure_in_days
, DATE_DIFF(DATE(2022, 12, 31), last_purchase_date, DAY) as days_since_last_shop
FROM tbl_customer

Теперь, если мы хотим вычислитьtenure_in_daysили days_since_last_shopна основе другой даты, например 31 марта 2023 года, нам потребуется изменить две отдельные даты по отдельности. Рассмотрим сценарий, в котором мы выполняем 20 различных вычислений даты. Нынешняя структура кода не только громоздка в обслуживании, но и подвержена ошибкам.

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

WITH cte_select_date AS (
  DATE(2022,12,31) as select_date
) 

SELECT customer_id
, first_name
, last_name
, DATE_DIFF(b.select_date, enrollment_date, DAY) as tenure_in_days
, DATE_DIFF(b.select_date, last_purchase_date, DAY) as days_since_last_shop
FROM tbl_customer a 
CROSS JOIN cte_select_date b 

При такой настройке SQL обновление значения даты select_date — это все, что требуется для вычисления tenure_in_days и days_since_last_shop на основе другой даты. Такой подход значительно упрощает обслуживание кода, сводя к минимуму риск ошибок и обеспечивая более надежное решение.

Совет № 5. Упростите поиск схемы таблицы с помощью SQL

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

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

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

SELECT *
FROM `project.dataset.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'table'

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

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

SELECT
 TO_JSON_STRING(
    ARRAY_AGG(STRUCT( 
      IF(is_nullable = 'YES', 'NULLABLE', 'REQUIRED') AS mode,
      column_name AS name,
      data_type AS type)
    ORDER BY ordinal_position), TRUE) AS schema
FROM
  dataset_id.INFORMATION_SCHEMA.COLUMNS
WHERE
  table_name = 'table_id'

Этот SQL-запрос упрощает получение схемы таблицы, экономя ваше драгоценное время и избавляя от необходимости вручную записывать схему в формате JSON в блокноте. Это особенно полезно при управлении активами данных с использованием инфраструктуры как кода (IaC) с такими инструментами, как Terraform или Pulumi, где в файлах конфигурации требуются явные определения схемы столбца за столбцом. С помощью этого простого SQL вы можете легко получить схему таблицы и включить ее в свои конфигурации IaC, повысив эффективность рабочего процесса.

Заключение

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