Search code examples
hivehiveqlhiveddl

Hive temporary table auto deletion

During practice i have created tmp table using following query inside hive prompt.

$create temporary table tmp (id int);

Now table is getting successfully created and if i close the hive session table will be deleted automatically by hive which is true according to documentation.

Now, there are other ways to run same query by using following command.

$hive -e 'create temporary table tmp (id int);'

table is getting created successfully but my doubt is this time, why tmp table will not get auto deleted this time. i can still see the tmp table after executing next following command.

$hive -e 'show tables;'
OK
customers
order
product
tmp
Time taken: 0.856 seconds, Fetch: 4 row(s)
Solution

  • Most probably you have already the table with the same name but not temporary.

    Steps for reproduce:

    I have checked that there is no such table (not temporary) already exists.

    hive -e 'use myschema; show tables "tmp";'
--no rows returned

    Then I ran your example:

    $hive -e 'use myschema; create temporary table tmp (id int);'
OK

    Check there is no table:

    hive -e 'use myschema; show tables "tmp";'
--no rows returned - it works correctly

    Create not temporary table

    hive -e 'use myschema; create table tmp (id int);'
--ok

    Now there is persistent table:

    hive -e 'use myschema; show tables "tmp";'
OK
tmp
Time taken: 0.081 seconds, Fetched: 1 row(s)

    Try to create the same temporary table:

    hive -e 'use myschema; create temporary table tmp (id int);'
OK

    Persistent table remains in the schema. Temporary table was dropped successfully and temporary table was isolated inside session, not visible to other sessions.