External Table Not Present At the Specified Location in Hive

[#1]

I created an External Table and gave the location as '/tmp'

Now the table has been created and i can see that using "Show Tables;" Command. But when I go to /tmp directory, I don't see my table. I tried searching but did not find the table anywhere in hdfs. Where is my external table data will be stored? Or is the file created only once I load the data into the table?

[#2 · Accepted solution]

@Deepak Girdher

You ended up giving whole /tmp as your table location. What do you see when you run select * from table;

[#3]

@Deepak Girdher run this

show create table tablename;

Example:

hive> create external table ns1 (name string) location '/tmp/';
OK
Time taken: 0.597 seconds
hive> show create table ns1;
OK
CREATE EXTERNAL TABLE `ns1`(
  `name` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://phdns01.cloud.hortonworks.com:8020/tmp'
TBLPROPERTIES (
  'COLUMN_STATS_ACCURATE'='false', 
  'numFiles'='0', 
  'numRows'='-1', 
  'rawDataSize'='-1', 
  'totalSize'='0', 
  'transient_lastDdlTime'='1455648183')
Time taken: 0.354 seconds, Fetched: 17 row(s)

[#4]

@Neeraj Sabharwal Thanks For the quick reply.

I got the solution to my problem.

1. I figured out that once the data is loaded into the External Table, Only then the file will appear in HDFS as create table just stores the metadata and data is not present for HDFS.

2. My root was not having write access to /tmp . When I tried to load the data I got the error. That is how it stuck my mind that there could be some permission issue. 🙂

Once I gave the path for which my root had the access i.e. /user/root in my case which i created and loaded the data into the table, I could see the file and its contents.

[#5]

@Deepak Girdher

1. I figured out that once the data is loaded into the External Table, Only then the file will appear in HDFS as create table just stores the metadata and data is not present for HDFS.

Incorrect : Data resides in HDFS. HCatalog stores the data

2. My root was not having write access to /tmp . When I tried to load the data I got the error. That is how it stuck my mind that there could be some permission issue. 🙂

You never point a table to /tmp ---> Permission issue is there because you are trying to read other users directries

Once I gave the path for which my root had the access i.e. /user/root in my case which i created and loaded the data into the table, I could see the file and its contents.

That's correct

[#6]

@Neeraj Sabharwal : I did not get the first point : "Incorrect: Data resides in HDFS. HCatalog stores the data"

Since my data is in local file system, And I have just created an external table in Hive(Still data is not loaded). At this moment data is in local file system and no file exists at the path which I have mentioned in Create table. As Hive does not know which data to load how will it store the data in HDFS? Little confused on this part.

[#7]

@Deepak Girdher That's correct...Data will show up in HDFS after loading into Hive table.

[#8]

@Deepak Girdher Now , answering your original question: You never point a table to a location like /tmp or /user or / anything ...You want to point table location to particular directory structure not landing/root of mounts

[#9]

@Neeraj Sabharwal. Got It.. Thanks a lot.

[#10]

I have created external table in hive. I am able to see the path when i use describe formatted table_name;

But when i look at HDFS, I am not able to see the table

Can any one please explain why this has happend ?

,

I created external table in hive. When i use describe formatted table_name; i am able to see the location where it has stored. But when i look at HDFS, i am not able to see external table.

Can any one please explain why it has happened like this ??