Repository: drill Updated Branches: refs/heads/gh-pages a05706e63 -> f53226a3e
DRILL-2505 Project: http://git-wip-us.apache.org/repos/asf/drill/repo Commit: http://git-wip-us.apache.org/repos/asf/drill/commit/f53226a3 Tree: http://git-wip-us.apache.org/repos/asf/drill/tree/f53226a3 Diff: http://git-wip-us.apache.org/repos/asf/drill/diff/f53226a3 Branch: refs/heads/gh-pages Commit: f53226a3e73fba104c42f28cacb447ef6b976f45 Parents: a05706e Author: Kristine Hahn <kh...@maprtech.com> Authored: Wed Mar 25 18:40:52 2015 -0700 Committer: Bridget Bevens <bbev...@maprtech.com> Committed: Thu Mar 26 13:24:14 2015 -0700 ---------------------------------------------------------------------- _docs/data-sources/004-json-ref.md | 18 +- _docs/query/001-get-started.md | 4 +- _docs/query/query-complex/001-sample-donuts.md | 3 +- _docs/sql-ref/001-data-types.md | 6 +- _docs/sql-ref/004-functions.md | 24 +- _docs/sql-ref/008-sql-extensions.md | 123 ++------ _docs/sql-ref/data-types/001-date.md | 49 ++- _docs/sql-ref/functions/001-math.md | 329 ++++++++++++++++++++ _docs/sql-ref/nested/001-flatten.md | 27 +- _docs/sql-ref/nested/002-kvgen.md | 15 +- _docs/sql-ref/nested/003-repeated-cnt.md | 23 +- _docs/sql-ref/nested/004-repeated-contains.md | 80 +++++ _docs/tutorial/004-lesson2.md | 2 +- 13 files changed, 530 insertions(+), 173 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/drill/blob/f53226a3/_docs/data-sources/004-json-ref.md ---------------------------------------------------------------------- diff --git a/_docs/data-sources/004-json-ref.md b/_docs/data-sources/004-json-ref.md index bbb4386..b80231e 100644 --- a/_docs/data-sources/004-json-ref.md +++ b/_docs/data-sources/004-json-ref.md @@ -89,15 +89,15 @@ You can write data from Drill to a JSON file. The following setup is required: * In the storage plugin definition, include a writable (mutable) workspace. For example: - { - . . . - "workspaces": { - . . . - "myjsonstore": { - "location": "/tmp", - "writable": true, - } - . . . + { + . . . + "workspaces": { + . . . + "myjsonstore": { + "location": "/tmp", + "writable": true, + } + . . . * Set the output format to JSON. For example: http://git-wip-us.apache.org/repos/asf/drill/blob/f53226a3/_docs/query/001-get-started.md ---------------------------------------------------------------------- diff --git a/_docs/query/001-get-started.md b/_docs/query/001-get-started.md index 0b24448..f60b50e 100644 --- a/_docs/query/001-get-started.md +++ b/_docs/query/001-get-started.md @@ -67,10 +67,10 @@ In some cases, such as stopping while a query is in progress, this command does these steps: 1. Issue a CTRL Z to stop the query, then start Drill again. If the startup message indicates success, skip the rest of the steps. If not, proceed to step 2. - 2. Search for the Drill process ID. + 2. Search for the Drill process IDs. $ ps auwx | grep drill - 3. Kill the process using the process number in the grep output. For example: + 3. Kill each process using the process numbers in the grep output. For example: $ sudo kill -9 2674 http://git-wip-us.apache.org/repos/asf/drill/blob/f53226a3/_docs/query/query-complex/001-sample-donuts.md ---------------------------------------------------------------------- diff --git a/_docs/query/query-complex/001-sample-donuts.md b/_docs/query/query-complex/001-sample-donuts.md index 9bace24..f1c7a1f 100644 --- a/_docs/query/query-complex/001-sample-donuts.md +++ b/_docs/query/query-complex/001-sample-donuts.md @@ -2,7 +2,8 @@ title: "Sample Data: Donuts" parent: "Querying Complex Data" --- -The complex data queries use sample `donuts.json` and `moredonuts.json` files. +The complex data queries use the sample `donuts.json` file. To download this file, go to [Drill test resources](https://github.com/apache/drill/tree/master/exec/java-exec/src/test/resources) page, locate donuts.json in the list of files, and download it. For example, on the Mac right-click the five, select Save Link As, and then click Save. + Here is the single complete "record" (`0001`) from the `donuts.json `file. In terms of Drill query processing, this record is equivalent to a single record in a table. http://git-wip-us.apache.org/repos/asf/drill/blob/f53226a3/_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 7e56231..7e370e4 100644 --- a/_docs/sql-ref/001-data-types.md +++ b/_docs/sql-ref/001-data-types.md @@ -122,7 +122,7 @@ The following table lists data types top to bottom, in descending precedence. Dr Drill supports a number of functions to cast and convert compatible data types: * CAST - Casts textual data from one data type to another. + Casts data from one data type to another. * CONVERT_TO and CONVERT_FROM Converts data, including binary data, from one data type to another. * TO_CHAR @@ -136,7 +136,7 @@ Drill supports a number of functions to cast and convert compatible data types: The following tables show data types that Drill can cast to/from other data types. Not all types are available for explicit casting in the current release. -### Explicit type Casting: Numeric and Character types +### Explicit Type Casting: Numeric and Character types <table> <tr> @@ -463,7 +463,7 @@ If the SELECT statement includes a WHERE clause that compares a column of an unk SELECT c_row, CAST(c_int AS DECIMAL(28,8)) FROM mydata WHERE CAST(c_int AS CECIMAL(28,8)) > -3.0 -Although you can use CAST to handle binary data, CONVERT_TO and CONVERT_FROM are recommended for these conversions. +Do not use CAST to handle binary data conversions. Use CONVERT_TO and CONVERT_FROM for these conversions. ### Using CONVERT_TO and CONVERT_FROM http://git-wip-us.apache.org/repos/asf/drill/blob/f53226a3/_docs/sql-ref/004-functions.md ---------------------------------------------------------------------- diff --git a/_docs/sql-ref/004-functions.md b/_docs/sql-ref/004-functions.md index 03cd6ae..e444268 100644 --- a/_docs/sql-ref/004-functions.md +++ b/_docs/sql-ref/004-functions.md @@ -5,6 +5,7 @@ parent: "SQL Reference" You can use the following types of functions in your Drill queries: * Math Functions + * Trig Functions * String Functions * Date/Time Functions * Data Type Formatting Functions @@ -13,23 +14,6 @@ You can use the following types of functions in your Drill queries: * Convert and Cast Functions * Nested Data Functions -## Math - -You can use the following scalar math functions in your Drill queries: - - * ABS - * CEIL - * CEILING - * DIV - * FLOOR - * MOD - * POWER - * RANDOM - * ROUND - * SIGN - * SQRT - * TRUNC - ## String Functions The following table provides the string functions that you can use in your @@ -136,11 +120,7 @@ CONVERT_FROM function on HBase, Drill decodes the data and converts it to the specified data type. In instances where Drill sends data back to HBase during a query, you can use the CONVERT_TO function to change the data type to bytes. -Although you can achieve the same results by using the CAST function for some -data types (such as VARBINARY to VARCHAR conversions), in general it is more -efficient to use CONVERT functions when your data sources return binary data. -When your data sources return more conventional data types, you can use the -CAST function. +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 other cases. CONVERT functions not only work regardless of the types you are converting but are also more efficient to use than CAST when your data sources return binary data. The following table provides the data types that you use with the CONVERT_TO and CONVERT_FROM functions: http://git-wip-us.apache.org/repos/asf/drill/blob/f53226a3/_docs/sql-ref/008-sql-extensions.md ---------------------------------------------------------------------- diff --git a/_docs/sql-ref/008-sql-extensions.md b/_docs/sql-ref/008-sql-extensions.md index 1eb5f65..ce5be8f 100644 --- a/_docs/sql-ref/008-sql-extensions.md +++ b/_docs/sql-ref/008-sql-extensions.md @@ -4,79 +4,26 @@ parent: "SQL Reference" --- Drill extends SQL to work with Hadoop-scale data and to explore smaller-scale data in ways not possible with SQL. Using intuitive SQL extensions you work with self-describing data and complex data types. Extensions to SQL include capabilities for exploring self-describing data, such as files and HBase, directly in the native format. -Drill provides language support for pointing to [storage plugin]() interfaces that Drill uses to interact with data sources. Use the name of a storage plugin to specify a file system *database* as a prefix in queries when you refer to objects across databases. Query files, including compressed .gz files and directories like an SQL table using a single query. +Drill provides language support for pointing to [storage plugin]() interfaces that Drill uses to interact with data sources. Use the name of a storage plugin to specify a file system *database* as a prefix in queries when you refer to objects across databases. Query files, including compressed .gz files, and [directories](/docs/querying-directories), as you would query an SQL table. You can query [multiple files in a directory](/docs/lesson-3-create-a-storage-plugin/query-multiple-files-in-a-directory). -Drill extends the SELECT statement for reading complex, multi-structured data. The extended CREATE TABLE AS SELECT, provides the capability to write data of complex/multi-structured data types. Drill extends the [lexical rules](http://drill.apache.org/docs/lexical-structure) for working with files and directories, such as using back ticks for including file names, directory names, and reserved words in queries. Drill syntax supports using the file system as a persistent store for query profiles and diagnostic information. +Drill extends the SELECT statement for reading complex, multi-structured data. The extended CREATE TABLE AS SELECT provides the capability to write data of complex/multi-structured data types. Drill extends the [lexical rules](http://drill.apache.org/docs/lexical-structure) for working with files and directories, such as using back ticks for including file names, directory names, and reserved words in queries. Drill syntax supports using the file system as a persistent store for query profiles and diagnostic information. ## Extensions for Hive- and HBase-related Data Sources -Drill supports Hive and HBase as a plug-and-play data source. You can query Hive tables with no modifications and creating model in the Hive metastore. Primitives, such as JOIN, support columnar operation. +Drill supports Hive and HBase as a plug-and-play data source. Drill can read tables created in Hive that use [data types compatible](/docs/hive-to-drill-data-type-mapping) with Drill. You can query Hive tables without modifications. You can query self-describing data without requiring metadata definitions in the Hive metastore. Primitives, such as JOIN, support columnar operation. ## Extensions for JSON-related Data Sources -For reading all JSON data as text, use the all text mode extension. Drill extends SQL to provide access to repeating values in arrays and arrays within arrays (array indexes). You can use these extensions to reach into deeply nested data. Drill extensions use standard JavaScript notation for referencing data elements in a hierarchy. +For reading all JSON data as text, use the [all text mode](http://drill.apache.org/docs/handling-different-data-types/#all-text-mode-option) extension. Drill extends SQL to provide access to repeating values in arrays and arrays within arrays (array indexes). You can use these extensions to reach into deeply nested data. Drill extensions use standard JavaScript notation for referencing data elements in a hierarchy, as shown in ["Analyzing JSON."](/docs/json-data-model#analyzing-json) -## Extensions for Text Data Sources -Drill handles plain text files and directories like standard SQL tables and can infer knowledge about the schema of the data. You can query compressed .gz files. - -## SQL Commands Extensions +## Extensions for Parquet Data Sources +SQL does not support all Parquet data types, so Drill infers data types in many instances. Users [cast] (/docs/sql-functions) data types to ensure getting a particular data type. Drill offers more liberal casting capabilities than SQL for Parquet conversions if the Parquet data is of a logical type. You can use the default dfs storage plugin installed with Drill for reading and writing Parquet files as shown in the section, [âParquet Format.â](/docs/parquet-format) -The following table describes key Drill extensions to SQL commands. -<table> - <tr> - <th>Command</th> - <th>SQL</th> - <th>Drill</th> - </tr> - <tr> - <td>ALTER (SESSION | SYSTEM)</td> - <td>None</td> - <td>Changes a system or session option.</td> - </tr> - <tr> - <td>CREATE TABLE AS SELECT</td> - <td>Creates a table from selected data in an existing database table.</td> - <td>Stores selected data from one or more data sources on the file system.</td> - </tr> - <tr> - <td>CREATE VIEW</td> - <td>Creates a virtual table. The fields in a view are fields from one or more real tables in the database.</td> - <td>Creates a virtual structure for and stores the result set. The fields in a view are fields from files in a file system, Hive, Hbase, MapR-DB tables</td> - </tr> - <tr> - <td>DESCRIBE</td> - <td>Obtains information about the <select list> columns</td> - <td>Obtains information about views created in a workspace and tables created in Hive, HBase, and MapR-DB.</td> - </tr> - <tr> - <td>EXPLAIN</td> - <td>None</td> - <td>Obtains a query execution plan.</td> - </tr> - <tr> - <td>INSERT</td> - <td>Loads data into the database for querying.</td> - <td>No INSERT function. Performs schema-on-read querying and execution; no need to load data into Drill for querying.</td> - </tr> - <tr> - <td>SELECT</td> - <td>Retrieves rows from a database table or view.</td> - <td>Retrieves data from Hbase, Hive, MapR-DB, file system or other storage plugin data source.</td> - </tr> - <tr> - <td>SHOW (DATABASES | SCHEMAS | FILES | TABLES)</td> - <td>None</td> - <td>Lists the storage plugin data sources available for querying or the Hive, Hbase, MapR-DB tables, or views for the data source in use. Supports a FROM clause for listing file data sources in directories.</td> - </tr> - <tr> - <td>USE</td> - <td>Targets a database in SQL schema for querying.</td> - <td>Targets Hbase, Hive, MapR-DB, file system or other storage plugin data source, which can be schema-less for querying.</td> - </tr> -</table> +## Extensions for Text Data Sources +Drill handles plain text files and directories like standard SQL tables and can infer knowledge about the schema of the data. Drill extends SQL to handle structured file types, such as comma separated values (CSV) files. An extension of the SELECT statement provides COLUMNS[n] syntax for accessing CSV rows in a readable format, as shown in ["COLUMNS[n] Syntax."](/docs/querying-plain-text-files) ## SQL Function Extensions -The following table describes key Drill functions for analyzing nested data. +Drill provides the following functions for analyzing nested data. <table> <tr> @@ -85,51 +32,41 @@ The following table describes key Drill functions for analyzing nested data. <th>Drill</th> </tr> <tr> - <td>CAST</td> - <td>Casts database data from one type to another.</td> - <td>Casts database data from one type to another and also casts data having no metadata into a readable type. Allows liberal casting of schema-less data.</td> - </tr> - <tr> - <td>CONVERT_TO</td> - <td>Converts an expression from one type to another using the CONVERT command.</td> - <td>Converts an SQL data type to complex types, including Hbase byte arrays, JSON and Parquet arrays and maps.</td> - </tr> - <tr> - <td>CONVERT_FROM</td> - <td>Same as above</td> - <td>Converts from complex types, including Hbase byte arrays, JSON and Parquet arrays and maps to an SQL data type.</td> - </tr> - <tr> - <td>FLATTEN</td> + <td><a href='http://drill.apache.org/docs/flatten-function'>FLATTEN</a> </td> <td>None</td> <td>Separates the elements in nested data from a repeated field into individual records.</td> </tr> <tr> - <td>KVGEN</td> + <td><a href='http://drill.apache.org/docs/kvgen-function'>KVGEN</a></td> <td>None</td> <td>Returns a repeated map, generating key-value pairs to simplify querying of complex data having unknown column names. You can then aggregate or filter on the key or value.</td> </tr> <tr> - <td>REPEATED_COUNT</td> + <td><a href='http://drill.apache.org/docs/repeated-count-function'>REPEATED_COUNT</a></td> + <td>None</td> + <td>Counts the values in an array.</td> + </tr> + <tr> + <td><a href='http://drill.apache.org/docs/repeated-contains'>REPEATED_CONTAINS</a></td> <td>None</td> - <td>Counts the values in a JSON array.</td> + <td>Searches for a keyword in an array.</td> </tr> </table> ## Other Extensions -[`sys` database system tables]() provide port, version, and option information. Drill Connects to a random node, know where youâre connected: +The [`sys` database system tables]() provide port, version, and option information. For example, Drill connects to a random node. You query the sys table to know where you are connected: -select host from sys.drillbits where `current` = true; -+------------+ -| host | -+------------+ -| 10.1.1.109 | -+------------+ + SELECT host FROM sys.drillbits WHERE `current` = true; + +------------+ + | host | + +------------+ + | 10.1.1.109 | + +------------+ -select commit_id from sys.version; -+------------+ -| commit_id | -+------------+ -| e3ab2c1760ad34bda80141e2c3108f7eda7c9104 | + SELECT commit_id FROM sys.version; + +------------+ + | commit_id | + +------------+ + | e3ab2c1760ad34bda80141e2c3108f7eda7c9104 | http://git-wip-us.apache.org/repos/asf/drill/blob/f53226a3/_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 88fe008..87f93ba 100644 --- a/_docs/sql-ref/data-types/001-date.md +++ b/_docs/sql-ref/data-types/001-date.md @@ -16,29 +16,29 @@ Next, use the following literals in a SELECT statement. * `time` * `timestamp` - SELECT date '2010-2-15' FROM dfs.`/Users/drilluser/apache-drill-0.8.0/dummy.json`; - +------------+ - | EXPR$0 | - +------------+ - | 2010-02-15 | - +------------+ - 1 row selected (0.083 seconds) - - SELECT time '15:20:30' from dfs.`/Users/drilluser/apache-drill-0.8.0/dummy.json`; - +------------+ - | EXPR$0 | - +------------+ - | 15:20:30 | - +------------+ - 1 row selected (0.067 seconds) - - SELECT timestamp '2015-03-11 6:50:08' FROM dfs.`/Users/drilluser/apache-drill-0.8.0/dummy.json`; - +------------+ - | EXPR$0 | - +------------+ - | 2015-03-11 06:50:08.0 | - +------------+ - 1 row selected (0.071 seconds) + SELECT date '2010-2-15' FROM dfs.`/Users/drilluser/apache-drill-0.8.0/dummy.json`; + +------------+ + | EXPR$0 | + +------------+ + | 2010-02-15 | + +------------+ + 1 row selected (0.083 seconds) + + SELECT time '15:20:30' from dfs.`/Users/drilluser/apache-drill-0.8.0/dummy.json`; + +------------+ + | EXPR$0 | + +------------+ + | 15:20:30 | + +------------+ + 1 row selected (0.067 seconds) + + SELECT timestamp '2015-03-11 6:50:08' FROM dfs.`/Users/drilluser/apache-drill-0.8.0/dummy.json`; + +------------+ + | EXPR$0 | + +------------+ + | 2015-03-11 06:50:08.0 | + +------------+ + 1 row selected (0.071 seconds) ## INTERVAL @@ -120,8 +120,7 @@ The following CTAS statement shows how to cast text from a JSON file to INTERVAL cast( INTERVALDAY_col as interval day) INTERVALDAY_col FROM `/user/root/intervals.json`); -Output is: +<!-- Text and include output --> -TBD need to test in a future build. http://git-wip-us.apache.org/repos/asf/drill/blob/f53226a3/_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 new file mode 100644 index 0000000..829611d --- /dev/null +++ b/_docs/sql-ref/functions/001-math.md @@ -0,0 +1,329 @@ +--- +title: "Math and Trig" +parent: "SQL Functions" +--- +Drill supports the math functions shown in the following table plus trig functions listed at the end of this section. Most math functions and all trig functions take these input types: + +* INT +* BIGINT +* FLOAT4 +* FLOAT8 +* SMALLINT +* UINT1 +* UINT2 +* UINT4 +* UINT8 + +Exceptions are the LSHIFT and RSHIFT functions, which take all types except the float types. DEGREES, EXP, RADIANS, and the multiple LOG functions take the input types in this list plus the following additional types: + +* DECIMAL9 +* DECIMAL18 + +**Math Functions** + +<table> + <tr> + <th>Function</th> + <th>Return Type</th> + <th>Description</th> + </tr> + <tr> + <td>ABS(x)</td> + <td>Same as input</td> + <td>Returns the absolute value of the input argument x.</td> + </tr> + <tr> + <td>CBRT(x)</td> + <td>FLOAT8</td> + <td>Returns the cubic root of x.</td> + </tr> + <tr> + <td>CEIL(x)</td> + <td>Same as input</td> + <td>Returns the smallest integer not less than x.</td> + </tr> + <tr> + <td>CEILING(x)</td> + <td>Same as input</td> + <td>Same as CEIL.</td> + </tr> + <tr> + <td>DEGREES(x)</td> + <td>FLOAT8</td> + <td>Converts x radians to degrees.</td> + </tr> + <tr> + <td>EXP(x)</td> + <td>FLOAT8</td> + <td>Returns e (Euler's number) to the power of x.</td> + </tr> + <tr> + <td>FLOOR(x)</td> + <td>Same as input</td> + <td>Returns the largest integer not greater than x.</td> + </tr> + <tr> + <td>LOG(x)</td> + <td>FLOAT8</td> + <td>Returns the log value of x.</td> + </tr> + <tr> + <td>LSHIFT(x, y)</td> + <td>Same as input</td> + <td>Shifts the binary x by y times to the left.</td> + </tr> + <tr> + <td>RADIANS</td> + <td>FLOAT8</td> + <td>Converts x degress to radians.</td> + </tr> + <tr> + <td>ROUND(x)</td> + <td>Same as input</td> + <td>Rounds to the nearest integer.</td> + </tr> + <tr> + <td>ROUND(x, y)</td> + <td>DECIMAL</td> + <td>Rounds x to s decimal places.</td> + </tr> + <tr> + <td>RSHIFT(x, y)</td> + <td>Same as input</td> + <td>Shifts the binary x by y times to the right.</td> + </tr> + <tr> + <td>SIGN(x)</td> + <td>INT</td> + <td>Returns the sign of x.</td> + </tr> + <tr> + <td>SQRT(x)</td> + <td>Same as input</td> + <td>Returns the square root of x.</td> + </tr> + <tr> + <td>TRUNC(x)</td> + <td>Same as input</td> + <td>Truncates x toward zero.</td> + </tr> + <tr> + <td>TRUNC(x, y)</td> + <td>DECIMAL</td> + <td>Truncates x to y decimal places.</td> + </tr> +</table> + +## Math Function Examples + +Examples in this section use the following files: + +* The `input2.json` file +* A dummy 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. On the Mac, for example, right-click input2.json and choose Save Link As, and then click Save. + +Create the a dummy JSON file having the following contents: + + {"dummy" : "data"} + +#### ABS Example +Get the absolute value of the integer key in `input2.json`. The following snippet of input2.json shows the relevant integer content: + + { "integer" : 2010, + "float" : 17.4, + "x": { + "y": "kevin", + "z": "paul" + . . . + } + { "integer" : -2002, + "float" : -1.2 + } + . . . + + SELECT `integer` FROM dfs.`/Users/drill/input2.json`; + + +------------+ + | integer | + +------------+ + | 2010 | + | -2002 | + | 2001 | + | 6005 | + +------------+ + 4 rows selected (0.113 seconds) + + SELECT ABS(`integer`) FROM dfs.`/Users/drill/input2.json`; + + +------------+ + | EXPR$0 | + +------------+ + | 2010 | + | 2002 | + | 2001 | + | 6005 | + +------------+ + 4 rows selected (0.357 seconds) + +## CEIL Example +Get the ceiling of float key values in input2.json. The input2.json file contains these float key values: + +* 17.4 +* -1.2 +* 1.2 +* 1.2 + + SELECT CEIL(`float`) FROM dfs.`/Users/drill/input2.json`; + + +------------+ + | EXPR$0 | + +------------+ + | 18.0 | + | -1.0 | + | 2.0 | + | 2.0 | + +------------+ + 4 rows selected (0.647 seconds) + +### FLOOR Example +Get the floor of float key values in input2.json. + + SELECT FLOOR(`float`) FROM dfs.`/Users/drill/input2.json`; + + +------------+ + | EXPR$0 | + +------------+ + | 17.0 | + | -2.0 | + | 1.0 | + | 1.0 | + +------------+ + 4 rows selected (0.11 seconds) + +### ROUND Examples +Open input2.json and change the first float value from 17.4 to 3.14159. Get values of the float columns in input2.json rounded as follows: + +* Rounded to the nearest integer. +* Rounded to the fourth decimal place. + + SELECT ROUND(`float`) FROM dfs.`/Users/khahn/Documents/test_files_source/input2.json`; + + +------------+ + | EXPR$0 | + +------------+ + | 3.0 | + | -1.0 | + | 1.0 | + | 1.0 | + +------------+ + 4 rows selected (0.061 seconds) + + SELECT ROUND(`float`, 4) FROM dfs.`/Users/khahn/Documents/test_files_source/input2.json`; + + +------------+ + | EXPR$0 | + +------------+ + | 3.1416 | + | -1.2 | + | 1.2 | + | 1.2 | + +------------+ + 4 rows selected (0.059 seconds) + +## Log Examples + +Get the base 2 log of 64. + + SELECT log(2, 64) FROM dfs.`/Users/drill/dummy.json`; + + +------------+ + | EXPR$0 | + +------------+ + | 6.0 | + +------------+ + 1 row selected (0.069 seconds) + +Get the common log of 100. + + SELECT log10(100) FROM dfs.`/Users/drill/dummy.json`; + + +------------+ + | EXPR$0 | + +------------+ + | 2.0 | + +------------+ + 1 row selected (0.203 seconds) + +Get the natural log of 7.5. + + SELECT log(7.5) FROM dfs.`/Users/drill/sample-data/dummy.json`; + + +------------+ + | EXPR$0 | + +------------+ + | 2.0149030205422647 | + +------------+ + 1 row selected (0.063 seconds) + +**Trig Functions** + +Drill supports the following trig functions, which return a FLOAT8 result. + +* SIN(x) + Sine of angle x in radians + +* COS(x) + Cosine of angle x in radians + +* TAN(x) + Tangent of angle x in radians + +* ASIN(x) + Inverse sine of angle x in radians + +* ACOS(x) + Inverse cosine of angle x in radians + +* ATAN(x) + Inverse tangent of angle x in radians + +* SINH() + Hyperbolic sine of hyperbolic angle x in radians + +* COSH() + Hyperbolic cosine of hyperbolic angle x in radians + +* TANH() + Hyperbolic tangent of hyperbolic angle x in radians + +**Examples** + +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 dfs.`/Users/drill/dummy.json`; + + +------------+ + | Degrees | + +------------+ + | 0.7853981633974483 | + +------------+ + 1 row selected (0.045 seconds) + + SELECT SIN(0.7853981633974483) AS `Sine of 30 degrees` FROM dfs.`/Users/drill/dummy.json`; + + +-----------------------+ + | Sine of 45 degrees | + +-----------------------+ + | 0.7071067811865475 | + +-----------------------+ + 1 row selected (0.059 seconds) + + SELECT TAN(0.7853981633974483) AS `Tangent of 30 degrees` from dfs.`/Users/drill/dummy.json`; + + +-----------------------+ + | Tangent of 45 degrees | + +-----------------------+ + | 0.9999999999999999 | + +-----------------------+ + http://git-wip-us.apache.org/repos/asf/drill/blob/f53226a3/_docs/sql-ref/nested/001-flatten.md ---------------------------------------------------------------------- diff --git a/_docs/sql-ref/nested/001-flatten.md b/_docs/sql-ref/nested/001-flatten.md index fdebbf4..0a6b7aa 100644 --- a/_docs/sql-ref/nested/001-flatten.md +++ b/_docs/sql-ref/nested/001-flatten.md @@ -2,11 +2,22 @@ title: "FLATTEN Function" parent: "Nested Data Functions" --- +FLATTEN separates the elements in a repeated field into individual records. + +## Syntax + + FLATTEN(z) + +*z* is a JSON array. + +## Usage Notes + The FLATTEN function is useful for flexible exploration of repeated data. -FLATTEN separates the elements in a repeated field into individual records. To -maintain the association between each flattened value and the other fields in -the record, all of the other columns are copied into each new record. A very -simple example would turn this data (one record): + +To maintain the association between each flattened value and the other fields in +the record, the FLATTEN function copies all of the other columns into each new record. + +A very simple example would turn this data (one record): { "x" : 5, @@ -16,7 +27,7 @@ simple example would turn this data (one record): into three distinct records: - select flatten(z) from table; + SELECT FLATTEN(z) FROM table; | x | y | z | +-------------+----------------+-----------+ | 5 | "a string" | 1 | @@ -26,7 +37,9 @@ into three distinct records: The function takes a single argument, which must be an array (the `z` column in this example). - +Using the all (*) wildcard as the argument to flatten is not supported and returns an error. + +## Examples For a more interesting example, consider the JSON data in the publicly available [Yelp](https://www.yelp.com/dataset_challenge/dataset) data set. The @@ -85,5 +98,5 @@ the categories array, then run a COUNT function on the flattened result: +---------------|------------+ A common use case for FLATTEN is its use in conjunction with the -[KVGEN](/docs/flatten-function) function. +[KVGEN](/docs/flatten-function) function as shown in the section, ["JSON Data Model"](/docs/json-data-model/). http://git-wip-us.apache.org/repos/asf/drill/blob/f53226a3/_docs/sql-ref/nested/002-kvgen.md ---------------------------------------------------------------------- diff --git a/_docs/sql-ref/nested/002-kvgen.md b/_docs/sql-ref/nested/002-kvgen.md index dd4edd3..97c3e76 100644 --- a/_docs/sql-ref/nested/002-kvgen.md +++ b/_docs/sql-ref/nested/002-kvgen.md @@ -2,6 +2,16 @@ title: "KVGEN Function" parent: "Nested Data Functions" --- +Return a list of the keys that exist in the map. + +## Syntax + + KVGEN(column) + +*column* is the name of a column. + +## Usage Notes + KVGEN stands for _key-value generation_. This function is useful when complex data files contain arbitrary maps that consist of relatively "unknown" column names. Instead of having to specify columns in the map to access the data, you @@ -13,8 +23,6 @@ keys or constrain the keys in some way. For example, you can use the [FLATTEN](/docs/flatten-function) function to break the array down into multiple distinct rows and further query those rows. - - For example, assume that a JSON file contains this data: {"a": "valA", "b": "valB"} @@ -146,5 +154,4 @@ distinct rows: +------------+ 9 rows selected (0.151 seconds) -See the description of [FLATTEN](/docs/flatten-function) -for an example of a query against the flattened data. \ No newline at end of file +For more examples of KVGEN and FLATTEN, see the examples in the section, ["JSON Data Model"](/docs/json-data-model). \ No newline at end of file http://git-wip-us.apache.org/repos/asf/drill/blob/f53226a3/_docs/sql-ref/nested/003-repeated-cnt.md ---------------------------------------------------------------------- diff --git a/_docs/sql-ref/nested/003-repeated-cnt.md b/_docs/sql-ref/nested/003-repeated-cnt.md index f15eeda..531c8ad 100644 --- a/_docs/sql-ref/nested/003-repeated-cnt.md +++ b/_docs/sql-ref/nested/003-repeated-cnt.md @@ -2,7 +2,23 @@ title: "REPEATED_COUNT Function" parent: "Nested Data Functions" --- -This function counts the values in an array. The following example returns the +This function counts the values in an array. + +## Syntax + + REPEATED_COUNT (array) + +*array* is the name of an array. + +## Usage Notes + +The COUNT function requires a single argument, which must be an array. Note that +this function is not a standard SQL aggregate function and does not require +the count to be grouped by other columns in the select list (such as `name` in +this example). + +## Example +The following example returns the counts for the `categories` array in the `yelp_academic_dataset_business.json` file. The counts are restricted to rows that contain the string `pizza`. @@ -24,10 +40,5 @@ file. The counts are restricted to rows that contain the string `pizza`. 7 rows selected (2.03 seconds) -The function requires a single argument, which must be an array. Note that -this function is not a standard SQL aggregate function and does not require -the count to be grouped by other columns in the select list (such as `name` in -this example). - For another example of this function, see the following lesson in the Apache Drill Tutorial for Hadoop: [Lesson 3: Run Queries on Complex Data Types](/docs/lession-3-run-queries-on-complex-data-types/). \ No newline at end of file http://git-wip-us.apache.org/repos/asf/drill/blob/f53226a3/_docs/sql-ref/nested/004-repeated-contains.md ---------------------------------------------------------------------- diff --git a/_docs/sql-ref/nested/004-repeated-contains.md b/_docs/sql-ref/nested/004-repeated-contains.md new file mode 100644 index 0000000..cd12760 --- /dev/null +++ b/_docs/sql-ref/nested/004-repeated-contains.md @@ -0,0 +1,80 @@ +--- +title: "REPEATED_CONTAINS Function" +parent: "Nested Data Functions" +--- +REPEATED CONTAINS searches for a keyword in an array. + +## Syntax + + REPEATED_CONTAINS(array_name, keyword) + +* array_name is a simple array, such as topping: + + { + . . . + "topping": + [ + "None", + "Glazed", + "Sugar", + "Powdered Sugar", + "Chocolate with Sprinkles", + "Chocolate", + "Maple" + ] + } + +* keyword is a value in the array, such as 'Glazed'. + +## Usage Notes +REPEATED_CONTAINS returns true if Drill finds a match; otherwise, the function returns false. The function supports regular expression wildcards, such as *, ., and ?, but not at the beginning of the keyword. Enclose keyword string values in single quotation marks. Do not enclose numerical keyword values in single quotation marks. + +## Examples +The examples in this section `testRepeatedWrite.json`. To download this file, go to [Drill test resources](https://github.com/apache/drill/tree/master/exec/java-exec/src/test/resources) page, locate testRepeatedWrite.json.json in the list of files, and download it. For example, on the Mac right-click the five, select Save Link As, and then click Save. + +Which donuts having glazed or glaze toppings? + + SELECT name, REPEATED_CONTAINS(topping, 'Glaze?') AS `Glazed?` FROM dfs.`/Users/drill/testRepeatedWrite.json` WHERE type='donut'; + + +------------+------------+ + | name | Glazed? | + +------------+------------+ + | Cake | true | + | Raised | true | + | Old Fashioned | true | + | Filled | true | + | Apple Fritter | true | + +------------+------------+ + 5 rows selected (0.072 seconds) + +Which objects have powered sugar toppings? Use the asterisk wildcard instead of typing the entire keyword pair. + + SELECT name, REPEATED_CONTAINS(topping, 'P*r') AS `Powdered Sugar?` FROM dfs.`/Users/khahn/Documents/test_files_source/testRepeatedWrite.json` WHERE type='donut'; + + +------------+-----------------+ + | name | Powdered Sugar? | + +------------+-----------------+ + | Cake | true | + | Raised | true | + | Old Fashioned | false | + | Filled | true | + | Apple Fritter | false | + +------------+-----------------+ + 5 rows selected (0.089 seconds) + +Which donuts have toppings beginning with the letters "Map" and ending in any two letters? + + SELECT name, REPEATED_CONTAINS(topping, 'Map..') AS `Maple?` FROM dfs.`/Users/drill/testRepeatedWrite.json` WHERE type='donut'; + + +------------+------------+ + | name | Maple? | + +------------+------------+ + | Cake | true | + | Raised | true | + | Old Fashioned | true | + | Filled | true | + | Apple Fritter | false | + +------------+------------+ + 5 rows selected (0.085 seconds) + + http://git-wip-us.apache.org/repos/asf/drill/blob/f53226a3/_docs/tutorial/004-lesson2.md ---------------------------------------------------------------------- diff --git a/_docs/tutorial/004-lesson2.md b/_docs/tutorial/004-lesson2.md index 5b29b7b..eca22bb 100644 --- a/_docs/tutorial/004-lesson2.md +++ b/_docs/tutorial/004-lesson2.md @@ -237,7 +237,7 @@ register any sales in that state. Note the following features of this query: - * The CAST function is required for every column in the table. This function returns the MapR-DB/HBase binary data as readable integers and strings. Alternatively, you can use CONVERT_TO/CONVERT_FROM functions to decode the columns. CONVERT_TO and CONVERT_FROM are more efficient than CAST in most cases. + * The CAST function is required for every column in the table. This function returns the MapR-DB/HBase binary data as readable integers and strings. Alternatively, you can use CONVERT_TO/CONVERT_FROM functions to decode the string columns. CONVERT_TO/CONVERT_FROM are more efficient than CAST in most cases. Use only CONVERT_TO to convert binary types to any type other than VARCHAR. * The row_key column functions as the primary key of the table (a customer ID in this case). * The table alias t is required; otherwise the column family names would be parsed as table names and the query would return an error.
