Session Variables as Identifiers

Question

I'm having some trouble with using session variables as identifiers in Snowflake. I have reviewed the docs:

https://docs.snowflake.com/en/sql-reference/session-variables.html

https://docs.snowflake.com/en/sql-reference/identifier-literal.html

https://docs.snowflake.com/en/sql-reference/data-types-text.html

https://docs.snowflake.com/en/sql-reference/identifiers-syntax.html

I am able to get their examples to work, but what seems to me like a logical application of the concepts presented does not work. Here's the example that works (set your db and schema to a sandbox first):

input:

set (table_n, column_n) = ('test', 'c1');
select $table_n, $column_n;

output:

$TABLE_N    $COLUMN_N
test        c1

input:

create or replace table identifier($table_n) (c1 number);
insert into identifier($table_n) (c1) values (1), (2), (3);
select identifier($column_n) from identifier($table_n);

output:

So far so good. That was all lifted right from the docs. However, when I try to use identifiers in slightly different contexts the operation fails with an unhelpful error message.

input:

insert into identifier($table_n) (identifier($column_n)) values (5), (6), (7);

output:

SQL compilation error: syntax error line 1 at position 44 unexpected '('. syntax error line 1 at position 45 unexpected '$column_n'. syntax error line 1 at position 55 unexpected ')'.

input:

create or replace table identifier($table_n) (identifier($column_n) number);

output:

SQL compilation error: syntax error line 1 at position 56 unexpected '('. syntax error line 1 at position 74 unexpected ')'.

I am also getting errors when there is a single quote at the beginning or end of the identifier. If we return to the first example which works, and just change the column name to be "'c1'" it fails.

input:

set (table_n, column_n) = ('test', '\'c1\'');
select $table_n, $column_n;

output:

$TABLE_N    $COLUMN_N
test        'c1'

input:

create or replace table identifier($table_n) ("'c1'" number);
insert into identifier($table_n) ("'c1'") values (1), (2), (3);

output:

number of rows inserted
3

input:

select identifier($column_n) from identifier($table_n);

output:

SQL compilation error: error line 1 at position 18 invalid identifier '$column_n'

Any help is greatly appreciated. I would really like to understand what is going on behind the scenes so I really understand how to use Snowflake. Thank you!

user_12127815 · Answer

For your first SQL Error, I was able to insert the values into the table without having to specify the column (c1 in this case) with this statement below:

INSERT INTO identifier($table_n)  VALUES (5), (6), (7);

This seems though because C1 defaulted to indexing numerically: where the select output is then:

However for the create or replace identifier(variable) identifier(variable)...; seems to be causing issues. Two variables, seperated by commas in Select statements are not throwing errors.

I did find this note for SQL Identifiers:

"Identifiers (database names, table names, column names, etc.) cannot start with special characters unless the entire name is enclosed in double quotes. For more information, see Object Identifiers." from https://docs.snowflake.com/en/sql-reference/session-variables.html#using-variables-in-sql

Drafted conclusion: (variable variable) can only be done in session variables, not SQL variables(for example in the JDBC API https://docs.snowflake.com/en/sql-reference/session-variables.html#setting-variables-on-connection)

Thoughts?

Added: In addition the last select statement did not give expected results when I changed it to

select $column_n from identifier($table_n);

Session Variables as Identifiers

1 Answers