Creating tables with CTAS in S3

[Theo]

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!

Theo

[balaji.ramaswamy]

@Theo

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
Bali

[Theo]

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”
or
SELECT * FROM .“Stage-Test”.“0_0_0.parquet”

[Theo]

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”?

[ben]

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.

[desi]

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