Snowflakeが今熱いらしいので試しに使ってみた

# 検証用のデータベースとウェアハウスを作成
use role sysadmin;
create database kido_db;
create or replace WAREHOUSE kido_wh WITH WAREHOUSE_SIZE = 'XSMALL';

# 検証用ユーザーに割当てるロールを作成
use role securityadmin;
create or replace role kido_role;
grant role kido_role to role sysadmin;

# 作成したロールに検証用リソースの所有権を移す
use role sysadmin;
grant ownership on database kido_db to role kido_role;
grant ownership on schema kido_db.public to role kido_role;
grant ownership on warehouse kido_wh to role kido_role;

# 検証用ユーザーを作成
use role useradmin;
create or replace user kido password='password' default_role='kido_role' must_change_password=TRUE;

# 検証用ユーザーにロールを割り当てる
use role securityadmin;
grant role kido_role to user kido;

use role kido_role;
use database kido_db;
use schema public;
use warehouse kido_wh;

# テーブルを作成
create or replace table trips  
(tripduration integer,
  starttime timestamp,
  stoptime timestamp,
  start_station_id integer,
  start_station_name string,
  start_station_latitude float,
  start_station_longitude float,
  end_station_id integer,
  end_station_name string,
  end_station_latitude float,
  end_station_longitude float,
  bikeid integer,
  membership_type string,
  usertype string,
  birth_year integer,
  gender integer);

# S3に置いてあるデータを指定
create or replace stage citibike_trips URL = 's3://snowflake-workshop-lab/citibike-trips';

# データのファイルフォーマットを指定
create or replace file FORMAT CSV TYPE = 'CSV' COMPRESSION = 'AUTO' FIELD_DELIMITER = ',' RECORD_DELIMITER = '\n' SKIP_HEADER = 0 FIELD_OPTIONALLY_ENCLOSED_BY = '\042' TRIM_SPACE = FALSE ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE ESCAPE = 'NONE' ESCAPE_UNENCLOSED_FIELD = '\134' DATE_FORMAT = 'AUTO' TIMESTAMP_FORMAT = 'AUTO' NULL_IF = ('');

# テーブルにデータをロード（実行時間は数秒かかります）
copy into trips from @citibike_trips
file_format=CSV;

select count(*) from trips;

select count(*) from trips group by usertype;

create share my_share;

// 共有するオブジェクトを指定
grant usage on database kido_db to share my_share;
grant usage on schema public to share my_share;
grant select on table trips to share my_share;

// 誰に共有するか指定
alter share my_share add accounts=xy56789;

create database shared from share ab01234.my_share;

select * from shared.public.trips limit 10;