❄️

Snowflakeデータシェアリングのハンズオンイベントの環境構築

holywater044

2023/12/17に公開

Snowflake

snowflakedb

tech

本記事は、Snowflake Advent Calendar 2023 の 17 日目です。

はじめに

本記事は、2023年9月25日に名古屋で開催した、中部Tableauユーザー会 × SnowVillage共催イベント『Tableau＆Snowflakeハンズオン　データコラボレーションをぷち体験しよう』において、主催者側で準備したSnowflake環境の構築についてまとめたものです。
このイベントは、前半でSnowflakeでのデータシェアリング、後半でシェアリングしたデータをTableauで可視化するハンズオンで、参加者15名の想定で企画しました。（実際はキャンセルで13名で実施）
イベント内容の詳細については、こちらのSpeakerDeckをご参照ください。

環境構築の概要

やりたいこと

本イベントで行うデータシェアリングは、以下の状態からスタートします。

主催者のSnowflakeアカウントに愛知県データ
背番号が奇数の参加者のSnowflakeアカウントに岐阜県データ
背番号が偶数の参加者のSnowflakeアカウントに三重県データ

イベントの中で、参加者は２人ペアを組み（1番＆2番、3番＆4番、・・）、GUI操作やクエリ実行でデータシェアリングを行います。

主催者から、参加者全員へ愛知県データを共有
奇数参加者は、ペアの偶数参加者に、岐阜県データを共有
偶数参加者は、ペアの奇数参加者に、三重県データを共有

これにより、最初1県のデータしかもっていなかった参加者が、東海三県のデータを手にして、後半のTableauハンズオンに進むというものです。

本記事は、これを実行するためのSnowflake環境を、事前に構築するというものです。

実現方針

本イベントの環境は、Snowflakeのトライアルアカウントを利用させていただきました。
しかし、ハンズオン内容に、参加者自身によるデータシェアリングやマーケットプレースでのデータ購入を含むため、一人一人にSnowflakeアカウントとそのACCOUNTADMINロールを持つユーザを用意する必要がありました。
とはいえ、「参加者自身にトライアルアカウントを作成してきてもらう」というのは、Snowflake初心者を想定したハンズオンとしては、非現実的でした。

参加者に要求する技術レベルが高くなってしまう、事前の作業負担が大きい（敷居が上がってしまい、未経験者・初心者が参加しにくくなってしまう）
主催者と参加者の間で、データシェアリングに必要なアカウント情報や、使うデータやクエリのやり取りが必要になってしまう（事前にやるのも当日やるのもキツイ）

そこで、ORGADMINロールによるCREATE ACCOUNTクエリで、主催者のオーガニゼーションに参加者用のアカウントを作成して、提供することにしました。これなら、使うデータやクエリも、主催者が事前に配布しておくこともできます。

主催者用×1（トライアルで払い出されたアカウント）
参加者用×16（CREATE ACCOUNTクエリで作ったアカウント）

※ 参加者用が人数より1個多いのは、参加者操作のお手本を画面で見せるために、主催者が16人目の参加者を兼ねたためです

補足：Snowflakeのオーガニゼーションについて

普通に使うだけならあまり意識しなくていいんですが、Snowflakeには、アカウントの上位概念として、複数のアカウントを束ねるオーガニゼーションというものがあります。

オーガニゼーションで最初に作られたアカウントには、ORGADMINロールが存在し、CREATE ACCOUNTクエリでオーガニゼーション内に別のアカウントを作ることができます。（通常は25アカウントまで。超えたい場合はサポートに相談要）
なお、CREATE ACCOUNTクエリで作られた子アカウントは、通常はORGADMINロールが存在しません。（子アカウントへのORGADMIN追加は、サポートに相談要）

トライアルアカウントでもORGADMINが使えるか試したところ、普通に使えてアカウント増やせたので、今回のハンズオンを思いついた次第です。

なお、Snowflakeのトライアルを申し込むと、400$分のクレジットの範囲で自由に使うことができますが、これはアカウントではなくオーガニゼーションにチャージされているので、「アカウント増やせば400$をいっぱい使えるぜヒャッハー」というわけにはいきませんｗ。

ちなみに、Snowflake環境のURLには、2種類の表記方法がありますが、後者のハイフンを使った表記方法の前半にあるのがオーガニゼーション名です。

https://<アカウントロケーター>.<リージョンID>.<クラウド>.snowflakecomputing.com
https://<オーガニゼーション名>-<アカウント名>.snowflakecomputing.com

環境の全体像

今回は、下図のような環境を作りました。
毎度おなじみPrepperOpenDataBank（以下、PODB）の、市区町村人口データを使わせていただいています。（truestar社さんいつもありがとうございます！！）

この環境の作成の段取りは以下になります。

1. 主催者アカウントの準備
- 1-1. マーケットプレースでPODBのJAPANESE CENSUS & SPATIAL DATAを申請＆取得
- 1-2. 主催者アカウントにPODBから3県のデータ作成
- 1-3. 参加者アカウント16個の作成
- 1-4. 参加者アカウントへの岐阜県・三重県データの事前シェア
1. 参加者アカウントの準備
- 2-1. VWHとデータの準備
- 2-2. 参加者ユーザ（ACCOUTADMINロール）の作成
- 2-3. 参加者ユーザのSnowSight設定
1. 事前準備の後始末
- 3-1. 事前シェアの削除

以降で、各ステップのクエリの紹介と説明をしていきます。

1. 主催者アカウントの準備

まずは、主催者アカウント（親アカウント）の準備です。

1-1. PODBの申請＆取得

PODBの JAPANESE CENSUS & SPATIAL DATA を、マーケットプレースで利用申請して、取得します。手順については省略します。
なお、デフォルトだとDB名が長いため、取得時にDB名を PREPPER_OPEN_DATA_BANK にしています。

1-2. データの作成

ハンズオン用DBを作り、そこにPODBから3県それぞれのTABLEを作成します。

//■ 主催者アカウント準備　1-2. 3県のデータの作成
use role ACCOUNTADMIN;

//　DB作成
create database PROVIDER_DB;

// 主催者の手持ちデータ（愛知県）のテーブル作成
CREATE TABLE PROVIDER_DB.PUBLIC.AICHI_CITY_01 AS
  SELECT * FROM PREPPER_OPEN_DATA_BANK.J_PODB.J_CITY_01_CENSUS2020
  WHERE "都道府県コード"='23';

// 奇数参加者の手持ちデータ（岐阜県）のテーブル作成
CREATE OR REPLACE TABLE PROVIDER_DB.PUBLIC.GIFU_CITY_01 AS
  SELECT * FROM PREPPER_OPEN_DATA_BANK.J_PODB.J_CITY_01_CENSUS2020
  WHERE "都道府県コード"='21';

// 偶数参加者の手持ちデータ（三重県）のテーブル作成
CREATE OR REPLACE TABLE PROVIDER_DB.PUBLIC.MIE_CITY_01 AS
  SELECT * FROM PREPPER_OPEN_DATA_BANK.J_PODB.J_CITY_01_CENSUS2020
  WHERE "都道府県コード"='24';

1.3 参加者アカウントの作成

ORGADMINロールを使って、create account クエリで参加者アカウント16個を作成します。
オプションで参加者アカウントのACCOUNTADMINユーザ 'SV_ADMIN' を作成していますが、これは主催者用です。（参加者用のACCOUNTADMINユーザ 'HO_ADMIN' は、後で参加者アカウントの準備の方で作成します）

//■ 主催者アカウント準備：　1.3 参加者アカウントの作成
use role ORGADMIN; 

// 参加者人用アカウント作成
create account HO01  admin_name='SV_ADMIN'  admin_password='********'
  email='********'  edition=ENTERPRISE;
create account HO02  admin_name='SV_ADMIN'  admin_password='********'
  email='********'  edition=ENTERPRISE;
// 中略（HO03～HO15も同様に作成）
create account HO16  admin_name='SV_ADMIN'  admin_password='********'
  email='********'  edition=ENTERPRISE;

// 結果確認
show organization accounts;

ちなみに、上記create accountクエリでは、リージョンを指定していないので、主催者アカウント（トライアルアカウント）を作ったAWS東京で作成されています。
オプションでリージョンを指定すれば、子アカウントをAWS大阪やAZURE東京に作成することも可能です。
「DR（ディザスタリカバリ）のために、バックアップを別リージョンに・・」とかが簡単にできちゃいます。Snowflakeのすごいところの一つと思います！！

1-4. 参加者アカウントへの事前シェア

参加者アカウントに事前に岐阜県・三重県のデータを配布するために、SHAREオブジェクトを作って、データシェアリングします。
（後ほど、参加者アカウントでこれを取得して、奇数参加者は岐阜県、偶数参加は三重県のデータを作成します）

//■ 主催者アカウント準備　1-4. 参加者アカウントへの事前データシェアリング
use role ACCOUNTADMIN;

// 事前シェアを作成、データオブジェクトの権限を付与
create share PRE_SHARE;
grant usage on database PROVIDER_DB to share PRE_SHARE; 
grant usage on schema PROVIDER_DB.PUBLIC to share PRE_SHARE; 
grant select on table PROVIDER_DB.PUBLIC.GIFU_CITY_01 to share PRE_SHARE; 
grant select on table PROVIDER_DB.PUBLIC.MIE_CITY_01 to share PRE_SHARE; 

// 事前シェアに子アカウントの参照権を追加
//　※　一度に10個までしか追加できないので、8個×2回にしてる
alter share PRE_SHARE add accounts=
  LKZQOBG.HO01, LKZQOBG.HO02, LKZQOBG.HO03, LKZQOBG.HO04, LKZQOBG.HO05, 
  LKZQOBG.HO06, LKZQOBG.HO07, LKZQOBG.HO08;
alter share PRE_SHARE add accounts=
  LKZQOBG.HO09, LKZQOBG.HO10, LKZQOBG.HO11, LKZQOBG.HO12, LKZQOBG.HO13,
  LKZQOBG.HO14, LKZQOBG.HO15, LKZQOBG.HO16;
  
// 結果確認
show shares;
show grants to share PRE_SHARE;

いやぁ、create share から alter share の実質5行、

SHAREオブジェクトの作成
SHAREへのDB使用権付与
SHAREへのスキーマ使用権付与
SHAREへのテーブル参照権付与
SHAREへの共有先アカウント設定

でデータシェアリングできちゃうのは、Snowflakeホントすごいです！！

2. 参加者アカウントの準備 ×16

1-3で作成した16個の参加者アカウント（子アカウント）に対し、1アカウントずつ地道に作業するものです。（イベント前日深夜に、寝落ちしそうになりながらやってました・・）

2-1. VWHとデータの準備

SV_ADMIN でログインして実施する作業です。この準備およびハンズオンで使うオブジェクトを整えます。

create accountで作成したアカウントには初期状態でVWHが無いので作る
ハンズオン用DBを作る
1-4の事前シェアのデータを受け取る＝シェアDBを作る
当該アカウントで使うデータを、ハンズオンDBに実テーブルで作る（奇数アカウントと偶数アカウントでクエリが異なる）
終わったら、シェアDBを消す（ハンズオンで邪魔になるため）

//■ 参加者アカウント準備：　2.1 VWHとデータの作成
// SV_ADMIN　でログイン
use role ACCOUNTADMIN;

// VWH作成 　※子アカウントは、COMPUTE_WHが用意されていないため
create warehouse COMPUTE_WH warehouse_size=XSmall auto_suspend=60 initially_suspended=true statement_timeout_in_seconds=7200;

// 主催者からの事前データシェアリングの受取（DB作成）
create database PRE_SHARED_DB from share LKZQOBG.XU52167.PRE_SHARE;

// DB作成
create database HO_DB;

//　【注】　子アカウントが奇数か偶数かで、いずれか片方を実行（間違えないように！！）
// 奇数アカウント（岐阜）用
create table HO_DB.PUBLIC.MY_CITY_01
  as select * from PRE_SHARED_DB.PUBLIC.GIFU_CITY_01;
// 偶数アカウント（三重）用
create table HO_DB.PUBLIC.MY_CITY_01
  as select * from PRE_SHARED_DB.PUBLIC.MIE_CITY_01;

// 事前データシェアリングDBの削除
drop database PRE_SHARED_DB;

参加者が持つデータを、奇数参加者（岐阜県）・偶数参加者（三重県）とも同じテーブル名 MY_CITY_01 にすることで（入ってるデータは違う）、ハンズオンで実行するクエリを奇数・偶数で共通化できるようにしたのが工夫したところになります。

2-2. 参加者用ユーザの作成

主催者用の SV_ADIM とは別に、参加者用の ACCOUNTADMINユーザ HO_ADMIN を作成します。

// ■参加者アカウント準備：　2-2. 参加者ユーザの作成
use role USERADMIN;
create user HO_ADMIN  password='********'
  first_name ='********'  last_name='********'  email='****@****'
  default_role='ACCOUNTADMIN'  default_warehouse='COMPUTE_WH'
  default_namespace='HO_DB.PUBLIC';

use role SECURITYADMIN;
grant role ACCOUNTADMIN to user HO_ADMIN;

参加者がスムーズに使えるように、オプションでデフォルトのロール・VWH・DB＆スキーマを設定しています。（いちいち自力で選択しなくてよい）
また、ハンズオンではデータシェアリングの他に、マーケットプレースでPODBのカレンダーデータの申込みも行うため、その際必要な first_name・last_name・email もオプションで設定しています。

2-3. 参加者ユーザのSnowSight準備

2-2で作った参加者用ユーザ HO_ADMIN にログインして、参加者のSnowSightの設定を整えます。

ユーザのプロファイルで、languageを日本語に変更
ハンズオン用ワークシートの作成して、参加者が当日実行するクエリを張り付けておく

同じアカウント内であれば、別ユーザのワークシートが共有できるらしいのですが、今回はアカウントから別になるため、上記作業は手作業で実施しました。（誰かもっと簡単な方法を知っていたら教えてもらえるとありがたいです・・）

3. 事前準備の後始末

すべての参加者アカウントで2.が終わったら、もう一度主催者アカウントにログインして、事前シェアを削除しておきます。

// ■主催者アカウント後始末：　3-1. 準備用シェアを削除
drop database TMP_SHARE;

参加者アカウントでは、2-1の最後で準備用シェアに紐づくシェアDBは消しただけだと、まだSnowSightの共有データのメニューには、準備用シェアが残っている状態です。ハンズオンで参加者がデータシェアリングする際に混乱しないように、消しておくことにしました。
主催者アカウントで準備用シェアを削除することで、参加者アカウントの共有データからも消えます。

おまけ：イベント終了後の後始末

今回はハンズオンの内容上、参加者にACCOUTADINユーザを渡さざるを得ませんでした。ちょっと強力すぎる権限なので、イベント終了後に、参加者アカウント16個に入って、'HO_ADMIN' を無効化しました。

// ■イベント終了後：　SV_ADMIN　でログイン
//　参加者用ユーザの停止
alter user HO_ADMIN set DISABLED = TRUE;
//　結果確認
show users;

ちなみに、一応、ログイン履歴やクエリ履歴の確認とかもしてたりするのですが、ここでは省略します。

まとめ

本イベントは、「自力でアカウントを作成できる」と「簡単データシェアリングできる」というSnowflakeの特徴を活かすことで実現できました。これだけの準備ですんだのは、Snowflakeならではないかと思います。
振り返ると、ストアドプロシージャを使うことで、もう少し準備を楽にすることができたように思うので、次の機会には工夫していきたいです。

最後になりますが、本イベントの開催にあたっては、中部Tableauユーザー会の他の幹事の皆さんはもちろん、Snowflake社さま、Salesforce社さま、そしてTRUESTAR社さまから、強力な援軍をいただきました。
協力いただいた皆さんのおかげで、以前から目標にしていた「東海地方でのSnowflakeコミュニティイベント開催」を実現することができました。この場を借りて、あらためて深くお礼申し上げます。

最後まで読んでいただきありがとうございました。みなさま、よいお年をお迎えください！

Snowflake Data HeroesPublication

Snowlfake データクラウドのユーザ会 SnowVillage のメンバーで運営しています。 Publication参加方法はこちらをご参照ください。 zenn.dev/dataheroes/articles/db5da0959b4bdd