Hive query not reading partition field

Question

I created a partitioned Hive table using the following query

CREATE EXTERNAL TABLE `customer`(            
   `cid` string COMMENT '',              
   `member` string COMMENT '',           
   `account` string COMMENT '')
   PARTITIONED BY (update_period string)
 ROW FORMAT SERDE                                   
   'org.apache.hadoop.hive.serde2.avro.AvroSerDe'   
 STORED AS INPUTFORMAT                              
   'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'  
 OUTPUTFORMAT                                       
   'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat' 
 LOCATION                                           
   'hdfs://nameservice1/user/customer'          
 TBLPROPERTIES (                                    
   'avro.schema.url'='/user/schema/Customer.avsc')

I'm writing to the partitioned location using map reduce program. when I read the output files using avro tools it is showing the correct data in json format. But when I use hive query to display the data, nothing is displayed. If I don't use partition field during table creation then the values are displayed in hive. what could be the reason for this ? I specify the output location for the mapreduce program as "/user/customer/update_period=201811".

Do I need to add anything in the mapreduce program configuration to resolve this?

score 1 · Accepted Answer · answered Dec 03 '18 at 03:40

You need to run msck repair table once you have loaded a new partition in HDFS location.

Why we need to run msck Repair table statement everytime after each ingestion?

Hive stores a list of partitions for each table in its metastore. However new partitions are directly added to HDFS , the metastore (and hence Hive) will not be aware of these partitions unless the user runs either of below ways to add the newly add partitions.

1.Adding each partition to the table

hive> alter table <db_name>.<table_name> add partition(`date`='<date_value>')
 location '<hdfs_location_of the specific partition>';

(or)

2.Run metastore check with repair table option

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

which will add metadata about partitions to the Hive metastore for partitions for which such metadata doesn't already exist. In other words, it will add any partitions that exist on HDFS but not in metastore to the metastore.

Hive query not reading partition field

1 Answers1