HIVE - Create Alter & Drop Tables

CREATE:

we can create a table in two ways

1.Internal or managed table

2.External table

hive> create database rkstd;
hive> show databases; 

we can see using webui by typing localhost:9870 in the url

Path to find rkstd.db: /user/hive/warehouse

hive> create table clstd(sid int,sname string)
    > row format delimited
    > fields terminated by ',';

hive> show tables;

hive> use rkstd;

hive> create table clwstd(sid int,sname string)
    > row format delimited
    > fields terminated by ',';

hive> load data local inpath '/home/rk/Desktop/cstd' into table clwstd ;

hive> select * from clwstd ;

hive> load data local inpath '/home/rk/Desktop/cstd1' into table clwstd ;

hive> select * from clwstd ;

hive> describe <or> desc clwstd;

hive> describe extended clwstd ; // metadata

ALTER: 

alter table:

hive> alter table clstd rename to clstd_int ;

external table:

hive> create external table clwstd_ext(sno int, sname string)
    > row format delimited
    > fields terminated by ',' ;

hive> show tables ;

hive> load data local inpath '/home/rk/Desktop/cstd' into table clwstd_ext ;

hive> select * from clwstd_ext;

hive> show tables ;
OK
clwstd_ext
clwstd_int
Time taken: 0.054 seconds, Fetched: 2 row(s)

DROP:

drop table:

hive> drop table clwstd_ext ;
OK
Time taken: 0.892 seconds
hive> drop table clwstd_int ;
OK
Time taken: 0.206 seconds
hive> show tables ;
OK
Time taken: 0.024 seconds

Note: when we try to droped internal and external tables created in the same database  we observed that only internal table will be deleted i.e clwstd_int

 

 

How to view the content of the file from hive  

  

INSERT:

How to insert the content of data into the file(clinst_int)  

 

Method -1:

hive> create table clinst_int(sno int, sname string)
    > row format delimited
    > fields terminated by ',' ;

hive> show tables ;
hive> select * from clinst_int ;
hive> load data local inpath '/home/rk/Desktop/cstd' into table clinst_int ;
hive> select * from clinst_int;
OK
111    raju
222    rani
444    vani
Time taken: 0.169 seconds, Fetched: 3 row(s)

Method -2:

hive> use rk ;

hive> create table s_cluster ( sno int, sname string);

hive> show tables ;

hive> desc s_cluster ;

hive> insert into table s_cluster values (11,'rama');

hive> insert into table s_cluster values (12,'sita');

hive> select * from s_cluster ;