How to pass a string of column name as a parameter into a CREATE TABLE FUNCTION in BigQuery

user_9059348 0
Viewed 404

I want to create a table function that takes two arguments, fieldName and parameter, where I can later use this function to create tables in other fieldName and parameter pairs. I tried multiple ways, and it seems like the fieldName(column name) is always parsed as a string in the where clause. Wondering how should I be doing this in the correct way.

CREATE OR REPLACE TABLE FUNCTION dataset.functionName( fieldName ANY TYPE, parameter ANY TYPE)
as
(SELECT *
    FROM `dataset.table` 
    WHERE  format("%t",fieldName ) = parameter 
)

Later call the function as

SELECT * 
from dataset.functionName( 'passed_qa', 'yes')

(passed_qa is a column name and assume it only has 'yes' and 'no' value)

I tried using EXECUTE IMMEDIATE, it works, but I just want to know if there's a way to approach this in a functional way.

Thanks for any help!

google-bigquery google-cloud-platform sql
2 Answers

Good news - IT IS POSSIBLE!!! (side note: in my experience - i haven't had any cases when something was not possible to achieve in BigQuery directly or indirectly/workaround maybe with some few exceptions)

See example below

create or replace table function dataset.functionName(fieldName any type, parameter any type)
as (
select * from `bigquery-public-data.utility_us.us_states_area` t
where exists ( select true
    from unnest(`bqutil.fn.json_extract_keys`(to_json_string(t))) key with offset
    join unnest(`bqutil.fn.json_extract_values`(to_json_string(t))) value with offset
    using(offset)
    where key = fieldName and value = parameter
  )
)

Now, when table function created - run below as see result

select * 
from dataset.functionName('state_abbreviation', 'GU')

you will get record for GUAM

enter image description here

Then try below

select * 
from dataset.functionName('division_code', '0')

with output

enter image description here

user_52219440

For details see: https://cloud.google.com/bigquery/docs/reference/standard-sql/table-functions

A work-around can be to use a case statement to select the desired column. If any column is needed, please use the solution of Mikhail Berlyant.

Create or replace table function   Test.HL(fieldName   string,parameter ANY TYPE)
as
(
SELECT *
From (   select "1" as tmp, 2 as passed_qa) # generate dummy table
Where case 
    when fieldName="passed_qa" then format("%t",passed_qa)
    when fieldName="tmp" then format("%t",tmp)
    else ERROR(concat('column ',fieldName,' not found')) end = parameter
)
user_165295760
Related