前回までで、 Snowflake に Shopify データがある・ dbt Cloud のアカウントを作ったという状態で、このやってみたのお題をもらった方から「Shopify x Fivetran は dbt パッケージがあるのでいいですよ〜」と教えてもらってたので、それを使うところから始まった。パッケージの GitHub はここ↓で、作成されるモデルの dbt docs も README からリンクが貼ってあって神。

https://github.com/fivetran/dbt_shopify

モデルを作ってくれる dbt package を使ったことがなかった + dbt Cloud もよくわからんところからはじめたので、README を見てもイマイチよくわからなかったんだけど

package.yml に追加
dbt run を実行

すると、たしかに Snowflake 上にモデルが出来ててすっごい便利だった。

疑問

(1) models/ 以下に新しくできた Shopify の stg/int/mart モデルは増えてないけどいいんだよね...?

fivetran/shopify パッケージが model の定義を持ってるから、自分のコードには定義は追加されないってこと? といったん納得してみた。dbt docs の方には反映されてたのでよさそう

(2) project, database/schema がめちゃめちゃ分かれちゃったんだけど

project に関しては、Shopify だけで shopify_source と shopify でわかれちゃったし、Snowflake 側も

fivetran_data.shopify schema 以下に source のテーブル
pc_dbt_db Database の中に dbt_sobacha dbt_sobacha_shopify dbt_sobacha_stg_shopify schema の中に各 stg/int/mart モデル用のテーブル

という感じになって、これでいいんだっけ...? という気持ちになった。とはいえ、 fivetran/dbt の docs を見ると project は source とそれ以外で分かれているのでそういうものなのかも?

2. semantic layer の設定

このガイドの step 8 から進める感じにした。step 8 では、 orders と customers テーブルを作っていて、これを staging 層から自分で作るか、それとも fivetran/shopify パッケージが作ってくれた mart テーブルの上に作るかで迷ったんだけど、以外と order_date とか total を計算するのが大変そうだったので後者にした。

ガイドにある fct_orders と dim_customers のモデルを作ったあとは、ガイドの metrics ファイルをコピペした。

疑問というか

コピペして、だいたいは BI で表示するところまで行けるんだけど、 large_orders measure に関してだけこういうエラーが出た。疲れてこれはトラシューしてない。たぶん Dimension('order_id__order_count_dim') みたいなやつが怪しいと思うんだけど...

3. semantic layer の接続設定

とりまガイドの続きとクラメソさんの記事を読めばいいんだけど、なんかいろいろうまくいかなくて大変だった。

やらかし 1: クエリが "No active warehouse selected" になる

起こったことは、接続はうまくいって steep 上に metrics 一覧は出るんだけど、 metrics のグラフを表示しようとするとエラーメッセージが出る、っていう。エラー画面はこう↓

steep のエラーメッセージは普通に謎だけど、 Snowflake 側のクエリ実行履歴を見ると、

No active warehouse selected in the current session. Select an active warehouse with the 'use warehouse' command.

ということだった。ようは use warehouse hoge_warehouse できてないよ、ってことっぽくて、SL の接続用につくった Snowflake user を確認すると、 default warehouse が設定されてなかった。

やらかし? 2: user の設定を変えても反映されない

ということで SL 用 user に default warehouse をつけたんだけど、引き続き同じエラーが出続けてガン萎えした。明確に解決してないんだけど、

別のユーザをつくる
dbt Cloud で Service Token を発行しなおす
Google Sheets でも試して同じエラーになることを確認 (i.e. steep 固有の問題でない）

あたりを試したら、時間経過のせいかとりあえず別のユーザで接続できるようになった。どっかでキャッシュしてるのかな...

なんやかんやで BI は表示できた

わーい。前のステップでバリ疲れたので、もう表示できただけで嬉しかった。

こういう↓感じで time grain を daily/monthly とかで切り替えられるし 28 days の moving average とかも使えるので、うまく導入できれば Spreadsheet でサグラダファミリアを作らなくてすみそう。

おまけ: Snowflake で readonly/select 権限をつける

User/Role を作るにあたって結局なんの権限をどのレイヤーにつければいいのか混乱したんだけど、ここ↓がわかりやすかった。

たとえば、 my_db の my_schema 以下にあるすべての table/view に権限をつけたい場合は

grant usage on database my_db to role hoge_role;
grant usage on schema my_schema to role hoge_role;

grant select on all tables in database my_db to role hoge_role;
grant select on future tables in database my_db to role hoge_role;
grant select on all views in database my_db to role hoge_role;
grant select on future tables in database my_db to role hoge_role;

だと思う、試行錯誤して発行してたのであとで修正するかも。なんか、今回みたいな database も schema もいっぱいあるよ、ってときだとちょっとめんどくさいな... と思った。

2024-04-26

やってみた: Shopify → Fivetran → Snowflake → dbt Cloud → steep PART 1

pre-amble
Snowflake 入門
steps
感想

pre-amble

「Shopify のデータを Fivetran で ETL して、 semantic layer を使って BI にデータ出してみよう」というお題をいただいたのでやってみた。今回の記事は dbt Cloud に持ってくとこまで。

特別なことをしてないのでわりとチュートリアル通りだけど、概念の整理とかトラシューとかを書いていく。

ETL セットアップの流れとしてはこんな感じ↓

Fivetran に Snowflake destination を作成
Shopify の Fivetran connector を作る
Snowflake のスキーマ確認
dbt Cloud の接続

Snowflake 入門

Fivetran に destination を作る前に、そもそも Snowflake が初見なので 30分くらいで入門してみた。30分は盛ってるかもだけど、半日はかけなかった。

アカウント作成

Snowflake は free trial ができるので、とりあえずアカウントを作る。すると、自動的に Getting started 系のデータや Worksheets っていうやつを作ってくれるので、そこで UI の操作とか概念を学べる。

ちなみに、アカウント選択時に「よく使う言語はなんですか?」と聞かれて、そこで選択した言語に応じてサンプル Worksheets を作ってくれる。自分は Python と SQL を選択したので、こういう感じで作ってもらえた↓

各種リソース

自分の理解だと、リソースはだいたいこんな感じ↓

Worksheets
Data
Warehouse
Users, Roles

Worksheets

Worksheets っていうのは「ファイル」的なもので、 Python, SQL, Java など各種言語で記述できる。

驚きポイントとしては、 Worksheet 1枚で ETL が実現できることで、 Getting started の Worksheet だと S3 の public データから Snowflake にデータを流すことまでやってた。

SQL の Worksheet のコードを抜粋するとこうなる↓

-------------------------------------------------------------------------------------------
    -- Step 3: To connect to the Blob Storage, let's create a Stage
        -- Creating an S3 Stage: https://docs.snowflake.com/en/user-guide/data-load-s3-create-stage
-------------------------------------------------------------------------------------------

---> create the Stage referencing the Blob location and CSV File Format
CREATE OR REPLACE STAGE tasty_bytes_sample_data.public.blob_stage
url = 's3://sfquickstarts/tastybytes/'
file_format = (type = csv);

---> query the Stage to find the Menu CSV file
LIST @tasty_bytes_sample_data.public.blob_stage/raw_pos/menu/;


-------------------------------------------------------------------------------------------
    -- Step 4: Now let's Load the Menu CSV file from the Stage
        -- COPY INTO <table>: https://docs.snowflake.com/en/sql-reference/sql/copy-into-table
-------------------------------------------------------------------------------------------

---> copy the Menu file into the Menu table
COPY INTO tasty_bytes_sample_data.raw_pos.menu
FROM @tasty_bytes_sample_data.public.blob_stage/raw_pos/menu/;

Data

データ系のリソースは階層になってて、 Database > Schema > Table という感じ。たとえば、下の Fivetran → Shopify 用の Database だと、

FIVETRAN_DATA という Database の中に
SHOPIFY という Schema があって、
その中に ORDER CUSTOMER PRODUCT という各種テーブルがある、という構造になる。

自分は BigQuery の2層構造に慣れていたので Snowflake の3層構造に若干戸惑ったけど、 SQL を書くときは

select count(*) from "FIVETRAN_DATA"."SHOPIFY"."ORDER"

と、 BQ の <project>.<dataset>.<table or view> というのとあんまり変わらないのでそこまで違和感はないかも?

Warehouse

ここが一番わかりにくかったんだけど、 Warehouse とはざっくりいうと "compute server" である、という Reddit の投稿がわかりやすかった。

In summary, a warehouse is just a computer server. In Snowflake you need one because a warehouse does the work of running a query.

Databases and schemas are just an organizational structure for tables. There are other things that can live inside schemas, like views and UDFs. You need to know about this structure because Snowflake wants you to be specific about which tables to access. A well used Snowflake can have thousands of tables, so this structure does help.

https://www.reddit.com/r/snowflake/comments/13xjxly/can_someone_please_explain_accessing_warehouses/?rdt=52733

Warehouse の画面は↓の2枚のような感じで、たしかにメニューも Data のところじゃなくて Admin のところにあるし、 "Size" という項目もあるので、「サーバっぽい」っていうのは納得できた。名前的に最初 BQ の dataset なのか...? って思ってたけど全然違った、ワラ。

Users/Roles

ここは割と名前の通りで、ブラウザでログインしている自分用の User や、 Fivetran とか dbt に接続する User がいる。 Role も名前の通り User に対して付与するもの。

ちなみに、 Roles の画面だとこういうグラフも見れて感動した↓

Users, Roles & Privileges with Worksheets

privilege って単語、毎回綴りを毎回間違えるんだけど

Snowflake の Worksheet 画面では右上で実行する Role と Warehouse を設定できて、これを Context とよぶ。実行する「権限」と実行する「環境」を選んでるっていうのが自分の理解。

Role は右上から選ぶ以外にも、 Worksheet の中で

use role hoge_role;
create database hoge_db;

のように途中で role を切り替えることもできるし、 use warehouse hoge_warehouse も構文としてあるので、 warehouse も切り替えられそう (実際 Getting started だと use role も use warehouse もあった)。

User には Role を付与し、 Role には Privilege を付与することで誰が何をできるっていうのを制御できて、たとえば

grant select on database hoge_db to role hoge_role;

のような SQL 文で管理できる。次の ETL の設定ではこの権限周りが結構だるかった。

steps

1. Snowflake destination として作成

基本 Fivetran のガイド通りにつくるんだけど、ちょくちょくトラブって、 Fivetran の提供する Worksheet 用のコードをいじる必要があった。

ガイド: https://fivetran.com/docs/destinations/snowflake/setup-guide

今回はとりあえずつなぐことが目的だったので、プライベート接続的なセキュアにするオプションは全部すっとばしたところ、ガイドにある Worksheet コードを動かすところがここの作業の9割だった。

トラブったところは GRANT 系の SQL で、適宜 use role の位置をずらしたり、実行している Role へ GRANT を発行したりしたのだが、正しいのかちょっと怪しいので作業内容は割愛 (次回整理するという目的もこめつつ...)。

2. Shopify connector を作成

これもガイド通りに Shopify の自分のストアに Fivetran app をインストールすれば OK。ポイントとして、接続する前に Shopify にブラウザでログインしておくとスムーズかも。画面遷移的には

Shopify connector を新しくつくる画面
→ install Fivetran app のリンクをクリック
→ Shopify に遷移
→ また Shopify connector を新しくつくる画面

という流れなので、若干 janky ではあった。

3. dbt Cloud プロジェクトの作成

多分 dbt core でもできるような気がするが、そっちは前にやったことがあるのでマネージド版を使ってみたくて dbt Cloud の方にした。

基本は↓の Quickstart に沿いつつ、構成が若干違うので適宜アレンジする。
https://docs.getdbt.com/guides/snowflake

Quickstart の 1-3 はSnowflake にデータをいれる話なので、 4 の "Connect dbt Cloud to Snowflake" からやる。接続には manual と Partner Connect っていう自動で接続設定を作ってくれる機能を使う2つの方法があって、今回は Partner Connect を使ってみた。

Partner Connect は Snowflake ↔ dbt の partner で、 Snowflake 側の market place で dbt を選択すると、自動で dbt Cloud に Snowflake の接続設定を持ったアカウントを作ってくれる。

この「アカウント」っていうのがポイントで、実はもともとメールアドレス1で Snowflake 、メールアドレス2で dbt Cloud のアカウントをそれぞれ持っていて、てっきり Snowflake で Partner connect をクリックしたら dbt Cloud の認証画面に行くのかと思ったんだけど、いきなり Snowflake のメールアドレス1で dbt Cloud のアカウントごと作成されて若干戸惑った。まあ、別にどちらのメアドもプライベート用なのでいいんだけど一瞬混乱した。

あとは、 QuickStart だと Partner Connect をつくるときに dbt が Snowflake の Database から読めるように権限をつけているんだけど、自分はそれをしていなかったので↓の GRANT を発行した。

grant usage on database fivetran_data to pc_dbt_role;
grant usage on schema fivetran_data.shopify to pc_dbt_role;
grant select on all tables in schema fivetran_data.shopify to pc_dbt_role;
grant select on future tables in schema fivetran_data.shopify to pc_dbt_role;

ポイントは

ALL TABLES = 今存在するテーブル、 FUTURE TABLES = これから作られるテーブルという理解なので両方発行が必要っぽい
database/schema/table のすべての階層に権限が必要で、たとえば schema に権限がないと SELECT は発行できない

以上が終わったら、 select count(*) from "FIVETRAN_DATA"."SHOPIFY"."ORDER" が Snowflake → dbt Cloud IDE で実行できるか確認する。

感想

Snowflake さわれてよかった！次は Fivetran の Shopify 用 dbt package を入れたり semantic layer やるけどこっちも大変そう。ひえー

2024-04-25

めも: iTerm2 x Starship で git branch の文字とかが変になるとき

まじでこれだけ

わたしは powerline 系のフォントを入れたのに ligature を ON にしていなかったので、マリオのハテナブロックが出てた

2024-04-18

AWS のサポートケースをテキストファイルに出力するコード

code AWS

前提
code

AWS のサポートケースの検索がなんだかめんどくさいので、ケースごとに返信を1つのテキストファイルにまとめるスクリプトを書いた。

前提

boto3 が pip install されている
AWS_PROFILE 環境変数がセットされている

code

import os
import shutil

import boto3

client = boto3.client("support")

OUTPUT_DIR = "output"


def reset_output():
    if os.path.isdir(OUTPUT_DIR):
        shutil.rmtree(OUTPUT_DIR)
    os.mkdir(OUTPUT_DIR)


def write_comms(case_id, filepath):
    comms = []
    next_token = None
    while True:
        params = {
            "caseId": case_id,
        }
        if next_token:
            params["nextToken"] = next_token
        comm_res = client.describe_communications(**params)
        print(comm_res.get("ResponseMetadata", {}).get("HTTPStatusCode"))
        for comm in comm_res.get("communications"):
            author = comm.get("submittedBy")
            ts = comm.get("timeCreated")
            body = comm.get("body")
            if "お客様の問題解決にお役に立てたでしょうか" in body:
                continue
            comms.append(
                "\n".join(
                    [
                        f"author: {author}",
                        f"timestamp: {ts}",
                        "",
                        body,
                    ]
                )
            )
        # write to file
        with open(filepath, "a") as f:
            f.write("\n\n-------\n\n".join(comms))
        comms = []
        with open("output.json", "w") as f:
            f.write(str(comm_res))
        next_token = comm_res.get("nextToken")
        if next_token is None:
            break


def main():
    reset_output()
    next_token = None
    csv_lines = [["caseId", "displayId", "subject", "serviceCode", "timestamp"]]

    while True:
        params = {
            "includeResolvedCases": True,
            "language": "ja",
            "includeCommunications": False,
        }
        if next_token:
            params["nextToken"] = next_token
        case_res = client.describe_cases(**params)
        for case in case_res.get("cases"):
            caseId = case.get("caseId")
            print(f"processing case {caseId}")
            displayId = case.get("displayId")
            filepath = f"output/{displayId}.txt"

            subject = case.get("subject")
            serviceCode = case.get("serviceCode")
            timestamp = case.get("timeCreated")
            csv_lines.append([caseId, displayId, subject, serviceCode, timestamp])
            with open(filepath, "w") as f:
                f.write(
                    "\n".join(
                        [
                            f"subject: {subject}",
                            f"serviceCode: {serviceCode}",
                            f"timestamp: {timestamp}",
                            "-------\n\n",
                        ]
                    )
                )
            write_comms(caseId, filepath)
        next_token = case_res.get("nextToken")
        if next_token is None:
            break

    with open(f"{OUTPUT_DIR}/files.csv", "w") as f:
        f.write("\n".join([",".join(line) for line in csv_lines]))


if __name__ == "__main__":
    main()
    print("done!")

2024-04-18

QuickSight まとめ

QuickSight

about
資料
QuickSight の基礎?知識
QuickSight の詳細

about

埋め込み QuickSight で BI アプリケーションをつくることになったものの、いろいろわからんことが多かったので調べたり聞いたりしためも。マルチテナントで作りたかったので、ユースケースとしては

埋め込み
マルチテナント

あたりに記事の内容はフォーカスするかも。

資料

QuickSight は EC2 とかと違って、巷にあんまり Qiita とか Zenn の記事が転がっていない。一方ドキュメントも例のごとくわかりやすくないので、資料を見つけるところから大変だった。

結論、 Black belt とか AWS が配布してるスライド資料が役に立つし SA さんからもこれらを案内された。

Amazon QuickSight におけるシングルサインオンの設計と実装

一番見た資料
ユーザ管理についての説明
アプリケーションの構成の例も載ってる (p.81)

Amazon QuickSight 埋め込みハンズオン

二番目に見た資料
この通りにつくればマルチテナントの埋め込みアプリがつくれる

このほかの資料

QuickSight の基礎?知識

QuickSight とは

そもそも QuickSight は AWS が提供する BI マネージドサービスで、 BigQuery や S3, Athena, Salesforce などいろいろなデータソースからデータを引っ張って可視化できる。

QuickSight の概念

data source
data set
visual/analysis/dashboard
user
group
namespace

あたりがコアの概念。

Amazon Quicksight 埋め込みハンズオンのここにまとまっていて、図にするとこう↓

https://awsj-assets-qs.s3.ap-northeast-1.amazonaws.com/workshop/public/jp/03-workshop/01-multi-tenant-environment-setup/06-create-data-set.html

まず、マルチテナントにおいて重要なのがテナントごとの独立したリソース管理で、それが QuickSight の namespace で実現できる。というのをテナントごとにつくれば、テナントごとに独立したリソース管理ができる。

このほか、visual が一個一個のチャートや表などで、 analysis・dashboard は複数の visual を持つ。analysis は visual を「作る」場所で、 dashboard は上司とか顧客に visual を「見せる」場所.. というのが自分の理解。

これが visual をつくる analysis

https://community.amazonquicksight.com/t/coming-november-2023-a-new-analysis-experience-on-amazon-quicksight/18974

こっちは dashboard

https://community.amazonquicksight.com/t/coming-june-2022-an-updated-amazon-quicksight-dashboard-experience/3695

User と Group に関しては名前のままだけど、マルチテナントの場合は namespace ごとに user/group を作成するところがポイント。なので、「QuickSight にユーザを追加します」というより「QuickSight のこの名前空間にユーザを追加します」という操作になる。

default namespace と custom namespace (カスタム名前空間)

QuickSight では必ずしも namespace を設定しないといけないわけではなく、たとえば社内用の QuickSight だと名前空間はつくらないで運用している。ここであえてちょっと分かりにくい文になっているのは、もともと QuickSight には default namespace (デフォルト名前空間) が作られていて、マルチテナント運用にしない場合はすべてこの default namespace の中にユーザを追加したりデータセットを作成したりすることになる。

で、マルチテナント運用にする場合は default namespace 以外の名前空間、すなわち「カスタム名前空間」をつくってそれぞれに各テナントを割り振る。ので、namespace がある・ないというよりは、 custom namespace を使う・使わないという切り分けになっていて、 AWS のドキュメントも「カスタム名前空間の場合は〜〜」みたいな書き方になる。

埋め込み QuickSight

... の話をする前に、QuickSight の「ふつうの使い方」について話すと、QuickSight は

AWS management console とはユーザーの管理がまったく別
独自のログインページがある (management console から飛ぶこと「も」できる)

というちょっと特殊なサービスになっていて、つまり AWS を普段触らない営業さんも management console を経由せず QuickSight を使ってもらうことができる (理論上は)。

https://aws.amazon.com/jp/blogs/big-data/an-updated-amazon-quicksight-sign-in-experience/

1 に関しては、QuickSight は QuickSight でこういう↓ユーザリストがあって、IAM User の有無にかかわらず QuickSight を使う人の email はここに登録する。

https://docs.aws.amazon.com/ja_jp/quicksight/latest/user/managing-users.html

2 については、 IAM User がある場合はこういうふうに management console から飛ぶこともできるが、そうでない場合は QuickSight 独自のログインページを案内できる。

埋め込み QuickSight では、この QuickSight の URL に案内せず、自分のアプリケーションの中に QuickSight のダッシュボードやコンソール*を iframe で埋め込む。埋め込み用の SDK があるので、これを使うとそこそこ簡単に埋め込みができる。

*ダッシュボードが閲覧のみなのに対し、コンソールというのは dataset を追加したり、 visual/analysis/dataset を作れる QuickSight のフル機能を備えた UI で、つまり埋め込みでない QuickSight にアクセスしたときと同じもの。

QuickSight は AWS アカウントに1つ

セクションタイトルの通りだけど、 AWS アカウントに1つしか QuickSight のインスタンス? を立てられない。ほとんどないけど、たとえば「ユーザにどのように QuickSight への認証させるか」という設定は QuickSight 作成時にしか設定できないので注意かも。

https://community.amazonquicksight.com/t/integrating-aws-managed-microsoft-ad-with-quicksight/1230

QuickSight の詳細

RLS

QuickSight のデータセットには RLS (row-level security) を設定できて、どういう user や group がどういう条件の行にアクセスできるかを指定できる。ドキュメントはここ。

具体的には、こういう↓形式の CSV やクエリを書いて RLS を各 dataset に対して設定する。

この表は「Group ごとにアクセスできる Region と Segment を制御しますよ」という RLS で、1行目は「EMEA-Sales」グループは Region = EMEA, Segment = Enterprise,SMB,Startup のいずれかを満たす行を閲覧できるというルールを表している。

で、この query / CSV から RLS を表す dataset を作成して、それをデータの入った dataset に設定する。たとえば、 CLI の aws quicksight create-data-set だと、 --row-level-permission-data-set オプションに↓のように値を渡す。

{
  "Arn": "arn:aws ...",
  "PermissionPolicy": "GRANT_ACCESS",
  "FormatVersion": "VERSION_2",
  "Status": "ENABLED"
}

create-data-set — AWS CLI 2.15.39 Command Reference

注意点としては string に対してしか RLS を設定できないので、数値の ID 列とかだと hash したりする必要がある。

custom namespaces / カスタム名前空間だと、 UI でリソースが管理できない

default namespace (= 新しい名前空間を作らなかった場合) は Manage QuickSight の画面から user, group, dataset を確認したり追加したりできる。

一方、custom namespace の場合、この画面には現れないので、CLI を叩いたり別途管理画面を作ったりする必要がある。BI アプリケーションを作るときにここに気づかないと、あとから慌てて管理画面をつくる工数を捻出したり、最初はエンジニアに依頼して CLI を叩いてもらう... というようなことが必要になる

custom namespace の limitations のところに "Password-based logins" はできないよ、と書いてあって、これがなんのことかよくわからなかった。

https://docs.aws.amazon.com/quicksight/latest/user/namespaces.html

結論、↓の画面を経由する = "password-based login" ということらしい (SA さんに確認)。

custom namespaces と user の IdentityType

同じく custom namespaces の limitations のところには

Custom namespaces—those that are not the default namespace—are only accessible to IAM Federated Single-Sign On users.

と書いてあるのだが、別の workshop には

Federated users, IAM users and QuickSight managed users can all be created in secondary namespaces. However, only Federated and IAM users in secondary namespace will be able to access QuickSight console directly. You can user QuickSight managed users with secondary namespaces if your use case requires only embedded access.

https://catalog.workshops.aws/quicksight/en-US/admin-workshop/5-multitenancy

ということで、埋め込み QuickSight なら QuickSight-managed users でもいけそう?

このやり方は試してないけど、今回は IdentityType = IAM にした。もし IdentityType = QUICKSIGHT にした場合、

QuickSight-managed users にする
= ユーザ自身に QuickSight ユーザを作成する権限を与えられない (IAM ロールと紐づかないから)
= 自己プロビジョニングできない
管理者が QuickSight にアカウントをつくる必要がある (ユーザ自身で sign up できない)

という予感がするので注意かも

Athena データソースの注意

Athena データソース・マルチテナント・AUTHOR 権限の組み合わせでの注意点で、S3 にあるファイルから Athena でテーブルや view をつくる場合、ユーザに dataset にする Athena view に権限をつける必要がまずある。しかし、これだけだと元の S3 ファイルに権限がないのでエラーになるので、結局大元の S3 ファイルや途中の view にも権限をつけることになる。

ここで、ユーザに AUTHOR 権限 = dataset を作成する権限を与えた場合、RLS を仮に dataset に付与していたとしても元の Athena view や S3 あら新たな dataset を作成できてしまうのでまずい。回避方法としては、 S3 → Athena で dataset となる view を作ったあと、もう一度 Athena → S3 にエクスポートして、 S3 data source に RLS かけてみたけど、結局 RedShift とか使った方がよかったのかなと思ったり...

Cognito を使う場合の Cognito User と QuickSight User の紐づけ

Amazon QuickSight におけるシングルサインオンの設計と実装の資料だと、↓のような埋め込みアプリケーションの設計が紹介されている。

言葉にすると、

ユーザは Cognito でアプリケーションにログインする
アプリケーションはバックエンドで QuickSight の generate-embed-url-for-registered-user を叩いて、クライアントにその URL を返す
クライアントは URL を使って埋め込みを描画する

という流れになる。このとき、 step 2 では Cognito のユーザと QuickSight ユーザを紐づけてどの "registered user" に対する URL かを判定しないといけない。

ここがいまいちわかりにくいのだけど、結論としてはユーザの email などを key にして Cognito user の email → QuickSight user の username をつくることになる。

たとえば、foo@example.com というユーザに利用してもらいたい場合、

Cognito に email: foo@example.com となるユーザを登録する
同時に QuickSight にも email: foo@example.com でユーザを登録する

と2つのユーザを登録する必要がある。 generate-embed-... ではパラメータに UserARN が必要なので、バックエンド側のロジックとしては

Cognito の id token を受け取る
id token から email を取得
QuickSight の DescribeUser API で UserName から UserARN を取得する
UserARN を GenerateEmbedUrlForRegisteredUser に渡して URL を取得

のようになる。

なお、 QuickSight User の identity type が IAM の場合、

RegisterUser の引数に IAM Role を渡す必要があり、
UserName は / (e.g. quicksight-user/foo@example.com)

となる。(role session name がいまいち何なのかよくわかってないけど Cognito だと email になった)

... というのは Amazon QuickSight におけるシングルサインオンの設計と実装の p.26 あたりにある

2024-04-17

やってみた: VPC 内に Cloud SQL を立てる

pre-amble

「VPC の中に Cloud SQL を立ててみよう」というお題でやってみた。ガイドはあるんだけど、一箇所にまとまってなかったので書いておく。

Quickstart: Connect to a Cloud SQL for MySQL instance with private IP | Google Cloud

これが VPC の中に Cloud SQL を立てて接続する方法を書いたガイドなんだけど、 default でない VPC Network を作成する場合の構成が書いてなかった。

setup

1つの project 内に↓を作成

VPC network + subnet
Cloud SQL
GCE

しらべもの

private IP vs VPC

Cloud SQL における「VPC の中にある」と「private IP」の関係がいまいちわかってなかった。で、調べたけっか

Cloud SQL には public IP と private IP の両方を付与することができる
private IP を付与する場合、VPC network 内の network address range を Cloud SQL に割り当てる
VPC の中からのみ接続を許可する場合、 public IP は off にする

ので、「VPC の中に Cloud SQL を立てる」とは、 Cloud SQL を private IP 接続のみにして private IP に VPC を割り当てる、と理解した

private IP Cloud SQL への接続

結論、 private IP 「しか」ない場合、 Cloud SQL に手元の PC から接続するためには踏み台 SSH サーバーとなる GCE (VM) がいる。つまり、 Cloud Shell → GCE に SSH して、GCE から同じ VPC 内にいる Cloud SQL に接続する。

SSH ができるようにするために、 VPC に SSH を接続するための firewall rule を設定する必要がある。

というのが、下記の2つのガイドを突合するとわかる

VPC Network の作成

VPC Peering のガイドを参考に VPC Network をつくっていく。VPC Peering 用なので、 VPC Network x 2 と Peering について書いてあるけど VPC Network の片方だけつくれば OK。このガイドを見ると、

network の作り方
subnet の作り方
firewall の作り方

がわかる

具体的にはここを見れば OK。

Firewall はこのコマンドで、なぜこのルールかというと

gcloud compute firewall-rules create network-a-fw --network network-a --allow tcp:22,icmp

Run the following to enable SSH and icmp, because you'll need a secure shell to communicate with VMs during connectivity testing:

とのこと

Cloud SQL の作成

これは QuickStart ガイドのここを見る↓

Quickstart: Connect to a Cloud SQL for MySQL instance with private IP | Google Cloud

で、このとき "From the Network dropdown, select default." と書いてあるところを、 default ではなく前項でつくった VPC network を選ぶ。Private service connection はガイド通り設定する。

GCE instance / VM の作成

これも基本↓のガイドを見るんだけど、

Quickstart: Connect to a Cloud SQL for MySQL instance with private IP | Google Cloud

この通りにやると default ネットワークにいるインスタンスができちゃうので、今回つくった VPC と subnet をインスタンスに設定する。

SSH する

ここまでできたらあとは本当にガイドのままで OK。↓のセクション以降に沿ってやる。

Quickstart: Connect to a Cloud SQL for MySQL instance with private IP | Google Cloud

以下ちょっと迷ったポイント

ssh ターミナルの起動

GCE のインスタンス一覧を見ると、各行のはしっこに SSH のボタンがある。これをクリックすると、新しいブラウザのタブで terminal が立ち上がるので、これを 2回クリックすれば mysql 用と cloud sql auth proxy 用が用意できる。

MySQL にログインできない

... というのはここに書いた

mariadb-client がインストールできない

QuickStart のガイドだと mysql コマンドを↓のようにインストールしてたんだけど、

sudo apt-get update
sudo apt-get install mariadb-client-10.6

OS の関係か mariadb-client-10.6 がインストールできなかった。出力は、

$ sudo apt-get install mariadb-client-10.6
Reading package lists... Done
Building dependency tree       
Reading state information... Done
E: Unable to locate package mariadb-client-10.6
E: Couldn't find any package by glob 'mariadb-client-10.6'
E: Couldn't find any package by regex 'mariadb-client-10.6'

ので、

apt-cache search mariadb-client

で mariadb-client がたしかに 10.3 しかないことを確認したと

sudo apt-get install mariadb-client

で雑に version 指定なしでインストールした。今回はダミーデータの流し込みだけしたかったので OK。

感想

VPC とかあまり触ったことなかったのでバリ大変だった。

pre-amble

steep にした理由

1. Shopify models の生成

疑問

2. semantic layer の設定

疑問というか

3. semantic layer の接続設定

やらかし 1: クエリが "No active warehouse selected" になる

やらかし? 2: user の設定を変えても反映されない

なんやかんやで BI は表示できた

おまけ: Snowflake で readonly/select 権限をつける

pre-amble

Snowflake 入門

アカウント作成

各種リソース

Worksheets

Data

Warehouse

Users/Roles

Users, Roles & Privileges with Worksheets

steps

1. Snowflake destination として作成

2. Shopify connector を作成

3. dbt Cloud プロジェクトの作成

感想

前提

code

about

資料

QuickSight の基礎?知識

QuickSight とは

QuickSight の概念

default namespace と custom namespace (カスタム名前空間)

埋め込み QuickSight

QuickSight は AWS アカウントに1つ

QuickSight の詳細

RLS

custom namespaces / カスタム名前空間 だと、 UI でリソースが管理できない

QuickSight user の password-based login について

custom namespaces と user の IdentityType

Athena データソースの注意

Cognito を使う場合の Cognito User と QuickSight User の紐づけ

pre-amble

setup

しらべもの

private IP vs VPC

private IP Cloud SQL への接続

VPC Network の作成

Cloud SQL の作成

GCE instance / VM の作成

SSH する

ssh ターミナルの起動

MySQL にログインできない

mariadb-client がインストールできない

感想

custom namespaces / カスタム名前空間だと、 UI でリソースが管理できない