Как восстановить случайно удаленные данные в Google BigQuery

У вас бывало такое, что вы случайно удаляли в Google BigQuery важную таблицу, представление (view) или целый набор данных (dataset)? В результате останавливалась работа с таблицами и запросами, содержащими удаленную информацию.

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

бонус для читателей

Лучшие кейсы по маркетинг-аналитике от OWOX BI

Скачать материал

Содержание

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

Удаленную таблицу можно восстановить, если прошло не более 7 дней с момента ее удаления, и вы знаете ее название и название набора данных (dataset), откуда она была удалена.

Для восстановления таблицы откройте командную строку Cloud Shell в интерфейсе Google BigQuery:

И введите следующую команду:

    
bq cp mydataset.mytable@-3600000 mydataset.newtable
    

Пример этой же команды с названиями таблицы и набора данных:

    
bq cp OWOXBI_Reports.123_Transactions_withModels@-13600000 OWOXBI_Reports_Restore.123_Transactions_withModels
    

Где:

  • OWOXBI_Reports.123­_Transactions­_withModels — это набор данных и таблица, которая была удалена.
  • OWOXBI_Reports_Restore.123­_Transactions­_withModels — это набор данных и название таблицы, куда нужно восстановить данные.
  • @-13600000 — сколько миллисекунд назад искомая таблица еще не была удалена. Например, если таблица была удалена 30 минут назад, то достаточно установить интервал времени @-3600000. 3600000 — это 1 час (60 секунд * 60 минут * 1000).

После первого введения запроса потребуется авторизация — нажмите на кнопку «Authorize»:

После успешного введения команды произойдет восстановление таблицы:

И вы увидите похожий текст: 

    
Waiting on bqjob_r4ca30008c2e3147d_0000017af0d58e5e_1 ... (0s) Current status: DONE   
Table 'summer-drive-112011:OWOXBI_Reports_Restore.test_table@-600000' successfully copied to 'summer-drive-112011:OWOXBI_Reports_Restore.test_table'
    

Как восстановить удаленное представление (view)

Восстановить удаленное представление (view) способом, описанным выше, не получится — он подходит только для таблиц.

Для восстановления view поддержка Google рекомендует использовать Logs Explorer.

Чтобы найти запросы, которые обновляли удаленное представление, в логах Google Cloud Platform нужно выполнить следующий запрос:

    
resource.type="bigquery_resource"
protoPayload.methodName="tableservice.update"
protoPayload.serviceData.tableUpdateRequest.resource.tableName.tableId="custom_events_attribution_VIEW"
    

Где:

  • tableservice.update — это команда для отображения обновления view в логах.
  • custom_events_attribution_VIEW — название представления.

В настройках Select time range установите временной период, в течение которого в представление могли вноситься изменения (например 1 год):

После выполнения команды отобразятся все запросы, которые обновляли искомое view. Выберите последний запрос по timestamp:

Скопируйте запрос из лога и создайте представление заново.

Кроме того, вы можете найти запрос, с помощью которого было создано искомое представление (до этого мы искали запросы, которые обновляли view). Для этого используйте следующую команду:

    
resource.type="bigquery_resource"
protoPayload.methodName="tableservice.insert"
protoPayload.serviceData.tableInsertRequest.resource.tableName.tableId="query_name_VIEW"
    

Рекомендуем установить максимально возможный период для поиска в логах нужной записи.

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

Как восстановить удаленный набор данных (dataset)

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

В первые 24 часа после удаления набора данных создать dataset с прежним названием нельзя, но все еще можно найти названия таблиц и представлений, которые входили в удаленный набор данных. Хотя самих таблиц и views уже не будет видно.

Найти представления из удаленного dataset можно по названию через поиск BigQuery:

Список таблиц найти через поиск не получится — у них нет общей части в названии.

Чтобы найти название таблиц из удаленного набора данных, используйте SQL-запрос приведенный ниже. В запросе нужно указать временной период, в течение которого в таблицы могли вноситься изменения (creation_time BETWEEN). В результате запрос выдаст таблицы, в которые вносились изменения — это будут требуемые таблицы из удаленного dataset.

    
SELECT
  *
FROM (
  SELECT
    query,
    user_email,
    CONCAT(destination_table.project_id,".",destination_table.dataset_id,".",destination_table.table_id) AS destination_table,
    COUNT(job_id) AS job_ids,
    MAX(creation_time) AS lastUpdateDate
  FROM
    region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE
    creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 180 DAY)
    AND TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 0 DAY)
    AND state = 'DONE'
    AND CONCAT(destination_table.project_id,".",destination_table.dataset_id,".",destination_table.table_id) LIKE "%OWOXBI_Reports.%"
  GROUP BY
    1,
    2,
    3
  ORDER BY
    5 DESC)
    

В результате запроса вы получите следующую информацию:

  • query — текст запроса, который произвел изменение в destination_table.
  • user _email — пользователь, который запустил запрос на обновление destination_table.
  • destination_table — таблица, которая была обновлена.
  • job_ids — количество запусков запроса за указанный временной интервал (creation_time BETWEEN …)
  • lastUpdateDate — последнее время запуска запроса (MAX(creation_time) AS lastUpdateDate).

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

Что делать, чтобы предотвратить подобные проблемы в будущем

  1. Включать сбор логов в проекте в Google BigQuery на старте работы с проектом.
  2. Не называть table и view так же, как и dataset.
  3. Дважды проверять название таблицы, которую нужно удалить из GBQ.
  4. Удалять таблицы через запрос, а не через интерфейс GBQ.
  5. Добавлять в название датасета, таблицы, view тип сущности. Например:
    1. OWOXBI_report_dataset
    2. OWOXBI_report_table
    3. OWOXBI_report_view

Удалить таблицу можно используя запрос, например:

    
DROP TABLE `summer-drive-112011.OWOXBI_Reports_Restore.test_table`
    

Удалить VIEW можно используя запрос, например:

    
DROP VIEW `summer-drive-112011.OWOXBI_Reports_Restore.test_VIEW`
    

Что сделать, чтобы смягчить проблему, если она произойдет снова

  • Не менять запросы в Schedule и Google Apps Script, на следующий день восстановить все представления и таблицы, после чего Schedule и Google Apps Script будут отрабатывать корректно.
  • Если view или таблица участвовала в настройке модели атрибуции, после восстановления данных на прежнее место нужно будет один раз перезапустить расчет атрибуции.

Полезные ссылки по теме

Наши клиенты
растут на 22% быстрее

Растите быстрее, анализируя, что лучше сработает в вашем маркетинге

Измеряйте KPI, находите зоны роста и увеличивайте свой ROI

Записаться на демо