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.
Syntax
IDENTIFIER ( strExpr )
Parameters
- strExpr: A constant
STRING
expression typically including one or more parameter markers.
Examples
Scala
// 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"))
SQL
-- 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
Related
Σχόλια
https://aka.ms/ContentUserFeedback.
Σύντομα διαθέσιμα: Καθ' όλη τη διάρκεια του 2024 θα καταργήσουμε σταδιακά τα ζητήματα GitHub ως μηχανισμό ανάδρασης για το περιεχόμενο και θα το αντικαταστήσουμε με ένα νέο σύστημα ανάδρασης. Για περισσότερες πληροφορίες, ανατρέξτε στο θέμα:Υποβολή και προβολή σχολίων για