Как сделать бэкап таблиц Google BigQuery

192
4822
Материалы для скачивания
978.32 Kb

Зачем делать бэкап в BigQuery?

Итак, вы используете BigQuery для аналитической работы со своими данными. К сожалению, в BigQuery нет «родного» механизма резервного копирования таблиц. Google резервирует данные клиентов достаточно надежно, чтобы гарантировать их сохранность от своих сбоев и аварий.

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

Бэкап средствами Google

Предлагаемое решение внедрено в eLama и будет особенно интересно компаниям, использующим инструментарий Google Cloud Platform. А если вы его не используете, то это отличный повод начать.

Как это работает? Таблицы, требующие регулярного бэкапа, по расписанию копируются в облачное хранилище Google Cloud Storage в виде json-файлов по соответствующему пути.

При этом наши таблицы в BigQuery можно разделить на:

  • одиночные постоянные. Они создаются один раз и никогда не меняются;
  • датированные вида tableName_20190612. Создаются с некоторой периодичностью (например, данные о посещениях сайта за сутки);
  • одиночные обновляемые. В них данные регулярно дополняются новыми строками (например, список рекламных кампаний).

Постоянные таблицы копируются один раз вручную. При этом к имени json-файла добавляется постфикс, содержащий дату копирования.

Датированные также копируются разово, но автоматически и регулярно. Частота бэкапа равна периодичности возникновения этих таблиц. Когда таблица создается, она копируется в GCS. При этом бывает, что таблица еще пару дней дописывается. (Например, стриминг через OWOX посещений и событий из Google Analytics создает суточные таблицы каждый день, но заканчивает запись в них в течение двух-трех дней.) Для таких случаев механизм бэкапирования перезаписывает в GCS последние три-четыре таблицы: те, что были изменены, и одну-две не измененных — «на всякий случай».

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

TableName_20190415
TableName_20190416
TableName_20190417
...
TableName_20190614
TableName_20190615

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

Google Cloud Storage как хранилище резервных копий

Достоинство GCS в хорошей совместимости с другими продуктами Google, высокой надежности и низкой стоимости.

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

Также нет возможности задать срок жизни конкретного файла — только для всего бакета сразу.

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

В рассматриваемом решении резервные копии хранятся в GCS в виде: 

  • {bucket}/{project}/{dataset}/{tableName}_{copyDate}.json — для постоянных таблиц,
  • {bucket}/{project}/{dataset}/{tableName}/{tableName}.json — для датированных таблиц,
  • {bucket}/{project}/{dataset}/{tableName}/{tableName}_{copyDate}.json — для обновляемых таблиц,

где

  • {bucket} — имя бакета в GCS,
  • {project} — имя проекта в BigQuery,
  • {dataset} — имя датасета в BigQuery,
  • {tableName} — имя таблицы в BigQuery (для датированных таблиц в качестве постфикса включает дату создания таблицы),
  • {copyDate} — дата копирования таблицы вида ггггммдд.

Google AppScript как инструмент автоматического копирования

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

AppScript для копирования таблиц в BigQuery

Итак, наша задача — написать скрипты, которые:

1) экспортируют таблицы из BigQuery в GCS в виде json-файлов (BigQuery использует формат JSON Newline Delimited);

2) по истечении срока хранения бэкапов удаляют их.

Экспорт таблиц из BigQuery в GCS

Для экспорта таблиц в GCS с помощью GAS используем метод создания работы в BigQuery: BigQuery.Jobs.insert(). Код скрипта выглядит так:

    
function backup_ElamaTables_daily() {
   BigQuery_project = “YOUR_BIGQUERY_PROJECT_ID”;
   BigQuery_project = “YOUR_BIGQUERY_DATASET_ID”;
   BigQuery_project = “YOUR_BIGQUERY_TABLE_ID”;

   backup_date_table(BigQuery_project, BigQuery_dataset, BigQuery_table);
   backup_one_table(BigQuery_project, BigQuery_dataset, BigQuery_table);

   // Экспорт одиночных обновляемых таблиц
   function backup_one_table(projectId, datasetId, tableId)
   {
      var d = new Date();
      var day = d.getDate();
      var month = d.getMonth() + 1;
      var year = d.getFullYear();
      var copyDate = "_" + String(year) + String(month<10 ? "0"+month : month) + String(day<10 ? "0"+day : day);
      var job = {
         configuration:{
           extract: {
             "sourceTable": {
               "projectId": projectId,
               "datasetId": datasetId,
               "tableId": tableId
             },
             "destinationUri": "gs://elama/backup/" + projectId + "/" + datasetId + "/" + tableId + "/" + tableId + copyDate + ".json",
             "destinationFormat": "NEWLINE_DELIMITED_JSON"
           }
         }
      }; 
      job = BigQuery.Jobs.insert(job, projectId); 
   }
  
   // Экспорт последней датированной таблицы
   function backup_date_table(projectId, datasetId, tableId)
   {
      var d = new Date();
      year = d.getFullYear(); //текущий год
      month = d.getMonth()+1; //текущий месяц
      day = d.getDate();
      reviewPeriod=7; // Период ретроспективы в днях
      i=0;
      
      do {
        date= "_" + String(year) + String(month<10 ? "0"+month : month) + String(day<10 ? "0"+day : day);
        var job = {
          configuration:{
            extract: {
              "sourceTable": {
                "projectId": projectId,
                "datasetId": datasetId,
                "tableId": tableId + date
            },
            "destinationUri": "gs://elama/backup/" + projectId + "/" + datasetId + "/" + tableId + "/" + tableId + date + ".json",
            "destinationFormat": "NEWLINE_DELIMITED_JSON"
            }
          }
        };
        job=BigQuery.Jobs.insert(job, projectId);
        i++; 
        date=minusDate(year,month,day);
        day=date.day;
        month=date.month;
        year=date.year;
        
        var result = JSON.parse(job);
        if (typeof result.status.errorResult=='undefined') break;
        error=result.status.errorResult.reason;
      // Экспортируем только последнюю созданную в BigQuery таблицу, за последние reviewPeriod дней
      } while(error=='notFound' && i<reviewPeriod);
      // Если же хотим экспортировать все таблицы, созданные за ретроспективный период, следует убрать из цикла условие error=='notFound'
   }
   
   // Функция декремента даты
   function minusDate(y,m,d) 
   {
      d--;
      if(d==0)
      {
         m--;
         if (m==1 || m==3 || m==5 || m==7 || m==8 || m==10) d=31;
         if (m==4 || m==6 || m==9 || m==11) d=30;
         if (m==2) d = (y==2016 || y==2020 ? 29 : 28);
         if (m==0) {m=12; d=31; y--;}
      }
      return { "day": d, "month": m, "year":y}
   } 
}
    

Автоудаление устаревших бэкапов

Эта задача посложнее. Встроенной библиотеки для работы с GCS у AppScript нет, поэтому придется обращаться к хранилищу по API. Алгоритм следующий:

1. Включить API GCS в консоли Google Cloud.

1.1. Открываем Cloud Console и переходим в раздел APIs and Services.

Включить API GCS в консоли Google Cloud

1.2. Подключаем API GCS.

APIs and Services в GBQ

Вернувшись в раздел APIs and Services, убедимся, что нужные API подключены.

2. Настроить права.

Дадим пользователю, от имени которого будет запускаться скрипт, права на доступ к объектам в GCS. Открываем Cloud Console — IAM — находим пользователя и создаем для него роли:

  • BigQuery Data Viewer
  • BigQuery Job User
  • Storage Object Admin
  • Storage Transfer User
  • Viewer
Выдать права на доступ к объектам в GCS

3. Разрешить доменное делегирование.

Чтобы наш скрипт мог работать с файлами GCS без ручной авторизации, нужно настроить делегирование полномочий по домену (укажите API Scope, который дает полномочия управлять объектами в GCS: https://www.googleapis.com/auth/devstorage.read_write). Тогда пользователь, чей аккаунт находится в корпоративном домене (мы дали ему права в предыдущем пункте), сможет использовать API GCS. Авторизация происходит через учетную запись для сервисного аккаунта Google в службах OAuth2.

4. Подключить библиотеку OAuth2 авторизации.

Для правильной работы скрипта подключим библиотеку OAuth2 for Apps Script.
В интерфейсе GAS открываем меню «Ресурсы» — «Библиотеки...». Вводим ключ библиотеки 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF, жмем «Добавить».

Подключить библиотеку OAuth2 for Apps Script

5. В скрипте обращаться к API.

Скрипт удаления устаревших бэкапов выглядит так:

    
function backup_DeleteExpired_daily() 
{
   // Private key and client email of the service account.
   var PRIVATE_KEY =
       '-----BEGIN PRIVATE <your private key> -----END PRIVATE KEY-----\n'
   var CLIENT_EMAIL = 'backup-storage@BQproject.iam.gserviceaccount.com'
   // Email address of the user to impersonate.
   var USER_EMAIL = 'user@yourdomain.ru'
   
   backup_lifetime=2; // сколько месяцев будет жить файл
   BigQuery_project = “YOUR_BIGQUERY_PROJECT_ID”;
   BigQuery_project = “YOUR_BIGQUERY_DATASET_ID”;
   BigQuery_project = “YOUR_BIGQUERY_TABLE_ID”;
bucket = “YOUR_BUCKET_IN_GCS”;

   reset();
   run(BigQuery_project, BigQuery_dataset, BigQuery_table);

   // Функция удаления устаревшего файла
   function run(projectId, datasetId, tableId) 
   {
      // формируем URL
      prefix='backup%2f'+projectId+'%2f'+datasetId+'%2f'+tableId+'%2f'
      prefixSlash=prefix.replace('%2f','/', 'g');
      var url= 'https://www.googleapis.com/storage/v1/b/'+bucket+'/o?prefix='+prefix+'&fields=items(name)' 
      
      // Дата истечения срока жизни файла
      var date = new Date();
      date.setMonth( date.getMonth() - backup_lifetime ); 
      year = date.getFullYear();
      month = date.getMonth()+1;
      day = date.getDate();
      var expiredDate = String(year) + String(month<10 ? "0"+month : month) + String(day<10 ? "0"+day : day);
      
      // Берем список файлов в GCS
      response=APIquery('get', url);
      response= JSON.parse(response);
      
      response.items.forEach(function(element) 
      {
         fileDateExt=element["name"].match('('+prefixSlash+tableId+'_)(.*)')[2]; // дата создания файла вида 20190530.json
         fileDate=fileDateExt.substr(0,8); // отсекаем расширение .json
         if (fileDate<=expiredDate)
         {
            url='https://storage.googleapis.com/'+bucket+'/'+prefixSlash+tableId+'_'+fileDateExt;
            APIquery('delete', url); // Отправляем запрос на удаление в API GCS
         }
      });

   }
   
   // Функция обращения к API GCS
   function APIquery(method, url)
   {
      var service = getService();
      if (service.hasAccess()) {
         var options= {
            method: method,
            headers: {
               Authorization: 'Bearer ' + service.getAccessToken()
            }
         }
         var response = UrlFetchApp.fetch(url, options); //*/         
      } else {
         Logger.log('Access denied.');
         response=false;
      }      
      return response;
   }

   function reset() {
     getService().reset();
   }

   // Функция OAuth2 авторизации
   function getService() {
     return OAuth2.createService('Storage:' + USER_EMAIL)
         .setTokenUrl('https://oauth2.googleapis.com/token')
         .setPrivateKey(PRIVATE_KEY)
         .setIssuer(CLIENT_EMAIL)
         // Set the name of the user to impersonate. This will only work for
         // Google Apps for Work/EDU accounts whose admin has setup domain-wide
         // delegation:
         // https://developers.google.com/identity/protocols/OAuth2ServiceAccount#delegatingauthority
         .setSubject(USER_EMAIL)
         .setPropertyStore(PropertiesService.getScriptProperties())
         // Set the scope. This must match one of the scopes configured during the
         // setup of domain-wide delegation.
         .setScope("https://www.googleapis.com/auth/devstorage.read_write");
   }  
}
    

Настройка расписания скриптов

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

1. В проекте GAS нажимаем иконку и переходим к списку триггеров.

2. Создаем новый триггер, указываем главную функцию скрипта, расписание запуска и частоту уведомлений об ошибках.

3. Жмем «Сохранить». Готово.

Настройка расписания скриптов

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

Восстановление из резервных копий

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

Вручную всё просто: открываем BigQuery, жмем Create Table, в качестве источника выбираем Google Cloud Storage и указываем путь к файлу в хранилище, устанавливаем формат JSON (Newline Delimited). Рекомендуем не полагаться на автоматическое определение схемы, а указать ее явно. Задав все настройки, жмем кнопку Create Table.

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

    
function restore_from_backup() {
  projectId="YOUR_BIGQUERY_PROJECT_ID";
  datasetId="YOUR_BIGQUERY_DATASET_ID";
  tableId="YOUR_BIGQUERY_TABLE_ID";
  source_GCS_uri="gs://BUCKET/PATH_TO_FILE.json";
  
  var job = {
    configuration:{
      load: {
        "sourceUris": [
          source_GCS_uri
        ],
        "destinationTable": {
          "projectId": projectId,
          "datasetId": datasetId,
          "tableId": tableId
        },
        "sourceFormat": "NEWLINE_DELIMITED_JSON",
        "autodetect": true,
      }
    }
  };   
  job = BigQuery.Jobs.insert(job, projectId); 
}
    

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

Вас также могут заинтересовать