Apache hive tips and tricks

By | April 17, 2017

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 employee LOCATION ‘/my/emp/datafiledir’ COMMENT ‘Contains Employee Details’

How to add database properties for hive database

Hive> CREATE DATABASE employee WITH DBPROPERTIES (‘owner’ = ‘Toodey’, ‘date’ = ’2017-04-16’)

How to use EXTENDED keyword in hive and describe the hive database details in more detailed way.

Hive> DESCRIBE DATABASE EXTENDED employee;

This command will show you the full hdfs location of database where table directory is stored also the properties assigned with this database.

How to use CASCADE keyword in hive to drop the database forcefully, as we know hive doesn’t allow us to drop database if database contains tables.

Hive> DROP DATABASE IF EXISTS employee CASCADE;




How to alter or add database properties to existing database in hive

Hive> ALTER DATABASE employee SET DBPROPERTIES (‘owner2’ = ‘Toodey2’)

Like the database properties we can also set properties for tables in hive

Let’s consider the below create table hive query.

Hive> CREATE TABLE IF NOT EXISTS employee ( Name STRING COMMENT ‘Emp Name’, Salary FLOAT COMMENT ‘Employee Salary’COMMENT ‘Employee table properties example’ TBLPROPERTIES (‘owner’ = ‘toodeyTBL’, ‘date’ = ‘2017-01-01’)

How to show hive table properties

Hive> SHOW TBLPROPERTIES employee;

How to copy hive schema to make another hive table

Hive> CREATE TABLE IF NOT EXISTS employee1 LIKE employee

In this case employee table should already need to be exist.

How to see schema only for specific column in hive

Hive> DESCRIBE default.employee.name;

In this case default is my database name, employee is my table and name is column of employee table.

RECOMMENDED BOOK: PROGRAMMING HIVE