IDENTIFIER clause
Applies to: Databricks SQL Databricks Runtime 13.3 LTS and above
The IDENTIFIER clause interprets a constant string as a:
table or view name
function name
column name
field name
schema name
The clause enables SQL injection safe parameterization of SQL statements.
The IDENTIFIER clause is supported for the following statements:
Table, view, or function name of a CREATE, ALTER, DROP, UNDROP
Table name of a MERGE, UPDATE, DELETE, INSERT, COPY INTO
Target of a SHOW or DESCRIBE
USE of a schema
A function invocation
A column or view referenced in a query. This includes queries embedded in a DDL or DML statement.
When using the identifier clause it may not be embedded within an identifier.
Parameters
strExpr: A constant
STRING
expression typically including one or more parameter markers.
Examples
// Creation of a table using parameter marker.
spark.sql("CREATE TABLE IDENTIFIER(:mytab)(c1 INT)", args = Map("mytab" -> "tab1"))
// Altering a table with a fixed schema and a parameterized table name.
spark.sql("ALTER TABLE IDENTIFIER('default.' || :mytab) ADD COLUMN c2 INT)", args = Map("mytab" -> "tab1"))
// Dropping a table with separate schema and table parameters.
spark.sql("DROP TABLE IDENTIFIER(:myschema || '.' || :mytab)", args = Map("mySchema" -> "default", "mytab" -> "tab1"))
// A parameterized reference to a table in a query. The table name is qualified and uses back-ticks.
spark.sql("SELECT * FROM IDENTIFIER(:mytab)", args = Map("mytab" -> "`default`.`tab1`"))
// You cannot qualify the IDENTIFIER claue or use it as a qualifier itself.
spark.sql("SELECT * FROM myschema.IDENTIFIER(:mytab)", args = Map("mytab" -> "`tab1`"))
spark.sql("SELECT * FROM IDENTIFIER(:myschema).mytab", args = Map("mychema" -> "`default`"))
// A parameterized column reference
spark.sql("SELECT IDENTIFIER(:col) FROM VALUES(1) AS T(c1)", args = Map("col" -> "t.c1"))
// Passing in an aggregate function name as a parameter
spark.sql("SELECT IDENTIFIER(:agg)(c1) FROM VALUES(1), (2) AS T(c1)", args = Map("agg" -> "max"))
-- Creation of a table using variable.
> DECLARE mytab = 'tab1';
> CREATE TABLE IDENTIFIER(mytab)(c1 INT);
-- Altering a table with a fixed schema and a parameterized table name.
> ALTER TABLE IDENTIFIER('default.' || mytab) ADD COLUMN c2 INT;
-- Inserting using a parameterized table name. The table name is qualified and uses back-ticks.
> SET VAR mytab = '`default`.`tab1`';
> INSERT INTO IDENTIFIER(mytab) VALUES(1, 2);
-- A parameterized reference to a table in a query.
> SELECT * FROM IDENTIFIER(mytab);
1 2
-- Dropping a table with separate schema and table parameters.
> DECLARE myschema = 'default';
> SET VAR mytab = 'tab1';
> DROP TABLE IDENTIFIER(myschema || '.' || mytab);
-- You cannot qualify the IDENTIFIER claue or use it as a qualifier itself.
> SELECT * FROM myschema.IDENTIFIER('tab');
Error: PARSE_SYNTAX_ERROR
> SELECT * FROM IDENTIFIER('default').mytab;
Error: PARSE_SYNTAX_ERROR
-- A parameterized column reference
> DECLARE col = 't.c1';
> SELECT IDENTIFIER(col) FROM VALUES(1) AS T(c1);
1
-- Passing in an aggregate function name as a parameter
> DECLARE agg = 'max';
> SELECT IDENTIFIER(agg)(c1) FROM VALUES(1), (2) AS T(c1);
2