この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
こんにちは。
データアナリティクス事業本部ソリューション部プリセールススペシャリストの兼本です。
当エントリは『クラスメソッド BigQuery Advent Calendar 2020』11日目のエントリです。 本アドベントカレンダーでは、12月01日から12月25日までの25日間、弊社DA（データアナリィクス）事業本部のメンバーがBigQueryに関連するブログを公開していきます。
- クラスメソッド BigQuery Advent Calendar 2020 の記事一覧 | Developers.IO
- クラスメソッド BigQuery Advent Calendar 2020 - Qiita
このエントリでは、2020年12月9日にGoogleによる買収のニュースが話題となったDataformでBigQueryのデータパイプラインを構築する手順についてご紹介します。
Dataformにはチュートリルが公開されてましたので、これを参考に進めていきます。
セットアップ
まずはお使いのBigQueryプロジェクトにDataformを接続します。まだBigQueryプロジェクトをお持ちでない場合は新規にプロジェクトを作成します。ここでは本アドベントカレンダーの07日目に作成した環境を使用します。
- Dataformのサイトにアクセスして、サインアップします。
- GoogleやGithubのアカウントを連携してサインアップすることもできます。
- サインアップが完了したら、「Create New Project」から新しいDataformプロジェクトを作成します。
- 作成するDataformプロジェクトの名称を入力します。
- 連携するBigQueryプロジェクトのIDを指定します。
- BigQueryプロジェクトのIDはGoogle Consleで確認できます。あわせてDataformからBigQueryにアクセスするためのサービスアカウントを作成して「BigQuery管理者」権限を付与します。合わせてJSON形式の秘密鍵を作成してダウンロードしておきます。
- データセットを作成するロケーションとサービスアカウントの秘密鍵ファイルを指定します。ダイアログの説明によると、同じリージョン内のデータセットにまたがってしかクエリを実行できないため、クエリを実行しようとするデータセットの場所と同じデータロケーションを選択する必要があるとのこと。そのためデータセットロケーションはBigQuery環境に合わせてASIA-NORTHEAST1を選択しています。
- これでDataformプロジェクトを作成できました。
データセットの作成
- Dataformプロジェクトダッシュボードの左上にある「+NEW DATASET」を押下し、新しいデータセットを追加します。
- データセットの定義はXSQLという拡張子のファイルとして保存されます。SQLXはSQLの拡張言語で、メタデータを記述したConfigブロックと、新しいテーブルまたはビューを定義するSELECTステートメントを含みます。簡単な例としてSalesforceのAccountテーブルをもとにActive_Accountビューを作成します。
- SalesforceのAccountテーブルは取引先会社が記録されており、削除済みフラグとしてIS_DELETEDフィールドが用意されています。Active_Accountビューでは削除済みのデータを除外するため、以下のようなSQLXを定義します。
config {
type: "view"
}
select * from salesforce.account
where is_deleted = false- SalesforceのContactテーブルには取引先責任者が記録されており、削除済みフラグとしてIS_DELETEDフィールドが、メール未達のフラグとしてIS_EMAIL_BOUNCEDフィールド用意されています。Active_Contactビューを作成して不要なデータを除外するSQLXを定義します。
config {
type: "view"
}
select * from salesforce.contact as contact
where contact.is_deleted = false and contact.is_email_bounced = false- 作成したActive_AcountとActive_Contactの2つのビューをもとにCustomerテーブルを作成します。
config {
type: "table",
description:"salesforceのアカウントと取引先責任者を結合した顧客マスタ"
}
select
account.id as account_id,
account.industry,
account.company_name,
contact.id as contact_id,
contact.first_name,
contact.last_name,
contact.state,
contact.email,
contact.lead_source
from
${ref("active_account")} as account
LEFT JOIN ${ref("active_contact")} as contact ON account.id = contact.account_id- Dataformで作成したビューやテーブルを参照する際、BigQuery上のSQLデータセット名を使用する代わりに、ref関数を使用してプロジェクト内の別のデータセットを簡単に参照できます。ref関数を使用すると、以下のメリットがあります。
- 完全なSQLデータセット名を指定する必要はありません。
- 参照するデータセットとクエリの間に自動的に依存関係が構築されます。
- 依存関係のあるクエリは、データの正確性を確保するため、Dataformプロジェクト内でのクエリ実行順序が管理されます。
- SQLXにはSQLクエリを記述するだけでなく、スクリプトを記述することも可能です。例えば、これまでの手順で作成したCustomerテーブルをカスタマイズして、stateフィールドの都道府県名から顧客を地域にマッピングする処理を作成します。
function areaGroup(prefecture) {
return `case
when ${prefecture} in ('北海道') then '01.北海道'
when ${prefecture} in ('青森県', '秋田県', '山形県', '岩手県', '宮城県', '福島県') then '02.東北'
when ${prefecture} in ('栃木県', '埼玉県', '群馬県', '東京都', '茨城県', '千葉県', '神奈川県') then '03.関東'
when ${prefecture} in ('新潟県', '岐阜県', '愛知県', '富山県', '山梨県', '石川県', '静岡県', '福井県', '長野県') then '04.中部'
when ${prefecture} in ('三重県', '滋賀県', '京都府', '大阪府', '奈良県', '和歌山県', '兵庫県') then '05.近畿'
when ${prefecture} in ('岡山県', '広島県', '山口県', '鳥取県', '島根県') then '06.中国'
when ${prefecture} in ('香川県', '徳島県', '愛媛県', '高知県') then '07.四国'
when ${prefecture} in ('福岡県', '佐賀県', '長崎県', '大分県', '宮崎県', '熊本県', '鹿児島県', '沖縄県') then '08.九州'
else '99.不明'
end`;
}
module.exports = {
areaGroup
};
config {
type: "view",
description:"取引責任者数を地方ごとに集計したVIEWです。",
columns: {
areaGroup: "地方",
count: "取引責任者数"
}
}
select customer.areaGroup, count(*) as count from ${ref("customer")} as customer
group by customer.areaGroup
order by customer.areaGroup- スクリプトを組み合わせることで、動的なSQLクエリを実行したり、データを追加するといった様々な処理が実行可能となります。
依存関係の管理
- 作成したテーブルやビューの依存関係は左サイドメニューから「Dependency Tree」を選択することで確認できます。
- ref関数で設定した依存関係に基づいたツリーのアイテムを選択するとテーブルやビューの詳細が表示されます。configブロックにDescriptionを記述しておけば、その情報を表示することができます。
プロジェクトの実行とスケジュール設定
Dataformコンソール右上部の「START NEW RUN」を押下することでプロジェクトを実行し、定義したビューやテーブルをBigQueryに作成します。
実行時にはプロジェクト全体を実行する、あるいはConfigブロックで定義されたタグを使って特定のファイルのみ実行することも可能です。
- プロジェクトが正常に実行完了すると、BigQueryに新しくDataformというデータセットが作成されており、分析用のデータを利用することができます。
- また、Dataformdで作成した分析用データは、オリジナルデータセットの更新に合わせて再生成する必要があります。日々の運用において、分析用データセットのスキーマや定義に変更があるかもしれません。このような場合のために、Dataformではcron形式のスケジュール設定をすることができます。
変更のコミット
Dataformはプロジェクトファイルの変更履歴をGitリポジトリで管理しています。最初に作成したプロジェクトは「name_dev」という名称のプライベートな開発ブランチに自動的保存され、ソースをコミットする際には変更点を確認することもできます。
まとめ
まだまだ本エントリでは紹介しきれない機能があるのですが、まずはDataformを使ってどんなことができるのか、その一端をご理解いただけたのではないでしょうか。
DataformはBiｇQuery以外にSnowflakeやRedshiftもサポートしており、様々なデータウェアハウスとの連携が可能です。クラウドデータウェアハウスのパフォーマンスを最大限に活かしたデータ分析を成功するにはスムーズかつ正確なデータ連携が不可欠であり、Dataformのようなデータパイプラインサービスがますます重要になるのではないかと考えます。
以上、最後までお付き合いいただきありがとうございました。
3
