User-Defined Function

Use the Catalog API to retrieve information about user-defined functions (UDFs), as well as to create, update, and delete UDFs.

User-Defined Function Object

{
  "entityType": "function",
  "id": "1568aa06-4eac-48cf-bc30-2aa3053c2840",
  "path": [
    "mySource",
    "team_folder",
    "test_subfolder",
    "filter_domain_orderdates"
  ],
  "tag": "b142bed0-6dcb-44fe-83fa-30be8f07f283",
  "createdAt": "2024-08-01T20:20:38.547Z",
  "lastModified": "2024-08-01T20:20:38.547Z",
  "isScalar": false,
  "functionArgList": "\"domain\" CHARACTER VARYING, \"orderdate\" DATE",
  "functionBody": "SELECT \"name\", \"email\", \"order_date\" FROM \"customer_data\" WHERE LOWER(\"email\") LIKE '%' || LOWER(domain) AND \"order_date\" >= orderdate",
  "returnType": "\"name\" CHARACTER VARYING, \"email\" CHARACTER VARYING, \"order_date\" DATE",
  "accessControlList": {
    "users": [
      {
        "id": "737a038f-c6cd-4fd3-a77a-59f692727ba5",
        "permissions": [
          "WRITE",
          "SELECT"
        ]
      }
    ],
    "roles": [
      {
        "id": "0f2d94e0-bb5e-4c03-8c6f-62d379d10889",
        "permissions": [
          "WRITE",
          "SELECT"
        ]
      }
    ]
  },
  "permissions": [],
  "owner": {
    "ownerId": "4740ab48-39c6-434c-9086-8f6e52e65349",
    "ownerType": "USER"
  }
}

User-Defined Function Attributes

entityType String

Type of the catalog object. For user-defined functions, the entityType is function.

id String (UUID)

UUID of the user-defined function.

path Array of String

Path of the user-defined function within Dremio, expressed as an array. The path consists of the source or catalog, followed by any folders and subfolders, followed by the name of the function itself as the last item in the array.

Example:

[
  "mySource",
  "team_folder",
  "test_subfolder",
  "filter_domain_orderdates"
]

tag String (UUID)

UUID of the version of the user-defined function. Dremio changes the tag whenever the function changes and uses the tag to ensure that PUT requests apply to the most recent version of the function.

createdAt String

Timestamp when the object was created.

lastModified String

Timestamp when the object was last modified.

isScalar Boolean

If the user-defined function is a scalar function, true. If the user-defined function is a tabular function, false.

functionArgList String

The user-defined function's arguments and their data types. If the function includes multiple arguments, the arguments are separated with a comma.

Example: \"domain\" CHARACTER VARYING, \"orderdate\" DATE

functionBody String

The statement that the user-defined function executes.

Example: SELECT \"name\", \"email\", \"order_date\" FROM \"customer_data\" WHERE LOWER(\"email\") LIKE '%' || LOWER(domain) AND \"order_date\" >= orderdate

returnType String

The data type of the result that the function returns (for scalar functions) or of each column that the function returns, separated by commas (for tabular functions).

Example: \"name\" CHARACTER VARYING, \"email\" CHARACTER VARYING, \"order_date\" DATE

accessControlList Object

Information about users and roles with access to the user-defined function 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 is empty if function-specific access control privileges are not set.

Example:

{
  "users": [{
    "id": "737a038f-c6cd-4fd3-a77a-59f692727ba5",
    "permissions": ["WRITE","SELECT"]
  }],
  "roles": [{
    "id": "0f2d94e0-bb5e-4c03-8c6f-62d379d10889",
    "permissions": ["WRITE","SELECT"]
  }]
}

permissions Array of String

List of the privileges that you have on the user-defined function. Only appears in the response if the request URL includes the permissions query parameter. For more information, read User-Defined Function Privileges.

owner Object

Information about the user-defined function's owner.

Example:

{
  "ownerId": "4740ab48-39c6-434c-9086-8f6e52e65349",
  "ownerType": "USER"
}

Attributes of the `accessControlList` Object

users Array of Object

List of users with access to the user-defined function and the specific privileges each user has.

Example:

[
  {
    "id": "737a038f-c6cd-4fd3-a77a-59f692727ba5",
    "permissions": [
      "WRITE",
      "SELECT"
    ]
  }
]

roles Array of Object

List of roles whose members have access to the user-defined function and the specific privileges each role has.

Example:

[
  {
    "id": "0f2d94e0-bb5e-4c03-8c6f-62d379d10889",
    "permissions": [
      "WRITE",
      "SELECT"
    ]
  }
]

Attributes of the `owner` Object

ownerId String (UUID)

UUID of the user-defined function's owner.

ownerType String

Type of owner of the user-defined function; must be USER or ROLE.

Attributes of Objects in the `users` and `roles` Arrays

id String (UUID)

UUID of the user or role with access to the user-defined function.

permissions Array of String

List of privileges the user or role has on the user-defined function. For more information, read User-Defined Function Privileges.

Example:

[
  "SELECT",
  "WRITE"
]

Create a User-Defined Function

Method and URL

POST /v0/projects/{project_id}/catalog

Parameters

project_id Path String (UUID)

entityType Body String

Type of the catalog object. For user-defined functions, the entityType is function.

path Body Array of String

Identifies the path where you want to create the user-defined function. Consists of the source or catalog, followed by any folders and subfolders, followed by the name of the function itself as the last item in the array.

Example:

[
  "mySource",
  "team_folder",
  "test_subfolder",
  "filter_domain_orderdates"
]

isScalar Body Boolean

If the user-defined function is a scalar function, true. If the user-defined function is a tabular function, false.

functionArgList Body String

The name of each argument in the user-defined function and the argument's data type. Separate the name and data type with a single space. If the function includes multiple arguments, separate the arguments with a comma.

Example: domain VARCHAR, orderdate DATE

functionBody Body String

The statement that the user-defined function should execute.

Example: SELECT name, email, order_date FROM customer_data WHERE LOWER(email) LIKE '%' || LOWER(domain) AND order_date >= orderdate

returnType Body String

The data type of each column that the user-defined function should return.

Example: name VARCHAR, email VARCHAR, order_date DATE

accessControlList Body Object Optional

Object used to specify which users and roles should have access to the user-defined function and the specific privileges each user or role should have. May include an array of users, an array of roles, or both. Omit if you do not want to configure function-specific access control privileges.

Example:

{
  "users": [
    {
      "id": "737a038f-c6cd-4fd3-a77a-59f692727ba5",
      "permissions": [
        "WRITE",
        "SELECT"
      ]
    }
  ],
  "roles": [
    {
      "id": "0f2d94e0-bb5e-4c03-8c6f-62d379d10889",
      "permissions": [
        "WRITE",
        "SELECT"
      ]
    }
  ]
}

Parameters of the `accessControlList` Object

users Body Array of Object Optional

List of users who should have access to the user-defined function and the specific privileges each user should have.

Example:

[
  {
    "id": "737a038f-c6cd-4fd3-a77a-59f692727ba5",
    "permissions": [
      "WRITE",
      "SELECT"
    ]
  }
]

roles Body Array of Object Optional

List of roles whose members should have access to the user-defined function and the specific privileges each role should have.

Example:

[
  {
    "id": "0f2d94e0-bb5e-4c03-8c6f-62d379d10889",
    "permissions": [
      "WRITE",
      "SELECT"
    ]
  }
]

Parameters of Objects in the `users` and `roles` Arrays

id Body String (UUID) Optional

UUID of the user or role who should have access to the user-defined function.

permissions Body Array of String Optional

List of privileges the user or role should have on the user-defined function. For more information, read User-Defined Function Privileges.

Valid Values: OWNERSHIP, SELECT, WRITE

Example:

[
  "SELECT",
  "WRITE"
]

Example

Request

curl -X POST "https://api.dremio.cloud/v0/projects/$PROJECT_ID/catalog" \
-H "Authorization: Bearer $DREMIO_TOKEN" \
-H 'Content-Type: application/json' \
--data-raw '{
  "entityType": "function",
  "path": [
    "mySource",
    "team_folder",
    "test_subfolder",
    "filter_domain_orderdates"
  ],
  "isScalar": false,
  "functionArgList": "domain VARCHAR, orderdate DATE",
  "functionBody": "SELECT name, email, order_date FROM customer_data WHERE LOWER(email) LIKE '%' || LOWER(domain) AND order_date >= orderdate",
  "returnType": "name VARCHAR, email VARCHAR, order_date DATE",
  "accessControlList": {
    "users": [
      {
        "id": "737a038f-c6cd-4fd3-a77a-59f692727ba5",
        "permissions": [
        "SELECT",
        "WRITE"
        ]
      }
    ],
    "roles": [
      {
        "id": "0f2d94e0-bb5e-4c03-8c6f-62d379d10889",
        "permissions": [
        "WRITE",
        "SELECT"
        ]
      }
    ]
  }
}'

Response

{
  "entityType": "function",
  "id": "1568aa06-4eac-48cf-bc30-2aa3053c2840",
  "path": [
    "mySource",
    "team_folder",
    "test_subfolder",
    "filter_domain_orderdates"
  ],
  "tag": "b142bed0-6dcb-44fe-83fa-30be8f07f283",
  "createdAt": "2024-08-01T20:20:38.547Z",
  "lastModified": "2024-08-01T20:20:38.547Z",
  "isScalar": false,
  "functionArgList": "\"domain\" CHARACTER VARYING, \"orderdate\" DATE",
  "functionBody": "SELECT \"name\", \"email\", \"order_date\" FROM \"customer_data\" WHERE LOWER(\"email\") LIKE '%' || LOWER(domain) AND \"order_date\" >= orderdate",
  "returnType": "\"name\" CHARACTER VARYING, \"email\" CHARACTER VARYING, \"order_date\" DATE",
  "accessControlList": {
    "users": [
      {
        "id": "737a038f-c6cd-4fd3-a77a-59f692727ba5",
        "permissions": [
          "WRITE",
          "SELECT"
        ]
      }
    ],
    "roles": [
      {
        "id": "0f2d94e0-bb5e-4c03-8c6f-62d379d10889",
        "permissions": [
          "WRITE",
          "SELECT"
        ]
      }
    ]
  },
  "permissions": [],
  "owner": {
    "ownerId": "4740ab48-39c6-434c-9086-8f6e52e65349",
    "ownerType": "USER"
  }
}

Response Status Codes

200   OK

400   Bad Request

401   Unauthorized

409   Conflict

Retrieve a User-Defined Function by ID

Method and URL

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

Parameters

project_id Path String (UUID)

id Path String (UUID)

UUID of the user-defined function.

include Query String Optional

Include a non-default attribute in the response. The available value for the include query parameter is permissions. For more information, see the include query parameter.

Example: ?include=permissions

Example

Request

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

Response

{
  "entityType": "function",
  "id": "1568aa06-4eac-48cf-bc30-2aa3053c2840",
  "path": [
    "mySource",
    "team_folder",
    "test_subfolder",
    "filter_domain_orderdates"
  ],
  "tag": "b142bed0-6dcb-44fe-83fa-30be8f07f283",
  "createdAt": "2024-08-01T20:20:38.547Z",
  "lastModified": "2024-08-01T20:20:38.547Z",
  "isScalar": false,
  "functionArgList": "\"domain\" CHARACTER VARYING, \"orderdate\" DATE",
  "functionBody": "SELECT \"name\", \"email\", \"order_date\" FROM \"customer_data\" WHERE LOWER(\"email\") LIKE '%' || LOWER(domain) AND \"order_date\" >= orderdate",
  "returnType": "\"name\" CHARACTER VARYING, \"email\" CHARACTER VARYING, \"order_date\" DATE",
  "accessControlList": {
    "users": [
      {
        "id": "737a038f-c6cd-4fd3-a77a-59f692727ba5",
        "permissions": [
          "WRITE",
          "SELECT"
        ]
      }
    ],
    "roles": [
      {
        "id": "0f2d94e0-bb5e-4c03-8c6f-62d379d10889",
        "permissions": [
          "WRITE",
          "SELECT"
        ]
      }
    ]
  },
  "permissions": [],
  "owner": {
    "ownerId": "4740ab48-39c6-434c-9086-8f6e52e65349",
    "ownerType": "USER"
  }
}

Response Status Codes

200   OK

401   Unauthorized

403   Forbidden

404   Not Found

Retrieve a User-Defined Function by Path

Method and URL

GET /v0/projects/{project_id}/catalog/by-path/{path}

Parameters

project_id Path String (UUID)

path Path String

Path of the user-defined function within Dremio. The path consists of the source or catalog, followed by any folders and subfolders, followed by the name of the function itself. Separate each level of the path with a forward slash.

Example: mySource/team_folder/test_subfolder/filter_domain_orderdates

include Query String Optional

Include a non-default attribute in the response. The available value for the include query parameter is permissions. For more information, see the include query parameter.

Example: ?include=permissions

Example

Request

curl -X GET "https://api.dremio.cloud/v0/projects/$PROJECT_ID/catalog/by-path/mySource/team_folder/test_subfolder/filter_domain_orderdates" \
-H "Authorization: Bearer $DREMIO_TOKEN" \
-H 'Content-Type: application/json'

Response

{
  "entityType": "function",
  "id": "1568aa06-4eac-48cf-bc30-2aa3053c2840",
  "path": [
    "mySource",
    "team_folder",
    "test_subfolder",
    "filter_domain_orderdates"
  ],
  "tag": "b142bed0-6dcb-44fe-83fa-30be8f07f283",
  "createdAt": "2024-08-01T20:20:38.547Z",
  "lastModified": "2024-08-01T20:20:38.547Z",
  "isScalar": false,
  "functionArgList": "\"domain\" CHARACTER VARYING, \"orderdate\" DATE",
  "functionBody": "SELECT \"name\", \"email\", \"order_date\" FROM \"customer_data\" WHERE LOWER(\"email\") LIKE '%' || LOWER(domain) AND \"order_date\" >= orderdate",
  "returnType": "\"name\" CHARACTER VARYING, \"email\" CHARACTER VARYING, \"order_date\" DATE",
  "accessControlList": {
    "users": [
      {
        "id": "737a038f-c6cd-4fd3-a77a-59f692727ba5",
        "permissions": [
          "WRITE",
          "SELECT"
        ]
      }
    ],
    "roles": [
      {
        "id": "0f2d94e0-bb5e-4c03-8c6f-62d379d10889",
        "permissions": [
          "WRITE",
          "SELECT"
        ]
      }
    ]
  },
  "permissions": [],
  "owner": {
    "ownerId": "4740ab48-39c6-434c-9086-8f6e52e65349",
    "ownerType": "USER"
  }
}

Response Status Codes

200   OK

401   Unauthorized

403   Forbidden

404   Not Found

Update a User-Defined Function

Method and URL

PUT /v0/projects/{project_id}/catalog/{id}

Parameters

project_id Path String (UUID)

id Path String (UUID)

UUID of the user-defined function.

entityType Body String

Type of the catalog object. For user-defined functions, the entityType is function.

path Body Array of String

Example:

[
  "mySource",
  "team_folder",
  "test_subfolder",
  "filter_domain_orderdates"
]

tag Body String (UUID)

UUID of the version of the user-defined function. Dremio uses the tag to ensure that you are requesting to update the most recent version of the user-defined function.

isScalar Body Boolean

If the user-defined function is a scalar function, true. If the user-defined function is a tabular function, false.

Example: false

functionArgList Body String

Example: domain VARCHAR, orderdate DATE

functionBody Body String

The statement that the user-defined function should execute.

Example: SELECT name, email, phone_number, order_date FROM customer_data WHERE LOWER(email) LIKE '%' || LOWER(domain) AND order_date >= orderdate

returnType Body String

The data type of each column that the user-defined function should return.

Example: name VARCHAR, email VARCHAR, phone_number VARCHAR, order_date DATE

accessControlList Body Object Optional

Object used to specify which users and roles should have access to the user-defined function and the specific privileges each user or role should have. If you omit the accessControlList object in a PUT request, Dremio removes all existing user and role access settings from the function. To keep existing user and role access settings while making other updates, duplicate the existing accessControlList in the PUT request.

Example:

{
  "users": [
    {
      "id": "737a038f-c6cd-4fd3-a77a-59f692727ba5",
      "permissions": [
        "WRITE",
        "SELECT"
      ]
    }
  ],
  "roles": [
    {
      "id": "0f2d94e0-bb5e-4c03-8c6f-62d379d10889",
      "permissions": [
        "WRITE",
        "SELECT"
      ]
    }
  ]
}

Parameters of the `accessControlList` Object

users Body Array of Object Optional

List of users who should have access to the user-defined function and the specific privileges each user should have. If you omit the users object in a PUT request, Dremio removes all existing user access settings from the function. To keep existing user access settings while making other updates, duplicate the existing users array in the PUT request.

Example:

[
  {
    "id": "737a038f-c6cd-4fd3-a77a-59f692727ba5",
    "permissions": [
      "WRITE",
      "SELECT"
    ]
  }
]

roles Body Array of Object Optional

List of roles whose members should have access to the user-defined function and the specific privileges each role should have. If you omit the roles object in a PUT request, Dremio removes all existing role access settings from the function. To keep existing role access settings while making other updates, duplicate the existing roles array in the PUT request.

Example:

[
  {
    "id": "0f2d94e0-bb5e-4c03-8c6f-62d379d10889",
    "permissions": [
      "WRITE",
      "SELECT"
    ]
  }
]

Parameters of Objects in the `users` and `roles` Arrays

id Body String (UUID) Optional

UUID of the user or role who should have access to the user-defined function.

permissions Body Array of String Optional

List of privileges the user or role should have on the user-defined function. For more information, read User-Defined Function Privileges.

Example:

[
  "WRITE",
  "SELECT"
]

Example

Request

curl -X PUT "https://api.dremio.cloud/v0/projects/$PROJECT_ID/catalog/$UDF_ID" \
-H "Authorization: Bearer $DREMIO_TOKEN" \
-H 'Content-Type: application/json' \
--data-raw '{
  "entityType": "function",
  "id": "1568aa06-4eac-48cf-bc30-2aa3053c2840",
  "path": [
    "mySource",
    "team_folder",
    "test_subfolder",
    "filter_domain_orderdates"
  ],
  "tag": "b142bed0-6dcb-44fe-83fa-30be8f07f283",
  "isScalar": false,
  "functionArgList": "domain VARCHAR, orderdate DATE",
  "functionBody": "SELECT name, email, phone_number, order_date FROM customer_data WHERE LOWER(email) LIKE '%' || LOWER(domain) AND order_date >= orderdate",
  "returnType": "name VARCHAR, email VARCHAR, phone_number VARCHAR, order_date DATE",
  "accessControlList": {
    "users": [
      {
        "id": "737a038f-c6cd-4fd3-a77a-59f692727ba5",
        "permissions": [
          "WRITE",
          "SELECT"
        ]
      }
    ],
    "roles": [
      {
        "id": "0f2d94e0-bb5e-4c03-8c6f-62d379d10889",
        "permissions": [
        "WRITE",
        "SELECT"
        ]
      }
    ]
  }
}'

Response

{
  "entityType": "function",
  "id": "1568aa06-4eac-48cf-bc30-2aa3053c2840",
  "path": [
    "mySource",
    "team_folder",
    "test_subfolder",
    "filter_domain_orderdates"
  ],
  "tag": "b142bed0-6dcb-44fe-83fa-30be8f07f283",
  "createdAt": "2024-08-01T20:20:38.547Z",
  "lastModified": "2024-08-07T17:17:17.360Z",
  "isScalar": false,
  "functionArgList": "\"domain\" CHARACTER VARYING, \"orderdate\" DATE",
  "functionBody": "SELECT \"name\", \"email\", \"phone_number\", \"order_date\" FROM \"customer_data\" WHERE LOWER(\"email\") LIKE '%' || LOWER(domain) AND \"order_date\" >= orderdate",
  "returnType": "\"name\" CHARACTER VARYING, \"email\" CHARACTER VARYING, \"phone_number\" CHARACTER VARYING, \"order_date\" DATE",
  "accessControlList": {
    "users": [
      {
        "id": "737a038f-c6cd-4fd3-a77a-59f692727ba5",
        "permissions": [
          "WRITE",
          "SELECT"
        ]
      }
    ],
    "roles": [
      {
        "id": "0f2d94e0-bb5e-4c03-8c6f-62d379d10889",
        "permissions": [
          "WRITE",
          "SELECT"
        ]
      }
    ]
  },
  "permissions": [],
  "owner": {
    "ownerId": "4740ab48-39c6-434c-9086-8f6e52e65349",
    "ownerType": "USER"
  }
}

Response Status Codes

200   OK

400   Bad Request

401   Unauthorized

403   Forbidden

404   Not Found

Delete a User-Defined Function

Method and URL

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

Parameters

project_id Path String (UUID)

id Path String (UUID)

UUID of the user-defined function.

Example Request

curl -X DELETE "https://api.dremio.cloud/v0/projects/$PROJECT_ID/catalog/$UDF_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

400   Bad Request

401   Unauthorized

403   Forbidden

404   Not Found

User-Defined Function Attributes​

Attributes of the accessControlList Object​

Attributes of the owner Object​

Attributes of Objects in the users and roles Arrays​

Create a User-Defined Function​

Parameters​

Parameters of the accessControlList Object​

Parameters of Objects in the users and roles Arrays​

Example​

Response Status Codes​

Retrieve a User-Defined Function by ID​

Parameters​

Example​

Response Status Codes​

Retrieve a User-Defined Function by Path​

Parameters​

Example​

Response Status Codes​

Update a User-Defined Function​

Parameters​

Parameters of the accessControlList Object​

Parameters of Objects in the users and roles Arrays​

Example​

Response Status Codes​

Delete a User-Defined Function​

Parameters​

Response Status Codes​

User-Defined Function Attributes

Attributes of the `accessControlList` Object

Attributes of the `owner` Object

Attributes of Objects in the `users` and `roles` Arrays

Create a User-Defined Function

Parameters

Parameters of the `accessControlList` Object

Parameters of Objects in the `users` and `roles` Arrays

Example

Response Status Codes

Retrieve a User-Defined Function by ID

Parameters

Example

Response Status Codes

Retrieve a User-Defined Function by Path

Parameters

Example

Response Status Codes

Update a User-Defined Function

Parameters

Parameters of the `accessControlList` Object

Parameters of Objects in the `users` and `roles` Arrays

Example

Response Status Codes

Delete a User-Defined Function

Parameters

Response Status Codes