Create external using section

Question

I have data in hadoop and created an external table using partitions (date and time). Creating a table is fine, but when I try to query the data, I get no results.

Hadoop file path → /test/dt=2012-01-30/hr=17/testdata*

Create statement →

 CREATE EXTERNAL TABLE test(adate STRING, remoteIp STRING, url STRING, type STRING, ip STRING, useragent STRING) COMMENT 'This is the Test view table' PARTITIONED BY(dt STRING, hr STRING) ROW FORMAT SERDE 'com.test.serde.ValidRawDataSerDe' STORED AS SEQUENCEFILE LOCATION '/test'; 

Table creation message →

 OK Time taken: 0.078 seconds 

When I use select query i'm not getting results ->

 hive> select * from test; OK Time taken: 0.052 seconds hive> select * from test where dt='2008-08-09' and hr='17'; OK 

I see nothing here. Please, help.

Answer 1

Yes. You need to inform Hive about the new section. The ALTER TABLE ... ADD PARTITION command used.

Answer 2

Creating an external table through HIVE JDBC is not reflected in the hive data warehouse.

After creating the table through Hive JDBC, stmt.executeQuery("create external table trial5 (TOPIC STRING) row format delimited fields terminated by '' STORED as TEXTFILE LOCATION '/user/ranjitha/trial5'");,

and I'm trying to extract from this file, nothing is returned ...

Here in this link: https://groups.google.com/a/cloudera.org/forum/?fromgroups#!topic/cdh-user/YTekdFtbelE , he says that creating an external table is not possible using HIVE JDBC ..

It would be very helpful if someone could direct me to the above.

Thanks!