Snowflake: READER ACCOUNT, 1 DB, 2 Schemas

Question

I am running into strange behavior when attempting to share 2 schemas from a single database to a SNOWFLAKE READER ACCOUNT.

Assuming both schemas on the producer database are properly shared the commands to create the database and shares on the READER ACCOUNT would look something like:

CREATE DATABASE myDB_A from share producerAccount.Producer_SHARE_Schema_A;
CREATE DATABASE myDB_B from share producerAccount.Producer_SHARE_Schema_B;

Where

Producer_SHARE_Schema_A is a share that looks like myDB.Schema_A and
Producer_SHARE_Schema_B is a share that looks like myDB.Schema_B

What seems to happen is myDB_A will have Schema_A and when the second command is run, myDB_A also picks up Schema_B.

Ideally, we would need a command that allows us to add a second schema to the database, using an ALTER approach:

CREATE DATABASE myDB_A from share producerAccount.Producer_SHARE_Schema_A;
**ALTER DATABASE myDB_B ADD share producerAccount.Producer_SHARE_Schema_B**;

The final question... Is there a standardized method to add additional schemas to a shared database in a target READER ACCOUNT?

Thanks!

DaveTurpin · Accepted Answer · 2019-10-15 18:18:09Z

1

I think I am able to answer my question... Instead of trying to add the second schema to a share that is created on the consumer (READER ACCOUNT) side, it's best to create the share on the PRODUCER side to include both schemas:

grant usage on database myDB to share myShare;
grant usage on schema myDB.Schema_A to share myShare;
grant usage on schema myDB.Schema_B to share myShare;

Then on the consumer side we only need to worry about a single share to create the database:

CREATE DATABASE myDB from share producerAccount.myshare;

That solved the problem. In hindsight, it is obvious. Put all of the pieces together on the producer side, and keep things real simple on the READER ACCOUNT.

Duh...

answered Oct 15, 2019 at 18:18

DaveTurpin

437 bronze badges

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

Collectives™ on Stack Overflow

Snowflake: READER ACCOUNT, 1 DB, 2 Schemas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related