Spark SQL 指南

标识符子句

描述

将常量 STRING 表达式转换为 SQL 对象名称。此子句的目的是允许在 SQL 语句中对标识符进行模板化，同时避免 SQL 注入攻击的风险。通常，此子句与参数标记或变量一起用作参数。

语法

IDENTIFIER ( strExpr )

参数

返回

一个（限定的）标识符，可作为以下内容使用：

示例

Scala 示例

这些示例使用命名参数标记来模板化查询。

// Creation of a table using parameter marker.
spark.sql("CREATE TABLE IDENTIFIER(:mytab)(c1 INT)", args = Map("mytab" -> "tab1")).show()

spark.sql("DESCRIBE IDENTIFIER(:mytab)", args = Map("mytab" -> "tab1")).show()
+--------+---------+-------+
|col_name|data_type|comment|
+--------+---------+-------+
|      c1|      int|   NULL|
+--------+---------+-------+

// 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")).show()

spark.sql("DESCRIBE IDENTIFIER(:mytab)", args = Map("mytab" -> "default.tab1")).show()
+--------+---------+-------+
|col_name|data_type|comment|
+--------+---------+-------+
|      c1|      int|   NULL|
|      c2|      int|   NULL|
+--------+---------+-------+

// A parameterized reference to a table in a query. This table name is qualified and uses back-ticks.
spark.sql("SELECT * FROM IDENTIFIER(:mytab)", args = Map("mytab" -> "`default`.`tab1`")).show()
+---+---+
| c1| c2|
+---+---+
+---+---+


// You cannot qualify the IDENTIFIER clause or use it as a qualifier itself.
spark.sql("SELECT * FROM myschema.IDENTIFIER(:mytab)", args = Map("mytab" -> "`tab1`")).show()
[INVALID_SQL_SYNTAX.INVALID_TABLE_VALUED_FUNC_NAME] `myschema`.`IDENTIFIER`.

spark.sql("SELECT * FROM IDENTIFIER(:myschema).mytab", args = Map("mychema" -> "`default`")).show()
[PARSE_SYNTAX_ERROR]

// Dropping a table with separate schema and table parameters.
spark.sql("DROP TABLE IDENTIFIER(:myschema || '.' || :mytab)", args = Map("myschema" -> "default", "mytab" -> "tab1")).show()

// A parameterized column reference
spark.sql("SELECT IDENTIFIER(:col) FROM VALUES(1) AS T(c1)", args = Map("col" -> "t.c1")).show()
+---+
| c1|
+---+
|  1|
+---+

// Passing in a function name as a parameter
spark.sql("SELECT IDENTIFIER(:func)(-1)", args = Map("func" -> "abs")).show();
+-------+
|abs(-1)|
+-------+
|      1|
+-------+

SQL 示例

这些示例使用 SQL 变量来模板化查询。

DECLARE mytab = 'tab1';

-- Creation of a table using variable.
CREATE TABLE IDENTIFIER(mytab)(c1 INT);

DESCRIBE IDENTIFIER(mytab);
+--------+---------+-------+
|col_name|data_type|comment|
+--------+---------+-------+
|      c1|      int|   NULL|
+--------+---------+-------+

-- Altering a table with a fixed schema and a parameterized table name. 
ALTER TABLE IDENTIFIER('default.' || mytab) ADD COLUMN c2 INT;

SET VAR mytab = '`default`.`tab1`';
DESCRIBE IDENTIFIER(mytab);
+--------+---------+-------+
|col_name|data_type|comment|
+--------+---------+-------+
|      c1|      int|   NULL|
|      c2|      int|   NULL|
+--------+---------+-------+

-- A parameterized reference to a table in a query. This table name is qualified and uses back-ticks.
SELECT * FROM IDENTIFIER(mytab);
+---+---+
| c1| c2|
+---+---+
+---+---+


-- Dropping a table with separate schema and table parameters.
DECLARE myschema = 'default';
SET VAR mytab = 'tab1';
DROP TABLE IDENTIFIER(myschema || '.' || mytab);

-- A parameterized column reference
DECLARE col = 't.c1';
SELECT IDENTIFIER(col) FROM VALUES(1) AS T(c1);
+---+
| c1|
+---+
|  1|
+---+

-- Passing in a function name as a parameter
DECLARE func = 'abs';
SELECT IDENTIFIER(func)(-1);
+-------+
|abs(-1)|
+-------+
|      1|
+-------+