BigQueryでPreppinDataのSQL版に挑戦するためのいろは

はじめに

私はTableau Prepのデータ加工チャレンジである「PreppinData」にここ1年ほど挑戦し、
毎週日曜日の勉強会に参加しているのですが、
その中でとある方が「SQLで解いてみた！」っていうのを聞いて、自分もやってみたいと思ったのが本投稿のきっかけです

PreppinDataとは？

Tableau Prepをメインとしたデータ加工チャレンジです。
毎週水曜日に発信され、翌火曜日にPrepでの解説が配信されます。
2019年から続いている伝統あるチャレンジです。
詳しくはこちら↓
PreppinData入門 - Tableau Prepユーザー会

で、今回はこのPreppinDataをBigQueryを使ってSQLで解いてみようというものです。
ただ、解き方については解説しません。ご自身でぜひ挑戦頂ければと思います。
今回はBigQueryの設定、SQLを書く前に行う下準備などについて紹介します。

BigQueryでのPreppinData挑戦の流れ

PreppinData挑戦、以下の流れで行っています。

1. 初期設定（最初だけ）
2. データセットを作成
3. csv 下準備
4. テーブルを作成
5. クエリを保存
6. SQL作成
7. 検証
8. SQL保存

それでは、行ってみよう！

1. 初期設定（最初だけ）

Google Cloud Platformのアカウントを作成し、BigQueryが使えるようになる必要があります。
ここは色々な方がすでに記事にしていますので、割愛します。
私はこちらのサイトの手順通りに行わせて頂きました。
とてもわかりやすいです。
初心者でもわかるBigQuery入門｜導入方法や使い方を徹底解説！

2. データセットを作成

BigQueryの初期設定が終わりましたら、作ったプロジェクトの中にデータセットを作っていきます。
1.でプロジェクト作成の手順が書いてあったと思いますので、そのプロジェクトの3点リーダーから「データセットを作成」をクリックします

次に、データセットを作成していきます。

• データセットID：わかりやすい名前を付ける（例：PreppiData2024week51）
• リージョン：よくわからないけど多分マルチリージョンより単一リージョンの方がよさそう。私は関西在住なので「asia-northeast2（大阪）」にしました
• 他の項目はでデフォルトでOK

これらの設定が完了したら、「データセットを作成」を押します。

3. csv下準備

次に、PreppinDataのInputとなるデータを読み込んでいくのですが、私はここで最初一番苦労しました。
Tableau Prepは割とどんな形式でのexcel, csvでも読み込んでくれるのですが、BigQueryはそうはいきません。DBなので、テーブルの形式に法ったものでないとエラーになったり全部NULLになったりします。
私がこれまで取り組んできた中で学んだ重要点は以下の2点です。

1. UTF-8にする

PreppinDataのInputデータは大概BOM付UTF-8です。私はここがわからず本当に苦労しました。
分かってしまえば簡単なので、VSCodeでササっと文字コードを変更しちゃいましょう。
VSCodeがない方はサクラエディタ等でもOKなのですが、そちらの方法は割愛し、VSCodeのやり方を紹介します。

まず、InputのcsvファイルをVSCodeで開いて、右下の文字コードをクリック
すると、上部に「エンコード付きで保存」が出てくるので、これをクリックします。

念のため、Ctr+Sで上書き保存しておきましょう。
これで、文字コードの変換は完了。
ちょっと待って！まだVSCodeは閉じないでください！
まだ少しテキストエディタでやることが残っています。

2. ヘッダーから記号を取り除く

ヘッダーに記号が入っていると高確率でエラーになるので、取り除くことが必要です。
値の場合どうなのかというと、正直まだこれまでの挑戦で値に記号が入っていたことないのでわからないです。
優秀なTableau Prep君ならどうにかしてくれるのですが、DBだとちょっと厳しいかもしれないですね...
とはいえヘッダーなら簡単に修正できるので、さっとやっちゃいましょう。
画像の例だと「：」とかが怪しいので、キャメルケースや「_（アンダーバー）」に直します。

画像の例の場合「Period life expectancy...」の後半部分は余計なので、まるっと削除してしまいました。
因みにスペースはどうなのかというと、実はいけるんですね。でも注意です。
ヘッダー名としてスペース入りは読み込めるけど、いざSQL書くってなったら列名にスペースを入れると構文エラーになります。
私はこのために過去ALTER TABLEで列名の変更などもしましたが、めんどくさいので最初にテキストエディタで変更する方がスムーズです。

これで、csv下準備は完了です。
次の工程に行きましょう。

4. テーブルを作成

2.でデータセットを作成しましたので、そこにテーブルを作っていきます。
データセットをクリックするとデータセット情報が出てきますので、そこから「テーブルを作成」をクリック

そしてテーブル情報を入力していきます。

• テーブルの作成元：「アップロード」を選択

• ファイルを選択：「参照」をクリックしてダイアログで3.で加工したcsvファイルを選択

• テーブル名：preppindataをやるにあたってわかりやすい名前を入力。※「PD2024w51Input」とかにすると、Inputが複数あるときに分かりにくいので、元々のInputのファイル名に近いのにするとよいです。

• 

• スキーマ：「自動検出」でOK.よっぽど変なcsvでない限り、スキーマを検出してくれるはず！（過去ココがダメなのかと思って、手動入力に挑戦したこともありましたが、余計訳が分からなくなりました（涙））
  

次がめっちゃ大事なのですが、「詳細オプション」を開いて「スキップするヘッダー行」を1にしてください。こうすることで1行目をヘッダとして読み込んでくれます。
ここを0のままにするとヘッダー名が「string_field0」とかのフィールドが自動生成されてしまいます。

ここまで出来たら、あとの設定はデフォルトで、「テーブルを作成」を押しましょう！

これまで行った下準備が正しくできているか、この工程で試されるので、私はいつもテーブル作成でドキドキしてしまいます。

うまくいったらこのような通知が画面下に出るので

「テーブルに移動」をクリック。

するとこのようにスキーマが表示されて、無事取り込めていることが分かります！
めでたしめでたし。

念のため、データの中身も確認しておきましょう。
スキーマの画面から、「次で開く＞SQLクエリ＞新しいタブ」をクリック

すると、最初列名が空欄のSQLクエリが自動生成されますので、
「*（アスタリスク）」を入力して「実行」をクリック。
すると、以下のようにデータの中身が表示されます。

無事取り込めてますね！

5. SQLクエリの保存

あとは、先ほど開いたSQLクエリ画面にSQLをゴリゴリ書いていくだけですが、
消えたら嫌なので保存しておきましょう。
先ほど開いた「無題のクエリ」の「保存」プルダウンから「クエリを保存」をクリック。

すると、クエリの保存画面が現れますので、
クエリの名前と、リージョンを選択して、「保存」ボタンを押すだけ。
リージョンはデータセットと合わせなきゃいけないのかな？と思ってましたが、そうでもないみたいです。
なぜかここでは「asia-northeast2（大阪）」が出てこないので、「asia-northeast1（東京）」としました。

6. SQL作成開始

さぁ、SQLをゴリゴリ書いていきましょう。
何度も言いますが途中で消えたら本当に悲しいので、ちょくちょく上書き保存してくださいね。
上書き保存は先ほどと同じ「クエリを保存」でOK

7. 検証

PreppinDataはOutputと完全一致させることがゴールとなります。
なのでSQLでも必ず検証を行いましょう。
Tableau Prepの場合は結合やUNIOINを使うといった方法がありますが、
SQLで二つのテーブルの完全一致を検証するには、EXCEPTを使います。
これもどこからの記事を参考に取得させて頂きましたが、以下の構文を使ってください。

--検算
SELECT
  *
FROM
  (SELECT * FROM {チャレンジした結果最終アウトプット} EXCEPT DISTINCT SELECT * FROM {PreppinData解答}
)
UNION ALL
SELECT
  *
FROM
  (SELECT * FROM {PreppinData解答}
 EXCEPT DISTINCT SELECT * FROM {チャレンジした結果最終アウトプット})

少々長いですがよく見るとなんてことない、アウトプットにあり解答にないデータと、解答にありアウトプットにないデータをがっちゃんこしているだけです。
これでアウトプットと解答の差分が取れます。
解答とアウトプットが完全一致すれば、以下のように表示件数が0になります。
これで正解です。

Prepで結合を使うとどこが違っているのかを確認できるのですが、SQLではそれができません。（あるのかもしれないけど私はまだ考案してないです）
なので合わない時は、作成したサブクエリやWITH句にもどって一個一個検証するように、私はしています。
途中書き忘れましたが、解答テーブルもまたテーブルとして取り込むことが必要です。
その場合はcsvでの下準備を忘れないようにしましょう。

8. おまけ（SQL保存）

せっかく作ったSQL、ローカルにも保存しておきたいですよね！
それも簡単で、SQL画面の「ダウンロード」を押せばOKです。

以上で、PreppinData BigQuery SQL版の取り組みは完了です。

終わりに

いかがでしたでしょうか？
私自身まだこのSQL取り組みは4回ほどチャレンジしたのですが、
PreppinDataもかなりSQLの学習になると感じています。
SQLの文法は一通り学習したので、実務へ向けて長いSQL文を書いてみたいという方にとてもお勧めです。
あと、これはかなり重要なのですが、
SQLをいきなり書くより最初にPrepでフローを作ったほうが早いです
これは本当にひしひしと感じていて、
SQLだとどのようにOutputまでたどり着くか、道筋を考える必要があり、そのためにPrepフローがかなり役立ちます。
Prepはステップの取り消しや途中確認がかなり容易にできるので、多くの場合SQLを書くより早く加工でき、直感的に操作できます。
私も1からSQLを書いた時は頭がこんがらがっていたのですが、完成したPrepフローを見ながら1つ1つWITH句を作るようになってからは、チャレンジが楽しくなりました。
この方法は本当にお勧めです！

それでは、最後まで読んでくださってありがとうございました。
PreppinDataをSQLでやっている人はかなり少ないと思うので、仲間が増えると嬉しいです。

Rieko