Is there a way to get the config of the account and roles, warehouses using snowpark api

Question

Is there a way to extract the config of the account,roles and warehouses from snowflake api. Is it possible to use the api to create ones using the extracted config?

from snowflake.snowpark import Session

from snowflake.snowpark.functions import udf, col, month, sql_expr
from snowflake.snowpark import functions as F
from snowflake.snowpark.types import StringType, IntegerType, StructType, StructField, ArrayType
from snowflake.snowpark import DataFrame
import os
connection_parameters = {
    "account": os.environ["SNOW_ACCOUNT"],
    "user": os.environ["SNOW_USER"],
    "password": os.environ["SNOW_PASSWORD"],
    "role": os.environ["SNOW_ROLE"],
    "warehouse": os.environ["SNOW_WAREHOUSE"],
    "database": os.environ["SNOW_DATABASE"],
    "schema": os.environ.get("SNOW_SCHEMA")
}
session = Session.builder.configs(connection_parameters).create()

Are you referring to get the data from [Information Schema](https://docs.snowflake.com/en/sql-reference/info-schema.html)? It looks to me the information you want can be retrieved from information schema and that is not Snowpark related but can be retrieved using Snowpark. — Sergiu, Oct 13 '22 at 16:24
Data like show users,show warehouses and maybe information schema too. I see the INFORMATION_SCHEMA views provides a SQL interface to the same information provided by the SHOW commands. I know how to do it via console but is it possible this can be retrieved using snowpark and how? — lunbox, Oct 13 '22 at 17:50

score 2 · Answer 1 · answered Oct 13 '22 at 18:58

Snowpark allows to execute any arbitrary SQL, for instance querying ACCOUNT_USAGE schema:

df_sql = session.sql("SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.USERS")
df_sql.show()

Getting current session setup:

SELECT CURRENT_ACCOUNT(), CURRENT_USER(), CURRENT_ROLE(), ...

Can we create a user via the snowpark api?

Yes, any query could be issued:

session.sql("CREATE USER <user_name> ").collect()

The crucial component are proper permissions to perform specific action, full list could be found at: Access Control Privileges

Is there a way to get the config of the account and roles, warehouses using snowpark api

1 Answers1