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.