You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@drill.apache.org by ts...@apache.org on 2015/05/19 01:36:45 UTC
[22/31] drill git commit: add BB's clause pages,
renamed sql command pages
http://git-wip-us.apache.org/repos/asf/drill/blob/9700ff63/_docs/sql-reference/sql-commands/085-select-offset.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/085-select-offset.md b/_docs/sql-reference/sql-commands/085-select-offset.md
new file mode 100644
index 0000000..9e97051
--- /dev/null
+++ b/_docs/sql-reference/sql-commands/085-select-offset.md
@@ -0,0 +1,29 @@
+---
+title: "SELECT OFFSET"
+parent: "SQL Commands"
+---
+The OFFSET clause provides a way to skip a specified number of first rows in a result set before starting to return any rows.
+
+## Syntax
+The OFFSET clause supports the following syntax:
+
+ [ OFFSET start { ROW | ROWS } ]
+
+Specifying ALL returns all records, which is equivalent to omitting the LIMIT clause from the SELECT statement.
+
+## Parameters
+*rows*
+
+Specifies the number of rows Drill should skip before returning the result set.
+
+## Usage Notes
+ * The OFFSET number must be a positive integer and cannot be larger than the number of rows in the underlying result set or no rows are returned.
+ * You can use the OFFSET clause in conjunction with the LIMIT and ORDER BY clauses.
+ * When used with the LIMIT option, OFFSET rows are skipped before starting to count the LIMIT rows that are returned. If the LIMIT option is not used, the number of rows in the result set is reduced by the number of rows that are skipped.
+ * The rows skipped by an OFFSET clause still have to be scanned, so it might be inefficient to use a large OFFSET value.
+
+## Examples
+The following example query returns the result set from row 101 and on, skipping the first 100 rows of the table:
+
+ SELECT * FROM dfs.logs OFFSET 100 ROWS;
+
http://git-wip-us.apache.org/repos/asf/drill/blob/9700ff63/_docs/sql-reference/sql-commands/086-select-order-by.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/086-select-order-by.md b/_docs/sql-reference/sql-commands/086-select-order-by.md
new file mode 100644
index 0000000..794d029
--- /dev/null
+++ b/_docs/sql-reference/sql-commands/086-select-order-by.md
@@ -0,0 +1,71 @@
+---
+title: "SELECT ORDER BY"
+parent: "SQL Commands"
+---
+The ORDER BY clause sorts the result set of a query.
+
+
+
+## Syntax
+The ORDER BY clause supports the following syntax:
+
+ [ ORDER BY expression
+ [ ASC | DESC ]
+ [ NULLS FIRST | NULLS LAST ]
+
+
+
+## Parameters
+*expression*
+
+Defines the sort order of the query result set, typically by specifying one or more columns in the select list.
+
+You can also specify:
+
+ * Columns that are not in the select list
+ * Expressions formed from one or more columns that exist in the tables referenced by the query
+ * Ordinal numbers that represent the position of select list entries (or the position of columns in the table if no select list exists)
+ * Aliases that define select list entries
+
+When the ORDER BY clause contains multiple expressions, the result set is sorted according to the first expression, then the second expression is applied to rows that have matching values from the first expression, and so on.
+
+ASC
+Specifies that the results should be returned in ascending order. If the order is not specified, ASC is the default.
+
+DESC
+Specifies that the results should be returned in descending order.
+
+NULLS FIRST
+Specifies that NULL values should be returned before non-NULL values.
+
+NULLS LAST
+Specifies that NULL values should be returned after non-NULL values.
+
+## Usage Notes
+ * NULL values are considered "higher" than all other values. With default ascending sort order, NULL values sort at the end.
+ * When a query does not contain an ORDER BY clause, the system returns result sets with no predictable ordering of the rows. The same query executed twice might return the result set in a different order.
+ * In any parallel system, when ORDER BY does not produce a unique ordering, the order of the rows is non-deterministic. That is, if the ORDER BY expression produces duplicate values, the return order of those rows may vary from other systems or from one run the system to the next.
+
+## Examples
+The following example query returns sales totals for each month in descending order, listing the highest sales month to the lowest sales month:
+
+ 0: jdbc:drill:> select `month`, sum(order_total)
+ from orders group by `month` order by 2 desc;
+ +------------+------------+
+ | month | EXPR$1 |
+ +------------+------------+
+ | June | 950481 |
+ | May | 947796 |
+ | March | 836809 |
+ | April | 807291 |
+ | July | 757395 |
+ | October | 676236 |
+ | August | 572269 |
+ | February | 532901 |
+ | September | 373100 |
+ | January | 346536 |
+ +------------+------------+
+
+
+
+
http://git-wip-us.apache.org/repos/asf/drill/blob/9700ff63/_docs/sql-reference/sql-commands/087-select-union.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/087-select-union.md b/_docs/sql-reference/sql-commands/087-select-union.md
new file mode 100644
index 0000000..6d5af65
--- /dev/null
+++ b/_docs/sql-reference/sql-commands/087-select-union.md
@@ -0,0 +1,42 @@
+---
+title: "SELECT UNION"
+parent: "SQL Commands"
+---
+The UNION set operator returns all rows in the result sets of two separate query expressions. For example, if two employee tables exist, you can use the UNION set operator to merge the two tables and build a complete list of all the employees. Drill supports UNION ALL only. Drill does not support DISTINCT.
+
+
+## Syntax
+The UNION set operator supports the following syntax:
+
+ query
+ { UNION [ ALL ] }
+ query
+
+
+## Parameters
+*query*
+
+Any SELECT query that Drill supports. See SELECT.
+
+## Usage Notes
+ * The two SELECT query expressions that represent the direct operands of the UNION must produce the same number of columns. Corresponding columns must contain compatible data types. See Supported Data Types.
+ * Multiple UNION operators in the same SELECT statement are evaluated left to right, unless otherwise indicated by parentheses.
+ * You cannot use * in UNION ALL for schemaless data.
+
+## Examples
+The following example uses the UNION ALL set operator to combine click activity data before and after a marketing campaign. The data in the example exists in the `dfs.clicks workspace`.
+
+ 0: jdbc:drill:> select t.trans_id transaction, t.user_info.cust_id customer from `clicks/clicks.campaign.json` t
+ union all
+ select u.trans_id, u.user_info.cust_id from `clicks/clicks.json` u limit 5;
+ +-------------+------------+
+ | transaction | customer |
+ +-------------+------------+
+ | 35232 | 18520 |
+ | 31995 | 17182 |
+ | 35760 | 18228 |
+ | 37090 | 17015 |
+ | 37838 | 18737 |
+ +-------------+------------+
+
+This UNION ALL query returns rows that exist in two files (and includes any duplicate rows from those files): `clicks.campaign.json` and `clicks.json`
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/drill/blob/9700ff63/_docs/sql-reference/sql-commands/088-select-where.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/088-select-where.md b/_docs/sql-reference/sql-commands/088-select-where.md
new file mode 100644
index 0000000..5d600fa
--- /dev/null
+++ b/_docs/sql-reference/sql-commands/088-select-where.md
@@ -0,0 +1,59 @@
+---
+title: "SELECT WHERE"
+parent: "SQL Commands"
+---
+The WHERE clause selects rows based on a boolean expression. Only rows for which the expression evaluates to TRUE are returned in the result.
+
+## Syntax
+The WHERE clause supports the following syntax:
+
+ [ WHERE boolean_expression ]
+
+## Expression
+A boolean expression can include one or more of the following operators:
+
+ * AND
+ * OR
+ * NOT
+ * IS NULL
+ * IS NOT NULL
+ * LIKE
+ * BETWEEN
+ * IN
+ * EXISTS
+ * Comparison operators
+ * Quantified comparison operators
+
+
+## Examples
+The following query compares order totals where the states are California and New York:
+
+ 0: jdbc:drill:> select o1.cust_id, sum(o1.order_total) as ny_sales,
+ (select sum(o2.order_total) from hive.orders o2
+ where o1.cust_id=o2.cust_id and state='ca') as ca_sales
+ from hive.orders o1 where o1.state='ny' group by o1.cust_id
+ order by cust_id limit 20;
+ +------------+------------+------------+
+ | cust_id | ny_sales | ca_sales |
+ +------------+------------+------------+
+ | 1001 | 72 | 47 |
+ | 1002 | 108 | 198 |
+ | 1003 | 83 | null |
+ | 1004 | 86 | 210 |
+ | 1005 | 168 | 153 |
+ | 1006 | 29 | 326 |
+ | 1008 | 105 | 168 |
+ | 1009 | 443 | 127 |
+ | 1010 | 75 | 18 |
+ | 1012 | 110 | null |
+ | 1013 | 19 | null |
+ | 1014 | 106 | 162 |
+ | 1015 | 220 | 153 |
+ | 1016 | 85 | 159 |
+ | 1017 | 82 | 56 |
+ | 1019 | 37 | 196 |
+ | 1020 | 193 | 165 |
+ | 1022 | 124 | null |
+ | 1023 | 166 | 149 |
+ | 1024 | 233 | null |
+ +------------+------------+------------+
http://git-wip-us.apache.org/repos/asf/drill/blob/9700ff63/_docs/sql-reference/sql-commands/089-select-with.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/089-select-with.md b/_docs/sql-reference/sql-commands/089-select-with.md
new file mode 100644
index 0000000..4253354
--- /dev/null
+++ b/_docs/sql-reference/sql-commands/089-select-with.md
@@ -0,0 +1,95 @@
+---
+title: "SELECT WITH"
+parent: "SQL Commands"
+---
+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]({{ site.baseurl }}/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
http://git-wip-us.apache.org/repos/asf/drill/blob/9700ff63/_docs/sql-reference/sql-commands/090-show-databases-and-show-schemas.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/090-show-databases-and-show-schemas.md b/_docs/sql-reference/sql-commands/090-show-databases-and-show-schemas.md
index 0f227ae..a7d07d2 100644
--- a/_docs/sql-reference/sql-commands/090-show-databases-and-show-schemas.md
+++ b/_docs/sql-reference/sql-commands/090-show-databases-and-show-schemas.md
@@ -1,5 +1,5 @@
---
-title: "SHOW DATABASES and SHOW SCHEMAS Command"
+title: "SHOW DATABASES and SHOW SCHEMAS"
parent: "SQL Commands"
---
The SHOW DATABASES and SHOW SCHEMAS commands generate a list of available Drill schemas that you can query.
http://git-wip-us.apache.org/repos/asf/drill/blob/9700ff63/_docs/sql-reference/sql-commands/100-show-files.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/100-show-files.md b/_docs/sql-reference/sql-commands/100-show-files.md
index 9651add..f115c21 100644
--- a/_docs/sql-reference/sql-commands/100-show-files.md
+++ b/_docs/sql-reference/sql-commands/100-show-files.md
@@ -1,5 +1,5 @@
---
-title: "SHOW FILES Command"
+title: "SHOW FILES"
parent: "SQL Commands"
---
The SHOW FILES command provides a quick report of the file systems that are
http://git-wip-us.apache.org/repos/asf/drill/blob/9700ff63/_docs/sql-reference/sql-commands/110-show-tables-command.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/110-show-tables-command.md b/_docs/sql-reference/sql-commands/110-show-tables-command.md
deleted file mode 100644
index 560bde4..0000000
--- a/_docs/sql-reference/sql-commands/110-show-tables-command.md
+++ /dev/null
@@ -1,136 +0,0 @@
----
-title: "SHOW TABLES Command"
-parent: "SQL Commands"
----
-The SHOW TABLES command returns a list of views created within a schema. It
-also returns the tables that exist in Hive, HBase, and MapR-DB when you have
-these data sources configured as storage plugin instances. See[ Storage Plugin
-Registration]({{ site.baseurl }}/docs/storage-plugin-registration).
-
-## Syntax
-
-The SHOW TABLES command supports the following syntax:
-
- SHOW TABLES;
-
-## Usage Notes
-
-First issue the USE command to identify the schema for which you want to view
-tables or views. For example, the following USE statement tells Drill that you
-only want information from the `dfs.myviews` schema:
-
- USE dfs.myviews;
-
-In this example, “`myviews`” is a workspace created withing an instance of the
-`dfs` storage plugin.
-
-When you use a particular schema and then issue the SHOW TABLES command, Drill
-returns the tables and views within that schema.
-
-#### Limitations
-
- * You can create and query tables within the file system, however Drill does not return these tables when you issue the SHOW TABLES command. You can issue the [SHOW FILES ]({{ site.baseurl }}/docs/show-files-command)command to see a list of all files, tables, and views, including those created in Drill.
-
- * You cannot create Hive, HBase, or MapR-DB tables in Drill.
-
-## Examples
-
-The following examples demonstrate the steps that you can follow when you want
-to issue the SHOW TABLES command on the file system, Hive, and HBase.
-
-Complete the following steps to see views that exist in a file system and
-tables that exist in Hive and HBase data sources:
-
- 1. Issue the SHOW SCHEMAS command to see a list of available schemas.
-
- 0: jdbc:drill:zk=drilldemo:5181> show schemas;
- +-------------+
- | SCHEMA_NAME |
- +-------------+
- | hive.default |
- | dfs.reviews |
- | dfs.flatten |
- | dfs.default |
- | dfs.root |
- | dfs.logs |
- | dfs.myviews |
- | dfs.clicks |
- | dfs.tmp |
- | sys |
- | hbase |
- | INFORMATION_SCHEMA |
- | s3.twitter |
- | s3.reviews |
- | s3.default |
- +-------------+
- 15 rows selected (0.072 seconds)
-
- 2. Issue the USE command to switch to a particular schema. When you use a particular schema, Drill searches or queries within that schema only.
-
- 0: jdbc:drill:zk=drilldemo:5181> use dfs.myviews;
- +------------+------------+
- | ok | summary |
- +------------+------------+
- | true | Default schema changed to 'dfs.myviews' |
- +------------+------------+
- 1 row selected (0.025 seconds)
-
- 3. Issue the SHOW TABLES command to see the views or tables that exist within workspace.
-
- 0: jdbc:drill:zk=drilldemo:5181> show tables;
- +--------------+------------+
- | TABLE_SCHEMA | TABLE_NAME |
- +--------------+------------+
- | dfs.myviews | logs_vw |
- | dfs.myviews | customers_vw |
- | dfs.myviews | s3_review_vw |
- | dfs.myviews | clicks_vw |
- | dfs.myviews | nestedclickview |
- | dfs.myviews | s3_user_vw |
- | dfs.myviews | s3_bus_vw |
- +--------------+------------+
- 7 rows selected (0.499 seconds)
- 0: jdbc:drill:zk=drilldemo:5181>
-
- 4. Switch to the Hive schema and issue the SHOW TABLES command to see the Hive tables that exist.
-
- 0: jdbc:drill:zk=drilldemo:5181> use hive;
- +------------+------------+
- | ok | summary |
- +------------+------------+
- | true | Default schema changed to 'hive' |
- +------------+------------+
- 1 row selected (0.043 seconds)
-
- 0: jdbc:drill:zk=drilldemo:5181> show tables;
- +--------------+------------+
- | TABLE_SCHEMA | TABLE_NAME |
- +--------------+------------+
- | hive.default | orders |
- | hive.default | products |
- +--------------+------------+
- 2 rows selected (0.552 seconds)
-
- 5. Switch to the HBase schema and issue the SHOW TABLES command to see the HBase tables that exist within the schema.
-
- 0: jdbc:drill:zk=drilldemo:5181> use hbase;
- +------------+------------+
- | ok | summary |
- +------------+------------+
- | true | Default schema changed to 'hbase' |
- +------------+------------+
- 1 row selected (0.043 seconds)
-
-
- 0: jdbc:drill:zk=drilldemo:5181> show tables;
- +--------------+------------+
- | TABLE_SCHEMA | TABLE_NAME |
- +--------------+------------+
- | hbase | customers |
- +--------------+------------+
- 1 row selected (0.412 seconds)
-
-
-
-
-
http://git-wip-us.apache.org/repos/asf/drill/blob/9700ff63/_docs/sql-reference/sql-commands/110-show-tables.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/110-show-tables.md b/_docs/sql-reference/sql-commands/110-show-tables.md
new file mode 100644
index 0000000..40621cf
--- /dev/null
+++ b/_docs/sql-reference/sql-commands/110-show-tables.md
@@ -0,0 +1,136 @@
+---
+title: "SHOW TABLES"
+parent: "SQL Commands"
+---
+The SHOW TABLES command returns a list of views created within a schema. It
+also returns the tables that exist in Hive, HBase, and MapR-DB when you have
+these data sources configured as storage plugin instances. See[ Storage Plugin
+Registration]({{ site.baseurl }}/docs/storage-plugin-registration).
+
+## Syntax
+
+The SHOW TABLES command supports the following syntax:
+
+ SHOW TABLES;
+
+## Usage Notes
+
+First issue the USE command to identify the schema for which you want to view
+tables or views. For example, the following USE statement tells Drill that you
+only want information from the `dfs.myviews` schema:
+
+ USE dfs.myviews;
+
+In this example, “`myviews`” is a workspace created withing an instance of the
+`dfs` storage plugin.
+
+When you use a particular schema and then issue the SHOW TABLES command, Drill
+returns the tables and views within that schema.
+
+#### Limitations
+
+ * You can create and query tables within the file system, however Drill does not return these tables when you issue the SHOW TABLES command. You can issue the [SHOW FILES ]({{ site.baseurl }}/docs/show-files-command)command to see a list of all files, tables, and views, including those created in Drill.
+
+ * You cannot create Hive, HBase, or MapR-DB tables in Drill.
+
+## Examples
+
+The following examples demonstrate the steps that you can follow when you want
+to issue the SHOW TABLES command on the file system, Hive, and HBase.
+
+Complete the following steps to see views that exist in a file system and
+tables that exist in Hive and HBase data sources:
+
+ 1. Issue the SHOW SCHEMAS command to see a list of available schemas.
+
+ 0: jdbc:drill:zk=drilldemo:5181> show schemas;
+ +-------------+
+ | SCHEMA_NAME |
+ +-------------+
+ | hive.default |
+ | dfs.reviews |
+ | dfs.flatten |
+ | dfs.default |
+ | dfs.root |
+ | dfs.logs |
+ | dfs.myviews |
+ | dfs.clicks |
+ | dfs.tmp |
+ | sys |
+ | hbase |
+ | INFORMATION_SCHEMA |
+ | s3.twitter |
+ | s3.reviews |
+ | s3.default |
+ +-------------+
+ 15 rows selected (0.072 seconds)
+
+ 2. Issue the USE command to switch to a particular schema. When you use a particular schema, Drill searches or queries within that schema only.
+
+ 0: jdbc:drill:zk=drilldemo:5181> use dfs.myviews;
+ +------------+------------+
+ | ok | summary |
+ +------------+------------+
+ | true | Default schema changed to 'dfs.myviews' |
+ +------------+------------+
+ 1 row selected (0.025 seconds)
+
+ 3. Issue the SHOW TABLES command to see the views or tables that exist within workspace.
+
+ 0: jdbc:drill:zk=drilldemo:5181> show tables;
+ +--------------+------------+
+ | TABLE_SCHEMA | TABLE_NAME |
+ +--------------+------------+
+ | dfs.myviews | logs_vw |
+ | dfs.myviews | customers_vw |
+ | dfs.myviews | s3_review_vw |
+ | dfs.myviews | clicks_vw |
+ | dfs.myviews | nestedclickview |
+ | dfs.myviews | s3_user_vw |
+ | dfs.myviews | s3_bus_vw |
+ +--------------+------------+
+ 7 rows selected (0.499 seconds)
+ 0: jdbc:drill:zk=drilldemo:5181>
+
+ 4. Switch to the Hive schema and issue the SHOW TABLES command to see the Hive tables that exist.
+
+ 0: jdbc:drill:zk=drilldemo:5181> use hive;
+ +------------+------------+
+ | ok | summary |
+ +------------+------------+
+ | true | Default schema changed to 'hive' |
+ +------------+------------+
+ 1 row selected (0.043 seconds)
+
+ 0: jdbc:drill:zk=drilldemo:5181> show tables;
+ +--------------+------------+
+ | TABLE_SCHEMA | TABLE_NAME |
+ +--------------+------------+
+ | hive.default | orders |
+ | hive.default | products |
+ +--------------+------------+
+ 2 rows selected (0.552 seconds)
+
+ 5. Switch to the HBase schema and issue the SHOW TABLES command to see the HBase tables that exist within the schema.
+
+ 0: jdbc:drill:zk=drilldemo:5181> use hbase;
+ +------------+------------+
+ | ok | summary |
+ +------------+------------+
+ | true | Default schema changed to 'hbase' |
+ +------------+------------+
+ 1 row selected (0.043 seconds)
+
+
+ 0: jdbc:drill:zk=drilldemo:5181> show tables;
+ +--------------+------------+
+ | TABLE_SCHEMA | TABLE_NAME |
+ +--------------+------------+
+ | hbase | customers |
+ +--------------+------------+
+ 1 row selected (0.412 seconds)
+
+
+
+
+
http://git-wip-us.apache.org/repos/asf/drill/blob/9700ff63/_docs/sql-reference/sql-commands/120-use-command.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/120-use-command.md b/_docs/sql-reference/sql-commands/120-use-command.md
deleted file mode 100644
index b1bc24a..0000000
--- a/_docs/sql-reference/sql-commands/120-use-command.md
+++ /dev/null
@@ -1,170 +0,0 @@
----
-title: "USE Command"
-parent: "SQL Commands"
----
-The USE command changes the schema context to the specified schema. When you
-issue the USE command to switch to a particular schema, Drill queries that
-schema only.
-
-## Syntax
-
-The USE command supports the following syntax:
-
- USE schema_name;
-
-## Parameters
-
-_schema_name_
-A unique name for a Drill schema. A schema in Drill is a configured storage
-plugin, such as hive, or a storage plugin and workspace. For example,
-`dfs.donuts` where `dfs` is an instance of the file system configured as a
-storage plugin, and `donuts` is a workspace configured to point to a directory
-within the file system. You can configure and use multiple storage plugins and
-workspaces in Drill. See [Storage Plugin Registration]({{ site.baseurl }}/docs/storage-plugin-registration) and
-[Workspaces]({{ site.baseurl }}/docs/Workspaces).
-
-## Usage Notes
-
-Issue the USE command to change to a particular schema. When you use a schema,
-you do not have to include the full path to a file or table in your query.
-
-For example, to query a file named `donuts.json` in the
-`/users/max/drill/json/` directory, you must include the full file path in
-your query if you do not use a defined workspace
-
- SELECT * FROM dfs.`/users/max/drill/json/donuts.json` WHERE type='frosted';
-
-If you create a schema that points to the `~/json` directory where the file is
-located and then use the schema, you can issue the query without explicitly
-stating the file path:
-
- USE dfs.json;
- SELECT * FROM `donuts.json`WHERE type='frosted';
-
-If you do not use a schema before querying a table, you must use absolute
-notation, such as `[schema.]table[.column]`, to query the table. If you switch
-to the schema where the table exists, you can just specify the table name in
-the query. For example, to query a table named "`products`" in the `hive`
-schema, tell Drill to use the hive schema and then issue your query with the
-table name only:
-
- USE hive;
- SELECT * FROM products limit 5;
-
-Before you issue the USE command, you may want to run SHOW DATABASES or SHOW
-SCHEMAS to see a list of the configured storage plugins and workspaces.
-
-## Example
-
-This example demonstrates how to use a file system and a hive schema to query
-a file and table in Drill.
-
-Issue the SHOW DATABASES or SHOW SCHEMAS command to see a list of the
-available schemas that you can use. Both commands return the same results.
-
- 0: jdbc:drill:zk=drilldemo:5181> show schemas;
- +-------------+
- | SCHEMA_NAME |
- +-------------+
- | hive.default |
- | dfs.reviews |
- | dfs.flatten |
- | dfs.default |
- | dfs.root |
- | dfs.logs |
- | dfs.myviews |
- | dfs.clicks |
- | dfs.tmp |
- | sys |
- | hbase |
- | INFORMATION_SCHEMA |
- | s3.twitter |
- | s3.reviews |
- | s3.default |
- +-------------+
- 15 rows selected (0.059 seconds)
-
-
-Issue the USE command with the schema that you want Drill to query.
-**Note:** If you use any of the Drill default schemas, such as `cp.default` or `dfs.default`, do not include .`default`. For example, if you want Drill to issue queries on files in its classpath, you can issue the following command:
-
- 0: jdbc:drill:zk=local> use cp;
- +------------+------------+
- | ok | summary |
- +------------+------------+
- | true | Default schema changed to 'cp' |
- +------------+------------+
- 1 row selected (0.04 seconds)
-
-Issue the USE command with a file system schema.
-
- 0: jdbc:drill:zk=drilldemo:5181> use dfs.logs;
- +------------+------------+
- | ok | summary |
- +------------+------------+
- | true | Default schema changed to 'dfs.logs' |
- +------------+------------+
- 1 row selected (0.054 seconds)
-
-You can issue the SHOW FILES command to view the files and directories within
-the schema.
-
- 0: jdbc:drill:zk=drilldemo:5181> show files;
- +------------+-------------+------------+------------+------------+------------+-------------+------------+------------------+
- | name | isDirectory | isFile | length | owner | group | permissions | accessTime | modificationTime |
- +------------+-------------+------------+------------+------------+------------+-------------+------------+------------------+
- | csv | true | false | 1 | mapr | mapr | rwxrwxr-x | 2015-02-09 06:49:17.0 | 2015-02-09 06:50:11.172 |
- | logs | true | false | 3 | mapr | mapr | rwxrwxr-x | 2014-12-16 18:58:26.0 | 2014-12-16 18:58:27.223 |
- +------------+-------------+------------+------------+------------+------------+-------------+------------+------------------+
- 2 rows selected (0.156 seconds)
-
-Query a file or directory in the file system schema.
-
- 0: jdbc:drill:zk=drilldemo:5181> select * from logs limit 5;
- +------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
- | dir0 | dir1 | trans_id | date | time | cust_id | device | state | camp_id | keywords | prod_id | purch_flag |
- +------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
- | 2014 | 8 | 24181 | 08/02/2014 | 09:23:52 | 0 | IOS5 | il | 2 | wait | 128 | false |
- | 2014 | 8 | 24195 | 08/02/2014 | 07:58:19 | 243 | IOS5 | mo | 6 | hmm | 107 | false |
- | 2014 | 8 | 24204 | 08/01/2014 | 12:10:27 | 12048 | IOS6 | il | 1 | marge | 324 | false |
- | 2014 | 8 | 24222 | 08/02/2014 | 16:28:37 | 2488 | IOS6 | pa | 2 | to | 391 | false |
- | 2014 | 8 | 24227 | 08/02/2014 | 07:14:00 | 154687 | IOS5 | wa | 2 | on | 376 | false |
- +------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
-
-Issue the USE command to switch to the hive schema.
-
- 0: jdbc:drill:zk=drilldemo:5181> use hive;
- +------------+------------+
- | ok | summary |
- +------------+------------+
- | true | Default schema changed to 'hive' |
- +------------+------------+
- 1 row selected (0.093 seconds)
-
-Issue the SHOW TABLES command to see the tables that exist within the schema.
-
- 0: jdbc:drill:zk=drilldemo:5181> show tables;
- +--------------+------------+
- | TABLE_SCHEMA | TABLE_NAME |
- +--------------+------------+
- | hive.default | orders |
- | hive.default | products |
- +--------------+------------+
- 2 rows selected (0.421 seconds)
-
-Query a table within the schema.
-
- 0: jdbc:drill:zk=drilldemo:5181> select * from products limit 5;
- +------------+------------+------------+------------+
- | prod_id | name | category | price |
- +------------+------------+------------+------------+
- | 0 | Sony notebook | laptop | 959 |
- | 1 | #10-4 1/8 x 9 1/2 Premium Diagonal Seam Envelopes | Envelopes | 16 |
- | 2 | #10- 4 1/8 x 9 1/2 Recycled Envelopes | Envelopes | 9 |
- | 3 | #10- 4 1/8 x 9 1/2 Security-Tint Envelopes | Envelopes | 8 |
- | 4 | #10 Self-Seal White Envelopes | Envelopes | 11 |
- +------------+------------+------------+------------+
- 5 rows selected (0.211 seconds)
-
-
-
http://git-wip-us.apache.org/repos/asf/drill/blob/9700ff63/_docs/sql-reference/sql-commands/120-use.md
----------------------------------------------------------------------
diff --git a/_docs/sql-reference/sql-commands/120-use.md b/_docs/sql-reference/sql-commands/120-use.md
new file mode 100644
index 0000000..aaaf74f
--- /dev/null
+++ b/_docs/sql-reference/sql-commands/120-use.md
@@ -0,0 +1,170 @@
+---
+title: "USE"
+parent: "SQL Commands"
+---
+The USE command changes the schema context to the specified schema. When you
+issue the USE command to switch to a particular schema, Drill queries that
+schema only.
+
+## Syntax
+
+The USE command supports the following syntax:
+
+ USE schema_name;
+
+## Parameters
+
+_schema_name_
+A unique name for a Drill schema. A schema in Drill is a configured storage
+plugin, such as hive, or a storage plugin and workspace. For example,
+`dfs.donuts` where `dfs` is an instance of the file system configured as a
+storage plugin, and `donuts` is a workspace configured to point to a directory
+within the file system. You can configure and use multiple storage plugins and
+workspaces in Drill. See [Storage Plugin Registration]({{ site.baseurl }}/docs/storage-plugin-registration) and
+[Workspaces]({{ site.baseurl }}/docs/Workspaces).
+
+## Usage Notes
+
+Issue the USE command to change to a particular schema. When you use a schema,
+you do not have to include the full path to a file or table in your query.
+
+For example, to query a file named `donuts.json` in the
+`/users/max/drill/json/` directory, you must include the full file path in
+your query if you do not use a defined workspace
+
+ SELECT * FROM dfs.`/users/max/drill/json/donuts.json` WHERE type='frosted';
+
+If you create a schema that points to the `~/json` directory where the file is
+located and then use the schema, you can issue the query without explicitly
+stating the file path:
+
+ USE dfs.json;
+ SELECT * FROM `donuts.json`WHERE type='frosted';
+
+If you do not use a schema before querying a table, you must use absolute
+notation, such as `[schema.]table[.column]`, to query the table. If you switch
+to the schema where the table exists, you can just specify the table name in
+the query. For example, to query a table named "`products`" in the `hive`
+schema, tell Drill to use the hive schema and then issue your query with the
+table name only:
+
+ USE hive;
+ SELECT * FROM products limit 5;
+
+Before you issue the USE command, you may want to run SHOW DATABASES or SHOW
+SCHEMAS to see a list of the configured storage plugins and workspaces.
+
+## Example
+
+This example demonstrates how to use a file system and a hive schema to query
+a file and table in Drill.
+
+Issue the SHOW DATABASES or SHOW SCHEMAS command to see a list of the
+available schemas that you can use. Both commands return the same results.
+
+ 0: jdbc:drill:zk=drilldemo:5181> show schemas;
+ +-------------+
+ | SCHEMA_NAME |
+ +-------------+
+ | hive.default |
+ | dfs.reviews |
+ | dfs.flatten |
+ | dfs.default |
+ | dfs.root |
+ | dfs.logs |
+ | dfs.myviews |
+ | dfs.clicks |
+ | dfs.tmp |
+ | sys |
+ | hbase |
+ | INFORMATION_SCHEMA |
+ | s3.twitter |
+ | s3.reviews |
+ | s3.default |
+ +-------------+
+ 15 rows selected (0.059 seconds)
+
+
+Issue the USE command with the schema that you want Drill to query.
+**Note:** If you use any of the Drill default schemas, such as `cp.default` or `dfs.default`, do not include .`default`. For example, if you want Drill to issue queries on files in its classpath, you can issue the following command:
+
+ 0: jdbc:drill:zk=local> use cp;
+ +------------+------------+
+ | ok | summary |
+ +------------+------------+
+ | true | Default schema changed to 'cp' |
+ +------------+------------+
+ 1 row selected (0.04 seconds)
+
+Issue the USE command with a file system schema.
+
+ 0: jdbc:drill:zk=drilldemo:5181> use dfs.logs;
+ +------------+------------+
+ | ok | summary |
+ +------------+------------+
+ | true | Default schema changed to 'dfs.logs' |
+ +------------+------------+
+ 1 row selected (0.054 seconds)
+
+You can issue the SHOW FILES command to view the files and directories within
+the schema.
+
+ 0: jdbc:drill:zk=drilldemo:5181> show files;
+ +------------+-------------+------------+------------+------------+------------+-------------+------------+------------------+
+ | name | isDirectory | isFile | length | owner | group | permissions | accessTime | modificationTime |
+ +------------+-------------+------------+------------+------------+------------+-------------+------------+------------------+
+ | csv | true | false | 1 | mapr | mapr | rwxrwxr-x | 2015-02-09 06:49:17.0 | 2015-02-09 06:50:11.172 |
+ | logs | true | false | 3 | mapr | mapr | rwxrwxr-x | 2014-12-16 18:58:26.0 | 2014-12-16 18:58:27.223 |
+ +------------+-------------+------------+------------+------------+------------+-------------+------------+------------------+
+ 2 rows selected (0.156 seconds)
+
+Query a file or directory in the file system schema.
+
+ 0: jdbc:drill:zk=drilldemo:5181> select * from logs limit 5;
+ +------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
+ | dir0 | dir1 | trans_id | date | time | cust_id | device | state | camp_id | keywords | prod_id | purch_flag |
+ +------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
+ | 2014 | 8 | 24181 | 08/02/2014 | 09:23:52 | 0 | IOS5 | il | 2 | wait | 128 | false |
+ | 2014 | 8 | 24195 | 08/02/2014 | 07:58:19 | 243 | IOS5 | mo | 6 | hmm | 107 | false |
+ | 2014 | 8 | 24204 | 08/01/2014 | 12:10:27 | 12048 | IOS6 | il | 1 | marge | 324 | false |
+ | 2014 | 8 | 24222 | 08/02/2014 | 16:28:37 | 2488 | IOS6 | pa | 2 | to | 391 | false |
+ | 2014 | 8 | 24227 | 08/02/2014 | 07:14:00 | 154687 | IOS5 | wa | 2 | on | 376 | false |
+ +------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
+
+Issue the USE command to switch to the hive schema.
+
+ 0: jdbc:drill:zk=drilldemo:5181> use hive;
+ +------------+------------+
+ | ok | summary |
+ +------------+------------+
+ | true | Default schema changed to 'hive' |
+ +------------+------------+
+ 1 row selected (0.093 seconds)
+
+Issue the SHOW TABLES command to see the tables that exist within the schema.
+
+ 0: jdbc:drill:zk=drilldemo:5181> show tables;
+ +--------------+------------+
+ | TABLE_SCHEMA | TABLE_NAME |
+ +--------------+------------+
+ | hive.default | orders |
+ | hive.default | products |
+ +--------------+------------+
+ 2 rows selected (0.421 seconds)
+
+Query a table within the schema.
+
+ 0: jdbc:drill:zk=drilldemo:5181> select * from products limit 5;
+ +------------+------------+------------+------------+
+ | prod_id | name | category | price |
+ +------------+------------+------------+------------+
+ | 0 | Sony notebook | laptop | 959 |
+ | 1 | #10-4 1/8 x 9 1/2 Premium Diagonal Seam Envelopes | Envelopes | 16 |
+ | 2 | #10- 4 1/8 x 9 1/2 Recycled Envelopes | Envelopes | 9 |
+ | 3 | #10- 4 1/8 x 9 1/2 Security-Tint Envelopes | Envelopes | 8 |
+ | 4 | #10 Self-Seal White Envelopes | Envelopes | 11 |
+ +------------+------------+------------+------------+
+ 5 rows selected (0.211 seconds)
+
+
+