【データ分析】データ分析基盤にデータソースを接続する【Redash】

前回、【データ分析】BIツールを使ってデータ分析基盤を構築する方法【Redash】を解説しました。

Redashをつかってダッシュボード作ってある程度可視化できたけど、自分のデータを可視化してみたい….と思っている方、自分が持っているデータソースを使えば、excel上で解析を使わなくても簡単にダッシュボードで分析することが可能です。

今回は、自前のデータソースとしてGoogle SheetsやCSVを用意して、そこに蓄積されたデータを可視化させてみようと思います。Google drive上にExcelファイルをアップロードしても同様のことができます。実際にできるといろんな解析が自動化できることがわかると思いますので早速やってみましょう。

*前回の記事でRedashの分析環境ができている前提で話をします。そのため、まだ環境構築できていない方は、先に前回記事の環境構築部分から取り組んでいただけると幸いです。

【データ分析】BIツールを使ってデータ分析基盤を構築する方法【Redash】

動作検証済み環境

macOS Monterey(12.4), クアッドコアIntel Core i7, メモリ32GB

公共データを用いたRNA-seq解析に関する初心者向け技術書を販売中

画面キャプチャをふんだんに掲載したわかりやすい解説！
自宅PCからドライ研究が始められます

技術書ページへ

Google スプレッドシートやCSVをデータソースにする

Google スプレッドシートやCSVをデータソースにするには、Google Cloud Platformの設定→Google スプレッドシートやCSV側の設定→Redash側の設定、と三段階で設定していきます。

Google Cloud Platformの設定

Google スプレッドシートに連携するためにはGoogle Cloud Platform にアクセスする必要があります。

以下のURLからGoogle Cloud Platform のAPIとサービスにアクセスしてください。（Googleアカウントが必要です）
https://console.cloud.google.com/apis/credentials

プロジェクトを作成をクリックします。次の画面がでたらそのまま「作成」を押してください。

「＋認証情報を作成」をクリックし、ドロップリストの中から「サービスアカウント」を選んでください。

サービスアカウント名：Redashと入力したら、「作成して続行」→「続行」→「完了」に進みます

サービスアカウントのところに新たなアカウントが追加されました。

次にサイドバーから「有効なAPIとサービス」をクリックします。

「APIとサービスの有効化」をクリックします。

出てきた画面を下にスクロールするか、検索ボックスに「Google Sheets API」と入力するとGoogle Sheets APIが見つかりますのでクリックしてください。

「有効にする」ボタンをクリックします。

サイドバーから先程の「認証情報」に戻り、先程作成したサービスアカウント名をクリックしてください。

タブからキーを選び、「鍵の追加」→「新しい鍵を作成」に進みます。

キーのタイプがJSONにあっているのを確認して作成をクリックしてください。鍵のダウンロードが始まりますので、保存しておいてください。

キーを作成することができました。

Google スプレッドシートやCSV側の設定

次は、Google スプレッドシートやCSV側の設定に移ります。Googleドライブにアップロードされた連携したいシートに行き、右上の共有をクリックしてください。

「ユーザーやグループを追加」のところに、先程ダウンロードしたJSONファイルの「client_email」の部分を入力します。

JSONファイルは以下のようになっていると思います。
下の例だと「redash@hogehoge.iam.gserviceaccount.com」の部分になります。

{
  "type": "service_account",
  "project_id": "(省略)",
  "private_key_id": "(省略)",
  "private_key": "(省略)"
  "client_email": "redash@hogehoge.iam.gserviceaccount.com",   // <---
  "client_id": "(省略)",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "(省略)"
}

これで、Google スプレッドシートでの設定は完了です。

Redashの設定

次にRedashに戻ります。左サイドバーから「Settings」を選び、「New Data Source」をクリックします。もしデータソースが見当たらないこちらからデータソースにアクセスしてください。

Google Sheets を選択します。CSVの場合でもGoogle Sheetsを選んでください。

Nameをsampleと設定し、「Click to upload」をクリックして、先程ダウンロードしてきたJSONキーをアップロードしてください。完了したら「Create」を押します。

きちんと接続されているかTestしてみましょう。Test Connectionをクリックして、Successをが右下に出てくると成功です！

クエリの書き方

実際にGoogle スプレッドシートのデータを元にクエリを作成してみます。

クエリとして以下のように書きます。

<SpreadSheet の ID >|0

SpreadSheet の ID は、SpreadSheet の URL で確認できます。
シート番号は、SpreadSheet ファイルの左が0になります。例えば1枚目のシートなら 0 になります。

https://docs.google.com/spreadsheets/d/<ここがIDです>/edit#gid=0

入力が終わったら「Execute」をクリックしてください。

以下のようにGoogle スプレッドシートの情報が表示されたら成功です！

しかしながら、この状態では他にデータソースのデータを抽出するクエリを書くことができません。これをするためには結果のキャッシュという作業が必要になります。

右上の名前をNew Queryから変更して、左下の「Save」をクリックしてください。

サイドバーからQueriesをクリックすると、先程のqueryが保存されているのが確認できます。こちらをクリックしてください。

こちらのURLの「queries/5」の部分が必要となります。

SQLを使ってキャッシュしたテーブルからデータを抽出する場合、以下のようにして取り扱います。

localhost/queries/n  → 　query_n

今回の画像例ではquery_5として指定します。

次に右サイドバーから「Settings」を選び、「New Data Source」をクリックしてください。

「Query Results」と検索して選びます。

Nameをつけて「Create」を押下してください。

ここまでできたら、New Queryを開いてQuery ResultsをデータソースにしてSQLを叩いてみましょう。参考SQLを載せておきます。

select * from query_5;　　-- 自身のquery番号に変更してください。

Query ResultsからSQLを用いてデータを呼ぶことができました！(データソースを変更するのを忘れないようにしてください)

このように、SQL分に応じて、結果を抽出することが可能となります。

Excelで対応する場合

excelをデータソースにする場合は、Google sheetとほぼ同じ手順をたどるのが一番カンタンだと思います。その場合、Excel→Google sheetへの変換操作が必要になります。

Google driveにexcelファイルをアップロードして開くと、右上に「.XLS」とついた状態で開けるかと思います。

右上の「ファイル」をクリックし、「Google スプレッドシートとして保存」をクリックします。

同じファイルがGoogle sheetsで新しく保存されるので、こちらを使って連携しましょう。

終わりに

いかがだったでしょうか。実際に自分のデータを用いて様々な可視化ができるようになるかと思います。
Googleスプレッドシート上でいちいちデータ統合をしなくてもBIツール上のコマンドで様々なデータソースを組み合わせて解析ができるようになります。
実際に使ってなれると、excelで管理するよりも楽でより可視化に優れていることが分かると思います。
データ分析基盤を使って自分の解析環境をどんどん自動化して行きましょう。