Supported SQL Commands
The following table provides a list of the SQL commands that Drill supports, with their descriptions and example syntax:
| Command | Description | Syntax |
|---|---|---|
| ALTER SESSION SET | The SET command replaces the ALTER SESSION SET command in Drill version 1.3 and later. ALTER SESSION is now just an alias for the SET command. See SET. | [ALTER SESSION] SET `option_name` = value; |
| ALTER SYSTEM SET | Changes a system setting. The new setting persists across all sessions. For a list of Drill options and their descriptions, see [Planning and Execution Options]. | ALTER SYSTEM SET `option_name` = value; |
| ALTER SYSTEM RESET | Changes a system setting back to its default system setting. For a list of Drill options and their descriptions, see [Planning and Execution Options]. | ALTER SYSTEM RESET `option_name`; |
| ALTER SYSTEM RESET ALL | Changes all system settings back to their default system values. For a list of Drill options and their descriptions, see [Planning and Execution Options]. | ALTER SYSTEM RESET ALL; |
| CREATE TABLE AS(CTAS) | 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. | CREATE TABLE name AS query; |
| CREATE VIEW | Creates a virtual structure for the result set of a stored query.- | CREATE [OR REPLACE] VIEW [workspace.]view_name [ (column_name [, ...]) ] AS query; |
| DESCRIBE | Returns information about columns in a table or view. | DESCRIBE [workspace.]table_name |
| DROP VIEW | Removes a view. | DROP VIEW [workspace.]view_name ; |
| EXPLAIN PLAN FOR | Returns the physical plan for a particular query. | EXPLAIN PLAN FOR query; |
| EXPLAIN PLAN WITHOUT IMPLEMENTATION FOR | Returns the logical plan for a particular query. | EXPLAIN PLAN WITHOUT IMPLEMENTATION FOR query; |
| RESET | Changes a session setting back to its default system setting. For a list of Drill options and their descriptions, see [Planning and Execution Options]. | [ALTER SESSION] RESET `option_name`; |
| SELECT | Retrieves data from tables and files. | [WITH subquery]SELECT column_list FROM table_name[WHERE clause] [GROUP BY clause][HAVING clause][ORDER BY clause]; |
| SET | 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, see [Planning and Execution Options]. | [ALTER SESSION] SET `option_name` = value; |
| SHOW DATABASES | Returns a list of available schemas. Equivalent to SHOW SCHEMAS. | SHOW DATABASES; |
| SHOW FILES | Returns a list of files in a file system schema. | SHOW FILES IN|FROM filesystem.`schema_name`; |
| SHOW SCHEMAS | Returns a list of available schemas. Equivalent to SHOW DATABASES. | SHOW SCHEMAS; |
| SHOW TABLES | Returns a list of tables and views. | SHOW TABLES; |
| USE | Change to a particular schema. When you opt to use a particular schema, Drill issues queries on that schema only. | USE schema_name; |