Google Cloud BigQuery 数据仓库怎么设计，从数据导入到查询逐步理解

很多企业第一次接触 BigQuery 时，容易把它理解成一台放在云端的“大型数据库”。这种理解并不完全准确。BigQuery 是 Google Cloud 上的全托管分析型数据仓库，它最擅长的事情不是处理传统OLTP数据库那种大量逐行写入和高频小事务，而是面对大规模数据进行扫描、聚合、关联和分析。

因此，设计 BigQuery 数据仓库时，思路不能简单照搬 MySQL、PostgreSQL 或传统 Oracle 数据仓库。一个查询为什么慢，往往不是因为“服务器不够快”，而是因为查询一次扫描了数TB数据；一张表为什么越来越贵，也可能不是存储成本太高，而是每天重复扫描历史数据；一个报表为什么在数据量增加以后突然变慢，则可能与分区、聚类、JOIN方式以及工作负载管理有关。

理解 BigQuery，最好按照一条完整的数据链路来看：数据从哪里来，进入什么层，怎样建模，怎样存储，查询时扫描多少数据，如何控制计算资源，最后如何进行权限、监控和成本治理。

从数据源开始设计

一个典型的 BigQuery 数据平台可能同时接收来自业务数据库、应用日志、SaaS平台、IoT设备、消息系统以及文件系统的数据。

Google Cloud Storage 可以作为非常重要的数据落地区域。CSV、JSON、Avro、Parquet 等文件都可以通过不同方式进入 BigQuery。对于大规模分析数据，Parquet、Avro 等结构化格式通常比CSV更适合数据工程流程，因为它们能够更好地保留类型信息，并且可以配合列式数据处理减少无意义的数据读取。

如果数据来自 Cloud SQL、Spanner、外部数据库或者企业业务系统，则可以根据实时性要求采用不同的数据同步方案。批处理、增量同步、CDC以及流式数据管道所适合的场景并不一样。

Dataflow 也不能简单理解成“BigQuery的数据导入接口”。它更适合承担数据处理、转换和流式管道任务。例如应用产生大量事件以后，可以经过消息系统进入 Dataflow，在那里完成清洗、转换、窗口计算和格式转换，再写入 BigQuery。

对于实时数据，BigQuery 也支持通过相应的写入方式持续接收数据，但实时写入并不意味着所有查询都应该实时执行。企业需要首先判断业务到底需要秒级、分钟级还是小时级数据。

如果财务报表每天凌晨更新一次已经足够，就没有必要为了追求“实时”而建立昂贵复杂的流式架构。

数据进入BigQuery以后不要急着做报表

成熟的数据仓库通常不会把所有数据直接扔进一张供业务人员查询的大表。

一个常见的架构会把数据分成不同层次。例如原始数据首先进入Raw或者Landing层，保留从源系统进入的数据；之后经过清洗、类型转换、去重和业务规则处理，进入标准化数据层；最终再形成面向分析的事实表、维度表以及报表数据集。

这样做的意义在于，当业务规则发生变化时，企业不必重新从几十个数据源开始处理。

例如电商系统每天产生订单、付款、退款和物流数据。原始订单数据可以保持接近源系统状态，而经过清洗以后形成统一的订单事实表。客户、商品、地区、销售渠道等则可以成为维度。

这种架构比把所有字段塞进一张“万能大表”更加容易维护。

分区是BigQuery性能和成本设计的核心工具之一

BigQuery 数据仓库中非常重要的设计概念之一就是 partitioning，也就是分区。

如果一张订单表保存了五年的数据，而用户每天查询的只是最近七天订单，那么让查询每次扫描五年的数据显然没有意义。

因此可以根据业务特点按照日期或时间戳建立时间分区。例如按照订单日期进行日分区，让查询最近一个月数据时能够尽可能排除历史分区。

但分区并不是“有日期字段就必须分区”。

如果数据规模很小，分区带来的管理复杂度可能没有意义。如果业务查询几乎从不按照某个时间字段过滤，也需要重新考虑分区字段是否合适。

分区字段应该围绕最常见、最有选择性的查询条件设计，而不是机械地给所有表增加一个date字段然后进行分区。

另外还要避免查询条件破坏分区裁剪。例如业务明明只需要最近30天数据，却在SQL中对分区字段进行了不必要的复杂函数处理，导致优化器无法有效缩小扫描范围。

聚类不是传统数据库里的“索引”

原稿中把 BigQuery 的 clustering 称为“分桶”，并建议设置128或者256个桶，这种说法不准确。

BigQuery 的 clustering 是一种物理数据组织方式，而不是传统MySQL意义上的B-tree索引，也不存在要求用户手动决定“128个桶还是256个桶”的通用规则。

聚类字段应该根据实际查询模式选择。

例如一张按照日期分区的订单表，经常按照customer_id、region或者product_id进行过滤或聚合，那么可以考虑选择其中合适的字段进行 clustering。

分区解决的是“应该读取哪些大块数据”，聚类进一步帮助系统在这些数据内部减少不必要的数据读取。

这两种机制可以组合使用。

例如：

一个订单表首先按照order_date进行分区，再按照customer_id等字段进行聚类。

当用户查询某一时间范围内某些客户的订单时，系统首先可以排除不需要的历史分区，然后利用聚类信息进一步减少需要读取的数据。

但聚类字段不是越多越好。应该根据真实查询模式和数据分布选择，而不是为了“看起来高级”把多个字段全部放进去。

数据模型仍然需要业务逻辑

BigQuery 是分析型数据仓库，并不意味着数据建模已经不重要。

星型模型仍然具有很强的实用价值。

例如销售分析可以建立sales_fact事实表，再建立customer_dim、product_dim、store_dim和date_dim等维度表。

事实表保存大量业务事件，维度表保存相对稳定的业务属性。

这种结构对于BI工具、报表系统以及复杂分析通常比较容易理解。

但也不能机械地认为所有BigQuery项目都必须使用传统星型模型。对于日志分析、事件分析、半结构化数据以及某些数据科学场景，宽表、嵌套字段和数组结构同样可能更加合适。

BigQuery 支持STRUCT和ARRAY等复杂数据类型，因此在建模时可以利用这些能力减少某些不必要的JOIN。

最终应该根据查询模式决定模型，而不是先选择一种“标准模型”，再强迫所有业务适应它。

查询优化首先看扫描了多少数据

BigQuery 查询性能分析中，一个非常重要的指标就是数据处理量。

很多开发人员看到SQL运行时间以后，第一反应是“是不是计算资源不够”。但对于分析型数据仓库来说，首先应该问的是：这个查询为什么要读取这么多数据？

例如下面这种查询：

SELECT *
FROM orders;

如果orders表有几十TB数据，这条SQL本身就可能制造非常大的扫描量。

而如果业务实际上只需要三个字段和最近30天的数据，更合理的写法应该是明确选择字段，并限制查询范围。

SELECT
order_id,
customer_id,
amount
FROM `project.dataset.orders`
WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY);

因此，避免SELECT *、合理过滤分区、只读取必要字段，是BigQuery优化中非常基础但也非常有效的手段。

JOIN往往比单表扫描更值得检查

当查询涉及多个大型事实表时，JOIN可能成为性能和成本问题的重要来源。

首先要确认JOIN条件是否正确。

如果一个本应该一对一的关联，因为数据重复变成了一对多，最终结果可能突然膨胀数十倍甚至数百倍。

其次要注意不要在JOIN之前无意义地读取大量数据。

如果业务只需要最近三个月的销售数据，就应该尽可能在JOIN之前先缩小数据范围，而不是把几年历史数据全部JOIN以后再过滤。

对于大型事实表，还应该考虑数据模型是否合理。有些重复JOIN可以通过预计算、汇总表或者物化视图减少。

但物化视图也不是“打开以后所有查询都会自动变快”。它适合具有稳定查询模式和可复用聚合逻辑的场景，需要根据实际工作负载评估维护成本和收益。

查询缓存不要当成性能优化的万能药

BigQuery具有查询缓存能力，在满足条件时，重复查询可以直接使用缓存结果，从而减少重新执行查询的需要。

但缓存并不是企业应该依赖的主要性能策略。

数据发生变化、查询条件发生变化或者查询本身不满足缓存条件时，都可能无法获得缓存收益。

因此不能因为某个报表运行得很快，就认为SQL本身已经优化。

真正应该优化的是底层数据读取、分区裁剪、聚类、JOIN、聚合以及工作负载设计。

对于需要稳定低延迟的重复分析，还可以考虑预聚合表、物化视图或者其他专门的数据服务，而不是单纯等待查询缓存解决问题。

EXPLAIN只是工具，不是优化答案

BigQuery提供查询计划和相关执行信息，可以帮助工程师分析查询到底在哪个阶段消耗了大量资源。

分析执行计划时，不应该只盯着某一个“计算节点”。

更应该观察数据读取量、JOIN、聚合、排序、shuffle以及不同阶段的资源消耗。

例如一个SQL只扫描几十GB，却因为大型JOIN产生大量shuffle，那么问题可能并不是存储读取，而是查询结构。

反过来，一个查询逻辑非常简单，却每次扫描数TB历史数据，那么继续调整JOIN顺序可能没有意义，首先应该解决数据过滤和分区问题。

专业优化的核心不是“让SQL看起来复杂”，而是减少不必要的工作。

BigQuery成本控制不能只看存储价格

BigQuery的成本治理通常需要同时观察存储和查询计算。

存储数据量不断增加当然会产生存储成本，但很多企业真正难以控制的是查询。

一个几十TB的数据仓库，如果每天只扫描很少的数据，成本可能完全可控；反过来，一个只有几TB的数据仓库，如果大量用户每天重复执行全表扫描，成本也可能迅速增加。

因此企业应该建立查询成本监控。

对于开发环境，可以要求工程师在执行大型查询之前估算数据处理量；对于生产环境，可以通过项目、用户、作业类型和工作负载进行监控。

BigQuery还提供不同的计算资源和容量管理方式，企业可以根据工作负载考虑按需计算或者基于槽位的容量管理。

高并发BI环境尤其需要注意这一点。

如果几十个报表同时执行复杂查询，与其简单地要求每个SQL“再优化一点”，不如从整体工作负载管理角度重新设计。

不要把Cloud Storage的Nearline当成BigQuery冷数据方案

原稿中“BigQuery冷数据使用Nearline Storage”的说法容易误导。

Nearline、Coldline等是Cloud Storage的存储类别，而BigQuery内部的数据存储和Cloud Storage对象存储并不是一回事。

如果企业希望把很少访问的历史数据迁移到对象存储，当然可以考虑把数据放到Cloud Storage，并根据访问模式选择合适的存储类别。

但这并不意味着在BigQuery内部把某些表简单设置成“Nearline”。

对于BigQuery本身，更应该关注数据生命周期、分区、存储费用以及查询访问模式。

如果某些历史数据几乎不会被查询，可以考虑归档到Cloud Storage，再通过适当的数据架构在需要时重新处理，而不是让所有历史数据永久保持在高频分析路径中。

安全设计应该从IAM和数据治理开始

BigQuery安全性并不是简单地“开启TLS”。

企业需要首先设计谁可以访问项目、数据集、表、列以及特定的数据内容。

Google Cloud IAM负责身份和权限控制，BigQuery本身还提供数据集、表和列级别的访问控制能力。

对于敏感数据，可以进一步使用列级访问控制、数据掩码等机制，让不同角色看到不同程度的信息。

例如财务人员可能需要看到完整交易金额，但普通分析人员只需要看到经过处理的客户标识。

对于审计要求较高的环境，还应该启用并分析相关审计日志。

加密方面，BigQuery的数据默认会进行服务器端加密。对于有更严格密钥控制、合规或组织安全要求的企业，可以考虑使用Cloud KMS提供的Customer-Managed Encryption Keys，也就是CMEK。

但CMEK并不是“打开以后查询更安全、性能更高”。它解决的主要是密钥控制、访问管理、生命周期和合规等问题。

网络安全不要简单写成VPC Peering

BigQuery是Google Cloud的托管分析服务，网络设计不能直接套用传统数据库服务器的VPC Peering思路。

企业如果有严格的网络访问控制要求，需要根据BigQuery的具体连接方式、Private Google Access、VPC Service Controls以及其他Google Cloud网络安全能力进行整体设计。

尤其对于涉及敏感数据的企业，网络边界只是其中一层。

真正完整的数据安全体系应该同时考虑身份认证、IAM、最小权限、数据分类、列级访问、加密、审计日志、密钥管理以及数据泄露防护。

TLS只是传输过程中的一环，并不能代替整个数据治理体系。

实时分析不等于所有数据都必须Streaming

BigQuery可以处理实时和近实时分析，但企业在设计实时架构时首先应该确定业务真正需要的延迟。

例如广告点击、IoT监控、风控等场景可能需要分钟甚至秒级数据；而财务报表、库存统计、管理层经营分析可能只需要每小时或者每天更新。

如果业务实际上允许15分钟延迟，就没有必要为了“实时”把所有系统设计成秒级Streaming架构。

数据实时性越高，系统复杂度通常越高。

消息系统、CDC、Dataflow、数据质量检查、重复事件处理、迟到数据、时间窗口以及失败重试都会进入架构。

因此实时数据平台首先是业务需求问题，其次才是技术问题。

数据仓库真正成熟以后，重点就从建表变成治理

BigQuery项目刚开始时，工程师往往关注如何把数据导进去、怎样建立第一张表。

当数据规模达到TB甚至PB级别以后，真正棘手的问题通常变成另外几件事情：谁可以访问数据，哪些表已经没人使用，哪些查询每天重复扫描大量数据，哪些数据应该归档，哪个团队产生了最多计算成本，以及某个字段修改以后会影响多少下游系统。

这时就需要数据目录、数据质量检查、血缘分析、权限治理、成本分摊以及生命周期管理。

例如，一个企业可能有几千张BigQuery表，但真正高频使用的只有几百张。如果没有治理机制，数据仓库很容易逐渐变成一个“数据垃圾场”。

表越来越多，字段越来越多，没人知道哪个才是官方数据源，BI团队自己复制一份，数据科学团队再复制一份，最终同一个销售指标出现三个不同数字。

这类问题不是BigQuery性能问题，而是数据治理问题。

一套实际可落地的BigQuery架构

对于多数中大型企业，可以从一个相对简单的架构开始。

业务系统负责产生原始数据，数据首先进入Cloud Storage、数据库或者事件管道；随后通过批处理、CDC或者Dataflow等方式进入BigQuery原始数据层；经过清洗、标准化和数据质量检查以后进入分析层；事实表根据实际查询模式进行分区和聚类；维度表保存相对稳定的业务属性；BI工具和分析人员从经过治理的数据集读取数据。

在查询层面，优先减少扫描量，再处理JOIN和聚合；在资源层面区分交互式查询、批处理、BI高并发以及数据工程任务；在安全层面采用最小权限、数据分类、列级访问和审计；在成本层面持续观察数据处理量和工作负载，而不是只看数据库容量。

这样的架构并不意味着一开始就要把所有Google Cloud服务全部部署起来。

恰恰相反，BigQuery的优势之一就是可以从比较简单的架构开始，然后随着数据量和业务需求增长逐步增加CDC、流式处理、Dataflow、物化视图、容量管理和数据治理能力。

对于企业，最昂贵的往往不是某一条SQL，而是错误的数据架构长期积累以后形成的重复存储、重复计算、重复管道和重复维护。

BigQuery真正值得掌握的核心并不是某一个命令，而是一种分析型数据仓库的设计思维：让数据在正确的地方保存，以正确的结构组织，在查询时尽可能少读取无关数据，在计算资源上按照工作负载进行管理，同时让权限、审计、生命周期和成本从第一天就进入架构设计。

当数据规模从几百GB增长到几十TB甚至PB以后，这种设计差异会从“SQL快几秒”逐渐变成每天数百甚至数千美元的基础设施成本差异，也会决定一个数据平台究竟是能够长期维护的企业系统，还是不断堆积数据和技术债务的云端数据仓库。