DRILL-2736
Project: http://git-wip-us.apache.org/repos/asf/drill/repo Commit: http://git-wip-us.apache.org/repos/asf/drill/commit/d3328217 Tree: http://git-wip-us.apache.org/repos/asf/drill/tree/d3328217 Diff: http://git-wip-us.apache.org/repos/asf/drill/diff/d3328217 Branch: refs/heads/gh-pages Commit: d3328217e27183e6978a0b9ebb09317d666bfd0f Parents: c8a79a5 Author: Kristine Hahn <kh...@maprtech.com> Authored: Mon Apr 13 13:17:36 2015 -0700 Committer: Bridget Bevens <bbev...@maprtech.com> Committed: Mon Apr 13 14:28:46 2015 -0700 ---------------------------------------------------------------------- _docs/0001-DRILL-2720.patch | 375 +++++++++++++++++++ _docs/img/loginSandBox.png | Bin 53970 -> 67090 bytes _docs/img/vbApplSettings.png | Bin 45140 -> 115803 bytes _docs/img/vbGenSettings.png | Bin 56436 -> 56642 bytes _docs/img/vbImport.png | Bin 29075 -> 85744 bytes _docs/img/vbNetwork.png | Bin 32117 -> 30826 bytes _docs/img/vbloginSandBox.png | Bin 52169 -> 65477 bytes _docs/img/vmLibrary.png | Bin 68085 -> 85632 bytes _docs/img/vmShare.png | Bin 49069 -> 22898 bytes _docs/manage/conf/001-mem-alloc.md | 8 +- _docs/manage/conf/002-startup-opt.md | 20 +- _docs/manage/conf/003-plan-exec.md | 52 ++- _docs/manage/conf/004-persist-conf.md | 4 - _docs/sql-ref/001-data-types.md | 238 +++++++----- _docs/sql-ref/002-lexical-structure.md | 11 + _docs/sql-ref/data-types/001-date.md | 33 +- _docs/sql-ref/data-types/002-diff-data-types.md | 2 +- _docs/sql-ref/functions/002-conversion.md | 219 ++++++----- _docs/sql-ref/functions/003-date-time-fcns.md | 288 +++++++++----- _docs/sql-ref/functions/004-string.md | 135 ++++--- _docs/sql-ref/functions/005-aggregate.md | 2 +- _docs/sql-ref/functions/006-nulls.md | 2 +- .../install-sandbox/001-install-mapr-vm.md | 25 +- .../install-sandbox/002-install-mapr-vb.md | 58 +-- 24 files changed, 1046 insertions(+), 426 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/drill/blob/d3328217/_docs/0001-DRILL-2720.patch ---------------------------------------------------------------------- diff --git a/_docs/0001-DRILL-2720.patch b/_docs/0001-DRILL-2720.patch new file mode 100644 index 0000000..738f444 --- /dev/null +++ b/_docs/0001-DRILL-2720.patch @@ -0,0 +1,375 @@ +From ec19736742e2a66a5cf4dfe7779af2b46f6e7117 Mon Sep 17 00:00:00 2001 +From: Kristine Hahn <kh...@maprtech.com> +Date: Wed, 8 Apr 2015 15:51:59 -0700 +Subject: [PATCH] DRILL-2720 + +--- + _docs/connect/009-mapr-db-plugin.md | 2 +- + _docs/manage/conf/001-mem-alloc.md | 88 ++++++++++++------------------- + _docs/sql-ref/004-functions.md | 2 +- + _docs/sql-ref/functions/001-math.md | 2 +- + _docs/sql-ref/functions/002-conversion.md | 14 ++--- + 5 files changed, 43 insertions(+), 65 deletions(-) + +diff --git a/_docs/connect/009-mapr-db-plugin.md b/_docs/connect/009-mapr-db-plugin.md +index bc06144..66d2a81 100644 +--- a/_docs/connect/009-mapr-db-plugin.md ++++ b/_docs/connect/009-mapr-db-plugin.md +@@ -2,7 +2,7 @@ + title: "MapR-DB Format" + parent: "Connect to a Data Source" + --- +-Drill includes a `maprdb` format plugin for handling MapR-DB and HBase data. The Drill Sandbox also includes the following `maprdb` format plugin on a MapR node: ++Drill includes a `maprdb` format plugin for accessing data stored in MapR-DB. The Drill Sandbox also includes the following `maprdb` format plugin on a MapR node: + + { + "type": "hbase", +diff --git a/_docs/manage/conf/001-mem-alloc.md b/_docs/manage/conf/001-mem-alloc.md +index 5d99015..df60b7f 100644 +--- a/_docs/manage/conf/001-mem-alloc.md ++++ b/_docs/manage/conf/001-mem-alloc.md +@@ -2,7 +2,7 @@ + title: "Overview" + parent: "Configuration Options" + --- +-The sys.options table in Drill contains information about boot and system options described in the following tables. You configure some of the options to tune performance. You can configure the options using the ALTER SESSION or ALTER SYSTEM command. ++The sys.options table in Drill contains information about boot and system options listed in the following tables. To tune performance, you adjust some of the options to suit your application. Configure the options using the ALTER SESSION or ALTER SYSTEM command. + + ## Boot Options + +@@ -10,7 +10,7 @@ The sys.options table in Drill contains information about boot and system option + <tr> + <th>Name</th> + <th>Default</th> +- <th>Description</th> ++ <th>Comments</th> + </tr> + <tr> + <td>drill.exec.buffer.impl</td> +@@ -128,9 +128,9 @@ The sys.options table in Drill contains information about boot and system option + + <table> + <tr> +- <th>name</th> ++ <th>Name</th> + <th>Default</th> +- <th>Description</th> ++ <th>Comments</th> + </tr> + <tr> + <td>drill.exec.functions.cast_empty_string_to_null</td> +@@ -140,12 +140,7 @@ The sys.options table in Drill contains information about boot and system option + <tr> + <td>drill.exec.storage.file.partition.column.label</td> + <td>dir</td> +- <td></td> +- </tr> +- <tr> +- <td>drill.exec.testing.exception-injections</td> +- <td></td> +- <td></td> ++ <td>Accepts a string input.</td> + </tr> + <tr> + <td>exec.errors.verbose</td> +@@ -155,27 +150,27 @@ The sys.options table in Drill contains information about boot and system option + <tr> + <td>exec.java_compiler</td> + <td>DEFAULT</td> +- <td></td> ++ <td>Switches between DEFAULT, JDK, and JANINO mode for the current session. Uses Janino by default for generated source code of less than exec.java_compiler_janino_maxsize; otherwise, switches to the JDK compiler.</td> + </tr> + <tr> + <td>exec.java_compiler_debug</td> + <td>TRUE</td> +- <td></td> ++ <td>Toggles the output of debug-level compiler error messages in runtime generated code.</td> + </tr> + <tr> + <td>exec.java_compiler_janino_maxsize</td> + <td>262144</td> +- <td></td> ++ <td>See the exec.java_compiler option comment. Accepts inputs of type LONG.</td> + </tr> + <tr> + <td>exec.max_hash_table_size</td> + <td>1073741824</td> +- <td>Starting size for hash tables. Increase according to available memory to improve performance.</td> ++ <td>Ending size for hash tables. Range: 0 - 1073741824</td> + </tr> + <tr> + <td>exec.min_hash_table_size</td> + <td>65536</td> +- <td></td> ++ <td>Starting size for hash tables. Increase according to available memory to improve performance. Range: 0 - 1073741824</td> + </tr> + <tr> + <td>exec.queue.enable</td> +@@ -185,27 +180,22 @@ The sys.options table in Drill contains information about boot and system option + <tr> + <td>exec.queue.large</td> + <td>10</td> +- <td></td> ++ <td>Range: 0-1000</td> + </tr> + <tr> + <td>exec.queue.small</td> + <td>100</td> +- <td></td> ++ <td>Range: 0-1001</td> + </tr> + <tr> + <td>exec.queue.threshold</td> + <td>30000000</td> +- <td></td> ++ <td>Range: 0-9223372036854775807</td> + </tr> + <tr> + <td>exec.queue.timeout_millis</td> + <td>300000</td> +- <td></td> +- </tr> +- <tr> +- <td>org.apache.drill.exec.compile.ClassTransformer.scalar_replacement</td> +- <td>try</td> +- <td></td> ++ <td>Range: 0-9223372036854775807</td> + </tr> + <tr> + <td>planner.add_producer_consumer</td> +@@ -215,7 +205,7 @@ The sys.options table in Drill contains information about boot and system option + <tr> + <td>planner.affinity_factor</td> + <td>1.2</td> +- <td></td> ++ <td>Accepts inputs of type DOUBLE.</td> + </tr> + <tr> + <td>planner.broadcast_factor</td> +@@ -225,22 +215,22 @@ The sys.options table in Drill contains information about boot and system option + <tr> + <td>planner.broadcast_threshold</td> + <td>10000000</td> +- <td></td> ++ <td>Threshold in number of rows that triggers a broadcast join for a query if the right side of the join contains fewer rows than the threshold. Avoids broadcasting too many rows to join. Range: 0-2147483647</td> + </tr> + <tr> + <td>planner.disable_exchanges</td> + <td>FALSE</td> +- <td></td> ++ <td>Toggles the state of hashing to a random exchange.</td> + </tr> + <tr> + <td>planner.enable_broadcast_join</td> + <td>TRUE</td> +- <td></td> ++ <td>Changes the state of aggregation and join operators. Do not disable.</td> + </tr> + <tr> + <td>planner.enable_demux_exchange</td> + <td>FALSE</td> +- <td></td> ++ <td>Toggles the state of hashing to a demulitplexed exchange.</td> + </tr> + <tr> + <td>planner.enable_hash_single_key</td> +@@ -250,12 +240,12 @@ The sys.options table in Drill contains information about boot and system option + <tr> + <td>planner.enable_hashagg</td> + <td>TRUE</td> +- <td></td> ++ <td>Enable hash aggregation; otherwise, Drill does a sort-based aggregation. Does not write to disk. Enable is recommended.</td> + </tr> + <tr> + <td>planner.enable_hashjoin</td> + <td>TRUE</td> +- <td></td> ++ <td>Enable the memory hungry hash join. Does not write to disk.</td> + </tr> + <tr> + <td>planner.enable_hashjoin_swap</td> +@@ -265,7 +255,7 @@ The sys.options table in Drill contains information about boot and system option + <tr> + <td>planner.enable_mergejoin</td> + <td>TRUE</td> +- <td></td> ++ <td>Sort-based operation. Writes to disk.</td> + </tr> + <tr> + <td>planner.enable_multiphase_agg</td> +@@ -275,12 +265,12 @@ The sys.options table in Drill contains information about boot and system option + <tr> + <td>planner.enable_mux_exchange</td> + <td>TRUE</td> +- <td></td> ++ <td>Toggles the state of hashing to a multiplexed exchange.</td> + </tr> + <tr> + <td>planner.enable_streamagg</td> + <td>TRUE</td> +- <td></td> ++ <td>Sort-based operation. Writes to disk.</td> + </tr> + <tr> + <td>planner.identifier_max_length</td> +@@ -325,7 +315,7 @@ The sys.options table in Drill contains information about boot and system option + <tr> + <td>planner.memory.non_blocking_operators_memory</td> + <td>64</td> +- <td></td> ++ <td>Range: 0-2048</td> + </tr> + <tr> + <td>planner.partitioner_sender_max_threads</td> +@@ -345,27 +335,27 @@ The sys.options table in Drill contains information about boot and system option + <tr> + <td>planner.producer_consumer_queue_size</td> + <td>10</td> +- <td></td> ++ <td>How much data to prefetch from disk (in record batches) out of band of query execution</td> + </tr> + <tr> + <td>planner.slice_target</td> + <td>100000</td> +- <td></td> ++ <td>The number of records manipulated within a fragment before Drill parallelizes operations.</td> + </tr> + <tr> + <td>planner.width.max_per_node</td> + <td>3</td> +- <td></td> ++ <td>The maximum degree of distribution of a query across cores and cluster nodes.</td> + </tr> + <tr> + <td>planner.width.max_per_query</td> + <td>1000</td> +- <td></td> ++ <td>Same as planner but applies to the query as executed by the entire cluster.</td> + </tr> + <tr> + <td>store.format</td> + <td>parquet</td> +- <td></td> ++ <td>Output format for data written to tables with the CREATE TABLE AS (CTAS) command. Allowed values are parquet, json, or text. Allowed values: 0, -1, 1000000</td> + </tr> + <tr> + <td>store.json.all_text_mode</td> +@@ -375,17 +365,17 @@ The sys.options table in Drill contains information about boot and system option + <tr> + <td>store.mongo.all_text_mode</td> + <td>FALSE</td> +- <td></td> ++ <td>Similar to store.json.all_text_mode for MongoDB.</td> + </tr> + <tr> + <td>store.parquet.block-size</td> + <td>536870912</td> +- <td></td> ++ <td>Sets the size of a Parquet row group to the number of bytes less than or equal to the block size of MFS, HDFS, or the file system.</td> + </tr> + <tr> + <td>store.parquet.compression</td> + <td>snappy</td> +- <td></td> ++ <td>Compression type for storing Parquet output. Allowed values: snappy, gzip, none</td> + </tr> + <tr> + <td>store.parquet.enable_dictionary_encoding</td> +@@ -398,22 +388,14 @@ The sys.options table in Drill contains information about boot and system option + <td></td> + </tr> + <tr> +- <td>store.parquet.vector_fill_check_threshold</td> +- <td>10</td> +- <td></td> +- </tr> +- <tr> +- <td>store.parquet.vector_fill_threshold</td> +- <td>85</td> +- <td></td> +- </tr> +- <tr> + <td>window.enable</td> + <td>FALSE</td> + <td></td> + </tr> + </table> + ++## Memory Allocation ++ + You can configure the amount of direct memory allocated to a Drillbit for + query processing. The default limit is 8G, but Drill prefers 16G or more + depending on the workload. The total amount of direct memory that a Drillbit +diff --git a/_docs/sql-ref/004-functions.md b/_docs/sql-ref/004-functions.md +index a076920..2f1ee0b 100644 +--- a/_docs/sql-ref/004-functions.md ++++ b/_docs/sql-ref/004-functions.md +@@ -12,4 +12,4 @@ You can use the following types of functions in your Drill queries: + * [Nested Data](/docs/nested-data-functions/) + * [Functions for Handling Nulls](/docs/functions-for-handling-nulls) + +- ++You need to use a FROM clause in Drill queries. Examples in this documentation often use `FROM sys.version` in the query for example purposes. +diff --git a/_docs/sql-ref/functions/001-math.md b/_docs/sql-ref/functions/001-math.md +index 718998a..4695e32 100644 +--- a/_docs/sql-ref/functions/001-math.md ++++ b/_docs/sql-ref/functions/001-math.md +@@ -158,7 +158,7 @@ Exceptions are the LSHIFT and RSHIFT functions, which take all types except the + + Examples in this section use the `input2.json` file. Download the `input2.json` file from the [Drill source code](https://github.com/apache/drill/tree/master/exec/java-exec/src/test/resources/jsoninput) page. + +-You need to use a FROM clause in Drill queries. This document often uses the sys.version table in the FROM clause of the query for example purposes. ++You need to use a FROM clause in Drill queries. In addition to using `input2.json`, examples in this documentation often use `FROM sys.version` in the query for example purposes. + + #### ABS Example + Get the absolute value of the integer key in `input2.json`. The following snippet of input2.json shows the relevant integer content: +diff --git a/_docs/sql-ref/functions/002-conversion.md b/_docs/sql-ref/functions/002-conversion.md +index 875de69..780b397 100644 +--- a/_docs/sql-ref/functions/002-conversion.md ++++ b/_docs/sql-ref/functions/002-conversion.md +@@ -10,7 +10,7 @@ Drill supports the following functions for casting and converting data types: + + ## CAST + +-The CAST function converts an entity having a single data value, such as a column name, from one type to another. ++The CAST function converts an expression from one type to another. + + ### Syntax + +@@ -18,7 +18,7 @@ The CAST function converts an entity having a single data value, such as a colum + + *expression* + +-An entity that evaluates to one or more values, such as a column name or literal ++A combination of one or more values, operators, and SQL functions that evaluate to a value + + *data type* + +@@ -381,13 +381,9 @@ Currently Drill does not support conversion of a date, time, or timestamp from o + +------------+ + 1 row selected (1.199 seconds) + +-2. Configure the default time zone format in the drill-override.conf. For example: ++2. Configure the default time zone format in <drill installation directory>/conf/drill-env.sh by adding `-Duser.timezone=UTC` to DRILL_JAVA_OPTS. For example: + +- drill.exec: { +- cluster-id: âxyz", +- zk.connect: âabc:5181", +- user.timezone: "UTC" +- } ++ export DRILL_JAVA_OPTS="-Xms1G -Xmx$DRILL_MAX_HEAP -XX:MaxDirectMemorySize=$DRILL_MAX_DIRECT_MEMORY -XX:MaxPermSize=512M -XX:ReservedCodeCacheSize=1G -ea -Duser.timezone=UTC" + + 3. Restart sqlline. + +@@ -416,7 +412,7 @@ TO_NUMBER(text, format)| numeric + TO_TIMESTAMP(text, format)| timestamp + TO_TIMESTAMP(double precision)| timestamp + +-Use the âzâ option to identify the time zone in TO_TIMESTAMP to make sure the timestamp has the timezone in it. Also, use the âzâ option to identify the time zone in a timestamp using the TO_CHAR function. For example: ++You can use the âzâ option to identify the time zone in TO_TIMESTAMP to make sure the timestamp has the timezone in it. Also, use the âzâ option to identify the time zone in a timestamp using the TO_CHAR function. For example: + + SELECT TO_TIMESTAMP('2015-03-30 20:49:59.0 UTC', 'YYYY-MM-dd HH:mm:ss.s z') AS Original, + TO_CHAR(TO_TIMESTAMP('2015-03-30 20:49:59.0 UTC', 'YYYY-MM-dd HH:mm:ss.s z'), 'z') AS TimeZone +-- +1.9.5 (Apple Git-50.3) + http://git-wip-us.apache.org/repos/asf/drill/blob/d3328217/_docs/img/loginSandBox.png ---------------------------------------------------------------------- diff --git a/_docs/img/loginSandBox.png b/_docs/img/loginSandBox.png index 30f73b2..5727ea4 100644 Binary files a/_docs/img/loginSandBox.png and b/_docs/img/loginSandBox.png differ http://git-wip-us.apache.org/repos/asf/drill/blob/d3328217/_docs/img/vbApplSettings.png ---------------------------------------------------------------------- diff --git a/_docs/img/vbApplSettings.png b/_docs/img/vbApplSettings.png index 2f7451b..a8050ab 100644 Binary files a/_docs/img/vbApplSettings.png and b/_docs/img/vbApplSettings.png differ http://git-wip-us.apache.org/repos/asf/drill/blob/d3328217/_docs/img/vbGenSettings.png ---------------------------------------------------------------------- diff --git a/_docs/img/vbGenSettings.png b/_docs/img/vbGenSettings.png index cae235f..9a4451d 100644 Binary files a/_docs/img/vbGenSettings.png and b/_docs/img/vbGenSettings.png differ http://git-wip-us.apache.org/repos/asf/drill/blob/d3328217/_docs/img/vbImport.png ---------------------------------------------------------------------- diff --git a/_docs/img/vbImport.png b/_docs/img/vbImport.png index e2f6cfe..a8ed45b 100644 Binary files a/_docs/img/vbImport.png and b/_docs/img/vbImport.png differ http://git-wip-us.apache.org/repos/asf/drill/blob/d3328217/_docs/img/vbNetwork.png ---------------------------------------------------------------------- diff --git a/_docs/img/vbNetwork.png b/_docs/img/vbNetwork.png index bbc1c7a..cbb36f1 100644 Binary files a/_docs/img/vbNetwork.png and b/_docs/img/vbNetwork.png differ http://git-wip-us.apache.org/repos/asf/drill/blob/d3328217/_docs/img/vbloginSandBox.png ---------------------------------------------------------------------- diff --git a/_docs/img/vbloginSandBox.png b/_docs/img/vbloginSandBox.png index 69c31ab..5012f7d 100644 Binary files a/_docs/img/vbloginSandBox.png and b/_docs/img/vbloginSandBox.png differ http://git-wip-us.apache.org/repos/asf/drill/blob/d3328217/_docs/img/vmLibrary.png ---------------------------------------------------------------------- diff --git a/_docs/img/vmLibrary.png b/_docs/img/vmLibrary.png index c0b97a3..b3f2fd2 100644 Binary files a/_docs/img/vmLibrary.png and b/_docs/img/vmLibrary.png differ http://git-wip-us.apache.org/repos/asf/drill/blob/d3328217/_docs/img/vmShare.png ---------------------------------------------------------------------- diff --git a/_docs/img/vmShare.png b/_docs/img/vmShare.png index 16ef052..803ffaf 100644 Binary files a/_docs/img/vmShare.png and b/_docs/img/vmShare.png differ http://git-wip-us.apache.org/repos/asf/drill/blob/d3328217/_docs/manage/conf/001-mem-alloc.md ---------------------------------------------------------------------- diff --git a/_docs/manage/conf/001-mem-alloc.md b/_docs/manage/conf/001-mem-alloc.md index df60b7f..608eef1 100644 --- a/_docs/manage/conf/001-mem-alloc.md +++ b/_docs/manage/conf/001-mem-alloc.md @@ -1,5 +1,5 @@ --- -title: "Overview" +title: "Configuration Options Overview" parent: "Configuration Options" --- The sys.options table in Drill contains information about boot and system options listed in the following tables. To tune performance, you adjust some of the options to suit your application. Configure the options using the ALTER SESSION or ALTER SYSTEM command. @@ -64,7 +64,7 @@ The sys.options table in Drill contains information about boot and system option </tr> <tr> <td>drill.exec.sys.store.provider.class</td> - <td>"org.apache.drill.exec.store.sys.zk.ZkPStoreProvider"</td> + <td>ZooKeeper: "org.apache.drill.exec.store.sys.zk.ZkPStoreProvider"</td> <td>The Pstore (Persistent Configuration Storage) provider to use. The Pstore holds configuration and profile data.</td> </tr> <tr> @@ -358,7 +358,7 @@ The sys.options table in Drill contains information about boot and system option <td>Output format for data written to tables with the CREATE TABLE AS (CTAS) command. Allowed values are parquet, json, or text. Allowed values: 0, -1, 1000000</td> </tr> <tr> - <td>store.json.all_text_mode</td> + <td><a href="/docs/json-data-model#handling-type-differences">store.json.all_text_mode</a></td> <td>FALSE</td> <td>Drill reads all data from the JSON files as VARCHAR. Prevents schema change errors.</td> </tr> @@ -368,7 +368,7 @@ The sys.options table in Drill contains information about boot and system option <td>Similar to store.json.all_text_mode for MongoDB.</td> </tr> <tr> - <td>store.parquet.block-size</td> + <td><a href="/docs/parquet-format#configuring-the-size-of-parquet-files">store.parquet.block-size</a></td> <td>536870912</td> <td>Sets the size of a Parquet row group to the number of bytes less than or equal to the block size of MFS, HDFS, or the file system.</td> </tr> http://git-wip-us.apache.org/repos/asf/drill/blob/d3328217/_docs/manage/conf/002-startup-opt.md ---------------------------------------------------------------------- diff --git a/_docs/manage/conf/002-startup-opt.md b/_docs/manage/conf/002-startup-opt.md index e0b64bf..778957b 100644 --- a/_docs/manage/conf/002-startup-opt.md +++ b/_docs/manage/conf/002-startup-opt.md @@ -43,7 +43,21 @@ You can run the following query to see a list of Drillâs startup options: You can configure start-up options for each Drillbit in the `drill- override.conf` file located in Drillâs` /conf` directory. -You may want to configure the following start-up options that control certain -behaviors in Drill: +The summary of start-up options, also known as boot options, lists default values. The following descriptions provide more detail on key options that are frequently reconfigured: + +* drill.exec.sys.store.provider.class + + Defines the persistent storage (PStore) provider. The [PStore](/docs/persistent-configuration-storage) holds configuration and profile data. + +* drill.exec.buffer.size + + Defines the amount of memory available, in terms of record batches, to hold data on the downstream side of an operation. Drill pushes data downstream as quickly as possible to make data immediately available. This requires Drill to use memory to hold the data pending operations. When data on a downstream operation is required, that data is immediately available so Drill does not have to go over the network to process it. Providing more memory to this option increases the speed at which Drill completes a query. + +* drill.exec.sort.external.spill.directories + + Tells Drill which directory to use when spooling. Drill uses a spool and sort operation for beyond memory operations. The sorting operation is designed to spool to a Hadoop file system. The default Hadoop file system is a local file system in the /tmp directory. Spooling performance (both writing and reading back from it) is constrained by the file system. For MapR clusters, use MapReduce volumes or set up local volumes to use for spooling purposes. Volumes improve performance and stripe data across as many disks as possible. + + +* drill.exec.zk.connect + Provides Drill with the ZooKeeper quorum to use to connect to data sources. Change this setting to point to the ZooKeeper quorum that you want Drill to use. You must configure this option on each Drillbit node. -<table ><tbody><tr><th >Option</th><th >Default Value</th><th >Description</th></tr><tr><td valign="top" >drill.exec.sys.store.provider</td><td valign="top" >ZooKeeper</td><td valign="top" >Defines the persistent storage (PStore) provider. The PStore holds configuration and profile data. For more information about PStores, see <a href="/docs/persistent-configuration-storage" rel="nofollow">Persistent Configuration Storage</a>.</td></tr><tr><td valign="top" >drill.exec.buffer.size</td><td valign="top" > </td><td valign="top" >Defines the amount of memory available, in terms of record batches, to hold data on the downstream side of an operation. Drill pushes data downstream as quickly as possible to make data immediately available. This requires Drill to use memory to hold the data pending operations. When data on a downstream operation is required, that data is immediately available so Drill does not have to go over the network to process it. Providing more memory to this option incr eases the speed at which Drill completes a query.</td></tr><tr><td valign="top" >drill.exec.sort.external.directoriesdrill.exec.sort.external.fs</td><td valign="top" > </td><td valign="top" >These options control spooling. The drill.exec.sort.external.directories option tells Drill which directory to use when spooling. The drill.exec.sort.external.fs option tells Drill which file system to use when spooling beyond memory files. <span style="line-height: 1.4285715;background-color: transparent;"> </span>Drill uses a spool and sort operation for beyond memory operations. The sorting operation is designed to spool to a Hadoop file system. The default Hadoop file system is a local file system in the /tmp directory. Spooling performance (both writing and reading back from it) is constrained by the file system. <span style="line-height: 1.4285715;background-color: transparent;"> </span>For MapR clusters, use MapReduce volumes or set up local volumes to use for spooling purposes. Volumes i mprove performance and stripe data across as many disks as possible.</td></tr><tr><td valign="top" colspan="1" >drill.exec.debug.error_on_leak</td><td valign="top" colspan="1" >True</td><td valign="top" colspan="1" >Determines how Drill behaves when memory leaks occur during a query. By default, this option is enabled so that queries fail when memory leaks occur. If you disable the option, Drill issues a warning when a memory leak occurs and completes the query.</td></tr><tr><td valign="top" colspan="1" >drill.exec.zk.connect</td><td valign="top" colspan="1" >localhost:2181</td><td valign="top" colspan="1" >Provides Drill with the ZooKeeper quorum to use to connect to data sources. Change this setting to point to the ZooKeeper quorum that you want Drill to use. You must configure this option on each Drillbit node.</td></tr><tr><td valign="top" colspan="1" >drill.exec.cluster-id</td><td valign="top" colspan="1" >my_drillbit_cluster</td><td valign="top" colspan="1" >Identifies the clu ster that corresponds with the ZooKeeper quorum indicated. It also provides Drill with the name of the cluster used during UDP multicast. You must change the default cluster-id if there are multiple clusters on the same subnet. If you do not change the ID, the clusters will try to connect to each other to create one cluster.</td></tr></tbody></table></div> http://git-wip-us.apache.org/repos/asf/drill/blob/d3328217/_docs/manage/conf/003-plan-exec.md ---------------------------------------------------------------------- diff --git a/_docs/manage/conf/003-plan-exec.md b/_docs/manage/conf/003-plan-exec.md index 56a1f69..40cd266 100644 --- a/_docs/manage/conf/003-plan-exec.md +++ b/_docs/manage/conf/003-plan-exec.md @@ -21,16 +21,42 @@ Use the` ALTER SYSTEM` or `ALTER SESSION` commands to set options. Typically, you set the options at the session level unless you want the setting to persist across all sessions. -The following table contains planning and execution options that you can set -at the system or session level: - -<table ><tbody><tr><th >Option name</th><th >Default value</th><th >Description</th></tr><tr><td valign="top" colspan="1" >exec.errors.verbose</td><td valign="top" colspan="1" ><p>false</p></td><td valign="top" colspan="1" ><p>This option enables or disables the verbose message that Drill returns when a query fails. When enabled, Drill provides additional information about failed queries.</p></td></tr><tr><td valign="top" colspan="1" ><span>exec.max_hash_table_size</span></td><td valign="top" colspan="1" >1073741824</td><td valign="top" colspan="1" ><span>The default maximum size for hash tables.</span></td></tr><tr><td valign="top" colspan="1" >exec.min_hash_table_size</td><td valign="top" colspan="1" >65536</td><td valign="top" colspan="1" >The default starting size for hash tables. Increasing this size is useful for very large aggregations or joins when you have large amounts of memory for Drill to use. Drill can spend a lot of time resizing the hash table as it finds new data. I f you have large data sets, you can increase this hash table size to increase performance.</td></tr><tr><td valign="top" colspan="1" >planner.add_producer_consumer</td><td valign="top" colspan="1" ><p>false</p><p> </p></td><td valign="top" colspan="1" ><p>This option enables or disables a secondary reading thread that works out of band of the rest of the scanning fragment to prefetch data from disk. <span style="line-height: 1.4285715;background-color: transparent;">If you interact with a certain type of storage medium that is slow or does not prefetch much data, this option tells Drill to add a producer consumer reading thread to the operation. Drill can then assign one thread that focuses on a single reading fragment. </span></p><p>If Drill is using memory, you can disable this option to get better performance. If Drill is using disk space, you should enable this option and set a reasonable queue size for the planner.producer_consumer_queue_size option.</p></td></tr><tr><td valign ="top" colspan="1" >planner.broadcast_threshold</td><td valign="top" colspan="1" >1000000</td><td valign="top" colspan="1" ><span style="color: rgb(34,34,34);">Threshold, in terms of a number of rows, that determines whether a broadcast join is chosen for a query. Regardless of the setting of the broadcast_join option (enabled or disabled), a broadcast join is not chosen unless the right side of the join is estimated to contain fewer rows than this threshold. The intent of this option is to avoid broadcasting too many rows for join purposes. Broadcasting involves sending data across nodes and is a network-intensive operation. (The "right side" of the join, which may itself be a join or simply a table, is determined by cost-based optimizations and heuristics during physical planning.)</span></td></tr><tr><td valign="top" colspan="1" ><p>planner.enable_broadcast_join<br />planner.enable_hashagg<br />planner.enable_hashjoin<br />planner.enable_mergejoin<br />planner.enable_mu ltiphase_agg<br />planner.enable_streamagg</p></td><td valign="top" colspan="1" >true</td><td valign="top" colspan="1" ><p>These options enable or disable specific aggregation and join operators for queries. These operators are all enabled by default and in general should not be disabled.</p><p>Hash aggregation and hash join are hash-based operations. Streaming aggregation and merge join are sort-based operations. Both hash-based and sort-based operations consume memory; however, currently, hash-based operations do not spill to disk as needed, but the sort-based operations do. If large hash operations do not fit in memory on your system, you may need to disable these operations. Queries will continue to run, using alternative plans.</p></td></tr><tr><td valign="top" colspan="1" >planner.producer_consumer_queue_size</td><td valign="top" colspan="1" >10</td><td valign="top" colspan="1" >Determines how much data to prefetch from disk (in record batches) out of band of query execution. The larger the queue size, the greater the amount of memory that the queue and overall query execution consumes.</td></tr><tr><td valign="top" colspan="1" >planner.slice_target</td><td valign="top" colspan="1" >100000</td><td valign="top" colspan="1" >The number of records manipulated within a fragment before Drill parallelizes them.</td></tr><tr><td valign="top" colspan="1" ><p>planner.width.max_per_node</p><p> </p></td><td valign="top" colspan="1" ><p>The default depends on the number of cores on each node.</p></td><td valign="top" colspan="1" ><p>In this context "width" refers to fanout or distribution potential: the ability to run a query in parallel across the cores on a node and the nodes on a cluster.</p><p><span>A physical plan consists of intermediate operations, known as query "fragments," that run concurrently, yielding opportunities for parallelism above and below each exchange operator in the plan. An exchange operator represents a breakpoint in the execution flow where processing can be distributed. For example, a single-process scan of a file may flow into an exchange operator, followed by a multi-process aggregation fragment.</span><span> </span></p><p>The maximum width per node defines the maximum degree of parallelism for any fragment of a query, but the setting applies at the level of a single node in the cluster.</p><p>The <em>default</em> maximum degree of parallelism per node is calculated as follows, with the theoretical maximum automatically scaled back (and rounded down) so that only 70% of the actual available capacity is taken into account:</p> -<script type="syntaxhighlighter" class="theme: Default; brush: java; gutter: false"><![CDATA[number of active drillbits (typically one per node) -* number of cores per node -* 0.7]]></script> -<p>For example, on a single-node test system with 2 cores and hyper-threading enabled:</p><script type="syntaxhighlighter" class="theme: Default; brush: java; gutter: false"><![CDATA[1 * 4 * 0.7 = 3]]></script> -<p>When you modify the default setting, you can supply any meaningful number. The system does not automatically scale down your setting.</p></td></tr><tr><td valign="top" colspan="1" >planner.width.max_per_query</td><td valign="top" colspan="1" >1000</td><td valign="top" colspan="1" ><p>The max_per_query value also sets the maximum degree of parallelism for any given stage of a query, but the setting applies to the query as executed by the whole cluster (multiple nodes). In effect, the actual maximum width per query is the <em>minimum of two values</em>:</p> -<script type="syntaxhighlighter" class="theme: Default; brush: java; gutter: false"><![CDATA[min((number of nodes * width.max_per_node), width.max_per_query)]]></script> -<p>For example, on a 4-node cluster where <span><code>width.max_per_node</code> is set to 6 and </span><span><code>width.max_per_query</code> is set to 30:</span></p> -<script type="syntaxhighlighter" class="theme: Default; brush: java; gutter: false"><![CDATA[min((4 * 6), 30) = 24]]></script> -<p>In this case, the effective maximum width per query is 24, not 30.</p></td></tr><tr><td valign="top" colspan="1" >store.format</td><td valign="top" colspan="1" > </td><td valign="top" colspan="1" >Output format for data that is written to tables with the CREATE TABLE AS (CTAS) command.</td></tr><tr><td valign="top" colspan="1" >store.json.all_text_mode</td><td valign="top" colspan="1" ><p>false</p></td><td valign="top" colspan="1" ><p>This option enables or disables text mode. When enabled, 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.</p></td></tr><tr><td valign="top" >store.parquet.block-size</td><td valign="top" ><p>536870912</p></td><td valign="top" >T<span style="color: rgb(34,34,34);">arget size for a parquet row group, which should be equal to or less than the configured HDFS block size. </span></td></tr></tbody></table> \ No newline at end of file +The summary of system options lists default values. The following descriptions provide more detail on some of these options: + +* exec.min_hash_table_size + + The default starting size for hash tables. Increasing this size is useful for very large aggregations or joins when you have large amounts of memory for Drill to use. Drill can spend a lot of time resizing the hash table as it finds new data. If you have large data sets, you can increase this hash table size to increase performance. + +* planner.add_producer_consumer + + This option enables or disables a secondary reading thread that works out of band of the rest of the scanning fragment to prefetch data from disk. If you interact with a certain type of storage medium that is slow or does not prefetch much data, this option tells Drill to add a producer consumer reading thread to the operation. Drill can then assign one thread that focuses on a single reading fragment. If Drill is using memory, you can disable this option to get better performance. If Drill is using disk space, you should enable this option and set a reasonable queue size for the planner.producer_consumer_queue_size option. + +* planner.broadcast_threshold + + Threshold, in terms of a number of rows, that determines whether a broadcast join is chosen for a query. Regardless of the setting of the broadcast_join option (enabled or disabled), a broadcast join is not chosen unless the right side of the join is estimated to contain fewer rows than this threshold. The intent of this option is to avoid broadcasting too many rows for join purposes. Broadcasting involves sending data across nodes and is a network-intensive operation. (The "right side" of the join, which may itself be a join or simply a table, is determined by cost-based optimizations and heuristics during physical planning.) + +* planner.enable_broadcast_join, planner.enable_hashagg, planner.enable_hashjoin, planner.enable_mergejoin, planner.enable_multiphase_agg, planner.enable_streamagg + + These options enable or disable specific aggregation and join operators for queries. These operators are all enabled by default and in general should not be disabled.</p><p>Hash aggregation and hash join are hash-based operations. Streaming aggregation and merge join are sort-based operations. Both hash-based and sort-based operations consume memory; however, currently, hash-based operations do not spill to disk as needed, but the sort-based operations do. If large hash operations do not fit in memory on your system, you may need to disable these operations. Queries will continue to run, using alternative plans. + +* planner.producer_consumer_queue_size + + Determines how much data to prefetch from disk (in record batches) out of band of query execution. The larger the queue size, the greater the amount of memory that the queue and overall query execution consumes. + +* planner.width.max_per_node + + In this context *width* refers to fanout or distribution potential: the ability to run a query in parallel across the cores on a node and the nodes on a cluster. A physical plan consists of intermediate operations, known as query "fragments," that run concurrently, yielding opportunities for parallelism above and below each exchange operator in the plan. An exchange operator represents a breakpoint in the execution flow where processing can be distributed. For example, a single-process scan of a file may flow into an exchange operator, followed by a multi-process aggregation fragment. + + The maximum width per node defines the maximum degree of parallelism for any fragment of a query, but the setting applies at the level of a single node in the cluster. The *default* maximum degree of parallelism per node is calculated as follows, with the theoretical maximum automatically scaled back (and rounded down) so that only 70% of the actual available capacity is taken into account: number of active drillbits (typically one per node) * number of cores per node * 0.7 + + For example, on a single-node test system with 2 cores and hyper-threading enabled: 1 * 4 * 0.7 = 3 + + When you modify the default setting, you can supply any meaningful number. The system does not automatically scale down your setting. + +* planner.width.max_per_query + + The max_per_query value also sets the maximum degree of parallelism for any given stage of a query, but the setting applies to the query as executed by the whole cluster (multiple nodes). In effect, the actual maximum width per query is the *minimum of two values*: min((number of nodes * width.max_per_node), width.max_per_query) + + For example, on a 4-node cluster where `width.max_per_node` is set to 6 and `width.max_per_query` is set to 30: min((4 * 6), 30) = 24 + + In this case, the effective maximum width per query is 24, not 30. \ No newline at end of file http://git-wip-us.apache.org/repos/asf/drill/blob/d3328217/_docs/manage/conf/004-persist-conf.md ---------------------------------------------------------------------- diff --git a/_docs/manage/conf/004-persist-conf.md b/_docs/manage/conf/004-persist-conf.md index 3f11906..fc2dada 100644 --- a/_docs/manage/conf/004-persist-conf.md +++ b/_docs/manage/conf/004-persist-conf.md @@ -66,10 +66,6 @@ override.conf.` ## MapR-DB for Persistent Configuration Storage -The MapR-DB plugin will be released soon. You can [compile Drill from -source](/docs/compiling-drill-from-source) to try out this -new feature. - If you have MapR-DB in your cluster, you can use MapR-DB for persistent configuration storage. Using MapR-DB to store persistent configuration data can prevent memory strain on ZooKeeper in clusters running heavy workloads. http://git-wip-us.apache.org/repos/asf/drill/blob/d3328217/_docs/sql-ref/001-data-types.md ---------------------------------------------------------------------- diff --git a/_docs/sql-ref/001-data-types.md b/_docs/sql-ref/001-data-types.md index 88e0177..689dbf7 100644 --- a/_docs/sql-ref/001-data-types.md +++ b/_docs/sql-ref/001-data-types.md @@ -2,11 +2,95 @@ title: "Data Types" parent: "SQL Reference" --- -Depending on the data format, you might need to cast or convert data types when Drill reads/writes data. -After Drill reads schema-less data into SQL tables, you need to cast data types explicitly to query the data. In some cases, Drill converts schema-less data to typed data implicitly. In this case, you do not need to cast. The file format of the data and the nature of your query determines the requirement for casting or converting. +## Supported Data Types -Differences in casting depend on the data source. The following list describes how Drill treats data types from various data sources: +Drill supports the following SQL data types: + +* BIGINT + 8-byte signed integer in the range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. + +* BINARY + Variable-length byte string + +* BOOLEAN + True or false + +* DATE + Years, months, and days in YYYY-MM-DD format since 4713 BC. + +* DECIMAL(p,s), or DEC(p,s), NUMERIC(p,s) + 38-digit precision number, precision is p, and scale is s. Example: DECIMAL(6,2) has 4 digits before the decimal point and 2 digits after the decimal point. + +* FLOAT + 4-byte floating point number + +* DOUBLE, DOUBLE PRECISION** + 8-byte floating point number, precision-scalable + +* INTEGER or INT + 4-byte signed integer in the range -2,147,483,648 to 2,147,483,647 + +* INTERVALDAY + A simple version of the interval type expressing a period of time in days, hours, minutes, and seconds only + +* INTERVALYEAR + A simple version of interval representing a period of time in years and months only + +* SMALLINT* + 2-byte signed integer in the range -32,768 to 32,767 + +* TIME + 24-hour based time before or after January 1, 2001 in hours, minutes, seconds format: HH:mm:ss + +* TIMESTAMP + JDBC timestamp in year, month, date hour, minute, second, and optional milliseconds format: yyyy-MM-dd HH:mm:ss.SSS + +* CHARACTER VARYING, CHARACTER, CHAR, or VARCHAR + UTF8-encoded variable-length string. For example, CHAR(30) casts data to a 30-character string maximum. The default limit is 1 character. The maximum character limit is 255. + +\* Not currently supported. +\*\* You specify a DECIMAL using a precision and scale. The precision (p) is the total number of digits required to represent the number. The scale (s) is the number of decimal digits to the right of the decimal point. Subtract s from p to determine the maximum number of digits to the left of the decimal point. Scale is a value from 0 through p. Scale is specified only if precision is specified. The default scale is 0. + +## CONVERT_TO and CONVERT_FROM Data Types + +The following table lists the data types for use with the CONVERT_TO +and CONVERT_FROM functions: + +**Type**| **Input Type**| **Output Type** +---|---|--- +BOOLEAN_BYTE| bytes(1)| boolean +TINYINT_BE| bytes(1)| tinyint +TINYINT| bytes(1)| tinyint +SMALLINT_BE| bytes(2)| smallint +SMALLINT| bytes(2)| smallint +INT_BE| bytes(4)| int +INT| bytes(4)| int +BIGINT_BE| bytes(8)| bigint +BIGINT| bytes(8)| bigint +FLOAT| bytes(4)| float (float4) +DOUBLE| bytes(8)| double (float8) +INT_HADOOPV| bytes(1-9)| int +BIGINT_HADOOPV| bytes(1-9)| bigint +DATE_EPOCH_BE| bytes(8)| date +DATE_EPOCH| bytes(8)| date +TIME_EPOCH_BE| bytes(8)| time +TIME_EPOCH| bytes(8)| time +UTF8| bytes| varchar +UTF16| bytes| var16char +UINT8| bytes(8)| uint8 +JSON | bytes | varchar + +## Using Drill Data Types + +If you understand how to use Drill data types, you have captured the essence of Drill. In Drill, you use data types in the following ways: + +* To cast or convert data to the required type for moving data from one data source to another +* To cast or convert data to the required type for Drill analysis + +In Drill, you do not use data types as you do in database software to define the type of a column during table creation. + +In some cases, Drill converts schema-less data to correctly-typed data implicitly. In this case, you do not need to cast the data. The file format of the data and the nature of your query determines the requirement for casting or converting. Differences in casting depend on the data source. The following list describes how Drill treats data types from various data sources: * HBase Does not implicitly cast input to SQL types. Convert data to appropriate types as shown in ["Querying HBase."](/docs/querying-hbase/) @@ -21,16 +105,13 @@ Differences in casting depend on the data source. The following list describes h * Text: CSV, TSV, and other text Implicitly casts all textual data to VARCHAR. -## Implicit Casting - +## Precedence of Data Types -Generally, Drill performs implicit casting based on the order of precedence shown in the implicit casting preference table. Drill usually implicitly casts a type from a lower precedence to a type having higher precedence. For instance, NULL can be promoted to any other type; SMALLINT can be promoted into INT. INT is not promoted to SMALLINT due to possible precision loss. Drill might deviate from these precedence rules for performance reasons. +Drill reads from and writes to data sources having a wide variety of types, more types than those Drill [previously listed](/docs/data-type-conversion#supported-data-types). Drill uses data types at the RPC level that are not supported for query input, often implicitly casting data. -Under certain circumstances, such as queries involving substr and concat functions, Drill reverses the order of precedence and allows a cast to VARCHAR from a type of higher precedence than VARCHAR, such as BIGINT. +The following list includes data types Drill uses in descending order of precedence. As shown in the table, you can cast a NULL value, which has the lowest precedence, to any other type; you can cast a SMALLINT value to INT. You cannot cast an INT value to SMALLINT due to possible precision loss. Drill might deviate from these precedence rules for performance reasons. Under certain circumstances, such as queries involving SUBSTR and CONCAT functions, Drill reverses the order of precedence and allows a cast to VARCHAR from a type of higher precedence than VARCHAR, such as BIGINT. -The following table lists data types top to bottom, in descending order of precedence. Drill implicitly casts to more data types than are currently supported for explicit casting. - -### Implicit Casting Precedence +### Casting Precedence <table> <tr> @@ -41,79 +122,80 @@ The following table lists data types top to bottom, in descending order of prece </tr> <tr> <td>1</td> - <td>INTERVAL</td> + <td>INTERVALYEAR (highest)</td> <td>13</td> - <td>UINT4</td> + <td>INT</td> </tr> <tr> <td>2</td> - <td>INTERVALYEAR</td> + <td>INTERVLADAY</td> <td>14</td> - <td>INT</td> + <td>UINT2</td> </tr> <tr> <td>3</td> - <td>INTERVLADAY</td> + <td>TIMESTAMPTZ*</td> <td>15</td> - <td>UINT2</td> + <td>SMALLINT</td> </tr> <tr> <td>4</td> - <td>TIMESTAMPTZ</td> + <td>TIMETZ*</td> <td>16</td> - <td>SMALLINT</td> + <td>UINT1</td> </tr> <tr> <td>5</td> - <td>TIMETZ</td> + <td>TIMESTAMP</td> <td>17</td> - <td>UINT1</td> + <td>VAR16CHAR</td> </tr> <tr> <td>6</td> - <td>TIMESTAMP</td> + <td>DATE</td> <td>18</td> - <td>VAR16CHAR</td> + <td>FIXED16CHAR</td> </tr> <tr> <td>7</td> - <td>DATE</td> + <td>TIME</td> <td>19</td> - <td>FIXED16CHAR</td> + <td>VARCHAR</td> </tr> <tr> <td>8</td> - <td>TIME</td> + <td>DOUBLE</td> <td>20</td> - <td>VARCHAR</td> + <td>CHAR</td> </tr> <tr> <td>9</td> - <td>DOUBLE</td> + <td>DECIMAL</td> <td>21</td> - <td>CHAR</td> + <td>VARBINARY**</td> </tr> <tr> <td>10</td> - <td>DECIMAL</td> + <td>UINT8</td> <td>22</td> - <td>VARBINARY*</td> + <td>FIXEDBINARY**</td> </tr> <tr> <td>11</td> - <td>UINT8</td> + <td>BIGINT</td> <td>23</td> - <td>FIXEDBINARY*</td> + <td>NULL (lowest)</td> </tr> <tr> <td>12</td> - <td>BIGINT</td> - <td>24</td> - <td>NULL</td> + <td>UINT4</td> + <td></td> + <td></td> </tr> </table> -\* The Drill Parquet reader supports these types. +\* Currently not supported. +\*\* The Drill Parquet reader supports these types. ## Explicit Casting @@ -134,54 +216,6 @@ In a textual file, such as CSV, Drill interprets every field as a VARCHAR, as pr If the SELECT statement includes a WHERE clause that compares a column of an unknown data type, cast both the value of the column and the comparison value in the WHERE clause. -## Supported Data Types for Casting -You use the following data types in queries that involve casting/converting data types: - -* BIGINT - 8-byte signed integer. the range is -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. - -* BOOLEAN - True or false - -* DATE - Years, months, and days in YYYY-MM-DD format - -* DECIMAL(p,s), or DEC(p,s), NUMERIC(p,s) - 38-digit precision number, precision is p, and scale is s. Example: DECIMAL(6,2) has 4 digits before the decimal point and 2 digits after the decimal point. - -* FLOAT - 4-byte single precision floating point number - -* DOUBLE, DOUBLE PRECISION - 8-byte double precision floating point number. - -* INTEGER or INT - 4-byte signed integer. The range is -2,147,483,648 to 2,147,483,647. - -* INTERVAL - Integer fields representing a period of time in years, months, days hours, minutes, seconds and optional milliseconds using ISO 8601 format. - -* INTERVALDAY - A simple version of the interval type expressing a period of time in days, hours, minutes, and seconds only. - -* INTERVALYEAR - A simple version of interval representing a period of time in years and months only. - -* SMALLINT - 2-byte signed integer. The range is -32,768 to 32,767. Supported in Drill 0.9 and later. See DRILL-2135. - -* TIME - Hours, minutes, seconds in the form HH:mm:ss, 24-hour based - -* TIMESTAMP - JDBC timestamp in year, month, date hour, minute, second, and optional milliseconds: yyyy-MM-dd HH:mm:ss.SSS - -* CHARACTER VARYING, CHARACTER, CHAR, or VARCHAR - Character string optionally declared with a length that indicates the maximum number of characters to use. For example, CHAR(30) casts data to a 30-character string maximum. The default limit is 1 character. The maximum character limit is 255. - -You specify a DECIMAL using a precision and scale. The precision (p) is the total number of digits required to represent the number. -. The scale (s) is the number of decimal digits to the right of the decimal point. Subtract s from p to determine the maximum number of digits to the left of the decimal point. Scale is a value from 0 through p. Scale is specified only if precision is specified. The default scale is 0. - For more information about and examples of casting, see [CAST](). ### Explicit Type Casting Maps @@ -216,7 +250,7 @@ The following tables show data types that Drill can cast to/from other data type <td>VARBINARY</td> </tr> <tr> - <td>SMALLINT</td> + <td>SMALLINT*</td> <td></td> <td>yes</td> <td>yes</td> @@ -300,7 +334,7 @@ The following tables show data types that Drill can cast to/from other data type <td>yes</td> </tr> <tr> - <td>FIXEDBINARY*</td> + <td>FIXEDBINARY**</td> <td>yes</td> <td>yes</td> <td>yes</td> @@ -312,7 +346,7 @@ The following tables show data types that Drill can cast to/from other data type <td>yes</td> </tr> <tr> - <td>VARCHAR**</td> + <td>VARCHAR***</td> <td>yes</td> <td>yes</td> <td>yes</td> @@ -324,7 +358,7 @@ The following tables show data types that Drill can cast to/from other data type <td>yes</td> </tr> <tr> - <td>VARBINARY*</td> + <td>VARBINARY**</td> <td>yes</td> <td>yes</td> <td>yes</td> @@ -337,9 +371,11 @@ The following tables show data types that Drill can cast to/from other data type </tr> </table> -\* For use with CONVERT_TO/FROM to cast binary data coming to/from sources such as MapR-DB/HBase. +\* Not supported in this release. + +\*\* Used to cast binary data coming to/from sources such as MapR-DB/HBase. -\*\* You cannot convert a character string having a decimal point to an INT or BIGINT. +\*\*\* You cannot convert a character string having a decimal point to an INT or BIGINT. #### Date and Time Data Types @@ -360,7 +396,6 @@ The following tables show data types that Drill can cast to/from other data type <td>TIME</td> <td>TIMESTAMP</td> <td>TIMESTAMPTZ</td> - <td>INTERVAL</td> <td>INTERVALYEAR</td> <td>INTERVALDAY</td> </tr> @@ -375,7 +410,7 @@ The following tables show data types that Drill can cast to/from other data type <td>Yes</td> </tr> <tr> - <td>FIXEDBINARY</td> + <td>FIXEDBINARY*</td> <td>No</td> <td>No</td> <td>No</td> @@ -395,7 +430,7 @@ The following tables show data types that Drill can cast to/from other data type <td>Yes</td> </tr> <tr> - <td>VARBINARY</td> + <td>VARBINARY*</td> <td>No</td> <td>No</td> <td>Yes</td> @@ -435,7 +470,7 @@ The following tables show data types that Drill can cast to/from other data type <td>No</td> </tr> <tr> - <td>TIMESTAMPTZ</td> + <td>TIMESTAMPTZ**</td> <td>Yes</td> <td>Yes</td> <td>Yes</td> @@ -445,16 +480,6 @@ The following tables show data types that Drill can cast to/from other data type <td>No</td> </tr> <tr> - <td>INTERVAL</td> - <td>Yes</td> - <td>No</td> - <td>Yes</td> - <td>Yes</td> - <td>No</td> - <td>Yes</td> - <td>Yes</td> - </tr> - <tr> <td>INTERVALYEAR</td> <td>Yes</td> <td>No</td> @@ -476,3 +501,8 @@ The following tables show data types that Drill can cast to/from other data type </tr> </table> +\* Used to cast binary data coming to/from sources such as MapR-DB/HBase. + +\*\* Not supported in this release. + + http://git-wip-us.apache.org/repos/asf/drill/blob/d3328217/_docs/sql-ref/002-lexical-structure.md ---------------------------------------------------------------------- diff --git a/_docs/sql-ref/002-lexical-structure.md b/_docs/sql-ref/002-lexical-structure.md index 6046490..634cb42 100644 --- a/_docs/sql-ref/002-lexical-structure.md +++ b/_docs/sql-ref/002-lexical-structure.md @@ -62,6 +62,17 @@ This section describes how to construct literals. ### Boolean Boolean values are true or false and are case-insensitive. Do not enclose the values in quotation marks. +### Date and Time +Format dates using dashes (-) to separate year, month, and day. Format time using colons (:) to separate hours, minutes and seconds. Format timestamps using a date and a time. These literals are shown in the following examples: + +* Date: 2008-12-15 + +* Time: 22:55:55.123... + +* Timestamp: 2008-12-15 22:55:55.12345 + +If you have dates and times in other formats, use a [data type conversion function](/data-type-conversion/#other-data-type-conversions) in your queries. + ### Identifier An identifier is a letter followed by any sequence of letters, digits, or the underscore. For example, names of tables, columns, and aliases are identifiers. Maximum length is 1024 characters. Enclose the following identifiers in back ticks: http://git-wip-us.apache.org/repos/asf/drill/blob/d3328217/_docs/sql-ref/data-types/001-date.md ---------------------------------------------------------------------- diff --git a/_docs/sql-ref/data-types/001-date.md b/_docs/sql-ref/data-types/001-date.md index cadeba5..4c036b6 100644 --- a/_docs/sql-ref/data-types/001-date.md +++ b/_docs/sql-ref/data-types/001-date.md @@ -4,7 +4,9 @@ parent: "Data Types" --- Using familiar date and time formats, listed in the [SQL data types table](/docs/data-types/supported-data-types), you can construct query date and time data. You need to cast textual data to date and time data types. The format of date, time, and timestamp text in a textual data source needs to match the SQL query format for successful casting. -DATE, TIME, and TIMESTAMP store values in Coordinated Universal Time (UTC). Currently, Drill does not support casting a TIMESTAMP with time zone, but you can use the [TO_TIMESTAMP function](/docs/casting/converting-data-types#to_timestamp) in a query to use time stamp data having a time zone. +DATE, TIME, and TIMESTAMP store values in Coordinated Universal Time (UTC). Drill supports time functions in the range 1971 to 2037. + +Currently, Drill does not support casting a TIMESTAMP with time zone, but you can use the [TO_TIMESTAMP function](/docs/casting/converting-data-types#to_timestamp) in a query to use time stamp data having a time zone. Next, use the following literals in a SELECT statement. @@ -36,9 +38,11 @@ Next, use the following literals in a SELECT statement. +------------+ 1 row selected (0.071 seconds) -## INTERVAL +## INTERVALYEAR and INTERVALDAY + +The INTERVALYEAR AND INTERVALDAY types represent a period of time. The INTERVALYEAR type specifies values from a year to a month. The INTERVALDAY type specifies values from a day to seconds. -The INTERVAL type represents a period of time. Use ISO 8601 syntax to format a value of this type: +Use ISO 8601 syntax to format an interval: P [qty] Y [qty] M [qty] D T [qty] H [qty] M [qty] S @@ -56,12 +60,23 @@ where: * M follows a number of minutes. * S follows a number of seconds and optional milliseconds to the right of a decimal point - -INTERVALYEAR (Year, Month) and INTERVALDAY (Day, Hours, Minutes, Seconds, Milliseconds) are a simpler version of INTERVAL with a subset of the fields. You do not need to specify all fields. - -The format of INTERVAL data in the data source differs from the query format. - -You can run the query described earlier to check the formatting of the fields. The input to the following SELECT statements show how to format INTERVAL data in the query. The output shows how to format the data in the data source. +You can restrict the set of stored interval fields by using one of these phrases in the query: + +* YEAR +* MONTH +* DAY +* HOUR +* MINUTE +* SECOND +* YEAR TO MONTH +* DAY TO HOUR +* DAY TO MINUTE +* DAY TO SECOND +* HOUR TO MINUTE +* HOUR TO SECOND +* MINUTE TO SECOND + +The following examples show the input and output format of INTERVALYEAR (Year, Month) and INTERVALDAY (Day, Hours, Minutes, Seconds, Milliseconds). The following SELECT statements show how to format the query input. The output shows how to format the data in the data source. SELECT INTERVAL '1 10:20:30.123' day to second FROM sys.version; +------------+ http://git-wip-us.apache.org/repos/asf/drill/blob/d3328217/_docs/sql-ref/data-types/002-diff-data-types.md ---------------------------------------------------------------------- diff --git a/_docs/sql-ref/data-types/002-diff-data-types.md b/_docs/sql-ref/data-types/002-diff-data-types.md index 539e9a9..e81c435 100644 --- a/_docs/sql-ref/data-types/002-diff-data-types.md +++ b/_docs/sql-ref/data-types/002-diff-data-types.md @@ -6,7 +6,7 @@ parent: "Data Types" To query HBase data in Drill, convert every column of an HBase table to/from byte arrays from/to an SQL data type using CONVERT_TO or CONVERT_FROM. For examples of how to use these functions, see "Convert and Cast Functions". ## Handling Textual Data -In a textual file, such as CSV, Drill interprets every field as a VARCHAR, as previously mentioned. In addition to using the CAST function, you can also use to_char, to_date, to_number, and to_timestamp. If the SELECT statement includes a WHERE clause that compares a column of an unknown data type, cast both the value of the column and the comparison value in the WHERE clause. +In a textual file, such as CSV, Drill interprets every field as a VARCHAR, as previously mentioned. In addition to using the CAST function, you can also use TO_CHAR, TO_DATE, TO_NUMBER, and TO_TIMESTAMP. If the SELECT statement includes a WHERE clause that compares a column of an unknown data type, cast both the value of the column and the comparison value in the WHERE clause. ## Handling JSON and Parquet Data Complex and nested data structures in JSON and Parquet files are of map and array types. http://git-wip-us.apache.org/repos/asf/drill/blob/d3328217/_docs/sql-ref/functions/002-conversion.md ---------------------------------------------------------------------- diff --git a/_docs/sql-ref/functions/002-conversion.md b/_docs/sql-ref/functions/002-conversion.md index 780b397..635bb6b 100644 --- a/_docs/sql-ref/functions/002-conversion.md +++ b/_docs/sql-ref/functions/002-conversion.md @@ -10,7 +10,7 @@ Drill supports the following functions for casting and converting data types: ## CAST -The CAST function converts an expression from one type to another. +The CAST function converts an entity, such as an expression that evaluates to a single value, from one type to another. ### Syntax @@ -40,7 +40,7 @@ Refer to the following tables for information about the data types to use for ca ### Examples -The following examples show how to cast a string to a number, a number to a string, and one numerical type to another. +The following examples show how to cast a string to a number, a number to a string, and one type of number to another. #### Casting a character string to a number You cannot cast a character string that includes a decimal point to an INT or BIGINT. For example, if you have "1200.50" in a JSON file, attempting to select and cast the string to an INT fails. As a workaround, cast to a FLOAT or DECIMAL type, and then to an INT. @@ -55,15 +55,7 @@ The following example shows how to cast a character to a DECIMAL having two deci +------------+ #### Casting a number to a character string -The first example shows that Drill uses a default limit of 1 character if you omit the VARCHAR limit: The result is truncated to 1 character. The second example casts the same number to a VARCHAR having a limit of 3 characters: The result is a 3-character string, 456. The third example shows that you can use CHAR as an alias for VARCHAR. You can also use CHARACTER or CHARACTER VARYING. - - SELECT CAST(456 as VARCHAR) FROM sys.version; - +------------+ - | EXPR$0 | - +------------+ - | 4 | - +------------+ - 1 row selected (0.063 seconds) +The first example shows Drill casting a number to a VARCHAR having a length of 3 bytes: The result is a 3-character string, 456. Drill supports the CHAR and CHARACTER VARYING alias. SELECT CAST(456 as VARCHAR(3)) FROM sys.version; +------------+ @@ -81,7 +73,7 @@ The first example shows that Drill uses a default limit of 1 character if you om +------------+ 1 row selected (0.093 seconds) -#### Casting from one numerical type to another +#### Casting from one type of number to another Cast an integer to a decimal. @@ -101,19 +93,29 @@ To cast INTERVAL data use the following syntax: CAST (column_name AS INTERVAL DAY) CAST (column_name AS INTERVAL YEAR) -For example, a JSON file contains the following objects: +For example, a JSON file named intervals.json contains the following objects: { "INTERVALYEAR_col":"P1Y", "INTERVALDAY_col":"P1D", "INTERVAL_col":"P1Y1M1DT1H1M" } { "INTERVALYEAR_col":"P2Y", "INTERVALDAY_col":"P2D", "INTERVAL_col":"P2Y2M2DT2H2M" } { "INTERVALYEAR_col":"P3Y", "INTERVALDAY_col":"P3D", "INTERVAL_col":"P3Y3M3DT3H3M" } -The following CTAS statement casts text from a JSON file to INTERVAL data types in a Parquet table: +1. Set the storage format to Parquet. + + ALTER SESSION SET `store.format` = 'parquet'; + + +------------+------------+ + | ok | summary | + +------------+------------+ + | true | store.format updated. | + +------------+------------+ + 1 row selected (0.037 seconds) + +Use a CTAS statement to cast text from a JSON file to year and day intervals and to write the data to a Parquet table: CREATE TABLE dfs.tmp.parquet_intervals AS - (SELECT cast (INTERVAL_col as interval), - cast( INTERVALYEAR_col as interval year) INTERVALYEAR_col, - cast( INTERVALDAY_col as interval day) INTERVALDAY_col - FROM `/user/root/intervals.json`); + (SELECT CAST( INTERVALYEAR_col as interval year) INTERVALYEAR_col, + CAST( INTERVALDAY_col as interval day) INTERVALDAY_col + FROM dfs.`/Users/drill/intervals.json`); <!-- Text and include output --> @@ -124,43 +126,15 @@ data to and from another data type. ## Syntax -CONVERT_TO (column, type) + CONVERT_TO (column, type) -CONVERT_FROM(column, type) + CONVERT_FROM(column, type) *column* is the name of a column Drill reads. -*type* is one of the data types listed in the CONVERT_TO/FROM Data Types table. - - -The following table provides the data types that you use with the CONVERT_TO -and CONVERT_FROM functions: - -### CONVERT_TO/FROM Data Types - -**Type**| **Input Type**| **Output Type** ----|---|--- -BOOLEAN_BYTE| bytes(1)| boolean -TINYINT_BE| bytes(1)| tinyint -TINYINT| bytes(1)| tinyint -SMALLINT_BE| bytes(2)| smallint -SMALLINT| bytes(2)| smallint -INT_BE| bytes(4)| int -INT| bytes(4)| int -BIGINT_BE| bytes(8)| bigint -BIGINT| bytes(8)| bigint -FLOAT| bytes(4)| float (float4) -DOUBLE| bytes(8)| double (float8) -INT_HADOOPV| bytes(1-9)| int -BIGINT_HADOOPV| bytes(1-9)| bigint -DATE_EPOCH_BE| bytes(8)| date -DATE_EPOCH| bytes(8)| date -TIME_EPOCH_BE| bytes(8)| time -TIME_EPOCH| bytes(8)| time -UTF8| bytes| varchar -UTF16| bytes| var16char -UINT8| bytes(8)| uint8 - +*type* is one of the data types listed in the [CONVERT_TO/FROM Data Types](/docs/data-types#convert_to-and-convert_from-data-types) table. + + ### Usage Notes You can use the CONVERT_TO and CONVERT_FROM functions to encode and decode data that is binary or complex. For example, HBase stores @@ -169,7 +143,7 @@ data as encoded VARBINARY data. To read HBase data in Drill, convert every colum Do not use the CAST function for converting binary data types to other types. Although CAST works for converting VARBINARY to VARCHAR, CAST does not work in some other binary conversion cases. CONVERT functions work for binary conversions and are also more efficient to use than CAST. ## Usage Notes -Use the CONVERT_TO function to change the data type to binary when sending data back to a binary data source, such as HBase, MapR, and Parquet, from a Drill query. CONVERT_TO also converts an SQL data type to complex types, including Hbase byte arrays, JSON and Parquet arrays, and maps. CONVERT_FROM converts from complex types, including Hbase arrays, JSON and Parquet arrays and maps to an SQL data type. +Use the CONVERT_TO function to change the data type to binary when sending data back to a binary data source, such as HBase, MapR, and Parquet, from a Drill query. CONVERT_TO also converts an SQL data type to complex types, including HBase byte arrays, JSON and Parquet arrays, and maps. CONVERT_FROM converts from complex types, including HBase arrays, JSON and Parquet arrays and maps to an SQL data type. ### Examples @@ -189,7 +163,7 @@ This example shows how to use the CONVERT_FROM function to convert complex HBase +------------+------------+------------+ 4 rows selected (1.335 seconds) -You use the CONVERT_FROM function to decode the binary data to render it readable: +You use the CONVERT_FROM function to decode the binary data to render it readable, selecting a data type to use from the [list of supported types](/docs/data-type-conversion/#convert_to-and-convert_from-data-types). JSON supports strings. To convert binary to strings, use the UTF8 type.: SELECT CONVERT_FROM(row_key, 'UTF8') AS studentid, CONVERT_FROM(students.account.name, 'UTF8') AS name, @@ -207,6 +181,36 @@ You use the CONVERT_FROM function to decode the binary data to render it readabl +------------+------------+------------+------------+------------+ 4 rows selected (0.504 seconds) +This example converts from VARCHAR to a JSON map: + + SELECT CONVERT_FROM('{x:100, y:215.6}' ,'JSON') AS MYCOL FROM sys.version; + +------------+ + | MYCOL | + +------------+ + | {"x":100,"y":215.6} | + +------------+ + 1 row selected (0.073 seconds) + +This example uses a list of BIGINT as input and returns a repeated list of vectors: + + SELECT CONVERT_FROM('[ [1, 2], [3, 4], [5]]' ,'JSON') AS MYCOL1 FROM sys.version; + +------------+ + | mycol1 | + +------------+ + | [[1,2],[3,4],[5]] | + +------------+ + 1 row selected (0.054 seconds) + +This example uses a map as input to return a repeated list vector (JSON). + + SELECT CONVERT_FROM('[{a : 100, b: 200}, {a:300, b: 400}]' ,'JSON') AS MYCOL1 FROM sys.version; + +------------+ + | MYCOL1 | + +------------+ + | [{"a":100,"b":200},{"a":300,"b":400}] | + +------------+ + 1 row selected (0.074 seconds) + #### Set up a storage plugin for working with HBase files This example assumes you are working in the Drill Sandbox. The `maprdb` storage plugin definition is limited, so you modify the `dfs` storage plugin slightly and use that plugin for this example. @@ -244,13 +248,14 @@ This example assumes you are working in the Drill Sandbox. The `maprdb` storage } } -#### Convert the binary HBase students table to JSON data. +#### Convert the binary HBase students table to JSON data +First, you set the storage format to JSON. Next, you use the CREATE TABLE AS SELECT (CTAS) statement to convert from a selected file of a different format, HBase in this example, to the storage format. You then convert the JSON file to Parquet using a similar procedure. Set the storage format to Parquet, and use a CTAS statement to convert to Parquet from JSON. In each case, you [select UTF8](/docs/data-type-conversion/#convert_to-and-convert_from-data-types) as the file format because the data you are converting from and then to consists of strings. 1. Start Drill on the Drill Sandbox and set the default storage format from Parquet to JSON. ALTER SESSION SET `store.format`='json'; -2. Use CONVERT_FROM queries to convert the VARBINARY data in the HBase students table to JSON, and store the JSON data in a file. +2. Use CONVERT_FROM queries to convert the binary data in the HBase students table to JSON, and store the JSON data in a file. You select a data type to use from the supported. JSON supports strings. To convert binary to strings, use the UTF8 type. CREATE TABLE tmp.`to_json` AS SELECT CONVERT_FROM(row_key, 'UTF8') AS `studentid`, @@ -274,7 +279,7 @@ This example assumes you are working in the Drill Sandbox. The `maprdb` storage 0_0_0.json -5. Take a look at the output om `to_json`: +5. Take a look at the output of `to_json`: { "studentid" : "student1", @@ -361,42 +366,17 @@ This example assumes you are working in the Drill Sandbox. The `maprdb` storage 4 rows selected (0.182 seconds) ## Other Data Type Conversions -In addition to the CAST, CONVERT_TO, and CONVERT_FROM functions, Drill supports data type conversion functions to perform the following conversions: +Drill supports the format for date and time literals shown in the following examples: -* A timestamp, integer, decimal, or double to a character string. -* A character string to a date -* A character string to a number - -## Time Zone Limitation -Currently Drill does not support conversion of a date, time, or timestamp from one time zone to another. The workaround is to configure Drill to use [UTC](http://www.timeanddate.com/time/aboututc.html)-based time, convert your data to UTC timestamps, and perform date/time operation in UTC. - -1. Take a look at the Drill time zone configuration by running the TIMEOFDAY function. This function returns the local date and time with time zone information. +* 2008-12-15 - SELECT TIMEOFDAY() FROM sys.version; +* 22:55:55.123... - +------------+ - | EXPR$0 | - +------------+ - | 2015-04-02 15:01:31.114 America/Los_Angeles | - +------------+ - 1 row selected (1.199 seconds) +If you have dates and times in other formats, use a data type conversion functions to perform the following conversions: -2. Configure the default time zone format in <drill installation directory>/conf/drill-env.sh by adding `-Duser.timezone=UTC` to DRILL_JAVA_OPTS. For example: - - export DRILL_JAVA_OPTS="-Xms1G -Xmx$DRILL_MAX_HEAP -XX:MaxDirectMemorySize=$DRILL_MAX_DIRECT_MEMORY -XX:MaxPermSize=512M -XX:ReservedCodeCacheSize=1G -ea -Duser.timezone=UTC" - -3. Restart sqlline. - -4. Confirm that Drill is now set to UTC: - - SELECT TIMEOFDAY() FROM sys.version; - - +------------+ - | EXPR$0 | - +------------+ - | 2015-04-02 17:05:02.424 UTC | - +------------+ - 1 row selected (1.191 seconds) +* A timestamp, integer, decimal, or double to a character string. +* A character string to a date +* A character string to a number The following table lists data type formatting functions that you can use in your Drill queries as described in this section: @@ -412,21 +392,8 @@ TO_NUMBER(text, format)| numeric TO_TIMESTAMP(text, format)| timestamp TO_TIMESTAMP(double precision)| timestamp -You can use the âzâ option to identify the time zone in TO_TIMESTAMP to make sure the timestamp has the timezone in it. Also, use the âzâ option to identify the time zone in a timestamp using the TO_CHAR function. For example: - - SELECT TO_TIMESTAMP('2015-03-30 20:49:59.0 UTC', 'YYYY-MM-dd HH:mm:ss.s z') AS Original, - TO_CHAR(TO_TIMESTAMP('2015-03-30 20:49:59.0 UTC', 'YYYY-MM-dd HH:mm:ss.s z'), 'z') AS TimeZone - FROM sys.version; - - +------------+------------+ - | Original | TimeZone | - +------------+------------+ - | 2015-03-30 20:49:00.0 | UTC | - +------------+------------+ - 1 row selected (0.299 seconds) - ### Format Specifiers for Numerical Conversions -Use the following format specifiers for numerical conversions: +Use the following format specifiers for converting numbers: <table > <tr > <th align=left>Symbol @@ -637,7 +604,7 @@ For more information about specifying a format, refer to one of the following fo ## TO_CHAR -TO_CHAR converts a date, time, timestamp, or numerical expression to a character string. +TO_CHAR converts a number, date, time, or timestamp expression to a character string. ### Syntax @@ -647,6 +614,9 @@ TO_CHAR converts a date, time, timestamp, or numerical expression to a character *'format'* is a format specifier enclosed in single quotation marks that sets a pattern for the output formatting. +### Usage Notes + +You can use the âzâ option to identify the time zone in TO_TIMESTAMP to make sure the timestamp has the timezone in it, as shown in the TO_TIMESTAMP description. ### Examples @@ -910,6 +880,49 @@ Convert a UTC date to a timestamp offset from the UTC time zone code. +------------+------------+ 1 row selected (0.129 seconds) +## Time Zone Limitation +Currently Drill does not support conversion of a date, time, or timestamp from one time zone to another. The workaround is to configure Drill to use [UTC](http://www.timeanddate.com/time/aboututc.html)-based time, convert your data to UTC timestamps, and perform date/time operation in UTC. + +1. Take a look at the Drill time zone configuration by running the TIMEOFDAY function. This function returns the local date and time with time zone information. + + SELECT TIMEOFDAY() FROM sys.version; + + +------------+ + | EXPR$0 | + +------------+ + | 2015-04-02 15:01:31.114 America/Los_Angeles | + +------------+ + 1 row selected (1.199 seconds) + +2. Configure the default time zone format in <drill installation directory>/conf/drill-env.sh by adding `-Duser.timezone=UTC` to DRILL_JAVA_OPTS. For example: + + export DRILL_JAVA_OPTS="-Xms1G -Xmx$DRILL_MAX_HEAP -XX:MaxDirectMemorySize=$DRILL_MAX_DIRECT_MEMORY -XX:MaxPermSize=512M -XX:ReservedCodeCacheSize=1G -ea -Duser.timezone=UTC" + +3. Restart sqlline. + +4. Confirm that Drill is now set to UTC: + + SELECT TIMEOFDAY() FROM sys.version; + + +------------+ + | EXPR$0 | + +------------+ + | 2015-04-02 17:05:02.424 UTC | + +------------+ + 1 row selected (1.191 seconds) + +You can use the âzâ option to identify the time zone in TO_TIMESTAMP to make sure the timestamp has the timezone in it. Also, use the âzâ option to identify the time zone in a timestamp using the TO_CHAR function. For example: + + SELECT TO_TIMESTAMP('2015-03-30 20:49:59.0 UTC', 'YYYY-MM-dd HH:mm:ss.s z') AS Original, + TO_CHAR(TO_TIMESTAMP('2015-03-30 20:49:59.0 UTC', 'YYYY-MM-dd HH:mm:ss.s z'), 'z') AS TimeZone + FROM sys.version; + + +------------+------------+ + | Original | TimeZone | + +------------+------------+ + | 2015-03-30 20:49:00.0 | UTC | + +------------+------------+ + 1 row selected (0.299 seconds) <!-- DRILL-448 Support timestamp with time zone -->
