如何在BigQuery中高效关联4个大型Salesforce表（sendID为非唯一键）

问题背景 #

需要以非唯一键sendID关联4个仅含2022年数据的Salesforce表，通过GROUP BY实现sendID唯一化并整合各表字段：

• salesforce_sent：最大的邮件发送记录表
• salesforce_open：邮件打开记录表（数据量较大）
• salesforce_clicks：邮件点击记录表
• salesforce_sendjobs：关联Salesforce与Google Analytics的表

原方案用带WITH子句的LEFT/INNER JOIN预处理分组后关联，查询耗时2-3小时，处理数据量达100GB，瓶颈集中在关联操作；UNION ALL无法保留各表完整行信息，不符合需求。原SQL如下：

WITH SENT AS (
SELECT
  sendid as sendid,
  EXTRACT(date FROM eventdate) as sent_date,
  lower(emailaddress) as emailaddress,
  COUNT(*) as sent,
FROM salesforce_sent
group by 1,2,3
),

CLICKS AS (
SELECT
  sendid as sendid,
  EXTRACT(DATE from eventdate) as click_date,
  url as url,
  regexp_extract(url, r'utm_source=([^&]+)') as source,
  regexp_extract(url, r'utm_medium=([^&]+)') as medium,
  regexp_extract(url, r'utm_campaign=([^&]+)') as campaign,
  regexp_extract(url, r'utm_content=([^&]+)') as ad_content,
  isunique as isunique_click,
  COUNT(*) as clicks,
FROM salesforce_clicks
group by 1,2,3,4,5,6,7,8
),

OPEN AS (
SELECT
  sendid as sendid,
  EXTRACT(date FROM eventdate) as open_date,
  isunique as isunique_open,
  COUNT(*) as open
FROM salesforce_opens
group by 1,2,3
),

SENDJOBS AS (
SELECT
  sendid as sendid,
  EXTRACT(date FROM senttime) as sent_date,
  LOWER(emailname) as  emailname,
  LOWER(SPLIT(emailname, '-')[SAFE_OFFSET(1)]) AS pos,
FROM salesforce_sendjobs
group by 1,2,3,4
)

SELECT 
  a.sendid as sendid,
  a.sent_date,
  c.open_date,
  d.click_date,
  a.emailaddress,
  b.emailname,
  d.url as url,
  b.pos,
  d.source,
  d.medium,
  d.campaign,
  d.ad_content,
  sum(a.sent) as sent,
  sum(c.open) as open,
  sum(d.clicks) as clicks
FROM SENT a
INNER JOIN SENDJOBS b ON a.sendid = b.sendid
INNER JOIN OPEN c ON a.sendid = c.sendid
INNER JOIN CLICKS d ON a.sendid = d.sendid
WHERE 1=1
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12

优化方案 #

1. 提前过滤2022年数据，砍预处理量级 #

原WITH子句未限定年份，直接在每个子查询中加入日期过滤，排除非2022年数据，大幅减少初始处理的数据量：

-- 示例：在salesforce_sent中添加过滤
WHERE EXTRACT(YEAR FROM eventdate) = 2022

其他表同理：salesforce_open、salesforce_clicks用eventdate过滤，salesforce_sendjobs用senttime过滤。如果表是按日期分区的，直接指定分区范围（如_PARTITIONDATE BETWEEN '2022-01-01' AND '2022-12-31'），避免全表扫描。

2. 压缩分组维度，避免关联后数据爆炸 #

原CLICKS子句按url、utm_source等8个字段分组，导致子查询结果集极大，后续关联时产生大量冗余数据。若目标是按sendID唯一整合，可将url、utm等字段聚合为数组（保留所有信息），仅按sendID分组：

CLICKS AS (
SELECT
  sendid,
  MAX(EXTRACT(DATE from eventdate)) as click_date, -- 取最后一次点击日期，按需调整
  COUNT(*) as clicks,
  COUNT(DISTINCT CASE WHEN isunique THEN 1 END) as unique_clicks,
  ARRAY_AGG(DISTINCT url) as urls,
  ARRAY_AGG(DISTINCT regexp_extract(url, r'utm_source=([^&]+)')) as sources,
  ARRAY_AGG(DISTINCT regexp_extract(url, r'utm_medium=([^&]+)')) as mediums,
  ARRAY_AGG(DISTINCT regexp_extract(url, r'utm_campaign=([^&]+)')) as campaigns,
  ARRAY_AGG(DISTINCT regexp_extract(url, r'utm_content=([^&]+)')) as ad_contents
FROM salesforce_clicks
WHERE EXTRACT(YEAR FROM eventdate) = 2022
GROUP BY 1
)

同理，OPEN子句也可简化为仅按sendID分组，聚合打开相关统计值。

3. 调整关联顺序，从最小表开始 #

原查询从最大的salesforce_sent出发关联，应改为从数据量最小的sendjobs表开始，依次关联其他表，减少中间结果集大小：

-- 关联顺序调整为：SENDJOBS → SENT → OPEN → CLICKS
FROM SENDJOBS b
INNER JOIN SENT a ON b.sendid = a.sendid
LEFT JOIN OPEN c ON b.sendid = c.sendid -- 允许无打开记录的sendID保留
LEFT JOIN CLICKS d ON b.sendid = d.sendid -- 允许无点击记录的sendID保留

4. 简化函数计算，降低CPU开销 #

原SENDJOBS中的SPLIT(emailname, '-')[SAFE_OFFSET(1)]可用更高效的字符串截取函数替代（假设emailname格式为xxx-pos）：

LOWER(SUBSTR(emailname, INSTR(emailname, '-') + 1)) AS pos

比SPLIT函数的计算开销更低。

优化后的完整SQL #

WITH SENT AS (
SELECT
  sendid,
  EXTRACT(date FROM eventdate) as sent_date,
  lower(emailaddress) as emailaddress,
  COUNT(*) as sent,
FROM salesforce_sent
WHERE EXTRACT(YEAR FROM eventdate) = 2022
GROUP BY 1,2,3
),

CLICKS AS (
SELECT
  sendid,
  MAX(EXTRACT(DATE from eventdate)) as click_date,
  COUNT(*) as clicks,
  COUNT(DISTINCT CASE WHEN isunique THEN 1 END) as unique_clicks,
  ARRAY_AGG(DISTINCT url) as urls,
  ARRAY_AGG(DISTINCT regexp_extract(url, r'utm_source=([^&]+)')) as sources,
  ARRAY_AGG(DISTINCT regexp_extract(url, r'utm_medium=([^&]+)')) as mediums,
  ARRAY_AGG(DISTINCT regexp_extract(url, r'utm_campaign=([^&]+)')) as campaigns,
  ARRAY_AGG(DISTINCT regexp_extract(url, r'utm_content=([^&]+)')) as ad_contents
FROM salesforce_clicks
WHERE EXTRACT(YEAR FROM eventdate) = 2022
GROUP BY 1
),

OPEN AS (
SELECT
  sendid,
  MAX(EXTRACT(date FROM eventdate)) as open_date,
  COUNT(DISTINCT CASE WHEN isunique THEN 1 END) as unique_opens,
  COUNT(*) as open
FROM salesforce_opens
WHERE EXTRACT(YEAR FROM eventdate) = 2022
GROUP BY 1
),

SENDJOBS AS (
SELECT
  sendid,
  EXTRACT(date FROM senttime) as sent_date,
  LOWER(emailname) as emailname,
  LOWER(SUBSTR(emailname, INSTR(emailname, '-') + 1)) AS pos
FROM salesforce_sendjobs
WHERE EXTRACT(YEAR FROM senttime) = 2022
GROUP BY 1,2,3,4
)

SELECT 
  b.sendid,
  COALESCE(a.sent_date, b.sent_date) as sent_date, -- 取有效的发送日期
  c.open_date,
  d.click_date,
  a.emailaddress,
  b.emailname,
  d.urls,
  b.pos,
  d.sources,
  d.mediums,
  d.campaigns,
  d.ad_contents,
  SUM(a.sent) as total_sent,
  SUM(c.open) as total_opens,
  SUM(d.clicks) as total_clicks,
  SUM(c.unique_opens) as total_unique_opens,
  SUM(d.unique_clicks) as total_unique_clicks
FROM SENDJOBS b
INNER JOIN SENT a ON b.sendid = a.sendid
LEFT JOIN OPEN c ON b.sendid = c.sendid
LEFT JOIN CLICKS d ON b.sendid = d.sendid
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12

额外建议 #

• 确保sendid字段有索引，加速关联时的匹配查找；
• 如果业务允许，可将预处理后的子查询结果存储为临时表，避免重复计算；
• 检查各表的sendid数据类型是否一致，避免隐式类型转换导致的性能损耗。

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