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; | |
| ANALYZE TABLE REFRESH METADATA | Computes and stores the table schema and table statistics to the Drill Metastore. | ANALYZE TABLE [table_name | table({table function name}(parameters))] [COLUMNS {(col1, col2, …) | NONE}] REFRESH METADATA [‘level’ LEVEL] [{COMPUTE | ESTIMATE} | STATISTICS [(column1, column2, …)] [ SAMPLE number PERCENT ]] | |
| ANALYZE TABLE COMPUTE STATISTICS | Computes statistics on Parquet data stored in tables and writes it to a JSON file in the .stats.drill directory. |
ANALYZE TABLE [workspace.]table_name COMPUTE STATISTICS [(column1, column2,…)] [SAMPLE number PERCENT] | |
| 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 TEMPORARY TABLE AS(CTTAS) | Stores the results of a query in a temporary table. | CREATE TEMPORARY 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; | |
| INSERT INTO | Inserts data into a datasource. | { INSERT | UPSERT } INTO tablePrimary [ ‘(‘ column [, column ]* ‘)’ ] 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; |