create schema but for a list of schemas

Question

What's the best programmatic option to create multiple schemas for a list of user names?

I wanted to do it for convenience, so when user logins into snowflake they'll have their environment ready. Users get synced by scim proto, so I can get a list from a group they are assigned.

Thanks!

Lukasz Szozda · Accepted Answer · 2021-10-18 19:11:20Z

3

The script could be generated for instance by using: ACCOUNT_USAGE.USERS:

USE ROLE ACCOUNTADMIN;


WITH schema_cte AS (
  SELECT 
    REPLACE('CREATE SCHEMA IF NOT EXISTS <name> /*CLONE <source schema>*/;'||CHAR(13)
           , '<name>', DISPLAY_NAME) AS create_schema_line
  FROM SNOWFLAKE.ACCOUNT_USAGE.USERS
  -- WHERE additional_condition_here
)
SELECT 
    'USE ROLE <role_name_here>;' || CHAR(13) ||
    'USE DATABASE <database_name_here>;' || CHAR(13) ||
    LISTAGG(create_schema_line) WITHIN GROUP(ORDER BY create_schema_line) AS script
FROM schema_cte;

Related: Identifier Requirements

edited Oct 18, 2021 at 19:11

answered Oct 18, 2021 at 19:05

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user17145065 Over a year ago

Thanks, found a similar solution with RESULTS_SCAN. show grants of role "blah"; select 'create-statement ' || "grantee_name" || ';' FROM TABLE(RESULTS_SCAN(LAST_QUERY_ID()))); What's the best way to execute these statements? Using a stored procedure? PS Sorry for the format, I can't figure out this crappy editor

Lukasz Szozda Over a year ago

@user17145065 You will need some kode to invoke it, so either Stored procedure would be way to go(Java/JavaScript). I hope soon SF will allow to use SQL Scripting(stored procedure purely in SQL and support for dynamic SQL so it will be much more straighforward with EXECUTE IMMEDIATE (<generated code here>)

user17145065 Over a year ago

Ok, so I got a stored procedure that runs the sql statement and then executes sql statements generated by first one. Now I’m thinking maybe this is not a good way at all. Maybe executing sql from properly tested and written apps using SDK would be more manageable? What do you think?

Lukasz Szozda Over a year ago

@user17145065 It depends o needs. If your team is more familiar with app development then yes, any language will do as long as there is connector to Snowflake. On the other hand having everything enclosed within Snowflake platform has its own advantages(it could be run on schedule with TASK for instance every day). That's the beauty of Snowflake Data Cloud that both approaches will realize the requirements.

user17145065 Over a year ago

I am trying to figure out what’s going to work best in the long term. Team probably can get comfortable with one of the languages and use it for all dba task. Stored procedures are cool but already running into limitations, like var size only 512 bytes - that’s just silly. No unit tests either… on the other side, if snowflake management wrapped into something like glue jobs - then boom! All infra management, unit tests, scheduling, access management, environments - all is easy to get done ✅

Collectives™ on Stack Overflow

create schema but for a list of schemas

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related