Scripts

Use the Scripts API to manage scripts, retrieve the contents of scripts for use in a scheduler, retrieve and update privileges on scripts, and migrate scripts between different environments.

Scripts Object

{
  "total": 3,
  "data": [
    {
      "id": "74cfddfd-cb0b-4b2f-b555-cb8b827fec1e",
      "name": "newScript",
      "content": "VACUUM CATALOG azure_test_3",
      "context": [
        "azure_test_3"
      ],
      "owner": "8be516f3-04c4-4d19-824d-5a70b3c4442e",
      "createdAt": "2024-05-24T17:42:00.304Z",
      "createdBy": "a97c694f-1e55-4f34-91aa-97f99fee802e",
      "modifiedAt": "2024-05-24T17:42:00.304Z",
      "modifiedBy": "a97c694f-1e55-4f34-91aa-97f99fee802e"
    },
    {
      "id": "37dab994-3f1f-4de7-b2e7-49cb5ff0b395",
      "name": "tmp_testing_04-15-24",
      "content": "-- create table accounting_storage.\"tmp.dremio.com\".test1(id INT);\n-- refresh dataset test1\nalter table test1 REFRESH METADATA;",
      "context": [
        "accounting_storage",
        "tmp.dremio.com"
      ],
      "owner": "ba92bf87-174d-422e-becb-d526757c8099",
      "createdAt": "2024-04-15T10:38:31.433Z",
      "createdBy": "ba92bf87-174d-422e-becb-d526757c8099",
      "modifiedAt": "2024-04-15T11:03:27.542Z",
      "modifiedBy": "ba92bf87-174d-422e-becb-d526757c8099"
    },
    {
      "id": "02fef13e-cedd-46ac-b5bf-abcdcd092146",
      "name": "pop10000_A",
      "content": "SELECT * FROM Samples.\"samples.dremio.com\".\"zips.json\" WHERE pop > 10000 AND STARTS_WITH(city, 'A');\n\nCREATE OR REPLACE VIEW myView AS\nSELECT city, state, pop FROM Samples.\"samples.dremio.com\".\"zips.json\"\nWHERE pop > 10000;\n\nCREATE OR REPLACE VIEW myView2 AS\nSELECT * FROM myView\nWHERE STARTS_WITH(city, 'A');\n\nALTER TABLE myView2 \nCREATE RAW REFLECTION myReflection \nUSING DISPLAY(city, state, pop);\n\nSELECT * FROM myView2;\n\nCREATE OR REPLACE FUNCTION isMA(state VARCHAR)\n    RETURNS BOOLEAN\n    RETURN SELECT state = 'MA';\n\nALTER TABLE myView ADD ROW ACCESS POLICY isMA(\"state\");\n\nSELECT * FROM myView2;\n\n// Refresh the reflection\n\nSELECT * FROM myView2;",
      "context": [
        "dev"
      ],
      "owner": "7a92baf7-646a-4bc5-b0f4-eaf18d0a9921",
      "createdAt": "2024-05-16T18:08:06.363Z",
      "createdBy": "7a92baf7-646a-4bc5-b0f4-eaf18d0a9921",
      "modifiedAt": "2024-05-16T18:31:22.593Z",
      "modifiedBy": "7a92baf7-646a-4bc5-b0f4-eaf18d0a9921"
    }
  ]
}

Scripts Attributes

total Integer

Total number of scripts in the project.

Example: 3

data Array of Object

List of the scripts in the project, with an individual object representing each script.

Attributes of Objects in the `data` Array

id String (UUID)

Unique identifier of the script. Generated by Dremio and immutable.

Example: 74cfddfd-cb0b-4b2f-b555-cb8b827fec1e

name String

User-provided name of the script.

Example: newScript

content String

The script's SQL.

Example: VACUUM CATALOG azure_test_3

context Array of String

Path where the SQL query specified in the content attribute runs. If no context is specified for the script, the value is an empty array.

Example: ["azure_test_3"]

owner String

Unique identifier for the user who owns the script.

Example: 8be516f3-04c4-4d19-824d-5a70b3c4442e

createdAt String

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

Example: 2024-05-24T17:42:00.304Z

createdBy String

The unique identifier for the user who created the script.

Example: a97c694f-1e55-4f34-91aa-97f99fee802e

modifiedAt String

Date and time that the script was last modified. In UTC format.

Example: 2024-05-24T17:42:00.304Z

modifiedBy String

The unique identifier for the user who last modified the script.

Example: a97c694f-1e55-4f34-91aa-97f99fee802e

Creating a Script

Create a script.

Method and URL

POST /v0/api/projects/{project-id}/scripts

Parameters

project-id Path String (UUID)

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

Example: 3ae2d8b7-d436-4454-b1c6-438e70f11fe1

name Body String

Name to use for the script.

Example: newScript

content Body String

The SQL for the script.

Example: VACUUM CATALOG azure_test_3

context Body Array of String Optional

Path where the SQL query specified in the content attribute should run.

Example: ["azure_test_3"]

owner Body String Optional

Unique identifier for the user who should own the script. Default is the ID for the user who creates the script.

Example: 8be516f3-04c4-4d19-824d-5a70b3c4442e

Example Request

curl -X POST 'https://api.dremio.cloud/v0/api/projects/3ae2d8b7-d436-4454-b1c6-438e70f11fe1/scripts' \
--header 'Authorization: Bearer <PersonalAccessToken>' \
--header 'Content-Type: application/json' \
--data-raw '{
  "name": "newScript",
  "content": "VACUUM CATALOG azure_test_3",
  "context": [
    "azure_test_3"
  ],
  "owner": "8be516f3-04c4-4d19-824d-5a70b3c4442e"
}'

Example Response

{
  "id": "f873a72e-12a5-4537-a393-f9675da7c5f8",
  "name": "newScript",
  "content": "VACUUM CATALOG azure_test_3",
  "context": [
    "azure_test_3"
  ],
  "owner": "8be516f3-04c4-4d19-824d-5a70b3c4442e",
  "createdAt": "2024-05-24T17:42:00.304Z",
  "createdBy": "a97c694f-1e55-4f34-91aa-97f99fee802e",
  "modifiedAt": "2024-05-24T17:42:00.304Z",
  "modifiedBy": "a97c694f-1e55-4f34-91aa-97f99fee802e"
}

Response Status Codes

200   OK

400   Bad Request

401   Unauthorized

Retrieving All Scripts

Retrieve a list of all scripts in the project.

Method and URL

GET /v0/api/projects/{project-id}/scripts/

Parameters

project-id Path String (UUID)

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

Example: 3ae2d8b7-d436-4454-b1c6-438e70f11fe1

createdBy Query String Optional

Unique identifier for the user who created the scripts you want to retrieve. For more information, read createdBy Query Parameter.

maxResults Query Integer Optional

Maximum number of scripts to return in the response. Maximum valid value is 100. Default is 25. For more information, read maxResults Query Parameter.

offset Query Integer Optional

Number of rows to skip for pagination. Default is 0. Read limit and offset Query Parameters for usage examples.

orderBy Query String Optional

Organize the response in ascending (default) or descending order by name, createdAt, or modifiedAt. To specify descending order, precede the orderBy value with a - character. To organize the response by more than one attribute, use a comma-separated list: ?orderBy=createdAt,name. For more information, read orderBy Query Parameter.

ownedBy Query String Optional

Unique identifier for the user who owns the scripts you want to retrieve. For more information, read ownedBy Query Parameter.

search Query String Optional

The string for which to search the values of the name attributes in scripts. Read search Query Parameter for usage examples.

Example Request

curl -X GET 'https://api.dremio.cloud/v0/api/projects/3ae2d8b7-d436-4454-b1c6-438e70f11fe1/scripts' \
--header 'Authorization: Bearer <PersonalAccessToken>' \
--header 'Content-Type: application/json'

Example Response

{
  "total": 3,
  "data": [
    {
      "id": "74cfddfd-cb0b-4b2f-b555-cb8b827fec1e",
      "name": "newScript",
      "content": "VACUUM CATALOG azure_test_3",
      "context": [
        "azure_test_3"
      ],
      "owner": "8be516f3-04c4-4d19-824d-5a70b3c4442e",
      "createdAt": "2024-05-24T17:42:00.304Z",
      "createdBy": "a97c694f-1e55-4f34-91aa-97f99fee802e",
      "modifiedAt": "2024-05-24T17:42:00.304Z",
      "modifiedBy": "a97c694f-1e55-4f34-91aa-97f99fee802e"
    },
    {
      "id": "37dab994-3f1f-4de7-b2e7-49cb5ff0b395",
      "name": "tmp_testing_04-15-24",
      "content": "-- create table accounting_storage.\"tmp.dremio.com\".test1(id INT);\n-- refresh dataset test1\nalter table test1 REFRESH METADATA;",
      "context": [
        "accounting_storage",
        "tmp.dremio.com"
      ],
      "owner": "ba92bf87-174d-422e-becb-d526757c8099",
      "createdAt": "2024-04-15T10:38:31.433Z",
      "createdBy": "ba92bf87-174d-422e-becb-d526757c8099",
      "modifiedAt": "2024-04-15T11:03:27.542Z",
      "modifiedBy": "ba92bf87-174d-422e-becb-d526757c8099"
    },
    {
      "id": "02fef13e-cedd-46ac-b5bf-abcdcd092146",
      "name": "pop10000_A",
      "content": "SELECT * FROM Samples.\"samples.dremio.com\".\"zips.json\" WHERE pop > 10000 AND STARTS_WITH(city, 'A');\n\nCREATE OR REPLACE VIEW myView AS\nSELECT city, state, pop FROM Samples.\"samples.dremio.com\".\"zips.json\"\nWHERE pop > 10000;\n\nCREATE OR REPLACE VIEW myView2 AS\nSELECT * FROM myView\nWHERE STARTS_WITH(city, 'A');\n\nALTER TABLE myView2 \nCREATE RAW REFLECTION myReflection \nUSING DISPLAY(city, state, pop);\n\nSELECT * FROM myView2;\n\nCREATE OR REPLACE FUNCTION isMA(state VARCHAR)\n    RETURNS BOOLEAN\n    RETURN SELECT state = 'MA';\n\nALTER TABLE myView ADD ROW ACCESS POLICY isMA(\"state\");\n\nSELECT * FROM myView2;\n\n// Refresh the reflection\n\nSELECT * FROM myView2;",
      "context": [
        "@dev"
      ],
      "owner": "7a92baf7-646a-4bc5-b0f4-eaf18d0a9921",
      "createdAt": "2024-05-16T18:08:06.363Z",
      "createdBy": "7a92baf7-646a-4bc5-b0f4-eaf18d0a9921",
      "modifiedAt": "2024-05-16T18:31:22.593Z",
      "modifiedBy": "7a92baf7-646a-4bc5-b0f4-eaf18d0a9921"
    }
  ]
}

Response Status Codes

200 OK

401 Unauthorized

Retrieving a Script by ID

Retrieve the specified script.

Method and URL

GET /v0/api/projects/{project-id}/scripts/{id}

Parameters

project-id Path String (UUID)

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

Example: 3ae2d8b7-d436-4454-b1c6-438e70f11fe1

id Path String (UUID)

Unique identifier of the script you want to retrieve.

Example: 74cfddfd-cb0b-4b2f-b555-cb8b827fec1e

Example Request

curl -X GET 'https://api.dremio.cloud/v0/api/projects/3ae2d8b7-d436-4454-b1c6-438e70f11fe1/scripts/74cfddfd-cb0b-4b2f-b555-cb8b827fec1e' \
--header 'Authorization: Bearer <PersonalAccessToken>' \
--header 'Content-Type: application/json'

Example Response

{
  "id": "74cfddfd-cb0b-4b2f-b555-cb8b827fec1e",
  "name": "newScript",
  "content": "VACUUM CATALOG azure_test_3",
  "context": [
    "azure_test_3"
  ],
  "owner": "8be516f3-04c4-4d19-824d-5a70b3c4442e",
  "createdAt": "2024-05-24T17:42:00.304Z",
  "createdBy": "a97c694f-1e55-4f34-91aa-97f99fee802e",
  "modifiedAt": "2024-05-24T17:42:00.304Z",
  "modifiedBy": "a97c694f-1e55-4f34-91aa-97f99fee802e"

Response Status Codes

200   OK

401   Unauthorized

404   Not Found

Updating a Script

Update the specified script.

Method and URL

PATCH /v0/api/projects/{project-id}/scripts/{id}

Parameters

project-id Path String (UUID)

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

Example: 3ae2d8b7-d436-4454-b1c6-438e70f11fe1

id Path String (UUID)

Unique identifier of the script.

Example: 74cfddfd-cb0b-4b2f-b555-cb8b827fec1e

name Body String Optional

User-provided name to use for the script.

Example: updatedScript

content Body Array of String Optional

The updated SQL for the script.

Example: SELECT * FROM Samples."samples.dremio.com"."zips.json"

context Body String Optional

Path where the SQL query specified in the content attribute should run.

Example: ["azure_test_3","secondScriptsFolder"]

owner Body String Optional

Unique identifier for the user who should own the script.

Example: a97c694f-1e55-4f34-91aa-97f99fee802e

Example Request

curl -X PATCH 'https://api.dremio.cloud/v0/api/projects/3ae2d8b7-d436-4454-b1c6-438e70f11fe1/scripts/74cfddfd-cb0b-4b2f-b555-cb8b827fec1e' \
--header 'Authorization: Bearer <PersonalAccessToken>' \
--header 'Content-Type: application/json' \
--data-raw '{
  "name": "updatedScript",
  "content": "SELECT * FROM Samples."samples.dremio.com"."zips.json"",
  "context": [
    "azure_test_3",
    "secondScriptsFolder"
  ],
  "owner": "a97c694f-1e55-4f34-91aa-97f99fee802e"
}'

Example Response

{
  "id": "74cfddfd-cb0b-4b2f-b555-cb8b827fec1e",
  "name": "updatedScript",
  "content": "SELECT * FROM Samples.\"samples.dremio.com\".\"zips.json\"",
  "context": [
    "azure_test_3",
    "secondScriptsFolder"
  ],
  "owner": "a97c694f-1e55-4f34-91aa-97f99fee802e",
  "createdAt": "2024-05-24T17:42:00.304Z",
  "createdBy": "a97c694f-1e55-4f34-91aa-97f99fee802e",
  "modifiedAt": "2024-05-24T18:56:59.409Z",
  "modifiedBy": "a97c694f-1e55-4f34-91aa-97f99fee802e"
}

Response Status Codes

200   OK

400   Bad Request

401   Unauthorized

404   Not Found

Deleting a Script

Delete the specified script.

Method and URL

DELETE /v0/api/projects/{project-id}/scripts/{id}

Parameters

project-id Path String (UUID)

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

Example: 3ae2d8b7-d436-4454-b1c6-438e70f11fe1

id Path String (UUID)

Unique identifier of the script that you want to delete.

Example: 74cfddfd-cb0b-4b2f-b555-cb8b827fec1e

Example Request

curl -X DELETE 'https://api.dremio.cloud/v0/api/projects/3ae2d8b7-d436-4454-b1c6-438e70f11fe1/scripts/74cfddfd-cb0b-4b2f-b555-cb8b827fec1e' \
--header 'Authorization: Bearer <PersonalAccessToken>' \
--header 'Content-Type: application/json'

Example Response

No response

Response Status Codes

204   No Content

401   Unauthorized

404   Not Found

Deleting a Group of Scripts

Delete the listed group of scripts.

Method and URL

POST /v0/api/projects/{project-id}/scripts:batchDelete

Parameters

project-id Path String (UUID)

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

Example: 3ae2d8b7-d436-4454-b1c6-438e70f11fe1

ids Body Array of String

Array of unique identifiers of the scripts that you want to delete.

Example: ["74cfddfd-cb0b-4b2f-b555-cb8b827fec1e","37dab994-3f1f-4de7-b2e7-49cb5ff0b395","02fef13e-cedd-46ac-b5bf-abcdcd092146"]

Example Request

curl -X POST 'https://api.dremio.cloud/v0/api/projects/3ae2d8b7-d436-4454-b1c6-438e70f11fe1/scripts:batchDelete' \
--header 'Authorization: Bearer <PersonalAccessToken>' \
--header 'Content-Type: application/json' \
--data-raw '{
  "ids": [
    "74cfddfd-cb0b-4b2f-b555-cb8b827fec1e",
    "37dab994-3f1f-4de7-b2e7-49cb5ff0b395",
    "02fef13e-cedd-46ac-b5bf-abcdcd092146"
  ]
}'

Example Response

{
  "unauthorizedIds": [],
  "notFoundIds": [],
  "otherErrorIds": []
}

Response Status Codes

200   OK

401   Unauthorized

404   Not Found

Retrieving Privilege Information for a Script

Retrieve information about the privileges granted on the specified script.

Method and URL

GET /v0/api/projects/{project-id}/scripts/{id}/grants

Parameters

project-id Path String (UUID)

Unique identifier for the project that contains the script whose privilege information you want to retrieve.

Example: 3ae2d8b7-d436-4454-b1c6-438e70f11fe1

id Path String (UUID)

Unique identifier of the script whose privilege information you want to retrieve.

Example: 74cfddfd-cb0b-4b2f-b555-cb8b827fec1e

Example Request

curl -X GET 'https://api.dremio.cloud/v0/api/projects/3ae2d8b7-d436-4454-b1c6-438e70f11fe1/scripts/74cfddfd-cb0b-4b2f-b555-cb8b827fec1e/grants' \
--header 'Authorization: Bearer <PersonalAccessToken>' \
--header 'Content-Type: application/json'

Example Response

{
  "users": [
    {
      "granteeId": "8be516f3-04c4-4d19-824d-5a70b3c4442e",
      "privileges": [
        "VIEW",
        "MODIFY",
        "DELETE",
        "MANAGE_GRANTS"
      ]
    },
    {
      "granteeId": "a97c694f-1e55-4f34-91aa-97f99fee802e",
      "privileges": [
        "VIEW",
        "MODIFY",
        "DELETE",
        "MANAGE_GRANTS"
      ]
    }
  ],
  "roles": []
}

Response Status Codes

200   OK

401   Unauthorized

404   Not Found

Updating Privileges Granted on a Script

Update the privileges that are granted on the specified script.

Method and URL

PUT /v0/api/projects/{project-id}/scripts/{id}/grants

Parameters

project-id Path String (UUID)

Unique identifier for the project that contains the script whose privilege grants you want to update.

Example: 3ae2d8b7-d436-4454-b1c6-438e70f11fe1

id Path String (UUID)

Unique identifier of the script whose privilege grants you want to update.

Example: 74cfddfd-cb0b-4b2f-b555-cb8b827fec1e

users Body Array of Object Optional

Array of objects that specify which users should have privileges on the script, as well as each user's specific privileges.

roles Body Array of Object Optional

Array of objects that specify which roles should have privileges on the script, as well as each role's specific privileges.

Attributes of Objects in the `users` Array

granteeId Body String Optional

Unique identifier for the user for whom you want to add or update privileges.

Example: 8be516f3-04c4-4d19-824d-5a70b3c4442e

privileges Body Array of String Optional

The array of privileges you want to add or update for the user.

Enum: VIEW, MODIFY, DELETE, MANAGE_GRANTS

Example: ["VIEW","MODIFY","DELETE"]

Attributes of Objects in the `roles` Array

granteeId Body String Optional

Unique identifier for the role for which you want to add or update privileges.

Example: 6a1725a3-5721-44e3-b64f-0b39a35749ab

privileges Body Array of String Optional

The array of privileges you want to add or update for the role.

Enum: VIEW, MODIFY, DELETE, MANAGE_GRANTS

Example: ["VIEW","MODIFY","DELETE"]

Example Request

curl -X PUT 'https://api.dremio.cloud/v0/api/projects/3ae2d8b7-d436-4454-b1c6-438e70f11fe1/scripts/74cfddfd-cb0b-4b2f-b555-cb8b827fec1e/grants' \
--header 'Authorization: Bearer <PersonalAccessToken>' \
--header 'Content-Type: application/json' \
--data-raw '{
  "users": [
    {
      "granteeId": "8be516f3-04c4-4d19-824d-5a70b3c4442e",
      "privileges": [
        "VIEW",
        "MODIFY",
        "DELETE"
      ]
    }
  ],
  "roles": [
    {
      "granteeId": "6a1725a3-5721-44e3-b64f-0b39a35749ab",
      "privileges": [
        "VIEW",
        "MODIFY",
        "DELETE"
      ]
    }
  ]
}'

Example Response

{
  "users": [
    {
      "granteeId": "8be516f3-04c4-4d19-824d-5a70b3c4442e",
      "privileges": [
        "VIEW",
        "MODIFY",
        "DELETE"
      ]
    },
    {
      "granteeId": "a97c694f-1e55-4f34-91aa-97f99fee802e",
      "privileges": [
        "VIEW",
        "MODIFY",
        "DELETE",
        "MANAGE_GRANTS"
      ]
    }
  ],
  "roles": [
    {
      "granteeId": "6a1725a3-5721-44e3-b64f-0b39a35749ab",
      "privileges": [
        "VIEW",
        "MODIFY",
        "DELETE"
      ]
    }
  ]
}

Response Status Codes

200   OK

401   Unauthorized

404   Not Found

Scripts

Scripts Attributes​

Attributes of Objects in the data Array​

Creating a Script​

Parameters​

Response Status Codes​

Retrieving All Scripts​

Parameters​

Response Status Codes​

Retrieving a Script by ID​

Parameters​

Response Status Codes​

Updating a Script​

Parameters​

Response Status Codes​

Deleting a Script​

Parameters​

Response Status Codes​

Deleting a Group of Scripts​

Parameters​

Response Status Codes​

Retrieving Privilege Information for a Script​

Parameters​

Response Status Codes​

Updating Privileges Granted on a Script​

Parameters​

Attributes of Objects in the users Array​

Attributes of Objects in the roles Array​

Response Status Codes​

Scripts Attributes

Attributes of Objects in the `data` Array

Creating a Script

Parameters

Response Status Codes

Retrieving All Scripts

Parameters

Response Status Codes

Retrieving a Script by ID

Parameters

Response Status Codes

Updating a Script

Parameters

Response Status Codes

Deleting a Script

Parameters

Response Status Codes

Deleting a Group of Scripts

Parameters

Response Status Codes

Retrieving Privilege Information for a Script

Parameters

Response Status Codes

Updating Privileges Granted on a Script

Parameters

Attributes of Objects in the `users` Array

Attributes of Objects in the `roles` Array

Response Status Codes