如何在BigQuery中关联firestore-bigquery-export导入的集合与子集合表

核心思路是从子集合photos_view的document_name字段中提取父文档user_id，再与users表的document_id字段关联。

操作步骤 #

1. 验证路径结构，确认user_id在路径中的位置
   先执行以下查询查看document_name拆分后的层级，确认user_id对应的数组下标：

SELECT
  document_name,
  SPLIT(document_name, '/') AS path_parts
FROM `你的项目ID.你的数据集名.photos_view`
LIMIT 10

常规场景下路径有两种格式：

• 短路径格式如database/users/user_id_1/photos/photo_1：user_id对应数组下标为2
• 完整资源路径格式如projects/项目ID/databases/(default)/documents/users/user_id_1/photos/photo_1：user_id对应数组下标为6

2. 提取user_id并关联users表
   将第一步确认的下标填入OFFSET参数即可，完整关联查询示例如下：

SELECT
  u.document_id AS user_id,
  u.data.user_name AS user_name,
  p.document_id AS photo_id,
  p.data.url AS photo_url
-- 此处替换为你实际的users表名，通常导出后默认视图为users_raw_latest
FROM `你的项目ID.你的数据集名.users_raw_latest` u
LEFT JOIN (
  SELECT
    *,
    -- 此处OFFSET后的数字替换为第一步确认的user_id下标
    SPLIT(document_name, '/')[OFFSET(2)] AS extracted_user_id
  FROM `你的项目ID.你的数据集名.photos_view`
) p
ON u.document_id = p.extracted_user_id

注意事项 #

如果子集合的层级有调整，只需要重新确认路径拆分后的下标，修改OFFSET值即可适配。

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