My table "test_table2" holds around 12mil records and has 6 columns with small records (short strings, dates, etc.). The size of the table is cca 1 GB. When I'm running this query:
query = """create table db.table1 as ( select id, string1, date1, gpslon, gpslat, timestamp from db.test_table2 )"""
from jupyter notebook with python, I can see in the Storage Management tools (macos), that the free space is lowered by 1 GB and after the creation of the "table1", the free space raises back again (cut by the size of the new table).
The size of the "table1" is cca 630 MB.
How this all works? Does the whole "test_table2" need to be stored temporarily when creating another table as select from this table? Can this be managed somehow? Because when I was trying to create a table as select from a bigger one than 1 GB, the process consumed all free space and everything failed consequently.
Thanks for any help or explanation!