CODEWITHSUNDEEP
google-bigquery
ajor
ajor

Reputation: 1634

BigQuery Standard SQL - store query or UDF in table

Is it possible to store data in a table that can then be converted into either a SQL query or a UDF - like a javascript eval()?

The use case is that I have a list of clients where earnings are calculated in quite significantly different ways for each, and this can change over time. So I would like to have a lookup table which can be updated with a formula for calculating this figure rather than having to write not only hundreds of queries (one for each client) but also maintain these.

I have tried to think if there is a way of having a standard formula that would be flexible enough, but I really don't think it's possible unfortunately.

Upvotes: 1

Views: 229

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59245

Sure! BigQuery can define and use JS UDFs. The good news is that eval() works as expected:

CREATE TEMP FUNCTION calculate(x FLOAT64, y FLOAT64, formula STRING)
RETURNS FLOAT64
LANGUAGE js AS """
  return eval(formula);
""";

WITH table AS (
  SELECT 1 AS x, 5 as y, 'x+y' formula
  UNION ALL SELECT 2, 10, 'x-y'
  UNION ALL SELECT 3, 15, 'x*y'
)

SELECT x, y, formula, calculate(x, y, formula) result
FROM table;

enter image description here

Upvotes: 3

Related Questions