Have a dynamic parameter as a JS function

[caracena]

Hello everybody,

We are trying to start using Dataform to orquestate and versionize our BigQuery queries but in the early tests I find that many of the logic we use in most of our queries cannot be used in DF. For example, one thing that is very common for us is to catch the last character/digit of a string or number and make comparisons on the fly. For example return a boolean if it's even or false if it's odd.

Normally in BQ I would use the function RIGHT() (with the parameter 1) to get the last character and use that inside a CASE operation. What I'm trying to achieve is using the result of RIGHT() inside a custom function written in JavaScript (inside the SQLX file itself or in a modules.js file under includes) to turn the result into a boolean.

I've tried many different approaches. The last iteration is the following:

Even or odd function:

js {
    function is_even(n) {
        let res = false;
        if (n % 2 == 0) res = true;
        return res;
    }
}

 SQL:

SELECT ${is_even(RIGHT(id, 1))} AS even_or_odd
FROM ${ref("step-01")}

The result is always one of two: It doesn't find the field "id" from the source of, like in this case, it doen't find the function RIGHT. It also doesn't work nesting custom functions (i've also created one to replace RIGHT()).

One more thing. I've tried with types "table", "view" and "operations". Nothing. Is it possible?

Thanks in advance!

[kolban]

The way I understand Dataform to work is that the JavaScript function is called once during compilation of the SQL and the result of the function passed into the SQL statement.

So ... when you code:

SELECT ${is_even(RIGHT(id, 1))} AS even_or_odd
FROM ${ref("step-01")}

The function is_even() is invoked and either returns true or false.  However, the core thing to note is that it is invoked once.  We see that is passed RIGHT(id,1) ... I am actually surprised we didn't fail with an error ... but that's a different story.

At the end of the function calls, your statement likely looks something like:

SELECT true AS even_or_odd
FROM `somedataset.step-01`

And it is THAT statement that is sent to BigQuery for execution.  I believe the core misunderstanding is that the JavaScript function you have coded is not magically transformed into some kind of SQL that is passed to BigQuery.  It is not a function that is executed once per row for every row in your table.  Rather, it is a function that is executed ONCE during the compilation phase of your SQL statement.

[caracena]

Hello @kolban thanks for your reply.

I understand what you are saying and it gives me something else to think about. It would mean that I cannot use a JS/helper function to compute or transform a column for each of the resulting rows but that is something for another topic.

The problem here is, contrary of what you say (which makes total sense) is that in this case the function passed as a parameter for the JS function (in this case RIGHT() or any other SQL/BQ function I use) is not recognized as a function. Even if I don't use a function but instead I pass one of hte columns received from the SELECT statement, that column is marked as "Not found". Strange I know.

To make a clear example, if I try to run

SELECT ${is_even(id)} AS even_or_odd
FROM ${ref("step-01")}

it won't recognize "id". If instead I do a simple SELECT id FROM referenced able, it does recognize it. This happens even when the referenced table has 1 row.

My problem is that we need to transport hundred of queries into Dataform some being 20 lines long, and some being 2000 lines long. Imagine the amount of transformations and aggregations we need to make in all of those queries, for which we would need to use custom functions.

If I do a simple SELECT RIGHT(id, 1) it works of course, but if I need to see if that resulting digit is even or odd, to stay with the same example, in BQ I would use a UDF and use it with a CASE statement inside the SELECT itself.

Here in Dataform, it is to my understanding there's no UDF but there are JS functions so I created one again to tell me if it's odd or even:

js {
    function is_even(n) {
        let res = false;
        if (n % 2 == 0) res = true;
        return res;
    }
}

This is because I've tried to use a CASE statement and do WHILE RIGHT(id,1) % 2 = 0 THEN true (or even or whatever) and the % is not recognized in Dataform. So, after I create that function in JS, I tried to use it like this:

WITH cte1 AS (
    SELECT RIGHT(id, 1) AS last_digit
    FROM ${ref("step-01")}
)
SELECT 
    ${is_even(last_digit)}
FROM cte1

and the error is "last_digit is not defined".

I guess my conctrete question is... is there a way for me to do something like this? According to your last post I could, but unlike you (and what logic dictates) I cannot pass any parameter to a JS function that is not strictly a hard-coded string, digit or whatever.

Thanks again!

[kolban]

I'm doing this from a notepad rather than having tested it ... so apologies....

I'm imagining:

js {
  function is_even(columnName) {
    return `(MOD(${columnName}, 2) = 0)`
  }
}

WITH cte1 AS (
    SELECT RIGHT(id, 1) AS last_digit
    FROM ${ref("step-01")}
)
SELECT 
    ${is_even("last_digit")}
FROM cte1

When the Dataform compilation occurs, what it is doing is building the SQL statement(s) to be executed.  There is no place in Dataform where a SQL statement is executed and the results passed through JavaScript.