BigQuery 用下来的一些心得

数据团队一直用 BigQuery 跑离线分析，我这边主要是把业务库的数据同步过去，再给数据同事写点查询接口。用了小一年，最大的感受就是这东西上手极快，但账单和查询习惯这两块不注意一下真的容易翻车。

第一次被账单教育是因为写了个查询没加分区过滤，团队的表按天分区，我图省事直接 SELECT * 加了个业务条件就跑了：

SELECT user_id, event_type, created_at
FROM analytics.events
WHERE event_type = 'purchase'

跑完一看这条查询扫了小两百 GB，因为它把整张表所有分区全扫了一遍，event_type 这个过滤条件根本帮不上忙，BigQuery 不会因为你过滤了别的字段就自动跳过不相关的分区。BigQuery 是按扫描字节数计费的，这一条查询单独跑出来的钱够喝好几顿奶茶了。后来养成习惯，只要是分区表，查询里必须带上分区字段的过滤：

SELECT user_id, event_type, created_at
FROM analytics.events
WHERE _PARTITIONDATE = '2024-07-10'
  AND event_type = 'purchase'

还有个配套的坏习惯是写查询之前不看预估扫描量，BigQuery 控制台其实在你输入 SQL 的时候右上角就会实时显示这次查询大概要扫多少数据，这个提示条我以前压根没注意过，后来才知道原来早就有这功能，写完就顺手瞄一眼，超出预期就得回去看看是不是漏了分区条件或者不小心笛卡尔积了。

第二个坑是 SELECT * 这个习惯在列式存储上代价比想象中大。BigQuery 是列式存储，只读你查询里涉及的列，SELECT * 等于把所有列都读一遍，哪怕你只是想看看这张表长什么样。之前调试的时候随手 SELECT * FROM huge_table LIMIT 10，以为加了 LIMIT 就没事，结果一看账单扫描量没有明显减少——LIMIT 只是限制返回的行数，不会减少扫描的数据量，该读的列一行都不会少读。后来调试用小表或者干脆用 BigQuery 自带的表预览功能（不产生查询费用），别一言不合就上 SELECT * LIMIT 10。

JOIN 这块也吃过亏，两张大表 JOIN 的时候没注意数据倾斜，其中一个 join key 有个值占了整张表一半以上的行数，跑起来慢得离谱还经常超时。BigQuery 底层是分布式执行的，倾斜的 key 会导致某个 worker 节点分到的数据量远超其他节点，木桶效应，整体查询时间被这一个热点值拖死。后来学乖了，join 之前先跑个 GROUP BY 看看 key 的分布情况，遇到严重倾斜的场景要么拆成两条查询分别处理热点值和长尾值，要么找数据团队的同事帮忙看看有没有更好的建模方式。

物化视图（materialized view）这东西刚接触觉得像变魔术，把常用的聚合查询结果预先算好存起来，后续查询命中的话直接读预计算结果，又快又省钱。但也不是万能的，底层表更新频繁的话物化视图的维护成本（自动增量刷新）也会跟着涨，用之前最好想清楚这张表的更新频率和查询频率的比例，更新特别频繁但查询很少的表用物化视图反而不划算。

权限管理这块跟公司云平台账号体系整合的时候费了点劲，BigQuery 的权限是数据集级别、表级别都能单独设，一开始图方便直接把数据集权限开得比较宽，后来安全审计的时候被要求收紧，才按最小权限原则重新梳理了一遍每个服务账号实际需要访问哪些表，用 authorized views 控制哪些人能看哪些字段（比如给非核心团队开放的视图会把用户手机号这类敏感字段直接排除掉）。这块建议一开始就按最小权限设计，回头收紧比一开始就设计好的成本高不少。

总的来说 BigQuery 处理海量数据确实爽，SQL 一写扫描几百 GB 也就几秒钟的事，但正因为这么快、这么容易上手，反而容易忽略背后的扫描成本，写查询的时候多留意一下预估扫描量和分区裁剪,能省下不少不必要的账单。

Tags: #BigQuery #GCP #数据分析