Repository: drill Updated Branches: refs/heads/gh-pages 7fb253c5f -> 8212952e7
Updated docs for SET command changes Project: http://git-wip-us.apache.org/repos/asf/drill/repo Commit: http://git-wip-us.apache.org/repos/asf/drill/commit/8212952e Tree: http://git-wip-us.apache.org/repos/asf/drill/tree/8212952e Diff: http://git-wip-us.apache.org/repos/asf/drill/diff/8212952e Branch: refs/heads/gh-pages Commit: 8212952e78f6fcde3ab8c53f41f3d3106f39a560 Parents: 7fb253c Author: Bridget Bevens <[email protected]> Authored: Wed Dec 9 15:06:22 2015 -0800 Committer: Bridget Bevens <[email protected]> Committed: Wed Dec 9 15:06:22 2015 -0800 ---------------------------------------------------------------------- .../030-planning-and-exececution-options.md | 2 +- .../sql-commands/005-supported-sql-commands.md | 38 +++++----- .../sql-commands/010-alter-session.md | 74 -------------------- _docs/sql-reference/sql-commands/010-set.md | 66 +++++++++++++++++ _docs/sql-reference/sql-commands/012-reset.md | 63 +++++++++++++++++ .../sql-commands/020-alter-system.md | 65 ++++++++--------- 6 files changed, 181 insertions(+), 127 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/drill/blob/8212952e/_docs/configure-drill/configuration-options/030-planning-and-exececution-options.md ---------------------------------------------------------------------- diff --git a/_docs/configure-drill/configuration-options/030-planning-and-exececution-options.md b/_docs/configure-drill/configuration-options/030-planning-and-exececution-options.md index d1c9a30..3549b7f 100644 --- a/_docs/configure-drill/configuration-options/030-planning-and-exececution-options.md +++ b/_docs/configure-drill/configuration-options/030-planning-and-exececution-options.md @@ -10,7 +10,7 @@ level settings override system level settings. ## Configuring Planning and Execution Options -Use the ALTER SYSTEM or ALTER SESSION commands to set options. Typically, +Use the [ALTER SYSTEM]({{site.baseurl}}/docs/alter-system/) or [SET]({{site.baseurl}}/docs/set/) commands to set planning and execution options. Typically, you set the options at the session level unless you want the setting to persist across all sessions. http://git-wip-us.apache.org/repos/asf/drill/blob/8212952e/_docs/sql-reference/sql-commands/005-supported-sql-commands.md ---------------------------------------------------------------------- diff --git a/_docs/sql-reference/sql-commands/005-supported-sql-commands.md b/_docs/sql-reference/sql-commands/005-supported-sql-commands.md index 66db086..a438def 100644 --- a/_docs/sql-reference/sql-commands/005-supported-sql-commands.md +++ b/_docs/sql-reference/sql-commands/005-supported-sql-commands.md @@ -3,21 +3,25 @@ title: Supported SQL Commands parent: "SQL Commands" --- The following table provides a list of the SQL commands that Drill supports, -with their descriptions and example syntax: +with their descriptions and example syntax: -| Command | Description | Syntax | -|--------------------------------------------------------------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------| -| [ALTER SESSION]({{site.baseurl}}/docs/alter-session) | Changes a system setting for the duration of a session. A session ends when you quit the Drill shell. For a list of Drill options and their descriptions, refer to Planning and Execution Options. | ALTER SESSION SET \`option_name` = value; | -| [ALTER SYSTEM]({{site.baseurl}}/docs/alter-system) | Permanently changes a system setting. The new settings persist across all sessions. For a list of Drill options and their descriptions, refer to Planning and Execution Options. | ALTER SYSTEM SET \`option_name`=value; | -| [CREATE TABLE AS(CTAS) ]({{site.baseurl}}/docs/create-table-as--ctas) | Creates a new table and populates the new table with rows returned from a SELECT query. Use the CREATE TABLE AS (CTAS) statement in place of INSERT INTO. | CREATE TABLE name AS query; | -| [CREATE VIEW]({{site.baseurl}}/docs/create-view) | Creates a virtual structure for the result set of a stored query.- | CREATE [OR REPLACE] VIEW [workspace.]view_name [ (column_name [, ...]) ] AS query; | -| [DESCRIBE ]({{site.baseurl}}/docs/describe) | Returns information about columns in a table or view. | DESCRIBE [workspace.]table_name | -| [DROP VIEW]({{site.baseurl}}/docs/drop-view) | Removes a view. | DROP VIEW [workspace.]view_name ; | -| [EXPLAIN PLAN FOR]({{site.baseurl}}/docs/explain) | Returns the physical plan for a particular query. | EXPLAIN PLAN FOR query; | -| [EXPLAIN PLAN WITHOUT IMPLEMENTATION FOR]({{site.baseurl}}/docs/explain) | Returns the logical plan for a particular query. | EXPLAIN PLAN WITHOUT IMPLEMENTATION FOR query; | -| [SELECT]({{site.baseurl}}/docs/select) | Retrieves data from tables and files. | [WITH subquery]SELECT column_list FROM table_name[WHERE clause] [GROUP BY clause][HAVING clause][ORDER BY clause]; | -| [SHOW DATABASES]({{site.baseurl}}/docs/show-databases-and-show-schemas) | Returns a list of available schemas. Equivalent to SHOW SCHEMAS. | SHOW DATABASES; | -| [SHOW FILES]({{site.baseurl}}/docs/show-files) | Returns a list of files in a file system schema. | SHOW FILES IN|FROM filesystem.\`schema_name`; | -| [SHOW SCHEMAS]({{site.baseurl}}/docs/show-databases-and-show-schemas) | Returns a list of available schemas. Equivalent to SHOW DATABASES. | SHOW SCHEMAS; | -| [SHOW TABLES]({{site.baseurl}}/docs/show-tables) | Returns a list of tables and views. | SHOW TABLES; | -| [USE]({{site.baseurl}}/docs/use) | Change to a particular schema. When you opt to use a particular schema, Drill issues queries on that schema only. | USE schema_name; | \ No newline at end of file +| Command | Description | Syntax | +|--------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------| +| [ALTER SESSION SET]({{site.baseurl}}/docs/set) | The SET command replaces the ALTER SESSION SET command in Drill version 1.3 and later. ALTER SESSION is now just an alias for the SET command. See [SET]({{site.baseurl}}/docs/set). | [ALTER SESSION] SET \`option_name` = value; | +| [ALTER SYSTEM SET]({{site.baseurl}}/docs/alter-system) | Changes a system setting. The new setting persists across all sessions. For a list of Drill options and their descriptions, see [Planning and Execution Options]{{site.baseurl/docs/planning-and-execution-options/}}. | ALTER SYSTEM SET \`option_name` = value; | +| [ALTER SYSTEM RESET]({{site.baseurl}}/docs/alter-system) | Changes a system setting back to its default system setting. For a list of Drill options and their descriptions, see [Planning and Execution Options]{{site.baseurl/docs/planning-and-execution-options/}}. | ALTER SYSTEM RESET \`option_name`; | +| [ALTER SYSTEM RESET ALL]({{site.baseurl}}/docs/alter-system) | Changes all system settings back to their default system values. For a list of Drill options and their descriptions, see [Planning and Execution Options]{{site.baseurl/docs/planning-and-execution-options/}}. | ALTER SYSTEM RESET ALL; | +| [CREATE TABLE AS(CTAS) ]({{site.baseurl}}/docs/create-table-as--ctas) | Creates a new table and populates the new table with rows returned from a SELECT query. Use the CREATE TABLE AS (CTAS) statement in place of INSERT INTO. | CREATE TABLE name AS query; | +| [CREATE VIEW]({{site.baseurl}}/docs/create-view) | Creates a virtual structure for the result set of a stored query.- | CREATE [OR REPLACE] VIEW [workspace.]view_name [ (column_name [, ...]) ] AS query; | +| [DESCRIBE ]({{site.baseurl}}/docs/describe) | Returns information about columns in a table or view. | DESCRIBE [workspace.]table_name | +| [DROP VIEW]({{site.baseurl}}/docs/drop-view) | Removes a view. | DROP VIEW [workspace.]view_name ; | +| [EXPLAIN PLAN FOR]({{site.baseurl}}/docs/explain) | Returns the physical plan for a particular query. | EXPLAIN PLAN FOR query; | +| [EXPLAIN PLAN WITHOUT IMPLEMENTATION FOR]({{site.baseurl}}/docs/explain) | Returns the logical plan for a particular query. | EXPLAIN PLAN WITHOUT IMPLEMENTATION FOR query; | +| [RESET]({{site.baseurl}}/docs/reset) | Changes a session setting back to its default system setting. For a list of Drill options and their descriptions, see [Planning and Execution Options]{{site.baseurl/docs/planning-and-execution-options/}}. | [ALTER SESSION] RESET \`option_name`; | +| [SELECT]({{site.baseurl}}/docs/select) | Retrieves data from tables and files. | [WITH subquery]SELECT column_list FROM table_name[WHERE clause] [GROUP BY clause][HAVING clause][ORDER BY clause]; | +| [SET]({{site.baseurl}}/docs/set) | Changes a system setting for the duration of a session. A session ends when you quit the Drill shell. For a list of Drill options and their descriptions, see [Planning and Execution Options]{{site.baseurl/docs/planning-and-execution-options/}}. | [ALTER SESSION] SET \`option_name` = value; | +| [SHOW DATABASES]({{site.baseurl}}/docs/show-databases-and-show-schemas) | Returns a list of available schemas. Equivalent to SHOW SCHEMAS. | SHOW DATABASES; | +| [SHOW FILES]({{site.baseurl}}/docs/show-files) | Returns a list of files in a file system schema. | SHOW FILES IN|FROM filesystem.\`schema_name`; | +| [SHOW SCHEMAS]({{site.baseurl}}/docs/show-databases-and-show-schemas) | Returns a list of available schemas. Equivalent to SHOW DATABASES. | SHOW SCHEMAS; | +| [SHOW TABLES]({{site.baseurl}}/docs/show-tables) | Returns a list of tables and views. | SHOW TABLES; | +| [USE]({{site.baseurl}}/docs/use) | Change to a particular schema. When you opt to use a particular schema, Drill issues queries on that schema only. | USE schema_name; | | \ No newline at end of file http://git-wip-us.apache.org/repos/asf/drill/blob/8212952e/_docs/sql-reference/sql-commands/010-alter-session.md ---------------------------------------------------------------------- diff --git a/_docs/sql-reference/sql-commands/010-alter-session.md b/_docs/sql-reference/sql-commands/010-alter-session.md deleted file mode 100644 index 588688e..0000000 --- a/_docs/sql-reference/sql-commands/010-alter-session.md +++ /dev/null @@ -1,74 +0,0 @@ ---- -title: "ALTER SESSION" -parent: "SQL Commands" ---- -The ALTER SESSION command changes a system setting for the duration of a -session. Session level settings override system level settings. - -## Syntax - -The ALTER SESSION command supports the following syntax: - - ALTER SESSION SET `option_name` = value; - -## Parameters - -*option_name* -This is the option name as it appears in the systems table. - -*value* -A value of the type listed in the sys.options table: number, string, boolean, -or float. Use the appropriate value type for each option that you set. - -## Usage Notes - -Use the ALTER SESSION command to set Drill query planning and execution -options per session in a cluster. The options that you set using the ALTER -SESSION command only apply to queries that run during the current Drill -connection. A session ends when you quit the Drill shell. You can set any of -the system level options at the session level. - -You can run the following query to see a complete list of planning and -execution options that are currently set at the system or session level: - - 0: jdbc:drill:zk=local> SELECT name, type FROM sys.options WHERE type in ('SYSTEM','SESSION') order by name; - +------------+----------------------------------------------+ - | name | type | - +----------------------------------------------+------------+ - | drill.exec.functions.cast_empty_string_to_null | SYSTEM | - | drill.exec.storage.file.partition.column.label | SYSTEM | - | exec.errors.verbose | SYSTEM | - | exec.java_compiler | SYSTEM | - | exec.java_compiler_debug | SYSTEM | - ⦠- +------------+----------------------------------------------+ - -{% include startnote.html %}This is a truncated version of the list.{% include endnote.html %} - -## Example - -This example demonstrates how to use the ALTER SESSION command to set the -`store.json.all_text_mode` option to âtrueâ for the current Drill session. -Setting this option to âtrueâ enables text mode so that Drill reads everything -in JSON as a text object instead of trying to interpret data types. This -allows complicated JSON to be read using CASE and CAST. - - 0: jdbc:drill:zk=local> alter session set `store.json.all_text_mode`= true; - +------------+------------+ - | ok | summary | - +------------+------------+ - | true | store.json.all_text_mode updated. | - +------------+------------+ - 1 row selected (0.046 seconds) - -You can issue a query to see all of the session level settings. Note that the -option type is case-sensitive. - - 0: jdbc:drill:zk=local> SELECT name, type, bool_val FROM sys.options WHERE type = 'SESSION' order by name; - +------------+------------+------------+ - | name | type | bool_val | - +------------+------------+------------+ - | store.json.all_text_mode | SESSION | true | - +------------+------------+------------+ - 1 row selected (0.176 seconds) - http://git-wip-us.apache.org/repos/asf/drill/blob/8212952e/_docs/sql-reference/sql-commands/010-set.md ---------------------------------------------------------------------- diff --git a/_docs/sql-reference/sql-commands/010-set.md b/_docs/sql-reference/sql-commands/010-set.md new file mode 100644 index 0000000..82cb7ba --- /dev/null +++ b/_docs/sql-reference/sql-commands/010-set.md @@ -0,0 +1,66 @@ +--- +title: "SET" +parent: "SQL Commands" +--- +The SET command replaces the ALTER SESSION SET command in Drill version 1.3 and later. The SET command changes a system setting for the duration of a session. Session level settings override system level settings. + +## Syntax + +The SET command supports the following syntax: + + [ALTER SESSION] SET `option_name` = value; + +## Parameters + +*option_name* +This is the option name as it appears in the systems table. + +*value* +A value of the type listed in the sys.options table: number, string, boolean, +or float. Use the appropriate value type for each option that you set. + +## Usage Notes +You can still use the ALTER SESSION SET command, however ALTER SESSION is now just an alias for the SET command. Use the SET command to change Drill query planning and execution +options per session in a cluster. The options that you set only apply to queries that run during the current Drill connection. A session ends when you quit the Drill shell. You can set any of the system level options at the session level. You can use the [RESET]({{site.baseurl}}/docs/reset) command to change the value of an option back to the default system setting. + +You can run the following query to see a complete list of planning and +execution options that are currently set at the system or session level: + + 0: jdbc:drill:zk=local> SELECT name, type FROM sys.options WHERE type in ('SYSTEM','SESSION') order by name; + +------------+------------------------------------------------+ + | name | type | + +----------------------------------------------+--------------+ + | drill.exec.functions.cast_empty_string_to_null | SYSTEM | + | drill.exec.storage.file.partition.column.label | SYSTEM | + | exec.errors.verbose | SYSTEM | + | exec.java_compiler | SYSTEM | + | exec.java_compiler_debug | SYSTEM | + ⦠+ +------------+------------------------------------------------+ + +{% include startnote.html %}This is a truncated version of the list.{% include endnote.html %} + +## Example + +This example demonstrates how to use the SET command to set the +`store.json.all_text_mode` option to âtrueâ for the current Drill session. +Setting this option to âtrueâ enables text mode so that Drill reads everything +in JSON as a text object instead of trying to interpret data types. This +allows complicated JSON to be read using CASE and CAST. + + 0: jdbc:drill:zk=local> SET `store.json.all_text_mode`= true; + +-------+------------------------------------+ + | ok | summary | + +-------+------------------------------------+ + | true | store.json.all_text_mode updated. | + +-------+------------------------------------+ + +You can issue a query to see options set at the session level. Note that the +option type is case-sensitive. + + 0: jdbc:drill:zk=local> SELECT name, type, bool_val FROM sys.options WHERE type = 'SESSION' order by name; + +---------------------------+----------+-----------+ + | name | type | bool_val | + +---------------------------+----------+-----------+ + | store.json.all_text_mode | SESSION | true | + +---------------------------+----------+-----------+ \ No newline at end of file http://git-wip-us.apache.org/repos/asf/drill/blob/8212952e/_docs/sql-reference/sql-commands/012-reset.md ---------------------------------------------------------------------- diff --git a/_docs/sql-reference/sql-commands/012-reset.md b/_docs/sql-reference/sql-commands/012-reset.md new file mode 100644 index 0000000..6799dff --- /dev/null +++ b/_docs/sql-reference/sql-commands/012-reset.md @@ -0,0 +1,63 @@ +--- +title: "RESET" +parent: "SQL Commands" +--- +The RESET command is available in Drill version 1.3 and later. The RESET command resets a session level option back to its default system setting. + +## Syntax + +The RESET command supports the following syntax: + + [ALTER SESSION] RESET `option_name`; + +## Parameters + +*option_name* +This is the option name as it appears in the systems table. + +*value* +A value of the type listed in the sys.options table: number, string, boolean, +or float. Use the appropriate value type for each option that you set. + +## Usage Notes +You can use the ALTER SESSION RESET command, however ALTER SESSION is just an alias for the RESET command. Use the RESET command to change Drill query planning and execution +options set at the session level back to their default system settings. You can reset any options set at the session level. + +You can run the following query to see a complete list of planning and +execution options that are currently set at the system or session level: + + 0: jdbc:drill:zk=local> SELECT name, type FROM sys.options WHERE type in ('SYSTEM','SESSION') order by name; + +------------+----------------------------------------------+ + | name | type | + +----------------------------------------------+------------+ + | drill.exec.functions.cast_empty_string_to_null | SYSTEM | + | drill.exec.storage.file.partition.column.label | SYSTEM | + | exec.errors.verbose | SYSTEM | + | exec.java_compiler | SYSTEM | + | exec.java_compiler_debug | SYSTEM | + ⦠+ +------------+----------------------------------------------+ + +{% include startnote.html %}This is a truncated version of the list.{% include endnote.html %} + +## Example + +This example demonstrates how to use the RESET command to reset the +`store.json.all_text_mode` option back to the default system setting (false) which disables all text mode so that Drill interprets data types when querying data. + + 0: jdbc:drill:zk=local> RESET `store.json.all_text_mode`; + +-------+------------------------------------+ + | ok | summary | + +-------+------------------------------------+ + | true | store.json.all_text_mode updated. | + +-------+------------------------------------+ + 1 row selected (0.093 seconds) + +You can issue a query to see the default system setting for the option: + + 0: jdbc:drill:zk=local> SELECT name, type, bool_val FROM sys.options WHERE name='store.json.all_text_mode'; + +---------------------------+---------+-----------+ + | name | type | bool_val | + +---------------------------+---------+-----------+ + | store.json.all_text_mode | SYSTEM | false | + +---------------------------+---------+-----------+ \ No newline at end of file http://git-wip-us.apache.org/repos/asf/drill/blob/8212952e/_docs/sql-reference/sql-commands/020-alter-system.md ---------------------------------------------------------------------- diff --git a/_docs/sql-reference/sql-commands/020-alter-system.md b/_docs/sql-reference/sql-commands/020-alter-system.md index 573f2bd..2617786 100644 --- a/_docs/sql-reference/sql-commands/020-alter-system.md +++ b/_docs/sql-reference/sql-commands/020-alter-system.md @@ -10,7 +10,9 @@ settings. The ALTER SYSTEM command supports the following syntax: - ALTER SYSTEM SET `option_name` = value; + ALTER SYSTEM SET `option_name` = value; + ALTER SYSTEM RESET `option_name`; + ALTER SYSTEM RESET ALL; ## Parameters @@ -25,43 +27,28 @@ or float. Use the appropriate value type for each option that you set. ## Usage Notes -Use the ALTER SYSTEM command to permanently set Drill query planning and +Use the ALTER SYSTEM SET command to permanently set Drill query planning and execution options per cluster. Options set at the system level affect the entire system and persist between restarts. +Using ALTER SYSTEM RESET changes the value of an option back to the default system setting. + +Using ALTER SYSTEM RESET ALL changes the value of every option back to the default system setting. + You can run the following query to see a complete list of planning and -execution options that you can set at the system level: - - 0: jdbc:drill:zk=local> select name, type, num_val, string_val, bool_val, float_val from sys.options where type like 'SYSTEM' order by name; - +------------+------------+------------+------------+------------+------------+ - | name | type | num_val | string_val | bool_val | float_val | - +------------+------------+------------+------------+------------+------------+ - | drill.exec.functions.cast_empty_string_to_null | SYSTEM | null | null | false | null | - | drill.exec.storage.file.partition.column.label | SYSTEM | null | dir | null | null | - | exec.errors.verbose | SYSTEM | null | null | false | null | - | exec.java_compiler | SYSTEM | null | DEFAULT | null | null | - | exec.java_compiler_debug | SYSTEM | null | null | true | null | - | exec.java_compiler_janino_maxsize | SYSTEM | 262144 | null | null | null | - | exec.queue.timeout_millis | SYSTEM | 400000 | null | null | null | - | planner.add_producer_consumer | SYSTEM | null | null | true | null | - | planner.affinity_factor | SYSTEM | null | null | null | 1.2 | - | planner.broadcast_threshold | SYSTEM | 1000000 | null | null | null | - | planner.disable_exchanges | SYSTEM | null | null | false | null | - | planner.enable_broadcast_join | SYSTEM | null | null | true | null | - | planner.enable_hash_single_key | SYSTEM | null | null | true | null | - | planner.enable_hashagg | SYSTEM | null | null | true | null | - | planner.enable_hashjoin | SYSTEM | null | null | true | null | - | planner.slice_target | SYSTEM | 100000 | null | null | null | - | planner.width.max_per_node | SYSTEM | 2 | null | null | null | - | planner.width.max_per_query | SYSTEM | 1000 | null | null | null | - | store.format | SYSTEM | null | parquet | null | null | - | store.json.all_text_mode | SYSTEM | null | null | false | null | - | store.mongo.all_text_mode | SYSTEM | null | null | false | null | - | store.parquet.block-size | SYSTEM | 536870912 | null | null | null | - | store.parquet.use_new_reader | SYSTEM | null | null | false | null | - | store.parquet.vector_fill_check_threshold | SYSTEM | 10 | null | null | null | - | store.parquet.vector_fill_threshold | SYSTEM | 85 | null | null | null | - +------------+------------+------------+------------+------------+------------+ +execution options that are currently set at the system or session level: + + 0: jdbc:drill:zk=local> SELECT name, type FROM sys.options WHERE type in ('SYSTEM','SESSION') order by name; + +------------+------------------------------------------------+ + | name | type | + +----------------------------------------------+--------------+ + | drill.exec.functions.cast_empty_string_to_null | SYSTEM | + | drill.exec.storage.file.partition.column.label | SYSTEM | + | exec.errors.verbose | SYSTEM | + | exec.java_compiler | SYSTEM | + | exec.java_compiler_debug | SYSTEM | + ⦠+ +------------+------------------------------------------------+ {% include startnote.html %}This is a truncated version of the list.{% include endnote.html %} @@ -96,7 +83,15 @@ Note that the option type is case-sensitive. | planner.enable_streamagg | SYSTEM | true | | planner.enable_hashjoin | SYSTEM | true | +------------+------------+------------+ - 9 rows selected (0.159 seconds) + 9 rows selected (0.159 seconds) + +Issuing the ALTER SYSTEM RESET command resets the option back to the default system value (false): + + 0: jdbc:drill:zk=local> ALTER SYSTEM RESET `planner.add_producer_consumer`; + +Issuing the ALTER SYSTEM RESET ALL command resets all options back to their default system values: + + 0: jdbc:drill:zk=local> ALTER SYSTEM RESET ALL;
