CODEWITHSUNDEEP
google-cloud-platformuser-defined-functionsgoogle-bigquery
joe-shad
joe-shad

Reputation: 53

Using SQL inside bigquery UDF

I'm trying to solve a problem I'm facing in the data by creating a function that takes the codes from string array and get the codes descriptions from other table and then rejoin the results in single string

I want to create a function that generalise the solution for my problem, I didn't prefer to use temp function if you have other solutions:

CREATE TEMP FUNCTION
  TEST(ref_no INT64,
    string_array STRING,
    market_par STRING) AS ( (
    SELECT
      STRING_AGG(SUBSTR(descr,2,LENGTH(descr)-2),', ')
    FROM (
      SELECT
        json_EXTRACT(description,
          '$.EnglishDesc') descr
      FROM (
        SELECT
          CAST(n AS int64) n
        FROM (
          SELECT
            *
          FROM
            UNNEST(SPLIT(SUBSTR(string_array,2,LENGTH(string_array)-2))) AS n)) z
      JOIN
        `project.dataset.table` y
      ON
        (z.n = y.code
          AND y.typeid = ref_no
          AND y.market = market_par)) )) ;

so i'm converting the string array to nested columns then use unnest on it , finally i join the result with the dictionary table I'm getting the following error:

CREATE TEMPORARY FUNCTION statements must be followed by an actual query.

can my problem be solved and generalised to be used for other type of codes or I have to hard code my solution each time without using the UDF

Upvotes: 0

Views: 3211

Answers (1)

guillaume blaquiere
guillaume blaquiere

Reputation: 75715

You can't perform a query to another table in a SQL UDF. That's why, some weeks ago, Google has introduce, in Beta, scriptings and stored procedures in Bigquery.

I think that you want to achieve is can be done in a stored procedure, then you can call it with the desired parameters

Upvotes: 2

Related Questions