Table Literals/Functions

In this Topic:

Table Literals

Table literals are used to pass the name of a table or a placeholder value (instead of a table name) to a query. Table literals appear in the FROM clause of a SQL statement and consist of either the table name, or a SQL variable or API bind variable in place of the table name.

Table Literal Syntax

TABLE( <table_name> | <session_variable> | <bind_variable> )
table_name

An identifier for a table:

  • The identifier can be fully-qualified in the form of <db_name>.<schema_name>.<table_name> or <schema_name>.<table_name>.

  • Double-quotes are supported for individual object identifiers that are case-sensitive or contain spaces and special characters.

  • The entire identifer string must be enclosed in single quotes or $$. For example:

    'mytable' or $$mytable$$
    'mydb.myschema.mytable' or $$mydb.myschema.mytable$$
    '"DB 1"."Schema 1".mytable' or $$"DB 1"."Schema 1".mytable$$
session_variable
A SQL variable that has been set for the session.
bind_variable
A bind variable, in the standard form of ? or :<name>, for use with APIs that support bindings (Java, Python, etc.).

Table Literal Usage Notes

  • Table literals are supported in the FROM clause only.
  • When a bind variable is used to prepare a statement, table metadata is not available after preparing the statement.

Table Literal Examples

Query the table mytable using a table literal (note that the following two examples are syntactically equivalent):

SELECT * FROM TABLE('mytable');

SELECT * FROM TABLE($$mytable$$);

Query the table mytable in the schema myschema and the database mydb using a table literal (note that the following two examples are syntactically equivalent):

SELECT * FROM TABLE('mydb."myschema"."mytable"');

SELECT * FROM TABLE($$mydb."myschema"."mytable"$$);

Set a session variable that references a table name and then query the table using the variable passed as a table literal:

SET myvar = 'mytable';

SELECT * FROM TABLE($myvar);

Prepare a statement with a binding that represents a table (note that the following two examples are syntactically equivalent):

SELECT * FROM TABLE(?);

SELECT * FROM TABLE(:binding);

Table Functions

Table functions return a set of rows instead of a single scalar value. Table functions appear in the FROM clause of a SQL statement and cannot be used as a replacement for scalar functions. Table functions take scalar expressions as input.

Note

You can also define table UDFs that return rows from a SQL query. For more information, see User-Defined Functions.

List of System-Defined Table Functions

Snowflake provides the following system-defined table functions:

Sub-category Function Notes
Data Loading VALIDATE For more information, see Data Loading.
Data Generation GENERATOR  
Semi-structured Queries FLATTEN For more information, see Querying Semi-structured Data.
Query Results RESULT_SCAN Can be used to perform SQL operations on the output from another SQL operation (e.g. SHOW).
Historical Information: These table functions are part of the Snowflake Information Schema. For more information, see Information Schema.
Queries
QUERY_HISTORY , QUERY_HISTORY_BY_*
Warehouse & Storage Usage
DATABASE_STORAGE_USAGE_HISTORY
WAREHOUSE_LOAD_HISTORY
WAREHOUSE_METERING_HISTORY
STAGE_STORAGE_USAGE_HISTORY
User Login
LOGIN_HISTORY , LOGIN_HISTORY_BY_USER

General Table Function Syntax

SELECT ... FROM <input_table> T, [ LATERAL ] TABLE( <table_function>( T.<col1> , ... , <argN> ) ) F;

For function-specific syntax, see the documentation for the individual system-defined table functions.

Table Function Usage Notes

  • Table functions can also be applied to a set of rows using the LATERAL construct.
  • To enable using table expressions, Snowflake supports ANSI/ISO standard syntax for table expressions in the FROM clause of SELECT queries and subqueries. It is used to indicate that an expression returns a collection of rows instead of a single row.
  • This ANSI/ISO syntax is valid only in the FROM clause of the SELECT list. You cannot omit these keywords and parentheses from a collection subquery specification in any other context.