IDENTIFIER 子句
适用于: Databricks SQL Databricks Runtime 13.3 LTS 及更高版本
IDENTIFIER 子句将常量字符串解释为:
- 表或视图名称
- 函数名称
- 列名称
- 字段名称
- 架构名称
子句支持 SQL 语句的 SQL 注入安全参数化。
以下语句支持 IDENTIFIER 子句:
- CREATE、ALTER、DROP、UNDROP 的表、视图或函数名称
- MERGE、UPDATE、DELETE、INSERT、COPY INTO 的表名
- SHOW 或 DESCRIBE 的目标
- 使用架构
- 函数调用
- 查询中引用的列或视图。 这包括 DDL 或 DML 语句中嵌入的查询。
使用 identifier 子句时,它可能不会嵌入在标识符中。
语法
IDENTIFIER ( strExpr )
参数
- strExpr:一个常数
STRING
表达式,通常包含一个或多个参数标记。
示例
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