December 2017
Beginner to intermediate
264 pages
5h 38m
English
Content preview from Learning Google BigQuery
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
Start your free trial
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:
#standardSQLCREATE TEMPORARY FUNCTION GetOtherCharges(tipamount FLOAT64, tollsamount FLOAT64)RETURNS INT64LANGUAGE js AS """ return tipamount + tollsamount;""";SELECT vendor_id, GetOtherCharges( tip_amount, ...