4

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.

Balaji
  • 859
  • 1
  • 16
  • 27

2 Answers2

5

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

Olaf
  • 6,249
  • 1
  • 19
  • 37
  • Thanks Olaf. Can you give me a example for the above statement how to do that. – Balaji Jan 31 '12 at 21:32
  • I got the query Olaf. Is there a way to overcome the "ALTER TABLE ADD PARTITION". Because i need to summarize hourly and 4 hours data. I dont want to alter the table with add partition all the time :-( – Balaji Jan 31 '12 at 21:48
  • @Balaji: Did I mention that this can/should be done programmatically, for example via Hive JDBC? – Olaf Jan 31 '12 at 22:27
1

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!

Artur Udod
  • 4,465
  • 1
  • 29
  • 58
arian
  • 11
  • 1
  • Can you try performing two operations using JDBC - 1) Create simple table (NO External) 2) Load data from local inpath. https://cwiki.apache.org/Hive/languagemanual-dml.html#LanguageManualDML-Loadingfilesintotables – Balaji Oct 17 '12 at 18:46