MySQL导出BigQuery数据重复，需批量自动创建去重视图

我来帮你搞定这个批量创建去重视图的问题！刚好之前处理过类似的场景，给你两个实用方案，不管是用Shell脚本还是纯BigQuery SQL都能解决～

核心思路 #

咱们的目标很明确：给每个目标表自动生成带去重逻辑的视图，用ROW_NUMBER()窗口函数按唯一键分组，保留每组里的有效记录（比如最新的那条）。关键是要自动抓取所有需要处理的表，不用手动挨个写SQL。

方案一：Shell脚本 + BigQuery命令行工具（bq） #

这个方案适合在本地或服务器端跑批量任务，步骤很清晰：

1. 先配置基础参数 #

先把项目、数据集这些固定信息定义成变量，方便后续修改：

# 替换成你的BigQuery项目ID
PROJECT_ID="your-project-id"
# 替换成你的数据集名称
DATASET_ID="your-dataset-id"
# 唯一键字段（比如主键，多个字段用逗号分隔，比如"user_id,order_time"）
UNIQUE_KEYS="id"
# 去重视图的后缀（比如原表名users变成users_dedup）
VIEW_SUFFIX="_dedup"

2. 获取需要处理的表列表 #

用bq ls命令拉取数据集里的所有表，自动排除已经存在的去重视图，避免重复创建：

# 提取数据集内所有非去重视图的表名
TABLES=$(bq ls --format=csv "$PROJECT_ID:$DATASET_ID" | tail -n +2 | awk -F',' '{print $1}' | grep -v "$VIEW_SUFFIX")

3. 批量生成并执行视图创建语句 #

循环遍历每个表，自动生成去重SQL，然后用bq query执行：

for TABLE in $TABLES; do
  # 拼接视图名称
  VIEW_NAME="${TABLE}${VIEW_SUFFIX}"
  # 构建去重逻辑：按唯一键分组，保留每组中最新的记录（可以根据需求改ORDER BY的字段）
  DEDUP_SQL="
  CREATE OR REPLACE VIEW \`${PROJECT_ID}.${DATASET_ID}.${VIEW_NAME}\` AS
  SELECT * EXCEPT(row_num)
  FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY ${UNIQUE_KEYS} ORDER BY updated_at DESC) AS row_num
    FROM \`${PROJECT_ID}.${DATASET_ID}.${TABLE}\`
  ) t
  WHERE row_num = 1
  "
  # 打印日志并执行SQL
  echo "正在创建去重视图：${TABLE} -> ${VIEW_NAME}"
  bq query --use_legacy_sql=false "$DEDUP_SQL"
done

4. 后续新增表的处理 #

把这个脚本做成Linux的cron定时任务，比如每天凌晨跑一次，就能自动给新增的表创建去重视图啦。

方案二：BigQuery存储过程（纯SQL方式） #

如果不想碰Shell脚本，完全可以用BigQuery自带的存储过程来实现，纯SQL就能搞定：

1. 创建存储过程 #

CREATE OR REPLACE PROCEDURE `your-project-id.your-dataset-id.create_dedup_views`(
  IN unique_keys STRING,
  IN view_suffix STRING DEFAULT '_dedup'
)
BEGIN
  DECLARE table_name STRING;
  DECLARE done BOOL DEFAULT FALSE;
  -- 声明游标，获取数据集内所有基础表（排除已有的去重视图）
  DECLARE table_cursor CURSOR FOR
    SELECT table_id
    FROM `your-project-id.your-dataset-id.INFORMATION_SCHEMA.TABLES`
    WHERE table_type = 'BASE TABLE'
      AND NOT table_id LIKE CONCAT('%', view_suffix);

  OPEN table_cursor;
  FETCH NEXT FROM table_cursor INTO table_name;

  WHILE NOT done DO
    -- 动态生成视图创建语句并执行
    EXECUTE IMMEDIATE FORMAT("""
      CREATE OR REPLACE VIEW `%s.%s.%s%s` AS
      SELECT * EXCEPT(row_num)
      FROM (
        SELECT *,
               ROW_NUMBER() OVER (PARTITION BY %s ORDER BY updated_at DESC) AS row_num
        FROM `%s.%s.%s`
      ) t
      WHERE row_num = 1
    """,
    'your-project-id', 'your-dataset-id', table_name, view_suffix,
    unique_keys,
    'your-project-id', 'your-dataset-id', table_name
    );
    
    FETCH NEXT FROM table_cursor INTO table_name;
    IF NOT FOUND THEN
      SET done = TRUE;
    END IF;
  END WHILE;

  CLOSE table_cursor;
END;

2. 执行存储过程 #

直接调用存储过程，传入唯一键即可：

-- 单个唯一键的情况
CALL `your-project-id.your-dataset-id.create_dedup_views`('id');
-- 多个唯一键的情况（比如用户ID+订单号）
CALL `your-project-id.your-dataset-id.create_dedup_views`('user_id,order_no');

3. 后续新增表的处理 #

在BigQuery的「计划查询」里设置定期执行这个存储过程，就能自动处理新增的表啦。

几个关键注意事项 #

• 唯一键要选对：一定要用能唯一标识记录的字段，别选错了导致误删有效数据。没有单一主键的话，就用多个字段组合。
• 排序逻辑按需调整：ORDER BY决定保留哪条重复记录——想留最新的就按时间字段降序，无所谓的话按唯一键排序就行。
• 权限要到位：执行脚本或存储过程的账号需要有BigQuery的Data Editor权限，能创建视图和读取原表。
• 视图命名统一：用统一的后缀/前缀（比如_dedup），方便后续识别和过滤。

内容的提问来源于stack exchange，提问作者Felipe