Hive what location should I mention while creating table

Question

create external table zemp202_external (e_id int,e_name string,e_exp int)
row format delimited fields terminated by ' '
location '/user/hive/warehouse/zemp202_external';

the above location should be warehouse path or it can be local/hdfs path?(because I tried with hdfs path it's not working) ??

score 0 · Answer 1 · May 23, 2019

It can be a hdfs path. The hive external table is created and the location is given of hdfs so that the data gets loaded into the table through the file present in the hdfs and we won't have to execute the load command to load the data in our table.

Go through the below example for creating the hive external table,

When you create an external table, you can give the path of the file from which you want to add the data in the table.

Suppose, your file is in the directory named flag in hdfs. Now, to load the data from this file you will have to execute the below query

create external table country (name string, landmass string,
zone string, area int)row format delimited fields terminated by ','
stored as textfile location "/user/edureka_398276/flag";

The above command will get the data from the file stored in the directory in hdfs named flag and store the data in the table named a country. This way we don't have to execute the load data command to load the data in our table.

The syntax for the above command is as below:

create external table <table_name> (<column_name> <data_type>, <column_name> <data_type>, ...) 
row format delimited fields terminated by '<field_delimiter>' 
stored as <file_type> location <hdfs_path>