BigQuery UDF: A match made in heaven!

Leave a Comment / BigQuery / By

BigQuery supports User Defined Functions written in SQL or Javascript.

This is such a nice feature since you are effectively freed from SQL limitations. Or if a built in function doesn’t exist or isn’t serving your purposes, you can simply create your own using Javascript and use it inside of your SQL queries or views!

(Bear in mind, you won’t be able to use a temporary function and save it inside of a view. Instead, you can simply create it as a persistent function and then reference it inside of your views!)

Also, while sometimes you can achieve an operation in SQL, it is often so much more performant and elegant in Javascript!

Let’s look at an example of performing some Camel Case text formatting:

SQL:
CREATE FUNCTION [dbo].[CamelCase]
(@Str varchar(8000))
RETURNS varchar(8000) AS
BEGIN
  DECLARE @Result varchar(2000)
  SET @Str = LOWER(@Str) + ' '
  SET @Result = ''
  WHILE 1=1
  BEGIN
    IF PATINDEX('% %',@Str) = 0 BREAK
    SET @Result = @Result + UPPER(Left(@Str,1))+
    SubString  (@Str,2,CharIndex(' ',@Str)-1)
    SET @Str = SubString(@Str,
      CharIndex(' ',@Str)+1,Len(@Str))
  END
  SET @Result = Left(@Result,Len(@Result))
  RETURN @Result
END
Javascript:
function toCamelCase(phrase) {
    var out = “”;
    try {
        phrase.split(” “).forEach(function (el, idx) {
            var add = el.toLowerCase();
            out += (add[0].toUpperCase() + add.slice(1) + ” “);
        });
        return out;
    }
    catch {
        return phrase;
    }
}
 

Leave a Comment