Hive Tutorial | Hive DDL Commands

Sponsored Links

Ad by Google

Apache Hive is data warehouse tool to process structure dataset and it's based on Hadoop infrastructure. The syntax is very much similar to MySql database, Hive mainly used for data presentation phase unlike Pig, here is very nice article explained on the differences between Apache Hive and Apache Pig. Hive was developed at Facebook, but now it's a part of Apache project and known as one of the popular Apache project among other big data technologies.

In this tutorial, We will see Hive DDL Commands with simple example,hive ddl is a part of Hive Query Language and the ddl commands use in hive are very much similar to other databases like mysql.
If you are very new to hive, then you have to prepared your hive environment and here is a step by step guide to setup hive environment.

HiveQL: Data Definition Language

1. CREATE database database_name;
   

   create database ddl_commands;
   

2. CREATE schema schema_name;
   

   create schema ddl_commands;
   

3. CREATE table
   

   create table t(t_name string);
   

4. CREATE view
   

   create view t_view as select * from t;
   

5. CREATE function
   

   CREATE function my_fun AS MainClass USING JAR myjar;
   

6. CREATE index
   

   CREATE index index_t on table t2(t_name) AS
   'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
   WITH DEFERRED REBUILD;
   

7. DESCRIBE database
   

   DESCRIBE database ddl_commands;
   

8. DESCRIBE schema
   

   DESCRIBE schema ddl_commands;
   

9. DESCRIBE table
   

   DESCRIBE t1;
   

10. DESCRIBE view_name
    

    DESCRIBE t_view;
    

11. TRUNCATE table
    

    TRUNCATE t;
    

12. ALTER database
    

    ALTER database ddl_commands SET
    DBPROPERTIES ('creator' = 'javamakeuse');
    

13. ALTER schema
    

    ALTER schema ddl_commands SET
    DBPROPERTIES ('creator' = 'javamakeuse');
    

14. ALTER table
    

    ALTER table t SET TBLPROPERTIES ("comment"="table for learning");
    

15. ALTER view
    

    ALTER view t_view as select t_name from t;
    

16. SHOW databases
    

    SHOW databases;
    

17. SHOW schemas
    

    SHOW schemas;
    

18. SHOW tables
    

    SHOW tables;
    

19. SHOW TBLPROPERTIES
    

    SHOW TBLPROPERTIES t;
    

20. SHOW PARTITIONS
    

    SHOW PARTITIONS t;
    

21. SHOW functions
    

    SHOW functions;
    

22. SHOW indexs
    

    show index on t;
    

23. SHOW columns
    

    show columns from t;
    

24. SHOW create table
    

    show CREATE TABLE t;
    

25. MSCK repair table
    

    MSCK REPAIR TABLE t;
    

DROP COMMANDS:

1. DROP index
   

   drop index index_t on t;
   

2. DROP view
   

   drop view t_view;
   

3. DROP table
   

   drop table t;
   

4. DROP database
   

   drop database ddl_commands;
   

5. DROP schema
   

   drop schema ddl_commands;
   

That's it all about Hive DDL commands.

Sponsored Links