Create Partitioned Table - Hive SQL

access_time 27 days ago visibility13 comment 0

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:


info Last modified by Administrator at 27 days ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Kontext Column

Created for everyone to publish data, programming and cloud related articles.
Follow three steps to create your columns.


Learn more arrow_forward

More from Kontext

local_offer hive local_offer SQL local_offer hive-sql-ddl

visibility 37
thumb_up 0
access_time 28 days ago

This page shows how to create, drop, and truncate Hive tables via Hive SQL (HQL). External and internal tables Refer to  Differences between Hive Extern...

local_offer teradata local_offer SQL local_offer teradata-functions

visibility 10
thumb_up 0
access_time 15 days ago

Teradata LPAD function is used to add repeated characters at the beginning of a string to increase the string to a specified length. It can be used to add leading space or zeros to a string. LPAD function signature  LPAD(source_string, length, fill_string) Returns the...

local_offer teradata local_offer SQL local_offer teradata-functions

visibility 24
thumb_up 0
access_time 2 months ago

The following code snippets show how to use round numbers to hundreds or thousands in Teradata.  warning Alert - The following code snippets are used to round to the smallest hundreds or thousands that is equal or greater than the i...

About column

Hadoop

Articles about Apache Hadoop installation, performance tuning and general tutorials.

*The yellow elephant logo is a registered trademark of Apache Hadoop.

rss_feed Subscribe RSS