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/09/27 05:05:35 UTC
[spark] branch master updated: [SPARK-44780][DOC] SQL temporary variables
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 f6c6acbc00d [SPARK-44780][DOC] SQL temporary variables
f6c6acbc00d is described below
commit f6c6acbc00d2d96c43298c282e4bd8ebeb160ad1
Author: Serge Rielau <sr...@users.noreply.github.com>
AuthorDate: Wed Sep 27 13:05:19 2023 +0800
[SPARK-44780][DOC] SQL temporary variables
### What changes were proposed in this pull request?
Document the previously pushed feature SQL temporary variables
### Why are the changes needed?
If it's not documented, it doesn't exist....
### Does this PR introduce _any_ user-facing change?
No
### How was this patch tested?
build docs, verify HTML
Closes #42467 from srielau/SPARK-44780-Doc-sql-session-variables.
Lead-authored-by: Serge Rielau <sr...@users.noreply.github.com>
Co-authored-by: srielau <se...@rielau.com>
Signed-off-by: Wenchen Fan <we...@databricks.com>
---
docs/sql-ref-syntax-aux-conf-mgmt-set.md | 3 +
docs/sql-ref-syntax-aux-set-var.md | 98 +++++++++++++++++++++++++++++
docs/sql-ref-syntax-ddl-declare-variable.md | 82 ++++++++++++++++++++++++
docs/sql-ref-syntax-ddl-drop-variable.md | 66 +++++++++++++++++++
docs/sql-ref-syntax.md | 3 +
5 files changed, 252 insertions(+)
diff --git a/docs/sql-ref-syntax-aux-conf-mgmt-set.md b/docs/sql-ref-syntax-aux-conf-mgmt-set.md
index f97b7f2a8ef..9e57a221f96 100644
--- a/docs/sql-ref-syntax-aux-conf-mgmt-set.md
+++ b/docs/sql-ref-syntax-aux-conf-mgmt-set.md
@@ -23,6 +23,8 @@ license: |
The SET command sets a property, returns the value of an existing property or returns all SQLConf properties with value and meaning.
+To set SQL variables defined with [DECLARE VARIABLE](sql-ref-syntax-ddl-declare-variable.html) use [SET VAR](sql-ref-syntax-aux-set-var.html).
+
### Syntax
```sql
@@ -69,3 +71,4 @@ SET spark.sql.variable.substitute;
### Related Statements
* [RESET](sql-ref-syntax-aux-conf-mgmt-reset.html)
+* [SET VAR](sql-ref-syntax-aux-set-var.html)
diff --git a/docs/sql-ref-syntax-aux-set-var.md b/docs/sql-ref-syntax-aux-set-var.md
new file mode 100644
index 00000000000..9ce9e68cd4f
--- /dev/null
+++ b/docs/sql-ref-syntax-aux-set-var.md
@@ -0,0 +1,98 @@
+---
+layout: global
+title: SET VAR
+displayTitle: SET VAR
+license: |
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+---
+
+### Description
+
+The `SET VAR` command sets a temporary variable which has been previously declared in the current session.
+
+To set a config variable or a hive variable use [SET](sql-ref-syntax-aux-conf-mgmt-set.html).
+
+### Syntax
+
+```sql
+SET { VAR | VARIABLE }
+ { { variable_name = { expression | DEFAULT } } [, ...] |
+ ( variable_name [, ...] ) = ( query ) }
+```
+
+### Parameters
+
+* **variable_name**
+
+ Specifies an existing variable.
+ If you specify multiple variables, there must not be any duplicates.
+
+* **expression**
+
+ Any expression, including scalar subqueries.
+
+* **DEFAULT**
+
+ If you specify `DEFAULT`, the default expression of the variable is assigned,
+ or `NULL` if there is none.
+
+* **query**
+
+ A [query](sql-ref-syntax-qry-select.html) that returns at most one row and as many columns as
+ the number of specified variables. Each column must be implicitly castable to the data type of the
+ corresponding variable.
+ If the query returns no row `NULL` values are assigned.
+
+### Examples
+
+```sql
+--
+DECLARE VARIABLE var1 INT DEFAULT 7;
+DECLARE VARIABLE var2 STRING;
+
+-- A simple assignment
+SET VAR var1 = 5;
+SELECT var1;
+ 5
+
+-- A complex expression assignment
+SET VARIABLE var1 = (SELECT max(c1) FROM VALUES(1), (2) AS t(c1));
+SELECT var1;
+ 2
+
+-- resetting the variable to DEFAULT
+SET VAR var1 = DEFAULT;
+SELECT var1;
+ 7
+
+-- A multi variable assignment
+SET VAR (var1, var2) = (SELECT max(c1), CAST(min(c1) AS STRING) FROM VALUES(1), (2) AS t(c1));
+SELECT var1, var2;
+ 2 1
+
+-- Too many rows
+SET VAR (var1, var2) = (SELECT c1, CAST(c1 AS STRING) FROM VALUES(1), (2) AS t(c1));
+Error: ROW_SUBQUERY_TOO_MANY_ROWS
+
+-- No rows
+SET VAR (var1, var2) = (SELECT c1, CAST(c1 AS STRING) FROM VALUES(1), (2) AS t(c1) WHERE 1=0);
+SELECT var1, var2;
+ NULL NULL
+```
+
+### Related Statements
+
+* [SET](sql-ref-syntax-aux-conf-mgmt-set.html)
diff --git a/docs/sql-ref-syntax-ddl-declare-variable.md b/docs/sql-ref-syntax-ddl-declare-variable.md
new file mode 100644
index 00000000000..eea6222646f
--- /dev/null
+++ b/docs/sql-ref-syntax-ddl-declare-variable.md
@@ -0,0 +1,82 @@
+---
+layout: global
+title: DECLARE VARIABLE
+displayTitle: DECLARE VARIABLE
+license: |
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+---
+
+### Description
+
+The `DECLARE VARIABLE` statement is used to create a temporary variable in Spark.
+Temporary variables are scoped at a session level.
+
+You can reference variables by their name everywhere constant expressions are allowed.
+Unless you qualify a variable with `session` or `system.session`, a variable is only resolved after
+Spark fails to resolve a name to a column or column alias.
+
+Temporary variables cannot be referenced in persisted objects such as persisted view,
+column default expressions, and generated column expressions.
+
+### Syntax
+
+```sql
+DECLARE [ OR REPLACE ] [ VARIABLE ]
+ variable_name [ data_type ] [ { DEFAULT | = } default_expr ]
+```
+
+### Parameters
+
+* **OR REPLACE**
+
+ If specified, a pre-existing temporary variable is replaced if it exists.
+
+* **variable_name**
+
+ Specifies a name for the variable to be created.
+ The variable name may be optionally qualified with a `system`.`session` or `session`.
+
+ **Syntax:** `[ system . [ session .] ] variable_name`
+
+* **data_type**
+
+ Optionally defines the data type of the variable.
+ If it is not specified the type is derived from the default expression.
+
+* **default_expr**
+
+ An optional expression used to initialize the value of the variable after declaration.
+ The expression is re-evaluated whenever the variable is reset to `DEFAULT` using
+ [SET VAR](sql-ref-syntax-aux-set-var.html).
+ If `data_type` is specified `default_expr` must be castable to the variable type.
+ If `data_type` is not specified you must specify a default and its type will become the type of
+ the variable.
+ If no default expression is given, the variable is initialized with `NULL`.
+
+### Examples
+
+```sql
+-- The dense form of declaring a variabel with default
+DECLARE five = 5;
+
+-- STRING variable initialialized to `NULL`
+DECLARE some_var STRING;
+```
+
+### Related Statements
+
+* [DROP TEMPORARY VARIABLE](sql-ref-syntax-ddl-drop-variable.html)
+* [SET VARIABLE](sql-ref-syntax-aux-set-var.html)
diff --git a/docs/sql-ref-syntax-ddl-drop-variable.md b/docs/sql-ref-syntax-ddl-drop-variable.md
new file mode 100644
index 00000000000..c6cf6676924
--- /dev/null
+++ b/docs/sql-ref-syntax-ddl-drop-variable.md
@@ -0,0 +1,66 @@
+---
+layout: global
+title: DROP TEMPORARY VARIABLE
+displayTitle: DROP TEMPORARY VARIABLE
+license: |
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+---
+
+### Description
+
+The `DROP TEMPORARY VARIABLE` statement drops a temporary variable. An exception will
+be thrown if the variable does not exist.
+
+### Syntax
+
+```sql
+DROP TEMPORARY VARIABLE [ IF EXISTS ] variable_name
+```
+
+### Parameters
+
+* **variable_name**
+
+ Specifies the name of an existing variable. The function name may be
+ optionally qualified with a `system.session` or `session`.
+
+ **Syntax:** `[ system. [ session.] ] variable_name`
+
+* **IF EXISTS**
+
+ If specified, no exception is thrown when the variable does not exist.
+
+### Examples
+
+```sql
+-- Create a temporary variable var1
+DECLARE VARIABLE var1 INT;
+
+-- Drop temporary variable
+DROP TEMPORARY VARIABLE var1;
+
+-- Try to drop temporary variable which is not present
+DROP TEMPORARY VARIABLE var1;
+Error: VARIABLE_NOT_FOUND
+The variable `system`.`session`.`var1` cannot be found.
+
+-- Drop temporart variable if it exists
+DROP TEMPORARY VARIABLE IF EXISTS var1;
+```
+
+### Related Statements
+
+* [DECLARE VARIABLE](sql-ref-syntax-ddl-declare-variable.html)
diff --git a/docs/sql-ref-syntax.md b/docs/sql-ref-syntax.md
index 9109d130ab7..8bc1c7cec56 100644
--- a/docs/sql-ref-syntax.md
+++ b/docs/sql-ref-syntax.md
@@ -32,9 +32,11 @@ Data Definition Statements are used to create or modify the structure of databas
* [CREATE FUNCTION](sql-ref-syntax-ddl-create-function.html)
* [CREATE TABLE](sql-ref-syntax-ddl-create-table.html)
* [CREATE VIEW](sql-ref-syntax-ddl-create-view.html)
+ * [DECLARE VARIABLE](sql-ref-syntax-ddl-declare-variable.html)
* [DROP DATABASE](sql-ref-syntax-ddl-drop-database.html)
* [DROP FUNCTION](sql-ref-syntax-ddl-drop-function.html)
* [DROP TABLE](sql-ref-syntax-ddl-drop-table.html)
+ * [DROP TEMPORARY VARIABLE](sql-ref-syntax-ddl-drop-variable.html)
* [DROP VIEW](sql-ref-syntax-ddl-drop-view.html)
* [REPAIR TABLE](sql-ref-syntax-ddl-repair-table.html)
* [TRUNCATE TABLE](sql-ref-syntax-ddl-truncate-table.html)
@@ -105,6 +107,7 @@ ability to generate logical and physical plan for a given query using
* [REFRESH FUNCTION](sql-ref-syntax-aux-cache-refresh-function.html)
* [RESET](sql-ref-syntax-aux-conf-mgmt-reset.html)
* [SET](sql-ref-syntax-aux-conf-mgmt-set.html)
+ * [SET VAR](sql-ref-syntax-aux-set-var.html)
* [SHOW COLUMNS](sql-ref-syntax-aux-show-columns.html)
* [SHOW CREATE TABLE](sql-ref-syntax-aux-show-create-table.html)
* [SHOW DATABASES](sql-ref-syntax-aux-show-databases.html)
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org