Create external with Partition

问题:

I have data in hadoop and created a external table using partitions (date and hour). The table creation is fine but when i try to query the data i m not getting any 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

Am i missing anything here. Please help.

回答1:

Yes. You need to inform Hive about the new partition. The command to use is ALTER TABLE ... ADD PARTITION.

回答2:

External Table creation via HIVE JDBC isnt reflected in the hive datawarehouse.

After creating the table via 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 try retrieving from this file, nothing is returned...

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

It would be really helpful if someone can guide me on the above..

Thanks!