SnowflakeのReaderアカウントを作成してデータを共有してみよう

こんにちは！DA(データアナリティクス)事業本部 インテグレーション部の大高です。

Snowflakeの「Readerアカウント」が面白そうだったので「データベースを作成して共有～Readerアカウントを発行して参照」までを実際に試してみました。なお、GUIベースでは以下の記事にもまとめられていますので、今回はSQLベースで試してみます。

Readerアカウントとは？

Snowflakeのデータ共有の仕組みの中に「Readerアカウント」というものがあります。

提供者である「Providerアカウント」が管理する読み取り専用のアカウントで、クレジット（課金）も「Providerアカウント」に課せられます。また「Readerアカウント」は他のアカウントの情報は見ることができず、「Providerアカウント」が提供したデータ（共有データベース）のみ利用できるというのも特徴になります。

Managing Reader Accounts — Snowflake Documentation

以下が、ヘルプページにあるイメージ図です。

データの準備

それではまず「Readerアカウント」に共有するデータベースの準備を行っていきます。

ロールの作成

まずは、データベースを利用するロールの作成から行っていきます。今回の「Readerアカウント」に関してはあまり意味が無いのですが、実際のユースケースとして、個別にデータベース利用のロールを作成することもあるかと思いますので、ここから開始しています。

-- SECURITYADMIN ロールで作業
USE ROLE securityadmin;

-- ロールを新規作成して、SYSADMINを割り当て
CREATE ROLE ootaka_sandbox_role COMMENT='cm-ootaka sandbox role';
GRANT ROLE ootaka_sandbox_role TO ROLE sysadmin;

以上で、ロールが作成されました。ロール作成時のお作法として、カスタムロールはSYSADMIN配下にするという作法に従っています。

詳しくは、下記ヘルプの「Role Hierarchy and Privilege Inheritance」の項をご参照ください。

Overview of Access Control — Snowflake Documentation

データベースの作成

次に、データベースを作成します。単純にデータベースを作成し、先程作成したロールに権限を付与しています。

-- SYSADMIN ロールで作業
USE ROLE sysadmin;

-- データベース作成
CREATE DATABASE IF NOT EXISTS ootaka_sandbox_db COMMENT='cm-ootaka sandbox database';

-- OOTAKA_SANDBOX_ROLE へ権限付与
GRANT OWNERSHIP ON DATABASE ootaka_sandbox_db TO ootaka_sandbox_role;
GRANT ALL ON ALL SCHEMAS IN DATABASE ootaka_sandbox_db TO ootaka_sandbox_role;

これでデータベースができました。

テーブルの作成

次に、テーブルを作成しデータを投入しておきます。テーブルはpublicスキーマに作成し、テーブルの構造、および、データは既存のサンプルデータである「snowflake_sample_data.tpch_sf1.region」から投入します。

なお「snowflake_sample_data」も共有データベースですね。

-- OOTAKA_SANDBOX_ROLE ロールで作業
USE ROLE ootaka_sandbox_role;

-- テーブルを作成
USE DATABASE ootaka_sandbox_db;
CREATE TABLE IF NOT EXISTS public.region AS
  SELECT * FROM snowflake_sample_data.tpch_sf1.region;

これで、「Readerアカウント」に共有するデータベースの準備が整いました。本来であれば、セキュリティーを考慮して「セキュアビュー」として共有すべきかと思いますが、今回はテーブルのままでいきます。

SHARE（共有）の作成

データベースの準備ができたので、今度はこれを共有できるように設定します。共有を行うためには「SHARE」を作成し、「SHARE」に対して共有させたいデータベース、スキーマ、テーブルへの権限を与えます。

-- ACCOUNTADMIN ロールで作業
USE ROLE accountadmin;

-- 共有の作成
CREATE SHARE ootaka_sandbox_share;

-- 権限の付与
GRANT USAGE ON DATABASE ootaka_sandbox_db TO SHARE ootaka_sandbox_share;
GRANT USAGE ON SCHEMA ootaka_sandbox_db.public TO SHARE ootaka_sandbox_share;
GRANT SELECT ON TABLE ootaka_sandbox_db.public.region TO SHARE ootaka_sandbox_share;

-- 権限の確認
SHOW GRANTS TO SHARE ootaka_sandbox_share;

権限が付与できていると、以下のように表示されるはずです。

created_on	privilege	granted_on	name	granted_to	grantee_name	grant_option	granted_by
2020-03-04 16:40:46.000 -0800	USAGE	DATABASE	OOTAKA_SANDBOX_DB	SHARE	FOO.OOTAKA_SANDBOX_SHARE	false	OOTAKA_SANDBOX_ROLE
2020-03-04 16:40:47.000 -0800	USAGE	SCHEMA	OOTAKA_SANDBOX_DB.PUBLIC	SHARE	FOO.OOTAKA_SANDBOX_SHARE	false	SYSADMIN
2020-03-04 16:40:47.000 -0800	SELECT	TABLE	OOTAKA_SANDBOX_DB.PUBLIC.REGION	SHARE	FOO.OOTAKA_SANDBOX_SHARE	false	OOTAKA_SANDBOX_ROLE

Readerアカウントの作成と共有設定

最後に、「Readerアカウント」を作成し共有設定を行います。「Readerアカウント」作成時には、同時に管理者(ADMIN)ユーザアカウントのADMIN_NAMEとADMIN_PASSWORDを設定します。ADMIN_NAMEには「英数字のみ」利用可能です。

-- ACCOUNTADMIN ロールで作業
USE ROLE accountadmin;

-- READER ACCOUNTの作成 (ADMIN_NAMEには英数字のみ利用可)
CREATE MANAGED ACCOUNT ootaka_reader_account
  ADMIN_NAME = ootakareader,
  ADMIN_PASSWORD = 'Ootaka_Password!_2020',
  TYPE = reader
;

作成直後に、結果として以下のようにアカウント情報（アカウント名とログインURL）が出力されます。

{"accountName":"UR09592","loginUrl":"https://ur09592.ap-northeast-1.aws.snowflakecomputing.com"}

また、下記のコマンドで改めて確認することもできます。

-- READER ACCOUNTの確認
SHOW MANAGED ACCOUNTS;

アカウントが作成されたら、先程作成した「SHARE」に対してアカウントを追加します。ACCOUNTSに指定するのは、先程確認したaccountNameの値です。

-- SHAREにアカウントを追加（ACCOUNTSにはReader AccountのaccountNameを指定）
ALTER SHARE ootaka_sandbox_share ADD ACCOUNTS = UR09592;

-- SHAREの確認
SHOW SHARES;

うまく共有されていれば、以下のように表示されるはずです。

created_on	kind	name	database_name	to	owner	comment
2019-12-13 21:13:35.224 -0800	INBOUND	SFC_SAMPLES.SAMPLE_DATA	SNOWFLAKE_SAMPLE_DATA			
2019-12-23 10:59:45.718 -0800	INBOUND	SNOWFLAKE.ACCOUNT_USAGE	SNOWFLAKE			
2020-03-04 16:40:46.032 -0800	OUTBOUND	FOO.OOTAKA_SANDBOX_SHARE	OOTAKA_SANDBOX_DB	UR09592	ACCOUNTADMIN	

いざ、Readerアカウント側で確認

それではReaderアカウント側で確認してみましょう。先程発行したアカウントのURL（https://ur09592.ap-northeast-1.aws.snowflakecomputing.com）にアクセスし、作成した「Readerアカウント」のADMIN_NAMEとADMIN_PASSWORDでログインします。

ログイン直後に、以下のようなダイアログが表示されて「Data Provider」なのか「Data Consumer」なのかを聞かれます。

「Data Provider」を選択すると、下記のように「Reader Account Setup」のワークシートが表示されて、アカウントセットアップの流れを指示してくれます。このあたり、とても親切ですね。また、画面左上に「Reader」と表示されていて、「Readerアカウント」であることも分かりますね。

今回は一旦このあたりは飛ばしまして、共有したデータベースの表示だけやります。

まずは「Provier」アカウントで作成した「SHARE」からデータベースを作成します。データベース名は任意ですが、今回は共有元と同じ名前にしています。

-- ACCOUNTADMIN ロールで作業
USE ROLE accountadmin;

-- SHAREからデータベースを作成
CREATE DATABASE ootaka_sandbox_share FROM SAHRE foo.ootaka_sandbox_share;

データベースができたら、データを確認します。その際にウェアハウスが必要になるので、併せて作成してからSELECTしています。

-- データ確認用にウェアハウスを作成
CREATE WAREHOUSE my_wh warehouse_size=large initially_suspended=true;

-- データを確認
SELECT * FROM ootaka_sandbox_share.public.region LIMIT 3;

R_REGIONKEY	R_NAME	R_COMMENT
0	AFRICA	lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to 
1	AMERICA	hs use ironic, even requests. s
2	ASIA	ges. thinly even pinto beans ca

共有されたデータの確認ができました！

後片付け

最後に後片付けです。

Readerアカウントと共有の削除

「Provider」側のアカウントに戻って、「Readerアカウント」と「SHARE」を削除します。

-- ACCOUNTADMIN ロールで作業
USE ROLE accountadmin;

-- Readerアカウントの削除
DROP MANAGED ACCOUNT ootaka_reader_account;

-- SHAREの削除
DROP SHARE ootaka_sandbox_share;

また、不要であればデータベースとロールも削除してしまいましょう。

-- データベースの削除
DROP DATABASE ootaka_sandbox_db;

-- ロールの削除
DROP ROLE ootaka_sandbox_role;

まとめ

以上、「データベースを作成して共有～Readerアカウントを発行して参照」まで、でした。読み取り専用とはいえ、「Readerアカウント」として、まるっと環境を作成できるのは面白いですね。

どなたかのお役に立てば幸いです。それでは！