You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@drill.apache.org by ts...@apache.org on 2015/05/19 01:36:37 UTC
[14/31] drill git commit: fix links
http://git-wip-us.apache.org/repos/asf/drill/blob/5f6a51af/_docs/sql-reference/sql-functions/020-data-type-conversion.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-functions/020-data-type-conversion.md b/_docs/sql-reference/sql-functions/020-data-type-conversion.md
index c60c096..82e8931 100644
--- a/_docs/sql-reference/sql-functions/020-data-type-conversion.md
+++ b/_docs/sql-reference/sql-functions/020-data-type-conversion.md
@@ -47,6 +47,8 @@ The following examples show how to cast a string to a number, a number to a stri
### 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.
+{% include startnote.html %}In this release, Drill disables the DECIMAL data type. To enable, set the planner.enable_decimal_data_type option to true.{% include endnote.html %}
+
The following example shows how to cast a character to a DECIMAL having two decimal places.
SELECT CAST('1' as DECIMAL(28, 2)) FROM sys.version;
@@ -56,8 +58,6 @@ The following example shows how to cast a character to a DECIMAL having two deci
| 1.00 |
+------------+
-{% include startnote.html %}In this release, Drill disables the DECIMAL data type. To enable, set the planner.enable_decimal_data_type option to true.{% include endnote.html %}
-
### Casting a Number to a Character String
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.
@@ -82,12 +82,11 @@ The first example shows Drill casting a number to a VARCHAR having a length of 3
Cast an integer to a decimal.
SELECT CAST(-2147483648 AS DECIMAL(28,8)) FROM sys.version;
- +------------+
- | EXPR$0 |
- +------------+
- | -2.147483648E9 |
- +------------+
- 1 row selected (0.08 seconds)
+ +-----------------+
+ | EXPR$0 |
+ +-----------------+
+ | -2.147483648E9 |
+ +-----------------+
{% include startnote.html %}In this release, Drill disables the DECIMAL data type. To enable, set the planner.enable_decimal_data_type option to true.{% include endnote.html %}
@@ -108,12 +107,12 @@ For example, a JSON file named intervals.json contains the following objects:
ALTER SESSION SET `store.format` = 'parquet';
- +------------+------------+
- | ok | summary |
- +------------+------------+
- | true | store.format updated. |
- +------------+------------+
- 1 row selected (0.037 seconds)
+ +-------+------------------------+
+ | ok | summary |
+ +-------+------------------------+
+ | true | store.format updated. |
+ +-------+------------------------+
+ 1 row selected (0.072 seconds)
2. 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:
@@ -153,14 +152,14 @@ This example shows how to use the CONVERT_FROM function to convert complex HBase
SELECT * FROM students;
- +------------+------------+------------+
- | row_key | account | address |
- +------------+------------+------------+
- | [B@e6d9eb7 | {"name":"QWxpY2U="} | {"state":"Q0E=","street":"MTIzIEJhbGxtZXIgQXY=","zipcode":"MTIzNDU="} |
- | [B@2823a2b4 | {"name":"Qm9i"} | {"state":"Q0E=","street":"MSBJbmZpbml0ZSBMb29w","zipcode":"MTIzNDU="} |
- | [B@3b8eec02 | {"name":"RnJhbms="} | {"state":"Q0E=","street":"NDM1IFdhbGtlciBDdA==","zipcode":"MTIzNDU="} |
+ +-------------+---------------------+---------------------------------------------------------------------------+
+ | row_key | account | address |
+ +-------------+---------------------+---------------------------------------------------------------------------+
+ | [B@e6d9eb7 | {"name":"QWxpY2U="} | {"state":"Q0E=","street":"MTIzIEJhbGxtZXIgQXY=","zipcode":"MTIzNDU="} |
+ | [B@2823a2b4 | {"name":"Qm9i"} | {"state":"Q0E=","street":"MSBJbmZpbml0ZSBMb29w","zipcode":"MTIzNDU="} |
+ | [B@3b8eec02 | {"name":"RnJhbms="} | {"state":"Q0E=","street":"NDM1IFdhbGtlciBDdA==","zipcode":"MTIzNDU="} |
| [B@242895da | {"name":"TWFyeQ=="} | {"state":"Q0E=","street":"NTYgU291dGhlcm4gUGt3eQ==","zipcode":"MTIzNDU="} |
- +------------+------------+------------+
+ +-------------+---------------------+---------------------------------------------------------------------------+
4 rows selected (1.335 seconds)
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]({{ site.baseurl }}/docs/data-type-conversion/#convert_to-and-convert_from-data-types). JSON supports strings. To convert binary to strings, use the UTF8 type.:
@@ -171,25 +170,25 @@ You use the CONVERT_FROM function to decode the binary data to render it readabl
CONVERT_FROM(students.address.street, 'UTF8') AS street,
CONVERT_FROM(students.address.zipcode, 'UTF8') AS zipcode FROM students;
- +------------+------------+------------+------------+------------+
- | studentid | name | state | street | zipcode |
- +------------+------------+------------+------------+------------+
- | student1 | Alice | CA | 123 Ballmer Av | 12345 |
- | student2 | Bob | CA | 1 Infinite Loop | 12345 |
- | student3 | Frank | CA | 435 Walker Ct | 12345 |
+ +------------+------------+------------+------------------+------------+
+ | studentid | name | state | street | zipcode |
+ +------------+------------+------------+------------------+------------+
+ | student1 | Alice | CA | 123 Ballmer Av | 12345 |
+ | student2 | Bob | CA | 1 Infinite Loop | 12345 |
+ | student3 | Frank | CA | 435 Walker Ct | 12345 |
| student4 | Mary | CA | 56 Southern Pkwy | 12345 |
- +------------+------------+------------+------------+------------+
+ +------------+------------+------------+------------------+------------+
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)
+ +----------------------+
+ | MYCOL |
+ +----------------------+
+ | {"x":100,"y":215.6} |
+ +----------------------+
+ 1 row selected (0.163 seconds)
This example uses a list of BIGINT as input and returns a repeated list of vectors:
@@ -204,12 +203,12 @@ This example uses a list of BIGINT as input and returns a repeated list of vecto
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)
+ +--------------------+
+ | MYCOL1 |
+ +--------------------+
+ | [[1,2],[3,4],[5]] |
+ +--------------------+
+ 1 row selected (0.141 seconds)
### Set Up a Storage Plugin for Working with HBase Files
@@ -311,12 +310,12 @@ First, you set the storage format to JSON. Next, you use the CREATE TABLE AS SEL
6. Set up Drill to store data in Parquet format.
ALTER SESSION SET `store.format`='parquet';
- +------------+------------+
- | ok | summary |
- +------------+------------+
- | true | store.format updated. |
- +------------+------------+
- 1 row selected (0.056 seconds)
+ +-------+------------------------+
+ | ok | summary |
+ +-------+------------------------+
+ | true | store.format updated. |
+ +-------+------------------------+
+ 1 row selected (0.07 seconds)
7. Use CONVERT_TO to convert the JSON data to a binary format in the Parquet file.
@@ -337,14 +336,14 @@ First, you set the storage format to JSON. Next, you use the CREATE TABLE AS SEL
8. Take a look at the binary Parquet output:
SELECT * FROM tmp.`json2parquet`;
- +------------+------------+------------+------------+------------+
- | id | name | state | street | zip |
- +------------+------------+------------+------------+------------+
+ +-------------+-------------+-------------+-------------+-------------+
+ | id | name | state | street | zip |
+ +-------------+-------------+-------------+-------------+-------------+
| [B@224388b2 | [B@7fc36fb0 | [B@77d9cd57 | [B@7c384839 | [B@530dd5e5 |
- | [B@3155d7fc | [B@7ad6fab1 | [B@37e4b978 | [B@94c91f3 | [B@201ed4a |
+ | [B@3155d7fc | [B@7ad6fab1 | [B@37e4b978 | [B@94c91f3 | [B@201ed4a |
| [B@4fb2c078 | [B@607a2f28 | [B@75ae1c93 | [B@79d63340 | [B@5dbeed3d |
- | [B@2fcfec74 | [B@7baccc31 | [B@d91e466 | [B@6529eb7f | [B@232412bc |
- +------------+------------+------------+------------+------------+
+ | [B@2fcfec74 | [B@7baccc31 | [B@d91e466 | [B@6529eb7f | [B@232412bc |
+ +-------------+-------------+-------------+-------------+-------------+
4 rows selected (0.12 seconds)
9. Use CONVERT_FROM to convert the Parquet data to a readable format:
@@ -356,14 +355,14 @@ First, you set the storage format to JSON. Next, you use the CREATE TABLE AS SEL
CONVERT_FROM(zip, 'UTF8') AS zip
FROM tmp.`json2parquet2`;
- +------------+------------+------------+------------+------------+
- | id | name | state | address | zip |
- +------------+------------+------------+------------+------------+
- | student1 | Alice | CA | 123 Ballmer Av | 12345 |
- | student2 | Bob | CA | 1 Infinite Loop | 12345 |
- | student3 | Frank | CA | 435 Walker Ct | 12345 |
+ +------------+------------+------------+------------------+------------+
+ | id | name | state | address | zip |
+ +------------+------------+------------+------------------+------------+
+ | student1 | Alice | CA | 123 Ballmer Av | 12345 |
+ | student2 | Bob | CA | 1 Infinite Loop | 12345 |
+ | student3 | Frank | CA | 435 Walker Ct | 12345 |
| student4 | Mary | CA | 56 Southern Pkwy | 12345 |
- +------------+------------+------------+------------+------------+
+ +------------+------------+------------+------------------+------------+
4 rows selected (0.182 seconds)
## Other Data Type Conversions
@@ -481,11 +480,12 @@ Convert an integer to a character string.
Convert a date to a character string.
SELECT TO_CHAR((CAST('2008-2-23' AS DATE)), 'yyyy-MMM-dd') FROM sys.version;
- +------------+
- | EXPR$0 |
- +------------+
- | 2008-Feb-23 |
- +------------+
+ +--------------+
+ | EXPR$0 |
+ +--------------+
+ | 2008-Feb-23 |
+ +--------------+
+ 1 row selected (0.166 seconds)
Convert a time to a string.
@@ -501,12 +501,12 @@ Convert a time to a string.
Convert a timestamp to a string.
SELECT TO_CHAR(CAST('2015-2-23 12:00:00' AS TIMESTAMP), 'yyyy MMM dd HH:mm:ss') FROM sys.version;
- +------------+
- | EXPR$0 |
- +------------+
- | 2015 Feb 23 12:00:00 |
- +------------+
- 1 row selected (0.075 seconds)
+ +-----------------------+
+ | EXPR$0 |
+ +-----------------------+
+ | 2015 Feb 23 12:00:00 |
+ +-----------------------+
+ 1 row selected (0.142 seconds)
## TO_DATE
Converts a character string or a UNIX epoch timestamp to a date.
@@ -690,21 +690,22 @@ Specify a format using patterns defined in [Java DateTimeFormat class](http://jo
Convert a date to a timestamp.
SELECT TO_TIMESTAMP('2008-2-23 12:00:00', 'yyyy-MM-dd HH:mm:ss') FROM sys.version;
- +------------+
- | EXPR$0 |
- +------------+
- | 2008-02-23 12:00:00.0 |
- +------------+
+ +------------------------+
+ | EXPR$0 |
+ +------------------------+
+ | 2008-02-23 12:00:00.0 |
+ +------------------------+
+ 1 row selected (0.126 seconds)
Convert Unix Epoch time to a timestamp.
SELECT TO_TIMESTAMP(1427936330) FROM sys.version;
- +------------+
- | EXPR$0 |
- +------------+
- | 2015-04-01 17:58:50.0 |
- +------------+
- 1 row selected (0.094 seconds)
+ +------------------------+
+ | EXPR$0 |
+ +------------------------+
+ | 2015-04-01 17:58:50.0 |
+ +------------------------+
+ 1 row selected (0.114 seconds)
Convert a UTC date to a timestamp offset from the UTC time zone code.
@@ -712,12 +713,12 @@ Convert a UTC date to a timestamp offset from the UTC time zone code.
TO_CHAR(TO_TIMESTAMP('2015-03-30 20:49:59.0 UTC', 'YYYY-MM-dd HH:mm:ss.s z'), 'z') AS New_TZ
FROM sys.version;
- +------------+------------+
- | Original | New_TZ |
- +------------+------------+
- | 2015-03-30 20:49:00.0 | UTC |
- +------------+------------+
- 1 row selected (0.129 seconds)
+ +------------------------+---------+
+ | Original | New_TZ |
+ +------------------------+---------+
+ | 2015-03-30 20:49:00.0 | UTC |
+ +------------------------+---------+
+ 1 row selected (0.148 seconds)
## Time Zone Limitation
Currently Drill does not support conversion of a date, time, or timestamp from one time zone to another. Queries of data associated with a time zone can return inconsistent results or an error. For more information, see the ["Understanding Drill's Timestamp and Timezone"](http://www.openkb.info/2015/05/understanding-drills-timestamp-and.html#.VUzhotpVhHw) blog. The Drill time zone is based on the operating system time zone unless you override it. To work around the limitation, 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.
@@ -726,12 +727,12 @@ Currently Drill does not support conversion of a date, time, or timestamp from o
SELECT TIMEOFDAY() FROM sys.version;
- +------------+
- | EXPR$0 |
- +------------+
- | 2015-04-02 15:01:31.114 America/Los_Angeles |
- +------------+
- 1 row selected (1.199 seconds)
+ +----------------------------------------------+
+ | EXPR$0 |
+ +----------------------------------------------+
+ | 2015-05-17 22:37:29.516 America/Los_Angeles |
+ +----------------------------------------------+
+ 1 row selected (0.108 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:
@@ -743,12 +744,12 @@ Currently Drill does not support conversion of a date, time, or timestamp from o
SELECT TIMEOFDAY() FROM sys.version;
- +------------+
- | EXPR$0 |
- +------------+
- | 2015-04-02 17:05:02.424 UTC |
- +------------+
- 1 row selected (1.191 seconds)
+ +----------------------------------------------+
+ | EXPR$0 |
+ +----------------------------------------------+
+ | 2015-05-17 22:37:57.082 America/Los_Angeles |
+ +----------------------------------------------+
+ 1 row selected (0.087 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:
@@ -756,12 +757,12 @@ You can use the ‘z’ option to identify the time zone in TO_TIMESTAMP to make
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)
+ +------------------------+-----------+
+ | Original | TimeZone |
+ +------------------------+-----------+
+ | 2015-03-30 20:49:00.0 | UTC |
+ +------------------------+-----------+
+ 1 row selected (0.097 seconds)
<!-- DRILL-448 Support timestamp with time zone -->
http://git-wip-us.apache.org/repos/asf/drill/blob/5f6a51af/_docs/sql-reference/sql-functions/030-date-time-functions-and-arithmetic.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-functions/030-date-time-functions-and-arithmetic.md b/_docs/sql-reference/sql-functions/030-date-time-functions-and-arithmetic.md
index 7f6a534..66df6ff 100644
--- a/_docs/sql-reference/sql-functions/030-date-time-functions-and-arithmetic.md
+++ b/_docs/sql-reference/sql-functions/030-date-time-functions-and-arithmetic.md
@@ -5,22 +5,22 @@ parent: "SQL Functions"
In addition to the TO_DATE, TO_TIME, and TO_TIMESTAMP functions, Drill supports a number of other date/time functions and arithmetic operators for use with dates, times, and intervals. Drill supports time functions based on the Gregorian calendar and in the range 1971 to 2037.
-This section defines the following date/time functions:
+This section covers the Drill [time zone limitation]({{site.baseurl}}/docs/data-type-conversion/#time-zone-limitation) and defines the following date/time functions:
**Function**| **Return Type**
---|---
[AGE(TIMESTAMP)]({{ site.baseurl }}/docs/date-time-functions-and-arithmetic#age)| INTERVALDAY or INTERVALYEAR
[EXTRACT(field from time_expression)]({{ site.baseurl }}/docs/date-time-functions-and-arithmetic#extract)| DOUBLE
-[CURRENT_DATE]({{ site.baseurl }}/docs/date-time-functions-and-arithmetic#current_*x*-local*x*-now-and-timeofday)| DATE
-[CURRENT_TIME]({{ site.baseurl }}/docs/date-time-functions-and-arithmetic#current_*x*-local*x*-now-and-timeofday)| TIME
-[CURRENT_TIMESTAMP]({{ site.baseurl }}/docs/date-time-functions-and-arithmetic#current_*x*-local*x*-now-and-timeofday)| TIMESTAMP
+[CURRENT_DATE]({{ site.baseurl }}/docs/date-time-functions-and-arithmetic/#other-date-and-time-functions)| DATE
+[CURRENT_TIME]({{ site.baseurl }}/docs/date-time-functions-and-arithmetic/#other-date-and-time-functions)| TIME
+[CURRENT_TIMESTAMP]({{ site.baseurl }}/docs/date-time-functions-and-arithmetic/#other-date-and-time-functions)| TIMESTAMP
[DATE_ADD]({{ site.baseurl }}/docs/date-time-functions-and-arithmetic#date_add)| DATE, TIMESTAMP
[DATE_PART]({{ site.baseurl }}/docs/date-time-functions-and-arithmetic#date_part)| DOUBLE
[DATE_SUB]({{ site.baseurl }}/docs/date-time-functions-and-arithmetic#date_sub)| DATE, TIMESTAMOP
-[LOCALTIME]({{ site.baseurl }}/docs/date-time-functions-and-arithmetic#current_*x*-local*x*-now-and-timeofday)| TIME
-[LOCALTIMESTAMP]({{ site.baseurl }}/docs/date-time-functions-and-arithmetic#current_*x*-local*x*-now-and-timeofday)| TIMESTAMP
-[NOW]({{ site.baseurl }}/docs/date-time-functions-and-arithmetic#current_*x*-local*x*-now-and-timeofday)| TIMESTAMP
-[TIMEOFDAY]({{ site.baseurl }}/docs/date-time-functions-and-arithmetic#current_*x*-local*x*-now-and-timeofday)| VARCHAR
+[LOCALTIME]({{ site.baseurl }}/docs/date-time-functions-and-arithmetic/#other-date-and-time-functions)| TIME
+[LOCALTIMESTAMP]({{ site.baseurl }}/docs/date-time-functions-and-arithmetic/#other-date-and-time-functions)| TIMESTAMP
+[NOW]({{ site.baseurl }}/docs/date-time-functions-and-arithmetic/#other-date-and-time-functions)| TIMESTAMP
+[TIMEOFDAY]({{ site.baseurl }}/docs/date-time-functions-and-arithmetic/#other-date-and-time-functions)| VARCHAR
## AGE
Returns the interval between two timestamps or subtracts a timestamp from midnight of the current date.
@@ -49,14 +49,14 @@ Find the interval between midnight April 3, 2015 and June 13, 1957.
Find the interval between 11:10:10 PM on January 1, 2001 and 10:10:10 PM on January 1, 2001.
SELECT AGE(CAST('2010-01-01 10:10:10' AS TIMESTAMP), CAST('2001-01-01 11:10:10' AS TIMESTAMP)) FROM sys.version;
- +------------+
- | EXPR$0 |
- +------------+
- | P109M16DT82800S |
- +------------+
- 1 row selected (0.161 seconds)
+ +------------------+
+ | EXPR$0 |
+ +------------------+
+ | P109M16DT82800S |
+ +------------------+
+ 1 row selected (0.122 seconds)
-For information about how to read the interval data, see the [Interval section]({{ site.baseurl }}/docs/date-time-and-timestamp#interval).
+For information about how to read the interval data, see the [Interval section]({{ site.baseurl }}/docs/date-time-and-timestamp/#intervalyear-and-intervalday).
## DATE_ADD
Returns the sum of a date/time and a number of days/hours, or of a date/time and date/time interval.
@@ -92,22 +92,22 @@ Add two days to today's date May 15, 2015.
Add two months to April 15, 2015.
SELECT DATE_ADD(date '2015-04-15', interval '2' month) FROM sys.version;
- +------------+
- | EXPR$0 |
- +------------+
- | 2015-06-15 00:00:00.0 |
- +------------+
- 1 row selected (0.073 seconds)
+ +------------------------+
+ | EXPR$0 |
+ +------------------------+
+ | 2015-06-15 00:00:00.0 |
+ +------------------------+
+ 1 row selected (0.107 seconds)
Add 10 hours to the timestamp 2015-04-15 22:55:55.
SELECT DATE_ADD(timestamp '2015-04-15 22:55:55', interval '10' hour) FROM sys.version;
- +------------+
- | EXPR$0 |
- +------------+
- | 2015-04-16 08:55:55.0 |
- +------------+
- 1 row selected (0.068 seconds)
+ +------------------------+
+ | EXPR$0 |
+ +------------------------+
+ | 2015-04-16 08:55:55.0 |
+ +------------------------+
+ 1 row selected (0.199 seconds)
Add 10 hours to the time 22 hours, 55 minutes, 55 seconds.
@@ -122,22 +122,22 @@ Add 10 hours to the time 22 hours, 55 minutes, 55 seconds.
Add 1 year and 1 month to the timestamp 2015-04-15 22:55:55.
SELECT DATE_ADD(timestamp '2015-04-15 22:55:55', interval '1-2' year to month) FROM sys.version;
- +------------+
- | EXPR$0 |
- +------------+
- | 2016-06-15 22:55:55.0 |
- +------------+
- 1 row selected (0.065 seconds)
+ +------------------------+
+ | EXPR$0 |
+ +------------------------+
+ | 2016-06-15 22:55:55.0 |
+ +------------------------+
+ 1 row selected (0.106 seconds)
Add 1 day 2 and 1/2 hours and 45.100 seconds to the time 22:55:55.
SELECT DATE_ADD(time '22:55:55', interval '1 2:30:45.100' day to second) FROM sys.version;
- +------------+
- | EXPR$0 |
- +------------+
- | 01:26:40.100 |
- +------------+
- 1 row selected (0.07 seconds)
+ +---------------+
+ | EXPR$0 |
+ +---------------+
+ | 01:26:40.100 |
+ +---------------+
+ 1 row selected (0.106 seconds)
## DATE_PART
Returns a field of a date, time, timestamp, or interval.
@@ -234,12 +234,12 @@ Subtact two months from April 15, 2015.
Subtract 10 hours from the timestamp 2015-04-15 22:55:55.
SELECT DATE_SUB(timestamp '2015-04-15 22:55:55', interval '10' hour) FROM sys.version;
- +------------+
- | EXPR$0 |
- +------------+
- | 2015-04-15 12:55:55.0 |
- +------------+
- 1 row selected (0.068 seconds)
+ +------------------------+
+ | EXPR$0 |
+ +------------------------+
+ | 2015-04-15 12:55:55.0 |
+ +------------------------+
+ 1 row selected (0.108 seconds)
Subtract 10 hours from the time 22 hours, 55 minutes, 55 seconds.
@@ -254,24 +254,24 @@ Subtract 10 hours from the time 22 hours, 55 minutes, 55 seconds.
Subtract 1 year and 1 month from the timestamp 2015-04-15 22:55:55.
SELECT DATE_SUB(timestamp '2015-04-15 22:55:55', interval '1-2' year to month) FROM sys.version;
- +------------+
- | EXPR$0 |
- +------------+
- | 2014-02-15 22:55:55.0 |
- +------------+
- 1 row selected (0.073 seconds)
+ +------------------------+
+ | EXPR$0 |
+ +------------------------+
+ | 2014-02-15 22:55:55.0 |
+ +------------------------+
+ 1 row selected (0.108 seconds)
Subtract 1 day, 2 and 1/2 hours, and 45.100 seconds from the time 22:55:55.
SELECT DATE_ADD(time '22:55:55', interval '1 2:30:45.100' day to second) FROM sys.version;
- +------------+
- | EXPR$0 |
- +------------+
- | 01:26:40.100 |
- +------------+
- 1 row selected (0.073 seconds)
+ +---------------+
+ | EXPR$0 |
+ +---------------+
+ | 01:26:40.100 |
+ +---------------+
+ 1 row selected (0.095 seconds)
-## CURRENT_*x*, LOCAL*x*, NOW, and TIMEOFDAY
+## Other Date and Time Functions
The following examples show how to use these functions:
@@ -300,58 +300,58 @@ The following examples show how to use these functions:
1 row selected (0.073 seconds)
SELECT CURRENT_TIMESTAMP FROM sys.version;
- +-------------------+
- | current_timestamp |
- +-------------------+
- | 2015-04-02 14:32:34.047 |
- +-------------------+
- 1 row selected (0.061 seconds)
+ +--------------------------+
+ | CURRENT_TIMESTAMP |
+ +--------------------------+
+ | 2015-05-17 22:45:55.848 |
+ +--------------------------+
+ 1 row selected (0.109 seconds)
SELECT LOCALTIME FROM sys.version;
- +------------+
- | localtime |
- +------------+
- | 14:33:04.95 |
- +------------+
- 1 row selected (0.051 seconds)
+ +---------------+
+ | LOCALTIME |
+ +---------------+
+ | 22:46:19.656 |
+ +---------------+
+ 1 row selected (0.105 seconds)
SELECT LOCALTIMESTAMP FROM sys.version;
- +----------------+
- | LOCALTIMESTAMP |
- +----------------+
- | 2015-04-02 23:13:13.204 |
- +----------------+
- 1 row selected (0.105 seconds)
+ +--------------------------+
+ | LOCALTIMESTAMP |
+ +--------------------------+
+ | 2015-05-17 22:46:47.944 |
+ +--------------------------+
+ 1 row selected (0.08 seconds)
SELECT NOW() FROM sys.version;
- +------------+
- | EXPR$0 |
- +------------+
- | 2015-04-02 23:14:30.076 |
- +------------+
- 1 row selected (0.05 seconds)
+ +--------------------------+
+ | EXPR$0 |
+ +--------------------------+
+ | 2015-05-17 22:47:11.008 |
+ +--------------------------+
+ 1 row selected (0.085 seconds)
-If you set up Drill for [UTC time]({{ site.baseurl }}/docs/casting-converting-data-types/time-zone-limitation), TIMEOFDAY returns the result for the UTC time zone.
+If you set up Drill for [UTC time]({{ site.baseurl }}/docs/data-type-conversion/#time-zone-limitation), TIMEOFDAY returns the result for the UTC time zone.
SELECT TIMEOFDAY() FROM sys.version;
- +------------+
- | EXPR$0 |
- +------------+
+ +-----------------------------+
+ | EXPR$0 |
+ +-----------------------------+
| 2015-04-02 22:05:02.424 UTC |
- +------------+
+ +-----------------------------+
1 row selected (1.191 seconds)
If you did not set up Drill for UTC time, TIMEOFDAY 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)
+ +----------------------------------------------+
+ | EXPR$0 |
+ +----------------------------------------------+
+ | 2015-05-17 22:47:38.012 America/Los_Angeles |
+ +----------------------------------------------+
+ 1 row selected (0.08 seconds)
## EXTRACT
http://git-wip-us.apache.org/repos/asf/drill/blob/5f6a51af/_docs/sql-reference/sql-functions/040-string-manipulation.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-functions/040-string-manipulation.md b/_docs/sql-reference/sql-functions/040-string-manipulation.md
index ffb3173..4c5a09a 100644
--- a/_docs/sql-reference/sql-functions/040-string-manipulation.md
+++ b/_docs/sql-reference/sql-functions/040-string-manipulation.md
@@ -92,12 +92,12 @@ Concatenates arguments.
SELECT CONCAT('Drill', ' ', 1.0, ' ', 'release') FROM sys.version;
- +------------+
- | EXPR$0 |
- +------------+
- | Drill 1.0 release |
- +------------+
- 1 row selected (0.221 seconds)
+ +--------------------+
+ | EXPR$0 |
+ +--------------------+
+ | Drill 1.0 release |
+ +--------------------+
+ 1 row selected (0.134 seconds)
Alternatively, you can use the [string concatenation operation]({{ site.baseurl }}/docs/operators#string-concatenate-operator) to concatenate strings.
@@ -112,12 +112,12 @@ Returns the string using initial caps.
SELECT INITCAP('apache drill release 1.0') FROM sys.version;
- +------------+
- | EXPR$0 |
- +------------+
- | Apache Drill Release 1.0 |
- +------------+
- 1 row selected (0.124 seconds)
+ +---------------------------+
+ | EXPR$0 |
+ +---------------------------+
+ | Apache Drill Release 1.0 |
+ +---------------------------+
+ 1 row selected (0.106 seconds)
## LENGTH
Returns the number of characters in the string.
@@ -159,12 +159,12 @@ Converts characters in the string to lowercase.
SELECT LOWER('Apache Drill') FROM sys.version;
- +------------+
- | EXPR$0 |
- +------------+
- | apache drill |
- +------------+
- 1 row selected (0.113 seconds)
+ +---------------+
+ | EXPR$0 |
+ +---------------+
+ | apache drill |
+ +---------------+
+ 1 row selected (0.103 seconds)
## LPAD
Pads the string to the length specified by prepending the fill or a space. Truncates the string if longer than the specified length.
@@ -178,12 +178,12 @@ Pads the string to the length specified by prepending the fill or a space. Trunc
SELECT LPAD('Release 1.0', 27, 'of Apache Drill 1.0') FROM sys.version;
- +------------+
- | EXPR$0 |
- +------------+
- | of Apache Drill Release 1.0 |
- +------------+
- 1 row selected (0.112 seconds)
+ +------------------------------+
+ | EXPR$0 |
+ +------------------------------+
+ | of Apache Drill Release 1.0 |
+ +------------------------------+
+ 1 row selected (0.132 seconds)
## LTRIM
Removes any characters from the beginning of string1 that match the characters in string2.
@@ -205,12 +205,12 @@ Removes any characters from the beginning of string1 that match the characters i
SELECT LTRIM('A powerful tool Apache Drill', 'Apache ') FROM sys.version;
- +------------+
- | EXPR$0 |
- +------------+
- | owerful tool Apache Drill |
- +------------+
- 1 row selected (0.07 seconds)
+ +----------------------------+
+ | EXPR$0 |
+ +----------------------------+
+ | owerful tool Apache Drill |
+ +----------------------------+
+ 1 row selected (0.1 seconds)
## POSITION
Returns the location of a substring.
@@ -249,22 +249,23 @@ Substitutes new text for substrings that match [POSIX regular expression pattern
Replace a's with b's in this string.
SELECT REGEXP_REPLACE('abc, acd, ade, aef', 'a', 'b') FROM sys.version;
- +------------+
- | EXPR$0 |
- +------------+
- | bbc, bcd, bde, bef |
- +------------+
+ +---------------------+
+ | EXPR$0 |
+ +---------------------+
+ | bbc, bcd, bde, bef |
+ +---------------------+
+ 1 row selected (0.105 seconds)
Use the regular expression *a* followed by a period (.) in the same query to replace all a's and the subsequent character.
SELECT REGEXP_REPLACE('abc, acd, ade, aef', 'a.','b') FROM sys.version;
- +------------+
- | EXPR$0 |
- +------------+
- | bc, bd, be, bf |
- +------------+
- 1 row selected (0.099 seconds)
+ +-----------------+
+ | EXPR$0 |
+ +-----------------+
+ | bc, bd, be, bf |
+ +-----------------+
+ 1 row selected (0.113 seconds)
## RPAD
@@ -277,12 +278,12 @@ Pads the string to the length specified. Appends the text you specify after the
### RPAD Example
SELECT RPAD('Apache Drill ', 22, 'Release 1.0') FROM sys.version;
- +------------+
- | EXPR$0 |
- +------------+
- | Apache Drill Release 1 |
- +------------+
- 1 row selected (0.15 seconds)
+ +-------------------------+
+ | EXPR$0 |
+ +-------------------------+
+ | Apache Drill Release 1 |
+ +-------------------------+
+ 1 row selected (0.107 seconds)
## RTRIM
Removes any characters from the end of string1 that match the characters in string2.
@@ -303,12 +304,12 @@ Removes any characters from the end of string1 that match the characters in stri
1 row selected (0.135 seconds)
SELECT RTRIM('1.0 Apache Tomcat 1.0', 'Drill 1.0') from sys.version;
- +------------+
- | EXPR$0 |
- +------------+
- | 1.0 Apache Tomcat |
- +------------+
- 1 row selected (0.088 seconds)
+ +--------------------+
+ | EXPR$0 |
+ +--------------------+
+ | 1.0 Apache Tomcat |
+ +--------------------+
+ 1 row selected (0.102 seconds)
## STRPOS
Returns the location of the substring in a string.
@@ -377,20 +378,20 @@ Removes any characters from the beginning, end, or both sides of string2 that ma
1 row selected (0.172 seconds)
SELECT TRIM(both 'l' from 'long live Drill') FROM sys.version;
- +------------+
- | EXPR$0 |
- +------------+
- | ong live Dri |
- +------------+
- 1 row selected (0.087 seconds)
+ +---------------+
+ | EXPR$0 |
+ +---------------+
+ | ong live Dri |
+ +---------------+
+ 1 row selected (0.104 seconds)
SELECT TRIM(leading 'l' from 'long live Drill') FROM sys.version;
- +------------+
- | EXPR$0 |
- +------------+
- | ong live Drill |
- +------------+
- 1 row selected (0.077 seconds)
+ +-----------------+
+ | EXPR$0 |
+ +-----------------+
+ | ong live Drill |
+ +-----------------+
+ 1 row selected (0.101 seconds)
## UPPER
Converts characters in the string to uppercase.
@@ -403,9 +404,9 @@ Converts characters in the string to uppercase.
SELECT UPPER('Apache Drill') FROM sys.version;
- +------------+
- | EXPR$0 |
- +------------+
- | APACHE DRILL |
- +------------+
- 1 row selected (0.104 seconds)
\ No newline at end of file
+ +---------------+
+ | EXPR$0 |
+ +---------------+
+ | APACHE DRILL |
+ +---------------+
+ 1 row selected (0.081 seconds)
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/drill/blob/5f6a51af/_docs/sql-reference/sql-functions/050-aggregate-and-aggregate-statistical.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-functions/050-aggregate-and-aggregate-statistical.md b/_docs/sql-reference/sql-functions/050-aggregate-and-aggregate-statistical.md
index 21e6e40..ff1305d 100644
--- a/_docs/sql-reference/sql-functions/050-aggregate-and-aggregate-statistical.md
+++ b/_docs/sql-reference/sql-functions/050-aggregate-and-aggregate-statistical.md
@@ -44,11 +44,11 @@ MIN, MAX, COUNT, AVG, and SUM accept ALL and DISTINCT keywords. The default is A
13 rows selected (0.056 seconds)
SELECT AVG(ALL a2) FROM t2;
- +------------+
- | EXPR$0 |
- +------------+
+ +--------------------+
+ | EXPR$0 |
+ +--------------------+
| 4.3076923076923075 |
- +------------+
+ +--------------------+
1 row selected (0.084 seconds)
SELECT AVG(DISTINCT a2) FROM t2;
http://git-wip-us.apache.org/repos/asf/drill/blob/5f6a51af/_docs/tutorials/010-tutorials-introduction.md
----------------------------------------------------------------------
diff --git a/_docs/tutorials/010-tutorials-introduction.md b/_docs/tutorials/010-tutorials-introduction.md
index 178e721..46c782c 100644
--- a/_docs/tutorials/010-tutorials-introduction.md
+++ b/_docs/tutorials/010-tutorials-introduction.md
@@ -12,13 +12,13 @@ If you've never used Drill, use these tutorials to download, install, and start
Explore data using a Hadoop environment pre-configured with Drill.
* [Analyzing Highly Dynamic Datasets]({{site.baseurl}}/docs/analyzing-highly-dynamic-datasets)
Delve into changing data without creating a schema or going through an ETL phase.
-* [Analyzing Social Media]({site.baseurl}}/docs/analyzing-social-media)
+* [Analyzing Social Media]({{site.baseurl}}/docs/analyzing-social-media)
Analyze Twitter data in native JSON format using Apache Drill.
* [Tableau Examples]({{site.baseurl}}/docs/tableau-examples)
Access Hive tables in Tableau.
-* [Using MicroStrategy Analytics with Apache Drill]({{site.baseurl}}/docs/using-microstrategy-analytics-with--apache-drill/)
+* [Using MicroStrategy Analytics with Apache Drill]({{site.baseurl}}/docs/using-microstrategy-analytics-with-apache-drill/)
Use the Drill ODBC driver from MapR to analyze data and generate a report using Drill from the MicroStrategy UI.
-* [Using Tibco Spotfire Server with Drill]({{site.baseurl}}/drill/docs/using-tibco-spotfire-with-drill/)
+* [Using Tibco Spotfire Server with Drill]({{site.baseurl}}/docs/using-tibco-spotfire-with-drill/)
Use the Apache Drill to query complex data structures from Tibco Spotfire Desktop.
* [Configuring Tibco Spotfire Server with Drill]({{site.baseurl}}/docs/configuring-tibco-spotfire-server-with-drill)
Integrate Tibco Spotfire Server with Apache Drill and explore multiple data formats on Hadoop.
http://git-wip-us.apache.org/repos/asf/drill/blob/5f6a51af/_docs/tutorials/020-drill-in-10-minutes.md
----------------------------------------------------------------------
diff --git a/_docs/tutorials/020-drill-in-10-minutes.md b/_docs/tutorials/020-drill-in-10-minutes.md
index 95db7a9..5a51043 100755
--- a/_docs/tutorials/020-drill-in-10-minutes.md
+++ b/_docs/tutorials/020-drill-in-10-minutes.md
@@ -11,7 +11,7 @@ without having to perform any setup tasks.
## Installation Overview
-You can install Drill in embedded mode on a machine running Linux, Mac OS X, or Windows. For information about running Drill in distributed mode, see [Deploying Drill in a Cluster]({{ site.baseurl }}/docs/deploying-drill-in-a-cluster).
+You can install Drill in embedded mode on a machine running Linux, Mac OS X, or Windows. For information about installing Drill in distributed mode, see [Installing Drill in Distributed Mode]({{ site.baseurl }}/docs/installing-drill-in-distributed-mode).
This installation procedure includes how to download the Apache Drill archive and extract the contents to a directory on your machine. The Apache Drill archive contains sample JSON and Parquet files that you can query immediately.
http://git-wip-us.apache.org/repos/asf/drill/blob/5f6a51af/_docs/tutorials/030-analyzing-the-yelp-academic-dataset.md
----------------------------------------------------------------------
diff --git a/_docs/tutorials/030-analyzing-the-yelp-academic-dataset.md b/_docs/tutorials/030-analyzing-the-yelp-academic-dataset.md
index 3a53efd..1eb2728 100644
--- a/_docs/tutorials/030-analyzing-the-yelp-academic-dataset.md
+++ b/_docs/tutorials/030-analyzing-the-yelp-academic-dataset.md
@@ -2,17 +2,14 @@
title: "Analyzing the Yelp Academic Dataset"
parent: "Tutorials"
---
-[Apache Drill](https://www.mapr.com/products/apache-drill) is one of the
-fastest growing open source projects, with the community making rapid progress
-with monthly releases. The key difference is Drill’s agility and flexibility.
+Apache Drill is one of the fastest growing open source projects, with the community making rapid progress with monthly releases. The key difference is Drill’s agility and flexibility.
Along with meeting the table stakes for SQL-on-Hadoop, which is to achieve low
latency performance at scale, Drill allows users to analyze the data without
any ETL or up-front schema definitions. The data could be in any file format
such as text, JSON, or Parquet. Data could have simple types such as string,
integer, dates, or more complex multi-structured data, such as nested maps and
arrays. Data can exist in any file system, local or distributed, such as HDFS,
-[MapR FS](https://www.mapr.com/blog/comparing-mapr-fs-and-hdfs-nfs-and-
-snapshots), or S3. Drill, has a “no schema” approach, which enables you to get
+MapR FS, or S3. Drill, has a “no schema” approach, which enables you to get
value from your data in just a few minutes.
Let’s quickly walk through the steps required to install Drill and run it
@@ -28,7 +25,7 @@ example is downloadable from [Yelp](http://www.yelp.com/dataset_challenge)
[http://drill.apache.org/download/](http://drill.apache.org/download/)
-You can also [deploy Drill in clustered mode]({{ site.baseurl }}/docs/deploying-drill-in-a-cluster) if you
+You can also [in Drill in distributed mode]({{ site.baseurl }}/docs/installing-drill-in-distributed-mode) if you
want to scale your environment.
### Step 2 : Open the Drill tar file
@@ -417,8 +414,8 @@ Stay tuned for more features and upcoming activities in the Drill community.
To learn more about Drill, please refer to the following resources:
- * Download Drill here:<http://getdrill.org/drill/download>
- * 10 reasons we think Drill is cool:</docs/why-drill>
+ * Download Drill here: <http://getdrill.org/drill/download>
+ * [10 reasons we think Drill is cool]({{site.baseurl}}/docs/why-drill)
* [A simple 10-minute tutorial]({{ site.baseurl }}/docs/drill-in-10-minutes>)
* [More tutorials]({{ site.baseurl }}/docs/tutorials-introduction/)
http://git-wip-us.apache.org/repos/asf/drill/blob/5f6a51af/_docs/tutorials/050-analyzing-highly-dynamic-datasets.md
----------------------------------------------------------------------
diff --git a/_docs/tutorials/050-analyzing-highly-dynamic-datasets.md b/_docs/tutorials/050-analyzing-highly-dynamic-datasets.md
index bb5680b..69cf93d 100644
--- a/_docs/tutorials/050-analyzing-highly-dynamic-datasets.md
+++ b/_docs/tutorials/050-analyzing-highly-dynamic-datasets.md
@@ -104,7 +104,7 @@ You can get value from the data quickly by applying both KVGEN and FLATTEN funct
On the output of flattened data, you use standard SQL functionality such as filters , aggregates, and sort. Let’s see a few examples.
-## Get the total number of check-ins recorded in the Yelp dataset
+### Get the total number of check-ins recorded in the Yelp dataset
0: jdbc:drill:zk=local> SELECT SUM(checkintbl.checkins.`value`) AS TotalCheckins FROM (
. . . . . . . . . . . > SELECT FLATTEN(KVGEN(checkin_info)) checkins FROM dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_checkin.json` ) checkintbl
@@ -115,7 +115,7 @@ On the output of flattened data, you use standard SQL functionality such as filt
| 4713811 |
+---------------+
-## Get the number of check-ins specifically for Sunday midnights
+### Get the number of check-ins specifically for Sunday midnights
0: jdbc:drill:zk=local> SELECT SUM(checkintbl.checkins.`value`) AS SundayMidnightCheckins FROM (
. . . . . . . . . . . > SELECT FLATTEN(KVGEN(checkin_info)) checkins FROM dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_checkin.json` ) checkintbl WHERE checkintbl.checkins.key='23-0';
@@ -125,7 +125,7 @@ On the output of flattened data, you use standard SQL functionality such as filt
| 8575 |
+------------------------+
-## Get the number of check-ins per day of the week
+### Get the number of check-ins per day of the week
0: jdbc:drill:zk=local> SELECT `right`(checkintbl.checkins.key,1) WeekDay,sum(checkintbl.checkins.`value`) TotalCheckins from (
. . . . . . . . . . . > select flatten(kvgen(checkin_info)) checkins FROM dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_checkin.json` ) checkintbl GROUP BY `right`(checkintbl.checkins.key,1) ORDER BY TotalCheckins;
@@ -141,7 +141,7 @@ On the output of flattened data, you use standard SQL functionality such as filt
| 5 | 937201 |
+------------+---------------+
-## Get the number of check-ins per hour of the day
+### Get the number of check-ins per hour of the day
0: jdbc:drill:zk=local> SELECT SUBSTR(checkintbl.checkins.key,1,strpos(checkintbl.checkins.key,'-')-1) AS HourOfTheDay ,SUM(checkintbl.checkins.`value`) TotalCheckins FROM (
. . . . . . . . . . . > SELECT FLATTEN(KVGEN(checkin_info)) checkins FROM dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_checkin.json` ) checkintbl GROUP BY SUBSTR(checkintbl.checkins.key,1,strpos(checkintbl.checkins.key,'-')-1) ORDER BY TotalCheckins;
http://git-wip-us.apache.org/repos/asf/drill/blob/5f6a51af/_docs/tutorials/learn-drill-with-the-mapr-sandbox/020-getting-to-know-the-drill-sandbox.md
----------------------------------------------------------------------
diff --git a/_docs/tutorials/learn-drill-with-the-mapr-sandbox/020-getting-to-know-the-drill-sandbox.md b/_docs/tutorials/learn-drill-with-the-mapr-sandbox/020-getting-to-know-the-drill-sandbox.md
index bf1a136..cb679bf 100644
--- a/_docs/tutorials/learn-drill-with-the-mapr-sandbox/020-getting-to-know-the-drill-sandbox.md
+++ b/_docs/tutorials/learn-drill-with-the-mapr-sandbox/020-getting-to-know-the-drill-sandbox.md
@@ -18,8 +18,6 @@ Drill includes a shell for connecting to relational databases and executing SQL
sqlline version 1.1.6
0: jdbc:drill:>
-[Starting SQLLine outside the sandbox]({{ site.baseurl }}/docs/install-drill) for use with Drill requires entering a few options, covered in the section, ["Install Drill"](docs/install-drill/).
-
In this tutorial you query a number of data sets, including Hive and HBase, and files on the file system, such as CSV, JSON, and Parquet files. To access these diverse data sources, you connect Drill to storage plugins.
## Storage Plugin Overview