External table does not return the data in its folder

Question

I have created an external table in Hive with at this location :

CREATE EXTERNAL TABLE tb 
(
...
) 
PARTITIONED BY (datehour INT)
ROW FORMAT SERDE 'com.cloudera.hive.serde.JSONSerDe'
LOCATION '/user/cloudera/data';

The data is present in the folder but when I query the table, it returns nothing. The table is structured in a way that it fits the data structure.

SELECT * FROM tb LIMIT 3;

Is there a kind of permission issue with Hive tables: do specific users have permissions to query some tables? Do you know some solutions or workarounds?

Answer 1

You have created your table as partitioned table base on column datehour, but you are putting your data in /user/cloudera/data. Hive will look for data in /user/cloudera/data/datehour=(some int value). Since it is an external table hive will not update the metastore. You need to run some alter statement to update that

So here are the steps for external tables with partition:

1.) In you external location /user/cloudera/data, create a directory datehour=0909201401

                                OR

Load data using: LOAD DATA [LOCAL] INPATH '/path/to/data/file' INTO TABLE partition(datehour=0909201401)

2.) After creating your table run a alter statement: ALTER TABLE ADD PARTITION (datehour=0909201401)

Hope it helps...!!!