Recommendations
Use the Recommendations API to get job-based recommendations and get and create usage-based reflections that can accelerate your queries.
Getting job-based recommendations requires making the following two API requests:
- Submit the job IDs of jobs that have run SQL queries. These are job IDs of the queries for which you want to retrieve recommendations in further requests. This request returns the job ID to use in the second request.
- Retrieve job-based recommendations for reflections that can accelerate your queries. Use the job ID that was returned in your first request to make the request for recommendations.
Creating reflections from usage-based recommendations requires making the following two API requests:
- Retrieve usage-based recommendations for reflections. This request returns the parameters to use in the body of the second request.
- Create reflections from usage-based recommendations that can accelerate your queries from the usage-based recommendations. Use the recommendation ID and reflection request body that were returned in your first request to create the reflections.
Dremio supports creating only raw reflections from usage-based recommendations.
The POST /api/v3/reflection/recommendations endpoint is deprecated. In its place, use the job-based and usage-based endpoints described on this page to retrieve and refresh reflection recommendations.
{
"data": [
{
"viewRequestBody": {
"entityType": "dataset",
"type": "VIRTUAL_DATASET",
"path": [
"azure_3",
"table_2"
],
"sql": "--Default Raw Reflection"
},
"viewRequestEndpoint": "POST {hostname}/api/v3/catalog",
"reflectionRequestBody": {
"type": "RAW",
"name": "raw_47f54460-543f-430f-a9e5-ca71d246265e",
"datasetId": "45b9d98b-b0dc-4dd2-a271-d971ae998c0c",
"enabled": true,
"arrowCachingEnabled": false,
"dimensionFields": [],
"measureFields": [],
"displayFields": [
{
"name": "passenger_count"
},
{
"name": "EXPR$1"
}
],
"entityType": "reflection"
},
"reflectionRequestEndpoint": "POST {hostname}/api/v3/reflection",
"jobIds": [
"13ffb629-9f0e-4265-97df-99bf0d425813"
],
"jobCount": 1,
"recommendationId": "9be8a451-4190-4618-a72e-9932f790c744",
"reflectionScore": 50.67,
"avgImprovementFactor": 10.43,
"avgImprovementMs": 7196
}
],
"canAlterReflections": true
}
{
"data": [
{
"viewRequestBody": {
"entityType": "dataset",
"type": "VIRTUAL_DATASET",
"path": [
"recommended_view",
"view_1"
],
"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_0e0c4ab9-def7-48da-81f1-ca8c1da11ed4",
"datasetId": "2df93b5a-eb46-4687-8460-b61e471d20ef",
"enabled": true,
"arrowCachingEnabled": false,
"dimensionFields": [
{
"name": "passenger_count",
"granularity": "DATE"
}
],
"measureFields": [
{
"name": "fare_amount",
"measureTypeList": [
"SUM",
"COUNT"
]
}
],
"displayFields": [],
"entityType": "reflection"
},
"reflectionRequestEndpoint": "POST {hostname}/api/v3/reflection",
"jobIds": [
"1ded81f8-4d06-4d09-8163-9e2517027d8d"
],
"jobCount": 1,
"recommendationId": "1855d2dd-4106-4359-a97a-e08a916096e6",
"reflectionScore": 60.12,
"avgImprovementFactor": 8.39,
"avgImprovementMs": 5400
}
],
"canAlterReflections": true
}
Recommendation Attributes
data Array of Object
List of recommended reflection objects for the submitted job IDs.
canAlterReflections Boolean
If the columns in the recommended reflection can be edited, added, and removed, true. Otherwise, false.
Example: true
Attributes of Objects in the data Array
viewRequestBody Object
The fields to include in a request to the Catalog API to create the view on which to define the recommended reflection.
viewRequestEndpoint String
The endpoint to use when submitting a request to the Catalog API to create the view on which to define the recommended reflection.
reflectionRequestBody Object
The fields to include in a request to the Reflection API to create the recommended reflection.
reflectionRequestEndpoint String
The endpoint to use when submitting the request to the Reflection API to create the recommended reflection.
jobIds Array of String
The job IDs of the queries for which the reflection recommendations are given.
Example: ["13ffb629-9f0e-4265-97df-99bf0d425813"]
jobCount Array of String
The number of jobs for which reflection recommendations are given.
Example: 1
recommendationId Array of String
The ID of the recommended reflection.
Example: ["9be8a451-4190-4618-a72e-9932f790c744"]
reflectionScore Double
Score for the recommended reflection's quality, on a scale of 0 (worst) to 100 (best). The reflectionScore value considers the recommended reflection's anticipated quality compared to existing reflections and other recommended reflections, as well as the likely improvement in query run times if the recommended reflection is implemented.
Example: 50.67
avgImprovementFactor Double
The likely average multiplicative rate of improvement for each query if you implement the recommended reflection. For example, if the avgImprovementFactor value is 2.34, implementing the recommended reflection is likely to speed up each query by 2.34 times, on average.
Example: 10.43
avgImprovementMs Double
The likely average improvement, in milliseconds, for each query if you implement the recommended reflection. For example, if the avgImprovementMs value is 5400, implementing the recommended reflection is likely to save an average of 5400 milliseconds for each query that uses the reflection.
Example: 7196
Attributes of the viewRequestBody Object
entityType String
Type of catalog entity. For views, the entityType is dataset.
type String
Type of dataset. For views, the type is VIRTUAL_DATASET.
path Array of String
Path to the location where the view should be created within Dremio, expressed in an array. The path lists each level of hierarchy in order, from outer to inner: Arctic source or catalog first, then folder and subfolders, then a name for the view itself as the last item in the array. Views can only be created in Arctic sources and the project's Arctic catalog.
Example: ["azure_3","table_2"]
sql String
For aggregation reflections, the SQL query to use to create the view. For default raw reflections, the sql value --Default Raw Reflection; creating a view is unnecessary because raw recommendations are given only for existing views.
Attributes of the reflectionRequestBody Object
type String
Reflection type. For details, read Types of Reflections.
Enum: RAW, AGGREGATION
Example: AGGREGATION
name String
User-provided name for the reflection. For reflections created in the Dremio console, if the user did not provide a name, the default values are Raw Reflection and Aggregation Reflection (automatically assigned based on the reflection type).
Example: raw_47f54460-543f-430f-a9e5-ca71d246265e
datasetId String
Unique identifier for the anchor dataset to associate with the reflection.
Example: 45b9d98b-b0dc-4dd2-a271-d971ae998c0c
enabled Boolean
If the reflection is available for accelerating queries, true. Otherwise, false.
Example: true
arrowCachingEnabled Boolean
If Dremio converts data from the reflection's Parquet files to Apache Arrow format when copying that data to executor nodes, true. Otherwise, false.
Example: false
dimensionFields Array of Object
Information about the dimension fields from the anchor dataset used in the reflection. Dimension fields are the fields you expect to group by when analyzing data. Valid only for aggregation reflections. For raw reflections or if the anchor dataset does not include any dimension fields, the dimensionFields value is an empty array. For aggregation reflections, if the anchor dataset includes dimension fields, each object in the dimensionFields array contains two attributes: name and granularity.
Example: [{"name": "pickup_date","granularity": "DATE"},{"name": "pickup_datetime","granularity": "DATE"},{"name": "dropoff_date","granularity": "DATE"},{"name": "dropoff_datetime","granularity": "DATE"},{"name": "passenger_count","granularity": "DATE"},{"name": "total_amount","granularity": "DATE"}]
measureFields Array of Object
Information about the measure fields from the anchor dataset used in the reflection. Measure fields are the fields you expect to use for calculations when analyzing the data. Valid only for aggregation reflections. For raw reflections or if the anchor dataset does not include any measure fields, the measureFields value is an empty array. For aggregation reflections, if the anchor dataset includes measure fields, each object in the measureFields array contains two attributes: name and measureTypeList.
Example: [{"name": "passenger_count","measureTypeList": ["SUM,"COUNT"]},{"name": "trip_distance_mi","measureTypeList": ["SUM","COUNT"]},{"name": "fare_amount","measureTypeList": ["SUM","COUNT"]},{"name": "surcharge","measureTypeList": ["SUM","COUNT"]},{"name": "tip_amount","measureTypeList": ["SUM","COUNT"]},{"name": "total_amount","measureTypeList": ["SUM","COUNT"]}]
displayFields Array of Object
Information about the fields displayed from the anchor dataset. Valid only for raw reflections. For aggregation reflections or if the anchor dataset does not include any display fields, the value is an empty array. For raw reflections, if the anchor dataset includes display fields, each object in the displayFields array contains one attribute: name.
Example: [{"name": "passenger_count"},{"name": "EXPR$1"}]
entityType String
Type of entity. For reflection objects, the entityType is reflection.
Submitting Job IDs
Submit the job IDs of queries for which you want to request reflection recommendations.
The response includes objects that contain an id attribute and value for each job ID you submit. Use these id values to retrieve recommendations for reflections to accelerate the queries.
Method and URLPOST /api/v3/reflection/recommendations/job-based/
Parameters
jobIds Body Array of String
The job IDs of the queries for which you want to request reflection recommendations. To get the job IDs, use the SQL API or find them on the Jobs page in the Dremio console. Use a comma-separated list to submit multiple job IDs.
Example: ["a7efcd50-791a-48e8-bb05-391b4411e66b"]
Example Requestcurl -X POST 'https://{hostname}/api/v3/reflection/recommendations/job-based/' \
--header 'Authorization: Bearer <PersonalAccessToken>' \
--header 'Content-Type: application/json' \
--data-raw '{
"jobIds": ["a7efcd50-791a-48e8-bb05-391b4411e66b","c2485882-e6b7-4aa8-af5b-a825d2870589"]
}'
{
"id": "13ffb629-9f0e-4265-97df-99bf0d425813"
}
Response Status Codes
200 OK
400 Bad Request
401 Unauthorized
405 Method Not Allowed
500 Internal Server Error
Retrieving Job-Based Recommendations
Retrieve job-based recommended reflections to accelerate the queries whose job IDs you submitted.
- For default raw reflections, each recommendation comprises the path to the view on which to define the reflection and the parameters to use in a request to create the reflection.
- For aggregation reflections, each recommendation comprises the parameters to use in a request to create a view on which to define the recommended reflection and the parameters to use in a request to create the reflection.
After you retrieve the recommended reflections for your queries, use the Catalog API to create the recommended views. Then, use the Reflection API to create the desired reflections.
Before submitting Catalog API requests to create the recommended views for aggregation reflections, create a folder named recommended_view. In your Catalog API requests, the path parameter must include the full path to the recommended_view folder.
GET /api/v3/reflection/recommendations/job-based/{id}/results/
Parameters
id Path String
The id value returned in the response to your request to submit the job ID or IDs of the queries for which you want to retrieve recommended reflections.
Example: 13ffb629-9f0e-4265-97df-99bf0d425813
Example Requestcurl -X GET 'https://{hostname}/api/v3/reflection/recommendations/job-based/13ffb629-9f0e-4265-97df-99bf0d425813/results/' \
--header 'Authorization: Bearer <PersonalAccessToken>' \
--header 'Content-Type: application/json'
{
"data": [
{
"viewRequestBody": {
"entityType": "dataset",
"type": "VIRTUAL_DATASET",
"path": [
"azure_3",
"table_2"
],
"sql": "--Default Raw Reflection"
},
"viewRequestEndpoint": "{hostname}/api/v3/catalog",
"reflectionRequestBody": {
"type": "RAW",
"name": "raw_47f54460-543f-430f-a9e5-ca71d246265e",
"datasetId": "45b9d98b-b0dc-4dd2-a271-d971ae998c0c",
"enabled": true,
"arrowCachingEnabled": false,
"dimensionFields": [],
"measureFields": [],
"displayFields": [
{
"name": "passenger_count"
},
{
"name": "EXPR$1"
}
],
"entityType": "reflection"
},
"reflectionRequestEndpoint": "POST {hostname}/api/v3/reflection",
"jobIds": [
"13ffb629-9f0e-4265-97df-99bf0d425813"
],
"jobCount": 1,
"recommendationId": "9be8a451-4190-4618-a72e-9932f790c744",
"reflectionScore": 50.67,
"avgImprovementFactor": 10.43,
"avgImprovementMs": 7196
}
],
"canAlterReflections": true
}
Response Status Codes
200 OK
400 Bad Request
401 Unauthorized
405 Method Not Allowed
500 Internal Server Error
Retrieving Usage-Based Recommendations
Retrieve usage-based reflection recommendations. The response includes the reflectionRequestBody and recommendationId attributes to use as body parameters in your request to create usage-based reflections.
GET /api/v3/reflection/recommendations/usage-based/
curl -X GET 'https://{hostname}/api/v3/reflection/recommendations/usage-based/' \
--header 'Authorization: Bearer <PersonalAccessToken>' \
--header 'Content-Type: application/json'
{
"data": [
{
"viewRequestBody": {
"entityType": "dataset",
"type": "VIRTUAL_DATASET",
"path": [
"prodFolder",
"cost_based"
],
"sql": "--Default Raw Reflection"
},
"viewRequestEndpoint": "POST {hostname}/api/v3/catalog",
"reflectionRequestBody": {
"type": "RAW",
"name": "AutoRef_cost_based_raw",
"datasetId": "61d689a2-cd04-4d5d-84a7-021bdc15bff6",
"enabled": true,
"arrowCachingEnabled": false,
"dimensionFields": [],
"measureFields": [],
"displayFields": [
{
"name": "passenger_count"
},
{
"name": "pickup_datetime"
},
{
"name": "EXPR$2"
}
],
"entityType": "reflection"
},
"reflectionRequestEndpoint": "POST {hostname}/api/v3/reflection",
"jobIds": [
"1975ec43-349a-9310-2e40-acbd8d025c00",
"1975ac7c-6541-86db-ae43-dcef2ffee300",
"1974b533-8c88-946b-92ce-ee5ab7791500"
],
"jobCount": 3,
"recommendationId": "prodFolder.cost_based",
"reflectionScore": 36.928031592652964,
"avgImprovementFactor": 10.00000020692081,
"avgImprovementMs": 7393.800016999235
}
],
"canAlterReflections": true
}
Response Status Codes
200 OK
400 Bad Request
401 Unauthorized
500 Internal Server Error
Creating Reflections from Usage-Based Recommendations
Create reflections to accelerate queries using the usage-based recommendations that you retrieved.
You must retrieve usage-based recommendations to get the parameters you need for this request.
Dremio supports creating only raw reflections from usage-based recommendations.
POST /api/v3/reflection/recommendations/usage-based/
Parameters
reflection Body Object
Information about the usage-based reflection to create. The reflection object includes the contents of the reflectionRequestBody included in the response for requests to retrieve usage-based recommendations.
NOTE: If desired, you may change the name of the reflection by changing the value for the reflection.name parameter in the body of your request. Dremio ignores any changes to the values of other parameters in the reflection object.
recommendationId Body String
Identifier for the usage-based recommendation you want to use to create reflections. The recommendationId is included in the response for requests to retrieve usage-based recommendations.
Example: prodFolder.cost_based
Parameters of the reflection Object
type Body String
Reflection type. Value must be RAW.
name Body String
User-provided name for the reflection.
NOTE: If desired, you may change the name of the reflection by changing the value for the name parameter in the body of your request.
Example: AutoRef_cost_based_raw
datasetId Body String
Unique identifier for the anchor dataset to associate with the reflection.
Example: 61d689a2-cd04-4d5d-84a7-021bdc15bff6
enabled Body Boolean
If the reflection is available for accelerating queries, true. Otherwise, false.
Example: true
arrowCachingEnabled Body Boolean
If Dremio converts data from the reflection's Parquet files to Apache Arrow format when copying that data to executor nodes, true. Otherwise, false.
Example: false
dimensionFields Body Array of Object
Information about the dimension fields from the anchor dataset used in the reflection. For raw reflections, the dimensionFields value is an empty array.
Example: []
measureFields Body Array of Object
Information about the measure fields from the anchor dataset used in the reflection. For raw reflections, the measureFields value is an empty array.
Example: []
displayFields Body Array of Object
Information about the fields displayed from the anchor dataset. Valid only for raw reflections. If the anchor dataset includes display fields, each object in the displayFields array contains one attribute: name.
Example: [{"name": "passenger_count"},{"name": "EXPR$1"}]
entityType Body String
Type of entity. For reflection objects, the entityType is reflection.
curl -X POST 'https://{hostname}/api/v3/reflection/recommendations/usage-based/' \
--header 'Authorization: Bearer <PersonalAccessToken>' \
--header 'Content-Type: application/json' \
--data-raw '{
"reflection": {
"type": "RAW",
"name": "AutoRef_cost_based_raw",
"datasetId": "61d689a2-cd04-4d5d-84a7-021bdc15bff6",
"enabled": true,
"arrowCachingEnabled": false,
"dimensionFields": [],
"measureFields": [],
"displayFields": [
{
"name": "passenger_count"
},
{
"name": "pickup_datetime"
},
{
"name": "EXPR$2"
}
],
"entityType": "reflection"
},
"recommendationId": "prodFolder.cost_based"
}
{
"id": "c929b8d2-82bf-4175-9476-010ba17c4f7f",
"type": "RAW",
"name": "AutoRef_cost_based_raw",
"tag": "4p/COEkSud7=",
"createdAt": "2024-06-28T19:30:30.977Z",
"updatedAt": "2024-06-28T19:30:30.977Z",
"datasetId": "61d689a2-cd04-4d5d-84a7-021bdc15bff6",
"currentSizeBytes": 0,
"totalSizeBytes": 0,
"enabled": true,
"arrowCachingEnabled": false,
"status": {
"config": "OK",
"refresh": "SCHEDULED",
"availability": "NONE",
"combinedStatus": "CANNOT_ACCELERATE_SCHEDULED",
"failureCount": 0,
"lastDataFetch": "1969-12-31T23:59:59.999Z",
"expiresAt": "1969-12-31T23:59:59.999Z"
},
"displayFields": [
{
"name": "passenger_count"
},
{
"name": "pickup_datetime"
},
{
"name": "EXPR$2"
}
],
"partitionDistributionStrategy": "CONSOLIDATED",
"canView": true,
"canAlter": true,
"entityType": "reflection"
}
Response Status Codes
200 OK
400 Bad Request
401 Unauthorized
500 Internal Server Error
Refreshing Usage-Based Recommendations
Process collected data about view usage, clear existing usage-based recommendations, and generate new usage-based recommendations.
Use the usage-based endpoints to retrieve and create reflections based on the refreshed recommendations this endpoint creates.
Method and URLPOST /api/v3/reflection/recommendations/usage-based/refresh/
curl -X POST 'https://{hostname}/api/v3/reflection/recommendations/usage-based/refresh/' \
--header 'Authorization: Bearer <PersonalAccessToken>' \
--header 'Content-Type: application/json'
This endpoint returns an empty response body with a 202 Accepted response status code. Dremio updates the recommendations asynchronously, so it may take several minutes before you can retrieve the updated recommendations.
Response Status Codes
202 Accepted
400 Bad Request
401 Unauthorized
500 Internal Server Error
Deleting Usage-Based Recommendations
Delete all collected usage data and all current reflection recommendations.
We recommend deleting recommendations only when troubleshooting.
DELETE /api/v3/reflection/recommendations/usage-based/
curl -X DELETE 'https://{hostname}/api/v3/reflection/recommendations/usage-based/' \
--header 'Authorization: Bearer <PersonalAccessToken>' \
--header 'Content-Type: application/json'
This endpoint returns an empty response body with a 202 Accepted response status code. Dremio deletes the recommendations asynchronously, so it may take several minutes for the deletion to complete.
Response Status Codes
202 Accepted
400 Bad Request
401 Unauthorized
500 Internal Server Error
Retrieving Recommendations (Deprecated)
The POST /api/v3/reflection/recommendations endpoint described in this section is deprecated. In its place, use the job-based and usage-based endpoints to retrieve and refresh reflection recommendations.
Use the Recommendations API to submit job IDs of jobs that ran SQL queries, and receive recommendations for aggregation reflections that can accelerate those queries.
For more information, see Sending Requests to the Recommendations API.
Recommendation Object{
"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",
"jobIds": ["6j6c34cf-9drf-b07a-5ab7-abea69a66d00"]
}
],
"canAlterReflections": true
}
Recommendation Attributes (Deprecated)
viewRequestBody Array of Object
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.
For descriptions of these fields, see View.
viewRequestEndpoint String
The endpoint to use when submitting the request to the Catalog API to create the view on which to define the reflection.
reflectionRequestBody Array of Object
The fields that you can include in a request to the Reflection API for creating the recommended aggregation reflection.
For descriptions of these fields, see Reflection.
reflectionRequestEndpoint String
The endpoint to use when submitting the request to the Reflection API to create the aggregation reflection.
jobIds Array of String
The IDs of the jobs that ran the queries for which the recommendation is given.
canAlterReflections String
Indicates whether the columns in the reflection can be edited, and whether columns can be added or removed.
Requesting Recommendations (Deprecated)
This endpoint is deprecated. In its place, use the job-based and usage-based endpoints to retrieve and refresh reflection recommendations.
Request recommended aggregation reflections to accelerate the queries associated with the provided job ID or IDs.
Method and URLPOST /api/v3/reflection/recommendations
Parameters (Deprecated)
jobIds Body Array of String
A list of the job IDs of jobs that have run the SQL commands that you want to receive one or more recommended reflections for.
Example Requestcurl -X POST 'https://{hostname}/api/v3/reflection/recommendations' \
--header 'Authorization: Bearer <PersonalAccessToken>' \
--header 'Content-Type: application/json' \
--data
'{
"jobIds": [
"1a515250-7572-0f9b-f5e5-89f505b55200",
"1a515292-583c-e407-79ef-9f9b494fa600"
]
}'