CREATE TABLE FUNCTION

In this section, you will find information about BigQuery's CREATE TABLE FUNCTION and its Snowflake equivalent.

Grammar Syntax

CREATE [ OR REPLACE ] TABLE FUNCTION [ IF NOT EXISTS ]
  [[project_name.]dataset_name.]function_name
  ( [ function_parameter [, ...] ] )
  [RETURNS TABLE < column_declaration [, ...] > ]
  [OPTIONS (table_function_options_list) ]
  AS sql_query

function_parameter:
  parameter_name { data_type | ANY TYPE }

column_declaration:
  column_name data_type

Click here to go to the BigQuery specification for this syntax.

Overview

In general terms, the parts of BigQuery's CREATE TABLE FUNCTION that are not directly supported by Snowflake are few.

Notably, the RETURNS TABLE clause uses < and > in BigQuery, and is translated to ( and ) in Snowflake.

Most of the OPTIONS are removed, with the notable exception of the DESCRIPTION option, which is translated to a Snowflake's COMMENT clause.

Samples

Basic

For basic translation scenarios, the translation consists of removing the TABLE keyword, tweaking the RETURNS TABLE clause and adding TABLE(...) to every call to the function.

-- INPUT
CREATE OR REPLACE TABLE FUNCTION function_name_basic (parameter_name INTEGER)
RETURNS TABLE <col1 INTEGER, col2 INTEGER>
AS
  SELECT * FROM table_name t1;

SELECT * FROM function_name_basic(10);

-- OUTPUT
CREATE OR REPLACE FUNCTION function_name_basic (parameter_name INTEGER)
RETURNS TABLE (
  column_name INTEGER, column_name_2 INTEGER
)
AS
  $$
      SELECT * FROM
        table_name t1
  $$;

SELECT * FROM
  TABLE(function_name_basic(10));

DESCRIPTION option

The DESCRIPTION option is translated to a COMMENT clause in Snowflake.

-- INPUT
CREATE TABLE FUNCTION function_name_description (parameter_name INTEGER)
RETURNS TABLE <col1 INTEGER, col2 INTEGER>
OPTIONS (
  DESCRIPTION = "this_is_the_description"
)
AS
  SELECT * FROM table_name t1;

-- OUTPUT

CREATE FUNCTION function_name_description (parameter_name INTEGER)
RETURNS TABLE (
  col1 INTEGER, col2 INTEGER
)
COMMENT = 'this_is_the_description'
AS
  $$
      SELECT * FROM
        table_name t1
  $$;

No RETURNS clause

Snowflake requires that a RETURNS TABLE clause is present in every create table function statement. The types of the resulting table's columns have to be specified too.

BigQuery allows the definition of CREATE TABLE FUNCTION without a RETURNS TABLE clause, so SnowConvert infers the required resulting table's column names and types, and adds them to the translated function.

-- INPUT

CREATE OR REPLACE TABLE FUNCTION function_name_noreturns (parameter_name INTEGER)
AS
  SELECT t1.col1, t1.col2 FROM table_name t1;

-- OUTPUT

CREATE OR REPLACE FUNCTION function_name_noreturns (parameter_name INTEGER)
RETURNS TABLE (
  col1 INT,
  col2 INT
)
AS
  $$
      SELECT t1.col1, t1.col2 FROM
        table_name t1
  $$;

No RETURNS clause: Missing dependent object information

In some scenarios, SnowConvert may be unable to obtain the information about the resulting table's column types. In such scenarios, a VARIANT data type is used.

-- INPUT

CREATE OR REPLACE TABLE FUNCTION function_name_noreturns_missing_object (parameter_name INTEGER)
AS
  SELECT t1.col1, t1.col2 FROM missing_object t1;
  
CREATE OR REPLACE TABLE FUNCTION f1 ()
AS
  SELECT * FROM unknownTable1 t1 JOIN unknownTable2 t2 ON t1.col1 = t2.col1;

-- OUTPUT

--** MSC-WARNING - MSCEWI1050 - MISSING DEPENDENT OBJECT "missing_object" **
CREATE OR REPLACE FUNCTION function_name_noreturns_missing_object (parameter_name INTEGER)
RETURNS TABLE (
  col1 VARIANT,
  col2 VARIANT
)
AS
  $$
      SELECT t1.col1, t1.col2 FROM
        missing_object t1
  $$;
  
--** MSC-WARNING - MSCEWI1050 - MISSING DEPENDENT OBJECTS "unknownTable1", "unknownTable2" **
CREATE OR REPLACE FUNCTION f1 ()
----** MSC-INFORMATION - MSCINF0008 - UNABLE TO GENERATE CORRECT RETURNS TABLE CLAUSE DUE TO MISSING DEPENDENT OBJECT INFORMATION. **
--RETURNS TABLE (
--)
AS
  $$
      SELECT * FROM
        unknownTable1 t1 JOIN
          unknownTable2 t2 ON t1.col1 = t2.col1
  $$;

In the most extreme cases, when SnowConvert is unable to get any information about the query to properly generate the RETURNS TABLE clause, a relevant message is applied and the RETURNS TABLE clause is commented out.

-- OUTPUT

CREATE OR REPLACE FUNCTION function_name_noreturns_no_information (parameter_name INTEGER)
----** MSC-ERROR - MSC-BQ0014 - SNOWCONVERT WAS UNABLE TO GENERATE CORRECT RETURNS TABLE CLAUSE. **
--RETURNS TABLE (
--)
AS
  $$
      ...
      ...
      ...
  $$;

ANY TYPE parameter

BigQuery functions may have ANY TYPE parameters. These allow the function to receive any data type as parameter, often handling them carefully within their implementation.

Snowflake does not directly support ANY TYPE but, as a solution, SnowConvert translates these ANY TYPE parameters to VARIANT parameters, and casts the arguments that were previously passed as the ANY TYPE parameter to VARIANT using the TO_VARIANT function.

-- INPUT

CREATE OR REPLACE TABLE FUNCTION function_name_anytype (parameter_name ANY TYPE)
RETURNS TABLE <col1 INTEGER, col2 INTEGER>
AS
  SELECT * FROM table_name t1;

SELECT * FROM function_name_anytype(10);

-- OUTPUT

CREATE OR REPLACE FUNCTION function_name_anytype (parameter_name VARIANT)
RETURNS TABLE (
  col1 INTEGER, col2 INTEGER
)
AS
  $$
      SELECT * FROM
        table_name t1
  $$;

SELECT * FROM
  TABLE(function_name_anytype(TO_VARIANT(10)));

Known issues

1. Limitations

Although the ANY TYPE translation that SnowConvert provides does work for many cases, there could be scenarios where executing TO_VARIANT(...) on the function call arguments won't produce the expected result. If you suspect that your code's functionality is not being preserved accurately, please email us at [email protected].

Related EWIs

1. MSC-BQ0014: SnowConvert was unable to generate correct return table clause.

2. MSCINF0008: Unable to generate correct RETURNS TABLE clause due to missing dependent object information.