Table
Use the Catalog API to retrieve tables, format files and folders as tables, update and refresh tables, and revert tables to files and folders.
Table Object{
"entityType": "dataset",
"id": "c9c11d32-0576-4200-5a5b-8c7229cb3d72",
"type": "PHYSICAL_DATASET",
"path": [
"Samples",
"samples.dremio.com",
"Dremio University",
"restaurant_reviews.parquet"
],
"createdAt": "2023-01-13T19:52:01.894Z",
"tag": "cb2905bb-39c0-497f-ae74-4c310d534f25",
"accelerationRefreshPolicy": {
"refreshPeriodMs": 3600000,
"gracePeriodMs": 10800000,
"method": "FULL",
"neverExpire": false,
"neverRefresh": false
},
"format": {
"type": "Parquet",
"name": "restaurant_reviews.parquet",
"fullPath": [
"Samples",
"samples.dremio.com",
"Dremio University",
"restaurant_reviews.parquet"
],
"ctime": 0,
"isFolder": false,
"location": "/samples.dremio.com/Dremio University/restaurant_reviews.parquet",
"autoCorrectCorruptDates": true
},
"accessControlList": {
"users": [
{
"id": "c590ed7f-b2b4-4e1f-ba7d-94173afdc9a3",
"permissions": [
"SELECT",
"ALTER"
]
},
{
"id": "30fca499-4abc-4469-7142-fc8dd29acac8",
"permissions": [
"SELECT",
"ALTER",
"MANAGE_GRANTS"
]
}
],
"roles": [
{
"id": "76a9884b-aea5-46d5-a73a-000edf23f390",
"permissions": [
"SELECT",
"ALTER"
]
}
]
},
"permissions": [
"READ",
"WRITE",
"ALTER_REFLECTION",
"SELECT",
"ALTER",
"VIEW_REFLECTION",
"MODIFY",
"MANAGE_GRANTS",
"CREATE_TABLE",
"DROP",
"EXTERNAL_QUERY",
"INSERT",
"TRUNCATE",
"DELETE",
"UPDATE",
"EXECUTE",
"CREATE_SOURCE",
"ALL"
],
"owner": {
"ownerId": "30fca499-4abc-4469-7142-fc8dd29acac8",
"ownerType": "USER"
},
"fields": [
{
"name": "_id",
"type": {
"name": "VARCHAR"
}
},
{
"name": "name",
"type": {
"name": "VARCHAR"
}
},
{
"name": "city",
"type": {
"name": "VARCHAR"
}
},
{
"name": "state",
"type": {
"name": "VARCHAR"
}
},
{
"name": "categories",
"type": {
"name": "LIST",
"subSchema": [
{
"type": {
"name": "VARCHAR"
}
}
]
}
},
{
"name": "review_count",
"type": {
"name": "BIGINT"
}
},
{
"name": "stars",
"type": {
"name": "DOUBLE"
}
},
{
"name": "attributes",
"type": {
"name": "STRUCT",
"subSchema": [
{
"name": "Parking",
"type": {
"name": "STRUCT",
"subSchema": [
{
"name": "garage",
"type": {
"name": "BOOLEAN"
}
},
{
"name": "street",
"type": {
"name": "BOOLEAN"
}
},
{
"name": "lot",
"type": {
"name": "BOOLEAN"
}
},
{
"name": "valet",
"type": {
"name": "BOOLEAN"
}
}
]
}
},
{
"name": "Accepts Credit Cards",
"type": {
"name": "BOOLEAN"
}
},
{
"name": "Wheelchair Accessible",
"type": {
"name": "BOOLEAN"
}
},
{
"name": "Price Range",
"type": {
"name": "BIGINT"
}
}
]
}
},
{
"name": "date",
"type": {
"name": "VARCHAR"
}
}
],
"approximateStatisticsAllowed": false
}
Table Attributes
{{< api-section file="data/software-api/table.json" data="tableAttributes" >}}
accelerationRefreshPolicy
{{< api-section file="data/software-api/table.json" data="accelerationRefreshPolicyAttributes" >}}
format
{{< api-section file="data/software-api/table.json" data="formatAttributes" >}}
accessControlList
{{< api-section file="data/software-api/table.json" data="accessControlListAttributes" >}}
users and roles
{{< api-section file="data/software-api/table.json" data="usersRolesAccessControlAttributes" >}}
owner
{{< api-section file="data/software-api/table.json" data="ownerAttributes" >}}
fields
{{< api-section file="data/software-api/table.json" data="fieldsAttributes" >}}
type
{{< api-section file="data/software-api/table.json" data="typeAttributes" >}}
subSchema
{{< api-section file="data/software-api/table.json" data="subSchemaAttributes" >}}
parquetDataFormat
{{< api-section file="data/software-api/table.json" data="parquetDataFormatAttributes" >}}
Formatting a File or Folder as a Table
Format a file or folder as a table so that you can query the data in Dremio.
To format a folder, all files in the folder must be the same format.
POST /api/v3/catalog/{id}
Parameters
{{< api-section file="data/software-api/table.json" data="formatTableParameters" >}}
format
{{< api-section file="data/software-api/table.json" data="formatFormatObjectParameters" >}}
accessControlList
{{< api-section file="data/software-api/table.json" data="formatAccessControlListObjectParameters" >}}
users and roles
{{< api-section file="data/software-api/table.json" data="formatUsersRolesAccessControlParameters" >}}
Example Request
curl -X POST 'https://{hostname}/api/v3/catalog/c9c11d32-0576-4200-5a5b-8c7229cb3d72' \
--header 'Authorization: _dremio{tokenstring}' \
--header 'Content-Type: application/json' \
--data-raw '{
"entityType": "dataset",
"path": [
"Samples",
"Dremio University",
"restaurant_reviews.parquet"
],
"type": "PHYSICAL_DATASET",
"format": {
"type": "Parquet"
},
"accessControlList": {
"users": [
{
"id": "c590ed7f-b2b4-4e1f-ba7d-94173afdc9a3",
"permissions": [
"SELECT",
"ALTER"
]
},
{
"id": "30fca499-4abc-4469-7142-fc8dd29acac8",
"permissions": [
"SELECT",
"ALTER",
"MANAGE_GRANTS"
]
}
],
"roles": [
{
"id": "76a9884b-aea5-46d5-a73a-000edf23f390",
"permissions": [
"SELECT",
"ALTER"
]
}
]
}
}'
{
"entityType": "dataset",
"id": "c9c11d32-0576-4200-5a5b-8c7229cb3d72",
"type": "PHYSICAL_DATASET",
"path": [
"Samples",
"samples.dremio.com",
"Dremio University",
"restaurant_reviews.parquet"
],
"createdAt": "2023-01-13T19:52:01.894Z",
"tag": "cb2905bb-39c0-497f-ae74-4c310d534f25",
"format": {
"type": "Parquet",
"name": "restaurant_reviews.parquet",
"fullPath": [
"Samples",
"samples.dremio.com",
"Dremio University",
"restaurant_reviews.parquet"
],
"ctime": 0,
"isFolder": false,
"location": "/samples.dremio.com/Dremio University/restaurant_reviews.parquet",
"autoCorrectCorruptDates": true
},
"accessControlList": {
"users": [
{
"id": "c590ed7f-b2b4-4e1f-ba7d-94173afdc9a3",
"permissions": [
"SELECT",
"ALTER"
]
},
{
"id": "30fca499-4abc-4469-7142-fc8dd29acac8",
"permissions": [
"SELECT",
"ALTER",
"MANAGE_GRANTS"
]
}
],
"roles": [
{
"id": "76a9884b-aea5-46d5-a73a-000edf23f390",
"permissions": [
"SELECT",
"ALTER"
]
}
]
},
"owner": {
"ownerId": "30fca499-4abc-4469-7142-fc8dd29acac8",
"ownerType": "USER"
},
"fields": [
{
"name": "_id",
"type": {
"name": "VARCHAR"
}
},
{
"name": "name",
"type": {
"name": "VARCHAR"
}
},
{
"name": "city",
"type": {
"name": "VARCHAR"
}
},
{
"name": "state",
"type": {
"name": "VARCHAR"
}
},
{
"name": "categories",
"type": {
"name": "LIST",
"subSchema": [
{
"type": {
"name": "VARCHAR"
}
}
]
}
},
{
"name": "review_count",
"type": {
"name": "BIGINT"
}
},
{
"name": "stars",
"type": {
"name": "DOUBLE"
}
},
{
"name": "attributes",
"type": {
"name": "STRUCT",
"subSchema": [
{
"name": "Parking",
"type": {
"name": "STRUCT",
"subSchema": [
{
"name": "garage",
"type": {
"name": "BOOLEAN"
}
},
{
"name": "street",
"type": {
"name": "BOOLEAN"
}
},
{
"name": "lot",
"type": {
"name": "BOOLEAN"
}
},
{
"name": "valet",
"type": {
"name": "BOOLEAN"
}
}
]
}
},
{
"name": "Accepts Credit Cards",
"type": {
"name": "BOOLEAN"
}
},
{
"name": "Wheelchair Accessible",
"type": {
"name": "BOOLEAN"
}
},
{
"name": "Price Range",
"type": {
"name": "BIGINT"
}
}
]
}
},
{
"name": "date",
"type": {
"name": "VARCHAR"
}
}
],
"approximateStatisticsAllowed": false
}
curl -X POST 'https://{hostname}/api/v3/catalog/dremio%3A%2FSamples%2Fsamples.dremio.com%2FDremio%20University%2Foracle-departments.xlsx' \
--header 'Authorization: _dremio{tokenstring}' \
--header 'Content-Type: application/json' \
--data-raw '{
"entityType": "dataset",
"path": [
"Samples",
"samples.dremio.com",
"Dremio University",
"oracle-departments.xlsx"
],
"type": "PHYSICAL_DATASET",
"format": {
"type": "Excel",
"extractHeader": true,
"hasMergedCells": true,
"sheetName": "Sheet1"
}
}'
curl -X POST 'https://{hostname}/api/v3/catalog/6ba3bd6e-fd27-4572-a535-77e1548283b3' \
--header 'Authorization: _dremio{tokenstring}' \
--header 'Content-Type: application/json' \
--data-raw '{
"entityType": "dataset",
"path": [
"Samples",
"samples.dremio.com",
"Dremio University",
"airbnb_listings.csv"
],
"type": "PHYSICAL_DATASET",
"format": {
"type": "Text",
"fieldDelimiter": ",",
"skipFirstLine": false,
"extractHeader": true,
"quote": "\"",
"comment": "#",
"escape": "\"",
"lineDelimiter": "\r\n",
"autoGenerateColumnNames": true,
"trimHeader": false
}
}'
Response Status Codes
{{< api-responses file="data/software-api/table.json" data="formatTableResponseCodes" >}}
Retrieving a Table by ID
Retrieve a table by specifying the table's id value.
GET /api/v3/catalog/{id}
Parameters
{{< api-section file="data/software-api/table.json" data="retrieveTableIdParameters" >}}
Example Request
curl -X GET 'https://{hostname}/api/v3/catalog/c9c11d32-0576-4200-5a5b-8c7229cb3d72' \
--header 'Authorization: _dremio{tokenstring}' \
--header 'Content-Type: application/json'
{
"entityType": "dataset",
"id": "c9c11d32-0576-4200-5a5b-8c7229cb3d72",
"type": "PHYSICAL_DATASET",
"path": [
"Samples",
"samples.dremio.com",
"Dremio University",
"restaurant_reviews.parquet"
],
"createdAt": "2023-01-13T19:52:01.894Z",
"tag": "cb2905bb-39c0-497f-ae74-4c310d534f25",
"format": {
"type": "Parquet",
"name": "restaurant_reviews.parquet",
"fullPath": [
"Samples",
"samples.dremio.com",
"Dremio University",
"restaurant_reviews.parquet"
],
"ctime": 0,
"isFolder": false,
"location": "/samples.dremio.com/Dremio University/restaurant_reviews.parquet",
"autoCorrectCorruptDates": true
},
"accessControlList": {
"users": [
{
"id": "c590ed7f-b2b4-4e1f-ba7d-94173afdc9a3",
"permissions": [
"SELECT",
"ALTER"
]
},
{
"id": "30fca499-4abc-4469-7142-fc8dd29acac8",
"permissions": [
"SELECT",
"ALTER",
"MANAGE_GRANTS"
]
}
],
"roles": [
{
"id": "76a9884b-aea5-46d5-a73a-000edf23f390",
"permissions": [
"SELECT",
"ALTER"
]
}
]
},
"owner": {
"ownerId": "30fca499-4abc-4469-7142-fc8dd29acac8",
"ownerType": "USER"
},
"fields": [
{
"name": "_id",
"type": {
"name": "VARCHAR"
}
},
{
"name": "name",
"type": {
"name": "VARCHAR"
}
},
{
"name": "city",
"type": {
"name": "VARCHAR"
}
},
{
"name": "state",
"type": {
"name": "VARCHAR"
}
},
{
"name": "categories",
"type": {
"name": "LIST",
"subSchema": [
{
"type": {
"name": "VARCHAR"
}
}
]
}
},
{
"name": "review_count",
"type": {
"name": "BIGINT"
}
},
{
"name": "stars",
"type": {
"name": "DOUBLE"
}
},
{
"name": "attributes",
"type": {
"name": "STRUCT",
"subSchema": [
{
"name": "Parking",
"type": {
"name": "STRUCT",
"subSchema": [
{
"name": "garage",
"type": {
"name": "BOOLEAN"
}
},
{
"name": "street",
"type": {
"name": "BOOLEAN"
}
},
{
"name": "lot",
"type": {
"name": "BOOLEAN"
}
},
{
"name": "valet",
"type": {
"name": "BOOLEAN"
}
}
]
}
},
{
"name": "Accepts Credit Cards",
"type": {
"name": "BOOLEAN"
}
},
{
"name": "Wheelchair Accessible",
"type": {
"name": "BOOLEAN"
}
},
{
"name": "Price Range",
"type": {
"name": "BIGINT"
}
}
]
}
},
{
"name": "date",
"type": {
"name": "VARCHAR"
}
}
],
"approximateStatisticsAllowed": false
}
Response Status Codes
{{< api-responses file="data/software-api/table.json" data="retrieveTableIdResponseCodes" >}}
Retrieving a Table by Path
Retrieve a table by specifying the table's path.
Method and URLGET /api/v3/catalog/by-path/{path}
Parameters
{{< api-section file="data/software-api/table.json" data="retrieveTablePathParameters" >}}
Example Request
curl -X GET 'https://{hostname}/api/v3/catalog/by-path/Samples/samples.dremio.com/Dremio%20University/restaurant_reviews.parquet' \
--header 'Authorization: _dremio{tokenstring}' \
--header 'Content-Type: application/json'
{
"entityType": "dataset",
"id": "c9c11d32-0576-4200-5a5b-8c7229cb3d72",
"type": "PHYSICAL_DATASET",
"path": [
"Samples",
"samples.dremio.com",
"Dremio University",
"restaurant_reviews.parquet"
],
"createdAt": "2023-01-13T19:52:01.894Z",
"tag": "cb2905bb-39c0-497f-ae74-4c310d534f25",
"format": {
"type": "Parquet",
"name": "restaurant_reviews.parquet",
"fullPath": [
"Samples",
"samples.dremio.com",
"Dremio University",
"restaurant_reviews.parquet"
],
"ctime": 0,
"isFolder": false,
"location": "/samples.dremio.com/Dremio University/restaurant_reviews.parquet",
"autoCorrectCorruptDates": true
},
"accessControlList": {
"users": [
{
"id": "c590ed7f-b2b4-4e1f-ba7d-94173afdc9a3",
"permissions": [
"SELECT",
"ALTER"
]
},
{
"id": "30fca499-4abc-4469-7142-fc8dd29acac8",
"permissions": [
"SELECT",
"ALTER",
"MANAGE_GRANTS"
]
}
],
"roles": [
{
"id": "76a9884b-aea5-46d5-a73a-000edf23f390",
"permissions": [
"SELECT",
"ALTER"
]
}
]
},
"owner": {
"ownerId": "30fca499-4abc-4469-7142-fc8dd29acac8",
"ownerType": "USER"
},
"fields": [
{
"name": "_id",
"type": {
"name": "VARCHAR"
}
},
{
"name": "name",
"type": {
"name": "VARCHAR"
}
},
{
"name": "city",
"type": {
"name": "VARCHAR"
}
},
{
"name": "state",
"type": {
"name": "VARCHAR"
}
},
{
"name": "categories",
"type": {
"name": "LIST",
"subSchema": [
{
"type": {
"name": "VARCHAR"
}
}
]
}
},
{
"name": "review_count",
"type": {
"name": "BIGINT"
}
},
{
"name": "stars",
"type": {
"name": "DOUBLE"
}
},
{
"name": "attributes",
"type": {
"name": "STRUCT",
"subSchema": [
{
"name": "Parking",
"type": {
"name": "STRUCT",
"subSchema": [
{
"name": "garage",
"type": {
"name": "BOOLEAN"
}
},
{
"name": "street",
"type": {
"name": "BOOLEAN"
}
},
{
"name": "lot",
"type": {
"name": "BOOLEAN"
}
},
{
"name": "valet",
"type": {
"name": "BOOLEAN"
}
}
]
}
},
{
"name": "Accepts Credit Cards",
"type": {
"name": "BOOLEAN"
}
},
{
"name": "Wheelchair Accessible",
"type": {
"name": "BOOLEAN"
}
},
{
"name": "Price Range",
"type": {
"name": "BIGINT"
}
}
]
}
},
{
"name": "date",
"type": {
"name": "VARCHAR"
}
}
],
"approximateStatisticsAllowed": false
}
Response Status Codes
{{< api-responses file="data/software-api/table.json" data="retrieveTablePathResponseCodes" >}}
Updating a Table
Update the specified table in Dremio.
Method and URLPUT /api/v3/catalog/{id}
Parameters
{{< api-section file="data/software-api/table.json" data="updateTableParameters" >}}
format
{{< api-section file="data/software-api/table.json" data="updateFormatObjectParameters" >}}
accessControlList
{{< api-section file="data/software-api/table.json" data="updateAccessControlListObjectParameters" >}}
users and roles
{{< api-section file="data/software-api/table.json" data="updateUsersRolesAccessControlParameters" >}}
Example Request
curl -X PUT 'https://{hostname}/api/v3/catalog/dba1e4fe-6351-44d2-a3e0-7aa20e782bf3' \
--header 'Authorization: _dremio{tokenstring}' \
--header 'Content-Type: application/json' \
--data-raw '{
"entityType": "dataset",
"id": "dba1e4fe-6351-44d2-a3e0-7aa20e782bf3",
"path": [
"Samples",
"samples.dremio.com",
"Dremio University",
"airbnb_listings.csv"
],
"type": "PHYSICAL_DATASET",
"format": {
"type": "Text",
"fieldDelimiter": ",",
"skipFirstLine": false,
"extractHeader": true,
"quote": "\"",
"comment": "#",
"escape": "\"",
"lineDelimiter": "\r\n",
"autoGenerateColumnNames": true,
"trimHeader": true
}
}'
{
"entityType": "dataset",
"id": "dba1e4fe-6351-44d2-a3e0-7aa20e782bf3",
"type": "PHYSICAL_DATASET",
"path": [
"Samples",
"samples.dremio.com",
"Dremio University",
"airbnb_listings.csv"
],
"createdAt": "2023-01-23T21:26:59.568Z",
"tag": "fc1707df-35a1-45c1-87d7-5f66fb11a729",
"format": {
"type": "Text",
"ctime": 0,
"isFolder": false,
"location": "/samples.dremio.com/Dremio University/airbnb_listings.csv",
"fieldDelimiter": ",",
"skipFirstLine": false,
"extractHeader": true,
"quote": "\"",
"comment": "#",
"escape": "\"",
"lineDelimiter": "\r\n",
"autoGenerateColumnNames": true,
"trimHeader": true
},
"accessControlList": {},
"owner": {
"ownerId": "c590ed7f-7142-4e1f-ba7d-94173afdc9a3",
"ownerType": "USER"
},
"fields": [
{
"name": "id",
"type": {
"name": "VARCHAR"
}
},
{
"name": "listing_url",
"type": {
"name": "VARCHAR"
}
},
{
"name": "scrape_id",
"type": {
"name": "VARCHAR"
}
},
{
"name": "last_scraped",
"type": {
"name": "VARCHAR"
}
},
{
"name": "name",
"type": {
"name": "VARCHAR"
}
},
{
"name": "summary",
"type": {
"name": "VARCHAR"
}
},
{
"name": "reviews_per_month",
"type": {
"name": "VARCHAR"
}
}
],
"approximateStatisticsAllowed": false
}
Response Status Codes
{{< api-responses file="data/software-api/table.json" data="updateTableResponseCodes" >}}
Refreshing a Table
Refresh the reflections associated with the specified table.
Refreshing a table's reflections does not refresh its metadata. Read Refreshing Metadata to learn how to refresh table metadata. Read Refreshing Reflections for more information about refreshing reflections.
POST /api/v3/catalog/{id}/refresh
Parameters
{{< api-section file="data/software-api/table.json" data="refreshTableParameters" >}}
Example Request
curl -X POST 'https://{hostname}/api/v3/catalog/c9c11d32-0576-4200-5a5b-8c7229cb3d72/refresh' \
-H 'Authorization: _dremio{tokenstring}' \
-H 'Content-Type: application/json'
No response
Response Status Codes
{{< api-responses file="data/software-api/table.json" data="refreshTableResponseCodes" >}}
Reverting a Table to a File or Folder
Revert a table in a source to change the data in the table back to its original format, file or folder. For more information, read Formatting Data to a Table and Removing Formatting on Data
If a table is saved in your home space, the revert request will delete the table entirely. The revert endpoint only changes a table back to a file or folder if the table is saved in a source.
DELETE /api/v3/catalog/{id}
Parameters
{{< api-section file="data/software-api/table.json" data="revertTableParameters" >}}
Example Request
curl -X DELETE 'https://{hostname}/api/v3/catalog/c9c11d32-0576-4200-5a5b-8c7229cb3d72' \
-H 'Authorization: _dremio{tokenstring}' \
-H 'Content-Type: application/json'
No response
Response Status Codes
{{< api-responses file="data/software-api/table.json" data="revertTableResponseCodes" >}}