Изменение срока действия таблиц в Google BigQuery
В этой статье рассматривается способ изменения срока действия таблиц по умолчанию с данными Google Analytics 4 в Google BigQuery.
Решение предложил Симо Ахава (Simo Ahava), автор блога simoahava.com и соучредитель платформы обучения Team Simmer. Именно на teamsimmer.com он опубликовал способ изменения срока таблиц, которым я поделюсь с вами. Очень интересный материал, который стоит перевода и отдельной публикации в блоге с моими комментариями и дополнениями.
Несколько материалов, которые вы можете прочитать для лучшего понимания этой темы:
- Публичный набор данных BigQuery для Google Analytics 4
- Схема данных Google Analytics 4 в Google BigQuery
- Выполнение запросов нескольких таблиц GA4 events_ с _TABLE_SUFFIX
Итак, если при настройке связи между Google Analytics 4 и BigQuery вы используете тестовую среду BigQuery (sandbox, песочница), передача данных о событиях в течение дня не производится (недоступна потоковая передача данных), а также на ваш проект BigQuery накладываются дополнительные ограничения. Наиболее чувствительное для нас - это срок хранения данных в таблицах, который по умолчанию составляет 60 дней.
Это означает, что как только данные будут переданы за определенный день из Google Analytics 4 в Google BigQuery, у таблицы появится дата создания. И с этой даты пойдет отсчет 60 дней. По истечении этого времени таблица будет удалена из набора данных. Если вы перейдете на вкладку Details, то увидите две даты для каждой таблицы:
- Created - дата создания таблицы;
- Table expiration - дата истечения срока таблицы.
Срок жизни таблицы в песочнице - 60 дней (BigQuery sandbox)
На уровне набора данных (датасета, dataset) срок хранения таблиц также будет выставлен по умолчанию 60 дней (Default table expiration):
Срок хранения таблиц в песочнице (по умолчанию) - 60 дней
Примечание: в связи с текущими событиями в мире Google приостановил работу Google Cloud Plaftorm для пользователей из России. На момент публикации этой статьи вы не сможете привязать свою банковскую карту, выпущенную на территории РФ. Наиболее простое и эффективное решение - выпустить карту другой страны (Казахстан, Киргизия, Армения и т.д.), чтобы иметь возможность полноценно пользоваться Google BigQuery без каких-либо ограничений по сроку жизни таблиц и оплачивать счета.
Чтобы данные не удалялись через 60 дней, и вы имели возможность настроить потоковую передачу данных в течение дня, вам необходимо привязать к своей учетной записи Google Cloud Platform платежный аккаунт (банковскую карту) и изменить срок хранения данных в таблице.
Таким образом, нам необходимо решить две проблемы:
- обновить срок действия таблиц по умолчанию, чтобы он был больше, чем 60 дней (например, чтобы данные не удалялись из BigQuery никогда);
- обновить уже созданные таблицы, чтобы срок их действия не истек через 60 дней.
После привязки платежного аккаунта к проекту в Google Cloud обновите свой набор данных, чтобы все новые таблицы, которые будут создаваться в этом датасете, не имели срока действия. Это можно сделать с помощью настройки Edit Details, выбрав сам набор данных:
Dataset info - Edit Details
В открывшемся окне уберите галочку рядом с Enable table expiration:
Enable table expiration - Never
Сохраните изменения. С этого момента срок хранения таблиц на уровне вашего набора данных изменится с 60 дней на Never (никогда не удаляться):
Срок действия таблиц в наборе данных - Never (без ограничений по времени)
Однако для всех таблиц, которые уже были созданы в песочнице, по-прежнему будет установлен срок действия 60 дней по умолчанию. В этом можно легко убедиться, спустившись внутрь датасета и открыв любую таблицу, созданную в BigQuery до текущих изменений. Вы увидите все тот же срок хранения данных - 60 дней:
Срок действия существующих (старых) таблиц все те же 60 дней
Поэтому нам нужен способ обновления существующих таблиц, чтобы они не исчезли через 60 дней с момента их создания. Самый простой вариант - это открыть каждую таблицу через Edit Details:
Table info - Edit Details
В открывшемся окне переведите переключатель с Specify date на None:
Expiration Time - None
Сохраните изменения. Вернувшись назад на вкладку Details, вы увидите, что значение Table expiration изменилось на NEVER. Теперь эта таблица не будет удалена из вашего набора данных.
Table expiration - NEVER
Такой способ обновления таблиц подойдет в том случае, если у вас их не очень много. Вручную прокликать несколько раз не так сложно. Но что, если вам нужно обновить десятки таблиц?
Вы можете использовать оператор ALTER TABLE. С помощью такого запроса берется конкретная таблица в BigQuery и обновляются ее параметры, в том числе и срок действия (Table expiration). Для этого воспользуйтесь оператором ALTER TABLE SET OPTIONS и установите для таблицы значение expiration_timestamp = null. Это тоже самое, что и NEVER:
|
1 2 3 4 |
ALTER TABLE `yadashboard-394112.analytics_344295990.events_20230729` SET OPTIONS(expiration_timestamp=null) |
, где вместо yadashboard-394112.analytics_344295990.events_20230729 вы должны указать идентификатор своей таблицы. Найти его можно на вкладке Details, открыв нужную таблицу в проекте:
Идентификатор конкретной таблицы (Table ID)
В поле Table ID вы увидите полное названия вашей таблицы. Скопируйте это значение. Откройте редактор запросов через меню Query:
Редактор запросов в BigQuery
И выполните вышеприведенный запрос с помощью команды Run:
Выполнение запроса для конкретной таблицы (изменение срока действия)
После этого обновите страницу и вернитесь на вкладку Details, чтобы убедиться, что у данной таблицы срок действия изменился на Never:
Изменение срока действия таблицы с помощью SQL-запроса
SQL-запрос - это хорошо, но он предназначен для конкретной таблицы и его выполнение гораздо сложнее, чем просто открыть таблицу и вручную изменить срок ее действия. Вместо этого мы можем использовать операторы языка описания данных (Data Definition Language, сокр. DDL), чтобы запустить определенную программную логику для команды изменения таблиц.
С помощью нижеприведенного запроса, который опубликовал Симо Ахава в блоге Team Simmer, мы хотим добиться, чтобы в нем перебирались по очереди все таблицы нашего набора данных и для каждой таблицы, встречающейся в цикле, запускался оператор ALTER TABLE.
Вот как выглядит реализация DDL:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DECLARE list_of_tables ARRAY; DECLARE counter INT64 DEFAULT 0; EXECUTE IMMEDIATE 'SELECT ARRAY(SELECT distinct table_name FROM `project.dataset.INFORMATION_SCHEMA.COLUMNS`)' INTO list_of_tables; LOOP IF counter = ARRAY_LENGTH(list_of_tables) THEN LEAVE; END IF; EXECUTE IMMEDIATE CONCAT('ALTER TABLE `project.dataset.',list_of_tables[offset(counter)],'` SET OPTIONS(expiration_timestamp=null)'); SET counter = counter + 1; END LOOP; |
, где вместо project.dataset в строчках 5 и 14 вам необходимо указать свои собственные идентификаторы проекта и набора данных. Project ID и Dataset ID можно скопировать с вкладки Details, выбрав собственный датасет:
Project ID и dataset ID
Принцип работы данного SQL-запроса следующий: в строках 4–6 код извлекает все имена таблиц в наборе данных в массив с именем list_of_tables. Также инициализируется счетчик — мы будем использовать его при циклическом просмотре массива имен таблиц. В строках 8–17 мы запускаем цикл, который проходит по всем именам таблиц в массиве и запускает команду ALTER TABLE для каждой из них, объединяя имя таблицы с сигнатурой набора данных. Наконец, значение counter увеличивается на 1 с каждой итерацией, и цикл прерывается, как только счетчик достигает конца массива.
Откройте редактор запросов и выполните вышеприведенный код, изменив идентификаторы проекта и набора данных BigQuery на свои значения. Запустите выполнение запроса с помощью команды Run. Вполне вероятно, что вы увидите ошибку Not found: Dataset project.dataset. was not found in location US at [1:1]
Ошибка выполнения запроса
Данная ошибка свидетельствует о том, что ваш запрос обрабатывается не в том же регионе, в котором находится сам набор данных. То есть когда вы запрашиваете данные с помощью федеративного запроса BigQuery, он должен находиться в том же месте, что и ваш набор данных BigQuery.
И действительно, если вы откроете свой датасет и перейдете на вкладку Details, то увидите, что в поле Data location у вас может быть указан регион, отличный от региона выполнения запроса. Для моего проекта данные находятся в EU (Европейский союз), а запрос по умолчанию выполнялся в US (США).
Местоположение (хранение) ваших данных (у меня - EU, Европейский союз)
Местоположение (хранение) ваших данных в BigQuery вы задавали во время настройки связи GA4 с BigQuery в интерфейсе вашего ресурса:
Местоположение данных (интерфейс GA4)
Чтобы изменить регион самого запроса, в редакторе запросов BigQuery откройте раздел MORE - Query settings:
MORE - Query settings
В настройках запроса дойдите до самого конца и откройте расширенные настройки:
Advanced options
В опции Location Type выберите Multi-region и в открывшемся списке назначьте запросу тот же регион, что указан у вас в наборе данных в поле Data location. Поскольку в моем примере - это EU, то и в настройках запроса мне необходимо задать EU:
Регион выполнения запроса
В завершение не забудьте сохранить изменения с помощью кнопки SAVE. После этого перезапустите запрос. В самом редакторе внизу окна должен отобразиться текущий регион запроса:
Регион запроса будет указан внизу окна (Processing location)
Когда вы запустите этот запрос, вы увидите этапы его обработки, так как каждая итерация цикла запускает свой собственный процесс BigQuery. Это может занять некоторое время, если вам нужно обновить много таблиц. Для моего набора данных требовались всего три изменения сроков действия таблиц:
Статусы выполнения запроса и обновлений сроков действия таблиц
Как только запрос завершит свое выполнение, откройте любую из обновленных таблиц. Вы должны увидеть, как срок ее действия (Table expiration) был автоматически изменен на NEVER:
Автоматическое изменение срока действия таблицы на NEVER
Таким образом, все эти таблицы никогда не будут удалены из вашего набора данных BigQuery. Главное, что нужно сделать после подключения платежного аккаунта к вашему проекту Google Cloud - это обновить срок действия таблиц в наборе данных на Never (без ограничений по времени), чтобы любые таблицы, созданные в будущем после этого действия, больше не требовали никаких обновлений.
Понравилось изучать BigQuery и SQL-запросы? Приглашаю вас на онлайн-обучение GA4&BigQuery!
