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.
{
"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": "{\"tableKey\":[\"azure_3\",\"table_2\"],\"contentId\":\"356d6214-6c55-4011-8378-34b2f65d38c6\",\"versionContext\":{\"type\":\"BRANCH\",\"value\":\"main\"}}",
"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": "{\"tableKey\":[\"recommended_view\",\"view_1\"],\"contentId\":\"7ae1eded-5133-4e80-bf06-1be20975e0aa\",\"versionContext\":{\"type\":\"BRANCH\",\"value\":\"main\"}}",
"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 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. NOTE: The API returns the Software endpoint. The correct endpoint for Cloud requests is POST /v0/projects/{project-id}/catalog
.
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. NOTE: The API returns the Software endpoint. The correct endpoint for Cloud requests is POST /v0/projects/{project-id}/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.