Skip to main content
Version: current [24.2.x]

Reflection Recommendations Enterprise

The SYS.RECOMMEND_REFLECTIONS table function recommends aggregation reflections to accelerate existing SQL queries.

Dremio automatically consolidates similar SQL queries into groups when possible. The output is a table that lists a pair of SQL commands for each group: one for creating a view and one for adding the recommended aggregation reflection to the created view.

Syntax​

Syntax for the SYS.RECOMMEND_REFLECTIONS table function
SELECT * FROM TABLE(SYS.RECOMMEND_REFLECTIONS(ARRAY['<jobId>', '<jobId>']))

The SYS.RECOMMEND_REFLECTIONS function's argument must be an array literal.

Parameters​

The SYS.RECOMMEND_REFLECTIONS table function has one parameter: an array that contains the job IDs for the SQL queries that you want to accelerate.

You can list up to 10 job IDs in each SYS.RECOMMEND_REFLECTIONS query. You must have permission to view every job you list in the query.

Output​

The output for SYS.RECOMMEND_REFLECTIONS queries is a table that includes the following two columns:

Column NameData TypeDescription
view_sqlvarcharSQL command that creates a view based on the initial SQL query in the referenced job ID. Dremio uses this view to create an aggregation reflection when you run the SQL command from the reflection_sql column.
reflection_sqlvarcharSQL command that alters and adds an aggregation reflection to the view that is created when you run the SQL command in the view_sql column.

The SQL commands in the output table assume that you have already created a Nessie catalog or space named recommended_view. You must create this catalog or space before running the SQL commands in the output table.

Dremio automatically consolidates recommendations when possible to create a single recommendation for similar SQL queries, so the output table may not contain an individual row that corresponds to each job ID listed in the SYS.RECOMMEND_REFLECTIONS query.

Example Query and Output​

To request a reflection recommendation, you must have the job ID for an initial SQL query that you want to accelerate. For example, suppose this is the initial SQL query:

Initial SQL Query
SELECT Max("p_size")
FROM s3.bucket1.tpch.sf10.parquet.part
JOIN s3.bucket1.tpch.sf10.parquet.lineitem
ON "p_partkey" = "l_partkey"
GROUP BY "p_brand"

To retrieve and use reflection recommendations:

  1. Retrieve the job ID for the initial SQL query.

  2. Run the SYS.RECOMMEND_REFLECTIONS query with the desired job ID. In this example, the job ID is 844c0023-6272-8b16-aef3-aea289acadb1:

    Example SYS.RECOMMEND_REFLECTIONS Query
    SELECT * FROM TABLE(SYS.RECOMMEND_REFLECTIONS(ARRAY['844c0023-6272-8b16-aef3-aea289acadb1']))

    The output is a table that lists two SQL commands for each job ID in the SYS.RECOMMEND_REFLECTIONS query:

    view_sqlreflection_sql
    CREATE VIEW "recommended_view"."Dataset_9d74a03b-747a-42a2-a5ca-7f9c6f77b55d" AS SELECT "part"."P_BRAND" AS "F0[P_BRAND]", "part"."P_SIZE" AS "F1[P_SIZE]", "part"."P_PARTKEY" AS "extra#0", "part"."P_NAME" AS "extra#1", "part"."P_MFGR" AS "extra#2", "part"."P_TYPE" AS "extra#4", "part"."P_CONTAINER" AS "extra#6", "part"."P_RETAILPRICE" AS "extra#7", "part"."P_COMMENT" AS "extra#8", "lineitem"."L_ORDERKEY" AS "extra#9", "lineitem"."L_PARTKEY" AS "extra#10", "lineitem"."L_SUPPKEY" AS "extra#11", "lineitem"."L_LINENUMBER" AS "extra#12", "lineitem"."L_QUANTITY" AS "extra#13", "lineitem"."L_EXTENDEDPRICE" AS "extra#14", "lineitem"."L_DISCOUNT" AS "extra#15", "lineitem"."L_TAX" AS "extra#16", "lineitem"."L_RETURNFLAG" AS "extra#17", "lineitem"."L_LINESTATUS" AS "extra#18", "lineitem"."L_SHIPDATE" AS "extra#19", "lineitem"."L_COMMITDATE" AS "extra#20", "lineitem"."L_RECEIPTDATE" AS "extra#21", "lineitem"."L_SHIPINSTRUCT" AS "extra#22", "lineitem"."L_SHIPMODE" AS "extra#23", "lineitem"."L_COMMENT" AS "extra#24"
    FROM "s3"."bucket1"."tpch"."sf10"."parquet"."lineitem"
    INNER JOIN "s3"."bucket1"."tpch"."sf10"."parquet"."part" ON "part"."P_PARTKEY" = "lineitem"."L_PARTKEY"    		ALTER DATASET "recommended_view"."Dataset_9d74a03b-747a-42a2-a5ca-7f9c6f77b55d" CREATE AGGREGATE REFLECTION "agg_0e07a376-7f8e-4c68-b2ce-6f6e819bebe6" USING DIMENSIONS ("F0[P_BRAND]") MEASURES ("F1[P_SIZE]" (MAX))

  3. Create a Nessie catalog or space named recommended_view.

  4. Run the query listed in the view_sql column to create a view based on the initial SQL query.

  5. Run the query listed in the reflection_sql column to alter the view by adding the recommended reflection and wait for the reflection to finish refreshing.

  6. When the reflection refresh is complete, run the initial SQL query to observe the acceleration due to the added reflection.

Limitations​

The SYS.RECOMMEND_REFLECTIONS table function has the following limitations:

  • The function recommends only aggregation reflections, not raw reflections.
  • The function cannot provide recommendations for jobs with multiple nested queries that contain joins.
  • The initial SQL query must contain a GROUP BY expression above the join.
  • The initial SQL query can contain only inner joins. Other types of joins are not supported.
  • The initial SQL query cannot contain Window functions or set operators (UNION, INTERSECT, MINUS, and EXCEPT).