Data Warehouse Statement Types

Lists the SQL statement types for data warehousing workloads that use Apache Impala. You can find the statement types on the Data Warehouse Queries page in the Type list. For more detailed information about these types of SQL statements, see the Impala documentation.

Table 1. Data Warehouse Statement Types
Statement Type Description
ALTER TABLE Changes the structure or properties of an existing table. For example, ALTER TABLE table_name ADD PARTITION (month=1, day=1);
ALTER VIEW Changes the characteristics of a view. For example, ALTER VIEW view_name AS SELECT * FROM table_name;
COMPUTE STATS Gathers information about volume and distribution data in a table and all associated columns and partitions. For example, COMPUTE STATS table_name;
CREATE DATABASE Creates a new database. For example, CREATE DATABASE database_name;
CREATE FUNCTION Creates a user-defined function (UDF), which you can use to implement custom logic during SELECT or INSERT operations. For example, CREATE FUNCTION function_name LOCATION 'hdfs_path_to_jar' SYMBOL='class_name';
CREATE ROLE Creates a role to which privileges can be granted. After privileges are granted to roles, then the roles can be assigned to users. A user who has been assigned a role is only able to exercise the privileges of that role. For example, CREATE ROLE role_name;
CREATE TABLE Creates a new table and specifies its characteristics. For example, CREATE TABLE table_name (column_name data_type) PARTITIONED BY (column_name data_type) LOCATION 'hdfs_path';
CREATE TABLE AS SELECT Creates a new table with the output from a SELECT statement. For example, CREATE TABLE table_name AS SELECT * FROM table_3;
CREATE TABLE LIKE Creates a new table by cloning an existing table. For example, CREATE TABLE table_name_2 LIKE table_name_1;
CREATE VIEW Creates a shorthand abbreviation (alias) for a query. A view is a purely logical construct with no physical data behind it. For example, CREATE VIEW view_name AS SELECT * FROM table_name;
DDL Data Definition Language. SQL statements that define data structures. For example, CREATE TABLE;
DESCRIBE DB Displays metadata about a database. For example, DESCRIBE database_name;
DESCRIBE TABLE Displays metadata about a table. For example, DESCRIBE table_name;
DML Data Manipulation Language. SQL statements that manipulate data structures. For example, ALTER TABLE;
DROP DATABASE Removes a database from the system. For example, DROP database_name;
DROP FUNCTION Removes a user-defined function (UDF) so that it is not available for execution during Impala SELECT or INSERT operations. For example, DROP FUNCTION function_name;
DROP STATS Removes the specified statistics from a table or a partition. For example, DROP STATS table_name;
DROP TABLE Removes a table and its underlying HDFS data files for internal tables, although not for external tables. For example, DROP TABLE table_name;
DROP VIEW Removes the specified view. Because a view is purely a logical construct with no physical data behind it, DROP VIEW only involves changes to metadata in the metastore database, not any data files in HDFS. For example, DROP VIEW view_name;
EXPLAIN Generates a query execution plan for a specific query. For example, EXPLAIN SELECT * FROM table_1;
GRANT PRIVILEGE Grants privileges on specified objects to groups. For example, GRANT privilege_name ON TABLE table_name TO role_name;
GRANT ROLE Grants roles on specified objects to groups. For example, GRANT ROLE role_nameTO GROUP group_name;
LOAD Loads data from an external data source into a table. For example, LOAD DATA INPATH 'hdfs_file_or_directory_path' INTO TABLE tablename;
N/A These queries failed due to syntax errors and Impala is not able to identify a query type for them.
REFRESH Reloads the metadata for a table from the metastore database and does an incremental reload of the file and block metadata from the HDFS NameNode. REFRESH is used to avoid inconsistencies between Impala and external metadata sources, specifically the Hive Metastore and the NameNode. For example, REFRESH table_name;
REVOKE PRIVILEGE Revokes privileges on a specified object from groups. For example, REVOKE privilege_name ON TABLE table_name;
REVOKE ROLE Revokes roles on a specified object from groups. For example, REVOKE ROLE role_name FROM GROUP group_name;
SELECT Requests data from a data source. For example, SELECT * FROM table_1;
SET Sets configuration properties or session parameters. For example, SET compression_codec=snappy;
SHOW COLUMN STATS Displays the column statistics for a specified table. For example, SHOW COLUMN STATS table_name;
SHOW CREATE TABLE Displays the CREATE TABLE statement used to reproduce the current structure of a table. For example, SHOW CREATE TABLE table_name;
SHOW DATABASES Displays all available databases. For example, SHOW DATABASES;
SHOW FILES Displays the files that constitute a specified table or a partition within a partitioned table. For example, SHOW FILES IN table_name;
SHOW FUNCTIONS Displays user-defined functions (UDFs) or user-defined aggregate functions (UDAFs) that are associated with a particular database. For example, SHOW FUNCTIONS IN database_name; or SHOW AGGREGATE FUNCTIONS IN database_name;
SHOW GRANT ROLE Lists all the grants for the specified role name. For example, SHOW GRANT ROLE role_name;
SHOW ROLES Displays all available roles. For example, SHOW ROLES;
SHOW TABLES Displays the names of tables. For example, SHOW TABLES;
SHOW TABLE STATS Displays the statistics for a table. For example, SHOW TABLE STATS table_name;
TRUNCATE TABLE Removes the data from an Impala table, while leaving the table itself. For example, TRUNCATE TABLE table_name;
USE Switches the current session to a specified database. For example, USE database_name;