Snowflake & dbt Cloudハンズオン実践 #10: 『既存のSnowflakeアカウント環境＋GitHubリポジトリを利用した実践』 #snowflakeDB #dbt

特集

しんや

2024.02.08

アライアンス事業部 エンジニアグループ　モダンデータスタック(MDS)チームの しんや です。

先日、全9本のシリーズ投稿という形でSnowflake社が提供しているハンズオン『Accelerating Data Teams with Snowflake and dbt Cloud Hands On Lab』の実践内容を投稿、公開しました。

こちらのシリーズ、Snowflake側のアカウントから『Partner Connect』という機能(？)経由でdbt Cloudを起動・連携しており、この場合、作成されるdbtアカウントもトライアル版となります。

このハンズオンに関してはSnowflake、dbt共にトライアル環境での実働を想定しているため、現状私の状況(Snowflakeアカウントもdbtアカウントも実利用出来るものが揃っている)だとちょっとノイズになってしまいます。

ということで、当エントリでは従来の『#1』で実践、紹介していた

『Snowflakeトライアルアカウントから、SnowflakeのPartner Connect経由でdbtプロジェクトを作成(Gitリポジトリはdbtマネージドのものを利用)し、実践内容を進める』

の部分を、

『任意のSnowflakeアカウント上にデータを用意し、dbtアカウントも既存利用可能な環境でプロジェクトを作成、Gitリポジトリも別途用意する形で実践内容を進める』

という内容で置き換える形の内容をご紹介していきたいと思います。(#1の内容は全面的に置き換え、#2以降の内容については適宜読み替えに関するポイントに言及する形で進めます)

※Snowflake社が提供しているハンズオン『Accelerating Data Teams with Snowflake and dbt Cloud Hands On Lab』の内容を『既存Snowflakeアカウント環境を使って実践してみたい！』という方は、下記一連のエントリを一度読んで頂いた上で当エントリの内容を実践頂くか、当エントリの内容から適宜下記エントリに展開する形で読み進めていき、実践頂けますと幸いです。

・#1: Snowflake環境準備編 [Snowflake QuickStarts: Step01-04]
・#2: dbt Cloud IDE探索編 [Snowflake QuickStarts: Step05]
・#3: dbt Cloud 基本構造紹介編 [Snowflake QuickStarts: Step06]
・#4: dbt Cloud 実践編1(ソース設定＆ステージングモデル作成) [Snowflake QuickStarts: Step07]
・#5: dbt Cloud 実践編2(シード＆マテリアライゼーション) [Snowflake QuickStarts: Step08]
・#6: dbt Cloud 実践編3(マートモデルの作成) [Snowflake QuickStarts: Step09]
・#7: dbt Cloud 実践編4(テスト＆ドキュメント) [Snowflake QuickStarts: Step10]
・#8: dbt Cloud 実践編5(デプロイ) [Snowflake QuickStarts: Step11]
・#9: Snowsightダッシュボード可視化編 [Snowflake QuickStarts: Step12]

【2024/03/19(火)追記】
・dbt Cloudのアカウントを作成すると、作成時点で『Analytics』というプロジェクトが作成されるようです。(中身としてはdbt initコマンドを実行した時に作られたときのものになっている模様)
・この状態で当ハンズオン内容を実践する場合、以下いずれかの手順を踏んで
　　進めて頂ければと思います。
　・[シナリオ1] Analyticsプロジェクトを削除、その後当エントリ内容の手順を進めていく
　・[シナリオ2] Analyticsプロジェクトに対して必要な設定を追記していき、
　　　Analyticsプロジェクトを活用する形で進めていく
　　・Snowflake環境を別途用意、dbtプロジェクト設定メニューからその設定を追加
　　・Gitリポジトリ環境を別途用意、dbtプロジェクト設定メニューからその設定を追加

目次

　

Step01(既存アカウント利用Ver). 概要

QuickStartsコンテンツ全体の概要に関する情報として、既存アカウント環境を活用して進める場合の情報としては以下の通り。

　

実践を進めるのに必要なもの

  • ACCOUNTADMIN アクセス権を持つ任意のSnowflake アカウント
  • dbt Cloudアカウント
  • ソースコード管理を行うためのGitリポジトリ

いずれも非トライアル、実稼働環境として使えるアカウントがある前提で進める形となります。その他の記述に関しては既存公開済みエントリの内容と同じです。

　

Step02(既存アカウント利用Ver). アーキテクチャとユースケースの概要

この部分の記述に関しては既存公開済みエントリの内容と同じです。

　

Step03(既存アカウント利用Ver). 利用するSnowflakeデータの確認

この部分が当エントリの肝であり、全面的に置き換える形となります。

予めSnowflakeアカウント側で用意されているSNOWFLAKE_SAMPLE_DATAデータベースの内容(主にTPCH_SF1スキーマ配下のテーブル)を、Snowflakeアカウント配下の任意のデータベースを用意する形で用意します。


(※この構成を模した形のものを複製するイメージです)

既存エントリで活用したテーブルは下記内容によりordersテーブルとlineitemの2テーブルのみでした。(追加でNationテーブルをseedを介して追加)なのでここではこの2テーブルのデータを『生データが格納されているテーブル』と想定して別途準備します。

ここではまず、TPCH_SF1スキーマの ordersテーブルと lineitemテーブルを対象として変換処理を行いたいと思います。この2テーブルに対するソース定義を作成します。

Snowflake環境に関して必要な要素に関して情報を整理したものが以下表となります。既存エントリ、Partner Connect経由で作成したものを『実稼働環境を想定して用意するならこういう形』という感じでしょうか。このあたりはある程度組織やプロジェクトのルール、命名規約等も絡んで来ると思いますのでそのあたりの条件やルールなどを踏まえたうえで予め『この環境ではこういう内容、名前でセットアップしよう』というのは検討整理しておいた方が後の作業もスムーズになるのでおすすめです。

項目 従来の指定内容
(Partner Connect経由)		 既存アカウント利用Verでの指定内容
(個別に環境作成)
Snowflake:データベース PC_DBT_DB shinyaa31_pc_dbt_db
Snowflake:ウェアハウス PC_DBT_WH
後の手順でxxxも作成		 shinyaa31_pc_dbt_wh_staging
shinyaa31_pc_dbt_wh_mart
Snowflake:システムユーザー PC_DBT_USER ACCOUNTADMINロールを利用可能な
任意のユーザー名
Snowflake:システムパスワード xxxx ACCOUNTADMINロールを利用可能な
任意のパスワード
Snowflake:システムロール PC_DBT_ROLE ACCOUNTADMIN
既存データベースへのアクセス許可 SNOWFLAKE_SAMPLE_DATA

また、これらSnowflake環境を作成するタイミングで開発環境、本番環境に相当するデータベース環境の検討、確認もしておきます。今回は任意のデータベース『shinyaa31_pc_dbt_db』配下に、環境毎にスキーマを分ける形で用意する形としました。生データ格納用スキーマとしてraw_tpch_sf1も合わせて作成します。

環境 スキーマ名
生データ格納用スキーマ:このタイミングで作成 raw_tpch_sf1
開発用に作成する個人スキーマ:プロジェクト作成時に指定 dbt_shinyaa31_dev
本番環境用スキーマ:デプロイ時に指定 production

上記内容を個別にSnowflakeコンソール経由で作成します。なお、このときに用いるロールはACCOUNTADMINとしています。ハンズオン作業をシンプルにさせるための措置ですが、実際の運用環境ではここは適切なロールを設定の上進める形としてください。

ウェアハウスの作成に関してはハンズオン用に最小サイズのX-SMALLを指定しています(ハンズオンに関してはこのサイズで十分との判断から)。ウェアハウスの稼働についても余計な時間分稼働させて費用が発生するのも避けたいので、AUTO_RESUME(SQL ステートメント（クエリなど）の送信時にウェアハウスを自動的に再開するかどうか)を有効に、AUTO_SUSPEND(ウェアハウスが自動的に中断されるまでの非アクティブの秒数) = 60(秒)としています。

/**------------------*/
/** ウェアハウス作成(1). */
/**------------------*/
/** 標準タイプ、サイズは最小のXS、自動再開ON、自動一時停止=60秒のウェアハウス(ステージング層テーブル用)を作成. */
CREATE OR REPLACE WAREHOUSE shinyaa31_pc_dbt_wh_staging
WITH
    WAREHOUSE_TYPE = STANDARD
    WAREHOUSE_SIZE='X-SMALL'
    AUTO_RESUME = TRUE
    AUTO_SUSPEND = 60
    COMMENT = 'using staging layer / for dbt and Snowflake handson';

/**------------------*/
/** ウェアハウス作成(2). */
/**------------------*/
/** 標準タイプ、サイズは最小のXS、自動再開ON、自動一時停止=60秒のウェアハウス(マート層テーブル用)を作成. */
/** ウェアハウスは別途分けて作成しているがスペックはいずれも同じです。実務の場合はサイズその他スペックを変えるイメージ. */
CREATE OR REPLACE WAREHOUSE shinyaa31_pc_dbt_wh_mart
WITH
    WAREHOUSE_TYPE = STANDARD
    WAREHOUSE_SIZE='X-SMALL'
    AUTO_RESUME = TRUE
    AUTO_SUSPEND = 60
    COMMENT = 'using staging layer / for dbt and Snowflake handson';

/**---------------*/
/** データベース作成. */
/**---------------*/
/** 今回は対象のデータベースは1つ(shinyaa31_pc_dbt_db)。この配下に『生データ』『開発用』『本番環境用』スキーマを同居させる形を取る. */
CREATE DATABASE shinyaa31_pc_dbt_db;

/**------------*/
/** スキーマ作成. */
/**------------*/
/** 生データ用のスキーマだけ始めに作成しておく。残りの『開発用』『本番環境用』はdbtプロジェクト作成・実演の過程で作成. */
CREATE SCHEMA raw_tpch_sf1;

データベースやスキーマと合わせて、生データを格納するテーブルをデータ毎作成(複製)しました。

/**----------------------*/
/** 生データ格納テーブル作成. */
/**----------------------*/
/** ソース用生データの準備. */
SELECT COUNT(*) FROM snowflake_sample_data.tpch_sf1.orders;
SELECT COUNT(*) FROM snowflake_sample_data.tpch_sf1.lineitem;

/** 任意のスキーマ配下に生データが投入された、という前提で、ハンズオンで利用していたテーブルを上記で作成したスキーマ配下に実データ入りテーブルとして用意. */
/** ordersテーブル */
CREATE OR REPLACE TABLE shinyaa31_pc_dbt_db.raw_tpch_sf1.orders
AS
  SELECT * FROM snowflake_sample_data.tpch_sf1.orders;

/** lineitemテーブル */
CREATE OR REPLACE TABLE shinyaa31_pc_dbt_db.raw_tpch_sf1.lineitem
AS
  SELECT * FROM snowflake_sample_data.tpch_sf1.lineitem;

/** nationテーブルはseed機能を通じてそれぞれ(開発環境・本番環境)の環境で用意されるのでここでは作らない. */

/** 投入データ確認. */
SELECT COUNT(*) FROM shinyaa31_pc_dbt_db.raw_tpch_sf1.orders;
SELECT COUNT(*) FROM shinyaa31_pc_dbt_db.raw_tpch_sf1.lineitem;

　

Step04(既存アカウント利用Ver). Partner Connect経由でdbt Cloudを起動・連携

このステップも前ステップ同様、ガラリと変わる形となります。大枠の流れは以下の通り。

  • dbtプロジェクト作成に必要なSnowflake環境を既存Snowflakeアカウントにて作成、準備(これは前ステップ:Step3で完了済)
  • dbtプロジェクト作成に必要なGitリポジトリ環境を作成、準備
  • dbt Cloud環境にてdbtプロジェクト作成を実施

　

dbtプロジェクト作成に必要なGitリポジトリ環境を作成、準備

既存エントリではdbt側で提供しているマネージドリポジトリを使ってハンズオンを進めていましたが、このエントリではサードパーティ提供のGitリポジトリを使って進めていきます。GitHubで今回用のリポジトリを用意していきます。

任意のGitリポジトリを用意し、ログインした状態で画面TOPの[New]を押下。

任意のリポジトリ名を入力して作成を行います。なお公開範囲についてはパブリックにする必要も無いのでプライベートとしました。

リポジトリ作成完了。

　

dbtプロジェクトの新規作成

ここまで用意した情報を以てdbtプロジェクトを新たに立ち上げます。dbt Cloudにログインし、画面右上の設定から[Account Settings]をクリック。

[Projects]→[New Project]を押下。

[Set up a new project]にて、ここからしばらく情報の入力作業が続きます。まずはプロジェクト名に任意の値を入力。ここではshinyaa31_pc_dbt_snowflakeとしました。[Advanced settings]の項目に関しては設定せずそのまま。[Continue]を押下。

接続先データウェアハウスの選択は[Snowflake]を選択して[Nex]を押下。

ここで次の手順にて接続先アカウントの情報を入力するのですが、その際に使う情報の元ネタをSnowflake管理コンソール経由で入手しておきます。Snowflake管理コンソール左下のSnowflakeアイコンマークから接続中のアカウント詳細で[アカウントURLをコピー]という機能が使えますのでここからURLを取得。

取得したURLの情報から「先頭のhttpsプロトコル」「末尾に付いている"snowflakecomputing.com"」を削った内容を次の[Account]に設定します。

  • before: https://oq65696.west-us-2.azure.snowflakecomputing.com
  • after: oq65696.west-us-2.azure

接続環境に必要な情報の設定。以下の情報を設定に追記します。

  • Connection Name: Snowflake
  • Account: 上述加工した内容
  • Database: (前述で作成した)任意のSnowflakeデータベース名
  • Warehouse: (前述で作成した)任意のSnowflakeウェアハウス名
  • Optional Settings/Role: (前述で作成、指定した)任意のロール名
    • ※今回はACCOUNTADMINを用いていますが本番環境、実運用環境下ではより適切な権限を持たせたロールを準備、活用頂くのを推奨、というのは前述の通りです。

開発環境に関する認証情報の設定。Auth Method/Username/Passwordは接続が可能なものをそれぞれ設定します。Schema(スキーマ)に関してはデフォルト(初期状態)ではSnowflakeアカウントを情報を元に任意のスキーマ名が設定されています。それをそのまま使うでも良いのですが、今回は前述検討部分で決めたdbt_shinyaa31_devという値で行ってみることにします。[Test Connection]を押下。

接続確認が取れたら[Next]を押下。

Gitリポジトリの設定。ここではGitHubを選択。連携しているアカウントがある場合、アカウント及び配下のgitリポジトリの一覧が表示されますが、Gitアカウント側の設定で「許可したものしか使わせない」設定になっていると上述で作成したリポジトリは表示されません。なので許可設定を行います。[profile]のリンクをクリック。

dbtのユーザーアカウント設定画面に遷移。[Linked Accounts]配下にGitHubメニューがありますので、

リンクを展開しGitHubの設定画面にアクセスします。

アカウントに対し、dbt Cloudの連携設定をインストールしますか？と聞かれるので対象のGitHubアカウントを選択。

GitHubアカウントへのログインを済ませると以下のような画面に遷移します。ここでリポジトリとして配下のリポジトリをすべてアクセス許可させるか否かを制御している訳です。私の環境の場合は"Only Select Repositories"(選択したリポジトリだけ許可)だったのでこの操作が必要になっている、という流れです。

前述作業で作成したリポジトリを指定し、

許可リポジトリ一覧に含まれたことを確認。

dbt Cloudのプロジェクト作成画面に戻ります。このままでは一覧にリポジトリは出てこない(画面リロードしたら駄目...たぶん)ので、[GitHub]以外の選択肢を一度選択して改めて[GitHub]を選択すると一覧に上記で指定追加したリポジトリが出てくるはずです。出てきたらそのリポジトリを選択。

程無くすると、プロジェクト作成の準備が出来ました！という画面に遷移します。

　

Step05(既存アカウント利用Ver). dbt Cloud IDEウォークスルー

dbt Cloud IDEにてプロジェクトの初期化、コミットを行ったあとは既存エントリ内容と同様です。

dbt runコマンド実行のタイミングで、用意したデータベース配下に開発用スキーマ:dbt_shinyaa31_dev、及び実行対象となったモデルのテーブルが生成されていることが確認出来ました。

モデルによって作成されたテーブルの中身はこうなっております。

(メモ：追加ウェアハウス作成は前述作業で完了済。ロールに対するウェアハウス利用権限付与(grant)についてはACCOUNTADMINであれば不要？だけど個別に作成しておいたロールなどの場合は追加で権限付与設定を行う必要があります)

　

Step06(既存アカウント利用Ver). dbtの基本構造

ブランチを切ったあとに編集を行ったdbt_project.ymlの変更後の内容については、ウェアハウスの指定部分が既存エントリと異なっています。

dbt_project.yml

name: 'snowflake_workshop'
version: '1.0.0'
config-version: 2

profile: 'default'

source-paths: ["models"]
analysis-paths: ["analysis"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

target-path: "target"  
clean-targets:         
    - "target"
    - "dbt_modules"

models:
  snowflake_workshop:
    staging:
      materialized: view
      snowflake_warehouse: shinyaa31_pc_dbt_wh_staging
    marts:
      materialized: table
      snowflake_warehouse: shinyaa31_pc_dbt_wh_mart

作業内容のGitリポジトリへの反映については、既存エントリと挙動が異なります。既存エントリではdbtのマネージドリポジトリでしたが今回はGitHubリポジトリを活用する形にしており、GitHubでは「プルリクエスト」の手順がここに追加されます。[Create a pull request on GitHub]を押下。

GitHubの画面に遷移し、プルリクエスト実施の手順に移行します。

各種確認作業が完了し、無事マージされました。(という流れでここは進めていきます)

マージ完了後はdbt Cloudの画面にてGitHubリポジトリの内容を同期する操作を行っています。

　

Step07(既存アカウント利用Ver). ソース(source)とステージング層モデル

ソース定義の際の設定ファイルの記述は以下の内容となります。 databaseとscheamの設定が、今回の環境用の値となる訳ですね。25行目のsource指定も、3行目のnameと配下のテーブル名で参照連携が取れているという形です。

models/staging/tpch/tpch_sources.yml

version: 2
sources:
  - name: tpch
    description: source tpch data
    database: shinyaa31_pc_dbt_db
    schema: raw_tpch_sf1
    tables:
      - name: orders
        description: main order tracking table
        
        columns:
          - name: o_orderkey
            description: SF*1,500,000 are sparsely populated
            tests: 
              - unique
              - not_null

      - name: lineitem
        description: main lineitem table
        columns:
          - name: l_orderkey
            description: Foreign Key to O_ORDERKEY
            tests:
              - relationships:
                  to: source('tpch', 'orders')
                  field: o_orderkey

ステージング層のモデル作成と実行(dbt run)を経て、Snowflake環境配下に以下の形でテーブルとビューが作成されていることを確認出来ました。

　

Step08(既存アカウント利用Ver). ソース(source)とステージング層モデル

シードを使ったマスタデータの投入とマテリアライゼーションの実践パート。一通りの手順を終えた後、Snowflakeアカウント開発環境スキーマ配下にテーブルが生成されていることを確認出来ました。

　

Step09(既存アカウント利用Ver). マートモデルの作成

マートモデルの作成パート。一通りの手順を終えたあと、Snowflakeアカウント開発環境スキーマ配下にテーブルが生成されていることを確認出来ました。

　

Step10(既存アカウント利用Ver). テストとドキュメント

ここまでの内容に対してのテストの実行とドキュメント作成を行うパート。一通りの手順を終えたあと、個別に用意したデータを参照する形でdbt Cloud上のドキュメントを参照することが出来ました。

　

Step11(既存アカウント利用Ver). dbt環境の本番環境デプロイ

ここまでの実践内容をdbt本番環境にデプロイするパート。Partner Connect経由では既に環境やジョブが作成されている流れですが、今回は本番環境を作るところから始まります。

画面上部[Deploy]から[Environment]を選択。

環境画面にて[Create Environment]を選択。

必要事項を入力。Deployment Connectionには本番環境で用いるSnowflake要素それぞれの値を設定します。ここではウェアハウスをマート用のもの(shinyaa31_pc_dbt_wh_mart)で指定しました。

本番環境認証情報(Deployment credentials)設定画面。ここではスキーマを本番環境用に設けるproductionと指定しました。ビルドを行うことでモデルによって生成されたデータがこのスキーマ配下のテーブルに入る形となります。設定が完了したら画面上部の[Save]を押下。

次いでジョブの作成に移ります。[Create Job]を押下。

デプロイジョブの作成。任意のジョブ名を入力、あと実行時にドキュメント生成(Generate docs on run)、ソースの鮮度に関する実行(Run source freshness)についても有効としました。

スケジュールとAdvanced Settingsに関しては設定なしです。

ジョブ実行の準備が出来ました。[Run Now]を押下。

諸々実行が為されますが、ここでエラー発生。

確認してみると、exampleのモデルに対するテストが実行された際、テストがコケるような設定になっていた(そしてそのテストを実行しないままハンズオンのこの過程まで来ていた)→ここで改めてテストを(すべてのモデルに対して)実行したところ、実行されていなかったテストがコケた、という流れでした。ここはテスト失敗を回避すべく、exampleのモデル作成時のSQLコードを一部改修。

開発環境で全testを含めたビルド実行が通ることを確認→変更内容をGitリポジトリにプッシュ→デプロイを再度実行。今度は上手く行きました！

デプロイ完了後、Snowflakeアカウント配下に本番環境用のスキーマ:production及び配下にテーブルやビューが作成されていることを確認出来ました。ひとまず一連の流れを既存Snowflakeアカウント環境、別途用意したGitリポジトリを活用する形で進めきることが出来ました。

　

Step12(既存アカウント利用Ver). dbt環境の本番環境デプロイ

当パートについては割愛します。

　

まとめ

ということで、SnowflakeのPartner Connectを活用するパターンとは別に、『既存Snowflakeアカウント環境／GitHubによるGitリポジトリ環境』でdbtプロジェクトを作成し、一連のハンズオンを実践した内容のご紹介でした。お手軽さは前者の方がありましたが、より実践に即した形で、より理解を深めながらという場合であれば後者の方がより流れや詳細が明確になっていたような気がします。いずれのケースに関してもdbtの良さ、dbt Cloudの便利さが体験できるとても良いハンズオンの内容だと思いますので興味のある人は是非試してみてください。

スキルアップ|初心者向け|書評|デベキャン|AWS認定|スキルアップ|デベキャンだより|勉強会|コミュニティ

関連記事

dbtで外部テーブルを参照するモデルを作成する

おざわ（じ）

2024.04.30

dbtの認定試験『dbt Analytics Engineering Certification Exam』が5月から新しくなるようです #dbt

しんや

2024.04.11

ストアドプロシージャからdbtへの移行手順 #dbt

しんや

2024.04.05

dbtでのSQLモデル記述時に利用推奨されている『共通テーブル式(CTE/Common Table Expression)』について

しんや

2024.03.31