Using SQL inside bigquery UDF

Question

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

guillaume blaquiere · Accepted Answer · 2019-11-15 12:35:57Z

2

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

answered Nov 15, 2019 at 12:35

guillaume blaquiere

76.5k3 gold badges65 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

joe-shad Over a year ago

thanks for responding,I think this is a big limitation for a function in SQL environment, I don't think procedure would help me because I want to transform the data and view it in single query, is there anyway I can do that ?

guillaume blaquiere Over a year ago

You want to call this on each row of your query? Is a view can help you to factorize this "function" code?

joe-shad Over a year ago

I want to use it to decode a certain column, the operation include transform the string array into rows then join these rows with the dictionary table to get the codes description then return all the descriptions in one string , I can write a view and hardcode the issue for each column but needed to create a function as it's better

Collectives™ on Stack Overflow

Using SQL inside bigquery UDF

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related