Dremio Cloud
Dremio Software
 
On this page

    Acceleration SQL Statements

    Reflections can be defined on an existing dataset using SQL commands.

    Raw Reflections

    Create raw reflections 
    ALTER DATASET <DATASET_PATH> 
CREATE RAW REFLECTION <REFLECTION_NAME> 
USING
DISPLAY (
field1,
field2,
field3
)
[PARTITION BY (field1)]
[LOCALSORT BY (field2)]
[DISTRIBUTE BY (field2)]

    Aggregate Reflections

    Create aggregate reflections 
    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...)

    When handling timestamps as dimensions, Dremio defaults to using the full granilarity of the provided field. In certain cases, users may opt to have less granularity. This can be configured to be at DAY level by declaring dimensions as follows dimension1 by day. This is also the UI default.

    External Reflections

    Create external reflections 
    ALTER DATASET <SOURCE_DATASET_PATH> 
CREATE EXTERNAL REFLECTION <REFLECTION_NAME> 
USING <TARGET_DATASET_PATH>

    In the example above, Dremio can leverage target_dataset in place of the dataset (source_dataset) that was altered.

    Dropping Reflections

    All reflection types can be dropped using:

    Drop reflections 
    ALTER DATASET <DATASET_PATH> DROP REFLECTION <REFLECTION_NAME>