CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquery
Pratap Singh
Pratap Singh

Reputation: 429

Do we have SQL Server like User Defined Functions in Google Big Query?

We have been using Big query extensively for sometime in our company. We had been writing some reusable utility logic(say computing distance in miles between two geo-coordinates) in our Queries. Found 'User Defined Function(UDF)' in Big Query that is something near my requirement but the problem with UDFs is that they are not reusable and TEMPORARY in nature as they cannot be persisted at one place, and referred across the queries. UDFs need to be duplicated per query to be referred or they are only locally referable to a query.

I am looking for something very similar to SQL Server/Oracle User Defined Functions that can be created, managed and referred.

Do we have some graceful solution for this, in Big Query as of today?

Upvotes: 0

Views: 207

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33765

BigQuery supports persistent user-defined functions. To get started, see the documentation.

For example, here's a CREATE FUNCTION statement that creates a function to compute the median of an array:

CREATE FUNCTION dataset.median(arr ANY TYPE) AS (
  (
    SELECT
      IF(
        MOD(ARRAY_LENGTH(arr), 2) = 0,
        (arr[OFFSET(DIV(ARRAY_LENGTH(arr), 2) - 1)] + arr[OFFSET(DIV(ARRAY_LENGTH(arr), 2))]) / 2,
        arr[OFFSET(DIV(ARRAY_LENGTH(arr), 2))]
      )
    FROM (SELECT ARRAY_AGG(x ORDER BY x) AS arr FROM UNNEST(arr) AS x)
  )
);

After executing this statement, you can reference it in a follow-up query:

SELECT dataset.median([7, 1, 2, 10]) AS median;

Upvotes: 1

Related Questions