Apache Hive External Tables


External tables

  • Data will be available in HDFS. The table is going to create on HDFS data.
  • We can call this one as schema on data.
  • At the time of dropping the table it drops only schema, the data will be still available in HDFS as before.
  • External tables provide an option to create multiple schemas for the data stored
  • in HDFS instead of deleting the data every time whenever schema updates

Ways of Creating External tables & loading data

1. Create external table and insert data using insert command
2. Create external table and load the data from Local file system
3. Create external table and load the data from HDFS

Create external table and insert data using insert command

hive> create ​EXTERNAL ​table employee_external(eid int,name string,salary
string,designation string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES
TERMINATED BY '\n' ​LOCATION ​'/user/training/employee_external';

hive> insert into employee_external values(111,'pavan','50000','manager');


Create external table and load the data from Local file system

hive> create EXTERNAL table employee_external(eid int,name string,salary
string,designation string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES
TERMINATED BY '\n' LOCATION '/user/training/employee_external';

hive> LOAD DATA LOCAL INPATH '/home/cloudera/training/hivedata/emp.txt' INTO
TABLE employee_external;

hive> LOAD DATA LOCAL INPATH '/home/cloudera/training/hivedata/emp.txt'
OVERWRITE ​INTO TABLE employee_external;

➔ If you use OVERWRITE in loading statement data will be override. Otherwise copy
of files will be created.
➔ If you drop the table , data file will not be removed from HDFS.

Create external table and load the data from HDFS

hive> create EXTERNAL table employee_external(eid int,name string,salary
string,designation string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES
TERMINATED BY '\n' LOCATION '/user/training/employee_external';

hive> LOAD DATA INPATH '/user/training/emp.txt' INTO TABLE employee_external;

Describing Table

hive> describe employee_external;
hive> describe ​extended ​employee_external;




Followers