google-bigquery - bigquery 类型检查运算符？像 Javascript 中的 typeof；或解决方法

Question

在一个 BigQuery 表的架构不断发展的项目中，我想知道有没有一种用泛型编写 SQL 代码的好方法？

例如，版本 1 中的功能标志字段是一个简单的 BOOLEAN，但后来演变为 FLOAT 以表示 0(假)和 1(真)之间的值，后来又变成了多个 BOOLEAN 的 STRUCT，对于每个模式修订，我也更改了表名，所以现在我有当前表 v3 和旧表 v2 和 v1，旧表有历史信息有时仍然有用，而且体积很大，不适合全部迁移到 v3架构；由于 bigquery 主要用作一次加载然后仅追加，或者大多数情况下是只读数据库，因此仅从旧表中查询就足够了；使用表名通配符我可以在单个查询中查询所有表，但不确定如何处理不同的输入类型，是否有动态类型检查函数来编写查询 SQL，如 typeof 运算符在Javascript中:？

CASE typeof feature
  WHEN "BOOLEAN" THEN ... # handle v1
  WHEN "FLOAT"   THEN ... # handle v2
  WHEN "STRUCT"  THEN ... # handle v3
  WHEN ...
  ELSE
END

或者您建议解决什么问题？如果项目的性质具有不断发展的模式(由于快速变化的需求或许多其他常见原因)

Answer 1

FORMAT('%T', obj)将任何类型的值转换为文字字符串。可用于在 SQL UDF 中实现 typeof 函数。

CREATE TEMP FUNCTION typeof_literal(input STRING)
AS (
      CASE
        -- Process NUMERIC, DATE, DATETIME, TIME, TIMESTAMP,
        WHEN REGEXP_CONTAINS(input, r'^[A-Z]+ "') THEN REGEXP_EXTRACT(input, r'^([A-Z]+) "')
        WHEN REGEXP_CONTAINS(input, r'^-?[0-9]*$') THEN 'INT64'
        WHEN REGEXP_CONTAINS(input, r'^(-?[0-9]+[.e].*|CAST\("([^"]*)" AS FLOAT64\))$') THEN 'FLOAT64'
        WHEN input IN ('true', 'false') THEN 'BOOL'
        WHEN input LIKE '"%' THEN 'STRING'
        WHEN input LIKE 'b"%' THEN 'BYTES'
        WHEN input LIKE '[%' THEN 'ARRAY'
        WHEN REGEXP_CONTAINS(input, r'^(STRUCT)?\(') THEN 'STRUCT'
        WHEN input LIKE 'ST_%' THEN 'GEOGRAPHY'
        WHEN input = 'NULL' THEN 'NULL'
      ELSE
      'UNKNOWN'
    END );

CREATE TEMP FUNCTION typeof(input ANY TYPE)
AS ( typeof_literal(FORMAT('%T', input)) );

-- You can pass any type value to typeof function
SELECT typeof(CURRENT_TIMESTAMP());
-- result: "TIMESTAMP"
SELECT typeof(STRUCT(1, 2, 3));
-- result: "STRUCT"

详尽的测试和结果放在 gist因为太长了。

更新

您可以使用基于社区的公共(public) UDF bqutil.fn.typeof .

CASE bqutil.fn.typeof(feature)
  WHEN "BOOLEAN" THEN ... # handle v1
  WHEN "FLOAT"   THEN ... # handle v2
  WHEN "STRUCT"  THEN ... # handle v3
  WHEN ...
  ELSE
END

注意:BigQuery 在执行查询之前会进行类型检查，因此 THEN 子句中的表达式必须在所有 WHEN-THEN 对中都有效。