如何将BQ表以JSON格式导出至GCS且避免编码转义

问题分析 #

你遇到的情况是：BigQuery表中显示的Shirt & Trouser Presses导出到GCS后变成Shirt & Trouser Presses。这里需要明确：BigQuery控制台会对特殊字符做HTML转义显示（比如把&显示为&），但表中实际存储的可能就是&；如果表中确实存储的是&字符串，导出JSON时默认会保留原始内容，不会自动解码。

如果你的需求是让导出的JSON文件中保留&（即把&转义为&），可以通过导出前查询转换字段的方式实现。

解决方案 #

修改原有代码，不再直接导出表，而是先通过SQL查询将字符串字段中的&替换为&，再将查询结果导出到GCS。具体代码调整如下：

dataset_ref = bigquery.DatasetReference(BQ_PROJECT, dataset_id)
client = bigquery.Client(project=BQ_PROJECT)
tables = client.list_tables(dataset_id)

for table in tables:
    if table.table_type == "TABLE":
        table_id = table.table_id
        destination_blob = table_id
        table_ref = dataset_ref.table(table_id)
        destination_uri = f"gs://{BUCKET}/{destination_blob}"

        # 获取表结构，生成带替换逻辑的查询语句
        table = client.get_table(table_ref)
        schema = table.schema
        select_clauses = []
        for field in schema:
            if field.field_type == "STRING":
                # 对STRING类型字段替换&为&
                select_clauses.append(f"REPLACE(`{field.name}`, '&', '&') AS `{field.name}`")
            else:
                select_clauses.append(f"`{field.name}`")
        select_query = f"SELECT {', '.join(select_clauses)} FROM `{table_ref.project}.{table_ref.dataset_id}.{table_ref.table_id}`"

        # 配置查询导出任务
        job_config = bigquery.QueryJobConfig()
        job_config.destination = destination_uri
        job_config.destination_format = bigquery.DestinationFormat.NEWLINE_DELIMITED_JSON
        job_config.write_disposition = bigquery.WriteDisposition.WRITE_TRUNCATE  # 覆盖目标位置已有文件

        # 执行查询导出
        query_job = client.query(
            select_query,
            job_config=job_config,
            location="EU"
        )
        query_job.result()  # 等待任务完成

补充说明 #

1. 如果需要保留更多HTML实体（比如<转义为<、>转义为>等），可以扩展REPLACE函数，比如使用嵌套REPLACE：

   REPLACE(REPLACE(REPLACE(`column_name`, '&', '&amp;'), '<', '&lt;'), '>', '&gt;') AS `column_name`
   

2. 先确认表中实际存储的内容：可以通过执行SELECT column_name FROM your-project.your-dataset.your-table LIMIT 1查看原始字符串，避免混淆控制台的转义显示和实际存储值。

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