BigQuery默认值填充的异常行为问题咨询

问题原因 #

BigQuery的列默认值仅在未显式提供该列的值时才会触发。当你使用INSERT ... SELECT *或显式指定插入b列时，哪怕源表的b列是NULL，BigQuery会认为你是主动插入NULL值，而非省略该列，因此不会启用默认值填充。

解决方案 #

根据需求不同，有三种常用处理方式：

1. 省略目标表的b列，自动填充默认值

如果源表的b列数据无需保留（或全部为NULL），直接只插入a列即可：

INSERT INTO `******.***STG_REFRESH_UAT.test1`(a)
SELECT a FROM `******.***STG_REFRESH_UAT.test`

执行后，目标表b列会自动使用DDL设置的默认值100。

2. 保留非NULL值，仅替换NULL为默认值

如果源表b列存在有效数据需要保留，仅将NULL替换为默认值，用IFNULL或COALESCE函数处理：

INSERT INTO `******.***STG_REFRESH_UAT.test1`(a, b)
SELECT 
  a,
  IFNULL(b, 100) AS b
FROM `******.***STG_REFRESH_UAT.test`

COALESCE(b, 100)与IFNULL效果一致，可按需选择。

3. 动态获取目标表默认值（适配默认值变动场景）

若不想硬编码默认值，可通过查询元数据动态获取目标表的默认值：

DECLARE default_b INT64;
SET default_b = (
  SELECT 
    CAST(JSON_EXTRACT_SCALE(options, '$.defaultValue') AS INT64)
  FROM `******.***STG_REFRESH_UAT.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'test1' AND column_name = 'b'
);

INSERT INTO `******.***STG_REFRESH_UAT.test1`(a, b)
SELECT 
  a,
  IFNULL(b, default_b) AS b
FROM `******.***STG_REFRESH_UAT.test`

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