CREATE TABLE FUNCTION
In this section, you will find information about BigQuery's CREATE TABLE FUNCTION and its Snowflake equivalent.
Grammar Syntax
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.
DESCRIPTION option
The DESCRIPTION
option is translated to a COMMENT
clause in Snowflake.
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.
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.
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.
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.
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 snowconvert-support@snowflake.com.
Related EWIs
MSC-BQ0014: SnowConvert was unable to generate correct return table clause.
MSCINF0008: Unable to generate correct RETURNS TABLE clause due to missing dependent object information.
Last updated