When operating TiDB with Presto, a large number of tables prefixed with tmp_presto_ are generated. What is the situation?

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: Presto操作TiDB时会产生大量的tmp_presto_开头的表,是什么情况。

| username: 超7成网友

As mentioned, when accessing TiDB with Presto, you will see a large number of tables starting with tmp_presto when you run show tables;.

tmp_presto_0675456ee0a94c9a9f9e5a0c31e7233a
tmp_presto_0679c51bc4674ee3929b10c1d8157ef4
tmp_presto_067b044189cd4699a8a0ed4abdd598cb
tmp_presto_068979aa3f7f48f1a54d68485f7e3c61
tmp_presto_068d6e74801a4c4197e8c8d90c8b986e
tmp_presto_068e8034dc6a4d849320427fead328bb
tmp_presto_06b1c73d62f648fc950e1a6a0e8fae0f
tmp_presto_06b6a09a115b4b1bb2e998b29ef0663c
| username: TiDBer_小阿飞 | Original post link

Presto adopts a three-layer table structure:

  1. Catalog corresponds to a certain type of data source, such as Hive data or MySQL data.
  2. Schema corresponds to the database in MySQL.
  3. Table corresponds to the table in MySQL.

When accessing TiDB, it is estimated that temporary tables will be generated.

| username: Fly-bird | Original post link

Temporary table files

| username: 超7成网友 | Original post link

Can these be cleaned up? Can’t it clean up automatically?

| username: ti-tiger | Original post link

Tables starting with tmp_presto are temporary tables created by Presto during query execution to store intermediate results. These tables are usually automatically deleted after the query ends, but sometimes they may remain in TiDB due to reasons such as:

  • An exception or error occurred during query execution.
  • Insufficient permissions to delete the temporary table or encountering network or storage issues, causing the operation to fail or timeout.

These tables do not affect normal queries but may occupy some space. They can be manually deleted.

| username: 大飞哥online | Original post link

Temporary tables store intermediate result set data and should be automatically deleted once the session is completed.