String interpolation in BQ JS UDF not supported in SQLX

[yan-hic]

I need to create some SQL statements at execution time, not compilation, and have therefore turned to a BQ UDF in JS.
It took me some time to figure that Dataform engine is confusing interpolation used within my UDF with "it's own".
For example,

create temp function test(x string)
returns string language js as r"return `seems ${x} to me`";
select test('ok')

is perfectly valid and works as-is in BQ SQL workplace but not in a dataform SQLX as its templating uses the same ES6 notation. One would get x not defined as not exported by any js module.

Instead of reverting to verbose string concatenations - even if my AI friend can help me-, any idea/trick to go around this, e.g. using a special notation ? If not, could be a feature request.

[ms4446]

Yes, you've correctly identified the core issue: both BigQuery JS UDFs and Dataform's SQLX templating utilize the same ES6 template literal syntax (${variable}) for variable interpolation. This leads to Dataform attempting to interpret variables within your UDF code that aren't meant for its templating engine.

Solutions Here are a few strategies to circumvent this:

1. Escape Template Literals in UDF To prevent Dataform from misinterpreting the template literals within your UDF's JavaScript code, escape the backticks and the dollar sign:

   CREATE TEMP FUNCTION test(x STRING) RETURNS STRING LANGUAGE js AS """ return \`seems \${x} to me\`; """; 
   

2. Distinct Placeholder Convention Adopt a different placeholder convention within your UDF code to distinguish it from Dataform's templating. For example:

   CREATE TEMP FUNCTION test(x STRING) RETURNS STRING LANGUAGE js AS """ return 'seems [[x]] to me'.replace('[[x]]', x); """; 
   -- In your Dataform SQLX: SELECT test('ok') AS result; 
   

   Dataform will ignore the [[x]] placeholders while your UDF handles the replacement using JavaScript's replace function (or similar) for greater efficiency.

3. String Concatenation (If Feasible) For simpler dynamic SQL generation, traditional string concatenation is a viable option:

CREATE TEMP FUNCTION test(x STRING) RETURNS STRING LANGUAGE js AS """ return 'seems ' + x + ' to me'; """; 

Choosing the Best Approach

The optimal solution depends on the complexity of the SQL statements you're dynamically generating within your UDFs:

[yan-hic]

First of all, thank you @ms4446  for the prompt reply - and very detailed answers ! Yours always go the extra mile and really help catch up fast (coming from Airflow).  

I had tried escaping the $ sign but to no avail, DF still tries to interpolate. It seems the engine ignores the escaping.

I have ended up using this python-inspired function (until escaping is supported):

const f = (template, ...args) => {let argIndex = 0; return template.replace(/%s/g, () => args[argIndex++] || ''); };

 usage: f('seems %s to me', 'ok') - good enough for my use case

[ms4446]

Given the issue with escaping, your custom formatting function is an excellent workaround to ensure reliable string interpolation within your BigQuery UDFs while working in Dataform!