ANALYZE command not write data into hive metastore

[#1]

Good day,

In SIT, I have manually create a hive table. Follow by manually create a partition on it. Then I copy the bucket file from other table and put inside the partition. At this moment, if I run select statement, I getting 0 result, I believe this is normal because data haven't write to meta store.

Then I run the following commands:

ANALYZE TABLE raw_zone_sit.jxtemp PARTITION (dl_created_yr='2023', dl_created_mth='12') 
COMPUTE STATISTICS;

After I run this command, I able to select data from my table.

After that I apply the same way in production, but I am still getting empty result.

Currently I am trying to recover all the meta store in production, this is because 1 of the person accidently delete all the meta store in production. Thus now I am trying to recover on it.

For your information, parquet tables working fine. But I have some ORC tables also, now pending ORC table to resolve.

Please help.

[#2]

Yes, 5 steps mentioned above

[#3]

Hi @smruti , Yes, I have done it in production environment, its not working, I still unable to select from it. But its working in SIT.

[#4]

@AhXian 
1. Are you able to read data from the external table in Prod(step  4)?

2. In step 5, I meant for you to insert data into a new ACID table, which can be created using similar DDL as the original table.

3. After INSERT(step 5),  see if you could read the data from the new table.

Ideally it should work

[#5]

hi @smruti , I unable to read data from external table in step 4.
Thus, I cant use the insert data query to insert into a new ACID table in step 5.

[#6]

@AhXian I hope we have copied the bucket files correctly, and also ran MSCK REPAIR. Could you also run ANALYZE.. COMPUTE STATISTICS on the external table once. Make sure the DESCRIBE command on the external table reflects the number of data files correctly. 

Please share describe and  "hdfs dfs -ls -R <externa table location>" o/p