Create Table NOT as iceberg

[Ken]

Hi team,

I have been using the OSS version of dremio for 1+ year, and I am loving it. It is easy to host and doing big data analytics is made easy.

However, I am always having trouble with the iceberg table format, especially with its size increase. I tried various ways to keep the size small (e.g. vaccuming + optimising the tables immediately after insertion), but it has always been painful.

Is there a way for dremio to create table NOT as iceberg, but as parquet or other file formats? For this format, I need dremio to support the normal DML operations (e.g. select, insert, update, merge).

Does dremio support this?

Thank you so much!

[balaji.ramaswamy]

@Ken PARQUET is immutable, ICEBERG supports DML. When you say size? Is it the actual table data or the metadata that is growing?

[rdkworld]

@balaji.ramaswamy related question, i have s3 added as a source for querying. How do I create iceberg table by inserting data into say iceberg folder in same s3 bucket. I dont have any catalog added

[balaji.ramaswamy]

@rdkworld You can just do CREATE TABLE S3_source_name.<folder_under>.<TABLE_NAME>

[Ken]

@balaji.ramaswamy

Using two iceberg tables as an illustration.

THe one in l0 is constantly getting new data via MERGE.

I perform vacuum (expire snapshot retain 1) and optimise after every MERGE.

However, it’s data size grows to 31GiB.

I ran this SQL to determine the file_size_in_bytes

select sum("record_count"), sum("file_size_in_bytes") from TABLE( table_files( 'Minio.finance.l0.XXXX' ) );

It says it is around 3.5GB (which is VERY DIFFERENT from the actual size of 31GiB).

========

However, after I recreate the table (and put it in the backup_2025_03_26)

create table Minio.finance.backup_2025_03_26."XXXXXX"

as (SELECT * FROM Minio.finance.l0."XXXXXX")

The file sized shrank to 3.3GiB. This is more inline with what the original statistics is saying.

Note that I have always expired all snapshots and it should not bloat to 31GiB.

I feel that I hit something similar to what is reported here (Iceberg file size on dremio - #13 by dacopan)

=======

In the original iceberg table (with regular MERGE of new data)

—> The data files folder are multiple. Those XLDIR directories were created after vacuum operation, where snapshots got deleted.

However, I think there are still dangling snapshots in the data files.

Meanwhile, in the recreated table (via CTAS), there’s just ONE data file folder

So I suppose that many data files folder in the original iceberg tables are actually useless (or else they would have been copied to the new iceberg table via CTAS)

[balaji.ramaswamy]

Thanks for the clear steps @Ken Let me see if someone can try and repro this

[rdkworld]

Thanks Balaji, it worked

[Ken]

@balaji.ramaswamy

In the below screenshot, it shows clearly that

In Minio storage, the file exists.

However, dremio cannot detect that.

Why cannot it delete this orphan data file?

I tried

VACUUM TABLE Minio.finance.l0.yahoo_stock_price_1m_history_2025_03 EXPIRE SNAPSHOTS RETAIN_LAST 1

but it is still there.

============

I manually deleted this data file using mcli and the result number of data counts remain the SAME!!!

minio@minio-prod:~$ mcli rm --dangerous --force -r  /mnt/data/finance/l0/yahoo_stock_price_1m_history_2025_03/1812f77a-4b1f-2a34-94b0-cfe8048cff00

So I confirm this is an orphan data file.

@balaji.ramaswamy Does Dremio support deleting orphan data files?

[Ken]

This screenshot shows this

• Left: It is the result of this query

select * from  table(table_files('Minio.finance.l0.yahoo_stock_price_1m_history_2025_03'))

It shows the data files actively tracked by Dremio

• Right. It is the list of data files in Minio using the Minio Client.

mcli du  /mnt/data/finance/l0/yahoo_stock_price_1m_history_2025_03/*

The two highlighted rows are the actual data files tracked by Dremio and the rest are orphan files.

For now, I think I will just write a python script to delete these files not tracked by dremio. Hopefully it will work.

[Ken]

@Ken

For now, I think I will just write a python script to delete these files not tracked by dremio. Hopefully it will work.

I confirm that this approach works!

In short, the approach is to delete data files not longer tracked by Dremio, but still remains in Minio.

Step 1. Query the data_files in Dremio

select "file_path" from table( table_files('XXXX'))

This list represents the list of data files tracked by Dremio.

Step 2. Use the list_objects function in Minio Client to list all the objects under the iceberg table folder

objects = self.client.list_objects(
            bucket_name,
            prefix=bucket_subpath,
            recursive=recursive
        )

Step 3: Perform reconciliation which matches each data file tracked by Dremio in Step 1 with the listed object in Step 2

Step 4: Except the Metadata files, delete all data files in Minio which are NOT tracked by Dremio. Use the remove_object method.

  self.client.remove_object(
            bucket_name=bucket_name,
            object_name= file_path,
        )

Validation

• File Size dropped

I have a superset dashboard which tracks the actual table size in Minio via du -sh command once every hour. The below graph shows that all table folders drop in size significantly after the above logic is applied.

Below is another graph which shows the available percentage of disk space in the whole Minio VM. It is obtained via df -h and by looking at the / mount.

• Row Count increases

Most importantly, row count for all tables did not drop.

Below is the growth in row count of 2 of the tables.