USE

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. Note: You must include the full path to a file or table whenever there is a temporary table in the session with the same name. See CTTAS.

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, in dfs.donuts, dfs is the configured file system and donuts the workspace. The workspace points to a directory within the file system. You can configure and use multiple storage plugins and workspaces in Drill. See Storage Plugin Registration and 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.


NOTE By default, Drill returns a result set when you issue DDL statements, such as USE. If the client tool from which you connect to Drill (via JDBC) does not expect a result set when you issue DDL statements, set the exec.query.return_result_set_for_ddl option to false, as shown, to prevent the client from canceling queries:

	SET `exec.query.return_result_set_for_ddl` = false
	//This option is available in Drill 1.15 and later.

When set to false, Drill returns the affected rows count, and the result set is null. ***

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      | abcd  | abcd  | rwxrwxr-x   | 2015-02-09 06:49:17.0 | 2015-02-09 06:50:11.172 |
| logs | true        | false  | 3      | abcd  | abcd  | 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)