[
https://issues.apache.org/jira/browse/HIVE-24086?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17186076#comment-17186076
]
John Sherman commented on HIVE-24086:
-------------------------------------
I suspect HIVE-23968 is the same issue or very similar.
> CTAS with HMS translation enabled returns empty results.
> --------------------------------------------------------
>
> Key: HIVE-24086
> URL: https://issues.apache.org/jira/browse/HIVE-24086
> Project: Hive
> Issue Type: Bug
> Components: Hive
> Reporter: Naveen Gangam
> Assignee: Naveen Gangam
> Priority: Major
>
> 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.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
