This page shows how to create partitioned Hive tables via Hive SQL (HQL).
Create partition table
Example:
CREATE TABLE IF NOT EXISTS hql.transactions(txn_id BIGINT, cust_id INT, amount DECIMAL(20,2),txn_type STRING, created_date DATE)
COMMENT 'A table to store transactions'
PARTITIONED BY (txn_date DATE)
STORED AS PARQUET;
The above command creates a Hive table partitioned by txn_date column.
Multiple partition columns
Example:
CREATE TABLE IF NOT EXISTS hql.transactions(txn_id BIGINT, cust_id INT, amount DECIMAL(20,2), created_date DATE)
COMMENT 'A table to store transactions'
PARTITIONED BY (txn_date DATE, txn_type STRING)
STORED AS PARQUET;
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: