Repository: drill Updated Branches: refs/heads/gh-pages 2856ae488 -> fbc18c480
http://git-wip-us.apache.org/repos/asf/drill/blob/2a34ac89/_docs/query/get-started/003-lesson3-plugin.md ---------------------------------------------------------------------- diff --git a/_docs/query/get-started/003-lesson3-plugin.md b/_docs/query/get-started/003-lesson3-plugin.md new file mode 100644 index 0000000..9aab881 --- /dev/null +++ b/_docs/query/get-started/003-lesson3-plugin.md @@ -0,0 +1,142 @@ +--- +title: "Lesson 3: Create a Storage Plugin" +parent: "Getting Started Tutorial" +--- +The Drill default storage plugins support common file formats. If you need +support for some other file format, create a custom storage plugin. You can also create a storage plugin to simplify querying file having long path names. A workspace name replaces the long path name. + +This lesson covers how to create and use a storage plugin to simplify queries. First, +you create the storage plugin in the Drill Web UI. Next, you connect to the +file through the plugin to query a file, and then a directory, and finally you +query multiple files in a directory. + +## Create a Storage Plugin + +You can create a storage plugin using the Apache Drill Web UI. + + 1. Create an `ngram` directory on your file system. + 2. Copy `googlebooks-eng-all-5gram-20120701-zo.tsv` to the `ngram` directory. + 3. Open the Drill Web UI by navigating to <http://localhost:8047/storage>. + To open the Drill Web UI, SQLLine must still be running. + 4. In New Storage Plugin, type `myplugin`. +  + 5. Click **Create**. + The Configuration screen appears. + 6. Replace null with the following storage plugin definition, except on the location line, use the path to your `ngram` directory instead of the drilluser's path and give your workspace an arbitrary name, for example, ngram: + + { + "type": "file", + "enabled": true, + "connection": "file:///", + "workspaces": { + "ngram": { + "location": "/Users/drilluser/ngram", + "writable": false, + "defaultInputFormat": null + } + }, + "formats": { + "tsv": { + "type": "text", + "extensions": [ + "tsv" + ], + "delimiter": "\t" + } + } + } + + 7. Click **Create**. + The success message appears briefly. + 8. Click **Back**. + The new plugin appears in Enabled Storage Plugins. +  + 9. Go back to the SQLLine prompt in the CLI, and list the storage plugins. Press RETURN in the CLI to get a prompt if necessary. + +Your custom plugin appears in the list and has two workspaces: the `ngram` +workspace that you defined and a default workspace. + +## Connect to and Query a File + +When querying the same data source repeatedly, avoiding long path names is +important. This exercise demonstrates how to simplify the query. Instead of +using the full path to the Ngram file, you use dot notation in the FROM +clause. + +``<workspace name>.`<location>``` + +This syntax assumes you connected to a storage plugin that defines the +location of the data. To query the data source while you are _not_ connected to +that storage plugin, include the plugin name: + +``<plugin name>.<workspace name>.`<location>``` + +This exercise shows how to query Ngram data when you are, and when you are +not, connected to `myplugin`. + + 1. Connect to the ngram file through the custom storage plugin. + `USE myplugin;` + 2. Get data about "Zoological Journal of the Linnean" that appears more than 250 times a year in the books that Google scans. In the FROM clause, instead of using the full path to the file as you did in the last exercise, connect to the data using the storage plugin workspace name ngram. + + SELECT COLUMNS[0], + COLUMNS[1], + COLUMNS[2] + FROM ngram.`/googlebooks-eng-all-5gram-20120701-zo.tsv` + WHERE ((columns[0] = 'Zoological Journal of the Linnean') + AND (columns[2] > 250)) + LIMIT 10; + + The output consists of 5 rows of data. + 3. Switch to the `dfs` storage plugin. + + 0: jdbc:drill:zk=local> USE dfs; + +------------+------------+ + | ok | summary | + +------------+------------+ + | true | Default schema changed to 'dfs' | + +------------+------------+ + 1 row selected (0.019 seconds) + 4. Query the TSV file again. Because you switched to `dfs`, Drill does not know the location of the file. To provide the information to Drill, preface the file name with the storage plugin and workspace names in the FROM clause. + + SELECT COLUMNS[0], + COLUMNS[1], + COLUMNS[2] + FROM myplugin.ngram.`/googlebooks-eng-all-5gram-20120701-zo.tsv` + WHERE ((columns[0] = 'Zoological Journal of the Linnean') + AND (columns[2] > 250)) + LIMIT 10; + +## Query Multiple Files in a Directory + +In this exercise, first you create a subdirectory in the `ngram` directory. +Next, you download, unzip, and add an extension to a second Ngram file. You +move both Ngram TSV files to the subdirectory. Finally, using the custom +plugin workspace, you query both files. In the FROM clause, simply reference +the subdirectory. + + 1. Download a second file of compressed Google Ngram data from this location: + + http://storage.googleapis.com/books/ngrams/books/googlebooks-eng-all-2gram-20120701-ze.gz + 2. Unzip `googlebooks-eng-all-2gram-20120701-ze.gz` and move `googlebooks-eng-all-2gram-20120701-ze` to the `ngram/myfiles` subdirectory. + 3. Change the name of `googlebooks-eng-all-2gram-20120701-ze` to add a `.tsv` extension. + 4. Move the 5gram file you worked with earlier `googlebooks-eng-all-5gram-20120701-zo.tsv` from the `ngram` directory to the `ngram/myfiles` subdirectory. + 5. At the SQLLine prompt, use the `myplugin.ngrams` workspace. + + USE myplugin.ngram; + 6. Query the myfiles directory for the "Zoological Journal of the Linnean" or "zero temperatures" in books published in 1998. + + SELECT * + FROM myfiles + WHERE (((COLUMNS[0] = 'Zoological Journal of the Linnean') + OR (COLUMNS[0] = 'zero temperatures')) + AND (COLUMNS[1] = '1998')); +The output lists ngrams from both files. + + +------------+ + | columns | + +------------+ + | ["Zoological Journal of the Linnean","1998","157","53"] | + | ["zero temperatures","1998","628","487"] | + +------------+ + 2 rows selected (5.316 seconds) + http://git-wip-us.apache.org/repos/asf/drill/blob/2a34ac89/_docs/sql-ref/003-functions.md ---------------------------------------------------------------------- diff --git a/_docs/sql-ref/003-functions.md b/_docs/sql-ref/003-functions.md index 4769257..bc57a39 100644 --- a/_docs/sql-ref/003-functions.md +++ b/_docs/sql-ref/003-functions.md @@ -4,15 +4,16 @@ parent: "SQL Reference" --- You can use the following types of functions in your Drill queries: - * Scalar Functions + * Math Functions + * String Functions + * Date/Time Functions + * Data Type Formatting Functions * Aggregate Functions * Aggregate Statistics Functions - * Convert Functions + * Convert and Cast Functions * Nested Data Functions -## Scalar Functions - -### Math +## Math You can use the following scalar math functions in your Drill queries: @@ -29,7 +30,7 @@ You can use the following scalar math functions in your Drill queries: * SQRT * TRUNC -### String Functions +## String Functions The following table provides the string functions that you can use in your Drill queries: @@ -60,7 +61,7 @@ trim([leading | trailing | both] [characters] from string)| text upper(string)| text -### Date/Time Functions +## Date/Time Functions The following table provides the date/time functions that you can use in your Drill queries: @@ -81,7 +82,7 @@ localtimestamp| timestamp now()| timestamp with time zone timeofday()| text -### Data Type Formatting Functions +## Data Type Formatting Functions The following table provides the data type formatting functions that you can use in your Drill queries: @@ -126,7 +127,7 @@ var_pop(expression)| smallint, int, bigint, real, double precision, or numeric| var_samp(expression)| smallint, int, bigint, real, double precision, or numeric| double precision for floating-point arguments, otherwise numeric -## Convert Functions +## Convert and Cast Functions You can use the CONVERT_TO and CONVERT_FROM functions to encode and decode data when you query your data sources with Drill. For example, HBase stores http://git-wip-us.apache.org/repos/asf/drill/blob/2a34ac89/_docs/sql-ref/005-cmd-summary.md ---------------------------------------------------------------------- diff --git a/_docs/sql-ref/005-cmd-summary.md b/_docs/sql-ref/005-cmd-summary.md index 13d9515..8082f88 100644 --- a/_docs/sql-ref/005-cmd-summary.md +++ b/_docs/sql-ref/005-cmd-summary.md @@ -5,5 +5,5 @@ parent: "SQL Reference" The following table provides a list of the SQL commands that Drill supports, with their descriptions and example syntax: -<table ><tbody><tr><th >Command</th><th >Description</th><th >Syntax</th></tr><tr><td valign="top" >ALTER SESSION</td><td valign="top" >Changes a system setting for the duration of a session. A session ends when you quit the Drill shell. For a list of Drill options and their descriptions, refer to <a href="/drill/docs/planning-and-execution-options" rel="nofollow">Planning and Execution Options</a>.</td><td valign="top" ><code>ALTER SESSION SET `option_name`='string';<br />ALTER SESSION SET `option_name`=TRUE | FALSE;</code></td></tr><tr><td valign="top" >ALTER SYSTEM</td><td valign="top" >Permanently changes a system setting. The new settings persist across all sessions. For a list of Drill options and their descriptions, refer to <a href="/drill/docs/planning-and-execution-options/" rel="nofollow">Planning and Execution Options</a>.</td><td valign="top" ><code>ALTER SYSTEM `option_name`='string'<br />ALTER SYSTEM `option_name`=TRUE | FALSE;</code></td></tr><tr><td valign="top" ><a href="/drill/docs/create-table-as-ctas-command">CREATE TABLE AS<br />(CTAS)</a></p></td><td valign="top" >Creates a new table and populates the new table with rows returned from a SELECT query. Use the CREATE TABLE AS (CTAS) statement in place of INSERT INTO. When you issue the CTAS command, you create a directory that contains parquet or CSV files. Each workspace in a file system has a default file type.<p>You can specify which writer you want Drill to use when creating a table: parquet, CSV, or JSON (as specified with <span style="line-height: 1.4285715;">the </span><code>store.format</code><span style="line-height: 1.4285715;"> option<span><span>).</span></span></span></p></td><td valign="top" ><code>CREATE TABLE new_table_name AS <query>;</code></td></tr><tr><td valign="top" colspan="1" >CREATE VIEW</td><td valign="top" colspan="1" >Creates a new view based on the results of a SELECT query.</td><td valign="top" colspan="1" ><code>CREATE VIEW view_name [(column_list)] AS & lt;query>;</code></td></tr><tr><td valign="top" colspan="1" >DROP VIEW</td><td valign="top" colspan="1" >Removes one or more views.</td><td valign="top" colspan="1" ><code>DROP VIEW view_name [, <em class="replaceable">view_name</em>] ...; </code></td></tr><tr><td valign="top" colspan="1" ><a href="/drill/docs/explain-commands" rel="nofollow">EXPLAIN PLAN FOR</a></td><td valign="top" colspan="1" >Returns the physical plan for a particular query.</td><td valign="top" colspan="1" ><code>EXPLAIN PLAN FOR <query>;</code></td></tr><tr><td valign="top" colspan="1" ><a href="/drill/docs/explain-commands/" rel="nofollow">EXPLAIN PLAN WITHOUT IMPLEMENTATION FOR</a></td><td valign="top" colspan="1" >Returns the logical plan for a particular query.</td><td valign="top" colspan="1" ><code>EXPLAIN PLAN WITHOUT IMPLEMENTATION FOR <query>;</code></td></tr><tr><td valign="top" colspan="1" ><a href="/drill/docs/select-statements" rel="nofollow">SELECT</a></td><td valign="top" cols pan="1" >Retrieves data from tables and files.</td><td valign="top" colspan="1" ><code>[WITH subquery]<br />SELECT column_list FROM table_name <br />[WHERE clause]<br />[GROUP BY clause]<br />[HAVING clause]<br />[ORDER BY clause];</code></td></tr><tr><td valign="top" colspan="1" >SHOW DATABASES</td><td valign="top" colspan="1" >Returns a list of available schemas. Equivalent to SHOW SCHEMAS.</td><td valign="top" colspan="1" ><code>SHOW DATABASES;</code></td></tr><tr><td valign="top" colspan="1" ><a href="/drill/docs/show-files-command/" rel="nofollow">SHOW FILES</a></td><td valign="top" colspan="1" >Returns a list of files in a file system schema.</td><td valign="top" colspan="1" ><code>SHOW FILES IN filesystem.`schema_name`;<br />SHOW FILES FROM filesystem.`schema_name`;</code></td></tr><tr><td valign="top" colspan="1" >SHOW SCHEMAS</td><td valign="top" colspan="1" >Returns a list of available schemas. Equivalent to SHOW DATABASES.</td><td valign="top" colspan="1" ><code>SHOW SCHE MAS;</code></td></tr><tr><td valign="top" colspan="1" >SHOW TABLES</td><td valign="top" colspan="1" >Returns a list of tables for all schemas. Optionally, you can first issue the <code>USE </code>command to identify the schema for which you want to view tables.<br />For example, the following <code>USE</code> statement tells Drill that you only want information from the <code>hive.default</code> schema:<br /><code>USE hive.`default`;</code></td><td valign="top" colspan="1" ><code>SHOW TABLES;</code></td></tr><tr><td valign="top" colspan="1" >USE</td><td valign="top" colspan="1" >Change to a particular schema. When you opt to use a particular schema, Drill issues queries on that schema only.</td><td valign="top" colspan="1" ><code>USE schema_name;</code></td></tr></tbody></table> +<table ><tbody><tr><th >Command</th><th >Description</th><th >Syntax</th></tr><tr><td valign="top" >ALTER SESSION</td><td valign="top" >Changes a system setting for the duration of a session. A session ends when you quit the Drill shell. For a list of Drill options and their descriptions, refer to <a href="/drill/docs/planning-and-execution-options" rel="nofollow">Planning and Execution Options</a>.</td><td valign="top" ><code>ALTER SESSION SET `option_name`='string';<br />ALTER SESSION SET `option_name`=TRUE | FALSE;</code></td></tr><tr><td valign="top" >ALTER SYSTEM</td><td valign="top" >Permanently changes a system setting. The new settings persist across all sessions. For a list of Drill options and their descriptions, refer to <a href="/drill/docs/planning-and-execution-options/" rel="nofollow">Planning and Execution Options</a>.</td><td valign="top" ><code>ALTER SYSTEM `option_name`='string'<br />ALTER SYSTEM `option_name`=TRUE | FALSE;</code></td></tr><tr><td valign="top" ><a href="/drill/docs/create-table-as-ctas-command">CREATE TABLE AS<br />(CTAS)</a></p></td><td valign="top" >Creates a new table and populates the new table with rows returned from a SELECT query. Use the CREATE TABLE AS (CTAS) statement in place of INSERT INTO. When you issue the CTAS command, you create a directory that contains parquet or CSV files. Each workspace in a file system has a default file type.<p>You can specify which writer you want Drill to use when creating a table: parquet, CSV, or JSON (as specified with <span style="line-height: 1.4285715;">the </span><code>store.format</code><span style="line-height: 1.4285715;"> option<span><span>).</span></span></span></p></td><td valign="top" ><code>CREATE TABLE new_table_name AS <query>;</code></td></tr><tr><td valign="top" colspan="1" >CREATE VIEW</td><td valign="top" colspan="1" >Creates a new view based on the results of a SELECT query.</td><td valign="top" colspan="1" ><code>CREATE VIEW view_name [(column_list)] AS & lt;query>;</code></td></tr><tr><td valign="top" colspan="1" >DROP VIEW</td><td valign="top" colspan="1" >Removes one or more views.</td><td valign="top" colspan="1" ><code>DROP VIEW view_name [, <em class="replaceable">view_name</em>] ...; </code></td></tr><tr><td valign="top" colspan="1" ><a href="/drill/docs/explain-commands" rel="nofollow">EXPLAIN PLAN FOR</a></td><td valign="top" colspan="1" >Returns the physical plan for a particular query.</td><td valign="top" colspan="1" ><code>EXPLAIN PLAN FOR <query>;</code></td></tr><tr><td valign="top" colspan="1" ><a href="/drill/docs/explain-commands/" rel="nofollow">EXPLAIN PLAN WITHOUT IMPLEMENTATION FOR</a></td><td valign="top" colspan="1" >Returns the logical plan for a particular query.</td><td valign="top" colspan="1" ><code>EXPLAIN PLAN WITHOUT IMPLEMENTATION FOR <query>;</code></td></tr><tr><td valign="top" colspan="1" ><a href="/drill/docs/select-statements" rel="nofollow">SELECT</a></td><td valign="top" cols pan="1" >Retrieves data from tables and files.</td><td valign="top" colspan="1" ><code>[WITH subquery]<br />SELECT column_list FROM table_name <br />[WHERE clause]<br />[GROUP BY clause]<br />[HAVING clause]<br />[ORDER BY clause];</code></td></tr><tr><td valign="top" colspan="1" >SHOW DATABASES</td><td valign="top" colspan="1" >Returns a list of available schemas. Equivalent to SHOW SCHEMAS.</td><td valign="top" colspan="1" ><code>SHOW DATABASES;</code></td></tr><tr><td valign="top" colspan="1" ><a href="/drill/docs/show-files-command/" rel="nofollow">SHOW FILES</a></td><td valign="top" colspan="1" >Returns a list of files in a file system schema.</td><td valign="top" colspan="1" ><code>SHOW FILES IN filesystem.`schema_name`;<br />SHOW FILES FROM filesystem.`schema_name`;</code></td></tr><tr><td valign="top" colspan="1" >SHOW SCHEMAS</td><td valign="top" colspan="1" >Returns a list of available schemas. Equivalent to SHOW DATABASES.</td><td valign="top" colspan="1" ><code>SHOW SCHE MAS;</code></td></tr><tr><td valign="top" colspan="1" >SHOW TABLES</td><td valign="top" colspan="1" >Returns a list of tables for all schemas. Optionally, you can first issue the <code>USE </code>command to identify the schema for which you want to view tables.<br />For example, the following <code>USE</code> statement tells Drill that you only want information from the <code>hive.default</code> schema:<br /><code>USE hive.`default`;</code></td><td valign="top" colspan="1" ><code>SHOW TABLES;</code></td></tr><tr><td valign="top" colspan="1" >USE</td><td valign="top" colspan="1" >Change to a particular schema. When you opt to use a particular schema, Drill issues queries on that schema only.</td><td valign="top" colspan="1" ><code>USE schema_name;</code></td></tr></tbody></table></div> http://git-wip-us.apache.org/repos/asf/drill/blob/2a34ac89/_docs/sql-ref/006-reserved-wds.md ---------------------------------------------------------------------- diff --git a/_docs/sql-ref/006-reserved-wds.md b/_docs/sql-ref/006-reserved-wds.md index a19f73c..d3cd280 100644 --- a/_docs/sql-ref/006-reserved-wds.md +++ b/_docs/sql-ref/006-reserved-wds.md @@ -12,5 +12,5 @@ keyword: The following table provides the Drill reserved keywords that require back ticks: -<table ><tbody><tr><td valign="top" ><h1 id="ReservedKeywords-A">A</h1><p>ABS<br />ALL<br />ALLOCATE<br />ALLOW<br />ALTER<br />AND<br />ANY<br />ARE<br />ARRAY<br />AS<br />ASENSITIVE<br />ASYMMETRIC<br />AT<br />ATOMIC<br />AUTHORIZATION<br />AVG</p><h1 id="ReservedKeywords-B">B</h1><p>BEGIN<br />BETWEEN<br />BIGINT<br />BINARY<br />BIT<br />BLOB<br />BOOLEAN<br />BOTH<br />BY</p><h1 id="ReservedKeywords-C">C</h1><p>CALL<br />CALLED<br />CARDINALITY<br />CASCADED<br />CASE<br />CAST<br />CEIL<br />CEILING<br />CHAR<br />CHARACTER<br />CHARACTER_LENGTH<br />CHAR_LENGTH<br />CHECK<br />CLOB<br />CLOSE<br />COALESCE<br />COLLATE<br />COLLECT<br />COLUMN<br />COMMIT<br />CONDITION<br />CONNECT<br />CONSTRAINT<br />CONVERT<br />CORR<br />CORRESPONDING<br />COUNT<br />COVAR_POP<br />COVAR_SAMP<br />CREATE<br />CROSS<br />CUBE<br />CUME_DIST<br />CURRENT<br />CURRENT_CATALOG<br />CURRENT_DATE<br />CURRENT_DEFAULT_TRANSFORM_GROUP<br />CURRENT_PATH<br />CURRENT_ROLE<br />CURRENT_SCHEMA<br />CURRENT_TIME<br />CURRENT_TIMESTAMP<br />CURRENT_TRANSFORM_GROUP_FOR_TYPE<br />CURRENT_USER<br />CURSOR<br />CYCLE</p></td><td valign="top" ><h1 id="ReservedKeywords-D">D</h1><p>DATABASES<br />DATE<br />DAY<br />DEALLOCATE<br />DEC<br />DECIMAL<br />DECLARE<br />DEFAULT<br />DEFAULT_KW<br />DELETE<br />DENSE_RANK<br />DEREF<br />DESCRIBE<br />DETERMINISTIC<br />DISALLOW<br />DISCONNECT<br />DISTINCT<br />DOUBLE<br />DROP<br />DYNAMIC</p><h1 id="ReservedKeywords-E">E</h1><p>EACH<br />ELEMENT<br />ELSE<br />END<br />END_EXEC<br />ESCAPE<br />EVERY<br />EXCEPT<br />EXEC<br />EXECUTE<br />EXISTS<br />EXP<br />EXPLAIN<br />EXTERNAL<br />EXTRACT</p><h1 id="ReservedKeywords-F">F</h1><p>FALSE<br />FETCH<br />FILES<br />FILTER<br />FIRST_VALUE<br />FLOAT<br />FLOOR<br />FOR<br />FOREIGN<br />FREE<br />FROM<br />FULL<br />FUNCTION<br />FUSION</p><h1 id="ReservedKeywords-G">G</h1><p>GET<br />GLOBAL<br />GRANT<br />GROUP<br />GROUPING</p><h1 id="ReservedKeywords-H">H</h1><p>HAVING<br />HOLD<b r />HOUR</p></td><td valign="top" ><h1 id="ReservedKeywords-I">I</h1><p>IDENTITY<br />IMPORT<br />IN<br />INDICATOR<br />INNER<br />INOUT<br />INSENSITIVE<br />INSERT<br />INT<br />INTEGER<br />INTERSECT<br />INTERSECTION<br />INTERVAL<br />INTO<br />IS</p><h1 id="ReservedKeywords-J">J</h1><p>JOIN</p><h1 id="ReservedKeywords-L">L</h1><p>LANGUAGE<br />LARGE<br />LAST_VALUE<br />LATERAL<br />LEADING<br />LEFT<br />LIKE<br />LIMIT<br />LN<br />LOCAL<br />LOCALTIME<br />LOCALTIMESTAMP<br />LOWER</p><h1 id="ReservedKeywords-M">M</h1><p>MATCH<br />MAX<br />MEMBER<br />MERGE<br />METHOD<br />MIN<br />MINUTE<br />MOD<br />MODIFIES<br />MODULE<br />MONTH<br />MULTISET</p><h1 id="ReservedKeywords-N">N</h1><p>NATIONAL<br />NATURAL<br />NCHAR<br />NCLOB<br />NEW<br />NO<br />NONE<br />NORMALIZE<br />NOT<br />NULL<br />NULLIF<br />NUMERIC</p><h1 id="ReservedKeywords-O">O</h1><p>OCTET_LENGTH<br />OF<br />OFFSET<br />OLD<br />ON<br />ONLY<br />OPEN<br />OR<br />ORDER<br />OUT<br />OUTER<br />OVER< br />OVERLAPS<br />OVERLAY</p></td><td valign="top" colspan="1" ><h1 id="ReservedKeywords-P">P</h1><p>PARAMETER<br />PARTITION<br />PERCENTILE_CONT<br />PERCENTILE_DISC<br />PERCENT_RANK<br />POSITION<br />POWER<br />PRECISION<br />PREPARE<br />PRIMARY<br />PROCEDURE</p><h1 id="ReservedKeywords-R">R</h1><p>RANGE<br />RANK<br />READS<br />REAL<br />RECURSIVE<br />REF<br />REFERENCES<br />REFERENCING<br />REGR_AVGX<br />REGR_AVGY<br />REGR_COUNT<br />REGR_INTERCEPT<br />REGR_R2<br />REGR_SLOPE<br />REGR_SXX<br />REGR_SXY<br />RELEASE<br />REPLACE<br />RESULT<br />RETURN<br />RETURNS<br />REVOKE<br />RIGHT<br />ROLLBACK<br />ROLLUP<br />ROW<br />ROWS<br />ROW_NUMBER</p><h1 id="ReservedKeywords-S">S</h1><p>SAVEPOINT<br />SCHEMAS<br />SCOPE<br />SCROLL<br />SEARCH<br />SECOND<br />SELECT<br />SENSITIVE<br />SESSION_USER<br />SET<br />SHOW<br />SIMILAR<br />SMALLINT<br />SOME<br />SPECIFIC<br />SPECIFICTYPE<br />SQL<br />SQLEXCEPTION<br />SQLSTATE<br />SQLWARNING<br />SQRT<br />START<br / >STATIC<br />STDDEV_POP<br />STDDEV_SAMP<br />SUBMULTISET<br />SUBSTRING<br >/>SUM<br />SYMMETRIC<br />SYSTEM<br />SYSTEM_USER</p></td><td valign="top" >colspan="1" ><h1 id="ReservedKeywords-T">T</h1><p>TABLE<br />TABLES<br >/>TABLESAMPLE<br />THEN<br />TIME<br />TIMESTAMP<br />TIMEZONE_HOUR<br >/>TIMEZONE_MINUTE<br />TINYINT<br />TO<br />TRAILING<br />TRANSLATE<br >/>TRANSLATION<br />TREAT<br />TRIGGER<br />TRIM<br />TRUE</p><h1 >id="ReservedKeywords-U">U</h1><p>UESCAPE<br />UNION<br />UNIQUE<br >/>UNKNOWN<br />UNNEST<br />UPDATE<br />UPPER<br />USE<br />USER<br >/>USING</p><h1 id="ReservedKeywords-V">V</h1><p>VALUE<br />VALUES<br >/>VARBINARY<br />VARCHAR<br />VARYING<br />VAR_POP<br />VAR_SAMP</p><h1 >id="ReservedKeywords-W">W</h1><p>WHEN<br />WHENEVER<br />WHERE<br >/>WIDTH_BUCKET<br />WINDOW<br />WITH<br />WITHIN<br />WITHOUT</p><h1 >id="ReservedKeywords-Y">Y</h1><p>YEAR</p></td></tr></tbody></table> +<table ><tbody><tr><td valign="top" ><h1 id="ReservedKeywords-A">A</h1><p>ABS<br />ALL<br />ALLOCATE<br />ALLOW<br />ALTER<br />AND<br />ANY<br />ARE<br />ARRAY<br />AS<br />ASENSITIVE<br />ASYMMETRIC<br />AT<br />ATOMIC<br />AUTHORIZATION<br />AVG</p><h1 id="ReservedKeywords-B">B</h1><p>BEGIN<br />BETWEEN<br />BIGINT<br />BINARY<br />BIT<br />BLOB<br />BOOLEAN<br />BOTH<br />BY</p><h1 id="ReservedKeywords-C">C</h1><p>CALL<br />CALLED<br />CARDINALITY<br />CASCADED<br />CASE<br />CAST<br />CEIL<br />CEILING<br />CHAR<br />CHARACTER<br />CHARACTER_LENGTH<br />CHAR_LENGTH<br />CHECK<br />CLOB<br />CLOSE<br />COALESCE<br />COLLATE<br />COLLECT<br />COLUMN<br />COMMIT<br />CONDITION<br />CONNECT<br />CONSTRAINT<br />CONVERT<br />CORR<br />CORRESPONDING<br />COUNT<br />COVAR_POP<br />COVAR_SAMP<br />CREATE<br />CROSS<br />CUBE<br />CUME_DIST<br />CURRENT<br />CURRENT_CATALOG<br />CURRENT_DATE<br />CURRENT_DEFAULT_TRANSFORM_GROUP<br />CURRENT_PATH<br />CURRENT_ROLE<br />CURRENT_SCHEMA<br />CURRENT_TIME<br />CURRENT_TIMESTAMP<br />CURRENT_TRANSFORM_GROUP_FOR_TYPE<br />CURRENT_USER<br />CURSOR<br />CYCLE</p></td><td valign="top" ><h1 id="ReservedKeywords-D">D</h1><p>DATABASES<br />DATE<br />DAY<br />DEALLOCATE<br />DEC<br />DECIMAL<br />DECLARE<br />DEFAULT<br />DEFAULT_KW<br />DELETE<br />DENSE_RANK<br />DEREF<br />DESCRIBE<br />DETERMINISTIC<br />DISALLOW<br />DISCONNECT<br />DISTINCT<br />DOUBLE<br />DROP<br />DYNAMIC</p><h1 id="ReservedKeywords-E">E</h1><p>EACH<br />ELEMENT<br />ELSE<br />END<br />END_EXEC<br />ESCAPE<br />EVERY<br />EXCEPT<br />EXEC<br />EXECUTE<br />EXISTS<br />EXP<br />EXPLAIN<br />EXTERNAL<br />EXTRACT</p><h1 id="ReservedKeywords-F">F</h1><p>FALSE<br />FETCH<br />FILES<br />FILTER<br />FIRST_VALUE<br />FLOAT<br />FLOOR<br />FOR<br />FOREIGN<br />FREE<br />FROM<br />FULL<br />FUNCTION<br />FUSION</p><h1 id="ReservedKeywords-G">G</h1><p>GET<br />GLOBAL<br />GRANT<br />GROUP<br />GROUPING</p><h1 id="ReservedKeywords-H">H</h1><p>HAVING<br />HOLD<b r />HOUR</p></td><td valign="top" ><h1 id="ReservedKeywords-I">I</h1><p>IDENTITY<br />IMPORT<br />IN<br />INDICATOR<br />INNER<br />INOUT<br />INSENSITIVE<br />INSERT<br />INT<br />INTEGER<br />INTERSECT<br />INTERSECTION<br />INTERVAL<br />INTO<br />IS</p><h1 id="ReservedKeywords-J">J</h1><p>JOIN</p><h1 id="ReservedKeywords-L">L</h1><p>LANGUAGE<br />LARGE<br />LAST_VALUE<br />LATERAL<br />LEADING<br />LEFT<br />LIKE<br />LIMIT<br />LN<br />LOCAL<br />LOCALTIME<br />LOCALTIMESTAMP<br />LOWER</p><h1 id="ReservedKeywords-M">M</h1><p>MATCH<br />MAX<br />MEMBER<br />MERGE<br />METHOD<br />MIN<br />MINUTE<br />MOD<br />MODIFIES<br />MODULE<br />MONTH<br />MULTISET</p><h1 id="ReservedKeywords-N">N</h1><p>NATIONAL<br />NATURAL<br />NCHAR<br />NCLOB<br />NEW<br />NO<br />NONE<br />NORMALIZE<br />NOT<br />NULL<br />NULLIF<br />NUMERIC</p><h1 id="ReservedKeywords-O">O</h1><p>OCTET_LENGTH<br />OF<br />OFFSET<br />OLD<br />ON<br />ONLY<br />OPEN<br />OR<br />ORDER<br />OUT<br />OUTER<br />OVER< br />OVERLAPS<br />OVERLAY</p></td><td valign="top" colspan="1" ><h1 id="ReservedKeywords-P">P</h1><p>PARAMETER<br />PARTITION<br />PERCENTILE_CONT<br />PERCENTILE_DISC<br />PERCENT_RANK<br />POSITION<br />POWER<br />PRECISION<br />PREPARE<br />PRIMARY<br />PROCEDURE</p><h1 id="ReservedKeywords-R">R</h1><p>RANGE<br />RANK<br />READS<br />REAL<br />RECURSIVE<br />REF<br />REFERENCES<br />REFERENCING<br />REGR_AVGX<br />REGR_AVGY<br />REGR_COUNT<br />REGR_INTERCEPT<br />REGR_R2<br />REGR_SLOPE<br />REGR_SXX<br />REGR_SXY<br />RELEASE<br />REPLACE<br />RESULT<br />RETURN<br />RETURNS<br />REVOKE<br />RIGHT<br />ROLLBACK<br />ROLLUP<br />ROW<br />ROWS<br />ROW_NUMBER</p><h1 id="ReservedKeywords-S">S</h1><p>SAVEPOINT<br />SCHEMAS<br />SCOPE<br />SCROLL<br />SEARCH<br />SECOND<br />SELECT<br />SENSITIVE<br />SESSION_USER<br />SET<br />SHOW<br />SIMILAR<br />SMALLINT<br />SOME<br />SPECIFIC<br />SPECIFICTYPE<br />SQL<br />SQLEXCEPTION<br />SQLSTATE<br />SQLWARNING<br />SQRT<br />START<br / >STATIC<br />STDDEV_POP<br />STDDEV_SAMP<br />SUBMULTISET<br />SUBSTRING<br >/>SUM<br />SYMMETRIC<br />SYSTEM<br />SYSTEM_USER</p></td><td valign="top" >colspan="1" ><h1 id="ReservedKeywords-T">T</h1><p>TABLE<br />TABLES<br >/>TABLESAMPLE<br />THEN<br />TIME<br />TIMESTAMP<br />TIMEZONE_HOUR<br >/>TIMEZONE_MINUTE<br />TINYINT<br />TO<br />TRAILING<br />TRANSLATE<br >/>TRANSLATION<br />TREAT<br />TRIGGER<br />TRIM<br />TRUE</p><h1 >id="ReservedKeywords-U">U</h1><p>UESCAPE<br />UNION<br />UNIQUE<br >/>UNKNOWN<br />UNNEST<br />UPDATE<br />UPPER<br />USE<br />USER<br >/>USING</p><h1 id="ReservedKeywords-V">V</h1><p>VALUE<br />VALUES<br >/>VARBINARY<br />VARCHAR<br />VARYING<br />VAR_POP<br />VAR_SAMP</p><h1 >id="ReservedKeywords-W">W</h1><p>WHEN<br />WHENEVER<br />WHERE<br >/>WIDTH_BUCKET<br />WINDOW<br />WITH<br />WITHIN<br />WITHOUT</p><h1 >id="ReservedKeywords-Y">Y</h1><p>YEAR</p></td></tr></tbody></table></div> http://git-wip-us.apache.org/repos/asf/drill/blob/2a34ac89/_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 6340e35..94f3bf7 100644 --- a/_docs/sql-ref/data-types/001-date.md +++ b/_docs/sql-ref/data-types/001-date.md @@ -135,14 +135,14 @@ supports the `interval day` data type in the following format: The following table provides examples for `interval day` data type: -<table ><tbody><tr><th >Use</th><th >Example</th></tr><tr><td valign="top">Literal</td><td valign="top"><code><span style="color: rgb(0,0,0);">select interval '1 10:20:30.123' day to second from dfs.`/tmp/input.json`;<br /></span><span style="color: rgb(0,0,0);">select interval '1 10' day to hour from dfs.`/tmp/input.json`;<br /></span><span style="color: rgb(0,0,0);">select interval '10' day from dfs.`/tmp/input.json`;<br /></span><span style="color: rgb(0,0,0);">select interval '10' hour from dfs.`/tmp/input.json`;</span></code><code><span style="color: rgb(0,0,0);">select interval '10.999' second from dfs.`/tmp/input.json`;</span></code></td></tr><tr><td colspan="1" valign="top"><code>JSON</code> Input</td><td colspan="1" valign="top"><code><span style="color: rgb(0,0,0);">{"col" : "P1DT10H20M30S"}<br /></span><span style="color: rgb(0,0,0);">{"col" : "P1DT10H20M30.123S"}<br /></span><span style="color: rgb(0,0,0);">{"col" : &q uot;P1D"}<br /></span><span style="color: rgb(0,0,0);">{"col" : "PT10H"}<br /></span><span style="color: rgb(0,0,0);">{"col" : "PT10.10S"}<br /></span><span style="color: rgb(0,0,0);">{"col" : "PT20S"}<br /></span><span style="color: rgb(0,0,0);">{"col" : "PT10H10S"}</span></code></td></tr><tr><td colspan="1" valign="top"><code>CAST</code> from <code>VARCHAR</code></td><td colspan="1" valign="top"><code><span style="color: rgb(0,0,0);">select cast(col as interval day) from dfs.`/tmp/input.json`;</span></code></td></tr></tbody></table> +<table><tbody><tr><th>Use</th><th>Example</th></tr><tr><td valign="top">Literal</td><td valign="top"><code><span style="color: rgb(0,0,0);">select interval '1 10:20:30.123' day to second from dfs.`/tmp/input.json`;<br /></span><span style="color: rgb(0,0,0);">select interval '1 10' day to hour from dfs.`/tmp/input.json`;<br /></span><span style="color: rgb(0,0,0);">select interval '10' day from dfs.`/tmp/input.json`;<br /></span><span style="color: rgb(0,0,0);">select interval '10' hour from dfs.`/tmp/input.json`;</span></code><code><span style="color: rgb(0,0,0);">select interval '10.999' second from dfs.`/tmp/input.json`;</span></code></td></tr><tr><td colspan="1" valign="top"><code>JSON</code> Input</td><td colspan="1" valign="top"><code><span style="color: rgb(0,0,0);">{"col" : "P1DT10H20M30S"}<br /></span><span style="color: rgb(0,0,0);">{"col" : "P1DT10H20M30.123S"}<br /></span><span style="color: rgb(0,0,0);">{"col" : " ;P1D"}<br /></span><span style="color: rgb(0,0,0);">{"col" : "PT10H"}<br /></span><span style="color: rgb(0,0,0);">{"col" : "PT10.10S"}<br /></span><span style="color: rgb(0,0,0);">{"col" : "PT20S"}<br /></span><span style="color: rgb(0,0,0);">{"col" : "PT10H10S"}</span></code></td></tr><tr><td colspan="1" valign="top"><code>CAST</code> from <code>VARCHAR</code></td><td colspan="1" valign="top"><code><span style="color: rgb(0,0,0);">select cast(col as interval day) from dfs.`/tmp/input.json`;</span></code></td></tr></tbody></table></div> ## Literal The following table provides a list of `date/time` literals that Drill supports with examples of each: -<table ><tbody><tr><th >Format</th><th colspan="1" >Interpretation</th><th >Example</th></tr><tr><td colspan="1" valign="top"><code><span style="color: rgb(0,0,0);">interval '1 10:20:30.123' day to second</span></code></td><td colspan="1" valign="top"><code>1 day, 10 hours, 20 minutes, 30 seconds, and 123 thousandths of a second</code></td><td colspan="1" valign="top"><code><span style="color: rgb(0,0,0);">select interval '1 10:20:30.123' day to second from dfs.`/tmp/input.json`;</span></code></td></tr><tr><td colspan="1" valign="top"><code><span style="color: rgb(0,0,0);">interval '1 10' day to hour</span></code></td><td colspan="1" valign="top"><code>1 day 10 hours</code></td><td colspan="1" valign="top"><code><span style="color: rgb(0,0,0);">select interval '1 10' day to hour from dfs.`/tmp/input.json`;</span></code></td></tr><tr><td colspan="1" valign="top"><code><span style="color: rgb(0,0,0);">interval '10' day</span></code></td><td colspan="1" valign="top"><code>10 days</code ></td><td colspan="1" valign="top"><code><span style="color: >rgb(0,0,0);">select interval '10' day from >dfs.`/tmp/input.json`;</span></code></td></tr><tr><td colspan="1" >valign="top"><code><span style="color: rgb(0,0,0);">interval '10' >hour</span></code></td><td colspan="1" valign="top"><code>10 >hours</code></td><td colspan="1" valign="top"><code><span style="color: >rgb(0,0,0);">select interval '10' hour from >dfs.`/tmp/input.json`;</span></code></td></tr><tr><td colspan="1" >valign="top"><code><span style="color: rgb(0,0,0);">interval '10.999' >second</span></code></td><td colspan="1" valign="top"><code>10.999 >seconds</code></td><td colspan="1" valign="top"><code><span style="color: >rgb(0,0,0);">select interval '10.999' second from dfs.`/tmp/input.json`; ></span></code></td></tr></tbody></table> +<table ><tbody><tr><th >Format</th><th colspan="1" >Interpretation</th><th >Example</th></tr><tr><td colspan="1" valign="top"><code><span style="color: rgb(0,0,0);">interval '1 10:20:30.123' day to second</span></code></td><td colspan="1" valign="top"><code>1 day, 10 hours, 20 minutes, 30 seconds, and 123 thousandths of a second</code></td><td colspan="1" valign="top"><code><span style="color: rgb(0,0,0);">select interval '1 10:20:30.123' day to second from dfs.`/tmp/input.json`;</span></code></td></tr><tr><td colspan="1" valign="top"><code><span style="color: rgb(0,0,0);">interval '1 10' day to hour</span></code></td><td colspan="1" valign="top"><code>1 day 10 hours</code></td><td colspan="1" valign="top"><code><span style="color: rgb(0,0,0);">select interval '1 10' day to hour from dfs.`/tmp/input.json`;</span></code></td></tr><tr><td colspan="1" valign="top"><code><span style="color: rgb(0,0,0);">interval '10' day</span></code></td><td colspan="1" valign="top"><code>10 days</code ></td><td colspan="1" valign="top"><code><span style="color: >rgb(0,0,0);">select interval '10' day from >dfs.`/tmp/input.json`;</span></code></td></tr><tr><td colspan="1" >valign="top"><code><span style="color: rgb(0,0,0);">interval '10' >hour</span></code></td><td colspan="1" valign="top"><code>10 >hours</code></td><td colspan="1" valign="top"><code><span style="color: >rgb(0,0,0);">select interval '10' hour from >dfs.`/tmp/input.json`;</span></code></td></tr><tr><td colspan="1" >valign="top"><code><span style="color: rgb(0,0,0);">interval '10.999' >second</span></code></td><td colspan="1" valign="top"><code>10.999 >seconds</code></td><td colspan="1" valign="top"><code><span style="color: >rgb(0,0,0);">select interval '10.999' second from dfs.`/tmp/input.json`; ></span></code></td></tr></tbody></table></div> http://git-wip-us.apache.org/repos/asf/drill/blob/2a34ac89/_docs/tutorial/005-lesson3.md ---------------------------------------------------------------------- diff --git a/_docs/tutorial/005-lesson3.md b/_docs/tutorial/005-lesson3.md index f6c7ae4..d08e8eb 100644 --- a/_docs/tutorial/005-lesson3.md +++ b/_docs/tutorial/005-lesson3.md @@ -279,7 +279,7 @@ ordered list of products purchased rather than a random list). | 17192 | 2014-04-22 | 2501 | AOS4.2 | 26 | true | ... -This query uses an SQL extension, the repeated_count function, to get an +This query uses a Drill extension of SQL, the repeated_count function, to get an aggregated count of the array values. The query returns the number of products searched for each session that converted into a purchase and ranks the counts in descending order. Only clicks that have resulted in a purchase are counted.
