User-defined functions

User-defined functions can be written in JavaScript or SQL in BigQuery. These functions can be called in queries to obtain results. The following are the supported datatypes that can be passed to and returned by the functions:

• ARRAY
• BOOL
• BYTES
• DATE
• FLOAT64
• STRING
• STRUCT
• TIMESTAMP

The following is a simple function written in JavaScript to return the sum of two numbers, and it is used in the query. This query passes the tip_amount and tolls_amount values for each row from the table to the function and gets the sum:

#standardSQL
CREATE TEMPORARY FUNCTION GetOtherCharges(tipamount FLOAT64, tollsamount FLOAT64)
RETURNS INT64
LANGUAGE js AS """
  return tipamount + tollsamount;
""";

SELECT vendor_id, GetOtherCharges( tip_amount, tolls_amount )
FROM `bigquery-public-data.new_york.tlc_green_trips_2013`

Note

Custom external JavaScript libraries can also be referenced to in user-defined functions. These files can be uploaded to Google Cloud Storage and then used in the queries as shown...