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;

[#7]

@mohammad_shamim For copying ACID tables from one cluster to another you can use import and export commands .Below are the steps in detail

1)On source cluster create new table using create-table-as-select on source acid table.

create table src_table as select * from src_acid_table;

2)Then run export on src_table.

export table src_table to '/data/src_table_export'

3)Copy the directory /data/src_table_export to destination cluster

4)Run import on destination cluster as below once data is copied.

import table dst_table from '/data/src_table_export'

5)Create new acid table same as source and then insert the data as below:

insert into table dst_acid_table select * from dst_table;

Verify that data on both source and target table were consistent by taking count of rows.