We have a bunch of cloud AD groups in Databricks and I can see what users are in each group by using the user interface Manage Account -> Users and groups -> Groups
I would like to be able to produce this full list in SQL. I have found the below code online to give me exactly what I want but it is in Python. There doesnt seem to be a way in SQL to do this? What I was thinking is if the SQL was a little complex I would just put it inside a SQL View and then my report could just call this view.
Also I tried to create a UDF that would use the code below (because you can use Python) but it gave me a error that [FEATURE_UNAVAILABLE] Python UDTF is not supported in your environment. To use this feature, please contact Databricks Support. SQLSTATE: 56038
Thanks
# Databricks notebook source
from pyspark.sql.functions import lit
# COMMAND ----------
df_groups = spark.sql("""SHOW GROUPS""")
# COMMAND ----------
df_users = spark.sql("""SHOW USERS""")
# COMMAND ----------
result = spark.createDataFrame([], "name: string, directGroup: boolean, user: string")
for user in df_users.collect():
df_user_groups = spark.sql(f"""SHOW GROUPS WITH USER `{user.name}`""").withColumn("user", lit(user.name))
result = result.unionAll(df_user_groups)
# COMMAND ----------
display(result) # Here you can create temp view and select data