Add reflection using a script in dremio

Question

I'm trying to create, update and delete a reflection (Raw/Aggregate) using a script on dremio data lake. Is it possible to do it in the community edition?

Answer 1

Yes can do it in the community edition For RAW Reflection

Code template

 ALTER DATASET <DATASET_PATH> 
        CREATE RAW REFLECTION <REFLECTION_NAME> 
        USING
        DISPLAY (
        field1,
        field2,
        field3
        )

Example

ALTER DATASET "Dremio-SPACE"."VDS_name"
CREATE RAW REFLECTION temRef
USING
DISPLAY (
firstColumn

)

For Aggregate Reflections

Code template

ALTER TABLE <Source_PDS_OR_VDS_NAME> 
 CREATE AGGREGATE REFLECTION <reflection_name> 
 USING 
 DIMENSIONS (x by day, y) 
 MEASURES (b (COUNT, MIN, MAX, SUM, APPROXIMATE COUNT DISTINCT)) 
 --PARTITION BY (col1,col2...) 
 --LOCALSORT BY (col1,col2...)
 --ARROW CACHE
 --DISTRIBUTE BY (col1,col2...)

Example

ALTER TABLE <Source_PDS_OR_VDS_NAME> 
 CREATE AGGREGATE REFLECTION tempRef 
 USING 
 DIMENSIONS (x by day, y) 
 MEASURES (b (COUNT, MIN, MAX, SUM, APPROXIMATE COUNT DISTINCT))

Delete Reflection

Code template

ALTER DATASET <DATASET_PATH> DROP REFLECTION <REFLECTION_NAME>

Example

ALTER DATASET "Dremio-SPACE"."VDS_name" DROP REFLECTION temRef

Reference http://docs.dremio.com/sql-reference/sql-commands/acceleration/