Partage sécurisé des données entre les régions et les plates-formes Cloud¶

use role accountadmin;

-- Promote an existing database in your local account as primary
alter database PrimaryDB enable replication to accounts AZURE_EASTUS2.AcmeProviderAccount2;

use role accountadmin;

-- Replicate the existing database to a secondary database in the other region
create database SecondaryDB
as replica of AWS_US_WEST_2.AcmeProviderAccount1.PrimaryDB;

-- Create a database for stored procedures
create database SecondaryDB_SP;
use database SecondaryDB_SP;

-- Schedule refresh of the secondary database

create task refresh_SecondaryDB_task
  warehouse = mywh
  schedule = '10 minute'
as
  alter database SecondaryDB refresh;

alter task refresh_SecondaryDB_task resume;

-- Refresh the secondary database now (as an alternative to the scheduled refresh)

alter database SecondaryDB refresh;

-- Create a share
create share share1;

-- Add objects to the share
grant usage on database SecondaryDB to share share1;
grant usage on schema SecondaryDB.sch to share share1;

grant select on view SecondaryDB.sch.view1 to share share1;

-- Add one or more consumer accounts to the share
alter share share1 add accounts=ConsumerAccount;

use role accountadmin;

-- In your local account, create a database with a subset of data
create database PrimaryDB;
create schema PrimaryDB.sch;
create table PrimaryDB.sch.tableB as select CUSTOMERID, USER_ORDER_COUNT, TOTAL_SPENT, TAGS from SourceDB.sch.tableA where REGION='azure_eastus2';
create secure view PrimaryDB.sch.view1 as select CUSTOMERID, USER_ORDER_COUNT, TOTAL_SPENT, TAGS from PrimaryDB.sch.tableB;

-- Set up a stream to record changes made to the source table
create stream mystream on table SourceDB.sch.tableA append_only = true;

-- Set up a task to lift the changes from the source database and insert them to the PrimaryDB database
CREATE TASK mytask1
  WAREHOUSE = mywh
  SCHEDULE = '5 minute'
WHEN
  SYSTEM$STREAM_HAS_DATA('mystream')
AS
  INSERT INTO tableB(CUSTOMERID, USER_ORDER_COUNT, TOTAL_SPENT, TAGS) select CUSTOMERID, USER_ORDER_COUNT, TOTAL_SPENT, TAGS FROM mystream WHERE REGION='azure_eastus2' AND METADATA$ACTION = 'INSERT';

-- Promote the new database as primary
alter database PrimaryDB enable replication to accounts AZURE_EASTUS2.AcmeProviderAccount2;

use role accountadmin;

-- Replicate the existing database to a secondary database in the other region
create database SecondaryDB
  as replica of AWS_US_WEST_2.AcmeProviderAccount1.PrimaryDB;

-- Schedule refresh of the secondary database

create task refresh_SecondaryDB_task
  warehouse = mywh
  schedule = '10 minute'
as
  alter database SecondaryDB refresh;

alter task refresh_SecondaryDB_task resume;

-- Create a share
create share share1;

-- Add objects to the share:
grant usage on database SecondaryDB to share share1;
grant usage on schema SecondaryDB.sch to share share1;

grant select on view SecondaryDB.sch.view1 to share share1;

-- Add one or more consumer accounts to the share
alter share share1 add accounts=ConsumerAccount;