[Snowflake] Data sharing

1. Understanding data sharing

• 일반적인 데이터 공유는 복잡한 프로세스를 가지고 있음
• Snowflake의 데이터 공유는 데이터의 실제 복사본 없이 데이터를 공유하고 최신 상태로 유지함
• 공유 데이터는 자체 컴퓨팅 리소스에 의해 소비됨
• Snowflake를 사용하지 않는 유저도 reader 계정으로 접근할 수 있음

 

2. Using data sharing

• 데이터베이스 생성

CREATE OR REPLACE DATABASE DATA_S;

 

• 스테이지 생성

CREATE OR REPLACE STAGE aws_stage
    url='s3://bucketsnowflakes3';

 

• 스테이지 확인

LIST @aws_stage;

 

• 테이블 생성

CREATE OR REPLACE TABLE ORDERS (
ORDER_ID VARCHAR(30)
,AMOUNT NUMBER(38,0)
,PROFIT NUMBER(38,0)
,QUANTITY NUMBER(38,0)
,CATEGORY VARCHAR(30)
,SUBCATEGORY VARCHAR(30));

 

• 데이터 로드

COPY INTO ORDERS
    FROM @MANAGE_DB.external_stages.aws_stage
    file_format= (type = csv field_delimiter=',' skip_header=1)
    pattern='.*OrderDetails.*';

 

• 데이터 확인

SELECT * FROM ORDERS;

 

• share 객체 생성

CREATE OR REPLACE SHARE ORDERS_SHARE;

 

• 데이터베이스에 권한 부여

GRANT USAGE ON DATABASE DATA_S TO SHARE ORDERS_SHARE;

 

• 스키마에 권한 부여

GRANT USAGE ON SCHEMA DATA_S.PUBLIC TO SHARE ORDERS_SHARE;

 

• 테이블에 권한 부여

GRANT SELECT ON TABLE DATA_S.PUBLIC.ORDERS TO SHARE ORDERS_SHARE;

 

• 권한 확인

SHOW GRANTS TO SHARE ORDERS_SHARE;

 

• 소비 계정 추가

ALTER SHARE ORDERS_SHARE ADD ACCOUNT=<consumer-account-id>;

 

 

3. Create share through the interface

 

 

 

4. Sharing with non-snowflake users

• 새 reader 계정 : 자체 URL 및 자체 계산 리소스가 있는 독립 인스턴스 생성
• 데이터 공유 : 데이터베이스 및 테이블을 공유할 수 있음
• 사용자 생성 : 관리자로서 사용자 및 역할을 생성함
• 데이터베이스 생성 : reader 계정에서 데이터베이스를 만들기 위해 공유

 

 

5. Creating a reader account

• reader 계정 생성

CREATE MANAGED ACCOUNT tech_joy_account
ADMIN_NAME = tech_joy_admin,
ADMIN_PASSWORD = 'Qwer1234',
TYPE = READER;

 

• 생성한 계정 확인

SHOW MANAGED ACCOUNTS;

 

• SHOW MANAGED ACCOUNTS 명령어를 실행해 얻을 수 있는 locator 값을 ADD ACCOUNT 값에 넣어줌

ALTER SHARE ORDERS_SHARE 
ADD ACCOUNT = NN87254;

 

• 업무상 중요하지 않은 계정 추가 옵션 - SHARE_RESTRICTIONS

ALTER SHARE ORDERS_SHARE 
ADD ACCOUNT =  <reader-account-id>
SHARE_RESTRICTIONS=false;

 

6. Creating a database from share

새롭게 생성한 SHARE url로 접근하여 실행
(ACCOUNTADMIN role 부여)

• SHARES 객체 확인

SHOW SHARES;

 

• SHOW SHARES 명령어를 통해 얻은 ID를 통해 더 자세한 내용 확인

DESC SHARE TQKNKHN.FQ30687.ORDERS_SHARE;

 

• SHARE 데이터베이스 생성

CREATE DATABASE DATA_SHARE_DB FROM SHARE TQKNKHN.FQ30687.ORDERS_SHARE;

 

• 테이블 확인 - 오류 발생(웨어하우스 실행 오류)

SELECT * FROM  DATA_SHARE_DB.PUBLIC.ORDERS;

 

• 웨어하우스 생성

CREATE WAREHOUSE READ_WH WITH
WAREHOUSE_SIZE='X-SMALL'
AUTO_SUSPEND = 180
AUTO_RESUME = TRUE
INITIALLY_SUSPENDED = TRUE;

 

7. Set up users for share

• 사용자 생성

CREATE USER MYRIAM PASSWORD = 'difficult_passw@ord=123';

 

• 웨어하우스에 권한 부여

GRANT USAGE ON WAREHOUSE READ_WH TO ROLE PUBLIC;

 

• 다른 사용자에게 권한 부여

GRANT IMPORTED PRIVILEGES ON DATABASE DATA_SHARE_DB TO ROLE PUBLIC;

 

• 새로운 창을 열어 같은 url로 접속 - 생성한 사용자로 로그인 

 

 

8. Sharing database & schema

• SHARE 확인

SHOW SHARES;

 

• SHARE 객체 생성

CREATE OR REPLACE SHARE COMEPLETE_SCHEMA_SHARE;

 

• 데이터베이스에 권한 부여

GRANT USAGE ON DATABASE OUR_FIRST_DB TO SHARE COMEPLETE_SCHEMA_SHARE;

 

• 스키마에 권한 부여

GRANT USAGE ON SCHEMA OUR_FIRST_DB.PUBLIC TO SHARE COMEPLETE_SCHEMA_SHARE;

 

• 모든 테이블에 조회 권한 부여

GRANT SELECT ON ALL TABLES IN SCHEMA OUR_FIRST_DB.PUBLIC TO SHARE COMEPLETE_SCHEMA_SHARE;
GRANT SELECT ON ALL TABLES IN DATABASE OUR_FIRST_DB TO SHARE COMEPLETE_SCHEMA_SHARE;

 

• SHARE에 계정 추가

ALTER SHARE COMEPLETE_SCHEMA_SHARE
ADD ACCOUNT=KAA74702;

 

• 데이터 변경

UPDATE OUR_FIRST_DB.PUBLIC.ORDERS
SET PROFIT=0 WHERE PROFIT < 0;

 

• 테이블 추가

CREATE TABLE OUR_FIRST_DB.PUBLIC.NEW_TABLE (ID int);

 

 

 

9. Secure vs. normal view

• 데이터베이스 생성

CREATE OR REPLACE DATABASE CUSTOMER_DB;

 

• 테이블 생성

CREATE OR REPLACE TABLE CUSTOMER_DB.public.customers (
   id int,
   first_name string,
  last_name string,
  email string,
  gender string,
  Job string,
  Phone string);

 

• file format 생성    

CREATE OR REPLACE FILE FORMAT MANAGE_DB.file_formats.csv_file
    type = csv
    field_delimiter = ','
    skip_header = 1;

 

• 스테이지 생성

CREATE OR REPLACE STAGE MANAGE_DB.external_stages.time_travel_stage
    URL = 's3://data-snowflake-fundamentals/time-travel/'
    file_format = MANAGE_DB.file_formats.csv_file;

 

• 스테이지 확인

LIST  @MANAGE_DB.external_stages.time_travel_stage;

 

• 데이터 로드

COPY INTO CUSTOMER_DB.public.customers
FROM @MANAGE_DB.external_stages.time_travel_stage
files = ('customers.csv');

 

• 데이터 확인

SELECT * FROM  CUSTOMER_DB.PUBLIC.CUSTOMERS;

 

• view 생성

CREATE OR REPLACE VIEW CUSTOMER_DB.PUBLIC.CUSTOMER_VIEW AS
SELECT 
FIRST_NAME,
LAST_NAME,
EMAIL
FROM CUSTOMER_DB.PUBLIC.CUSTOMERS
WHERE JOB != 'DATA SCIENTIST';

 

• 권한 부여

GRANT USAGE ON DATABASE CUSTOMER_DB TO ROLE PUBLIC;
GRANT USAGE ON SCHEMA CUSTOMER_DB.PUBLIC TO ROLE PUBLIC;
GRANT SELECT ON TABLE CUSTOMER_DB.PUBLIC.CUSTOMERS TO ROLE PUBLIC;
GRANT SELECT ON VIEW CUSTOMER_DB.PUBLIC.CUSTOMER_VIEW TO ROLE PUBLIC;

 

• view 확인

SHOW VIEWS LIKE '%CUSTOMER%';

 

• secure view 생성

CREATE OR REPLACE SECURE VIEW CUSTOMER_DB.PUBLIC.CUSTOMER_VIEW_SECURE AS
SELECT 
FIRST_NAME,
LAST_NAME,
EMAIL
FROM CUSTOMER_DB.PUBLIC.CUSTOMERS
WHERE JOB != 'DATA SCIENTIST';

 

• view에 조회 권한 부여

GRANT SELECT ON VIEW CUSTOMER_DB.PUBLIC.CUSTOMER_VIEW_SECURE TO ROLE PUBLIC;

 

• view 확인

SHOW VIEWS LIKE '%CUSTOMER%';

 

10. Sharing a secure view

• share 확인

SHOW SHARES;

 

• share 객체 생성

CREATE OR REPLACE SHARE VIEW_SHARE;

 

• 데이터베이스에 권한 부여

GRANT USAGE ON DATABASE CUSTOMER_DB TO SHARE VIEW_SHARE;

 

• 스키마에 권한 부여

GRANT USAGE ON SCHEMA CUSTOMER_DB.PUBLIC TO SHARE VIEW_SHARE;

 

• view에 조회 권한 부여

GRANT SELECT ON VIEW  CUSTOMER_DB.PUBLIC.CUSTOMER_VIEW TO SHARE VIEW_SHARE;
GRANT SELECT ON VIEW  CUSTOMER_DB.PUBLIC.CUSTOMER_VIEW_SECURE TO SHARE VIEW_SHARE;

 

• share에 계정 추가

ALTER SHARE VIEW_SHARE
ADD ACCOUNT=KAA74702;

 

 

Udemy의 'Snowflake - The Complete Masterclass (2023 Edition)'를 공부한 내용을 바탕으로 작성하였습니다.