[HIVE-24086] CTAS with HMS translation enabled returns empty results.

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Fix Version/s: 4.0.0-alpha-2
• Component/s: Hive
• Assignee: Naveen Gangam
• Reporter: Naveen Gangam
• Created: 27/Aug/20 19:37
• Updated: 16/Nov/22 13:50
• Resolved: 23/Feb/21 20:30

Description

when you execute something like
create table ctas_table as select * from mgd_table;

if mgd_table is a managed table, the hive query planner creates a plan with ctas_table as a managed table, so the location is set to something in the managed warehouse directory.

However with HMS translation enabled, non-acid MANAGED tables are converted to EXTERNAL with purge set to true. So the table location for this table is altered to be in the external warehouse directory.
But after the table creation, the rest of the query executes but the data is copied to the location set in the query plan. As a result when you execute a select from ctas_table, it will not return any results because that location is empty.

Workarounds:
1) if hive.create.as.acid=true, or hive.create.as.insert.only=true, this ctas_table remains a MANAGED acid table and HMS does not translate the table to an EXTERNAL table.
2) Instead of a create table as select, use
a) Create EXTERNAL table ctas_table as select * from mgd_table to make the target table an external table
OR
b) Create transactional table ctas_table as select * from mgd_table to make the target table an managed acid table.

Issue Links

• duplicates: HIVE-24625 CTAS with TBLPROPERTIES ('transactional'='false') loads data into incorrect directory