CODEWITHSUNDEEP
hive
Alejandro Sánchez Muñoz
Alejandro Sánchez Muñoz

Reputation: 77

How to create table over partitioned data

I have text file with snappy compression partitioned by field 'process_time' (result of Flume job). Example: hdfs://data/mytable/process_time=25-04-2019

This is my script for create table:

CREATE EXTERNAL TABLE mytable
(
... 
) 
PARTITIONED BY (process_time STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
STORED AS TEXTFILE 
LOCATION '/data/mytable/'
TBLPROPERTIES("textfile.compress"="snappy");

The result of queries against this table are allways 0 (but I know that there are some data). Any help?

Thanks!

Upvotes: 0

Views: 59

Answers (1)

notNull
notNull

Reputation: 31470

As you are creating external table on top of HDFS directory then to add the partitions to the hive table we need to run either of these commands.

  • if any partition added to HDFS directly(instead of using insert queries) then hive doesn't know about the newly added partitions, so we need to run either msck (or) add partitions to add newly added partitions to hive table.

To add all partitions to hive table:

hive> msck repair table <db_name>.<table_name>;

(or)

To manually add each partition to hive table:

hive> alter table <db_name>.<table_name> add partition(process_time="25-04-2019") 
      location '/data/mytable/process_time=25-04-2019';

For more details refer to this link.

Upvotes: 1

Related Questions