Skip to main content

Reflections

A reflection is an optimized materialization of source data or a query, similar to a materialized view, that is derived from an existing table or view. For more information about reflections, see Accelerating Queries with Reflections.

Creating Raw Reflections​

Syntax 
ALTER DATASET <dataset_path> 
CREATE RAW REFLECTION <reflection_name> 
USING
DISPLAY (
  <field_name>,
  <field_name>,
  ...
)
[PARTITION BY (<field_name>, <field_name>, ...)]
[LOCALSORT BY (<field_name>, <field_name>, ...)]
[ARROW CACHE]

Parameters​

{{< sql-section file="data/sql/reflections.json" data="creatingRawReflectionsParameters" >}}

Example​

Create a raw reflection that sorts customers by last name and partitions them by country
ALTER DATASET "@user1"."customers"
CREATE RAW REFLECTION customers_by_country
USING
DISPLAY (
  id,
  lastName,
  firstName,
  address,
  country
)
PARTITION BY (country)
LOCALSORT BY (lastName)

Creating Aggregation Reflections​

Syntax 
ALTER TABLE <dataset_path> 
CREATE AGGREGATE REFLECTION <reflection_name> 
USING 
DIMENSIONS (<field_name>, <field_name>, ...) 
MEASURES (<field_name> (<aggregation_type), <field_name> (<aggregation_type), ...) 
[PARTITION BY (<field_name>, <field_name>, ...)]
[LOCALSORT BY (<field_name>, <field_name>, ...)]
[ARROW CACHE]

Parameters​

{{< sql-section file="data/sql/reflections.json" data="creatingAggReflectionsParameters" >}}

Example​

Create an aggregation reflection that counts the number of cities per state in which a company has a franchise and sorts the result by state
ALTER TABLE Samples."samples.dremio.com"."zips.json" 
CREATE AGGREGATE REFLECTION per_state 
USING 
DIMENSIONS (state) 
MEASURES (city (COUNT)) 
LOCALSORT BY (state)

Creating External Reflections​

Syntax 
ALTER DATASET <source_dataset_path> 
CREATE EXTERNAL REFLECTION <reflection_name> 
USING <target_dataset_path>

Parameters​

{{< sql-section file="data/sql/reflections.json" data="externalReflections" >}}