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": "qBWpD7x6+Ws=",
  "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)

Unique identifier of the user-defined function.

Example: 1568aa06-4eac-48cf-bc30-2aa3053c2840

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 folder 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

Unique identifier 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.

Example: qBWpD7x6+Ws=

createdAt String

Date and time at which the user-defined function was created, in UTC format.

Example: 2024-08-01T20:20:38.547Z

lastModified String

Date and time at which the user-defined function was last modified, in UTC format.

Example: 2024-08-01T20:20:38.547Z

isScalar Boolean

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

Example: 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.

note

In response objects, the functionArgList value may contain aliases for data types, such as CHARACTER VARYING (an alias for VARCHAR).

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

Enterprise only. 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 array 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

Enterprise-only. 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 (UDF) 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

Enterprise only. 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

Enterprise only. 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)

Unique identifier of the user-defined function's owner.

Example: 4740ab48-39c6-434c-9086-8f6e52e65349

ownerType String

Type of owner of the user-defined function.

Enum: USER, ROLE

Example: USER

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

id String

Enterprise only. Unique identifier of the user or role with access to the user-defined function.

Example: 4740ab48-39c6-434c-9086-8f6e52e65349

permissions Array of String

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

Enum: OWNERSHIP, SELECT, WRITE

Example: ["SELECT","WRITE"]

Creating a User-Defined Function

Create a new user-defined function.

Method and URL

POST /v0/projects/{project-id}/catalog

Parameters

project-id Path String (UUID)

The UUID for the project that you want to create a user-defined function for.

Example: 02d36975-73eb-47ed-9bb5-de73060380f6

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 folder 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.

Example: 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.

note

In response objects, the functionArgList value may contain aliases for data types, such as CHARACTER VARYING (an alias for VARCHAR).

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

Enterprise only. 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

Enterprise only. 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

Enterprise only. 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 Optional

Enterprise only. Unique identifier of the user or role who should have access to the user-defined function.

Example: 737a038f-c6cd-4fd3-a77a-59f692727ba5

permissions Body Array of String Optional

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

Enum: OWNERSHIP, SELECT, WRITE

Example: ["SELECT","WRITE"]

Example Request

curl -X POST 'https://api.dremio.cloud/v0/projects/02d36975-73eb-47ed-9bb5-de73060380f6/catalog' \
--header 'Authorization: Bearer <PersonalAccessToken>' \
--header '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"
        ]
      }
    ]
  }
}'

Example Response

{
  "entityType": "function",
  "id": "1568aa06-4eac-48cf-bc30-2aa3053c2840",
  "path": [
    "mySource",
    "team_folder",
    "test_subfolder",
    "filter_domain_orderdates"
  ],
  "tag": "qBWpD7x6+Ws=",
  "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

Retrieving a User-Defined Function by ID

Retrieve a user-defined function and information about its contents by specifying the function's ID.

Method and URL

GET /v0/projects/{project-id}/catalog/{id}

Parameters

project-id Path String (UUID)

The UUID for the project that contains the user-defined function.

id Path String (UUID)

Unique identifier of the user-defined function that you want to retrieve.

Example: 1568aa06-4eac-48cf-bc30-2aa3053c2840

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, read include and exclude Query Parameters.

Example: ?include=permissions

Example Request

curl -X GET 'https://api.dremio.cloud/v0/projects/02d36975-73eb-47ed-9bb5-de73060380f6/catalog/1568aa06-4eac-48cf-bc30-2aa3053c2840' \
--header 'Authorization: Bearer <PersonalAccessToken>' \
--header 'Content-Type: application/json'

Example Response

{
  "entityType": "function",
  "id": "1568aa06-4eac-48cf-bc30-2aa3053c2840",
  "path": [
    "mySource",
    "team_folder",
    "test_subfolder",
    "filter_domain_orderdates"
  ],
  "tag": "qBWpD7x6+Ws=",
  "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

Retrieving a User-Defined Function by Path

Retrieve a user-defined function and information about its contents by specifying the function's path.

Method and URL

GET /v0/projects/{project-id}/catalog/by-path/{path}

Parameters

project-id Path String (UUID)

The UUID for the project that contains the user-defined function.

path Path String

Path of the user-defined function within Dremio. The path consists of the source or catalog, followed by any folder 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, read include and exclude Query Parameters.

Example: ?include=permissions

Example Request

curl -X GET 'https://api.dremio.cloud/v0/projects/02d36975-73eb-47ed-9bb5-de73060380f6/catalog/by-path/mySource/team_folder/test_subfolder/filter_domain_orderdates' \
--header 'Authorization: Bearer <PersonalAccessToken>' \
--header 'Content-Type: application/json'

Example Response

{
  "entityType": "function",
  "id": "1568aa06-4eac-48cf-bc30-2aa3053c2840",
  "path": [
    "mySource",
    "team_folder",
    "test_subfolder",
    "filter_domain_orderdates"
  ],
  "tag": "qBWpD7x6+Ws=",
  "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

Updating a User-Defined Function

Update the specified user-defined function.

Method and URL

PUT /v0/projects/{project-id}/catalog/{id}

Parameters

project-id Path String (UUID)

The UUID for the project that contains the user-defined function.

Example: 1568aa06-4eac-48cf-bc30-2aa3053c2840

id Body String (UUID)

Unique identifier of the user-defined function that you want to update.

Example: 1568aa06-4eac-48cf-bc30-2aa3053c2840

entityType Body String

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

path Body Array of String

Path of the user-defined function within Dremio, expressed as an array. The path consists of the source or user-defined function, followed by any folder 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 Body String

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

Example: qBWpD7x6+Ws=

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

note

In response objects, the functionArgList value may contain aliases for data types, such as CHARACTER VARYING (an alias for VARCHAR).

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 String Optional

Enterprise only. 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 array 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

Enterprise only. 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

Enterprise only. 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 Optional

Enterprise only. Unique identifier of the user or role who should have access to the user-defined function.

Example: 737a038f-c6cd-4fd3-a77a-59f692727ba5

permissions Body Array of String Optional

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

Enum: OWNERSHIP, SELECT, WRITE

Example: ["WRITE","SELECT"]

Example Request

curl -X PUT 'https://api.dremio.cloud/v0/projects/02d36975-73eb-47ed-9bb5-de73060380f6/catalog/1568aa06-4eac-48cf-bc30-2aa3053c2840' \
--header 'Authorization: Bearer <PersonalAccessToken>' \
--header 'Content-Type: application/json' \
--data-raw '{
  "entityType": "function",
  "id": "1568aa06-4eac-48cf-bc30-2aa3053c2840",
  "path": [
    "mySource",
    "team_folder",
    "test_subfolder",
    "filter_domain_orderdates"
  ],
  "tag": "qBWpD7x6+Ws=",
  "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"
        ]
      }
    ]
  }
}'

Example Response

{
  "entityType": "function",
  "id": "1568aa06-4eac-48cf-bc30-2aa3053c2840",
  "path": [
    "mySource",
    "team_folder",
    "test_subfolder",
    "filter_domain_orderdates"
  ],
  "tag": "4RuPbmWPoa9=",
  "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

Deleting a User-Defined Function

Delete the specified user-defined function.

Method and URL

DELETE /v0/projects/{project-id}/catalog/{id}

Parameters

project-id Path String (UUID)

The UUID for the project that contains the user-defined function.

id Path String (UUID)

Unique identifier of the user-defined function that you want to delete.

Example: 1568aa06-4eac-48cf-bc30-2aa3053c2840

Example Request

curl -X DELETE 'https://api.dremio.cloud/v0/projects/02d36975-73eb-47ed-9bb5-de73060380f6/catalog/1568aa06-4eac-48cf-bc30-2aa3053c2840' \
--header 'Authorization: Bearer <PersonalAccessToken>' \
--header 'Content-Type: application/json'

Example Response

No response

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​

Creating a User-Defined Function​

Parameters​

Parameters of the accessControlList Object​

Parameters of Objects in the users and roles Arrays​

Response Status Codes​

Retrieving a User-Defined Function by ID​

Parameters​

Response Status Codes​

Retrieving a User-Defined Function by Path​

Parameters​

Response Status Codes​

Updating a User-Defined Function​

Parameters​

Parameters of the accessControlList Object​

Parameters of Objects in the users and roles Arrays​

Response Status Codes​

Deleting 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

Creating a User-Defined Function

Parameters

Parameters of the `accessControlList` Object

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

Response Status Codes

Retrieving a User-Defined Function by ID

Parameters

Response Status Codes

Retrieving a User-Defined Function by Path

Parameters

Response Status Codes

Updating a User-Defined Function

Parameters

Parameters of the `accessControlList` Object

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

Response Status Codes

Deleting a User-Defined Function

Parameters

Response Status Codes