3

I have two snowflake instances out of which one instance has data and i'm looking for an option to clone data from this instance to another but it seems like there is no such option of cloning across instances in snowflake but we have something called Secure Data Sharing. While I tried that option and realized that the second account does not get create objects access in the schemas or databases which are shared.

Is there any way that both instances/accounts has create access on the shared database?

Thanks, Jyothirmai

demircioglu
  • 3,069
  • 1
  • 15
  • 22
jyothirmai thondamallu
  • 33
  • 4

1 Answers1

4

You cannot clone from a share and shares are read only. You can of course copy the data to the account where the data is shared:

create table MY_TABLE_CLONE clone "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."NATION"; 
--SQL compilation error: Cannot clone from a table that was imported from a share

create table MY_TABLE_CLONE like "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."NATION"; 
--Copy the DDL

insert into MY_TABLE_CLONE select * from "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."NATION";
--Copy the rows

Remember that if you have a lot of data to copy you can always increase the size of the warehouse performing the copy and then set it back to the original size when the copy is done.

Greg Pavlik
  • 10,089
  • 2
  • 12
  • 29
  • Thank you, Greg! But I unable to create tables or any object in shared databases and schemas. SQL execution error: Creating table on shared database '****' is not allowed. Please help! – jyothirmai thondamallu Sep 25 '20 at 04:53
  • 1
    You don't have to create a table in the SHARED database but in the account which makes use of the share. Here you use your insert the data from the share and thus you can modify the data. Keep in mind: Your modified data cant be seen automatically by the acccount which shares data with you (thats what Greg ment, two accounts cannot operate on one single share because they are read-only) – Marcel Sep 25 '20 at 06:14