Creating tables with CTAS in S3

I have an S3 data source and I am creating tables in it using the CTAS command.
However, the CTAS command does not directly create a physical table but a folder with a parquet file in it. I have to manually convert the folder to a physical table by using the button under the “Action” column.

I was wondering what is the purpose of CTAS creating a folder instead of a table. Also, is there a way for CTAS to create a physical table, directly, without the manual intervention?

Thanks for your help!



We currently support CTAS only on file system sources. What you can do is promote the folder back in Dremio and run queries against it as if it was a table


Thanks @balaji.ramaswamy

How can I run queries against the folder?
The following queries (against folder “Stage-Test”) result in errors

SELECT * FROM .“Stage-Test”
SELECT * FROM .“Stage-Test”.“0_0_0.parquet”

To follow-up on my previous question, since the CTAS only supports filesystem table creation then what is the role of the button under the “Action” column? I mean, what is happening when you change the folder with the parquet file into a “table”?


Hello @Theo,

That icon means “promote the file to a physical dataset” or, in other words, “add it to Dremio’s catalog so you can query it”.

When you create a new file or set of files with a CTAS SQL query, you will then have to promote the dataset. You should then be able to query it.

1 Like

@Theo - on the S3 Source, edit source and check this property “Automatically format files into physical datasets when users issue queries.” See screenshot.

Screen Shot 2020-08-14 at 6.07.00 AM