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

-- Sample 1 - IF NOT EXISTS and NO RETURNS CLAUSE
-- If Not Exists remains the same, adds returns clause with VARIANT data type,
-- and explicitly casts function body to VARIANT
CREATE FUNCTION IF NOT EXISTS test.basic (x string)
RETURNS VARIANT
AS
    $$
 (x) :: VARIANT
    $$;

-- 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)
RETURNS VARIANT
AS
    $$
 (x * y) :: VARIANT
    $$
----** MSC-ERROR - MSC-BQ0003 - MSC-ERROR - MSC-BQ0003 - OPTIONS CLAUSE NOT SUPPORTED IN CREATE FUNCTION. **
--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
--)
 ;

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

-- Sample 1 - No Deterministim Specifier
CREATE FUNCTION test.languageJs (x DOUBLE, y DOUBLE)
RETURNS DOUBLE
LANGUAGE JAVASCRIPT
AS
$$
 return x * y; 
$$;

-- Sample 2 - DETERMINISTIC
-- DETERMINISTIC is tranformed to Snowflake's IMMUTABLE
CREATE FUNCTION test.deterministicSpec (x string)
RETURNS STRING
LANGUAGE JAVASCRIPT
IMMUTABLE
AS
$$
 return x; 
$$;

-- Sample 3 - NOT DETERMINISTIC
-- NOT DETERMINISTIC is transformed to Snowflake's VOLATILE
CREATE OR REPLACE FUNCTION test.notDeterministic (x string)
RETURNS STRING
LANGUAGE JAVASCRIPT
VOLATILE
AS
$$ 
  return x;
$$;

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.