Supported SQL Commands

The following table provides a list of the SQL commands that Drill supports, with their descriptions and example syntax:

CommandDescriptionSyntax
ALTER SESSIONChanges 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, refer to Planning and Execution Options.
ALTER SESSION SET `<option_name>`=<value>;
ALTER SYSTEMPermanently changes a system setting. The new settings persist across all sessions. For a list of Drill options and their descriptions, refer to Planning and Execution Options.
ALTER SYSTEM SET `<option_name>`=<value>;

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. When you issue the CTAS command, you create a directory that contains parquet or CSV files. Each workspace in a file system has a default file type.
You can specify which writer you want Drill to use when creating a table: parquet, CSV, or JSON (as specified with the store.format option).
CREATE TABLE new_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>;
DESCRIBEReturns information about columns in a table or view.
DESCRIBE [workspace.]table_name|view_name
DROP VIEWRemoves a view.
DROP VIEW [workspace.]view_name ;
EXPLAIN PLAN FORReturns the physical plan for a particular query.
EXPLAIN PLAN FOR <query>;
EXPLAIN PLAN WITHOUT IMPLEMENTATION FORReturns the logical plan for a particular query.
EXPLAIN PLAN WITHOUT IMPLEMENTATION FOR <query>;
SELECTRetrieves data from tables and files.
[WITH subquery]
SELECT column_list FROM table_name 
[WHERE clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause];
SHOW DATABASES Returns a list of available schemas. Equivalent to SHOW SCHEMAS.
SHOW DATABASES;
SHOW FILESReturns a list of files in a file system schema.
SHOW FILES IN filesystem.`schema_name`;
SHOW FILES FROM filesystem.`schema_name`;
SHOW SCHEMASReturns a list of available schemas. Equivalent to SHOW DATABASES.
SHOW SCHEMAS;
SHOW TABLESReturns a list of tables and views.
SHOW TABLES;
USEChange to a particular schema. When you opt to use a particular schema, Drill issues queries on that schema only.
USE schema_name;
← SQL CommandsALTER SESSION Command →