Reflection Recommendations Enterprise

There are two different methods you can use to obtain recommendations for reflections to create to accelerate queries:

• Run the SYS.RECOMMEND_REFLECTIONS table function
• Send a request to the Recommendations API

Running the SYS.RECOMMEND_REFLECTIONS Table Function

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 Name	Data Type	Description
view_sql	varchar	SQL 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_sql	varchar	SQL 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 might not contain an individual row that corresponds to each job ID listed in the SYS.RECOMMEND_REFLECTIONS query.

More than one recommendation might be created in response to a single job ID.

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_sql

   reflection_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 can contain only inner joins. Outer joins that are part of a view definition are also supported. Other types of joins are not supported.
• The initial SQL query cannot contain Window functions.

Sending Requests to the Recommendations API

You can submit the IDs of jobs that have run SQL queries, and receive recommendations for one or more aggregation reflections to create for accelerating the execution of the same SQL queries in the future.

Each response includes one or more recommendations. Each recommendation is comprised of the parameters to use in a request to create a view on which to define the recommended aggregation reflection, and the parameters to use in a request to create the aggregation reflection.

You can use the Catalog API to request the creation of a recommended view. And then, you can use the Reflection API to request the creation of the corresponding recommended reflection.

Example request

curl -X POST 'https://{hostname}/api/v3/reflection/recommendations' \
--header 'Authorization: _dremio{tokenstring}' \
--header 'Content-Type: application/json' \
--data-raw
'{
	"jobIds": [
		"1b1874ce-b241-2475-63c0-db22e707f100",
		"1b1874ce-b241-2475-63c0-dbdsfssdfs00"
    ]
}'

You can obtain the job IDs either by using the SQL API or by looking them up on the Jobs page.

The response contains one or more recommendations. Each recommendation has four parts:

• viewRequestBody: The fields that you can include in a request to the Catalog API for creating the view on which to define the recommended aggregation reflection.
• viewRequestEndpoint: The endpoint to use when submitting the request to the Catalog API.
• reflectionRequestBody: The fields that you can include in a request to the Reflection API for creating the recommended aggregation reflection.
• reflectionRequestEndpoint: The endpoint to use when submitting the request to the Reflection API.

Example response

{
    "data": [
        {
        "viewRequestBody": {
            "entityType": "dataset",
            "path": [
                "recommended_view",
                "Dataset_be919a56-f18b-421b-9612-711a1cc51b69"
            ],
            "type": "VIRTUAL_DATASET",
            "sql": "SELECT * FROM Samples.samples.dremio.com.\"NYC-taxi-trips\" WHERE trip_distance_mi <= 2.0 ORDER BY trip_distance_mi ASC",
        }
        "viewRequestEndpoint": "POST {hostname}/api/v3/catalog",
        "reflectionRequestBody": {
            "type": "AGGREGATION",
            "name": "agg_250e70d1-5e2a-4938-a1a1-95f664085099",
            "datasetId": "be919a56-f18b-421b-9612-711a1cc51b69",
            "enabled": true,
            "dimensionFields": [
                {
                    "name": "passenger_count",
                    "granularity": "DATE"
                }
            ],
            "measureFields": [
                {
                    "name": "fare_amount",
                    "measureTypeList": [
                        "SUM",
                        "COUNT"
                    ]
                }
            ],
            "entityType": "reflection"
        }
        "reflectionRequestEndpoint": "POST {hostname}/api/v3/reflection"
        }
    ],
    "canAlterReflections": true  
}

For more information about the Recommendations API, see Recommendations in the API reference.