Drop Hive external table WITHOUT removing data

21,858

Use only this statement (without alter table):

DROP TABLE external_hive_table;
Share:
21,858

Related videos on Youtube

Laurens Koppenol
Author by

Laurens Koppenol

Just hobbying around. SOreadytohelp

Updated on October 07, 2021

Comments

  • Laurens Koppenol
    Laurens Koppenol over 2 years

    The goal is to destroy a Hive schema but keep the data underneath.

    Given a Hive external table, created for example with script 1, it can be dropped with script 2. This deletes the data (removes the folder /user/me/data/). This folder has to remain for use in other projects.

    A long search does not yield anything so far...

    Script 1: Create an external table

    CREATE EXTERNAL TABLE external_hive_table(
        column1 STRING
    )
    ROW FORMAT DELIMITED
    FIELDS TERMINATED BY
        '\t'
    STORED AS TEXTFILE
    LOCATION
       '/user/me/data/'
    TBLPROPERTIES (
        "skip.header.line.count"="1");
    

    Script 2: Drop external table (drop data)

    ALTER TABLE
        external_hive_table
    SET TBLPROPERTIES (
        'EXTERNAL'='FALSE');
    
    DROP TABLE external_hive_table;
    

    Edit: Script 3: Drop external table (keep data)

     DROP TABLE external_hive_table;