Welcome to Dremio Cloud Documentation - Dremio > > > >

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

Type of the catalog object. For tables, the entityType is dataset.

Example dataset

Unique identifier of the table.

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

Type of dataset. For tables, the type is PHYSICAL_DATASET.

Example PHYSICAL_DATASET

Path of the table within Dremio, expressed as an array. The path consists of the source or space, followed by any folder and subfolders, followed by the table itself as the last item in the array.

Example [ "Samples", "samples.dremio.com", "Dremio University", "restaurant_reviews.parquet" ]

Date and time that the table was created, in UTC format.

Example 2023-01-13T19:52:01.894Z

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

Example cb2905bb-39c0-497f-ae74-4c310d534f25

Attributes that define the acceleration refresh policy for the table.

Table format attributes.

Enterprise only. Information about users and roles with access to the table and the specific privileges each user or role has. May include an array of users, an array of roles, or both, depending on the configured access and privileges. The accessControlList array is empty if table-specific access control privileges are not set.

Example { "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" ] } ] }

Enterprise-only. List of the privileges that you have on the table. Only appears in the response if the request URL includes the permissions query parameter. For more information, read Privileges.

Example [ "READ", "WRITE", "ALTER_REFLECTION", "SELECT", "ALTER", "VIEW_REFLECTION", "MODIFY", "MANAGE_GRANTS", "CREATE_TABLE", "DROP", "EXTERNAL_QUERY", "INSERT", "TRUNCATE", "DELETE", "UPDATE", "EXECUTE", "CREATE_SOURCE", "ALL" ]

Information about the table's owner.

Example { "ownerId": "30fca499-4abc-4469-7142-fc8dd29acac8", "ownerType": "USER" }

Attributes that represent the table schema.

If true, COUNT DISTINCT queries run on the table return approximate results. Otherwise, false.

Example false

accelerationRefreshPolicy

Refresh period for the data in all reflections for the table, in milliseconds.

Example 3600000

Maximum age allowed for reflection data used to accelerate queries, in milliseconds.

Example 10800000

Approach used for refreshing the data in reflections for the table. For more information, read Refreshing Reflections.

Enum FULL , INCREMENTAL

Example FULL

For INCREMENTAL refresh method, the field to refresh for the table. Used only if method is INCREMENTAL.

Example business_id

If the reflection never expires, the value is true. Otherwise, the value is false.

Example false

If the reflection never refreshes, the value is true. Otherwise, the value is false.

Example false

format

Type of data in the table.

Enum Delta , Excel , Iceberg , JSON , Parquet , Text , Unknown , XLS

Example Parquet

Table name. Dremio automatically duplicates the name of the origin file or folder to populate this value.

Example restaurant_reviews.parquet

Path of the table within Dremio, expressed as an array. The path consists of the source or space, followed by any folder and subfolders, followed by the table itself as the last item in the array.

Example [ "Samples", "samples.dremio.com", "Dremio University", "restaurant_reviews.parquet" ]

Not used. Has the value 0.

Example 0

If the value is true, the table was created from a folder. If the value is false, the table was created from a file.

Example false

Location, expressed as a string, where the table's metadata is stored within a Dremio source or space.

Example /samples.dremio.com/Dremio University/restaurant_reviews.parquet

Not used. Has the value HDFS.

Example HDFS

Information about data format for Parquet tables.

List of data format types in the table. Included only for Iceberg tables, and PARQUET is the only valid value.

Example [ "PARQUET" ]

For tables created from files that contain multiple sheets, the name of the sheet used to create the table.

Example location_1

For tables created from files, the value is true if Dremio extracted the table's column names from the first line of the file. Otherwise, the value is false.

Example false

For tables created from files, the value is true if Dremio expanded merged cells in the file when creating the table. Otherwise, the value is false.

Example true

Character used to indicate separate fields in the table. May be , for a comma (default), \t for a tab, | for a pipe, or a custom character.

Character used for quotation marks in the table. May be \" for a double quote (default), ' for a single quote, or a custom character.

Character used to indicate comments in the table. May be # for a number sign (default) or a custom character.

Character used to indicate an escape in the table. May be " for a double quote (default), ` for a back quote, \\ for a backward slash, or a custom character.

Character used to indicate separate lines in the table. May be \r\n for a carriage return and a new line (default), \n for a new line, or a custom character.

If Dremio skipped the first line in the file or folder when creating the table, the value is true. Otherwise, the value is false.

Example false

If Dremio used the existing columnn names in the file or folder for the table columns, the value is true. Otherwise, the value is false.

Example true

If Dremio trimmed column names to a specific number of characters when creating the table, the value is true. Otherwise, the value is false.

Example true

If Dremio automatically corrects corrupted date fields in the table, the value is true. Otherwise, the value is false.

Example true

accessControlList

Enterprise only. List of users with access to the table and the specific privileges each user should have.

Example [ { "id": "c590ed7f-b2b4-4e1f-ba7d-94173afdc9a3", "permissions": [ "SELECT", "ALTER" ] }, { "id": "30fca499-4abc-4469-7142-fc8dd29acac8", "permissions": [ "SELECT", "ALTER", "MANAGE_GRANTS" ] } ]

Enterprise only. List of roles whose members have access to the table and the specific privileges each role has.

Example [ { "id": "76a9884b-aea5-46d5-a73a-000edf23f390", "permissions": [ "SELECT", "ALTER" ] } ]

users and roles

Enterprise only. Unique identifier of the user or role with access to the table.

Example c590ed7f-b2b4-4e1f-ba7d-94173afdc9a3

Enterprise only. List of privileges the user or role has on the table. For more information, read Privileges.

Example [ "SELECT', "ALTER" ]

owner

Unique identifier of the table's owner.

Example 30fca499-4abc-4469-7142-fc8dd29acac8

Type of owner of the table.

Enum USER , ROLE

Example USER

fields

Name of the table field.

Example review_count

Information about the table field.

type

Name of the table 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 BIGINT

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

Example 38

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

Example 0

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 appears only for the STRUCT, LIST, and UNION types.

subSchema

Name for the subSchema object.

Example Parking

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

Example { "name": "BOOLEAN" }

parquetDataFormat

Type of data in the table. Within the parquetDataFormat object, the only valid type is Parquet.

Example Parquet

Not used. Has the value 0.

Example 0

If the value is true, the table was created from a folder. If the value is false, the table was created from a file.

Example true

If the value is true, Dremio automatically corrects corrupted date fields in the table. Otherwise, the value is false.

Example true

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.

note:

To format a folder, all files in the folder must be the same format.

Method and URL

POST /api/v3/catalog/{id}

Parameters

Unique identifier of the file or folder you want to format. The ID can be a UUID or a text path. If the ID is a text path, URI-encode the path to replace special characters with their UTF-8 equivalents: %3A for a colon; %2F for a forward slash, and %20 for a space. For example, if the ID value is dremio:/Samples/samples.dremio.com/Dremio University, the URI-encoded ID is dremio%3A%2FSamples%2Fsamples.dremio.com%2FDremio%20University.

Example c590ed7f-7142-4e1f-ba7d-94173afdc9a3

Type of the catalog object. To format a file or folder as a table, the entityType is dataset.

Path of the file or folder you want to format, expressed as an array. The path consists of the source or space, followed by any folder and subfolders, followed by the file or folder itself as the last item in the array. Get the path from the file or folder's children object in the response to a Folder request.

Example [ "Samples', "samples.dremio.com", "Dremio University", "restaurant_reviews.parquet" ]

Type of dataset. For tables, the type is PHYSICAL_DATASET.

Parameters that describe how to format the file or folder.

Enterprise only. Object used to specify which users and roles should have access to the table and the specific privileges each user or role should have. May include an array of users, an array of roles, or both.

format

Type of data in the file or folder. All files in the folder must be the same format.

Enum Delta , Excel , Iceberg , JSON , Parquet , Text , Unknown , XLS

If Dremio should skip the first line in the file or folder when creating the table, set to true. Otherwise, set to false (default). Optional for files or folders of the Excel and Text types.

Example true

If Dremio should extract the table's column names from the first line of the file, set to true. Otherwise, set to false (default). Optional for files or folders of the Excel and Text types.

Example true

If Dremio should expand merged cells in the file when creating the table, set to true. Otherwise, set to false (default). Optional for files or folders of the Excel type.

Example true

For tables created from Excel files that contain multiple sheets, the name of the sheet to use to create the table. Default is the first sheet in the file (for files that contain multiple sheets).

Example location_1

Character to use to indicate separate fields in the table. May be , for a comma (default), \t for a tab, | for a pipe, or a custom character. Optional for files or folders of the Text type.

Character to use for quotes in the table. May be \" for a double quote (default), ' for a single quote, or a custom character. Optional for files or folders of the Text type.

Character to use to indicate comments in the table. May be # for a number sign (default) or a custom character. Optional for files or folders of the Text type.

Character used to indicate an escape in the table. May be " for a double quote (default), ` for a back quote, \\ for a backward slash, or a custom character. Optional for files or folders of the Text type.

Character used to indicate separate lines in the table. May be \r\n for a carriage return and a new line (default), \n for a new line, or a custom character. Optional for files or folders of the Text type.

If Dremio should use the existing columnn names in the file or folder for the table columns, set to true (default). Otherwise, set to false. Optional for files or folders of the Text type.

Example true

If Dremio should trim column names to a specific number of characters when creating the table, set to true. Otherwise, set to false (default). Optional for files or folders of the Text type.

Example true

accessControlList

Enterprise only. List of users who should have access to the table and the specific privileges each user should have.

Enterprise only. List of roles whose members should have access to the table and the specific privileges each role should have.

Example [ { "id": "76a9884b-aea5-46d5-a73a-000edf23f390", "permissions": [ "SELECT", "ALTER" ] } ]

users and roles

Enterprise only. Unique identifier of the user or role who should have access to the table.

Example c590ed7f-b2b4-4e1f-ba7d-94173afdc9a3

Enterprise only. List of privileges the user or role should have on the table. For more information, read Privileges.

Example [ "SELECT', "ALTER" ]

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"
        ]
      }
    ]
  }
}'

Example Response

{
    "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
  }

Example Request for Excel format type

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"
    }
}'

Example Request for Text format type

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

Retrieving a Table by ID

Retrieve a table by specifying the table’s id value.

Method and URL

GET /api/v3/catalog/{id}

Parameters

Unique identifier of the table that you want to retrieve.

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

Include a non-default attribute in the response. The available value for the include query parameter is permissions. For more information, read include and exclude Query Parameters.

Example ?include=permissions

Example Request

curl -X GET 'https://{hostname}/api/v3/catalog/c9c11d32-0576-4200-5a5b-8c7229cb3d72' \
--header 'Authorization: _dremio{tokenstring}' \
--header 'Content-Type: application/json'

Example Response

{
    "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

Retrieving a Table by Path

Retrieve a table by specifying the table’s path.

Method and URL

GET /api/v3/catalog/by-path/{path}

Parameters

Table's location within Dremio, using forward slashes as separators. For example, for the "NYC-taxi-trips" table in the "samples.dremio.com" folder within the source "Samples," the path is Samples/samples.dremio.com/NYC-taxi-trips. If the name of any component in the path includes a special character for URLs, such as a space, replace the special character with its UTF-8 equivalent. For example, "Dremio University" should be Dremio%20University in the URL path.

Example Samples/samples.dremio.com/Dremio%20University/restaurant_reviews.parquet

Include a non-default attribute in the response. The available value for the include query parameter is permissions. For more information, read include and exclude Query Parameters.

Example ?include=permissions

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'

Example Response

{
    "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

Updating a Table

Update the specified table in Dremio.

Method and URL

PUT /api/v3/catalog/{id}

Parameters

Unique identifier of the table that you want to update.

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

Type of the catalog object. For tables, the entityType is dataset.

Unique identifier of the table that you want to update.

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

Path of the table that you want to update, expressed as an array. The path consists of the source or space, followed by any folder and subfolders, followed by the table itself as the last item in the array. Get the path from the table's children object in the response to a Folder request.

Example [ "Samples', "samples.dremio.com", "Dremio University", "restaurant_reviews.parquet" ]

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

Example cb2905bb-39c0-497f-ae74-4c310d534f25

Type of dataset. For tables, the type is PHYSICAL_DATASET.

Parameters that describe the table's format.

format

Type of data in the table.

Enum Delta , Excel , Iceberg , JSON , Parquet , Text , Unknown , XLS

If Dremio should skip the first line in the table, set to true. Otherwise, set to false (default). Optional for Excel and Text types.

Example true

If Dremio should extract the table's column names from the first line of the file, set to true. Otherwise, set to false (default). Optional for tables created from files or folders of the Excel and Text types.

Example true

If Dremio should expand merged cells in the table, set to true. Otherwise, set to false (default). Optional for tables created from files or folders of the Excel type.

Example true

Character to use to indicate separate fields in the table. May be , for a comma (default), \t for a tab, | for a pipe, or a custom character. Optional for tables created from files or folders of the Text type.

Character to use for quotes in the table. May be \" for a double quote (default), ' for a single quote, or a custom character. Optional for tables created from files or folders of the Text type.

Character to use to indicate comments for the table. May be # for a number sign (default) or a custom character. Optional for tables created from files or folders of the Text type.

Character to use to indicate an escape for the table. May be " for a double quote (default), ` for a back quote, \\ for a backward slash, or a custom character. Optional for tables created from files or folders of the Text type.

Character to use to indicate separate lines for the table. May be \r\n for a carriage return and a new line (default), \n for a new line, or a custom character. Optional for tables created from files or folders of the Text type.

If Dremio should use the existing columnn names for the table columns, set to true (default). Otherwise, set to false. Optional for tables created from files or folders of the Text type.

Example true

If Dremio should trim column names to a specific number of characters when updating the table, set to true. Otherwise, set to false (default). Optional for tables created from files or folders of the Text type.

Example true

accessControlList

Enterprise only. List of users who should have access to the table and the specific privileges each user should have.

Enterprise only. List of roles whose members should have access to the table and the specific privileges each role should have.

Example [ { "id": "76a9884b-aea5-46d5-a73a-000edf23f390", "permissions": [ "SELECT", "ALTER" ] } ]

users and roles

Enterprise only. Unique identifier of the user or role that should have access to the table.

Example c590ed7f-b2b4-4e1f-ba7d-94173afdc9a3

Enterprise only. List of privileges the user or role should have on the table. For more information, read Privileges.

Example [ "SELECT', "ALTER" ]

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
  }
}'

Example Response

{
    "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

Refreshing a Table

Refresh the reflections associated with the specified table.

note:

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.

Method and URL

POST /api/v3/catalog/{id}/refresh

Parameters

Unique identifier of the table that you want to refresh.

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

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'

Example Response

No response

Response Status Codes

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

note:

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.

Method and URL

DELETE /api/v3/catalog/{id}

Parameters

Unique identifier of the table that you want to revert to a file or folder.

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

Example Request

curl -X DELETE 'https://{hostname}/api/v3/catalog/c9c11d32-0576-4200-5a5b-8c7229cb3d72' \
-H 'Authorization: _dremio{tokenstring}' \
-H 'Content-Type: application/json'

Example Response

No response

Response Status Codes

Was this page helpful?

Glad to hear it! Thank you for your feedback. Sorry to hear that. Thank you for your feedback.