Firestore BigQuery导出扩展查询性能不佳，求优化方案

云上老周

2026-8-7

问题描述

我用Firestore BigQuery导出扩展把数据流式传输到Google BigQuery，数据以JSON格式存储，所以用官方的Schema视图生成工具创建了结构化视图。但用BI工具基于这些视图做聚合筛选时，BigQuery查询性能极差。

我考虑过用物化视图，但生成的Schema视图是嵌套结构，物化视图不支持。我的集合只有十几万条记录，显然当前配置有问题。

补充说明

原始数据表

直接来自Firestore扩展的transaction_raw_changelog表：
transaction_raw_changelog表结构

生成的两个视图

transaction_raw_latest

-- Retrieves the latest document change events for all live documents.
--   timestamp: The Firestore timestamp at which the event took place.
--   operation: One of INSERT, UPDATE, DELETE, IMPORT.
--   event_id: The id of the event that triggered the cloud function mirrored the event.
--   data: A raw JSON payload of the current state of the document.
--   document_id: The document id as defined in the Firestore database
SELECT
  document_name,
  document_id,
  timestamp,
  event_id,
  operation,
  data
FROM
  (
    SELECT
      document_name,
      document_id,
      FIRST_VALUE(timestamp) OVER(
        PARTITION BY document_name
        ORDER BY
          timestamp DESC
      ) AS timestamp,
      FIRST_VALUE(event_id) OVER(
        PARTITION BY document_name
        ORDER BY
          timestamp DESC
      ) AS event_id,
      FIRST_VALUE(operation) OVER(
        PARTITION BY document_name
        ORDER BY
          timestamp DESC
      ) AS operation,
      FIRST_VALUE(data) OVER(
        PARTITION BY document_name
        ORDER BY
          timestamp DESC
      ) AS data,
      FIRST_VALUE(operation) OVER(
        PARTITION BY document_name
        ORDER BY
          timestamp DESC
      ) = "DELETE" AS is_deleted
    FROM
      `swipedrinks-app.transaction.transaction_raw_changelog`
    ORDER BY
      document_name,
      timestamp DESC
  )
WHERE
  NOT is_deleted
GROUP BY
  document_name,
  document_id,
  timestamp,
  event_id,
  operation,
  data

transaction_schema_transaction_schema_latest

-- Given a user-defined schema over a raw JSON changelog, returns the
-- schema elements of the latest set of live documents in the collection.
--   timestamp: The Firestore timestamp at which the event took place.
--   operation: One of INSERT, UPDATE, DELETE, IMPORT.
--   event_id: The event that wrote this row.
--   <schema-fields>: This can be one, many, or no typed-columns
--                    corresponding to fields defined in the schema.
SELECT
  *
EXCEPT
  (orderitem)
FROM
  (
    SELECT
      document_name,
      document_id,
      timestamp,
      operation,
      amount,
      bartenderId,
      eventStandId,
      event_id,
      paymentMethod,
      type,
      orderitem,
      toUserId
    FROM
      (
        SELECT
          document_name,
          document_id,
          FIRST_VALUE(timestamp) OVER(
            PARTITION BY document_name
            ORDER BY
              timestamp DESC
          ) AS timestamp,
          FIRST_VALUE(operation) OVER(
            PARTITION BY document_name
            ORDER BY
              timestamp DESC
          ) AS operation,
          FIRST_VALUE(operation) OVER(
            PARTITION BY document_name
            ORDER BY
              timestamp DESC
          ) = "DELETE" AS is_deleted,
          `swipedrinks-app.transaction.firestoreNumber`(
            FIRST_VALUE(JSON_EXTRACT_SCALAR(data, '$.amount')) OVER(
              PARTITION BY document_name
              ORDER BY
                timestamp DESC
            )
          ) AS amount,
          FIRST_VALUE(JSON_EXTRACT_SCALAR(data, '$.bartenderId')) OVER(
            PARTITION BY document_name
            ORDER BY
              timestamp DESC
          ) AS bartenderId,
          FIRST_VALUE(JSON_EXTRACT_SCALAR(data, '$.eventStandId')) OVER(
            PARTITION BY document_name
            ORDER BY
              timestamp DESC
          ) AS eventStandId,
          FIRST_VALUE(JSON_EXTRACT_SCALAR(data, '$.event_id')) OVER(
            PARTITION BY document_name
            ORDER BY
              timestamp DESC
          ) AS event_id,
          `swipedrinks-app.transaction.firestoreNumber`(
            FIRST_VALUE(JSON_EXTRACT_SCALAR(data, '$.paymentMethod')) OVER(
              PARTITION BY document_name
              ORDER BY
                timestamp DESC
            )
          ) AS paymentMethod,
          FIRST_VALUE(JSON_EXTRACT_SCALAR(data, '$.type')) OVER(
            PARTITION BY document_name
            ORDER BY
              timestamp DESC
          ) AS type,
          `swipedrinks-app.transaction.firestoreArray`(
            FIRST_VALUE(JSON_EXTRACT(data, '$.order')) OVER(
              PARTITION BY document_name
              ORDER BY
                timestamp DESC
            )
          ) AS orderitem,
          FIRST_VALUE(JSON_EXTRACT_SCALAR(data, '$.toUserId')) OVER(
            PARTITION BY document_name
            ORDER BY
              timestamp DESC
          ) AS toUserId
        FROM
          `swipedrinks-app.transaction.transaction_raw_latest`
      )
    WHERE
      NOT is_deleted
  ) transaction_raw_latest
  LEFT JOIN UNNEST(transaction_raw_latest.orderitem) AS orderitem_member WITH OFFSET orderitem_index
GROUP BY
  document_name,
  document_id,
  timestamp,
  operation,
  amount,
  bartenderId,
  eventStandId,
  event_id,
  paymentMethod,
  type,
  toUserId,
  orderitem_index,
  orderitem_member

慢查询示例

我需要查询交易金额总和：

SELECT sum(amount) FROM `swipedrinks-app.transaction.transaction_schema_transaction_schema_latest`

这个查询耗时约12秒，但表中只有196697行数据：
查询耗时截图

优化方案

1. 替换视图为实体表（定时刷新）

数据量不大，完全可以放弃嵌套视图，用实体表存储最新结构化数据：

创建目标表transaction_latest_structured，结构与transaction_schema_transaction_schema_latest一致
写定时触发的BigQuery存储过程，定期从原始changelog表计算最新数据并更新目标表
简化逻辑用ROW_NUMBER()筛选最新版本：

CREATE OR REPLACE PROCEDURE `swipedrinks-app.transaction.refresh_transaction_latest`()
BEGIN
  TRUNCATE TABLE `swipedrinks-app.transaction.transaction_latest_structured`;
  
  INSERT INTO `swipedrinks-app.transaction.transaction_latest_structured`
  WITH latest_changelog AS (
    SELECT 
      *,
      ROW_NUMBER() OVER(PARTITION BY document_name ORDER BY timestamp DESC) AS rn
    FROM `swipedrinks-app.transaction.transaction_raw_changelog`
    WHERE operation != 'DELETE'
  ),
  parsed_data AS (
    SELECT
      document_name,
      document_id,
      timestamp,
      event_id,
      operation,
      `swipedrinks-app.transaction.firestoreNumber`(JSON_EXTRACT_SCALAR(data, '$.amount')) AS amount,
      JSON_EXTRACT_SCALAR(data, '$.bartenderId') AS bartenderId,
      JSON_EXTRACT_SCALAR(data, '$.eventStandId') AS eventStandId,
      JSON_EXTRACT_SCALAR(data, '$.event_id') AS event_id,
      `swipedrinks-app.transaction.firestoreNumber`(JSON_EXTRACT_SCALAR(data, '$.paymentMethod')) AS paymentMethod,
      JSON_EXTRACT_SCALAR(data, '$.type') AS type,
      `swipedrinks-app.transaction.firestoreArray`(JSON_EXTRACT(data, '$.order')) AS orderitem,
      JSON_EXTRACT_SCALAR(data, '$.toUserId') AS toUserId
    FROM latest_changelog
    WHERE rn = 1
  )
  SELECT
    * EXCEPT(orderitem),
    orderitem_member AS orderitem,
    orderitem_index
  FROM parsed_data
  LEFT JOIN UNNEST(orderitem) AS orderitem_member WITH OFFSET orderitem_index;
END;

定时执行该存储过程（比如每5分钟一次），BI工具直接查询实体表，性能会大幅提升。

2. 优化原视图查询逻辑

不想改实体表的话，简化现有视图逻辑：

用ROW_NUMBER()替代重复的FIRST_VALUE()调用
去掉子查询中不必要的ORDER BY
优化后的transaction_raw_latest视图：

CREATE OR REPLACE VIEW `swipedrinks-app.transaction.transaction_raw_latest` AS
SELECT
  document_name,
  document_id,
  timestamp,
  event_id,
  operation,
  data
FROM (
  SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY document_name ORDER BY timestamp DESC) AS rn,
    operation = 'DELETE' AS is_deleted
  FROM `swipedrinks-app.transaction.transaction_raw_changelog`
)
WHERE rn = 1 AND NOT is_deleted;

逻辑更简洁，BigQuery执行计划会更高效。

3. 给原始表加分区和聚类

对transaction_raw_changelog表按timestamp分区、document_name聚类，加速窗口函数计算：

ALTER TABLE `swipedrinks-app.transaction.transaction_raw_changelog`
PARTITION BY DATE(timestamp)
CLUSTER BY document_name;

分区和聚类能让BigQuery只扫描必要数据块，减少IO开销。

4. 合并嵌套视图逻辑

把两层嵌套视图的逻辑合并成一个视图，减少多层解析开销：

CREATE OR REPLACE VIEW `swipedrinks-app.transaction.transaction_schema_latest_optimized` AS
WITH latest_changelog AS (
  SELECT 
    *,
    ROW_NUMBER() OVER(PARTITION BY document_name ORDER BY timestamp DESC) AS rn
  FROM `swipedrinks-app.transaction.transaction_raw_changelog`
  WHERE operation != 'DELETE'
),
parsed_data AS (
  SELECT
    document_name,
    document_id,
    timestamp,
    event_id,
    operation,
    `swipedrinks-app.transaction.firestoreNumber`(JSON_EXTRACT_SCALAR(data, '$.amount')) AS amount,
    JSON_EXTRACT_SCALAR(data, '$.bartenderId') AS bartenderId,
    JSON_EXTRACT_SCALAR(data, '$.eventStandId') AS eventStandId,
    JSON_EXTRACT_SCALAR(data, '$.event_id') AS event_id,
    `swipedrinks-app.transaction.firestoreNumber`(JSON_EXTRACT_SCALAR(data, '$.paymentMethod')) AS paymentMethod,
    JSON_EXTRACT_SCALAR(data, '$.type') AS type,
    `swipedrinks-app.transaction.firestoreArray`(JSON_EXTRACT(data, '$.order')) AS orderitem,
    JSON_EXTRACT_SCALAR(data, '$.toUserId') AS toUserId
  FROM latest_changelog
  WHERE rn = 1
)
SELECT
  * EXCEPT(orderitem),
  orderitem_member AS orderitem,
  orderitem_index
FROM parsed_data
LEFT JOIN UNNEST(orderitem) AS orderitem_member WITH OFFSET orderitem_index;

直接基于原始表生成最终视图，避免嵌套依赖。

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

Firestore BigQuery导出扩展查询性能不佳，求优化方案

补充说明 #

原始数据表 #

生成的两个视图 #

慢查询示例 #

1. 替换视图为实体表（定时刷新） #

2. 优化原视图查询逻辑 #

3. 给原始表加分区和聚类 #

4. 合并嵌套视图逻辑 #