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 <srie...@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 <srie...@users.noreply.github.com> Co-authored-by: srielau <se...@rielau.com> Signed-off-by: Wenchen Fan <wenc...@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-unsubscr...@spark.apache.org For additional commands, e-mail: commits-h...@spark.apache.org