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;
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;