Function View

Function views are executable views of user-defined functions that often encapsulate data access and query logic. If an administrator (or view creator) grants the view execution privilege to a user, the user can execute the view without requiring access to the underlying tables. Function views serve as a flexible and efficient tool for managing data access and query execution, particularly in scenarios where users may not have direct read permissions on certain tables due to data privacy or security concerns.

Working with Function Views

In DolphinDB, function views can be managed by administrators or users with VIEW_OWNER privilege. The following sections outline the key operations for working with function views.

Adding Function Views

A user defined function can be registered as a view with addFunctionView. Once added, function views are persisted across the cluster, ensuring availability on all nodes even after a DolphinDB cluster restart.

For example, define a function myFunc and add it as a function view:

def myFunc(){
    print "myFunc"
}
addFunctionView(udf=myFunc)

Functions defined within a module can be added as views in batches by specifying the module namespace. For example, declare a module myModule and define functions f1 and f2 in the file [home]/modules/dir1/myModule.dos.

module dir1::myModule
​
def f1(){
    print "dir1::myModule::f1"
}
def f2(){
    print "dir1::myModule::f2"
}

Reference the module with use, then add all functions within the module as views by specifying the module namespace moduleName="dir1::myModule" for addFunctionView.

use dir1::myModule
addFunctionView(moduleName="dir1::myModule")

Checking Function Views

To check registered function views, use getFunctionViews. Executing getFunctionViews() returns a table as follows, displaying view myFunc along with f1 and f2 defined in myModule.

NAME BODY
myFunc def myFunc(){ print("myFunc") }
dir1::myModule::f1 def f1(){ print("dir1::myModule::f1")}
dir1::myModule::f2 def f2(){ print("dir1::myModule::f2")}

Note: Administrators can call getFunctionViews to obtain all function views, while users with VIEW_OWNER permission can only retrieve the function views they have created.

Executing Function Views

A view creator can grant a user the VIEW_EXEC privilege to execute the specified function view. Granted users can obtain the result of a function view even without the privileges to access the involved data. VIEW_EXEC can be applied to global (), module namespace (<namespace>::), or function view level. If functions of a module are added as views, the fully-qualified namespace can be used for specifying a module-level access control. For example:

// grant user1 the execution privilege of myFunc
grant("user1", VIEW_EXEC, "myFunc")
​
// grant user1 the execution privilege of f1 within dir1::myModule
grant("user1", VIEW_EXEC, "dir1::myModule::f1")
​
// grant user1 the execution privilege of all views within dir1::myModule
grant("user1", VIEW_EXEC, "dir1::myModule::*")

These privileges can be revoked or denied with revoke or deny.

Deleting Function Views

To delete a function view, use dropFunctionView. For function views added by a module, you can either drop them independently by specifying the function name with its full namespace or remove them in batches by specifying the module namespace.

// drop the view of myFunc
dropFunctionView(name="myFunc")
​
// drop the view of f1 within dir1::myModule
dropFunctionView(name="dir1::myModule::f1")
​
// drop all views within dir1::myModule
dropFunctionView(name="dir1::myModule",isNamespace=true)

Note: Once a function view is added to a cluster, its definition cannot be modified. To update a view, you must delete the existing one, redefine the function, and add a new function view to the cluster.

Examples

In the following example, user1 does not have the privilege to read and write to table dfs://db1/pt. Login as an admin, define function getAvg as a function review, and grant VIEW_EXEC to user1. Although not granted to read table dfs://db1/pt, user1 can still execute getAvg to calculate daily average value.

// create table dfs://db1/pt
login(userId=`admin, password=`123456)
db = database(directory="dfs://db1", partitionType=VALUE, 
  partitionScheme=2024.01.01..2024.01.10)
t = table(stretch(2024.01.01..2024.01.10,100) as date, rand(1000,100) as val)
pt = createPartitionedTable(dbHandle=db, table=t, tableName="pt", 
  partitionColumns="date")
pt.tableInsert(t)
​
// define getAvg as function view
def getAvg(){
  return select avg(val) from loadTable(database="dfs://db1", tableName="pt") group by date
}
login(userId=`admin, password=`123456)
addFunctionView(udf=getAvg)
​
// create user1 and grant VIEW_EXEC
createUser(userId=`user1, password=`123456)
grant(userId=`user1, accessType=VIEW_EXEC, objs="getAvg")
​
// user1 cannot query table pt
login(userId=`user1, password=`123456)
select avg(val) from loadTable(database="dfs://db1", tableName="pt") group by date 
// <NoPrivilege>Not granted to read table dfs://db1/pt
​
// user1 executes getAvg()
getAvg()
DATE AVG_VAL
2024.01.01 438
2024.01.02 447.6
2024.01.03 499.6
2024.01.04 355.2
2024.01.05 551.9
2024.01.06 330.2
2024.01.07 366.5
2024.01.08 547.9
2024.01.09 473
2024.01.10 369.2

The value column in the example is randomly generated, so the average value (avg_val) in the result may be different on each execution.