Create reflection using SQL

1

Hello friends we want create a reflection using SQL but following the documentation we cannot create “AVG” measure that using UI can create,

Unexpected token '"AVG"'. Expecting {COUNT, MAX, MIN, SUM, APPROXIMATE, APPROX}.

Also how can set granularity (type date) in a dimension?

ALTER TABLE "lake2.prod".posts CREATE AGGREGATE REFLECTION lake_prod_posts_all_scan
    USING DIMENSIONS (
    ID_PAGE,
...
    AGE,
    CREATED_DATE, --how set granularity (type date)???
    
    )
    MEASURES(
    ID (COUNT),
     ....
    USERNAME_COMMENTS (APPROX COUNT DISTINCT, COUNT),
    COMMENTS (COUNT, SUM)
    ) partition by (bucket( 400, ID_PAGE ), TYPE, month (CREATED_DATE))
2

@dacopan You just have to enable COUNT and SUM, and AVG queries will get accelerated.

For your second question, you can add BY DAY. Something like this:

ALTER TABLE Samples."samples.dremio.com"."NYC-Taxi-Trips"
  CREATE AGGREGATE REFLECTION passenger_avg USING
    DIMENSIONS (pickup_datetime BY DAY ) MEASURES (passenger_count (COUNT, SUM))
2 Likes
3

Thanks for the response, this would be very helpful if it is clarified in the documentation

4

@dacopan thanks!
we will clarify it in Dremio documentation.