Repository: drill Updated Branches: refs/heads/gh-pages e22e0e6f5 -> 91e3f20aa
DRILL-2706 Project: http://git-wip-us.apache.org/repos/asf/drill/repo Commit: http://git-wip-us.apache.org/repos/asf/drill/commit/91e3f20a Tree: http://git-wip-us.apache.org/repos/asf/drill/tree/91e3f20a Diff: http://git-wip-us.apache.org/repos/asf/drill/diff/91e3f20a Branch: refs/heads/gh-pages Commit: 91e3f20aa1b941f2f2a70d362215fa6d1e9015ed Parents: e22e0e6 Author: Kristine Hahn <kh...@maprtech.com> Authored: Tue Apr 7 12:17:25 2015 -0700 Committer: Bridget Bevens <bbev...@maprtech.com> Committed: Tue Apr 7 17:43:51 2015 -0700 ---------------------------------------------------------------------- _docs/002-tutorial.md | 2 +- _docs/003-yelp.md | 2 +- _docs/connect/009-mapr-db-plugin.md | 4 +- _docs/sql-ref/004-functions.md | 4 +- _docs/sql-ref/data-types/001-date.md | 14 +-- _docs/sql-ref/functions/001-math.md | 12 +-- _docs/sql-ref/functions/002-conversion.md | 99 +++++++++++++++------- _docs/sql-ref/functions/003-date-time-fcns.md | 46 +++++----- _docs/sql-ref/functions/004-string.md | 30 +++---- 9 files changed, 126 insertions(+), 87 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/drill/blob/91e3f20a/_docs/002-tutorial.md ---------------------------------------------------------------------- diff --git a/_docs/002-tutorial.md b/_docs/002-tutorial.md index 9f69b71..104e28d 100644 --- a/_docs/002-tutorial.md +++ b/_docs/002-tutorial.md @@ -39,7 +39,7 @@ be used to get an overview on Apache Drill in a Hadoop environment. Business and technical analysts, product managers, and developers can use the sandbox environment to get a feel for the power and capabilities of Apache Drill by performing various types of queries. Once you get a flavor for the technology, -refer to the [Apache Drill web site](http://incubator.apache.org/drill/) and +refer to the [Apache Drill web site](http://drill.apache.org) and [Apache Drill documentation ](/docs)for more details. http://git-wip-us.apache.org/repos/asf/drill/blob/91e3f20a/_docs/003-yelp.md ---------------------------------------------------------------------- diff --git a/_docs/003-yelp.md b/_docs/003-yelp.md index 874f780..3a345d8 100644 --- a/_docs/003-yelp.md +++ b/_docs/003-yelp.md @@ -23,7 +23,7 @@ example is downloadable from [Yelp](http://www.yelp.com/dataset_challenge) ### Step 1: Download Apache Drill onto your local machine -[http://incubator.apache.org/drill/download/](http://incubator.apache.org/drill/download/) +[http://drill.apache.org/download/](http://drill.apache.org/download/) You can also [deploy Drill in clustered mode](/docs/deploying-apache-drill-in-a-clustered-environment) if you want to scale your environment. http://git-wip-us.apache.org/repos/asf/drill/blob/91e3f20a/_docs/connect/009-mapr-db-plugin.md ---------------------------------------------------------------------- diff --git a/_docs/connect/009-mapr-db-plugin.md b/_docs/connect/009-mapr-db-plugin.md index a5bd33e..bc06144 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` storage plugin on a MapR node: +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: { "type": "hbase", @@ -13,7 +13,7 @@ Drill includes a `maprdb` format plugin for handling MapR-DB and HBase data. The "enabled": true } -Using the Sandbox and this `maprdb` storage plugin, you can query HBase tables located in the `/tables` directory, as shown in the ["Query HBase"](/docs/querying-hbase) examples. +Using the Sandbox and this `maprdb` format plugin, you can query HBase tables located in the `/tables` directory, as shown in the ["Query HBase"](/docs/querying-hbase) examples. The `dfs` storage plugin includes the maprdb format when you install Drill from the `mapr-drill` package on a MapR node. Click **Update** next to the `dfs` instance in the Web UI of the Drill Sandbox to view the configuration for the `dfs` instance: http://git-wip-us.apache.org/repos/asf/drill/blob/91e3f20a/_docs/sql-ref/004-functions.md ---------------------------------------------------------------------- diff --git a/_docs/sql-ref/004-functions.md b/_docs/sql-ref/004-functions.md index 570a4b9..3eba507 100644 --- a/_docs/sql-ref/004-functions.md +++ b/_docs/sql-ref/004-functions.md @@ -8,8 +8,8 @@ You can use the following types of functions in your Drill queries: * [Casting and Converting Data Types](/docs/casting-converting-data-types/) * [Date/Time and Arithmetic](/docs/date-time-functions-and-arithmetic/) * [String Manipulation](/docs/string-manipulation) - * [Aggregate and Aggregate Statistics]() + * [Aggregate and Aggregate Statistical](/docs/aggregate-and-aggregate-statistical) * [Nested Data](/docs/nested-data-functions/) - * [Other functions]() + * [Functions for Handling Nulls](/docs/functions-for-handling-nulls) http://git-wip-us.apache.org/repos/asf/drill/blob/91e3f20a/_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 7115d11..cadeba5 100644 --- a/_docs/sql-ref/data-types/001-date.md +++ b/_docs/sql-ref/data-types/001-date.md @@ -12,7 +12,7 @@ Next, use the following literals in a SELECT statement. * `time` * `timestamp` - SELECT date '2010-2-15' FROM sys.drillbits; + SELECT date '2010-2-15' FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -20,7 +20,7 @@ Next, use the following literals in a SELECT statement. +------------+ 1 row selected (0.083 seconds) - SELECT time '15:20:30' from sys.drillbits; + SELECT time '15:20:30' from sys.version; +------------+ | EXPR$0 | +------------+ @@ -28,7 +28,7 @@ Next, use the following literals in a SELECT statement. +------------+ 1 row selected (0.067 seconds) - SELECT timestamp '2015-03-11 6:50:08' FROM sys.drillbits; + SELECT timestamp '2015-03-11 6:50:08' FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -63,7 +63,7 @@ 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. - SELECT INTERVAL '1 10:20:30.123' day to second FROM sys.drillbits; + SELECT INTERVAL '1 10:20:30.123' day to second FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -71,7 +71,7 @@ You can run the query described earlier to check the formatting of the fields. T +------------+ 1 row selected (0.054 seconds) - SELECT INTERVAL '1-2' year to month FROM sys.drillbits; + SELECT INTERVAL '1-2' year to month FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -79,7 +79,7 @@ You can run the query described earlier to check the formatting of the fields. T +------------+ 1 row selected (0.927 seconds) - SELECT INTERVAL '1' year FROM sys.drillbits; + SELECT INTERVAL '1' year FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -87,7 +87,7 @@ You can run the query described earlier to check the formatting of the fields. T +------------+ 1 row selected (0.088 seconds) - SELECT INTERVAL '13' month FROM sys.drillbits; + SELECT INTERVAL '13' month FROM sys.version; +------------+ | EXPR$0 | +------------+ http://git-wip-us.apache.org/repos/asf/drill/blob/91e3f20a/_docs/sql-ref/functions/001-math.md ---------------------------------------------------------------------- diff --git a/_docs/sql-ref/functions/001-math.md b/_docs/sql-ref/functions/001-math.md index ec1f023..fd0c8f3 100644 --- a/_docs/sql-ref/functions/001-math.md +++ b/_docs/sql-ref/functions/001-math.md @@ -269,7 +269,7 @@ Open input2.json and change the first float value from 17.4 to 3.14159. Get valu Get the base 2 log of 64. - SELECT log(2, 64) FROM sys.drillbits; + SELECT log(2, 64) FROM sys.version; +------------+ | EXPR$0 | @@ -280,7 +280,7 @@ Get the base 2 log of 64. Get the common log of 100. - SELECT log10(100) FROM sys.drillbits; + SELECT log10(100) FROM sys.version; +------------+ | EXPR$0 | @@ -291,7 +291,7 @@ Get the common log of 100. Get the natural log of 7.5. - SELECT log(7.5) FROM sys.drillbits; + SELECT log(7.5) FROM sys.version; +------------+ | EXPR$0 | @@ -335,7 +335,7 @@ Drill supports the following trig functions, which return a FLOAT8 result. Find the sine and tangent of a 45 degree angle. First convert degrees to radians for use in the SIN() function. - SELECT RADIANS(30) AS Degrees FROM sys.drillbits; + SELECT RADIANS(30) AS Degrees FROM sys.version; +------------+ | Degrees | @@ -344,7 +344,7 @@ Find the sine and tangent of a 45 degree angle. First convert degrees to radians +------------+ 1 row selected (0.045 seconds) - SELECT SIN(0.7853981633974483) AS `Sine of 30 degrees` FROM sys.drillbits; + SELECT SIN(0.7853981633974483) AS `Sine of 30 degrees` FROM sys.version; +-----------------------+ | Sine of 45 degrees | @@ -353,7 +353,7 @@ Find the sine and tangent of a 45 degree angle. First convert degrees to radians +-----------------------+ 1 row selected (0.059 seconds) - SELECT TAN(0.7853981633974483) AS `Tangent of 30 degrees` from sys.drillbits; + SELECT TAN(0.7853981633974483) AS `Tangent of 30 degrees` from sys.version; +-----------------------+ | Tangent of 45 degrees | http://git-wip-us.apache.org/repos/asf/drill/blob/91e3f20a/_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 9a47a95..67b6562 100644 --- a/_docs/sql-ref/functions/002-conversion.md +++ b/_docs/sql-ref/functions/002-conversion.md @@ -6,6 +6,7 @@ Drill supports the following functions for casting and converting data types: * [CAST](/docs/data-type-conversion#cast) * [CONVERT_TO and CONVERT_FROM](/docs/data-type-conversion#convert_to-and-convert_from) +* [Other Data Type Conversions](/docs/data-type-conversion#other-data-type-conversions) ## CAST @@ -46,7 +47,7 @@ You cannot cast a character string that includes a decimal point to an INT or BI 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.drillbits; + SELECT CAST('1' as DECIMAL(28, 2)) FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -56,7 +57,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.drillbits; + SELECT CAST(456 as VARCHAR) FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -64,7 +65,7 @@ The first example shows that Drill uses a default limit of 1 character if you om +------------+ 1 row selected (0.063 seconds) - SELECT CAST(456 as VARCHAR(3)) FROM sys.drillbits; + SELECT CAST(456 as VARCHAR(3)) FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -72,7 +73,7 @@ The first example shows that Drill uses a default limit of 1 character if you om +------------+ 1 row selected (0.08 seconds) - SELECT CAST(456 as CHAR(3)) FROM sys.drillbits; + SELECT CAST(456 as CHAR(3)) FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -84,7 +85,7 @@ The first example shows that Drill uses a default limit of 1 character if you om Cast an integer to a decimal. - SELECT CAST(-2147483648 AS DECIMAL(28,8)) FROM sys.drillbits; + SELECT CAST(-2147483648 AS DECIMAL(28,8)) FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -371,7 +372,7 @@ Currently Drill does not support conversion of a date, time, or timestamp from o 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.drillbits; + SELECT TIMEOFDAY() FROM sys.version; +------------+ | EXPR$0 | @@ -380,15 +381,19 @@ 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 <drill installation directory>/conf/drill-env.sh by adding `-Duser.timezone=UTC` to DRILL_JAVA_OPTS. For example: +2. Configure the default time zone format in the drill-override.conf. 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" + drill.exec: { + cluster-id: âxyz", + zk.connect: âabc:5181", + user.timezone: "UTC" + } 3. Restart sqlline. 4. Confirm that Drill is now set to UTC: - SELECT TIMEOFDAY() FROM sys.drillbits; + SELECT TIMEOFDAY() FROM sys.version; +------------+ | EXPR$0 | @@ -411,9 +416,18 @@ TO_NUMBER(text, format)| numeric TO_TIMESTAMP(text, format)| timestamp TO_TIMESTAMP(double precision)| timestamp -<!-- A character string to a timestamp with time zone +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: -A decimal type to a timestamp with time zone --> + 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: @@ -644,7 +658,7 @@ TO_CHAR converts a date, time, timestamp, or numerical expression to a character Convert a FLOAT to a character string. - SELECT TO_CHAR(125.789383, '#,###.###') FROM sys.drillbits; + SELECT TO_CHAR(125.789383, '#,###.###') FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -653,7 +667,7 @@ Convert a FLOAT to a character string. Convert an integer to a character string. - SELECT TO_CHAR(125, '#,###.###') FROM sys.drillbits; + SELECT TO_CHAR(125, '#,###.###') FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -663,7 +677,7 @@ 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.drillbits; + SELECT TO_CHAR((CAST('2008-2-23' AS DATE)), 'yyyy-MMM-dd') FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -672,7 +686,7 @@ Convert a date to a character string. Convert a time to a string. - SELECT TO_CHAR(CAST('12:20:30' AS TIME), 'HH mm ss') FROM sys.drillbits; + SELECT TO_CHAR(CAST('12:20:30' AS TIME), 'HH mm ss') FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -683,7 +697,7 @@ 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.drillbits; + SELECT TO_CHAR(CAST('2015-2-23 12:00:00' AS TIMESTAMP), 'yyyy MMM dd HH:mm:ss') FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -702,18 +716,43 @@ Converts a character string or a UNIX epoch timestamp to a date. *'format'* is a format specifier enclosed in single quotation marks that sets a pattern for the output formatting. Use this option only when the expression is a character string, not a UNIX epoch timestamp. -### Usage +### Usage Notes Specify a format using patterns defined in [Java DateTimeFormat class](http://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html). The TO_TIMESTAMP function takes a Unix epoch timestamp. The TO_DATE function takes a UNIX epoch timestamp in milliseconds. To compare dates in the WHERE clause, use TO_DATE on the value in the date column and in the comparison value. For example: - SELECT <fields> FROM <plugin> WHERE TO_DATE(<column>, <format>) < - TO_DATE(<value>, <format>); + SELECT <fields> FROM <plugin> WHERE TO_DATE(<field>, <format>) < TO_DATE (<value>, <format>); + +For example: + + SELECT TO_DATE(`date`, 'yyyy-MM-dd') FROM `sample.json`; + + +------------+ + | EXPR$0 | + +------------+ + | 2013-07-26 | + | 2013-05-16 | + | 2013-06-09 | + | 2013-07-19 | + | 2013-07-21 | + +------------+ + 5 rows selected (0.134 seconds) + + SELECT TO_DATE(`date`, 'yyyy-MM-dd') FROM `sample.json` WHERE TO_DATE(`date`, 'yyyy-MM-dd') < TO_DATE('2013-07-20', 'yyyy-MM-dd'); + + +------------+ + | EXPR$0 | + +------------+ + | 2013-05-16 | + | 2013-06-09 | + | 2013-07-19 | + +------------+ + 3 rows selected (0.177 seconds) ### Examples The first example converts a character string to a date. The second example extracts the year to verify that Drill recognizes the date as a date type. - SELECT TO_DATE('2015-FEB-23', 'yyyy-MMM-dd') FROM sys.drillbits; + SELECT TO_DATE('2015-FEB-23', 'yyyy-MMM-dd') FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -721,7 +760,7 @@ The first example converts a character string to a date. The second example extr +------------+ 1 row selected (0.077 seconds) - SELECT EXTRACT(year from mydate) `extracted year` FROM (SELECT TO_DATE('2015-FEB-23', 'yyyy-MMM-dd') AS mydate FROM sys.drillbits); + SELECT EXTRACT(year from mydate) `extracted year` FROM (SELECT TO_DATE('2015-FEB-23', 'yyyy-MMM-dd') AS mydate FROM sys.version); +------------+ | myyear | @@ -732,7 +771,7 @@ The first example converts a character string to a date. The second example extr The following example converts a UNIX epoch timestamp to a date. - SELECT TO_DATE(1427849046000) FROM sys.drillbits; + SELECT TO_DATE(1427849046000) FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -773,14 +812,14 @@ The data type of the output of TO_NUMBER is a numeric. You can use the following ### Examples - SELECT TO_NUMBER('987,966', '######') FROM sys.drillbits; + SELECT TO_NUMBER('987,966', '######') FROM sys.version; +------------+ | EXPR$0 | +------------+ | 987.0 | +------------+ - SELECT TO_NUMBER('987.966', '###.###') FROM sys.drillbits; + SELECT TO_NUMBER('987.966', '###.###') FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -788,7 +827,7 @@ The data type of the output of TO_NUMBER is a numeric. You can use the following +------------+ 1 row selected (0.063 seconds) - SELECT TO_NUMBER('12345', '##0.##E0') FROM sys.drillbits; + SELECT TO_NUMBER('12345', '##0.##E0') FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -812,7 +851,7 @@ Specify a format using patterns defined in [Java DateTimeFormat class](http://jo ### Examples - SELECT TO_TIME('12:20:30', 'HH:mm:ss') FROM sys.drillbits; + SELECT TO_TIME('12:20:30', 'HH:mm:ss') FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -822,7 +861,7 @@ Specify a format using patterns defined in [Java DateTimeFormat class](http://jo Convert 828550000 milliseconds (23 hours 55 seconds) to the time. - SELECT to_time(82855000) FROM sys.drillbits; + SELECT to_time(82855000) FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -847,7 +886,7 @@ 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.drillbits; + SELECT TO_TIMESTAMP('2008-2-23 12:00:00', 'yyyy-MM-dd HH:mm:ss') FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -856,7 +895,7 @@ Convert a date to a timestamp. Convert Unix Epoch time to a timestamp. - SELECT TO_TIMESTAMP(1427936330) FROM sys.drillbits; + SELECT TO_TIMESTAMP(1427936330) FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -868,7 +907,7 @@ Connvert a UTC date to a timestamp offset from the UTC time zone code. 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 New_TZ - FROM sys.drillbits; + FROM sys.version; +------------+------------+ | Original | New_TZ | http://git-wip-us.apache.org/repos/asf/drill/blob/91e3f20a/_docs/sql-ref/functions/003-date-time-fcns.md ---------------------------------------------------------------------- diff --git a/_docs/sql-ref/functions/003-date-time-fcns.md b/_docs/sql-ref/functions/003-date-time-fcns.md index 80643a0..71a0e12 100644 --- a/_docs/sql-ref/functions/003-date-time-fcns.md +++ b/_docs/sql-ref/functions/003-date-time-fcns.md @@ -56,7 +56,7 @@ Cast string arguments to timestamp to include time data in the calculations of t Find the interval between midnight April 3, 2015 and June 13, 1957. - SELECT AGE('1957-06-13') FROM sys.drillbits; + SELECT AGE('1957-06-13') FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -66,7 +66,7 @@ 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.drillbits; + 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 | +------------+ @@ -94,7 +94,7 @@ Returns a component of a timestamp, time, date, or interval. On the third day of the month, run the following function: - SELECT EXTRACT(day FROM NOW()), EXTRACT(day FROM CURRENT_DATE) FROM sys.drillbits; + SELECT EXTRACT(day FROM NOW()), EXTRACT(day FROM CURRENT_DATE) FROM sys.version; +------------+------------+ | EXPR$0 | EXPR$1 | @@ -105,7 +105,7 @@ On the third day of the month, run the following function: At 8:00 am, extract the hour from the value of CURRENT_DATE. - SELECT EXTRACT(hour FROM CURRENT_DATE) FROM sys.drillbits; + SELECT EXTRACT(hour FROM CURRENT_DATE) FROM sys.version; +------------+ | EXPR$0 | @@ -115,7 +115,7 @@ At 8:00 am, extract the hour from the value of CURRENT_DATE. What is the hour component of this time: 17:12:28.5? - SELECT EXTRACT(hour FROM TIME '17:12:28.5') from sys.drillbits; + SELECT EXTRACT(hour FROM TIME '17:12:28.5') from sys.version; +------------+ | EXPR$0 | @@ -126,7 +126,7 @@ What is the hour component of this time: 17:12:28.5? What is the second component of this timestamp: 2001-02-16 20:38:40 - SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40') from sys.drillbits; + SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40') from sys.version; +------------+ | EXPR$0 | @@ -144,7 +144,7 @@ Returns the sum of a date and an interval. #### Example - SELECT CAST(DATE_ADD(datetype(2008, 2, 27), intervaltype(0, 1, 0, 0, 0, 0, 0)) as VARCHAR(100)) FROM sys.drillbits; + SELECT CAST(DATE_ADD(datetype(2008, 2, 27), intervaltype(0, 1, 0, 0, 0, 0, 0)) as VARCHAR(100)) FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -168,7 +168,7 @@ Use Unix Epoch timestamp in milliseconds as the expression to get the field of a #### Examples - SELECT DATE_PART('day', '2015-04-02') FROM sys.drillbits; + SELECT DATE_PART('day', '2015-04-02') FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -176,7 +176,7 @@ Use Unix Epoch timestamp in milliseconds as the expression to get the field of a +------------+ 1 row selected (0.098 seconds) - SELECT DATE_PART('hour', '23:14:30.076') FROM sys.drillbits; + SELECT DATE_PART('hour', '23:14:30.076') FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -186,7 +186,7 @@ Use Unix Epoch timestamp in milliseconds as the expression to get the field of a Find the hour part of the timestamp for April 2, 2015 23:25:43. Use Unix Epoch timestamp in milliseconds, which is 1428017143000 in UTC. - SELECT DATE_PART('hour', 1428017143000) FROM sys.drillbits; + SELECT DATE_PART('hour', 1428017143000) FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -196,7 +196,7 @@ Find the hour part of the timestamp for April 2, 2015 23:25:43. Use Unix Epoch t Return the day part of the one year, 2 months, 10 days interval. - SELECT DATE_PART('day', '1:2:10') FROM sys.drillbits; + SELECT DATE_PART('day', '1:2:10') FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -213,7 +213,7 @@ Returns the sum of a date and an interval. #### Example - SELECT CAST(DATE_SUB(datetype(2008, 2, 27), intervaltype(0, 1, 0, 0, 0, 0, 0)) as VARCHAR(100)) FROM sys.drillbits; + SELECT CAST(DATE_SUB(datetype(2008, 2, 27), intervaltype(0, 1, 0, 0, 0, 0, 0)) as VARCHAR(100)) FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -234,7 +234,7 @@ The utilities are: The following examples show how to use the utilities: - SELECT CURRENT_DATE FROM sys.drillbits; + SELECT CURRENT_DATE FROM sys.version; +--------------+ | current_date | +--------------+ @@ -242,7 +242,7 @@ The following examples show how to use the utilities: +--------------+ 1 row selected (0.077 seconds) - SELECT CURRENT_TIME FROM sys.drillbits; + SELECT CURRENT_TIME FROM sys.version; +--------------+ | current_time | +--------------+ @@ -250,7 +250,7 @@ The following examples show how to use the utilities: +--------------+ 1 row selected (0.073 seconds) - SELECT CURRENT_TIMESTAMP FROM sys.drillbits; + SELECT CURRENT_TIMESTAMP FROM sys.version; +-------------------+ | current_timestamp | +-------------------+ @@ -258,7 +258,7 @@ The following examples show how to use the utilities: +-------------------+ 1 row selected (0.061 seconds) - SELECT LOCALTIME FROM sys.drillbits; + SELECT LOCALTIME FROM sys.version; +------------+ | localtime | @@ -267,7 +267,7 @@ The following examples show how to use the utilities: +------------+ 1 row selected (0.051 seconds) - SELECT LOCALTIMESTAMP FROM sys.drillbits; + SELECT LOCALTIMESTAMP FROM sys.version; +----------------+ | LOCALTIMESTAMP | @@ -276,7 +276,7 @@ The following examples show how to use the utilities: +----------------+ 1 row selected (0.105 seconds) - SELECT NOW() FROM sys.drillbits; + SELECT NOW() FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -286,7 +286,7 @@ The following examples show how to use the utilities: If you set up Drill for [UTC time](/docs/casting-converting-data-types/time-zone-limitation), TIMEOFDAY returns the result for the UTC time zone. - SELECT TIMEOFDAY() FROM sys.drillbits; + SELECT TIMEOFDAY() FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -296,7 +296,7 @@ If you set up Drill for [UTC time](/docs/casting-converting-data-types/time-zone 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.drillbits; + SELECT TIMEOFDAY() FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -311,7 +311,7 @@ date + interval --> Is the day returned from the NOW function the same as the day returned from the CURRENT_DATE function? - SELECT EXTRACT(day FROM NOW()) = EXTRACT(day FROM CURRENT_DATE) FROM sys.drillbits; + SELECT EXTRACT(day FROM NOW()) = EXTRACT(day FROM CURRENT_DATE) FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -321,7 +321,7 @@ Is the day returned from the NOW function the same as the day returned from the Every 23 hours, a 4 hour task started. What time does the task end? - SELECT TIME '04:00:00' + interval '23:00:00' hour to second FROM sys.drillbits; + SELECT TIME '04:00:00' + interval '23:00:00' hour to second FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -331,7 +331,7 @@ Every 23 hours, a 4 hour task started. What time does the task end? Is the time 2:00 PM? - SELECT EXTRACT(hour FROM CURRENT_DATE) = 2 FROM sys.drillbits; + SELECT EXTRACT(hour FROM CURRENT_DATE) = 2 FROM sys.version; +------------+ | EXPR$0 | +------------+ http://git-wip-us.apache.org/repos/asf/drill/blob/91e3f20a/_docs/sql-ref/functions/004-string.md ---------------------------------------------------------------------- diff --git a/_docs/sql-ref/functions/004-string.md b/_docs/sql-ref/functions/004-string.md index fc38d93..04c8ffc 100644 --- a/_docs/sql-ref/functions/004-string.md +++ b/_docs/sql-ref/functions/004-string.md @@ -69,7 +69,7 @@ Returns the number of characters in a string. ### Example - SELECT CHAR_LENGTH('Drill rocks') FROM sys.drillbits; + SELECT CHAR_LENGTH('Drill rocks') FROM sys.version; +------------+ | EXPR$0 | @@ -87,7 +87,7 @@ Concatenates arguments. ### Example - SELECT CONCAT('Drill', ' ', 1.0, ' ', 'release') FROM sys.drillbits; + SELECT CONCAT('Drill', ' ', 1.0, ' ', 'release') FROM sys.version; +------------+ | EXPR$0 | @@ -107,7 +107,7 @@ Returns the string using initial caps. ### Examples - SELECT INITCAP('apache drill release 1.0') FROM sys.drillbits; + SELECT INITCAP('apache drill release 1.0') FROM sys.version; +------------+ | EXPR$0 | @@ -124,7 +124,7 @@ Returns the number of characters in the string. ### Example - SELECT LENGTH('apache drill release 1.0') FROM sys.drillbits; + SELECT LENGTH('apache drill release 1.0') FROM sys.version; +------------+ | EXPR$0 | @@ -154,7 +154,7 @@ Converts characters in the string to lower case. ### Example - SELECT LOWER('Apache Drill') FROM sys.drillbits; + SELECT LOWER('Apache Drill') FROM sys.version; +------------+ | EXPR$0 | @@ -173,7 +173,7 @@ Pads the string to the length specified by prepending the fill or a space. Trunc ### Example - SELECT LPAD('Release 1.0', 27, 'of Apache Drill 1.0') FROM sys.drillbits; + SELECT LPAD('Release 1.0', 27, 'of Apache Drill 1.0') FROM sys.version; +------------+ | EXPR$0 | @@ -191,7 +191,7 @@ Removes the longest string having only characters specified in the second argume ### Example - SELECT LTRIM('Apache Drill', 'Apache ') FROM sys.drillbits; + SELECT LTRIM('Apache Drill', 'Apache ') FROM sys.version; +------------+ | EXPR$0 | @@ -209,7 +209,7 @@ Returns the location of a substring. ### Example - SELECT POSITION('c' in 'Apache Drill') FROM sys.drillbits; + SELECT POSITION('c' in 'Apache Drill') FROM sys.version; +------------+ | EXPR$0 | @@ -274,7 +274,7 @@ Pads the string to the length specified by appending the fill or a space. Trunca ### Example - SELECT RPAD('Apache Drill ', 22, 'Release 1.0') FROM sys.drillbits; + SELECT RPAD('Apache Drill ', 22, 'Release 1.0') FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -291,7 +291,7 @@ Removes the longest string having only characters specified in the second argume ### Example - SELECT RTRIM('Apache Drill', 'Drill ') FROM sys.drillbits; + SELECT RTRIM('Apache Drill', 'Drill ') FROM sys.version; +------------+ | EXPR$0 | @@ -309,7 +309,7 @@ STRPOS(string, substring) ### Example - SELECT STRPOS('Apache Drill', 'Drill') FROM sys.drillbits; + SELECT STRPOS('Apache Drill', 'Drill') FROM sys.version; +------------+ | EXPR$0 | @@ -328,7 +328,7 @@ Extracts characters from position 1 - x of the string an optional y times. ### Example - SELECT SUBSTR('Apache Drill', 8) FROM sys.drillbits; + SELECT SUBSTR('Apache Drill', 8) FROM sys.version; +------------+ | EXPR$0 | @@ -337,7 +337,7 @@ Extracts characters from position 1 - x of the string an optional y times. +------------+ 1 row selected (0.134 seconds) - SELECT SUBSTR('Apache Drill', 3, 2) FROM sys.drillbits; + SELECT SUBSTR('Apache Drill', 3, 2) FROM sys.version; +------------+ | EXPR$0 | @@ -355,7 +355,7 @@ Removes the longest string having only the characters from the beginning, end, o ### Example - SELECT TRIM(trailing 'l' from 'Drill') FROM sys.drillbits; + SELECT TRIM(trailing 'l' from 'Drill') FROM sys.version; +------------+ | EXPR$0 | +------------+ @@ -372,7 +372,7 @@ Converts characters in the string to lower case. ### Example - SELECT UPPER('Apache Drill') FROM sys.drillbits; + SELECT UPPER('Apache Drill') FROM sys.version; +------------+ | EXPR$0 |
