View

Use the Catalog API to retrieve, create, update, and delete views.

View Object

{
  "entityType": "dataset",
  "id": "{\"tableKey\":[\"Business\",\"Transportation\",\"NYC-taxi-trips-short-distance\"],\"contentId\":\"ef99ab32-89ca-4d1c-9e91-2c8be861bb8a\",\"versionContext\":{\"type\":\"BRANCH\",\"value\":\"main\"}}",
  "type": "VIRTUAL_DATASET",
  "path": [
    "Business",
    "Transportation",
    "NYC-taxi-trips-short-distance"
  ],
  "createdAt": "2022-11-17T18:31:23.236Z",
  "isMetadataExpired": false,
  "lastMetadataRefreshAt": "2024-01-31T09:50:01.012Z",
  "tag": "00000-f90d1526-e64b-47b1-9ab0-d25df5247cab",
  "sql": "SELECT * FROM \"NYC-taxi-trips\" WHERE trip_distance_mi <= 2.0 ORDER BY trip_distance_mi ASC",
  "sqlContext": [
    "Samples",
    "samples.dremio.com"
  ],
  "fields": [
    {
      "name": "pickup_datetime",
      "type": {
        "name": "TIMESTAMP"
      }
    },
    {
      "name": "passenger_count",
      "type": {
        "name": "BIGINT"
      }
    },
    {
      "name": "passenger_payment_method",
      "type": {
        "name": "STRUCT",
        "subSchema": [
          {
            "name": "cash",
            "type": {
              "name": "BOOLEAN"
            }
          },
          {
            "name": "credit-debit",
            "type": {
              "name": "BOOLEAN"
            }
          },
          {
            "name": "payment-app",
            "type": {
              "name": "BOOLEAN"
            }
          },
          {
            "name": "other",
            "type": {
              "name": "BOOLEAN"
            }
          }
        ]
      }
    },
    {
      "name": "trip_distance_mi",
      "type": {
        "name": "DOUBLE"
      }
    },
    {
      "name": "fare_amount",
      "type": {
        "name": "DOUBLE"
      }
    },
    {
      "name": "tip_amount",
      "type": {
        "name": "DOUBLE"
      }
    },
    {
      "name": "total_amount",
      "type": {
        "name": "DOUBLE"
      }
    }
  ]
}

View Attributes

entityType String

Type of catalog entity. For views, the entityType is dataset.

Example: dataset

id String

Unique identifier for the view. The id is a JSON string.

Example: {"tableKey":["myArcticSource","myFolder","myTable","myView"],"contentId":"65b24a53-c56e-4037-8ffc-f25297614c3c","versionContext":{"type":"BRANCH","value":"main"}}

type String

Type of dataset. For views, the type is VIRTUAL_DATASET.

Example: VIRTUAL_DATASET

path Array of String

Path to the view within Dremio, expressed in an array. The path lists each level of hierarchy in order, from outer to inner: source first, then folder and subfolders, then the view itself as the last item in the array.

Example: ["Business", "Transportation", "NYC-taxi-trips-short-distance"]

createdAt String

Date and time that the view was created. In UTC format.

Example: 2022-11-17T18:31:23.236Z

isMetadataExpired Boolean

If true, the metadata of the tables that the view is defined on needs to be refreshed. To refresh it, run the ALTER VIEW command, using the clause REFRESH METADATA. See ALTER VIEW.
If false, the metadata can still be used for planning queries against the view.
If NULL, metadata has never yet been collected for the tables that the view is defined on.

lastMetadataRefreshAt String

Date and time that the metadata of the tables that the view is defined on was last refreshed. In UTC format.

Example: 2024-01-31T09:50:01.012Z

tag String

Unique identifier for the view instance. Dremio changes the tag whenever the view changes and uses the tag value to ensure that PUT requests apply to the most recent version of the view.

Example: 00000-f90d1526-e64b-47b1-9ab0-d25df5247cab

sql String

SQL query used to create the view.

Example: SELECT * FROM "NYC-taxi-trips" WHERE trip_distance_mi <= 2.0 ORDER BY trip_distance_mi ASC

sqlContext Array of String

Context for the SQL query used to create the view.

Example: ["Samples", "samples.dremio.com"]

fields Array of Object

Attributes that represent the dataset schema.

Attributes of Objects in the `fields` Array

name String

Name of the view field.

Example: pickup_datetime

type Object

Information about the view field.

Attributes of the `type` Object

name String

Name of the view field's type.

Enum: STRUCT, LIST, UNION, INTEGER, BIGINT, FLOAT, DOUBLE, VARCHAR, VARBINARY, BOOLEAN, DECIMAL, TIME, DATE, TIMESTAMP, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH

Example: TIMESTAMP

precision Integer

Total number of digits in the number. Included only for the DECIMAL type.

Example: 38

scale Integer

Number of digits to the right of the decimal point. Included only for the DECIMAL type.

Example: 2

subSchema Array of Object

List of objects that represent the field's composition. For example, a field composed of data about a restaurant might have a subSchema with an object for parking options, another for payment methods, and so on. subSchemas may be nested within other subSchemas. subSchema is listed only for the STRUCT, LIST, and UNION types.

Attributes of Objects in the `subSchema` Array

name String

Name for the subSchema object.

Example: cash

type Object

Object that contains a name attribute that provides the field's type.

Example: {"name": "BOOLEAN"}

Creating a View

Create a view from a table in Dremio.

Method and URL

POST /projects/{project-id}/catalog/

Parameters

project-id Path String (UUID)

Unique identifier for the project where you want to create the view.

Example: 1df71752-69b7-47d9-9e6c-990e6b194aa4

entityType Body String

Type of catalog entity. For views, the entityType is dataset.

type Body String

Type of dataset. For views, the type is VIRTUAL_DATASET.

path Body 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. The name of the view cannot include the following special characters: /, :, [, or ]. Views can only be created in Arctic sources and the project's Arctic catalog.

Example: ["Business", "Transportation", "NYC-taxi-trips-short-distance"]

sql Body String

SQL query to use to create the view.

Example: SELECT * FROM \"NYC-taxi-trips\" WHERE trip_distance_mi <= 2.0 ORDER BY trip_distance_mi ASC

sqlContext Body Array of String

Context for the SQL query to use to create the view.

Example: ["Samples", "samples.dremio.com"]

Example Request

curl -X POST 'https://api.dremio.cloud/v0/projects/1df71752-69b7-47d9-9e6c-990e6b194aa4/catalog/' \
--header 'Authorization: Bearer <PersonalAccessToken>' \
--header 'Content-Type: application/json' \
--data-raw '{
  "entityType": "dataset",
  "path": [
    "Business",
    "Transportation",
    "NYC-taxi-trips-short-distance"
  ],
  "type": "VIRTUAL_DATASET",
  "sql": "SELECT * FROM \"NYC-taxi-trips\" WHERE trip_distance_mi <= 2.0 ORDER BY trip_distance_mi ASC",
  "sqlContext": [
    "Samples",
    "samples.dremio.com"
  ]
}'

Example Response

{
  "entityType": "dataset",
  "id": "{\"tableKey\":[\"Business\",\"Transportation\",\"NYC-taxi-trips-short-distance\"],\"contentId\":\"ef99ab32-89ca-4d1c-9e91-2c8be861bb8a\",\"versionContext\":{\"type\":\"BRANCH\",\"value\":\"main\"}}",
  "type": "VIRTUAL_DATASET",
  "path": [
    "Business",
    "Transportation",
    "NYC-taxi-trips-short-distance"
  ],
  "createdAt": "2022-11-17T18:31:23.236Z",
  "isMetadataExpired": false,
  "lastMetadataRefreshAt": "2024-01-31T09:50:01.012Z",
  "tag": "00000-f90d1526-e64b-47b1-9ab0-d25df5247cab",
  "sql": "SELECT * FROM \"NYC-taxi-trips\" WHERE trip_distance_mi <= 2.0 ORDER BY trip_distance_mi ASC",
  "sqlContext": [
    "Samples",
    "samples.dremio.com"
  ],
  "fields": [
    {
      "name": "pickup_datetime",
      "type": {
        "name": "TIMESTAMP"
      }
    },
    {
      "name": "passenger_count",
      "type": {
        "name": "BIGINT"
      }
    },
    {
      "name": "passenger_payment_method",
      "type": {
        "name": "STRUCT",
        "subSchema": [
          {
            "name": "cash",
            "type": {
              "name": "BOOLEAN"
            }
          },
          {
            "name": "credit-debit",
            "type": {
              "name": "BOOLEAN"
            }
          },
          {
            "name": "payment-app",
            "type": {
              "name": "BOOLEAN"
            }
          },
          {
            "name": "other",
            "type": {
              "name": "BOOLEAN"
            }
          }
        ]
      }
    },
    {
      "name": "trip_distance_mi",
      "type": {
        "name": "DOUBLE"
      }
    },
    {
      "name": "fare_amount",
      "type": {
        "name": "DOUBLE"
      }
    },
    {
      "name": "tip_amount",
      "type": {
        "name": "DOUBLE"
      }
    },
    {
      "name": "total_amount",
      "type": {
        "name": "DOUBLE"
      }
    }
  ]
}

Response Status Codes

200 OK

400 Bad Request

401 Unauthorized

403 Forbidden

404 Not Found

500 Internal Server Error

Retrieving a View by ID

Retrieve a view by specifying the view's id value.

Method and URL

GET /v0/projects/{project-id}/catalog/{id}

Parameters

project-id Path String (UUID)

Unique identifier for the project that contains the view you want to retrieve.

Example: 1df71752-69b7-47d9-9e6c-990e6b194aa4

id Path String

Unique identifier for the view you want to retrieve. The id is a JSON string. Use URL encoding to replace special characters in the id with their UTF-8-equivalent characters.

Example: %7B%22tableKey%22%3A%5B%22Business%22%2C%22Transportation%22%2C%22NYC-taxi-trips-short-distance%22%5D%2C%22contentId%22%3A%22ef99ab32-89ca-4d1c-9e91-2c8be861bb8a%22%2C%22versionContext%22%3A%7B%22type%22%3A%22BRANCH%22%2C%22value%22%3A%22main%22%7D%7D

Example Request

curl -X GET 'https://api.dremio.cloud/v0/projects/1df71752-69b7-47d9-9e6c-990e6b194aa4/catalog/%7B%22tableKey%22%3A%5B%22Business%22%2C%22Transportation%22%2C%22NYC-taxi-trips-short-distance%22%5D%2C%22contentId%22%3A%22ef99ab32-89ca-4d1c-9e91-2c8be861bb8a%22%2C%22versionContext%22%3A%7B%22type%22%3A%22BRANCH%22%2C%22value%22%3A%22main%22%7D%7D' \
--header 'Authorization: Bearer <PersonalAccessToken>' \
--header 'Content-Type: application/json'

Example Response

{
  "entityType": "dataset",
  "id": "{\"tableKey\":[\"Business\",\"Transportation\",\"NYC-taxi-trips-short-distance\"],\"contentId\":\"ef99ab32-89ca-4d1c-9e91-2c8be861bb8a\",\"versionContext\":{\"type\":\"BRANCH\",\"value\":\"main\"}}",
  "type": "VIRTUAL_DATASET",
  "path": [
    "Business",
    "Transportation",
    "NYC-taxi-trips-short-distance"
  ],
  "createdAt": "2022-11-17T18:31:23.236Z",
  "isMetadataExpired": false,
  "lastMetadataRefreshAt": "2024-01-31T09:50:01.012Z",
  "tag": "00000-f90d1526-e64b-47b1-9ab0-d25df5247cab",
  "sql": "SELECT * FROM \"NYC-taxi-trips\" WHERE trip_distance_mi <= 2.0 ORDER BY trip_distance_mi ASC",
  "sqlContext": [
    "Samples",
    "samples.dremio.com"
  ],
  "fields": [
    {
      "name": "pickup_datetime",
      "type": {
        "name": "TIMESTAMP"
      }
    },
    {
      "name": "passenger_count",
      "type": {
        "name": "BIGINT"
      }
    },
    {
      "name": "passenger_payment_method",
      "type": {
        "name": "STRUCT",
        "subSchema": [
          {
            "name": "cash",
            "type": {
              "name": "BOOLEAN"
            }
          },
          {
            "name": "credit-debit",
            "type": {
              "name": "BOOLEAN"
            }
          },
          {
            "name": "payment-app",
            "type": {
              "name": "BOOLEAN"
            }
          },
          {
            "name": "other",
            "type": {
              "name": "BOOLEAN"
            }
          }
        ]
      }
    },
    {
      "name": "trip_distance_mi",
      "type": {
        "name": "DOUBLE"
      }
    },
    {
      "name": "fare_amount",
      "type": {
        "name": "DOUBLE"
      }
    },
    {
      "name": "tip_amount",
      "type": {
        "name": "DOUBLE"
      }
    },
    {
      "name": "total_amount",
      "type": {
        "name": "DOUBLE"
      }
    }
  ]
}

Response Status Codes

200 OK

400 Bad Request

401 Unauthorized

403 Forbidden

404 Not Found

Retrieving a View by Path

Retrieve a view by specifying the view's path.

Method and URL

GET /v0/projects/{project-id}/catalog/by-path/{path}

Parameters

project-id Path String (UUID)

Unique identifier for the project that contains the view you want to retrieve.

Example: 1df71752-69b7-47d9-9e6c-990e6b194aa4

path Path String

View's location within Dremio, using forward slashes as separators. For example, for the "NYC-taxi-trips-short-distance" view in the "Transportation" folder within the source "Business," the path is Business/Transportation/NYC-taxi-trips-short-distance. If the name of any component in the path includes special characters for URLs, such as spaces, use URL encoding to replace the special characters with their UTF-8-equivalent characters. For example, "Dremio University" should be Dremio%20University in the URL path.

Example: Business/Transportation/NYC-taxi-trips-short-distance

Example Request

curl -X GET 'https://api.dremio.cloud/v0/projects/1df71752-69b7-47d9-9e6c-990e6b194aa4/catalog/by-path/Business/Transportation/NYC-taxi-trips-short-distance' \
--header 'Authorization: Bearer <PersonalAccessToken>' \
--header 'Content-Type: application/json'

Example Response

{
  "entityType": "dataset",
  "id": "{\"tableKey\":[\"Business\",\"Transportation\",\"NYC-taxi-trips-short-distance\"],\"contentId\":\"ef99ab32-89ca-4d1c-9e91-2c8be861bb8a\",\"versionContext\":{\"type\":\"BRANCH\",\"value\":\"main\"}}",
  "type": "VIRTUAL_DATASET",
  "path": [
    "Business",
    "Transportation",
    "NYC-taxi-trips-short-distance"
  ],
  "createdAt": "2022-11-17T18:31:23.236Z",
  "isMetadataExpired": false,
  "lastMetadataRefreshAt": "2024-01-31T09:50:01.012Z",
  "tag": "00000-f90d1526-e64b-47b1-9ab0-d25df5247cab",
  "sql": "SELECT * FROM \"NYC-taxi-trips\" WHERE trip_distance_mi <= 2.0 ORDER BY trip_distance_mi ASC",
  "sqlContext": [
    "Samples",
    "samples.dremio.com"
  ],
  "fields": [
    {
      "name": "pickup_datetime",
      "type": {
        "name": "TIMESTAMP"
      }
    },
    {
      "name": "passenger_count",
      "type": {
        "name": "BIGINT"
      }
    },
    {
      "name": "passenger_payment_method",
      "type": {
        "name": "STRUCT",
        "subSchema": [
          {
            "name": "cash",
            "type": {
              "name": "BOOLEAN"
            }
          },
          {
            "name": "credit-debit",
            "type": {
              "name": "BOOLEAN"
            }
          },
          {
            "name": "payment-app",
            "type": {
              "name": "BOOLEAN"
            }
          },
          {
            "name": "other",
            "type": {
              "name": "BOOLEAN"
            }
          }
        ]
      }
    },
    {
      "name": "trip_distance_mi",
      "type": {
        "name": "DOUBLE"
      }
    },
    {
      "name": "fare_amount",
      "type": {
        "name": "DOUBLE"
      }
    },
    {
      "name": "tip_amount",
      "type": {
        "name": "DOUBLE"
      }
    },
    {
      "name": "total_amount",
      "type": {
        "name": "DOUBLE"
      }
    }
  ]
}

Response Status Codes

200 OK

400 Bad Request

401 Unauthorized

403 Forbidden

404 Not Found

Updating a View

Update a view in Dremio.

Method and URL

PUT /projects/{project-id}/catalog/{id}

Parameters

project-id Path String (UUID)

Unique identifier for the project that contains the view you want to update.

Example: 1df71752-69b7-47d9-9e6c-990e6b194aa4

id Path String

Unique identifier for the view you want to update. The id is a JSON string. Use URL encoding to replace special characters in the id with their UTF-8-equivalent characters.

entityType Body String

Type of catalog entity. For views, the entityType is dataset.

type Body String

Type of dataset. For views, type is VIRTUAL_DATASET.

path Body Array of String

Path to the location where the view you want to update is saved 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 the name for the view itself as the last item in the array. Views can only be saved in Arctic sources and the project's Arctic catalog.

Example: ["Business", "Transportation", "NYC-taxi-trips-short-distance"]

tag Body String Optional

Unique identifier for the version of the view you want to update. If you provide a tag in the request body, Dremio uses the tag value to ensure that you are requesting to update the most recent version of the view. If you do not provide a tag, Dremio automatically updates the most recent version of the view.

Example: 00000-f90d1526-e64b-47b1-9ab0-d25df5247cab

sql Body String

SQL query to use to update the view.

Example: SELECT * FROM "NYC-taxi-trips" WHERE trip_distance_mi <= 2.0 ORDER BY trip_distance_mi DESC

sqlContext Body Array of String

Context for the SQL query to use for the updated view.

Example: ["Samples", "samples.dremio.com"]

Example Request

curl -X PUT 'https://api.dremio.cloud/v0/projects/1df71752-69b7-47d9-9e6c-990e6b194aa4/catalog/%7B%22tableKey%22%3A%5B%22Business%22%2C%22Transportation%22%2C%22NYC-taxi-trips-short-distance%22%5D%2C%22contentId%22%3A%22ef99ab32-89ca-4d1c-9e91-2c8be861bb8a%22%2C%22versionContext%22%3A%7B%22type%22%3A%22BRANCH%22%2C%22value%22%3A%22main%22%7D%7D' \
--header 'Authorization: Bearer <PersonalAccessToken>' \
--header 'Content-Type: application/json' \
--data-raw '{
  "entityType": "dataset",
  "id": "{\"tableKey\":[\"Business\",\"Transportation\",\"NYC-taxi-trips-short-distance\"],\"contentId\":\"ef99ab32-89ca-4d1c-9e91-2c8be861bb8a\",\"versionContext\":{\"type\":\"BRANCH\",\"value\":\"main\"}}",
  "path": [
    "Business",
    "Transportation",
    "NYC-taxi-trips-short-distance"
  ],
  "type": "VIRTUAL_DATASET",
  "tag": "00000-f90d1526-e64b-47b1-9ab0-d25df5247cab",
  "sql": "SELECT trip_distance_mi, fare_amount, tip_amount FROM \"NYC-taxi-trips\" WHERE trip_distance_mi <= 2.0 ORDER BY trip_distance_mi DESC",
  "sqlContext": [
    "Samples",
    "samples.dremio.com"
  ]
}'

Example Response

{
  "entityType": "dataset",
  "id": "{\"tableKey\":[\"Business\",\"Transportation\",\"NYC-taxi-trips-short-distance\"],\"contentId\":\"ef99ab32-89ca-4d1c-9e91-2c8be861bb8a\",\"versionContext\":{\"type\":\"BRANCH\",\"value\":\"main\"}}",
  "type": "VIRTUAL_DATASET",
  "path": [
    "Business",
    "Transportation",
    "NYC-taxi-trips-by-distance"
  ],
  "createdAt": "2023-01-20T15:26:39.780Z",
  "isMetadataExpired": false,
  "lastMetadataRefreshAt": "2024-01-31T09:50:01.012Z",
  "tag": "00001-7cab1a42-8835-4d31-827b-fedee1ad38d1",
  "sql": "SELECT trip_distance_mi, fare_amount, tip_amount FROM \"NYC-taxi-trips\" WHERE trip_distance_mi <= 2.0 ORDER BY trip_distance_mi DESC",
  "sqlContext": [
    "Samples",
    "samples.dremio.com"
  ],
  "fields": [
    {
      "name": "trip_distance_mi",
      "type": {
        "name": "DOUBLE"
      }
    },
    {
      "name": "fare_amount",
      "type": {
        "name": "DOUBLE"
      }
    },
    {
      "name": "tip_amount",
      "type": {
        "name": "DOUBLE"
      }
    }
  ]
}

Response Status Codes

200 OK

400 Bad Request

401 Unauthorized

403 Forbidden

404 Not Found

500 Internal Server Error

Refreshing the Reflections on a View

Refresh the reflections associated with the specified view.

Read Refreshing Reflections to learn how refreshing works.

Method and URL

POST /v0/projects/{project-id}/catalog/{id}/refresh

Parameters

project-id Path String (UUID)

Unique identifier of the project that contains the view you want to refresh.

Example: 1df71752-69b7-47d9-9e6c-990e6b194aa4

id Path String (UUID)

Unique identifier for the table you want to refresh. For views that are not in an Arctic source, the id is a UUID like c9c11d32-0576-4200-5a5b-8c7229cb3d72. For views in Arctic sources and the primary Arctic catalog, the id is a JSON string like {"tableKey":["myArcticSource","myFolder","myTable"],"contentId":"65b24a53-c56e-4037-8ffc-f25297614c3c","versionContext":{"type":"BRANCH","value":"main"}}.

Example: c9c11d32-0576-4200-5a5b-8c7229cb3d72

Example Request

curl -X POST 'https://api.dremio.cloud/v0/projects/1df71752-69b7-47d9-9e6c-990e6b194aa4/catalog/c9c11d32-0576-4200-5a5b-8c7229cb3d72/refresh' \
-H 'Authorization: Bearer <PersonalAccessToken>' \
-H 'Content-Type: application/json'

Example Response

No response

Response Status Codes

204 No Content

400 Bad Request

401 Unauthorized

403 Forbidden

404 Not Found

Deleting a View

Delete the specified view.

Method and URL

DELETE /v0/projects/{project-id}/catalog/{id}

Parameters

project-id Path String (UUID)

Unique identifier for the project that contains the view you want to delete.

Example: 1df71752-69b7-47d9-9e6c-990e6b194aa4

id Path String

Unique identifier for the view you want to delete. The id is a JSON string. Use URL encoding to replace special characters in the id with their UTF-8-equivalent characters.

Example Request

curl -X DELETE 'https://api.dremio.cloud/v0/projects/1df71752-69b7-47d9-9e6c-990e6b194aa4/catalog/%7B%22tableKey%22%3A%5B%22Business%22%2C%22Transportation%22%2C%22NYC-taxi-trips-short-distance%22%5D%2C%22contentId%22%3A%22ef99ab32-89ca-4d1c-9e91-2c8be861bb8a%22%2C%22versionContext%22%3A%7B%22type%22%3A%22BRANCH%22%2C%22value%22%3A%22main%22%7D%7D' \
-H 'Authorization: Bearer <PersonalAccessToken>' \
-H 'Content-Type: application/json'

Example Response

No response

Response Status Codes

204 No Content

400 Bad Request

401 Unauthorized

403 Forbidden

404 Not Found

View Attributes​

Attributes of Objects in the fields Array​

Attributes of the type Object​

Attributes of Objects in the subSchema Array​

Creating a View​

Parameters​

Response Status Codes​

Retrieving a View by ID​

Parameters​

Response Status Codes​

Retrieving a View by Path​

Parameters​

Response Status Codes​

Updating a View​

Parameters​

Response Status Codes​

Refreshing the Reflections on a View​

Parameters​

Response Status Codes​

Deleting a View​

Parameters​

Response Status Codes​

View Attributes

Attributes of Objects in the `fields` Array

Attributes of the `type` Object

Attributes of Objects in the `subSchema` Array

Creating a View

Parameters

Response Status Codes

Retrieving a View by ID

Parameters

Response Status Codes

Retrieving a View by Path

Parameters

Response Status Codes

Updating a View

Parameters

Response Status Codes

Refreshing the Reflections on a View

Parameters

Response Status Codes

Deleting a View

Parameters

Response Status Codes