You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by we...@apache.org on 2023/08/21 03:44:01 UTC
[spark] branch master updated: [SPARK-43205][DOCS][SQL][FOLLOWUP] IDENTIFIER clause docs
This is an automated email from the ASF dual-hosted git repository.
wenchen pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push:
new f351e7b4edd [SPARK-43205][DOCS][SQL][FOLLOWUP] IDENTIFIER clause docs
f351e7b4edd is described below
commit f351e7b4eddb9cb56f1ae7f4cfc9809ef87c56b1
Author: srielau <se...@rielau.com>
AuthorDate: Mon Aug 21 11:43:27 2023 +0800
[SPARK-43205][DOCS][SQL][FOLLOWUP] IDENTIFIER clause docs
### What changes were proposed in this pull request?
This is a follow-up of #41007 .
This pull request documents the IDENTIFIER clause
### Why are the changes needed?
If we don't document it, it doesn't exist...
### Does this PR introduce _any_ user-facing change?
Yes
### How was this patch tested?
Acceptance test.
<img width="727" alt="Screenshot 2023-08-15 at 5 31 13 PM" src="https://github.com/apache/spark/assets/3514644/a84e1d8b-fb37-4207-869b-93ab620282c3">
<img width="739" alt="Screenshot 2023-08-15 at 5 31 06 PM" src="https://github.com/apache/spark/assets/3514644/c1036faa-639d-4d43-953c-68fb1b044908">
<img width="741" alt="Screenshot 2023-08-15 at 5 30 54 PM" src="https://github.com/apache/spark/assets/3514644/28e796e4-226c-4fc5-8561-706b639b0791">
Closes #41335 from srielau/SPARK-43205-IDENTIFIER-clause-docs.
Lead-authored-by: srielau <se...@rielau.com>
Co-authored-by: Serge Rielau <sr...@users.noreply.github.com>
Signed-off-by: Wenchen Fan <we...@databricks.com>
---
docs/sql-ref-identifier-clause.md | 65 +++++++++++++++++++++-
.../analyzer-results/identifier-clause.sql.out | 44 +++++++++++++++
.../sql-tests/inputs/identifier-clause.sql | 10 ++++
.../sql-tests/results/identifier-clause.sql.out | 48 ++++++++++++++++
4 files changed, 166 insertions(+), 1 deletion(-)
diff --git a/docs/sql-ref-identifier-clause.md b/docs/sql-ref-identifier-clause.md
index 694731109f8..1772e1a5538 100644
--- a/docs/sql-ref-identifier-clause.md
+++ b/docs/sql-ref-identifier-clause.md
@@ -23,7 +23,7 @@ license: |
Converts a constant `STRING` expression into a SQL object name.
The purpose of this clause is to allow for templating of identifiers in SQL statements without opening up the risk of SQL injection attacks.
-Typically, this clause is used with a parameter marker as argument.
+Typically, this clause is used with a parameter marker or a variable as argument.
### Syntax
@@ -46,6 +46,8 @@ A (qualified) identifier which can be used as a:
### Examples
+#### Scala examples
+
These examples use named parameter markers to templatize queries.
```scala
@@ -104,3 +106,64 @@ spark.sql("SELECT IDENTIFIER(:func)(-1)", args = Map("func" -> "abs")).show();
| 1|
+-------+
```
+
+#### SQL examples
+
+These examples use SQL variables to templatize queries.
+
+```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|
++-------+
+```
diff --git a/sql/core/src/test/resources/sql-tests/analyzer-results/identifier-clause.sql.out b/sql/core/src/test/resources/sql-tests/analyzer-results/identifier-clause.sql.out
index 00e2d8ff8ae..64268a75ace 100644
--- a/sql/core/src/test/resources/sql-tests/analyzer-results/identifier-clause.sql.out
+++ b/sql/core/src/test/resources/sql-tests/analyzer-results/identifier-clause.sql.out
@@ -562,6 +562,50 @@ DROP TEMPORARY FUNCTION IDENTIFIER('my' || 'DoubleAvg')
DropFunctionCommand myDoubleAvg, false, true
+-- !query
+DECLARE var = 'sometable'
+-- !query analysis
+CreateVariable defaultvalueexpression(sometable, 'sometable'), false
++- ResolvedIdentifier org.apache.spark.sql.catalyst.analysis.FakeSystemCatalog$@xxxxxxxx, session.var
+
+
+-- !query
+CREATE TABLE IDENTIFIER(var)(c1 INT) USING CSV
+-- !query analysis
+CreateDataSourceTableCommand `spark_catalog`.`default`.`sometable`, false
+
+
+-- !query
+SET VAR var = 'c1'
+-- !query analysis
+SetVariable [variablereference(system.session.var='sometable')]
++- Project [c1 AS var#x]
+ +- OneRowRelation
+
+
+-- !query
+SELECT IDENTIFIER(var) FROM VALUES(1) AS T(c1)
+-- !query analysis
+Project [c1#x]
++- SubqueryAlias T
+ +- LocalRelation [c1#x]
+
+
+-- !query
+SET VAR var = 'some'
+-- !query analysis
+SetVariable [variablereference(system.session.var='c1')]
++- Project [some AS var#x]
+ +- OneRowRelation
+
+
+-- !query
+DROP TABLE IDENTIFIER(var || 'table')
+-- !query analysis
+DropTable false, false
++- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.sometable
+
+
-- !query
SELECT IDENTIFIER('c 1') FROM VALUES(1) AS T(`c 1`)
-- !query analysis
diff --git a/sql/core/src/test/resources/sql-tests/inputs/identifier-clause.sql b/sql/core/src/test/resources/sql-tests/inputs/identifier-clause.sql
index a1bd500455d..00467244ff1 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/identifier-clause.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/identifier-clause.sql
@@ -101,6 +101,16 @@ DROP SCHEMA ident;
CREATE TEMPORARY FUNCTION IDENTIFIER('my' || 'DoubleAvg') AS 'test.org.apache.spark.sql.MyDoubleAvg';
DROP TEMPORARY FUNCTION IDENTIFIER('my' || 'DoubleAvg');
+-- IDENTIFIER + variable
+DECLARE var = 'sometable';
+CREATE TABLE IDENTIFIER(var)(c1 INT) USING CSV;
+
+SET VAR var = 'c1';
+SELECT IDENTIFIER(var) FROM VALUES(1) AS T(c1);
+
+SET VAR var = 'some';
+DROP TABLE IDENTIFIER(var || 'table');
+
-- Error conditions
SELECT IDENTIFIER('c 1') FROM VALUES(1) AS T(`c 1`);
SELECT IDENTIFIER('') FROM VALUES(1) AS T(``);
diff --git a/sql/core/src/test/resources/sql-tests/results/identifier-clause.sql.out b/sql/core/src/test/resources/sql-tests/results/identifier-clause.sql.out
index 8eabb74da97..cfedaae39e8 100644
--- a/sql/core/src/test/resources/sql-tests/results/identifier-clause.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/identifier-clause.sql.out
@@ -658,6 +658,54 @@ struct<>
+-- !query
+DECLARE var = 'sometable'
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TABLE IDENTIFIER(var)(c1 INT) USING CSV
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SET VAR var = 'c1'
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT IDENTIFIER(var) FROM VALUES(1) AS T(c1)
+-- !query schema
+struct<c1:int>
+-- !query output
+1
+
+
+-- !query
+SET VAR var = 'some'
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+DROP TABLE IDENTIFIER(var || 'table')
+-- !query schema
+struct<>
+-- !query output
+
+
+
-- !query
SELECT IDENTIFIER('c 1') FROM VALUES(1) AS T(`c 1`)
-- !query schema
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org