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.

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)

UUID of the script.

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 (UUID)

UUID of the owner.

createdAt String

Timestamp when the script was created.

createdBy String (UUID)

UUID of the user who created the script.

modifiedAt String

Timestamp when the script was last modified.

modifiedBy String (UUID)

UUID of the user who last modified the script.

Create a Script

Method and URL

POST /v0/projects/{project_id}/scripts

Parameters

project_id Path String (UUID)

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 (UUID) Optional

UUID of the owner.

Example

Request

curl -X POST "https://api.dremio.cloud/v0/projects/$PROJECT_ID/scripts" \
-H "Authorization: Bearer $DREMIO_TOKEN" \
-H 'Content-Type: application/json' \
--data-raw '{
  "name": "newScript",
  "content": "VACUUM CATALOG azure_test_3",
  "context": [
    "azure_test_3"
  ],
  "owner": "8be516f3-04c4-4d19-824d-5a70b3c4442e"
}'

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

Retrieve All Scripts

Method and URL

GET /v0/projects/{project_id}/scripts/

Parameters

project_id Path String (UUID)

createdBy Query String Optional

Unique identifier of the user who created the scripts you want to retrieve. For more information, see the 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, see the maxResults query parameter.

offset Query Integer Optional

Number of rows to skip for pagination. Default is 0. See the offset query parameter 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, see the orderBy query parameter.

ownedBy Query String Optional

Unique identifier of the user who owns the scripts you want to retrieve. For more information, see the ownedBy query parameter.

search Query String Optional

The string for which to search the values of the name attributes in scripts. See the search query parameter for usage examples.

Example

Request

curl -X GET "https://api.dremio.cloud/v0/projects/$PROJECT_ID/scripts" \
-H "Authorization: Bearer $DREMIO_TOKEN" \
-H 'Content-Type: application/json'

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

Retrieve a Script by ID

Method and URL

GET /v0/projects/{project_id}/scripts/{id}

Parameters

project_id Path String (UUID)

id Path String (UUID)

UUID of the script you want to retrieve.

Example

Request

curl -X GET "https://api.dremio.cloud/v0/projects/$PROJECT_ID/scripts/$SCRIPT_ID" \
-H "Authorization: Bearer $DREMIO_TOKEN" \
-H 'Content-Type: application/json'

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

Update a Script

Method and URL

PATCH /v0/projects/{project_id}/scripts/{id}

Parameters

project_id Path String (UUID)

id Path String (UUID)

UUID of the script.

name Body String Optional

User-provided name to use for the script.

Example: updatedScript

content Body String Optional

The updated SQL for the script.

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

context Body Array of String Optional

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

Example:

[
  "azure_test_3",
  "secondScriptsFolder"
]

owner Body String (UUID) Optional

UUID of the owner.

Example

Request

curl -X PATCH "https://api.dremio.cloud/v0/projects/$PROJECT_ID/scripts/$SCRIPT_ID" \
-H "Authorization: Bearer $DREMIO_TOKEN" \
-H '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"
}'

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

Delete a Script

Method and URL

DELETE /v0/projects/{project_id}/scripts/{id}

Parameters

project_id Path String (UUID)

id Path String (UUID)

UUID of the script you want to delete.

Example

Request

curl -X DELETE "https://api.dremio.cloud/v0/projects/$PROJECT_ID/scripts/$SCRIPT_ID" \
-H "Authorization: Bearer $DREMIO_TOKEN" \
-H 'Content-Type: application/json'

A successful request returns an empty response body with HTTP status 204 No Content.

Response Status Codes

204   No Content

401   Unauthorized

404   Not Found

Delete a Group of Scripts

Method and URL

POST /v0/projects/{project_id}/scripts:batchDelete

Parameters

project_id Path String (UUID)

ids Body Array of String (UUID)

UUID array of the scripts 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/projects/$PROJECT_ID/scripts:batchDelete" \
-H "Authorization: Bearer $DREMIO_TOKEN" \
-H 'Content-Type: application/json' \
--data-raw '{
  "ids": [
    "74cfddfd-cb0b-4b2f-b555-cb8b827fec1e",
    "37dab994-3f1f-4de7-b2e7-49cb5ff0b395",
    "02fef13e-cedd-46ac-b5bf-abcdcd092146"
  ]
}'

Response

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

Response Status Codes

200   OK

401   Unauthorized

404   Not Found

Retrieve Privilege Information for a Script

Method and URL

GET /v0/projects/{project_id}/scripts/{id}/grants

Parameters

project_id Path String (UUID)

id Path String (UUID)

UUID of the script whose privilege information you want to retrieve.

Example

Request

curl -X GET "https://api.dremio.cloud/v0/projects/$PROJECT_ID/scripts/$SCRIPT_ID/grants" \
-H "Authorization: Bearer $DREMIO_TOKEN" \
-H 'Content-Type: application/json'

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

Update Privileges Granted on a Script

Method and URL

PUT /v0/projects/{project_id}/scripts/{id}/grants

Parameters

project_id Path String (UUID)

id Path String (UUID)

UUID of the script whose privilege grants you want to update.

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 (UUID) Optional

UUID of the user for whom you want to add or update privileges.

privileges Body Array of String Optional

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

Valid Values: VIEW, MODIFY, DELETE, MANAGE_GRANTS

Example:

[
  "VIEW",
  "MODIFY",
  "DELETE"
]

Attributes of Objects in the `roles` Array

granteeId Body String (UUID) Optional

UUID of the role for which you want to add or update privileges.

privileges Body Array of String Optional

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

Valid Values: VIEW, MODIFY, DELETE, MANAGE_GRANTS

Example:

[
  "VIEW",
  "MODIFY",
  "DELETE"
]

Example

Request

curl -X PUT "https://api.dremio.cloud/v0/projects/$PROJECT_ID/scripts/$SCRIPT_ID/grants" \
-H "Authorization: Bearer $DREMIO_TOKEN" \
-H '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"
      ]
    }
  ]
}'

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 Attributes​

Attributes of Objects in the data Array​

Create a Script​

Parameters​

Example​

Response Status Codes​

Retrieve All Scripts​

Parameters​

Example​

Response Status Codes​

Retrieve a Script by ID​

Parameters​

Example​

Response Status Codes​

Update a Script​

Parameters​

Example​

Response Status Codes​

Delete a Script​

Parameters​

Example​

Response Status Codes​

Delete a Group of Scripts​

Parameters​

Example​

Response Status Codes​

Retrieve Privilege Information for a Script​

Parameters​

Example​

Response Status Codes​

Update Privileges Granted on a Script​

Parameters​

Attributes of Objects in the users Array​

Attributes of Objects in the roles Array​

Example​

Response Status Codes​

Scripts Attributes

Attributes of Objects in the `data` Array

Create a Script

Parameters

Example

Response Status Codes

Retrieve All Scripts

Parameters

Example

Response Status Codes

Retrieve a Script by ID

Parameters

Example

Response Status Codes

Update a Script

Parameters

Example

Response Status Codes

Delete a Script

Parameters

Example

Response Status Codes

Delete a Group of Scripts

Parameters

Example

Response Status Codes

Retrieve Privilege Information for a Script

Parameters

Example

Response Status Codes

Update Privileges Granted on a Script

Parameters

Attributes of Objects in the `users` Array

Attributes of Objects in the `roles` Array

Example

Response Status Codes