[Hive] Internal & External Table

---

Internal & External Table

• Internal Table
• External Table

---

Internal Table

• Hive is the sole owner of the table’s data & metadata
  • only Hive can access the data
• when the table is dropped, the metadata & data will be lost
• Table Creation

-- default: internal table
create table if not exists table3(
	col1 int,
	col2 array<string>,
	col3 string,
	col4 int
)
row format delimited fields terminated by',' -- 구분자
collection items terminated by':' -- array내 구분자
lines terminated by'\n' -- 줄바꿈 구분자
stored as textfile
location '/user/zsu/table3'; -- path of table data in  HDFS

/*
cf.
default row format delimited fields terminated by: Ctrl+A
default lines terminated by: '\n';
*/

---

External Table

• Hive is responsible only for the table’s metadata
• when the table is dropped, the metadata will be lost, but the data still lies in the HDFS
  • since the metadata is lost even though the data still lies in the HDFS, hive does not have linkage to that data anymore
• Table Creation

create external table if not exists table2(
	col1 int,
	col2 array<string>,
	col3 string,
	col4 int
)
row format delimited fields terminated by',' 
collection items terminated by':' 
lines terminated by'\n' 
stored as textfile 
location'/user/zsu/emp';

---