Databricks: How to get the current user in %SQL QUERY

user_11698462 0
Viewed 3759

In the DATABRICKS DELTA databases I have information about different user groups in the same tables since the user groups change. I mean that all users in all groups use the same table.

Users access data through a single view.

I would like the data to be filtered depending on the user accessing the view. Do you know how I can do it? This would be an example (any_object would be the command to retrieve the value of the current user that uses the view or the user group to which it belongs):

%sql
CREATE VIEW db.table AS SELECT * FROM WHERE column1 = any_object.username OR any_object.groupname

it's possible this?

Thanks!!!

delta-lake databricks get identity sql
2 Answers

New answer, Databricks just released Spark SQL current_user() function. To test:

%sql
set spark.databricks.userInfoFunctions.enabled = true;
select current_user()

You can set spark.databricks.userInfoFunctions.enabled in the spark options for the cluster or as shown in your session. I used the latest workspace release (v3.29), DBR 7.3 and a High Concurrency cluster for my test.

user_73796440

Short answer is, you can't. [As of Spark 3.0/Spark 2.4.5]

Spark doesn't have a secure current_user() method.

Longer answer, here's a hack I have, that I use for some of my ETL code. I haven't tested it in a multi-user scenario you're looking for.

%scala
val key = "spark.databricks.username"
val value = dbutils.notebook.getContext.tags("user")
spark.conf.set(key, value)

print(spark.conf.get(key))

val confBd = sc.broadcast(spark.conf.getAll.toMap)
/*
  @return Current username
*/
val current_userx = () => {
  confBd.value.get("spark.databricks.username")
}
spark.udf.register("current_user", current_userx)

%sql
select current_user()
user_73796440
Related