How list all USERs FROM a especific GROUP USING SQL?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-04-2024 06:36 AM
I want to list, using sql editor, all users name from a specific group.
Reading documentation, I only learned how to show the groups or the users, using simples filters, like:
SHOW GROUPS LIKE '*XPTO*';
SHOW GROUPS WITH USER `test@gmail.com`
SHOW USERS LIKE '*gus*'
I would like to create something like:
select users_name from USERS where group_name = "XPTO_GROUP";
or
SHOW USERS WHERE GROUP "xpto_group";
- Labels:
-
Databricks SQL Warehouse
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-06-2024 06:13 AM
This solution seems to have been answered by an AI.
I need to know how create a SELECT for create this tables: USERS, GROUPS and GROUP_USERS.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
09-25-2024 01:46 AM
@Sardenberg wrote:This solution seems to have been answered by an AI.
I need to know how create a SELECT for create this tables: USERS, GROUPS and GROUP_USERS.
# 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
It will work fine if groups added to workspace.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-09-2024 06:52 AM - edited 10-09-2024 07:03 AM
I don't think it's possible yet. Unfortunately, I look in all system tables and command and didn't found this kind of things.
But with a Python notebook, like what did the AI, you can reconstruct it:
first you list all the users with
SHOW USERS
then you loop for all users to get its groups with:
SHOW GROUPS WITH USER `{user.name}`
And you can reconstruct the list of users in each groups like that, and put it in a delta table with a create table.
With this table you can easily list all users in a specific group in a SQL request. You "just" need to run your python notebook regularly to update your user-group table.
Due to the special type of command that this is, you can't (to my knowledge) do that kind of thing directly in SQL, (can't "SELECT * FROM SHOW ..." for example), and there is no specific command for that, so it's a little laborious, especially if you have a lot of users and groups.

