[
https://issues.apache.org/jira/browse/HIVE-24086?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Naveen Gangam updated HIVE-24086:
---------------------------------
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.
was:
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.
> 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)
