Setting table_type = 'EXTERNAL_TABLE' builds a 'MANAGED_TABLE'

[rotten]

Sorry to open one more...

When I create an external table it ends up being created as a managed table.

            my_table = TableBuilder(
                            table_name='my_table',
                            db_name=table['DatabaseName'],
                            storage_descriptor=storage_descriptor,
                            partition_keys=partition_keys,
                            parameters=parameters,
                            table_type='EXTERNAL_TABLE',
                            owner='root'
                            ).build()

However when I look in the metastore postgresql database for that table:

# select "TBL_TYPE" from "TBLS"  where "TBL_NAME" = 'my_table';
   TBL_TYPE
---------------
 MANAGED_TABLE
(1 row)

fwiw, create table WITH (external_location = xxx) works fine from the presto client and creates the EXTERNAL_TABLE type in the database.

I'm still looking for a root cause or work-around, but thought I'd log what I've run into while I'm looking. Your examples and tests don't include creating an external table.

[rotten]

Adding an explicit Table Parameter of {'EXTERNAL': 'TRUE'} to the table builder object seems to trick Hive (3.0.0) into creating the table as an EXTERNAL_TABLE instead of a MANAGED_TABLE. I think it completely ignores the table_type setting.

[rotten]

Unfortunately, although I can query a created in presto manually, I get 0 rows back from an identical table created (with a different name) using this client. So even though the "SHOW CREATE TABLE" command outputs are now identical, and the TBL_TYPE is now correct, there is something else not quite right - still investigating.

[rotten]

To follow up on the investigation into the 0 rows issue after the table was re-created. That one example I was testing appears to have had an underlying storage issue. Once that was resolved, the queries worked. I've gone back and recreated 75 of my tables now with the extra Table Parameter set, and that switched them all to EXTERNAL_TABLE when I did so and they started working.

[LucasMMota]

Hi @rotten thank you for opening this issue!
Actually, we had not realized that this problem was occurring in Hive Metastore Server. We have also faced some strange behaviors in the tables due to the EXTERNAL/MANAGED option thought.

As far as I know, after digging in the code and testing the possibilities this is a bug from Hive Metastore Server.
We will create a new method that encapsulates the behavior of adding the option {'EXTERNAL': 'TRUE'} so we can work around this issue for now.

[felipemiquelim]

We addressed the issue in the linked PRs and released version 1.0.4 with all due improvements 😄
Thank you again @rotten for bringing it up!