You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@drill.apache.org by br...@apache.org on 2015/03/18 21:28:16 UTC
[2/3] drill git commit: DRILL-2490
http://git-wip-us.apache.org/repos/asf/drill/blob/923bad37/_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 bcb506c..39b1468 100644
--- a/_docs/sql-ref/001-data-types.md
+++ b/_docs/sql-ref/001-data-types.md
@@ -1,150 +1,473 @@
---
-title: "Data Type Casting"
+title: "Data Types"
parent: "SQL Reference"
---
-The following table lists the type arguments you can use with the cast function:
+Depending on the data format, you might need to cast or convert data types when Drill reads/writes data.
+
+After Drill reads schema-less data into SQL tables, you need to cast data types explicitly to query the data. In some cases, Drill converts schema-less data to typed data implicitly. In this case, you do not need to cast. The file format of the data and the nature of your query determines the requirement for casting or converting.
+
+Differences in casting depend on the data source. The following list describes how Drill treats data types from various data sources:
+
+* HBase
+ No implicit casting to SQL types. Convert data to appropriate types as shown in ["Querying HBase."](/docs/querying-hbase/)
+* Hive
+ Implicitly casts Hive types to SQL types as shown in the Hive [type mapping example](/docs/hive-to-drill-data-type-mapping#type-mapping-example)
+* JSON
+ Implicitly casts JSON data to its [corresponding SQL types](/docs/json-data-model#data-type-mapping) or to VARCHAR if Drillis in all text mode.
+* MapR-DB
+ Implicitly casts MapR-DB data to SQL types when you use [the maprdb format](/docs/mapr-db-format) for reading MapR-DB data. The dfs storage plugin defines the format when you install Drill from the mapr-drill package on a MapR node.
+* Parquet
+ Implicitly casts Parquet data to the SQL types shown in [SQL Data Types to Parquet](/docs/parquet-format/sql-data-types-to-parquet).
+* Text: CSV, TSV, and other text
+ Implicitly casts all textual data to VARCHAR.
+
+## Implicit Casting
+
+
+In general, Drill implicitly casts (promotes) one type to another type based on the order of precedence shown in the following table. Drill also considers the performance cost of implicit casting to one type versus another. Drill usually implicitly casts a type from a lower precedence to a type having higher precedence. For instance, NULL can be promoted to any other type; SMALLINT can be promoted into INT. INT is not promoted to SMALLINT due to possible precision loss.
+
+Under certain circumstances, such as queries involving substr and concat functions, Drill reverses the order of precedence and allows a cast to VARCHAR from a type of higher precedence than VARCHAR, such as BIGINT.
+
+The following table lists data types top to bottom, in descending precedence. Drill implicitly casts to more data types than are currently supported for explicit casting.
<table>
<tr>
- <th>SQL Type</th>
- <th>Drill Description</th>
+ <th>Precedence</th>
+ <th>Data Type</th>
+ <th>Precedence</th>
+ <th>Data Type</th>
</tr>
<tr>
+ <td>1</td>
+ <td>INTERVAL</td>
+ <td>13</td>
<td>BIGINT</td>
- <td>8-byte signed integer</td>
</tr>
<tr>
- <td>BOOLEAN</td>
- <td>True or false</td>
+ <td>2</td>
+ <td>INTERVALYEAR</td>
+ <td>14</td>
+ <td>UINT4</td>
</tr>
<tr>
- <td>DATE</td>
- <td>Years, months, and days in YYYY-MM-DD format</td>
+ <td>3</td>
+ <td>INTERVLADAY</td>
+ <td>15</td>
+ <td>INT</td>
</tr>
<tr>
- <td>NUMERIC, DECIMAL, or DEC(p,s)</td>
- <td>38-digit precision number. Same as numeric(p,s) where precision is p, and scale is s. Example: decimal(6,2) has 4 digits before the decimal point and 2 digits after the decimal point.</td>
+ <td>4</td>
+ <td>TIMESTAMPTZ</td>
+ <td>16</td>
+ <td>UINT2</td>
</tr>
<tr>
- <td>FLOAT</td>
- <td>4-byte single precision floating point number</td>
+ <td>5</td>
+ <td>TIMETZ</td>
+ <td>17</td>
+ <td>SMALLINT</td>
</tr>
<tr>
- <td>DOUBLE, DOUBLE PRECISION</td>
- <td>8-byte double precision floating point number. </td>
+ <td>6</td>
+ <td>TIMESTAMP</td>
+ <td>18</td>
+ <td>UINT1</td>
</tr>
<tr>
- <td>INTEGER, INT</td>
- <td>4-byte signed integer</td>
+ <td>7</td>
+ <td>DATE</td>
+ <td>19</td>
+ <td>VAR16CHAR</td>
</tr>
<tr>
- <td>INTERVAL</td>
- <td>Integer fields representing a period of time in years, months, day,s hours, minutes, seconds and optional milliseconds using ISO 8601 format.</td>
+ <td>8</td>
+ <td>TIME</td>
+ <td>20</td>
+ <td>FIXED16CHAR</td>
</tr>
<tr>
- <td>INTERVALDAY</td>
- <td>A simple version of the interval type expressing a period of time in days, hours, minutes, and seconds only.</td>
+ <td>9</td>
+ <td>FLOAT8</td>
+ <td>21</td>
+ <td>VARCHAR</td>
</tr>
<tr>
- <td>INTERVALYEAR</td>
- <td>A simple version of interval representing a period of time in years and months only.</td>
+ <td>10</td>
+ <td>DECIMAL</td>
+ <td>22</td>
+ <td>FIXEDCHAR</td>
</tr>
<tr>
- <td>SMALLINT</td>
- <td>2-byte signed integer. Supported in Drill 0.9 and later./td>
+ <td>11</td>
+ <td>MONEY</td>
+ <td>23</td>
+ <td>VARBINARY</td>
</tr>
<tr>
- <td>TIME</td>
- <td>Hours, minutes, seconds in the form HH:mm:ss, 24-hour based</td>
+ <td>12</td>
+ <td>UINT8</td>
+ <td>24</td>
+ <td>FIXEDBINARY</td>
</tr>
<tr>
- <td>TIMESTAMP</td>
- <td>JDBC timestamp in year, month, date hour, minute, second, and optional milliseconds format: yyyy-MM-dd HH:mm:ss.SSS</td>
- </tr>
- <tr>
- <td>CHARACTER VARYING, CHARACTER, CHAR, or VARCHAR</td>
- <td>Character string variable length. </td>
+ <td></td>
+ <td></td>
+ <td>25</td>
+ <td>NULL</td>
</tr>
</table>
-DATE, TIME, and TIMESTAMP store values in Coordinated Universal Time (UTC). Currently, Drill does not support casting a TIMESTAMP with time zone, but you can use the TO_TIMESTAMP function (link to example) in a query to use time stamp data having a time zone.
+## Explicit Casting
-## Compatibility with Data Sources
+Drill supports a number of functions to cast and convert compatible data types:
-The following sections describe the data type mapping between Drill and supported data sources.
+* CAST
+ Casts textual 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
+ Converts a TIMESTAMP, INTERVAL, INTEGER, DOUBLE, or DECIMAL to a string.
+* TO_DATE
+ Converts a string to DATE.
+* TO_NUMBER
+ Converts a string to a DECIMAL.
+* TO_TIMESTAMP
+ Converts a string to TIMESTAMP.
-* HBase
- None. You need to convert data as shown in ["Querying HBase."](/docs/querying-hbase/).
-* Hive
- ["Hive-to-Drill Data Type Mapping"](/docs/hive-to-drill-data-type-mapping).
-* JSON
- [SQL-JSON data type mapping](/docs/json-data-model#data-type-mapping) or to varchar in all text mode
-* MapR-DB
- [The maprdb format](/docs/mapr-db-format) for reading (only).
-* Parquet
- [SQL Data Types to Parquet](/docs/parquet-format/sql-data-types-to-parquet).
-* Text: CSV, TSV, and other text
- Implicitly casts all textual data to VARCHAR.
+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.
-Depending on the data format, you might need to [cast or convert](/docs/handling-disparate-data-types) data types to/from these SQL types when Drill reads/writes data.
+### Explicit type Casting: Numeric and Character types
-## Guidelines for Using Float and Double
+<table>
+ <tr>
+ <th></th>
+ <th>To:</th>
+ <th></th>
+ <th></th>
+ <th></th>
+ <th></th>
+ <th></th>
+ <th></th>
+ <th></th>
+ <th></th>
+ <th></th>
+ </tr>
+ <tr>
+ <td>From:</td>
+ <td>SMALLINT</td>
+ <td>INT</td>
+ <td>BIGINT/UINT</td>
+ <td>DECIMAL</td>
+ <td>FLOAT4</td>
+ <td>FLOAT8</td>
+ <td>FIXEDCHAR</td>
+ <td>FIXEDBINARY</td>
+ <td>VARCHAR</td>
+ <td>VARBINARY</td>
+ </tr>
+ <tr>
+ <td>SMALLINT</td>
+ <td></td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ </tr>
+ <tr>
+ <td>INT</td>
+ <td>yes</td>
+ <td>no</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ </tr>
+ <tr>
+ <td>BIGINT/UINT</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ </tr>
+ <tr>
+ <td>DECIMAL</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ </tr>
+ <tr>
+ <td>FLOAT8</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>no</td>
+ <td>yes</td>
+ <td>no</td>
+ <td>yes</td>
+ <td>no</td>
+ </tr>
+ <tr>
+ <td>FLOAT4</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>no</td>
+ <td>no</td>
+ <td>yes</td>
+ <td>no</td>
+ <td>yes</td>
+ <td>no</td>
+ </tr>
+ <tr>
+ <td>FIXEDCHAR</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>no</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ </tr>
+ <tr>
+ <td>FIXEDBINARY</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>no</td>
+ <td>no</td>
+ <td>yes</td>
+ <td>yes</td>
+ </tr>
+ <tr>
+ <td>VARCHAR</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>no</td>
+ <td>yes</td>
+ </tr>
+ <tr>
+ <td>VARBINARY</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>no</td>
+ <td>yes</td>
+ <td>yes</td>
+ <td>no</td>
+ </tr>
+</table>
+
+### Explicit Type Casting: Date/Time types
+
+<table>
+ <tr>
+ <th></th>
+ <th>To:</th>
+ <th></th>
+ <th></th>
+ <th></th>
+ <th></th>
+ <th></th>
+ <th></th>
+ </tr>
+ <tr>
+ <td>From:</td>
+ <td>DATE</td>
+ <td>TIME</td>
+ <td>TIMESTAMP</td>
+ <td>TIMESTAMPTZ</td>
+ <td>INTERVAL</td>
+ <td>INTERVALYEAR</td>
+ <td>INTERVALDAY</td>
+ </tr>
+ <tr>
+ <td>FIXEDCHAR</td>
+ <td>Yes</td>
+ <td>Yes</td>
+ <td>Yes</td>
+ <td>Yes</td>
+ <td>Yes</td>
+ <td>Yes</td>
+ <td>Yes</td>
+ </tr>
+ <tr>
+ <td>FIXEDBINARY</td>
+ <td>No</td>
+ <td>No</td>
+ <td>No</td>
+ <td>Yes</td>
+ <td>No</td>
+ <td>No</td>
+ <td>No</td>
+ </tr>
+ <tr>
+ <td>VARCHAR</td>
+ <td>Yes</td>
+ <td>Yes</td>
+ <td>Yes</td>
+ <td>Yes</td>
+ <td>Yes</td>
+ <td>Yes</td>
+ <td>Yes</td>
+ </tr>
+ <tr>
+ <td>VARBINARY</td>
+ <td>No</td>
+ <td>No</td>
+ <td>Yes</td>
+ <td>Yes</td>
+ <td>No</td>
+ <td>No</td>
+ <td>No</td>
+ </tr>
+ <tr>
+ <td>DATE</td>
+ <td>No</td>
+ <td>No</td>
+ <td>Yes</td>
+ <td>Yes</td>
+ <td>No</td>
+ <td>No</td>
+ <td>No</td>
+ </tr>
+ <tr>
+ <td>TIME</td>
+ <td>No</td>
+ <td>Yes</td>
+ <td>Yes</td>
+ <td>Yes</td>
+ <td>No</td>
+ <td>No</td>
+ <td>No</td>
+ </tr>
+ <tr>
+ <td>TIMESTAMP</td>
+ <td>Yes</td>
+ <td>Yes</td>
+ <td>Yes</td>
+ <td>Yes</td>
+ <td>No</td>
+ <td>No</td>
+ <td>No</td>
+ </tr>
+ <tr>
+ <td>TIMESTAMPTZ</td>
+ <td>Yes</td>
+ <td>Yes</td>
+ <td>Yes</td>
+ <td>No</td>
+ <td>No</td>
+ <td>No</td>
+ <td>No</td>
+ </tr>
+ <tr>
+ <td>INTERVAL</td>
+ <td>Yes</td>
+ <td>No</td>
+ <td>Yes</td>
+ <td>Yes</td>
+ <td>No</td>
+ <td>Yes</td>
+ <td>Yes</td>
+ </tr>
+ <tr>
+ <td>INTERVALYEAR</td>
+ <td>Yes</td>
+ <td>No</td>
+ <td>Yes</td>
+ <td>Yes</td>
+ <td>Yes</td>
+ <td>No</td>
+ <td>Yes</td>
+ </tr>
+ <tr>
+ <td>INTERVALDAY</td>
+ <td>Yes</td>
+ <td>No</td>
+ <td>Yes</td>
+ <td>Yes</td>
+ <td>Yes</td>
+ <td>Yes</td>
+ <td>No</td>
+ </tr>
+</table>
-FLOAT and DOUBLE yield approximate results. These are variable-precision numeric types. Drill does not cast/convert all values precisely to the internal format, but instead stores approximations. Slight differences can occur in the value stored and retrieved. The following guidelines are recommended:
+### Using CAST
-* For conversions involving monetary calculations, for example, that require precise results use the decimal type instead of float or double.
-* For complex calculations or mission-critical applications, especially those involving infinity and underflow situations, carefully consider the limitations of type casting that involves FLOAT or DOUBLE.
-* Equality comparisons between floating-point values can produce unexpected results.
+Embed a CAST function in a query using this syntax:
-Values of FLOAT and DOUBLE that are less than the lowest value in the range (more negative) cause an error. Rounding can occur if the precision of an input number is too high.
+ cast <expression> AS <data type>
-## Complex Data Types
+* expression
+ An entity that has single data value, such as a column name, of the data type you want to cast to a different type
+* data type
+ The target data type, such as INTEGER or DATE
-Drill extends SQL to supoort complex and nested data structures in JSON and Parquet files. Drill reads/writes maps and arrays from/to JSON and Parquet files.
+Example: Inspect INTEGER data and cast the data to the DECIMAL type
-* A map is a set of name/value pairs.
- A value in a map can be a scalar type, such as string or int, or a complex type, such as an array or another map.
-* An array is a repeated list of values.
- A value in an array can be a scalar type, such as string or int, or an array can be a complex type, such as a map or another array.
+ SELECT c_row, c_int FROM mydata WHERE c_row = 9;
-In Drill, you do not cast a map or array to another type.
+ c_row | c_int
+ ------+------------
+ 9 | -2147483648
+ (1 row)
-The following example shows a JSON map having scalar values:
+ SELECT c_row, CAST(c_int AS DECIMAL(28,8)) FROM my_data WHERE c_row = 9;
- phoneNumber:
- {
- areaCode: 622,
- number: 1567845
- }
+ c_row | c_int
+ ------+---------------------
+ 9 | -2147483648.00000000
+ (1 row)
-The following example shows a JSON map having an array as a value:
+If the SELECT statement includes a WHERE clause that compares a column of an unknown data type, cast both the value of the column and the comparison value in the WHERE clause. For example:
- { citiesLived :
- [
- { place : Los Angeles,
- yearsLived : [ 1989, 1993, 1998, 2002]
- }
- ]
- }
+ SELECT c_row, CAST(c_int AS DECIMAL(28,8)) FROM mydata WHERE CAST(c_int AS CECIMAL(28,8)) > -3.0
-The following example shows a JSON array having scalar values:
+Although you can use CAST to handle binary data, CONVERT_TO and CONVERT_FROM are recommended for these conversions.
- yearsLived: [1990, 1993, 1998, 2008]
+### Using CONVERT_TO and CONVERT_FROM
-The following example shows a JSON array having complex type values:
+To query HBase data in Drill, convert every column of an HBase table to/from byte arrays from/to an [SQL data type](/docs/data-types/) that Drill supports when writing/reading data. For examples of how to use these functions, see ["Convert and Cast Functions".](/docs/sql-functions#convert-and-cast-functions)
- children:
- [
- { age : 10,
- gender : Male,
- name : Earl
- },
- { age : 6,
- gender : Male,
- name : Sam,
- { age : 8,
- gender : Male,
- name : Kit
- }
- ]
-
+## Handling Textual Data
+In a textual file, such as CSV, Drill interprets every field as a VARCHAR, as previously mentioned. In addition to using the CAST function, you can also use [to_char](link), [to_date](line), [to_number](link), and [to_timestamp](link). If the SELECT statement includes a WHERE clause that compares a column of an unknown data type, cast both the value of the column and the comparison value in the WHERE clause.
http://git-wip-us.apache.org/repos/asf/drill/blob/923bad37/_docs/sql-ref/006-cmd-summary.md
----------------------------------------------------------------------
diff --git a/_docs/sql-ref/006-cmd-summary.md b/_docs/sql-ref/006-cmd-summary.md
index 0a08af6..da18e68 100644
--- a/_docs/sql-ref/006-cmd-summary.md
+++ b/_docs/sql-ref/006-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="/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="/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="/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 <query>;</cod
e></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="/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="/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="/docs/select-statements" rel="nofollow">SELECT</a></td><td valign="top" colspan="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="/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 SCHEMAS;</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>
-
+<table style='table-layout:fixed;width:100%'>
+ <tr><th >Command</th><th >Description</th><th >Syntax</th></tr><tr><td valign="top" width="15%"><a href="/docs/alter-session-command">ALTER SESSION</a></td><td valign="top" width="60%">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="/docs/planning-and-execution-options">Planning and Execution Options</a>.</td><td valign="top"><pre>ALTER SESSION SET `<option_name>`=<value>;</pre></td></tr><tr><td valign="top" ><a href="/docs/alter-system-command">ALTER SYSTEM</a></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="/docs/planning-and-execution-options">Planning and Execution Options</a>.</td><td valign="top" ><pre>ALTER SYSTEM SET `<option_name>`=<value>;</pre></td></tr><tr><td valign="top" ><p><a href="/docs/crea
te-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.<br />You can specify which writer you want Drill to use when creating a table: parquet, CSV, or JSON (as specified with the <code>store.format</code> option).</td><td valign="top" ><pre class="programlisting">CREATE TABLE new_table_name AS <query>;</pre></td></tr><tr><td - valign="top" ><a href="/docs/create-view-command">CREATE VIEW </a></td><td - valign="top" >Creates a virtual structure for the result set of a stored query.-</td><td -valign="top" ><pre>CREATE [OR REPLACE] VIEW [workspace.]view_name [ (column_name [, ...]) ] AS <query>;</pre></td></tr><tr><td valign="top" ><a href="/docs
/describe-command">DESCRIBE</a></td><td valign="top" >Returns information about columns in a table or view.</td><td valign="top" ><pre>DESCRIBE [workspace.]table_name|view_name</pre></td></tr><tr><td valign="top" ><a href="/docs/drop-view-command">DROP VIEW</a></td><td valign="top" >Removes a view.</td><td valign="top" ><pre>DROP VIEW [workspace.]view_name ;</pre></td></tr><tr><td valign="top" ><a href="/docs/explain-commands">EXPLAIN PLAN FOR</a></td><td valign="top" >Returns the physical plan for a particular query.</td><td valign="top" ><pre>EXPLAIN PLAN FOR <query>;</pre></td></tr><tr><td valign="top" ><a href="/docs/explain-commands">EXPLAIN PLAN WITHOUT IMPLEMENTATION FOR</a></td><td valign="top" >Returns the logical plan for a particular query.</td><td valign="top" ><pre>EXPLAIN PLAN WITHOUT IMPLEMENTATION FOR <query>;</pre></td></tr><tr><td colspan="1" valign="top" ><a href="/docs/select-statements" rel="nofollow">SELECT</a></td><td valign="top" >Retrieves dat
a from tables and files.</td><td valign="top" ><pre>[WITH subquery]<br />SELECT column_list FROM table_name <br />[WHERE clause]<br />[GROUP BY clause]<br />[HAVING clause]<br />[ORDER BY clause];</pre></td></tr><tr><td valign="top" ><a href="/docs/show-databases-and-show-schemas-commands">SHOW DATABASES </a></td><td valign="top" >Returns a list of available schemas. Equivalent to SHOW SCHEMAS.</td><td valign="top" ><pre>SHOW DATABASES;</pre></td></tr><tr><td valign="top" ><a href="/docs/show-files-command" >SHOW FILES</a></td><td valign="top" >Returns a list of files in a file system schema.</td><td valign="top" ><pre>SHOW FILES IN filesystem.`schema_name`;<br />SHOW FILES FROM filesystem.`schema_name`;</pre></td></tr><tr><td valign="top" ><a href="/docs/show-databases-and-show-schemas-commands">SHOW SCHEMAS</a></td><td - valign="top" >Returns a list of available schemas. Equivalent to SHOW DATABASES.</td><td valign="top" ><pre>SHOW SCHEMAS;</pre></td></tr><tr><td valign="top" ><
a href="/docs/show-tables-command">SHOW TABLES</a></td><td valign="top" >Returns a list of tables and views.</td><td valign="top" ><pre>SHOW TABLES;</pre></td></tr><tr><td valign="top" ><a href="/docs/use-command">USE</a></td><td valign="top" >Change to a particular schema. When you opt to use a particular schema, Drill issues queries on that schema only.</td><td valign="top" ><pre>USE schema_name;</pre></td></tr></table>
http://git-wip-us.apache.org/repos/asf/drill/blob/923bad37/_docs/sql-ref/cmd-summary/001-alter-session.md
----------------------------------------------------------------------
diff --git a/_docs/sql-ref/cmd-summary/001-alter-session.md b/_docs/sql-ref/cmd-summary/001-alter-session.md
new file mode 100644
index 0000000..33c5bf8
--- /dev/null
+++ b/_docs/sql-ref/cmd-summary/001-alter-session.md
@@ -0,0 +1,74 @@
+---
+title: "ALTER SESSION Command"
+parent: "SQL Commands Summary"
+---
+The ALTER SESSION command changes a system setting for the duration of a
+session. Session level settings override system level settings.
+
+## Syntax
+
+The ALTER SESSION command supports the following syntax:
+
+ ALTER SESSION SET `<option_name>`=<value>;
+
+## Parameters
+
+*option_name*
+This is the option name as it appears in the systems table.
+
+*value*
+A value of the type listed in the sys.options table: number, string, boolean,
+or float. Use the appropriate value type for each option that you set.
+
+## Usage Notes
+
+Use the ALTER SESSION command to set Drill query planning and execution
+options per session in a cluster. The options that you set using the ALTER
+SESSION command only apply to queries that run during the current Drill
+connection. A session ends when you quit the Drill shell. You can set any of
+the system level options at the session level.
+
+You can run the following query to see a complete list of planning and
+execution options that are currently set at the system or session level:
+
+ 0: jdbc:drill:zk=local> SELECT name, type FROM sys.options WHERE type in ('SYSTEM','SESSION') order by name;
+ +------------+----------------------------------------------+
+ | name | type |
+ +----------------------------------------------+------------+
+ | drill.exec.functions.cast_empty_string_to_null | SYSTEM |
+ | drill.exec.storage.file.partition.column.label | SYSTEM |
+ | exec.errors.verbose | SYSTEM |
+ | exec.java_compiler | SYSTEM |
+ | exec.java_compiler_debug | SYSTEM |
+ …
+ +------------+----------------------------------------------+
+
+**Note:** This is a truncated version of the list.
+
+## Example
+
+This example demonstrates how to use the ALTER SESSION command to set the
+`store.json.all_text_mode` option to “true” for the current Drill session.
+Setting this option to “true” enables text mode so that Drill reads everything
+in JSON as a text object instead of trying to interpret data types. This
+allows complicated JSON to be read using CASE and CAST.
+
+ 0: jdbc:drill:zk=local> alter session set `store.json.all_text_mode`= true;
+ +------------+------------+
+ | ok | summary |
+ +------------+------------+
+ | true | store.json.all_text_mode updated. |
+ +------------+------------+
+ 1 row selected (0.046 seconds)
+
+You can issue a query to see all of the session level settings. Note that the
+option type is case-sensitive.
+
+ 0: jdbc:drill:zk=local> SELECT name, type, bool_val FROM sys.options WHERE type = 'SESSION' order by name;
+ +------------+------------+------------+
+ | name | type | bool_val |
+ +------------+------------+------------+
+ | store.json.all_text_mode | SESSION | true |
+ +------------+------------+------------+
+ 1 row selected (0.176 seconds)
+
http://git-wip-us.apache.org/repos/asf/drill/blob/923bad37/_docs/sql-ref/cmd-summary/001-create-table-as.md
----------------------------------------------------------------------
diff --git a/_docs/sql-ref/cmd-summary/001-create-table-as.md b/_docs/sql-ref/cmd-summary/001-create-table-as.md
deleted file mode 100644
index 573c295..0000000
--- a/_docs/sql-ref/cmd-summary/001-create-table-as.md
+++ /dev/null
@@ -1,134 +0,0 @@
----
-title: "CREATE TABLE AS (CTAS) command"
-parent: "SQL Commands Summary"
----
-You can create tables in Drill by using the CTAS command:
-
- CREATE TABLE new_table_name AS <query>;
-
-where query is any valid Drill query. Each table you create must have a unique
-name. You can include an optional column list for the new table. For example:
-
- create table logtable(transid, prodid) as select transaction_id, product_id from ...
-
-You can store table data in one of three formats:
-
- * csv
- * parquet
- * json
-
-The parquet and json formats can be used to store complex data.
-
-To set the output format for a Drill table, set the `store.format` option with
-the ALTER SYSTEM or ALTER SESSION command. For example:
-
- alter session set `store.format`='json';
-
-Table data is stored in the location specified by the workspace that is in use
-when you run the CTAS statement. By default, a directory is created, using the
-exact table name specified in the CTAS statement. A .json, .csv, or .parquet
-file inside that directory contains the data.
-
-You can only create new tables in workspaces. You cannot create tables in
-other storage plugins such as Hive and HBase.
-
-You must use a writable (mutable) workspace when creating Drill tables. For
-example:
-
- "tmp": {
- "location": "/tmp",
- "writable": true,
- }
-
-## Example
-
-The following query returns one row from a JSON file:
-
- 0: jdbc:drill:zk=local> select id, type, name, ppu
- from dfs.`/Users/brumsby/drill/donuts.json`;
- +------------+------------+------------+------------+
- | id | type | name | ppu |
- +------------+------------+------------+------------+
- | 0001 | donut | Cake | 0.55 |
- +------------+------------+------------+------------+
- 1 row selected (0.248 seconds)
-
-To create and verify the contents of a table that contains this row:
-
- 1. Set the workspace to a writable workspace.
- 2. Set the `store.format` option appropriately.
- 3. Run a CTAS statement that contains the query.
- 4. Go to the directory where the table is stored and check the contents of the file.
- 5. Run a query against the new table.
-
-The following sqlline output captures this sequence of steps.
-
-### Workspace Definition
-
- "tmp": {
- "location": "/tmp",
- "writable": true,
- }
-
-### ALTER SESSION Command
-
- alter session set `store.format`='json';
-
-### USE Command
-
- 0: jdbc:drill:zk=local> use dfs.tmp;
- +------------+------------+
- | ok | summary |
- +------------+------------+
- | true | Default schema changed to 'dfs.tmp' |
- +------------+------------+
- 1 row selected (0.03 seconds)
-
-### CTAS Command
-
- 0: jdbc:drill:zk=local> create table donuts_json as
- select id, type, name, ppu from dfs.`/Users/brumsby/drill/donuts.json`;
- +------------+---------------------------+
- | Fragment | Number of records written |
- +------------+---------------------------+
- | 0_0 | 1 |
- +------------+---------------------------+
- 1 row selected (0.107 seconds)
-
-### File Contents
-
- administorsmbp7:tmp brumsby$ pwd
- /tmp
- administorsmbp7:tmp brumsby$ cd donuts_json
- administorsmbp7:donuts_json brumsby$ more 0_0_0.json
- {
- "id" : "0001",
- "type" : "donut",
- "name" : "Cake",
- "ppu" : 0.55
- }
-
-### Query Against New Table
-
- 0: jdbc:drill:zk=local> select * from donuts_json;
- +------------+------------+------------+------------+
- | id | type | name | ppu |
- +------------+------------+------------+------------+
- | 0001 | donut | Cake | 0.55 |
- +------------+------------+------------+------------+
- 1 row selected (0.053 seconds)
-
-### Use a Different Output Format
-
-You can run the same sequence again with a different storage format set for
-the system or session (csv or parquet). For example, if the format is set to
-csv, and you name the table donuts_csv, the resulting file would look like
-this:
-
- administorsmbp7:tmp brumsby$ cd donuts_csv
- administorsmbp7:donuts_csv brumsby$ ls
- 0_0_0.csv
- administorsmbp7:donuts_csv brumsby$ more 0_0_0.csv
- id,type,name,ppu
- 0001,donut,Cake,0.55
-
http://git-wip-us.apache.org/repos/asf/drill/blob/923bad37/_docs/sql-ref/cmd-summary/002-alter-system.md
----------------------------------------------------------------------
diff --git a/_docs/sql-ref/cmd-summary/002-alter-system.md b/_docs/sql-ref/cmd-summary/002-alter-system.md
new file mode 100644
index 0000000..309873a
--- /dev/null
+++ b/_docs/sql-ref/cmd-summary/002-alter-system.md
@@ -0,0 +1,102 @@
+---
+title: "ALTER SYSTEM Command"
+parent: "SQL Commands Summary"
+---
+The ALTER SYSTEM command permanently changes a system setting. The new setting
+persists across all sessions. Session level settings override system level
+settings.
+
+## Syntax
+
+The ALTER SYSTEM command supports the following syntax:
+
+ ALTER SYSTEM SET `<option_name>`=<value>;
+
+## Parameters
+
+*option_name*
+
+This is the option name as it appears in the systems table.
+
+_value_
+
+A value of the type listed in the sys.options table: number, string, boolean,
+or float. Use the appropriate value type for each option that you set.
+
+## Usage Notes
+
+Use the ALTER SYSTEM command to permanently set Drill query planning and
+execution options per cluster. Options set at the system level affect the
+entire system and persist between restarts.
+
+You can run the following query to see a complete list of planning and
+execution options that you can set at the system level:
+
+ 0: jdbc:drill:zk=local> select name, type, num_val, string_val, bool_val, float_val from sys.options where type like 'SYSTEM' order by name;
+ +------------+------------+------------+------------+------------+------------+
+ | name | type | num_val | string_val | bool_val | float_val |
+ +------------+------------+------------+------------+------------+------------+
+ | drill.exec.functions.cast_empty_string_to_null | SYSTEM | null | null | false | null |
+ | drill.exec.storage.file.partition.column.label | SYSTEM | null | dir | null | null |
+ | exec.errors.verbose | SYSTEM | null | null | false | null |
+ | exec.java_compiler | SYSTEM | null | DEFAULT | null | null |
+ | exec.java_compiler_debug | SYSTEM | null | null | true | null |
+ | exec.java_compiler_janino_maxsize | SYSTEM | 262144 | null | null | null |
+ | exec.queue.timeout_millis | SYSTEM | 400000 | null | null | null |
+ | planner.add_producer_consumer | SYSTEM | null | null | true | null |
+ | planner.affinity_factor | SYSTEM | null | null | null | 1.2 |
+ | planner.broadcast_threshold | SYSTEM | 1000000 | null | null | null |
+ | planner.disable_exchanges | SYSTEM | null | null | false | null |
+ | planner.enable_broadcast_join | SYSTEM | null | null | true | null |
+ | planner.enable_hash_single_key | SYSTEM | null | null | true | null |
+ | planner.enable_hashagg | SYSTEM | null | null | true | null |
+ | planner.enable_hashjoin | SYSTEM | null | null | true | null |
+ | planner.slice_target | SYSTEM | 100000 | null | null | null |
+ | planner.width.max_per_node | SYSTEM | 2 | null | null | null |
+ | planner.width.max_per_query | SYSTEM | 1000 | null | null | null |
+ | store.format | SYSTEM | null | parquet | null | null |
+ | store.json.all_text_mode | SYSTEM | null | null | false | null |
+ | store.mongo.all_text_mode | SYSTEM | null | null | false | null |
+ | store.parquet.block-size | SYSTEM | 536870912 | null | null | null |
+ | store.parquet.use_new_reader | SYSTEM | null | null | false | null |
+ | store.parquet.vector_fill_check_threshold | SYSTEM | 10 | null | null | null |
+ | store.parquet.vector_fill_threshold | SYSTEM | 85 | null | null | null |
+ +------------+------------+------------+------------+------------+------------+
+
+**Note:** This is a truncated version of the list.
+
+## Example
+
+This example demonstrates how to use the ALTER SYSTEM command to set the
+`planner.add_producer_consumer` option to “true.” This option enables a
+secondary reading thread to prefetch data from disk.
+
+ 0: jdbc:drill:zk=local> alter system set `planner.add_producer_consumer` = true;
+ +------------+------------+
+ | ok | summary |
+ +------------+------------+
+ | true | planner.add_producer_consumer updated. |
+ +------------+------------+
+ 1 row selected (0.046 seconds)
+
+You can issue a query to see all of the system level settings set to “true.”
+Note that the option type is case-sensitive.
+
+ 0: jdbc:drill:zk=local> SELECT name, type, bool_val FROM sys.options WHERE type = 'SYSTEM' and bool_val=true;
+ +------------+------------+------------+
+ | name | type | bool_val |
+ +------------+------------+------------+
+ | exec.java_compiler_debug | SYSTEM | true |
+ | planner.enable_mergejoin | SYSTEM | true |
+ | planner.enable_broadcast_join | SYSTEM | true |
+ | planner.enable_hashagg | SYSTEM | true |
+ | planner.add_producer_consumer | SYSTEM | true |
+ | planner.enable_hash_single_key | SYSTEM | true |
+ | planner.enable_multiphase_agg | SYSTEM | true |
+ | planner.enable_streamagg | SYSTEM | true |
+ | planner.enable_hashjoin | SYSTEM | true |
+ +------------+------------+------------+
+ 9 rows selected (0.159 seconds)
+
+
+
http://git-wip-us.apache.org/repos/asf/drill/blob/923bad37/_docs/sql-ref/cmd-summary/002-explain.md
----------------------------------------------------------------------
diff --git a/_docs/sql-ref/cmd-summary/002-explain.md b/_docs/sql-ref/cmd-summary/002-explain.md
deleted file mode 100644
index 12a4d9a..0000000
--- a/_docs/sql-ref/cmd-summary/002-explain.md
+++ /dev/null
@@ -1,166 +0,0 @@
----
-title: "EXPLAIN commands"
-parent: "SQL Commands Summary"
----
-EXPLAIN is a useful tool for examining the steps that a query goes through
-when it is executed. You can use the EXPLAIN output to gain a deeper
-understanding of the parallel processing that Drill queries exploit. You can
-also look at costing information, troubleshoot performance issues, and
-diagnose routine errors that may occur when you run queries.
-
-Drill provides two variations on the EXPLAIN command, one that returns the
-physical plan and one that returns the logical plan. A logical plan takes the
-SQL query (as written by the user and accepted by the parser) and translates
-it into a logical series of operations that correspond to SQL language
-constructs (without defining the specific algorithms that will be implemented
-to run the query). A physical plan translates the logical plan into a specific
-series of steps that will be used when the query runs. For example, a logical
-plan may indicate a join step in general and classify it as inner or outer,
-but the corresponding physical plan will indicate the specific type of join
-operator that will run, such as a merge join or a hash join. The physical plan
-is operational and reveals the specific _access methods_ that will be used for
-the query.
-
-An EXPLAIN command for a query that is run repeatedly under the exact same
-conditions against the same data will return the same plan. However, if you
-change a configuration option, for example, or update the tables or files that
-you are selecting from, you are likely to see plan changes.
-
-## EXPLAIN Syntax
-
-The EXPLAIN command supports the following syntax:
-
- explain plan [ including all attributes ] [ with implementation | without implementation ] for <query> ;
-
-where `query` is any valid SELECT statement supported by Drill.
-
-##### INCLUDING ALL ATTRIBUTES
-
-This option returns costing information. You can use this option for both
-physical and logical plans.
-
-#### WITH IMPLEMENTATION | WITHOUT IMPLEMENTATION
-
-These options return the physical and logical plan information, respectively.
-The default is physical (WITH IMPLEMENTATION).
-
-## EXPLAIN for Physical Plans
-
-The EXPLAIN PLAN FOR <query> command returns the chosen physical execution
-plan for a query statement without running the query. You can use this command
-to see what kind of execution operators Drill implements. For example, you can
-find out what kind of join algorithm is chosen when tables or files are
-joined. You can also use this command to analyze errors and troubleshoot
-queries that do not run. For example, if you run into a casting error, the
-query plan text may help you isolate the problem.
-
-Use the following syntax:
-
- explain plan for <query> ;
- explain plan with implementation for <query> ;
-
-The following set command increases the default text display (number of
-characters). By default, most of the plan output is not displayed.
-
- 0: jdbc:drill:zk=local> !set maxwidth 10000
-
-Do not use a semicolon to terminate set commands.
-
-For example, here is the top portion of the explain output for a
-COUNT(DISTINCT) query on a JSON file:
-
- 0: jdbc:drill:zk=local> !set maxwidth 10000
-
- 0: jdbc:drill:zk=local> explain plan for
- select type t, count(distinct id)
- from dfs.`/Users/brumsby/drill/donuts.json`
- where type='donut' group by type;
-
- +------------+------------+
- | text | json |
- +------------+------------+
- | 00-00 Screen
- 00-01 Project(t=[$0], EXPR$1=[$1])
- 00-02 Project(t=[$0], EXPR$1=[$1])
- 00-03 HashAgg(group=[{0}], EXPR$1=[COUNT($1)])
- 00-04 HashAgg(group=[{0, 1}])
- 00-05 SelectionVectorRemover
- 00-06 Filter(condition=[=(CAST($0):CHAR(5) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", 'donut')])
- 00-07 Project(type=[$1], id=[$2])
- 00-08 ProducerConsumer
- 00-09 Scan(groupscan=[EasyGroupScan [selectionRoot=/Users/brumsby/drill/donuts.json, columns = null]])
- ...
-
-Read the text output from bottom to top to understand the sequence of
-operators that will execute the query. Note that the physical plan starts with
-a scan of the JSON file that is being queried. The selected columns are
-projected and filtered, then the aggregate function is applied.
-
-The EXPLAIN text output is followed by detailed JSON output, which is reusable
-for submitting the query via Drill APIs.
-
- | {
- "head" : {
- "version" : 1,
- "generator" : {
- "type" : "ExplainHandler",
- "info" : ""
- },
- "type" : "APACHE_DRILL_PHYSICAL",
- "options" : [ ],
- "queue" : 0,
- "resultMode" : "EXEC"
- },
- ....
-
-## Costing Information
-
-Add the INCLUDING ALL ATTRIBUTES option to the EXPLAIN command to see cost
-estimates for the query plan. For example:
-
- 0: jdbc:drill:zk=local> !set maxwidth 10000
- 0: jdbc:drill:zk=local> explain plan including all attributes for
- select * from dfs.`/Users/brumsby/drill/donuts.json` where type='donut';
-
- +------------+------------+
- | text | json |
- +------------+------------+
-
- | 00-00 Screen: rowcount = 1.0, cumulative cost = {4.1 rows, 14.1 cpu, 0.0 io, 0.0 network}, id = 3110
- 00-01 Project(*=[$0], type=[$1]): rowcount = 1.0, cumulative cost = {4.0 rows, 14.0 cpu, 0.0 io, 0.0 network}, id = 3109
- 00-02 SelectionVectorRemover: rowcount = 1.0, cumulative cost = {3.0 rows, 6.0 cpu, 0.0 io, 0.0 network}, id = 3108
- 00-03 Filter(condition=[=(CAST($1):CHAR(5) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", 'donut')]): rowcount = 1.0, cumulative cost = {2.0 rows, 5.0 cpu, 0.0 io, 0.0 network}, id = 3107
- 00-04 ProducerConsumer: rowcount = 1.0, cumulative cost = {1.0 rows, 1.0 cpu, 0.0 io, 0.0 network}, id = 3106
- 00-05 Scan(groupscan=[EasyGroupScan [selectionRoot=/Users/brumsby/drill/donuts.json, columns = null]]): rowcount = 1.0, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 3101
-
-## EXPLAIN for Logical Plans
-
-To return the logical plan for a query (again, without actually running the
-query), use the EXPLAIN PLAN WITHOUT IMPLEMENTATION syntax:
-
- explain plan without implementation for <query> ;
-
-For example:
-
- 0: jdbc:drill:zk=local> explain plan without implementation for
- select a.id
- from dfs.`/Users/brumsby/drill/donuts.json` a, dfs.`/Users/brumsby/drill/moredonuts.json` b
- where a.id=b.id;
-
- +------------+------------+
- | text | json |
- +------------+------------+
- | DrillScreenRel
- DrillProjectRel(id=[$1])
- DrillJoinRel(condition=[=($1, $3)], joinType=[inner])
- DrillScanRel(table=[[dfs, /Users/brumsby/drill/donuts.json]], groupscan=[EasyGroupScan [selectionRoot=/Users/brumsby/drill/donuts.json, columns = null]])
- DrillScanRel(table=[[dfs, /Users/brumsby/drill/moredonuts.json]], groupscan=[EasyGroupScan [selectionRoot=/Users/brumsby/drill/moredonuts.json, columns = null]])
- | {
- "head" : {
- "version" : 1,
- "generator" : {
- "type" : "org.apache.drill.exec.planner.logical.DrillImplementor",
- "info" : ""
- },
- ...
-
http://git-wip-us.apache.org/repos/asf/drill/blob/923bad37/_docs/sql-ref/cmd-summary/003-create-table-as.md
----------------------------------------------------------------------
diff --git a/_docs/sql-ref/cmd-summary/003-create-table-as.md b/_docs/sql-ref/cmd-summary/003-create-table-as.md
new file mode 100644
index 0000000..573c295
--- /dev/null
+++ b/_docs/sql-ref/cmd-summary/003-create-table-as.md
@@ -0,0 +1,134 @@
+---
+title: "CREATE TABLE AS (CTAS) command"
+parent: "SQL Commands Summary"
+---
+You can create tables in Drill by using the CTAS command:
+
+ CREATE TABLE new_table_name AS <query>;
+
+where query is any valid Drill query. Each table you create must have a unique
+name. You can include an optional column list for the new table. For example:
+
+ create table logtable(transid, prodid) as select transaction_id, product_id from ...
+
+You can store table data in one of three formats:
+
+ * csv
+ * parquet
+ * json
+
+The parquet and json formats can be used to store complex data.
+
+To set the output format for a Drill table, set the `store.format` option with
+the ALTER SYSTEM or ALTER SESSION command. For example:
+
+ alter session set `store.format`='json';
+
+Table data is stored in the location specified by the workspace that is in use
+when you run the CTAS statement. By default, a directory is created, using the
+exact table name specified in the CTAS statement. A .json, .csv, or .parquet
+file inside that directory contains the data.
+
+You can only create new tables in workspaces. You cannot create tables in
+other storage plugins such as Hive and HBase.
+
+You must use a writable (mutable) workspace when creating Drill tables. For
+example:
+
+ "tmp": {
+ "location": "/tmp",
+ "writable": true,
+ }
+
+## Example
+
+The following query returns one row from a JSON file:
+
+ 0: jdbc:drill:zk=local> select id, type, name, ppu
+ from dfs.`/Users/brumsby/drill/donuts.json`;
+ +------------+------------+------------+------------+
+ | id | type | name | ppu |
+ +------------+------------+------------+------------+
+ | 0001 | donut | Cake | 0.55 |
+ +------------+------------+------------+------------+
+ 1 row selected (0.248 seconds)
+
+To create and verify the contents of a table that contains this row:
+
+ 1. Set the workspace to a writable workspace.
+ 2. Set the `store.format` option appropriately.
+ 3. Run a CTAS statement that contains the query.
+ 4. Go to the directory where the table is stored and check the contents of the file.
+ 5. Run a query against the new table.
+
+The following sqlline output captures this sequence of steps.
+
+### Workspace Definition
+
+ "tmp": {
+ "location": "/tmp",
+ "writable": true,
+ }
+
+### ALTER SESSION Command
+
+ alter session set `store.format`='json';
+
+### USE Command
+
+ 0: jdbc:drill:zk=local> use dfs.tmp;
+ +------------+------------+
+ | ok | summary |
+ +------------+------------+
+ | true | Default schema changed to 'dfs.tmp' |
+ +------------+------------+
+ 1 row selected (0.03 seconds)
+
+### CTAS Command
+
+ 0: jdbc:drill:zk=local> create table donuts_json as
+ select id, type, name, ppu from dfs.`/Users/brumsby/drill/donuts.json`;
+ +------------+---------------------------+
+ | Fragment | Number of records written |
+ +------------+---------------------------+
+ | 0_0 | 1 |
+ +------------+---------------------------+
+ 1 row selected (0.107 seconds)
+
+### File Contents
+
+ administorsmbp7:tmp brumsby$ pwd
+ /tmp
+ administorsmbp7:tmp brumsby$ cd donuts_json
+ administorsmbp7:donuts_json brumsby$ more 0_0_0.json
+ {
+ "id" : "0001",
+ "type" : "donut",
+ "name" : "Cake",
+ "ppu" : 0.55
+ }
+
+### Query Against New Table
+
+ 0: jdbc:drill:zk=local> select * from donuts_json;
+ +------------+------------+------------+------------+
+ | id | type | name | ppu |
+ +------------+------------+------------+------------+
+ | 0001 | donut | Cake | 0.55 |
+ +------------+------------+------------+------------+
+ 1 row selected (0.053 seconds)
+
+### Use a Different Output Format
+
+You can run the same sequence again with a different storage format set for
+the system or session (csv or parquet). For example, if the format is set to
+csv, and you name the table donuts_csv, the resulting file would look like
+this:
+
+ administorsmbp7:tmp brumsby$ cd donuts_csv
+ administorsmbp7:donuts_csv brumsby$ ls
+ 0_0_0.csv
+ administorsmbp7:donuts_csv brumsby$ more 0_0_0.csv
+ id,type,name,ppu
+ 0001,donut,Cake,0.55
+
http://git-wip-us.apache.org/repos/asf/drill/blob/923bad37/_docs/sql-ref/cmd-summary/003-select.md
----------------------------------------------------------------------
diff --git a/_docs/sql-ref/cmd-summary/003-select.md b/_docs/sql-ref/cmd-summary/003-select.md
deleted file mode 100644
index 00004a4..0000000
--- a/_docs/sql-ref/cmd-summary/003-select.md
+++ /dev/null
@@ -1,85 +0,0 @@
----
-title: "SELECT Statements"
-parent: "SQL Commands Summary"
----
-Drill supports the following ANSI standard clauses in the SELECT statement:
-
- * WITH clause
- * SELECT list
- * FROM clause
- * WHERE clause
- * GROUP BY clause
- * HAVING clause
- * ORDER BY clause (with an optional LIMIT clause)
-
-You can use the same SELECT syntax in the following commands:
-
- * CREATE TABLE AS (CTAS)
- * CREATE VIEW
-
-INSERT INTO SELECT is not yet supported.
-
-## Column Aliases
-
-You can use named column aliases in the SELECT list to provide meaningful
-names for regular columns and computed columns, such as the results of
-aggregate functions. See the section on running queries for examples.
-
-You cannot reference column aliases in the following clauses:
-
- * WHERE
- * GROUP BY
- * HAVING
-
-Because Drill works with schema-less data sources, you cannot use positional
-aliases (1, 2, etc.) to refer to SELECT list columns, except in the ORDER BY
-clause.
-
-## UNION ALL Set Operator
-
-Drill supports the UNION ALL set operator to combine two result sets. The
-distinct UNION operator is not yet supported.
-
-The EXCEPT, EXCEPT ALL, INTERSECT, and INTERSECT ALL operators are not yet
-supported.
-
-## Joins
-
-Drill supports ANSI standard joins in the FROM and WHERE clauses:
-
- * Inner joins
- * Left, full, and right outer joins
-
-The following types of join syntax are supported:
-
-Join type| Syntax
----|---
-Join condition in WHERE clause|FROM table1, table 2 WHERE table1.col1=table2.col1
-USING join in FROM clause|FROM table1 JOIN table2 USING(col1, ...)
-ON join in FROM clause|FROM table1 JOIN table2 ON table1.col1=table2.col1
-NATURAL JOIN in FROM clause|FROM table 1 NATURAL JOIN table 2
-
-Cross-joins are not yet supported. You must specify a join condition when more
-than one table is listed in the FROM clause.
-
-Non-equijoins are supported if the join also contains an equality condition on
-the same two tables as part of a conjunction:
-
- table1.col1 = table2.col1 AND table1.c2 < table2.c2
-
-This restriction applies to both inner and outer joins.
-
-## Subqueries
-
-You can use the following subquery operators in Drill queries. These operators
-all return Boolean results.
-
- * ALL
- * ANY
- * EXISTS
- * IN
- * SOME
-
-In general, correlated subqueries are supported. EXISTS and NOT EXISTS
-subqueries that do not contain a correlation join are not yet supported.
-
http://git-wip-us.apache.org/repos/asf/drill/blob/923bad37/_docs/sql-ref/cmd-summary/004-create-view.md
----------------------------------------------------------------------
diff --git a/_docs/sql-ref/cmd-summary/004-create-view.md b/_docs/sql-ref/cmd-summary/004-create-view.md
new file mode 100644
index 0000000..ec33d1a
--- /dev/null
+++ b/_docs/sql-ref/cmd-summary/004-create-view.md
@@ -0,0 +1,197 @@
+---
+title: "CREATE VIEW command"
+parent: "SQL Commands Summary"
+---
+The CREATE VIEW command creates a virtual structure for the result set of a
+stored query. A view can combine data from multiple underlying data sources
+and provide the illusion that all of the data is from one source. You can use
+views to protect sensitive data, for data aggregation, and to hide data
+complexity from users. You can create Drill views from files in your local and
+distributed file systems, Hive, HBase, and MapR-DB tables, as well as from
+existing views or any other available storage plugin data sources.
+
+## Syntax
+
+The CREATE VIEW command supports the following syntax:
+
+ CREATE [OR REPLACE] VIEW [workspace.]view_name [ (column_name [, ...]) ] AS <query>;
+
+Use CREATE VIEW to create a new view. Use CREATE OR REPLACE VIEW to replace an
+existing view with the same name. When you replace a view, the query must
+generate the same set of columns with the same column names and data types.
+
+**Note:** Follow Drill’s rules for identifiers when you name the view. See coming soon...
+
+## Parameters
+
+_workspace_
+The location where you want the view to exist. By default, the view is created
+in the current workspace. See
+[Workspaces](/docs/Workspaces).
+
+_view_name_
+The name that you give the view. The view must have a unique name. It cannot
+have the same name as any other view or table in the workspace.
+
+_column_name_
+Optional list of column names in the view. If you do not supply column names,
+they are derived from the query.
+
+_query_
+A SELECT statement that defines the columns and rows in the view.
+
+## Usage Notes
+
+### Storage
+
+Drill stores views in the location specified by the workspace that you use
+when you run the CREATE VIEW command. If the workspace is not defined, Drill
+creates the view in the current workspace. You must use a writable workspace
+when you create a view. Currently, Drill only supports views created in the
+file system or distributed file system.
+
+The following example shows a writable workspace as defined within the storage
+plugin in the `/tmp` directory of the file system:
+
+ "tmp": {
+ "location": "/tmp",
+ "writable": true,
+ }
+
+Drill stores the view definition in JSON format with the name that you specify
+when you run the CREATE VIEW command, suffixed `by .view.drill`. For example,
+if you create a view named `myview`, Drill stores the view in the designated
+workspace as `myview.view.drill`.
+
+Data Sources
+
+Drill considers data sources to have either a strong schema or a weak schema.
+
+##### Strong Schema
+
+With the exception of text file data sources, Drill verifies that data sources
+associated with a strong schema contain data types compatible with those used
+in the query. Drill also verifies that the columns referenced in the query
+exist in the underlying data sources. If the columns do not exist, CREATE VIEW
+fails.
+
+#### Weak Schema
+
+Drill does not verify that data sources associated with a weak schema contain
+data types compatible with those used in the query. Drill does not verify if
+columns referenced in a query on a Parquet data source exist, therefore CREATE
+VIEW always succeeds. In the case of JSON files, Drill does not verify if the
+files contain the maps specified in the view.
+
+The following table lists the current categories of schema and the data
+sources associated with each:
+
+<table>
+ <tr>
+ <th></th>
+ <th>Strong Schema</th>
+ <th>Weak Schema</th>
+ </tr>
+ <tr>
+ <td valign="top">Data Sources</td>
+ <td>views<br>hive tables<br>hbase column families<br>text</td>
+ <td>json<br>mongodb<br>hbase column qualifiers<br>parquet</td>
+ </tr>
+</table>
+
+## Related Commands
+
+After you create a view using the CREATE VIEW command, you can issue the
+following commands against the view:
+
+ * SELECT
+ * DESCRIBE
+ * DROP
+
+**Note:** You cannot update, insert into, or delete from a view.
+
+## Example
+
+This example shows you some steps that you can follow when you want to create
+a view in Drill using the CREATE VIEW command. A workspace named “donuts” was
+created for the steps in this example.
+
+Complete the following steps to create a view in Drill:
+
+ 1. Decide which workspace you will use to create the view, and verify that the writable option is set to “true.” You can use an existing workspace, or you can create a new workspace. See [Workspaces](https://cwiki.apache.org/confluence/display/DRILL/Workspaces) for more information.
+
+ "workspaces": {
+ "donuts": {
+ "location": "/home/donuts",
+ "writable": true,
+ "defaultInputFormat": null
+ }
+ },
+
+ 2. Run SHOW DATABASES to verify that Drill recognizes the workspace.
+
+ 0: jdbc:drill:zk=local> show databases;
+ +-------------+
+ | SCHEMA_NAME |
+ +-------------+
+ | dfs.default |
+ | dfs.root |
+ | dfs.donuts |
+ | dfs.tmp |
+ | cp.default |
+ | sys |
+ | INFORMATION_SCHEMA |
+ +-------------+
+
+ 3. Use the writable workspace.
+
+ 0: jdbc:drill:zk=local> use dfs.donuts;
+ +------------+------------+
+ | ok | summary |
+ +------------+------------+
+ | true | Default schema changed to 'dfs.donuts' |
+ +------------+------------+
+
+ 4. Test run the query that you plan to use with the CREATE VIEW command.
+
+ 0: jdbc:drill:zk=local> select id, type, name, ppu from `donuts.json`;
+ +------------+------------+------------+------------+
+ | id | type | name | ppu |
+ +------------+------------+------------+------------+
+ | 0001 | donut | Cake | 0.55 |
+ +------------+------------+------------+------------+
+
+ 5. Run the CREATE VIEW command with the query.
+
+ 0: jdbc:drill:zk=local> create view mydonuts as select id, type, name, ppu from `donuts.json`;
+ +------------+------------+
+ | ok | summary |
+ +------------+------------+
+ | true | View 'mydonuts' created successfully in 'dfs.donuts' schema |
+ +------------+------------+
+
+ 6. Create a new view in another workspace from the current workspace.
+
+ 0: jdbc:drill:zk=local> create view dfs.tmp.yourdonuts as select id, type, name from `donuts.json`;
+ +------------+------------+
+ | ok | summary |
+ +------------+------------+
+ | true | View 'yourdonuts' created successfully in 'dfs.tmp' schema |
+ +------------+------------+
+
+ 7. Query the view created in both workspaces.
+
+ 0: jdbc:drill:zk=local> select * from mydonuts;
+ +------------+------------+------------+------------+
+ | id | type | name | ppu |
+ +------------+------------+------------+------------+
+ | 0001 | donut | Cake | 0.55 |
+ +------------+------------+------------+------------+
+
+
+ 0: jdbc:drill:zk=local> select * from dfs.tmp.yourdonuts;
+ +------------+------------+------------+
+ | id | type | name |
+ +------------+------------+------------+
+ | 0001 | donut | Cake |
+ +------------+------------+------------+
http://git-wip-us.apache.org/repos/asf/drill/blob/923bad37/_docs/sql-ref/cmd-summary/004-show-files.md
----------------------------------------------------------------------
diff --git a/_docs/sql-ref/cmd-summary/004-show-files.md b/_docs/sql-ref/cmd-summary/004-show-files.md
deleted file mode 100644
index 1fcf395..0000000
--- a/_docs/sql-ref/cmd-summary/004-show-files.md
+++ /dev/null
@@ -1,65 +0,0 @@
----
-title: "SHOW FILES Command"
-parent: "SQL Commands Summary"
----
-The SHOW FILES command provides a quick report of the file systems that are
-visible to Drill for query purposes. This command is unique to Apache Drill.
-
-## Syntax
-
-The SHOW FILES command supports the following syntax.
-
- SHOW FILES [ FROM filesystem.directory_name | IN filesystem.directory_name ];
-
-The FROM or IN clause is required if you do not specify a default file system
-first. You can do this with the USE command. FROM and IN are synonyms.
-
-The directory name is optional. (If the directory name is a Drill reserved
-word, you must use back ticks around the name.)
-
-The command returns standard Linux `stat` information for each file or
-directory, such as permissions, owner, and group values. This information is
-not specific to Drill.
-
-## Examples
-
-The following example returns information about directories and files in the
-local (`dfs`) file system.
-
- 0: jdbc:drill:> use dfs;
-
- +------------+------------+
- | ok | summary |
- +------------+------------+
- | true | Default schema changed to 'dfs' |
- +------------+------------+
- 1 row selected (0.318 seconds)
-
- 0: jdbc:drill:> show files;
- +------------+-------------+------------+------------+------------+------------+-------------+------------+------------------+
- | name | isDirectory | isFile | length | owner | group | permissions | accessTime | modificationTime |
- +------------+-------------+------------+------------+------------+------------+-------------+------------+------------------+
- | user | true | false | 1 | mapr | mapr | rwxr-xr-x | 2014-07-30 21:37:06.0 | 2014-07-31 22:15:53.193 |
- | backup.tgz | false | true | 36272 | root | root | rw-r--r-- | 2014-07-31 22:09:13.0 | 2014-07-31 22:09:13.211 |
- | JSON | true | false | 1 | root | root | rwxr-xr-x | 2014-07-31 15:22:42.0 | 2014-08-04 15:43:07.083 |
- | scripts | true | false | 3 | root | root | rwxr-xr-x | 2014-07-31 22:10:51.0 | 2014-08-04 18:23:09.236 |
- | temp | true | false | 2 | root | root | rwxr-xr-x | 2014-08-01 20:07:37.0 | 2014-08-01 20:09:42.595 |
- | hbase | true | false | 10 | mapr | mapr | rwxr-xr-x | 2014-07-30 21:36:08.0 | 2014-08-04 18:31:13.778 |
- | tables | true | false | 0 | root | root | rwxrwxrwx | 2014-07-31 22:14:35.0 | 2014-08-04 15:42:43.415 |
- | CSV | true | false | 4 | root | root | rwxrwxrwx | 2014-07-31 17:34:53.0 | 2014-08-04
- ...
-
-The following example shows the files in a specific directory in the `dfs`
-file system:
-
- 0: jdbc:drill:> show files in dfs.CSV;
-
- +------------+-------------+------------+------------+------------+------------+-------------+------------+------------------+
- | name | isDirectory | isFile | length | owner | group | permissions | accessTime | modificationTime |
- +------------+-------------+------------+------------+------------+------------+-------------+------------+------------------+
- | customers.csv | false | true | 62011 | root | root | rw-r--r-- | 2014-08-04 18:30:39.0 | 2014-08-04 18:30:39.314 |
- | products.csv.small | false | true | 34972 | root | root | rw-r--r-- | 2014-07-31 23:58:42.0 | 2014-07-31 23:59:16.849 |
- | products.csv | false | true | 34972 | root | root | rw-r--r-- | 2014-08-01 06:39:34.0 | 2014-08-04 15:58:09.325 |
- | products.csv.bad | false | true | 62307 | root | root | rw-r--r-- | 2014-08-04 15:58:02.0 | 2014-08-04 15:58:02.612 |
- +------------+-------------+------------+------------+------------+------------+-------------+------------+------------------+
- 4 rows selected (0.165 seconds)
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/drill/blob/923bad37/_docs/sql-ref/cmd-summary/005-describe.md
----------------------------------------------------------------------
diff --git a/_docs/sql-ref/cmd-summary/005-describe.md b/_docs/sql-ref/cmd-summary/005-describe.md
new file mode 100644
index 0000000..b9eb4f6
--- /dev/null
+++ b/_docs/sql-ref/cmd-summary/005-describe.md
@@ -0,0 +1,99 @@
+---
+title: "DESCRIBE Command"
+parent: "SQL Commands Summary"
+---
+The DESCRIBE command returns information about columns in a table or view.
+
+## Syntax
+
+The DESCRIBE command supports the following syntax:
+
+ DESCRIBE [workspace.]table_name|view_name
+
+## Usage Notes
+
+You can issue the DESCRIBE command against views created in a workspace and
+tables created in Hive, HBase, and MapR-DB. You can issue the DESCRIBE command
+on a table or view from any schema. For example, if you are working in the
+`dfs.myworkspace` schema, you can issue the DESCRIBE command on a view or
+table in another schema. Currently, DESCRIBE does not support tables created
+in a file system.
+
+Drill only supports SQL data types. Verify that all data types in an external
+data source, such as Hive or HBase, map to supported data types in Drill. See
+Drill Data Type Mapping for more information.
+
+## Example
+
+The following example demonstrates the steps that you can follow when you want
+to use the DESCRIBE command to see column information for a view and for Hive
+and HBase tables.
+
+Complete the following steps to use the DESCRIBE command:
+
+ 1. Issue the USE command to switch to a particular schema.
+
+ 0: jdbc:drill:zk=drilldemo:5181> use hive;
+ +------------+------------+
+ | ok | summary |
+ +------------+------------+
+ | true | Default schema changed to 'hive' |
+ +------------+------------+
+ 1 row selected (0.025 seconds)
+
+ 2. Issue the SHOW TABLES command to see the existing tables in the schema.
+
+ 0: jdbc:drill:zk=drilldemo:5181> show tables;
+ +--------------+------------+
+ | TABLE_SCHEMA | TABLE_NAME |
+ +--------------+------------+
+ | hive.default | orders |
+ | hive.default | products |
+ +--------------+------------+
+ 2 rows selected (0.438 seconds)
+
+ 3. Issue the DESCRIBE command on a table.
+
+ 0: jdbc:drill:zk=drilldemo:5181> describe orders;
+ +-------------+------------+-------------+
+ | COLUMN_NAME | DATA_TYPE | IS_NULLABLE |
+ +-------------+------------+-------------+
+ | order_id | BIGINT | YES |
+ | month | VARCHAR | YES |
+ | purchdate | TIMESTAMP | YES |
+ | cust_id | BIGINT | YES |
+ | state | VARCHAR | YES |
+ | prod_id | BIGINT | YES |
+ | order_total | INTEGER | YES |
+ +-------------+------------+-------------+
+ 7 rows selected (0.64 seconds)
+
+ 4. Issue the DESCRIBE command on a table in another schema from the current schema.
+
+ 0: jdbc:drill:zk=drilldemo:5181> describe hbase.customers;
+ +-------------+------------+-------------+
+ | COLUMN_NAME | DATA_TYPE | IS_NULLABLE |
+ +-------------+------------+-------------+
+ | row_key | ANY | NO |
+ | address | (VARCHAR(1), ANY) MAP | NO |
+ | loyalty | (VARCHAR(1), ANY) MAP | NO |
+ | personal | (VARCHAR(1), ANY) MAP | NO |
+ +-------------+------------+-------------+
+ 4 rows selected (0.671 seconds)
+
+ 5. Issue the DESCRIBE command on a view in another schema from the current schema.
+
+ 0: jdbc:drill:zk=drilldemo:5181> describe dfs.views.customers_vw;
+ +-------------+------------+-------------+
+ | COLUMN_NAME | DATA_TYPE | IS_NULLABLE |
+ +-------------+------------+-------------+
+ | cust_id | BIGINT | NO |
+ | name | VARCHAR | NO |
+ | address | VARCHAR | NO |
+ | gender | VARCHAR | NO |
+ | age | VARCHAR | NO |
+ | agg_rev | VARCHAR | NO |
+ | membership | VARCHAR | NO |
+ +-------------+------------+-------------+
+ 7 rows selected (0.403 seconds)
+
http://git-wip-us.apache.org/repos/asf/drill/blob/923bad37/_docs/sql-ref/cmd-summary/007-explain.md
----------------------------------------------------------------------
diff --git a/_docs/sql-ref/cmd-summary/007-explain.md b/_docs/sql-ref/cmd-summary/007-explain.md
new file mode 100644
index 0000000..8ce6432
--- /dev/null
+++ b/_docs/sql-ref/cmd-summary/007-explain.md
@@ -0,0 +1,156 @@
+---
+title: "EXPLAIN commands"
+parent: "SQL Commands Summary"
+---
+EXPLAIN is a useful tool for examining the steps that a query goes through
+when it is executed. You can use the EXPLAIN output to gain a deeper
+understanding of the parallel processing that Drill queries exploit. You can
+also look at costing information, troubleshoot performance issues, and
+diagnose routine errors that may occur when you run queries.
+
+Drill provides two variations on the EXPLAIN command, one that returns the
+physical plan and one that returns the logical plan. A logical plan takes the
+SQL query (as written by the user and accepted by the parser) and translates
+it into a logical series of operations that correspond to SQL language
+constructs (without defining the specific algorithms that will be implemented
+to run the query). A physical plan translates the logical plan into a specific
+series of steps that will be used when the query runs. For example, a logical
+plan may indicate a join step in general and classify it as inner or outer,
+but the corresponding physical plan will indicate the specific type of join
+operator that will run, such as a merge join or a hash join. The physical plan
+is operational and reveals the specific _access methods_ that will be used for
+the query.
+
+An EXPLAIN command for a query that is run repeatedly under the exact same
+conditions against the same data will return the same plan. However, if you
+change a configuration option, for example, or update the tables or files that
+you are selecting from, you are likely to see plan changes.
+
+## EXPLAIN Syntax
+
+The EXPLAIN command supports the following syntax:
+
+ explain plan [ including all attributes ] [ with implementation | without implementation ] for <query> ;
+
+where `query` is any valid SELECT statement supported by Drill.
+
+##### INCLUDING ALL ATTRIBUTES
+
+This option returns costing information. You can use this option for both
+physical and logical plans.
+
+#### WITH IMPLEMENTATION | WITHOUT IMPLEMENTATION
+
+These options return the physical and logical plan information, respectively.
+The default is physical (WITH IMPLEMENTATION).
+
+## EXPLAIN for Physical Plans
+
+The EXPLAIN PLAN FOR <query> command returns the chosen physical execution
+plan for a query statement without running the query. You can use this command
+to see what kind of execution operators Drill implements. For example, you can
+find out what kind of join algorithm is chosen when tables or files are
+joined. You can also use this command to analyze errors and troubleshoot
+queries that do not run. For example, if you run into a casting error, the
+query plan text may help you isolate the problem.
+
+Use the following syntax:
+
+ explain plan for <query> ;
+
+The following set command increases the default text display (number of
+characters). By default, most of the plan output is not displayed.
+
+ 0: jdbc:drill:zk=local> !set maxwidth 10000
+
+Do not use a semicolon to terminate set commands.
+
+For example, here is the top portion of the explain output for a
+COUNT(DISTINCT) query on a JSON file:
+
+ 0: jdbc:drill:zk=local> !set maxwidth 10000
+ 0: jdbc:drill:zk=local> explain plan for select type t, count(distinct id) from dfs.`/home/donuts/donuts.json` where type='donut' group by type;
+ +------------+------------+
+ | text | json |
+ +------------+------------+
+ | 00-00 Screen
+ 00-01 Project(t=[$0], EXPR$1=[$1])
+ 00-02 Project(t=[$0], EXPR$1=[$1])
+ 00-03 HashAgg(group=[{0}], EXPR$1=[COUNT($1)])
+ 00-04 HashAgg(group=[{0, 1}])
+ 00-05 SelectionVectorRemover
+ 00-06 Filter(condition=[=($0, 'donut')])
+ 00-07 Scan(groupscan=[EasyGroupScan [selectionRoot=/home/donuts/donuts.json, numFiles=1, columns=[`type`, `id`], files=[file:/home/donuts/donuts.json]]])...
+ ...
+
+Read the text output from bottom to top to understand the sequence of
+operators that will execute the query. Note that the physical plan starts with
+a scan of the JSON file that is being queried. The selected columns are
+projected and filtered, then the aggregate function is applied.
+
+The EXPLAIN text output is followed by detailed JSON output, which is reusable
+for submitting the query via Drill APIs.
+
+ | {
+ "head" : {
+ "version" : 1,
+ "generator" : {
+ "type" : "ExplainHandler",
+ "info" : ""
+ },
+ "type" : "APACHE_DRILL_PHYSICAL",
+ "options" : [ ],
+ "queue" : 0,
+ "resultMode" : "EXEC"
+ },
+ ....
+
+## Costing Information
+
+Add the INCLUDING ALL ATTRIBUTES option to the EXPLAIN command to see cost
+estimates for the query plan. For example:
+
+ 0: jdbc:drill:zk=local> !set maxwidth 10000
+ 0: jdbc:drill:zk=local> explain plan including all attributes for select * from dfs.`/home/donuts/donuts.json` where type='donut';
+ +------------+------------+
+ | text | json |
+ +------------+------------+
+ | 00-00 Screen: rowcount = 1.0, cumulative cost = {5.1 rows, 21.1 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 889
+ 00-01 Project(*=[$0]): rowcount = 1.0, cumulative cost = {5.0 rows, 21.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 888
+ 00-02 Project(T1¦¦*=[$0]): rowcount = 1.0, cumulative cost = {4.0 rows, 17.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 887
+ 00-03 SelectionVectorRemover: rowcount = 1.0, cumulative cost = {3.0 rows, 13.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 886
+ 00-04 Filter(condition=[=($1, 'donut')]): rowcount = 1.0, cumulative cost = {2.0 rows, 12.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 885
+ 00-05 Project(T1¦¦*=[$0], type=[$1]): rowcount = 1.0, cumulative cost = {1.0 rows, 8.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 884
+ 00-06 Scan(groupscan=[EasyGroupScan [selectionRoot=/home/donuts/donuts.json, numFiles=1, columns=[`*`], files=[file:/home/donuts/donuts.json]]]): rowcount = 1.0, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 883
+
+## EXPLAIN for Logical Plans
+
+To return the logical plan for a query (again, without actually running the
+query), use the EXPLAIN PLAN WITHOUT IMPLEMENTATION syntax:
+
+ explain plan without implementation for <query> ;
+
+For example:
+
+ 0: jdbc:drill:zk=local> explain plan without implementation for select type t, count(distinct id) from dfs.`/home/donuts/donuts.json` where type='donut' group by type;
+ +------------+------------+
+ | text | json |
+ +------------+------------+
+ | DrillScreenRel
+ DrillProjectRel(t=[$0], EXPR$1=[$1])
+ DrillAggregateRel(group=[{0}], EXPR$1=[COUNT($1)])
+ DrillAggregateRel(group=[{0, 1}])
+ DrillFilterRel(condition=[=($0, 'donut')])
+ DrillScanRel(table=[[dfs, /home/donuts/donuts.json]], groupscan=[EasyGroupScan [selectionRoot=/home/donuts/donuts.json, numFiles=1, columns=[`type`, `id`], files=[file:/home/donuts/donuts.json]]]) | {
+ | {
+ "head" : {
+ "version" : 1,
+ "generator" : {
+ "type" : "org.apache.drill.exec.planner.logical.DrillImplementor",
+ "info" : ""
+ },
+ "type" : "APACHE_DRILL_LOGICAL",
+ "options" : null,
+ "queue" : 0,
+ "resultMode" : "LOGICAL"
+ },...
http://git-wip-us.apache.org/repos/asf/drill/blob/923bad37/_docs/sql-ref/cmd-summary/008-select.md
----------------------------------------------------------------------
diff --git a/_docs/sql-ref/cmd-summary/008-select.md b/_docs/sql-ref/cmd-summary/008-select.md
new file mode 100644
index 0000000..cdc1d92
--- /dev/null
+++ b/_docs/sql-ref/cmd-summary/008-select.md
@@ -0,0 +1,178 @@
+---
+title: "SELECT Statements"
+parent: "SQL Commands Summary"
+---
+Drill supports the following ANSI standard clauses in the SELECT statement:
+
+ * WITH clause
+ * SELECT list
+ * FROM clause
+ * WHERE clause
+ * GROUP BY clause
+ * HAVING clause
+ * ORDER BY clause (with an optional LIMIT clause)
+
+You can use the same SELECT syntax in the following commands:
+
+ * CREATE TABLE AS (CTAS)
+ * CREATE VIEW
+
+INSERT INTO SELECT is not yet supported.
+
+## Column Aliases
+
+You can use named column aliases in the SELECT list to provide meaningful
+names for regular columns and computed columns, such as the results of
+aggregate functions. See the section on running queries for examples.
+
+You cannot reference column aliases in the following clauses:
+
+ * WHERE
+ * GROUP BY
+ * HAVING
+
+Because Drill works with schema-less data sources, you cannot use positional
+aliases (1, 2, etc.) to refer to SELECT list columns, except in the ORDER BY
+clause.
+
+## UNION ALL Set Operator
+
+Drill supports the UNION ALL set operator to combine two result sets. The
+distinct UNION operator is not yet supported.
+
+The EXCEPT, EXCEPT ALL, INTERSECT, and INTERSECT ALL operators are not yet
+supported.
+
+## Joins
+
+Drill supports ANSI standard joins in the FROM and WHERE clauses:
+
+ * Inner joins
+ * Left, full, and right outer joins
+
+The following types of join syntax are supported:
+
+Join type| Syntax
+---|---
+Join condition in WHERE clause|FROM table1, table 2 WHERE table1.col1=table2.col1
+USING join in FROM clause|FROM table1 JOIN table2 USING(col1, ...)
+ON join in FROM clause|FROM table1 JOIN table2 ON table1.col1=table2.col1
+NATURAL JOIN in FROM clause|FROM table 1 NATURAL JOIN table 2
+
+Cross-joins are not yet supported. You must specify a join condition when more
+than one table is listed in the FROM clause.
+
+Non-equijoins are supported if the join also contains an equality condition on
+the same two tables as part of a conjunction:
+
+ table1.col1 = table2.col1 AND table1.c2 < table2.c2
+
+This restriction applies to both inner and outer joins.
+
+## Subqueries
+
+You can use the following subquery operators in Drill queries. These operators
+all return Boolean results.
+
+ * ALL
+ * ANY
+ * EXISTS
+ * IN
+ * SOME
+
+In general, correlated subqueries are supported. EXISTS and NOT EXISTS
+subqueries that do not contain a correlation join are not yet supported.
+
+## WITH Clause
+
+The WITH clause is an optional clause used to contain one or more common table
+expressions (CTE) where each CTE defines a temporary table that exists for the
+duration of the query. Each subquery in the WITH clause specifies a table
+name, an optional list of column names, and a SELECT statement.
+
+## Syntax
+
+The WITH clause supports the following syntax:
+
+ [ WITH with_subquery [, ...] ]
+ where with_subquery is:
+ with_subquery_table_name [ ( column_name [, ...] ) ] AS ( query )
+
+## Parameters
+
+_with_subquery_table_name_
+
+A unique name for a temporary table that defines the results of a WITH clause
+subquery. You cannot use duplicate names within a single WITH clause. You must
+give each subquery a table name that can be referenced in the FROM clause.
+
+_column_name_
+
+An optional list of output column names for the WITH clause subquery,
+separated by commas. The number of column names specified must be equal to or
+less than the number of columns defined by the subquery.
+
+_query_
+
+Any SELECT query that Drill supports. See
+[SELECT](/docs/SELECT+Statements).
+
+## Usage Notes
+
+Use the WITH clause to efficiently define temporary tables that Drill can
+access throughout the execution of a single query. The WITH clause is
+typically a simpler alternative to using subqueries in the main body of the
+SELECT statement. In some cases, Drill can evaluate a WITH subquery once and
+reuse the results for query optimization.
+
+You can use a WITH clause in the following SQL statements:
+
+ * SELECT (including subqueries within SELECT statements)
+
+ * CREATE TABLE AS
+
+ * CREATE VIEW
+
+ * EXPLAIN
+
+You can reference the temporary tables in the FROM clause of the query. If the
+FROM clause does not reference any tables defined by the WITH clause, Drill
+ignores the WITH clause and executes the query as normal.
+
+Drill can only reference a table defined by a WITH clause subquery in the
+scope of the SELECT query that the WITH clause begins. For example, you can
+reference such a table in the FROM clause of a subquery in the SELECT list,
+WHERE clause, or HAVING clause. You cannot use a WITH clause in a subquery and
+reference its table in the FROM clause of the main query or another subquery.
+
+You cannot specify another WITH clause inside a WITH clause subquery.
+
+For example, the following query includes a forward reference to table t2 in
+the definition of table t1:
+
+## Example
+
+The following example shows the WITH clause used to create a WITH query named
+`emp_data` that selects all of the rows from the `employee.json` file. The
+main query selects the `full_name, position_title, salary`, and `hire_date`
+rows from the `emp_data` temporary table (created from the WITH subquery) and
+orders the results by the hire date. The `emp_data` table only exists for the
+duration of the query.
+
+**Note:** The `employee.json` file is included with the Drill installation. It is located in the `cp.default` workspace which is configured by default.
+
+ 0: jdbc:drill:zk=local> with emp_data as (select * from cp.`employee.json`) select full_name, position_title, salary, hire_date from emp_data order by hire_date limit 10;
+ +------------------+-------------------------+------------+-----------------------+
+ | full_name | position_title | salary | hire_date |
+ +------------------+-------------------------+------------+-----------------------+
+ | Bunny McCown | Store Assistant Manager | 8000.0 | 1993-05-01 00:00:00.0 |
+ | Danielle Johnson | Store Assistant Manager | 8000.0 | 1993-05-01 00:00:00.0 |
+ | Dick Brummer | Store Assistant Manager | 7900.0 | 1993-05-01 00:00:00.0 |
+ | Gregory Whiting | Store Assistant Manager | 10000.0 | 1993-05-01 00:00:00.0 |
+ | Juanita Sharp | HQ Human Resources | 6700.0 | 1994-01-01 00:00:00.0 |
+ | Sheri Nowmer | President | 80000.0 | 1994-12-01 00:00:00.0 |
+ | Rebecca Kanagaki | VP Human Resources | 15000.0 | 1994-12-01 00:00:00.0 |
+ | Shauna Wyro | Store Manager | 15000.0 | 1994-12-01 00:00:00.0 |
+ | Roberta Damstra | VP Information Systems | 25000.0 | 1994-12-01 00:00:00.0 |
+ | Pedro Castillo | VP Country Manager | 35000.0 | 1994-12-01 00:00:00.0 |
+ +------------+----------------+--------------+------------------------------------+
\ No newline at end of file