Hive partition tables

Question

Can we create manual partition for external tables ? if so how can we create ?

Answer 1

Yes, you can create manual partition. Here's how:

Open a new terminal and fire up hive by just typing hive. Create table on weather data.

CREATE EXTERNAL TABLE student ( name STRING, doj STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’ LOCATION ‘ /hive/data/student’;

ROW FORMAT should have delimiters used to terminate the fields and lines like in the above example the fields are terminated with comma (“,”). The default location of Hive table is overwritten by using LOCATION. So the data now is stored in data/student folder inside hive.

Load the data in table

Load the data from HDFS to Hive using the following command:

LOAD DATA INPATH ‘hdfs:/data/2012.txt’ INTO TABLE student;

Partitioning of table

Hive stores tables in partitions. Partitions are used to divide the table into related parts. Partitions make data querying more efficient. For example in the above weather table the data can be partitioned on the basis of year and month and when query is fired on weather table this partition can be used as one of the column.

CREATE EXTERNAL TABLE IF NOT EXSISTS student ( name STRING, doj STRING)
PARTITIONED BY (year INT, month STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’ LOCATION ‘ /hive/data/student’;