Display hive databases which starts with the given name using LIKE
Hive> SHOW DATABASES LIKE ‘e.*’;
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