Hive Table copy from Prod to QA .

[#1]

I have copied hive table from Prod to QA by using get and put command but while executing select counnt (*) is it showing 0 count but i can see physical location is having data however i have checked all permission are in place so i did invalidate metadata and table refresh and msck repiare but still facing same issue. can seomeone help.

Regards

Saim

[#2]

Thanks for asking your question @mohammad_shamim. While you await someone more technical to reply, may I suggest providing a little more detail into the steps taken, table information (format, external?, etc) and the permissions in place that led to the issue? 

[#3]

Hello @mohammad_shamim 

Thanks for sharing your question. Before provide an answer, can you please confirm if this is a MANAGED or EXTERNAL table? 

Based on this info I will let you know all the details or next steps to see if is possible to show the data in the table.

[#4]

Managed.. I have shared table structure in the same chain.

[#5]

can someone share the exact steps 

[#6]

@mohammad_shamim  You cannot copy a managed(ACID) table using HDFS GET/PUT commands because there are writeIDs associated with ACID tables, and if that information is missing in HMS, you will not be able to read the data files.

Here is the supported way to copy/move a managed table:

1. Create an external table first, on top of the new HDFS path:

CREATE EXTERNAL TABLE ext_source_table (
    col1    INT,
    col2    STRING,
    col3    DOUBLE,
    col4    DATE
)
STORED AS ORC
LOCATION '[HDFS PATH]';

2. PERFORM MSCK REAPAIR on the External table and see if you can read it.

MSCK REPAIR ext_source_table;

3. Use CREATE TABLE AS SELECT command to create teh target Managed ACID table from that external table.

e.g

CREATE TABLE target_managed_table
AS
SELECT * FROM ext_source_table;