【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 でスケーラブルなデータ変換パイプラインを開発、運用できるサービス。※公式ドキュメントから抜粋

https://cloud.google.com/dataform?hl=ja


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

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

https://cloud.google.com/dataform#pricing

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


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

* [Dataformって何？便利そうだし調べてみた！（前編）](https://qiita.com/kccs_hiromi-nishida/items/28be115f44c16fb077a4)
* [Dataformって何？便利そうだし調べてみた！（中編）](https://qiita.com/kccs_hiromi-nishida/items/ea6bb6835f938dcc3f02)
* [Dataformって何？便利そうだし調べてみた！（後編）](https://qiita.com/kccs_hiromi-nishida/items/f86e8367d2d8df2bb48c)


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

![ホーム画面.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3199948/97619ca2-4744-ba07-f462-d8c508b61563.png)


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


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

<br>

リポジトリ上では、以下のような機能がデフォルトで備わっているので便利
これだけで小～中規模くらいのデータ分析基盤の管理は格段に楽になりそう
* 開発者毎のワークスペース
* ジョブ実行ログの確認
* スケジュール実行
* リポジトリの各種設定


![リポジトリ内.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3199948/d6ad2939-e91c-f6f6-85cd-55a16ece0771.png)

　

ワークスペースの中身はこんな感じ
デフォルトで以下のようなディレクトリ構成が作成される
`definitions/`フォルダ内にsqlxファイルを配置し、その内容に沿ってDataformがELT処理してくれます
```
root
 ├definitons/　# このリポジトリで扱うデータについて定義するsqlxファイルを配置するフォルダ
    └　この中の構成は自由
 ├.gitignore
 ├dataform.json　# ここにデータベース名などのデフォルト値指定できる
 ├package-lock.json
 ├package.json
```


![ワークスペース＿インプットの定義.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3199948/f10d0b2c-ce7b-dfb4-d313-6003d79cabf1.png)



<br>

 .sqlxファイルSELECT分を書くことで、Dataformが裏でいい感じELT処理をしてくれます
 
 
![sqlx.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3199948/85b4c919-638b-c047-ae98-348c5f6fd2f8.png)



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

```sql
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
```


 <br>


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

![リネージ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3199948/deea4c02-bd55-55b8-5ce7-363991933511.png)


## 実践的な機能の説明
https://qiita.com/Memmbers_DataAdventure/items/b0722142c55bb9932126




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

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