Snowflake - Data Sharing - Working Session

You can get all snowflake Videos, PPTs, Queries, Interview questions and Practice files in my Udemy course for very less price.. I will be updating this content and will be uploading all new videos in this course.
My Snowflake Udemy Course:
www.udemy.com/...
I can be reachable on [e-mail].
=======================================
Data Sharing to Other Snowflake Users
=======================================
// Create a Database
CREATE DATABASE CUST_DB;
// Create schemas
CREATE SCHEMA CUST_TBLS;
CREATE SCHEMA CUST_VIEWS;
// Create some tables in tbls schema
CREATE TABLE CUST_DB.CUST_TBLS.CUSTOMER
AS SELECT * FROM SAMPLE_DATA.TPCH_SF1.CUSTOMER;
CREATE TABLE CUST_DB.CUST_TBLS.ORDERS
AS SELECT * FROM SAMPLE_DATA.TPCH_SF1.ORDERS;
// Create a view in views schema
CREATE OR REPLACE VIEW CUST_VIEWS.VW_CUST
AS
SELECT CST.C_CUSTKEY, CST.C_NAME, CST.C_ADDRESS, CST.C_PHONE FROM
SAMPLE_DATA.TPCH_SF10.CUSTOMER CST
INNER JOIN SAMPLE_DATA.TPCH_SF10.NATION NTN
ON CST.C_NATIONKEY = NTN.N_NATIONKEY
WHERE NTN.N_NAME='BRAZIL';
// Create a secure view in views schema
CREATE OR REPLACE SECURE VIEW CUST_VIEWS.SEC_VW_CUST
AS
SELECT CST.C_CUSTKEY, C_NAME,C_ADDRESS, C_PHONE
FROM CUST_DB.CUST_TBLS.CUSTOMER CST;
// Create a mat view in views schema
CREATE MATERIALIZED VIEW CUST_DB.CUST_VIEWS.MAT_VW_ORDERS
AS
SELECT * FROM CUST_DB.CUST_TBLS.CUSTOMER;
// Create a secure mat view in views schema
CREATE SECURE MATERIALIZED VIEW CUST_DB.CUST_VIEWS.SEC_MAT_VW_ORDERS
AS
SELECT * FROM CUST_DB.CUST_TBLS.CUSTOMER;
===============================
// Create a share object
-- we can create and manage share objects in two ways
-- 1. By using sql queries 2. By using share tabs in UI
CREATE OR REPLACE SHARE CUST_DATA_SHARE;
// Grant access to share object
GRANT USAGE ON DATABASE CUST_DB TO SHARE CUST_DATA_SHARE;
GRANT USAGE ON SCHEMA CUST_DB.CUST_TBLS TO SHARE CUST_DATA_SHARE;
GRANT SELECT ON TABLE CUST_DB.CUST_TBLS.CUSTOMER TO SHARE CUST_DATA_SHARE;
GRANT SELECT ON TABLE CUST_DB.CUST_TBLS.ORDERS TO SHARE CUST_DATA_SHARE;
GRANT USAGE ON SCHEMA CUST_DB.CUST_VIEWS TO SHARE CUST_DATA_SHARE;
GRANT SELECT ON TABLE CUST_DB.CUST_VIEWS.VW_CUST TO SHARE CUST_DATA_SHARE;
GRANT SELECT ON TABLE CUST_DB.CUST_VIEWS.SEC_VW_CUST TO SHARE CUST_DATA_SHARE;
GRANT SELECT ON TABLE CUST_DB.CUST_VIEWS.MAT_VW_ORDERS TO SHARE CUST_DATA_SHARE;
GRANT SELECT ON TABLE CUST_DB.CUST_VIEWS.SEC_MAT_VW_ORDERS TO SHARE CUST_DATA_SHARE;
// How to see share objects
SHOW SHARES; -- or we can use shares tab
// How to see the grants of a share object
SHOW GRANTS TO SHARE CUST_DATA_SHARE;
// Add the consumer account to share the data
ALTER SHARE CUST_DATA_SHARE ADD ACCOUNT = consumer-account-id;
// How to share complete schema
GRANT SELECT ON ALL TABLES IN SCHEMA CUST_DB.CUST_TBLS TO SHARE CUST_DATA_SHARE;
// How to share complete database
GRANT SELECT ON ALL TABLES IN DATABASE CUST_DB TO SHARE CUST_DATA_SHARE;
=============================
Consumer side database setup
=============================
SHOW SHARES;
DESC SHARE share-name;
// Create a database to consume the shared data
CREATE DATABASE CUST_DB_SHARED FROM SHARE share-name;
SELECT * FROM CUST_DB_SHARED.CUST_TBLS.CUSTOMER;
====================================
Data Sharing to Non-Snowflake Users
====================================
// Create a reader account
CREATE MANAGED ACCOUNT CUSTOMER_ANALYST
ADMIN_NAME = cust_analyst,
ADMIN_PASSWORD = 'Abcd@123',
TYPE = READER;
// How to see reader accounts
SHOW MANAGED ACCOUNTS;
// Add reader account to share object
ALTER SHARE CUST_DATA_SHARE ADD ACCOUNT = reader-account-id;
ALTER SHARE CUST_DATA_SHARE ADD ACCOUNT = reader-account-id
SHARE_RESTRICTIONS=false;
=============================
Reader side database setup
=============================
SHOW SHARES;
DESC SHARE share-name;
// Get url of reader account and login to that reader account
// Get inbound share details
SHOW SHARES;
// Create a database to consume the shared data
CREATE DATABASE CUST_DB_SHARED FROM SHARE share-name;
// Query the shared tables
SELECT * FROM CUST_DB_SHARED.CUST_TBLS.CUSTOMER;
// Create a virtual warehouse
CREATE WAREHOUSE READER_WH WITH
WAREHOUSE_SIZE='X-SMALL'
AUTO_SUSPEND = 180
AUTO_RESUME = TRUE;
SELECT * FROM CUST_DB_SHARED.CUST_TBLS.CUSTOMER;

[@ALLACHERUVUVENKATARAVINDRA]

Just Making things simple and easy in manner......Your Vedio just amazing .

[@atlasrinu2215]

Nice explanation. Please make a video on how to do migration from Dev to Test environment.

[@truedailyinmilkveggiesdeli5106]

Nice video sir ...very helpful..pls keep posting snowflake related videos more

[@sql-world5536]

It's really helpful sir thank you please make one video on Data sampling

[@vinodbarma2858]

great explanation

[@balajikomma541]

Please order your snowflake playlist, so that we can follow easily. Thanks for sharing your knowledge

[@mrjana520]

Done!

[@balajikomma541]

Thank you so much, also could u please add numbers beside the video name

[@NirajPatel-c4o]

Can we do DML opeartions like Insert,delet,update on the shared tables?

[@mrjana520]

No, you can't

[@rameshram1117]

nice explanation sir and i ihave one dobut streams, pipes ,stages ,fileformats,tasks stored procedures ni sahre cheyalema only views and tables ni matrame share cheyagalama and one more question database share chesi usage previllages ichamu alage create schema previllages isthe schema create cheyacha shared database lo and inka tables and etc..create cheyacha

[@mrjana520]

Answer is there in this video itself. And you can't create schemas and tables in the shared databases.

[@rameshram1117]

@@mrjana520 haa sir thank you and Mee anni videos nenu chusanu sir Mee videos chuse nen snowflake nerchukunna...

[@rameshram1117]

@@mrjana520 Mee Instagram I'd chepthara sir personal ga contact avadaniki SQL and Snowflake Mee daggara nerchukovadaniki poorthiga ante inka baaga real time work kuda .

[@mrjana520]

My mail id is available in the description of every video. Reach me on [e-mail]

[@rohitmanderwad6034]

In what all snowflake editions we can implement shares? can we implement shares in snowflake business critical & VPS editions?

[@mrjana520]

Not sure about VPS, but we can in enterprise and business critical

[@pratikthole2414]

Also, Could you please create tutorials on Clustering and Snowpipe

[@mrjana520]

Will do

[@93karanbhatia]

How to share if the accounts are in different region? Please provide step by step details or point to any video of yours. Thanks

[@mrjana520]

There is a concept called data replication, clearly explained in Snowflake documentation

[@NitinDhiwar88]

Using Grant All tables in db to share command can we share future tables also creared in provider DB? And if prev shared tables updated in provider side then consumer can see those changes??

[@mrjana520]

Yes, you can

[@UPavan07]

sir how can we load data in an external system into our snowflake account and is there any way to host the tables in our account to others

[@mrjana520]

What is meant by external system? is there any way to host the tables in our account to others? Yes through data sharing and data replication.

[@UPavan07]

@@mrjana520ok sir

[@AshokKumarThangarathinam-kj5et]

What is difference between data sharing, zero copy cloning and replications in snowflake

[@mrjana520]

It can't be answered in a single word or sentence, just watch data sharing video and zero copy cloning video fully, you will understand the concepts

[@mrjana520]

While education people used to by-hearting only faqs instead of understanding the concepts, they are following the same thing in job search as well, they just need direct question and answers. Guys have patience and understand the concepts fully and practice as well..

[@rameshram1117]

sir okate share ki multiple databases and multiple schemas ki usage grant ivvacha leda multiple databases share cheyalante multiple shares create cheylanaa? sir please reply me

[@mrjana520]

I never tried but as per my knowledge 1 share is enough for multiple databases

[@rameshram1117]

@@mrjana520 no sir work avvatledu okka database ki oka share create cheyalsinde nenu try chesa sir indaakane avvatledu.. already oka database assign chesina share ki inko database assign avatledu.( database does not belong to the database that is being shared.)ani vastundi sir malli vere share create chesi assign chesthe avutundi .. ante okko database ki okko share create cheyalanukuntunna sir..

[@mrjana520]

Oh k, thanks for the information

[@rameshram1117]

@@mrjana520 ha sir parledu no thanks Mee vallane nen teluskuntunna motham ,meeru okasari try cheyandi sir..

[@pratikthole2414]

Don't we need to provide grant usage, privileges if we do it through web ui ?

[@mrjana520]

It will be done automatically if do using ui, grants are mandatory, if you want to check, use web ui and then check the grants by using SQL query

[@tejaswinerella5223]

I am unable to add another Snowflake account it is showing the following error Error: The following accounts cannot be added to this share: ME******

[@mrjana520]

Hi, with just this error line, I can't help. If you follow the steps in the same order I have explained you will not get any error. pls check all the steps once.

[@BalajiChowdary5313]

I am unable to add another snowflake account its showing following error Errror:Following accounts cannot be added to this share:lq47367

[@mrjana520]

Both accounts are from same region?

[@93karanbhatia]

@@mrjana520No the accounts are in different region