【Google Cloud】初級編 Dataformを使ってSQLでELT処理を実装する

概要

GCPのサービスの一つであるDataformについて、以下の２部構成でまとめています

• 初級編：★本記事
• 実践編：https://qiita.com/Memmbers_DataAdventure/items/b0722142c55bb9932126

Dataformの利用開始手順、基本的な機能に加え、実際のビジネスシーンを想定して、より実践的な使い方もいくつか掲載しているので参考になれば幸いです

目次

1. Dataformとは

a. サービス概要
b. 料金体系
c. 用語

2. 基本的な機能の説明

a. 環境準備
b. SQLを用いてELT処理を書く
c. 実行
d. 実行結果の確認

3. まとめ

内容

Dataformとは

サービス概要

SQL を使用して BigQuery でスケーラブルなデータ変換パイプラインを開発、運用できるサービス。※公式ドキュメントから抜粋

何がいいの？

• SQLだけでBigQuery内のデータ変換パイプライン(ELT)を実装できる
• JavaやPythonなどのプログラミング言語の知識は不要
• BigQuery内でELT処理を実行でき、BigQueryの自動スケーリングの恩恵を受けられる
• パイプライン上のtableやView同士の依存関係(リネージ)を可視化できる
• そして、Dataform自体の利用は無料

料金体系

前述の通りDataform自体は無料のサービスです
ただし、実行対象のBigQueryなどはそのリソース自体の料金が発生するので、例えばDataformで作成したBigQuery上のtableなどに対しては、別途料金が発生します

用語

以下の機能紹介でも出てくるDataformで用いられる用語についていくつか事前に解説しておきます

• リポジトリ
  Dataformで処理するテーブルなどをまとめたもの
  Dataformで管理できる最も大きな単位
  概念としてはGitHubのリポジトリなどと近しい
• ワークスペース
  Gitでいうブランチのようなもの
  これを使ってDataformを動かすSQLを書いたり実行したりできる
• sqlx
  Dataformで使うSQLは、一部標準のSQLにはない記法がある
  この.sqlx拡張子のファイルにデータ変換のためのSQLを記述する

基本的な機能の説明

手っ取り早くハンズオンで試してみたい方はこちらの記事がおすすめです
筆者自身も最初はこちらを参考に触ってみました

• Dataformって何？便利そうだし調べてみた！（前編）
• Dataformって何？便利そうだし調べてみた！（中編）
• Dataformって何？便利そうだし調べてみた！（後編）

環境構築

GCPのアカウントがあればOK
DatformはBigQueryの付属品的な立ち位置らしく、BigQueryのページ内にある

「+リポジトリを作成」からリポジトリを作成できれば準備完了
Dataformのリソース自体にお金がかからないので、ここら辺は気軽に作ったりできて安心

もし検証用にBigQueryの公開データセットを使うなら、リージョンはus-central1にする必要あり

リポジトリ上では、以下のような機能がデフォルトで備わっているので便利
これだけで小～中規模くらいのデータ分析基盤の管理は格段に楽になりそう

• 開発者毎のワークスペース
• ジョブ実行ログの確認
• スケジュール実行
• リポジトリの各種設定

　

ワークスペースの中身はこんな感じ
デフォルトで以下のようなディレクトリ構成が作成される
definitions/フォルダ内にsqlxファイルを配置し、その内容に沿ってDataformがELT処理してくれます

root
 ├definitons/　# このリポジトリで扱うデータについて定義するsqlxファイルを配置するフォルダ
    └　この中の構成は自由
 ├.gitignore
 ├dataform.json　# ここにデータベース名などのデフォルト値指定できる
 ├package-lock.json
 ├package.json

.sqlxファイルSELECT分を書くことで、Dataformが裏でいい感じELT処理をしてくれます

肝となるのは2か所

• 一つ目はconfigの箇所
  このsqlxファイルで作成するデータの属性を定義できます
• もう一つはFROM句で${ref("ghcnd_2012_by_month")}のように用いられているref()関数
  このref()によって依存関係を宣言することで「COMPILED GRAPH」タブから、テーブル間の依存関係を可視化し、確認することができます

config {
  type: "table", -- テーブルとして作成すること宣言している. viewとすればビューで作成される
  schema: "dataform", -- このデータを作成するスキーマ（BigQueryでいうデータセット）を指定可能
  name: "atlanta_weather_1to6", -- テーブル（ビュー）名を指定可能
  description: "アトランタ1月～6月降水量",
  columns: {
    id: "id",
    name: "観測所名",
    date: "日",
    prcp: "降水量"
  }
}

SELECT
  id,
  name,
  date,
  MAX(prcp) AS prcp
FROM (
  SELECT
    wd.id,
    s.name,
    wd.date,
    IF( wd.element = 'PRCP', wd.value / 10, NULL ) AS prcp
  FROM
    ${ref("ghcnd_2012_by_month")} AS wd
  JOIN
    ${ref("ghcnd_stations")} AS s
  ON
    wd.id = s.id
  WHERE
    wd.date BETWEEN "2012-01-01" AND "2012-06-30"
    AND s.name LIKE "%ATLANTA%")
GROUP BY
  id,name,date
ORDER BY
  date

ref()関数の内容をもとに作成されたリネージ

実践的な機能の説明

まとめ

SQLなら、エンジニアだけではなくデータアナリストでもELT処理を気軽に書くことができ、データの民主化も推進できそう

類似サービスの代表的なものにdbtがあるが、BigQueryをデータ分析基盤として使っているなら、親和性の観点でDataformも選択肢としてかなり有力です