Using SQL Query to return value from BigQuery User Defined Function

Question

Can I use a query in Google BigQuery User Defined Function to return some value? I've been searching docs and stackoverflow for hours without any luck and I have a very specific use case where I need to return a single scalar value based on the values of multiple columns.

Following will be the use case for the query:

SELECT campaign,source,medium, get_channel(campaign,source,medium)
FROM table_name

the get_channel() UDF will use these parameters and a complex select statement to return a single scalar value for the row. I've prepared the query, I just need to find a way to use that query in the UDF, for which I, honestly am at loss and without a cause.

Is my use case correct? Is this even possible? Are there any alternatives to do this?

How is the documentation not helping here? cloud.google.com/bigquery/docs/reference/standard-sql/… — Martin Weitzmann
– Martin Weitzmann, Commented Apr 2, 2018 at 14:18
@martin I've visited these docs but it didn't hep me for my case, i.e, I couldn't find what i was looking for in there. — Faizan Mustafa Bhutto
– Faizan Mustafa Bhutto, Commented Apr 2, 2018 at 14:26

Mikhail Berlyant · Accepted Answer · 2018-04-02 14:28:08Z

6

Looks like you want to use UDF to select scalar value off of some lookup table. if so, NO - you cannot reference a table in UDF - see more in Limits and Limitations

But if you just want to have some complex manipulation with arguments - sure - see dummy example below

#standardSQL
CREATE TEMPORARY FUNCTION get_channel(campaign INT64, source INT64, medium INT64) AS ((
  SELECT campaign + source + medium as result_of_complex_select_statement
));
WITH `project.dataset.table_name` AS (
  SELECT 1 AS campaign, 2 AS source, 3 AS medium UNION ALL
  SELECT 4, 5, 6 UNION ALL
  SELECT 7, 8, 9
)
SELECT 
  campaign,
  source,
  medium, 
  get_channel(campaign,source,medium) AS channel
FROM `project.dataset.table_name`

You should rather use JOIN to achieve your goal

edited Apr 2, 2018 at 14:28

answered Apr 2, 2018 at 14:18

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

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

7 Comments

Faizan Mustafa Bhutto Over a year ago

Yes, i am trying to use these parameters to select a scalar value from a mapping table. I'll try your answer out when i am in office later tonight and update here if this works.

Faizan Mustafa Bhutto Over a year ago

Then this doesn't solve my problem :( I'll try fresh with a different logic then

Mikhail Berlyant Over a year ago

Yes, You should rather use JOIN to achieve your goal

Mikhail Berlyant Over a year ago

But, the question is definitely answered - you asked if you can do this or not - right? :o)

Faizan Mustafa Bhutto Over a year ago

Thanks @Mikhael, i initially DID try using joins but moved to using UDF because this was going to be a generic function used at multiple places. I thought UDFs in BigQuery will be somewhat same as UDFs in SQL Server. Well, back to storyboard then.

|

Collectives™ on Stack Overflow

Using SQL Query to return value from BigQuery User Defined Function

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related