User-defined functions

A user-defined function (UDF) lets you create a function by using a SQLexpression or JavaScript code. A UDF accepts columns of input, performs actionson the input, and returns the result of those actions as a value.

You can define UDFs as either persistent or temporary. You can reusepersistent UDFs across multiple queries, while temporary UDFs only exist in thescope of a single query.

To create a UDF, use theCREATE FUNCTIONstatement. To delete a persistent user-defined function, use theDROP FUNCTIONstatement. Temporary UDFs expire as soon as the query finishes. TheDROPFUNCTION statement is only supported for temporary UDFs inmulti-statement queries andprocedures.

For information on UDFs in legacy SQL, seeUser-defined functions in legacy SQL.

SQL UDFs

The following example creates a temporary SQL UDF namedAddFourAndDivide andcalls the UDF from within aSELECT statement:

CREATETEMPFUNCTIONAddFourAndDivide(xINT64,yINT64)RETURNSFLOAT64AS((x+4)/y);SELECTval,AddFourAndDivide(val,2)FROMUNNEST([2,3,5,8])ASval;

This example produces the following output:

+-----+-----+|val|f0_|+-----+-----+|2|3.0||3|3.5||5|4.5||8|6.0|+-----+-----+

The next example creates the same function as a persistent UDF:

CREATEFUNCTIONmydataset.AddFourAndDivide(xINT64,yINT64)RETURNSFLOAT64AS((x+4)/y);

Because this UDF is persistent, you must specify a dataset for the function(mydataset in this example). After you run theCREATE FUNCTION statement,you can call the function from a query:

SELECTval,mydataset.AddFourAndDivide(val,2)FROMUNNEST([2,3,5,8,12])ASval;

Templated SQL UDF parameters

A parameter with a type equal toANY TYPE can match more than one argumenttype when the function is called.

• If more than one parameter has typeANY TYPE, then BigQuery doesn'tenforce any type relationship between these arguments.
• The function return type cannot beANY TYPE. It must be either omitted,which means to be automatically determined based onsql_expression,or an explicit type.
• Passing the function arguments of types that are incompatible with thefunction definition results in an error at call time.

The following example shows a SQL UDF that uses a templated parameter.

CREATETEMPFUNCTIONaddFourAndDivideAny(xANYTYPE,yANYTYPE)AS((x+4)/y);SELECTaddFourAndDivideAny(3,4)ASinteger_input,addFourAndDivideAny(1.59,3.14)ASfloating_point_input;

This example produces the following output:

+----------------+-----------------------+|integer_input|floating_point_input|+----------------+-----------------------+|1.75|1.7802547770700636|+----------------+-----------------------+

The next example uses a templated parameter to return the last element of anarray of any type:

CREATETEMPFUNCTIONlastArrayElement(arrANYTYPE)AS(arr[ORDINAL(ARRAY_LENGTH(arr))]);SELECTlastArrayElement(x)ASlast_elementFROM(SELECT[2,3,5,8,13]ASx);

This example produces the following output:

+--------------+|last_element|+--------------+|13|+--------------+

Scalar subqueries

A SQL UDF can return the value of ascalar subquery.A scalar subquery must select a single column.

The following example shows a SQL UDF that uses a scalar subquery to count thenumber of users with a given age in a user table:

CREATETEMPTABLEusersAS(SELECT1ASid,10ASageUNIONALLSELECT2ASid,30ASageUNIONALLSELECT3ASid,10ASage);CREATETEMPFUNCTIONcountUserByAge(userAgeINT64)AS((SELECTCOUNT(1)FROMusersWHEREage=userAge));SELECTcountUserByAge(10)AScount_user_age_10,countUserByAge(20)AScount_user_age_20,countUserByAge(30)AScount_user_age_30;

This example produces the following output:

+-------------------+-------------------+-------------------+|count_user_age_10|count_user_age_20|count_user_age_30|+-------------------+-------------------+-------------------+|2|0|1|+-------------------+-------------------+-------------------+

Default project in SQL expressions

In the body of a SQL UDF, any references to BigQuery entities,such as tables or views, must include the project ID, unless the entity residesin the same project that contains the UDF.

For example, consider the following statement:

CREATEFUNCTIONproject1.mydataset.myfunction()AS((SELECTCOUNT(*)FROMmydataset.mytable));

If you run this statement fromproject1 andmydataset.mytable exists inproject1, then the statement succeeds. However, if you run this statementfrom a different project, then the statement fails. To correct the error,include the project ID in the table reference:

CREATEFUNCTIONproject1.mydataset.myfunction()AS((SELECTCOUNT(*)FROMproject1.mydataset.mytable));

You can also reference an entity in a different project or dataset from theone where you create the function:

CREATEFUNCTIONproject1.mydataset.myfunction()AS((SELECTCOUNT(*)FROMproject2.another_dataset.another_table));

JavaScript UDFs

A JavaScript UDF lets you call code written in JavaScript from a SQL query.JavaScript UDFs typically consume more slot resources as compared to standard SQLqueries, decreasing job performance. If the function can beexpressed in SQL, it is often more optimal to run the code as a standardSQL query job.

The following example shows a JavaScript UDF. The JavaScript code is quotedwithin araw string.

CREATETEMPFUNCTIONmultiplyInputs(xFLOAT64,yFLOAT64)RETURNSFLOAT64LANGUAGEjsASr"""  return x*y;""";WITHnumbersAS(SELECT1ASx,5asyUNIONALLSELECT2ASx,10asyUNIONALLSELECT3asx,15asy)SELECTx,y,multiplyInputs(x,y)ASproductFROMnumbers;

This example produces the following output:

+-----+-----+--------------+|x|y|product|+-----+-----+--------------+|1|5|5||2|10|20||3|15|45|+-----+-----+--------------+

The next example sums the values of all fields namedfoo in the given JSON string.

CREATETEMPFUNCTIONSumFieldsNamedFoo(json_rowSTRING)RETURNSFLOAT64LANGUAGEjsASr"""  function SumFoo(obj) {    var sum = 0;    for (var field in obj) {      if (obj.hasOwnProperty(field) && obj[field] != null) {        if (typeof obj[field] == "object") {          sum += SumFoo(obj[field]);        } else if (field == "foo") {          sum += obj[field];        }      }    }    return sum;  }  var row = JSON.parse(json_row);  return SumFoo(row);""";WITHInputAS(SELECTSTRUCT(1ASfoo,2ASbar,STRUCT('foo'ASx,3.14ASfoo)ASbaz)ASs,10ASfooUNIONALLSELECTNULL,4ASfooUNIONALLSELECTSTRUCT(NULL,2ASbar,STRUCT('fizz'ASx,1.59ASfoo)ASbaz)ASs,NULLASfoo)SELECTTO_JSON_STRING(t)ASjson_row,SumFieldsNamedFoo(TO_JSON_STRING(t))ASfoo_sumFROMInputASt;

The example produces the following output:

+---------------------------------------------------------------------+---------+|json_row|foo_sum|+---------------------------------------------------------------------+---------+|{"s":{"foo":1,"bar":2,"baz":{"x":"foo","foo":3.14}},"foo":10}|14.14||{"s":null,"foo":4}|4||{"s":{"foo":null,"bar":2,"baz":{"x":"fizz","foo":1.59}},"foo":null}|1.59|+---------------------------------------------------------------------+---------+

Supported JavaScript UDF data types

Some SQL types have a direct mapping to JavaScript types, but others don't. BigQuery represents types in the following manner:

Because JavaScript does not support a 64-bit integer type,INT64 is unsupported as an input type for JavaScriptUDFs. Instead, useFLOAT64 to represent integervalues as a number, orSTRING to represent integervalues as a string.

BigQuery does supportINT64 as a return typein JavaScript UDFs. In this case, the JavaScript function body can return eithera JavaScript Number or a String. BigQuery then converts either ofthese types toINT64.

If the return value of the JavaScript UDF is aPromise, BigQuery waits for thePromiseuntilPromise is settled. If thePromise settles into afulfilled state, BigQuery returns its result. If thePromise settles into a rejected state, BigQuery returnsan error.

Quote rules

You must enclose JavaScript code in quotes. For one line code snippets,you can use a standard quoted string:

CREATETEMPFUNCTIONplusOne(xFLOAT64)RETURNSFLOAT64LANGUAGEjsAS"return x+1;";SELECTval,plusOne(val)ASresultFROMUNNEST([1,2,3,4,5])ASval;

This example produces the following output:

+-----------+-----------+|val|result|+-----------+-----------+|1|2.0||2|3.0||3|4.0||4|5.0||5|6.0|+-----------+-----------+

In cases where the snippet contains quotes, or consists of multiple lines, usetriple-quoted blocks:

CREATETEMPFUNCTIONcustomGreeting(aSTRING)RETURNSSTRINGLANGUAGEjsASr"""  var d = new Date();  if (d.getHours() < 12) {    return 'Good Morning, ' + a + '!';  } else {    return 'Good Evening, ' + a + '!';  }""";SELECTcustomGreeting(names)ASeveryoneFROMUNNEST(['Hannah','Max','Jakob'])ASnames;

This example produces the following output:

+-----------------------+| everyone              |+-----------------------+| Good Morning, Hannah! || Good Morning, Max!    || Good Morning, Jakob!  |+-----------------------+

Include JavaScript libraries

You can extend your JavaScript UDFs using theOPTIONS section.This section lets you specify external code libraries for the UDF.

CREATETEMPFUNCTIONmyFunc(aFLOAT64,bSTRING)RETURNSSTRINGLANGUAGEjsOPTIONS(library=['gs://my-bucket/path/to/lib1.js','gs://my-bucket/path/to/lib2.js'])ASr"""  // Assumes 'doInterestingStuff' is defined in one of the library files.  return doInterestingStuff(a, b);""";SELECTmyFunc(3.14,'foo');

In the preceding example, code inlib1.js andlib2.jsis available to any code in the[external_code] section of the UDF.

Best practices for JavaScript UDFs

Prefilter your input

If your input can be filtered down before being passed to a JavaScriptUDF, your query might be faster and cheaper.

Avoid persistent mutable state

Don't store or access mutable state across JavaScript UDF calls. For example, avoid the following pattern:

-- Avoid this patternCREATEFUNCTIONtemp.mutable()RETURNSINT64LANGUAGEjsASr"""  var i = 0; // Mutable state  function dontDoThis() {    return ++i;  }  return dontDoThis()""";

Use memory efficiently

The JavaScript processing environment has limited memory available per query.JavaScript UDF queries that accumulate too much local state might fail due tomemory exhaustion.

Authorize routines

You can authorize UDFs asroutines.Authorized routines let you share query results with specific users or groupswithout giving them access to the underlying tables that generated the results.For example, an authorized routine can compute an aggregationover data or look up a table value and use that value in a computation.For more information, seeAuthorized routines.

Add descriptions to UDFs

To add a description to a UDF, follow these steps:

Create custom masking routines

You can create UDFs for use withcustom masking routines.You should create dedicated datasets and set up proper IAMpermissions for managing masking UDFs.Custom masking routines must meet the following requirements:

• The custom masking routine must be a SQL UDF.
• In the functionOPTIONS, thedata_governance_type option must be set toDATA_MASKING.
• Custom masking routines support the following functions:
  • AEAD.DECRYPT_BYTES AEAD encryption function withKEYS.KEYSET_CHAIN (raw key usage not supported)
  • AEAD.DECRYPT_STRING, AEAD encryption function withKEYS.KEYSET_CHAIN (raw key usage not supported)
  • AEAD.ENCRYPT AEAD encryption function with keyset_chain (raw key usage not supported)
  • CASTconversion function
  • CONCATstring function
  • CURRENT_DATETIMEdatetime function
  • CURRENT_DATEdate function
  • CURRENT_TIMESTAMPtimestamp function
  • CURRENT_TIMEtime function
  • DETERMINISTIC_DECRYPT_BYTES AEAD encryption function withKEYS.KEYSET_CHAIN (raw key usage not supported)
  • DETERMINISTIC_DECRYPT_STRING AEAD encryption function withKEYS.KEYSET_CHAIN (raw key usage not supported)
  • DETERMINISTIC_ENCRYPT AEAD encryption function withKEYS.KEYSET_CHAIN (raw key usage not supported)
  • FARM_FINGERPRINThash function
  • FROM_BASE32string function
  • FROM_BASE64string function
  • FROM_HEXstring function
  • GENERATE_UUIDutility function
  • KEYS.KEYSET_CHAIN AEAD encryption function
  • LENGTH string function
  • MD5hash function
  • REGEXP_REPLACEstring function
  • REGEX_EXTRACTstring function
  • REPLACEstring function
  • SAFE_CAST conversion function
  • SHA1hash function
  • SHA256hash function
  • SHA512hash function
  • STARTS_WITH string function
  • SUBSTRINGstring function
  • SUBSTR string function
  • TO_BASE32string function
  • TO_BASE64string function
  • TO_HEXstring function
• Custom masking routines can accept either no inputs or one input withinBigQuery data types,with the exception ofGEOGRAPHY andSTRUCT.GEOGRAPHY andSTRUCT arenot supported for custom masking routines.
• Templated SQL UDF parametersare not supported.
• When an input is provided, the input and output data types must be the same.
• An output type must be provided.
• No other UDFs, subqueries, tables, or views can be referenced in thedefinition body.
• After creating a masking routine, the routine cannot be changed to astandard function. This means that if thedata_governance_type option isset toDATA_MASKING, then you cannot changedata_governance_type usingDDL statements or API calls.
• Custom masking routines supportCASE andCASE expr statement. Following operators can be used withCASE andCASE expr statements:
  • Comparison Operators -<,<=,>,>=,=,!=,IN
  • Logical Operators -AND,OR,NOT
  • IS Operator

For example, a masking routine that replaces a user's social security numberwithXXX-XX-XXXX might look as follows:

CREATEORREPLACEFUNCTIONSSN_Mask(ssnSTRING)RETURNSSTRINGOPTIONS(data_governance_type="DATA_MASKING")AS(SAFE.REGEXP_REPLACE(ssn,'[0-9]','X')# 123-45-6789 -> XXX-XX-XXXX);

The following example hashes with user providedsalt, using theSHA256function:

CREATEORREPLACEFUNCTION`project.dataset.masking_routine1`(ssnSTRING)RETURNSSTRINGOPTIONS(data_governance_type='DATA_MASKING')AS(CAST(SHA256(CONCAT(ssn,'salt'))ASSTRINGformat'HEX'));

The following example masks aDATETIME column with a constant value:

CREATEORREPLACEFUNCTION`project.dataset.masking_routine2`(columnDATETIME)RETURNSDATETIMEOPTIONS(data_governance_type='DATA_MASKING')AS(SAFE_CAST('2023-09-07'ASDATETIME));

As a best practise, use theSAFEprefix wherever possible to avoid exposing raw data through error messages.

After you create the custom masking routine, it's available as a masking rule inCreate data policies.

Community-contributed functions

Community contributed UDFs are available in thebigquery-public-data.persistent_udfs public dataset and the open sourcebigquery-utils GitHub repository.You can see all thecommunity UDFs in the Google Cloud console bystarringthebigquery-public-data project in theExplorer pane, and thenexpanding the nestedpersistent_udfs dataset within that project.

Allow access to community-contributed functions within a VPC Service Controls perimeter

For projects where VPC Service Controls is enabled and BigQuery is a protected service, you must define an egress rule to thebigquery-public-data project (Project ID: 1057666841514).

This rule must enable the following operations:

• bigquery.routines.get (for using routines)
• bigquery.tables.getData (for querying BigQuery tables)

The following code shows an example YAML config:

-egressFrom:identityType:ANY_IDENTITYegressTo:operations:-serviceName:'bigquery.googleapis.com'methodSelectors:-permission:'bigquery.routines.get'-permission:'bigquery.tables.getData'resources:-projects/1057666841514# bigquery-public-data

If you want to contribute to the UDFs in this repository, seeContributing UDFs for instructions.

Unified access to routines across multiple regions

To use UDFs in queries across multiple regions, the UDF must be available in every region where a query containing the UDF is run. Therefore, you should create and maintain UDFs in any region where you might use the UDF in a query. Even if your tables are identical, you must maintain 2 versions of the function. For example, if you store your sales data in both theEU andUS multi-regions, then you should maintain a version of the function in each region. For example:

A query inEU multi-region:

SELECTid,europe_dataset.my_function(value)FROMsales;

A query inUS multi-region:

SELECTid,us_dataset.my_function(value)FROMsales;

Additionally, when the definition of the function changes, you must update it in all regions.

To make your UDFs region-independent, you can usecross-region dataset replication:

1. Create a new dedicated dataset, for examplemy_utils, to store all your necessary UDFs. Remember that any tables added to this dataset will be replicated, which will increase the cost. However, replicating UDFs and procedures does not incur any additional cost.
2. Add all your UDF to the new dataset. This can also include community UDFs such asbqutil copied fromGitHub.
3. Enable dataset replication. Configure this dataset to be replicated to all of the regions where you need to execute queries that call these UDFs. This will copy your functions to these regions and keep them synchronized.

When you run a query, BigQuery automatically uses the local version of the UDF from the local dataset replica without your specifying the region where the function is defined, making your queries portable across different locations.For example:

SELECTid,my_utils.my_function(value)FROMsales;

Limitations

The following limitations apply to temporary and persistent user-defined functions:

• The DOM objectsWindow,Document, andNode, and functions that require them, are not supported.
• JavaScript functions operate within a sandboxed environment, and those functions that rely on underlying system code might fail due to restricted system calls.
• A JavaScript UDF can time out and prevent your query from completing. Timeoutscan be as short as 5 minutes, but can vary depending on several factors,including how much user CPU time your function consumes and how large yourinputs and outputs to the JavaScript function are.
• Bitwise operations in JavaScript handle only the most significant 32 bits.
• UDFs are subject to certain rate limits and quota limits. For more information,seeUDF limits.

The following limitations apply to persistent user-defined functions:

• Each dataset can only contain one persistent UDF with the same name. However, you can create a UDF whose name is the same as the name of a table in the same dataset.
• When referencing a persistent UDF from another persistent UDF or a logical view, you must qualify the name with the dataset. For example:
  CREATE FUNCTION mydataset.referringFunction() AS (mydataset.referencedFunction());

The following limitations apply to temporary user-defined functions.

• When creating a temporary UDF,function_name cannot contain periods.
• Views and persistent UDFs cannot reference temporary UDFs.