Create, Drop, and Truncate Table - Hive SQL
insights Stats
Articles about Apache Hadoop, Hive and HBase installation, performance tuning and general tutorials.
*The yellow elephant logo is a registered trademark of Apache Hadoop.
This page shows how to create, drop, and truncate Hive tables via Hive SQL (HQL).
External and internal tables
Refer to Differences between Hive External and Internal (Managed) Tables to understand the differences between managed and unmanaged tables in Hive.
Create table
Example:
CREATE TABLE IF NOT EXISTS hql.customer(cust_id INT, name STRING, created_date DATE) COMMENT 'A table to store customer records.';
Output:
OK
Drop table
Syntax:
DROP TABLE [IF EXISTS] table_name [PURGE];
Example:
DROP TABLE IF EXISTS hql.customer;
Underlying data of this internal table will be moved to Trash folder.
DROP TABLE IF EXISTS hql.customer PURGE;
Underlying data in HDFS will be purged directly and table cannot be restored.
Truncate table
Syntax:
TRUNCATE [TABLE] table_name [PARTITION partition_spec]; partition_spec: : (partition_column = partition_col_value, partition_column = partition_col_value, ...)
TRUNCATE state is used to truncate a table or partitions in a table.
Example:
TRUNCATE TABLE hql.customer;
Install Hive database
Follow the article below to install Hive on Windows 10 via WSL if you don't have available available Hive database to practice Hive SQL:
Examples on this page are based on Hive 3.* syntax.
Run query
All these SQL statements can be run using beeline CLI:
$HIVE_HOME/bin/beeline --silent=true
The above command line connects to the default HiveServer2 service via beeline. Once beeline is loaded, type the following command to connect:
0: jdbc:hive2://localhost:10000> !connect jdbc:hive2://localhost:10000 Enter username for jdbc:hive2://localhost:10000: hive Enter password for jdbc:hive2://localhost:10000: 1: jdbc:hive2://localhost:10000>
The terminal looks like the following screenshot: