CODEWITHSUNDEEP
snowflake-cloud-data-platform
therji
therji

Reputation: 23

Can you create a View of a database Share, as a consumer?

I am trying to create a View of a database share (I am the consumer of the share).

e.g.

create view testview as (select * from myshare);

results in this error:

SQL execution error: Creating view on shared database 'myshare' is not allowed.

I didn't see any conclusive documentation around this. If this is not allowed, are there any workarounds to accomplish a similar outcome?

Upvotes: 1

Views: 1110

Answers (1)

Fieldy
Fieldy

Reputation: 601

You cannot create a view (or any other object) in the shared database as it is read-only reflection of the providers database.

You can create a view in another [database.schema] that references it.

Heres an example using ZEPL's US_STOCK_DAILY share from the Snowflake Marketplace.

So this fails, as you have found:

Use Database US_STOCKS_DAILY;
Use Schema PUBLIC;

Create View MAX_DATE as 
Select max(DATE)  MAXDATE from STOCK_HISTORY;

SQL execution error: Creating view on shared database 'US_STOCKS_DAILY' is not allowed.

But this works.

Use Database MY_DATABASE ;
Use Schema MY_SCHEMA;

Create View MAX_DATE as 
Select max(DATE)  MAXDATE 
  from US_STOCKS_DAILY.PUBLIC.STOCK_HISTORY; 

Select max(DATE) MAXDATE from MAX_DATE;

Note, you need to fully-qualify the object name in the providers schema.

Upvotes: 2

Related Questions