Hive_Table_Operation

Hive_Table_Operation

Hive_Table_Operation

I.) using 'insert' statement we can load the data from one table to another table. by default , we can not see the column name in the hive, Using the below property we can set as true , afte which we can see the column name; property :=> set hive.cli.print.header = true; hive > use oioi; Creating emp table and Loading the data from local hive> create table emp(id int, name string, role string, zip int, code int , rank int , keyword string) row format delimited fields terminated by',' lines terminated by'\n' stored as textfile; hive > load data local inpath '/home/aakashkumar/files/employee.txt' into table emp; Creating the target table and Loading the data from source table. Note :=> it is not necessary, while loading the data from source table to destination table both table should have same number of column. hive> create table emp_cp(id int, name string, role string, zip int, code int , rank int , keyword string) stored as textfile; OK Time taken: 0.43 seconds hive> select * from emp_cp; OK emp_cp.id emp_cp.name emp_cp.role emp_cp.zip emp_cp.code emp_cp.rank emp_cp.keyword Time taken: 0.195 seconds hive> 1.) Using into a.) loading all the columns from source to destination. hive> insert into table emp_cp select * from emp; hive> select * from emp_cp; b.) loading selected column from from source to destination. u can create a table emp_cp_test with three column id,name and zip and after creating use below command for loading the data. hive> insert into table emp_cp_test select id,name,zip from emp; hive> select * from emp_cp_test; c.) even we can use where clause for loading the data from source table to destination table. hive> insert into table emp_cp_test select id,name,zip from emp where role = 'Developer' ; 2.) Using overwrite. ( it will clear all the old data and insert the new data) a.) loading all the columns from source to destination. hive> insert overwrite table emp_cp select * from emp; hive> select * from emp_cp; b.) loading selected column from from source to destination. u can create a table emp_cp_test with three column id,name and zip and after creating use below command for loading the data. hive> insert overwrite table emp_cp_test select id,name,zip from emp; hive> select * from emp_cp_test; c.) even we can use where clause for loading the data from source table to destination table. hive> insert overwrite table emp_cp_test select id,name,zip from emp where role = 'Developer' ; II.) Insert Many :=> Load the data from different target tables. Example :=> if we have two target table first is dev_table and test_table. and we need to first filter the data and load it to respective dev and test table. hive> create table dev_table(id int, name string, role string, zip int, code int , rank int , keyword string) stored as textfile; OK Time taken: 0.357 seconds hive> create table test_manager(id int, name string, role string, zip int, code int , rank int , keyword string) stored as textfile; OK Time taken: 0.769 seconds hive> Loading Command :=> hive> from emp insert into table dev_table select * where role = 'Developer' insert into table test_manager select * where role = 'Mgr'; hive> select * from dev_table; OK dev_table.id dev_table.name dev_table.role dev_table.zip dev_table.code dev_table.rank dev_table.keyword 1581 Richard Developer 1000 1681 40 LKJ 1781 John Developer 6500 1681 10 IXZ Time taken: 0.264 seconds, Fetched: 2 row(s) hive> select * from test_manager; OK test_manager.id test_manager.name test_manager.role test_manager.zip test_manager.code test_manager.rank test_manager.keyword 1481 flink Mgr 9580 1681 10 IXZ 1681 Mira Mgr 5098 1481 10 IKZ Time taken: 0.278 seconds, Fetched: 2 row(s) hive>