无CHECK CONSTRAINT时，BigQuery如何实现数据合法性校验？

我们的应用未部署在服务器上，所有业务逻辑都需要在BigQuery内完成。在Microsoft SQL Server中，可通过CHECK CONSTRAINT实现数据校验，示例代码如下：

CREATE TABLE MyCleanJapanClient (
    Phone varchar(255) CHECK (Phone LIKE '+81%'),
    Email varchar(255) CHECK (Email LIKE '%_@__%.__%')
);

执行插入语句时，若数据不符合约束规则，会触发报错：The INSERT statement conflicted with the CHECK constraint

INSERT INTO `MyCleanJapanClient` 
SELECT
  Phone AS Phone,
  Email AS Email
FROM `YourDirtyJapanClient`;

以下是BigQuery中实现类似数据校验的几种方法：

1. 使用强制执行的CHECK约束 #

BigQuery支持CHECK约束，但默认不强制执行，需显式指定ENFORCED来确保插入/更新数据时触发校验，语法如下：

CREATE TABLE MyCleanJapanClient (
    Phone STRING,
    Email STRING,
    -- 校验日本手机号以+81开头
    CONSTRAINT chk_japan_phone CHECK (STARTS_WITH(Phone, '+81')) ENFORCED,
    -- 校验邮箱格式（用正则表达式更精准）
    CONSTRAINT chk_valid_email CHECK (REGEXP_CONTAINS(Email, r'^[^@]+@[^@]+\.[^@]+$')) ENFORCED
);

当插入不符合约束的数据时，BigQuery会直接报错，比如：Constraint chk_japan_phone violated，和SQL Server的行为一致。

2. 插入时过滤脏数据（保留错误记录） #

如果不想直接报错，而是希望过滤合规数据入库，同时留存错误数据用于后续排查，可以在INSERT语句中添加WHERE条件：

-- 插入合规数据到目标表
INSERT INTO `MyCleanJapanClient`
SELECT Phone, Email
FROM `YourDirtyJapanClient`
WHERE STARTS_WITH(Phone, '+81')
  AND REGEXP_CONTAINS(Email, r'^[^@]+@[^@]+\.[^@]+$');

-- 将不符合规则的数据存入错误记录表（需提前创建对应表结构）
INSERT INTO `InvalidJapanClientRecords`
SELECT 
  Phone, 
  Email, 
  CASE
    WHEN NOT STARTS_WITH(Phone, '+81') THEN '手机号不符合日本格式（需以+81开头）'
    WHEN NOT REGEXP_CONTAINS(Email, r'^[^@]+@[^@]+\.[^@]+$') THEN '邮箱格式无效'
    ELSE '未知错误'
  END AS error_reason
FROM `YourDirtyJapanClient`
WHERE NOT (STARTS_WITH(Phone, '+81') AND REGEXP_CONTAINS(Email, r'^[^@]+@[^@]+\.[^@]+$'));

3. 创建校验视图层 #

如果源表无法修改，可创建一个视图，仅展示符合校验规则的数据，业务逻辑直接查询该视图即可：

CREATE OR REPLACE VIEW ValidJapanClientView AS
SELECT Phone, Email
FROM `YourDirtyJapanClient`
WHERE STARTS_WITH(Phone, '+81')
  AND REGEXP_CONTAINS(Email, r'^[^@]+@[^@]+\.[^@]+$');

内容的提问来源于stack exchange，提问作者少強甘