Category Archives: Hive

How to add multiple partition in hive

How to add multiple partition in hive The simple example to add partition would be ALTER TABLE employee ADD IF NOT EXISTS PARTITION (countryName= ‘US’) LOCATION ‘/employee/US’ PARTITION (countryName= ‘EU’) LOCATION ‘/employee/EU’ How to drop a partition in hive ALTER TABLE employee DROP IF EXISTS PARTITION(countryName= ‘US’) How to rename a column in hive ALTER… Read More »

Partition internal or managed tables in hive

How to partition managed or internal hive table? Let’s say we need to partition the data country wise. Hive> CREATE TABLE countryDetails ( cid INT, countryName STRING ) PARTITIONED BY (countryName STRING) So in this case the country wise directory partitioned will be get created, so for US and EU there will be two separate directories will get created.… Read More »

What is Manage tables and External tables in hive?

Manage tables in hive: Manage tables in hive also called internal tables, this table uses hive default warehouse directory to store the table data. (hive.metastore.warehouse.dir) One disadvantage is, hive doesn’t allow another tools to share the data that is hive has the ownership of data and that’s where the external tables are came into picture.… Read More »

Apache hive tips and tricks

Display hive databases which starts with the given name using LIKE Hive> SHOW DATABASES LIKE ‘e.*’; employee emp … How to create hive database in specific or custom location Hive> CREATE DATABASE employee LOCATION ‘/my/emp/datafiledir’ In this case location is our HDFS location. We can also add comments while creating hive database Hive> CREATE DATABASE… Read More »

How to load data into hive partition table

How to load data into hive partition table LOAD DATA INPATH ‘/employee/emp.txt’ OVERWRITE  INTO TABLE employee PARTITION (countryName= ‘US’, state = ‘CH’) How to insert data into hive tables using select query INSERT OVERWRITE TABLE employee PARTITION (countryName = ‘US’, state = ‘CH’) SELECT * FROM old_emp oemp WHERE oemp.countryName = ‘US’ AND oemp.state = ‘CH’;… Read More »

Apache hive tips and tricks

Trick 1 – Running a hive query command from outside of hive CLI shell. $ hive –e “SELECT * FROM toodey” -e command in hive will help in running above hive query. Trick 2 – Run the query in silent mode, this means if you don’t want to see the query processing output just use… Read More »