BigQuery Backup Restore

[zking2000]

为了优化成本，可以考虑分阶段进行备份，以减少不必要的存储消耗和管理成本。下面是一些优化策略：

1. 快照的生命周期管理：定期删除旧的快照，只保留最近的几次快照，以减少BigQuery存储成本。
2. GCS存储生命周期管理：将数据导出到GCS后，设置GCS的生命周期规则，以便在特定时间后自动将数据移到更便宜的存储类（例如Nearline或Coldline）。
3. 分开运行快照和GCS导出：分开执行快照和导出操作，按需运行，避免不必要的重复存储。

优化后的Shell脚本

这个脚本包含了表快照的删除和GCS存储生命周期管理的步骤：

#!/bin/bash

# 设置变量
PROJECT_ID="your-project-id"
DATASET_ID="your-dataset-id"
TABLE_ID="your-table-id"
GCS_BUCKET_NAME="your-gcs-bucket-name"
TIMESTAMP=$(date +%Y%m%d%H%M%S)
SNAPSHOT_TABLE_ID="${TABLE_ID}_snapshot_${TIMESTAMP}"
GCS_EXPORT_PATH="gs://${GCS_BUCKET_NAME}/${TABLE_ID}_${TIMESTAMP}.json"
MAX_SNAPSHOTS=7

# 创建BigQuery表快照
echo "Creating table snapshot..."
bq cp -f ${PROJECT_ID}:${DATASET_ID}.${TABLE_ID} ${PROJECT_ID}:${DATASET_ID}.${SNAPSHOT_TABLE_ID}
if [ $? -ne 0 ]; then
  echo "Error creating table snapshot"
  exit 1
fi
echo "Snapshot created: ${DATASET_ID}.${SNAPSHOT_TABLE_ID}"

# 删除旧的快照，保留最近的几个快照
echo "Deleting old snapshots..."
SNAPSHOTS=$(bq ls -n 100 ${DATASET_ID} | grep "${TABLE_ID}_snapshot_" | awk '{print $1}' | sort -r)
COUNT=0
for SNAPSHOT in ${SNAPSHOTS}; do
  COUNT=$((COUNT+1))
  if [ ${COUNT} -gt ${MAX_SNAPSHOTS} ]; then
    bq rm -f -t ${DATASET_ID}.${SNAPSHOT}
    echo "Deleted snapshot: ${DATASET_ID}.${SNAPSHOT}"
  fi
done

# 导出BigQuery表到Google Cloud Storage
echo "Exporting table to GCS..."
bq extract --destination_format=NEWLINE_DELIMITED_JSON ${PROJECT_ID}:${DATASET_ID}.${TABLE_ID} ${GCS_EXPORT_PATH}
if [ $? -ne 0 ]; then
  echo "Error exporting table to GCS"
  exit 1
fi
echo "Exported ${TABLE_ID} to ${GCS_EXPORT_PATH}"

# 设置GCS生命周期管理
echo "Setting GCS lifecycle management..."
gsutil lifecycle set lifecycle.json gs://${GCS_BUCKET_NAME}
if [ $? -ne 0 ]; then
  echo "Error setting GCS lifecycle management"
  exit 1
fi

echo "Backup and snapshot completed successfully."

GCS生命周期管理

创建一个名为 lifecycle.json 的文件来定义GCS生命周期规则：

{
  "rule": [
    {
      "action": { "type": "SetStorageClass", "storageClass": "NEARLINE" },
      "condition": { "age": 30 }
    },
    {
      "action": { "type": "SetStorageClass", "storageClass": "COLDLINE" },
      "condition": { "age": 365 }
    },
    {
      "action": { "type": "Delete" },
      "condition": { "age": 1825 }
    }
  ]
}

配置cron job

1. 保存脚本： 将上述脚本保存为 bigquery_backup.sh 并确保它是可执行的：

   chmod +x bigquery_backup.sh

2. 设置cron job： 打开crontab编辑器：

   crontab -e

   添加一条cron job以定期运行备份脚本（例如，每天凌晨2点）：

   0 2 * * * /path/to/bigquery_backup.sh >> /path/to/bigquery_backup.log 2>&1

解释

1. 快照创建和管理：脚本创建表快照并管理旧快照，只保留最近的几次快照（例如7次），以减少BigQuery存储成本。
2. 导出数据到GCS：脚本将数据导出到GCS，并设置GCS生命周期规则，自动将数据移动到成本较低的存储类，并在数据达到特定年龄后自动删除。
3. 自动化管理：通过cron job定期运行脚本，实现备份的自动化管理。

通过这种优化方案，可以有效降低备份成本，同时保证数据的可用性和长期存储需求。

[zking2000]

表快照的优缺点

优点：

1.  快速恢复：快照在BigQuery内部创建和管理，恢复速度非常快。
2.  简便性：创建和管理快照非常简单，可以快速回滚到某个时间点的数据状态。

缺点：

1.  高存储成本：快照存储在BigQuery内，占用的存储空间较大，成本较高。
2.  短期保留：适合短期数据保留，对于长期存储成本较高且不太适合。

导出数据到GCS的优缺点

优点：

1.  长期存储：GCS提供低成本的长期存储选项，如Nearline和Coldline存储，适合长期备份归档。
2.  可移植性：导出的数据可以在多个系统之间移动，便于跨平台使用和数据共享。
3.  成本控制：GCS存储成本较低，尤其是长时间不访问的数据。

缺点：

1.  恢复复杂度：从GCS恢复数据需要将数据重新导入到BigQuery，步骤较多，速度较慢。
2.  需要手动管理：需要手动或通过脚本管理数据导出和存储生命周期。

综合考虑

综合上述优缺点，同时使用表快照和导出数据到GCS可以弥补单一方法的不足：

1.  表快照：
•   适用场景：短期快速恢复，数据量不大的情况。
•   恢复速度快：对于需要快速恢复的情况非常适用。
2.  导出数据到GCS：
•   适用场景：长期存储和归档，数据共享和跨平台使用。
•   成本低：长期存储成本低，适合归档数据。

这种组合方案确保了数据的高可用性和成本效益：

•   短期数据保留和快速恢复：使用表快照可以快速恢复到最近的备份点，适合应对短期数据问题。
•   长期存储和归档：导出数据到GCS，适合长期存储和归档需求，控制存储成本。

[zking2000]

通过这种方式，可以确保在发生数据丢失或损坏时，可以迅速恢复到最近的快照，同时保证数据长期存储的成本效益。

[zking2000]

从快照恢复BigQuery

#!/bin/bash

# 设置变量
PROJECT_ID="your-project-id"
DATASET_ID="your-dataset-id"
TABLE_ID="your-table-id"
SNAPSHOT_TIMESTAMP="your-snapshot-timestamp" # 要恢复的快照的时间戳，例如20230701010101
SNAPSHOT_TABLE_ID="${TABLE_ID}_snapshot_${SNAPSHOT_TIMESTAMP}"
BACKUP_TABLE_ID="${TABLE_ID}_backup_${SNAPSHOT_TIMESTAMP}"

# 将现有表重命名为备份表
echo "Renaming existing table to backup table..."
bq cp -f ${PROJECT_ID}:${DATASET_ID}.${TABLE_ID} ${PROJECT_ID}:${DATASET_ID}.${BACKUP_TABLE_ID}
if [ $? -ne 0 ]; then
  echo "Error renaming existing table to backup"
  exit 1
fi
echo "Existing table renamed to: ${DATASET_ID}.${BACKUP_TABLE_ID}"

# 从快照恢复表
echo "Restoring table from snapshot..."
bq cp -f ${PROJECT_ID}:${DATASET_ID}.${SNAPSHOT_TABLE_ID} ${PROJECT_ID}:${DATASET_ID}.${TABLE_ID}
if [ $? -ne 0 ]; then
  echo "Error restoring table from snapshot"
  exit 1
fi
echo "Table restored from snapshot: ${DATASET_ID}.${SNAPSHOT_TABLE_ID}"

echo "Table ${TABLE_ID} restored successfully from snapshot ${SNAPSHOT_TABLE_ID}"

[zking2000]

从GCS恢复表

#!/bin/bash

# 设置变量
PROJECT_ID="your-project-id"
DATASET_ID="your-dataset-id"
TABLE_ID="your-table-id"
GCS_BUCKET_NAME="your-gcs-bucket-name"
TIMESTAMP_TO_RESTORE="your-timestamp" # 要恢复的特定快照的时间戳，例如20230701010101
GCS_IMPORT_PATH="gs://${GCS_BUCKET_NAME}/${TABLE_ID}_${TIMESTAMP_TO_RESTORE}.json"
TEMP_TABLE_ID="${TABLE_ID}_temp_restore_${TIMESTAMP_TO_RESTORE}"
BACKUP_TABLE_ID="${TABLE_ID}_backup_${TIMESTAMP_TO_RESTORE}"

# 从GCS导入到临时表
echo "Importing data from GCS to temporary table..."
bq load --source_format=NEWLINE_DELIMITED_JSON ${PROJECT_ID}:${DATASET_ID}.${TEMP_TABLE_ID} ${GCS_IMPORT_PATH}
if [ $? -ne 0 ]; then
  echo "Error importing data from GCS"
  exit 1
fi
echo "Data imported to temporary table: ${DATASET_ID}.${TEMP_TABLE_ID}"

# 将现有表重命名为备份表
echo "Renaming existing table to backup table..."
bq cp -f ${PROJECT_ID}:${DATASET_ID}.${TABLE_ID} ${PROJECT_ID}:${DATASET_ID}.${BACKUP_TABLE_ID}
if [ $? -ne 0 ]; then
  echo "Error renaming existing table to backup"
  exit 1
fi
echo "Existing table renamed to: ${DATASET_ID}.${BACKUP_TABLE_ID}"

# 将临时表重命名为目标表
echo "Renaming temporary table to target table..."
bq cp -f ${PROJECT_ID}:${DATASET_ID}.${TEMP_TABLE_ID} ${PROJECT_ID}:${DATASET_ID}.${TABLE_ID}
if [ $? -ne 0 ]; then
  echo "Error renaming temporary table to target table"
  exit 1
fi
echo "Temporary table renamed to target table: ${DATASET_ID}.${TABLE_ID}"

# 删除临时表
echo "Deleting temporary table..."
bq rm -f -t ${PROJECT_ID}:${DATASET_ID}.${TEMP_TABLE_ID}
if [ $? -ne 0 ]; then
  echo "Error deleting temporary table"
  exit 1
fi
echo "Temporary table deleted: ${DATASET_ID}.${TEMP_TABLE_ID}"

echo "Table restored successfully from ${GCS_IMPORT_PATH}"

[zking2000]

使用说明

1.  设置变量:
•   在两个脚本中，将PROJECT_ID、DATASET_ID、TABLE_ID、SNAPSHOT_TIMESTAMP（对于从快照恢复）和GCS_BUCKET_NAME、TIMESTAMP_TO_RESTORE（对于从GCS恢复）替换为实际的值。
2.  执行脚本:
•   保存脚本到文件，例如 restore_from_snapshot.sh 和 restore_from_gcs.sh。
•   使用命令 chmod +x restore_from_snapshot.sh 和 chmod +x restore_from_gcs.sh 赋予执行权限。
•   使用命令 ./restore_from_snapshot.sh 或 ./restore_from_gcs.sh 执行相应的脚本。

注意事项

•   在执行恢复操作前，确保对现有数据进行了备份。
•   确保您具有对Google Cloud项目和BigQuery数据集的适当权限。
•   根据数据大小和网络状况，恢复过程可能需要一定时间，请耐心等待。

通过这些脚本，您可以在恢复数据时将旧表重命名为备份表，以备日后查验，从而更好地保护和管理您的数据。