Table Creation ( Internal & External With Partitions)

drop table if exists mhtprabhu.emp_sample;
create table if not exists mhtprabhu.emp_sample like venkat_db.pet_owners;
insert into mhtprabhu.emp_sample select * from venkat_db.pet_owners limit 20;

--------------------------------------------------------------------------------------------------------------------------
create table student (id int, name string, country string, department string, salary int) row format delimited fields terminated by ',';

--------------------------------------------------------------------------------------------------------------------------

create table if not exists student1 ( name string, id int , year int); 
create table if not exists student2 ( name string, id int , year int) 
row format delimited fields terminated by '\t' 
lines terminated by '\n' 
stored as textfile;

--------------------------------------------------------------------------------------------------------------------------

create external table if not exists student3 ( name string, id int , year int) 
row format delimited fields terminated by '|' 
lines terminated by '\n' 
stored as textfile;

--------------------------------------------------------------------------------------------------------------------------

create table if not exists hari_tab as select * from student where 1=0;

Note: Table create only with struture , but no data

--------------------------------------------------------------------------------------------------------------------------

create table if not exists prabhu_tab as select * from student where 1=1;

Note: Table create with structure and complete data

--------------------------------------------------------------------------------------------------------------------------

-- With Table Properties

create table demo.new_employee (Id int comment 'Employee Id', Name string comment 'Employee Name', Salary float comment 'Employee Salary')  

comment 'Table Description'  

TBLProperties ('creator'='Gaurav Chawla', 'created_at' = '2019-06-06 11:00:00');  

--------------------------------------------------------------------------------------------------------------------------

-- Exteranal Table Creation

create external table emplist ( name string, id int , year int) 

row format delimited  

 fields terminated by ','   

location ' /home/haritaraka12078/HiveDirectory';  

--------------------------------------------------------------------------------------------------------------------------

create table student_emp (id int, name string, age int,  institute string)   

partitioned by (course string)  

row format delimited  

fields terminated by ',';  

hive> load data local inpath '/home/codegyani/hive/student_details1' into table student  

partition(course= "java");    

hive> load data local inpath '/home/codegyani/hive/student_details2' into table student  

partition(course= "hadoop");  

--------------------------------------------------------------------------------------------------------------------------

Dynamic Partition

the values of partitioned columns exist within the table. So, it is not required to pass the values of partitioned columns manually.

Table Create:

create table student5 (id int, name string, age int, institute string, course string)

row format delimited  

fields terminated by ','

lines terminated by '\n' 

stored as textfile; 

Create Data in hadoop text file:

hadoop fs -put -p student.txt

 

[haritaraka12078@cxln4 ~]$ hadoop fs -cat student.txt

10,prabhu,24,mumbai,sql

20,hari,34,tnk,oracle

30,tarak,44,vjy,etl

40,manyam,54,dubai,hive

50,sunny,28,mumbai,sql

[adimulamvenkat19851609@cxln4 ~]$ hadoop fs -ls

Load Data:

LOAD DATA LOCAL inpath '/home/adimulamvenkat19851609/student.txt' INTO TABLE student5;

Create Table 2 with dynamic partation:

create table student_part(id int, name string, age int, institute string)   

partitioned by (course string)  

row format delimited  

fields terminated by ','

lines terminated by '\n' 

stored as textfile;  

Load Data: 

insert into student_part  partition(course)  select id, name, age, institute, course  from student5;  

Note: It won't allowed, because 

FAILED: SemanticException [Error 10096]: Dynamic partition strict mode requires at least one static partition column. To turn this off set hive.exec.dynamic.partition.mode=nonstrict

Now Change Property files 

• set hive.exec.dynamic.partition=true;    
• set hive.exec.dynamic.partition.mode=nonstrict;  

Now insert data again:

insert into student_part  partition(course)  select id, name, age, institute, course  from student5;  

hive> select * from student_part where course='sql';

OK

10      prabhu  24      mumbai      sql

50      sunny   28      mumbai      sql

--------------------------------------------------------------------------------------------------------------------------

Table Creation Examples:

Table:1

drop table if exists mhtprabhu.emp_sample;

create table if not exists mhtprabhu.emp_sample like venkat_db.pet_owners;

insert into mhtprabhu.emp_sample select * from venkat_db.pet_owners limit 20;

select count(*) from mhtprabhu.emp_sample; --------------->>.20

Table:2

hive> create table student4 (id int, name string, country string, department string, salary int) row format delimited fields terminated by ',';

hive> desc student4;

hive> desc formatted student4;

[adimulamvenkat19851609@cxln4 ~]$cat>student4.txt

1,prabhu,india,sales,20000

2,hari,uk,market,50000

3,subbu,india, police,25000

4,prakash,uk,lawyer,25400

5,mahesh,india,sales,30000

6,pawan,uk,market,55000

7,prasad,india, police,45000

8,abhi,uk,lawyer,55400

[adimulamvenkat19851609@cxln4 ~]$ hadoop fs -put -p student4.txt

[adimulamvenkat19851609@cxln4 ~]$ hadoop fs -cat student4.txt

load data inpath '/user/adimulamvenkat19851609/student4.txt' overwrite into table student4;

select count(*) from student4;-------->>>8