hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Naveen Gangam (Jira)" <j...@apache.org>
Subject [jira] [Updated] (HIVE-24086) CTAS with HMS translation enabled returns empty results.
Date Thu, 27 Aug 2020 19:44:00 GMT

     [ 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)

Mime
View raw message