Snowflake functions & usage query

1. Grant privileges

1-1. Grant permission to select a 'sales' database to 'sales' team

-- 1. Use accountadmin role 
USE ROLE accountadmin;

-- 2. create role
CREATE ROLE sales_team;
SHOW GRANTS TO ROLE sales_team; -- check

-- 3. grant permissions to role
GRANT USAGE ON DATABASE {database_name} TO ROLE sales_team;
GRANT USAGE ON SCHEMA {database_name}.{shema_name} TO ROLE sales_team;
GRANT SELECT ON ALL TABLES IN SCHEMA {database_name}.{shema_name} TO ROLE sales_team;

-- 4. grant permissions to user
GRANT ROLE sales_team TO USER {user_name};

-- 5. revoke permissions
REVOKE ALL PRIVILEGES ON SCHEMA {database_name}.{shema_name} from role sales_team;

2. Load Data

2-1. Load Bulk data (COPY command)

1) Snowflake data(A table) > S3 bucket

COPY INTO 's3://{bucket_nm}/{folder_nm}/{file_nm}.parquet' 
FROM (select * from {database_nm}.{schema_nm}.{table_nm})
    CREDENTIALS = (AWS_KEY_ID='{aws_key_id}' AWS_SECRET_KEY='{aws_secret_key}');

• Windows : C:\Users\User_name.aws\credentials 에 있는 key, secret 활용

2) S3 bucket > Snowflake data(B table)

2-2. Snowpipe

1) Create Stage

CREATE STAGE mystage
  URL = 's3://{bucket_nm}/{folder_nm}/'
  CREDENTIALS = (AWS_KEY_ID='{aws_key_id}' AWS_SECRET_KEY='{aws_secret_key}');

2) Create pipe (Enabled automatic data collection)

create pipe {DB_nm}.{schema_nm}.{snowpipe_nm} auto_ingest=true as
  copy into {DB_nm}.{schema_nm}.{table_mm}
  from @{DB_nm}.{schema_nm}.mystage
  file_format = (type = 'CSV');

You can see data in {DB_nm}.{schema_nm}.{table_mm}

3) Delete Staged file
Snowpipe cannot automatically delete staged file once data is loaded into the table.
To remove staged file that is no longer needed, I recommend periodically run the 'REMOVE' comand to delete the file.

-- Remove all files in path1 directory of mystage
REMOVE @mystage/path1;

-- Remove all files in specific table of stage
REMOVE @%{table_nm};

3. Data Sharing and Cooperation

3-1. Data Sharing

• 데이터를 여러 조직 또는 계정 간에 안전하게 공유할 수 있는 기능
• 다른 snowflake 계정에 data를 공유하고 싶을 때 사용

----------------------------------------------------------
-- ##  ORGADMIN data sharing to sales_team account  ##  -- 
----------------------------------------------------------

-- ### 1. Create share objects
CREATE SHARE my_share;

-- ### 2. Provider grants usage on database, schema, tables to share
GRANT USAGE ON DATABASE {database_name} TO SHARE my_share;
-- GRANT USAGE ON ALL SCHEMAS IN DATABASE {database_name} TO SHARE my_share;
GRANT USAGE ON SCHEMA {database_name}.{schema_name} TO SHARE my_share;  -- specific schema
GRANT SELECT ON ALL TABLES IN SCHEMA {database_name}.{schema_name} TO SHARE my_share;

-- ### 3. Provider adds consumer accounts to the share
ALTER SHARE my_share ADD ACCOUNTS = {account};  -- SF> Admin> Accounts> Locator_info

-- ### 4. Consumer creates a database from the share(Work in above account url)
-- !! Transfer Consumer account !!
CREATE DATABASE my_shared_database FROM SHARE <provider_account_identifier>.my_share;

-- ### 5. Now consumer can query the shared data
SELECT * FROM my_shared_database.my_schema.my_table;

💥 주의 💥

When using datahub, if the ownership is different, data connection is not possible.

• Change the onwership of the table

grant ownership on {database_nm}.{schema_nm}.{table_nm} TO ACCOUNTADMIN REVOKE CURRENT GRANTS;