bigquery type check operator? like typeof in Javascript; or workaround

google-bigquery

Solution

`FORMAT('%T', obj)` transforms any typed value to a literal string. It can be used to implement `typeof` function in SQL UDF.

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"

Exhaustive tests and result are placed in gist because it is too long.

Update

You can use community-based 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

Note: BigQuery does type check before query execution so expression in `THEN` clause must be valid in all `WHEN-THEN` pairs.

Problem

in a project where the BigQuery table's schema constantly evolves, I wonder is there a good way to write SQL code in generic? for example, a feature flag field in version 1 was a simple BOOLEAN, but later on evolved to a FLOAT to represent values between 0 (false) and 1 (true), then later changed to a STRUCT of multiple BOOLEANs, for every schema revision I changed table name as well, so now I have current table v3 and the old table v2 and v1 as well, the old tables have historical information is still useful sometimes, and volume is big not good to migrate all into v3 schema; since bigquery is mostly used as load-once and then append-only, or most cases read-only database, just query from old tables is good enough; with table name wildcards I can query all of the tables in a single query, but not sure how to handle the different input types, is there a dynamic type checking function to write the query SQL like this `typeof` operator in Javascript: ? ``` CASE typeof feature WHEN "BOOLEAN" THEN ... # handle v1 WHEN "FLOAT" THEN ... # handle v2 WHEN "STRUCT" THEN ... # handle v3 WHEN ... ELSE END ``` or what would you suggest to work around? if the project's nature has an constantly evolving schema (because of fast moving requirement or many other common reasons)

Original source