Hive Tables

Hive Tables 

Hive is Apache Project.
There are 2 types of the tables.

1. Internal or Managed table
2  External table

Internal/ Managed Table:-

Internal tables are basically used for testing purpose or local code development.

Syntax:-

Creation of the table:-

Create table <table name>
(  <field name>   <data type>,
 <field name>   <data type>,
 <field name>   <data type>,
 <field name>   <data type>
)
row format delimited
fields terminated by '<separator char in file>';

Loading data:-

Load data inpath '<path of the file>' into table <tablename>;


Key points:
1. when user run create table command that time hive internally creates a directory with the name of the table  in the Data base directory at '/user/hive/warehouse/<db name>'

2. as the name suggested, the managed tables are managed by hive.

3. when user run the load data command at that time file is copied to '/user/hive/warehouse/<db name>/<file name>'

4 so it is like cut paste. cut the file from original location and paste the file into warehouse path.

5.on dropping the table , hive will delete the directory from warehouse location and data will be lost.


External table:-
Basic property of external is if we drop the external table the original file as well as copy of the file in warehouse location not deleted.

There are 2 types of external table
1 . with location
2. without location

with location --> table is copied into warehouse location --> [copy paste]
without location --> table is not copied to warehouse location