Temporary Table RDBMS in Dremio

This is my day 1 in Dremio. My background is mostly working in traditional RDBMS. I did complete Dremio Fundamental training today. I have a few quick answers to find out so looking for help. I have VDS in my Dremio database.

  1. Is there a temporary table or #table in Dremio. For example, in sql databases we can create
    SELECT C1, C2 INTO #TEMP_TBL FROM MYTABLE
    SELECT * FROM #TEMP_TBL
    How can I do the same in Dremio

  2. How can write SQL block of Dremio? For example, in sql databases we can write
    Declare
    @today date
    Begin
    set @today = sysdate()
    End

Basically is there any document which talks about RDBMS to Dremio transition?

Thanks - Ashok

@ashokojha

Dremio does not write back anything to RDBMS. The only place Dremio can write back is on to a data lake like S3 in Parquet file format. Currently you can write an equivalent of PL/SQL o SQL block in Dremio

In other words you can query SQL server via Dremio and also create reflections so the data is written back in Parquet

Thanks
Bali

Hi Bali,

Thanks for your reply.

If I understand correctly the rdbms type Temp table alternate I have in Dremio to write to “S3 in Parquet format” and I should explore - reflections.

About your note “Currently you can write an equivalent of PL/SQL o SQL block in Dremio” - can you please point me to some sample code or case study or examples.

Thanks - Ashok

@ashokojha

Sorry, that was a typo, we currently cannot

Thanks
Bali

Hi there, so… it’s not possible yet to create an Temporary table on Dremio?

For example: select * into #temp from table
or create table #temp as select * from table

what is our options? thanks

@ericathm Dremio does support CTAS but those are permanent tables and will not be dropped on restart, you have to manually drop them using the DROP TABLE command, but certainly can be used like temp tables

You can CTAS either as PARQUET or ICEBERG