CREATE FUNCTION

Translation reference for the BigQuery User Defined Functions

BigQuery supports UDFs written in either SQL or Javascript

Syntax to create a SQL UDF

CREATE [ OR REPLACE ] [ TEMPORARY | TEMP ] FUNCTION [ IF NOT EXISTS ]
    [[project_name.]dataset_name.]function_name
    ([named_parameter[, ...]])
     ([named_parameter[, ...]])
  [RETURNS data_type]
  AS (sql_expression)
  [OPTIONS (function_option_list)]

named_parameter:
  param_name param_type

Code Samples

BigQuery

-- Sample 1 - IF NOT EXISTS and NO RETURNS CLAUSE
CREATE FUNCTION IF NOT EXISTS test.basic (x string)
AS (x);

-- Sample 2 - TEMPORARY
CREATE TEMPORARY FUNCTION temporary (param1 integer, param2 integer)
RETURNS INTEGER
AS (param1 * param2);

-- Sample 3 - OR REPLACE
CREATE OR REPLACE FUNCTION test.orReplace ()
RETURNS INTEGER
AS (select colX from tableY);

-- Sample 4 - OPTIONS
CREATE FUNCTION funcOptionsNoReturns (param1 integer)
AS (x * y)
OPTIONS(
    description="UDF with all supported options",
    library=["gs://my-bucket/lib1.js", "gs://my-bucket/lib2.js"],
    endpoint="https://www.snowflake.com/foo",
    user_defined_context=[("key1","value1"),("key2", "value2")],
    max_batching_rows=1000
);

Snowflake

Syntax to create Javascript UDF

CREATE [OR REPLACE] [TEMPORARY | TEMP] FUNCTION [IF NOT EXISTS]
    [[project_name.]dataset_name.]function_name
    ([named_parameter[, ...]])
  RETURNS data_type
  [determinism_specifier]
  LANGUAGE js
  [OPTIONS (function_option_list)]
  AS javascript_code

named_parameter:
  param_name param_type

determinism_specifier:
  { DETERMINISTIC | NOT DETERMINISTIC }

Code Samples

BigQuery

-- Sample 1 - No Deterministim Specifier
CREATE FUNCTION test.languageJs (x integer, y integer)
RETURNS integer
LANGUAGE js
AS "return x * y;";

-- Sample 2 - DETERMINISTIC
CREATE FUNCTION test.deterministicSpec (x string)
RETURNS STRING
DETERMINISTIC
LANGUAGE js
AS """return x;""";

-- Sample 3 - NOT DETERMINISTIC
CREATE OR REPLACE FUNCTION test.notDeterministic (x string)
RETURNS STRING
NOT DETERMINISTIC
LANGUAGE js
AS r"""
  return x;
""";

Snowflake

Syntax to create a remote function

CREATE [OR REPLACE] FUNCTION [IF NOT EXISTS]
    [[project_name.]dataset_name.]function_name
    ([named_parameter[, ...]])
  RETURNS data_type
  REMOTE WITH CONNECTION connection_path
  [OPTIONS (function_option_list)]

named_parameter:
  param_name param_type

The transformation for remote function is currently not being transformed to Snowflake.

Related EWIs

1. MSC-BQ0003: The Create Function Options clause is not supported by Snowflake.