こんにちはこんにちは！
『機能拡張スタンダードAll-In』というkintoneプラグインを販売しているSpicaです。
よろしくお願いします！
kintoneめちゃくちゃ便利なんですが使い倒すほどに、アプリに保存したデータを使っていろいろやりたいな、と思うことありませんか？
そんなときに挙がってくる選択肢のひとつとしてBigQueryがあります。そういうことにします。そういうことがあるんですきっと。
BigQueryとは
Google Cloud Platform (GCP) のサービスのひとつで、機械学習やBIツールのデータリソースとして使うことができるというしろものです。
今回の目的
kintoneアプリに保存されているデータをBigQueryにインポートできる形のjsonで出力して、BigQueryテーブルの作成まで行ってみようという試みです。
レコードデータはCSV出力することができますが、BigQueryにインポートするにはいくつかの問題をクリアしなければいけません。
問題点１. JSON化が必要
CSVのままでインポートすることができる場合もあるにはありますが、それは小規模なアプリの場合だと思われます。
特にサブテーブルが含まれているアプリだとCSVにしたときにサブテーブルの行数ぶんだけデータ行が増えてしまうので、JSON化する必要があります。
問題点２. カラム名は小文字英数とアンダースコアのみ
kintoneアプリのフィールド名はけっこう全角文字を使いますよね。そのままではBiqQueryテーブルのカラム名としては使えません。
フィールド名をカラム名として使えるものに差し替える必要があります。
問題点３. テーブルのスキーマ定義が必要
BigQueryテーブルにインポートする際に自動的に型判定をしてくれる機能もあるんですが、数値として読んでほしいのに文字列になってしまったり、日時の読み込みがうまくいかずエラーになったりと、これが結構なくせ者です。
kintoneのフィールド型からなるべく無理のないBigQueryテーブルのスキーマをちゃんと定義してあげると、最終的にできあがるデータがきれいです。
ということで参考のスクリプトを作ったよ
export_json というスクリプトです。そのまんまですな。
これを作るにあたり、kintoneのサンプルアプリ 『案件管理』 を使用しました。サンプルデータ込みでアプリを作成できるので、お試しする場合は同じアプリを作ってもらえればちゃんと動くと思います。
作ったファイル
-
export_json.html
kintoneアプリのカスタマイズ一覧のためのHTMLです。 -
export_json.js
json出力スクリプト本体。カスタマイズ一覧のHTMLに配置されるボタンから実行します。
使い方（設定方法）
1. カスタマイズ一覧の作成
アプリを作成したらまずはカスタマイズ一覧を作成しちゃいましょう。
アプリの設定ページ、一覧 タブから一覧を作成。
レコード一覧の表示形式で "カスタマイズ" を選択します。 ※管理権限が必要です
HTML の入力ボックスに export_json.html の内容をコピペして完了です。
2. javascriptの設定
必要なjavascpriptを設定します。
アプリの設定ページ、設定 タブから Javascript/CSSでカスタマイズ を選択。
PC用のJavaScriptファイルで読み込むように設定していきます。
jQueryを使ってますので、まずは https://js.cybozu.com/jquery/3.6.1/jquery.min.js をURL指定で追加。
次に export_json.js を追加です。
ダウンロードしてアップするかURL指定でhttps://spica-git.github.io/gh-projects/demo/kintone_export_json/export_json.jsを追加して、設定完了です。
アプリを更新しましょう。
使ってみる
カスタマイズ一覧を開くと
こんな感じになってるかと思います。
-
テーブルの内容
テーブルにアプリに配置されているフィールドがずらっと表示されていて、右端の列だけinputできるようになっています。
問題点２ で記載したBigQueryのカラム名の定義をここで行います。
番号とフィールド型名から生成された文字列が初期値になっていますので、とりあえずお試しするだけならこのままでOKです。 -
BigQueryテーブルスキーマの取得ボタン
アプリのフィールド内容からBigQueryのテーブルスキーマを生成して、テキストエリアに出力します。 -
レコードデータのjsonダウンロード
アプリの全レコードデータをjson形式にしてダウンロードを開始します。
ちなみにこのダウンロード機能ですが……めちゃくちゃ手抜きしてます。
具体的には、カーソル使ってません。あー、ね。
レコード数が多いアプリで実行すると全部のレコードは出力されません。すいません、時間が……そのうち直すかも……。
でもまあ、とりあえずここまででBigQueryにデータを持っていく準備ができました。
次はいよいよGCPのコンソールでの操作をしていきます。
とりあえずGCPのコンソールへ
GCP に利用登録しないとはじまりません。
90日間のトライアルと$300のお試し枠があるので、ちょっと使ってみるには十分でしょう。
クレジットカードの登録も必要になります。
GCPのプロジェクトを作る
GCPの登録ができたらこんな感じになるかと思います。
デフォルトで My First Project というプロジェクトが作られます。
目的やサービス別にプロジェクトという単位を作って、アクセス権なんかを管理していくのですが、お試しするならこのままでもOKですし新たにプロジェクトを作成してもOKです。
BigQueryのデータセットを作る
ナビゲーションメニューのハンバーガーアイコンを押すと、プルダウンメニューがびろっと開きます。
BigQuery があるので押下。
エクスプローラのカラムが開いてプロジェクト名が表示されます。
エクスプローラ内にあるプロジェクト名のオプションアイコンから「データセットを作成」、データセットIDを適当に決めて、データのロケーションを選択。
特に理由がなければ東京リージョンでよいでしょう。
「データセットを作成」ポチッとな！
なんかできた。これがデータセット。
データセットではデータ本体であるテーブルを作成したり、SQLを使ってデータにアクセスするための中間パーツ的なものを作成したりすることができます。
いよいよkintoneのデータをインポートしてみる
データセットのオプションアイコンを押すと「テーブルを作成」というメニューがあるので、ここからテーブルを作成していきます。
-
テーブルの作成元
ダウンロードしたjsonファイルを使うので「アップロード」を選択。 -
ファイルを選択
kintoneアプリから出力したjsonファイルを選択します。 -
テーブル
テーブル名です。任意の名前でOK -
スキーマ
自動検出でもいいんですが、せっかくなんで手動で設定してみた例を。
export_jsonスクリプトの機能「BigQueryテーブルスキーマの取得」でテキストエリアに出力される内容をすべてコピペする感じです。
「テーブルを作成」をファイナルフュージョン承認！
………… ジョブを作成しています ………… ささやき ………… いのり ………… えいしょう ………… ねんじろ！
てってれー！ 上手にできました！
あとはこのテーブルをいろいろと活用するだけです。
たとえばデータプラットフォーム（BIツール的なもの）の Looker Studio なんかが面白いかもしれません。
以上です。いかがだったでしょうか。
できれば Looker Studio の使い方の触りくらいまでできればと思っていたんですが、ここまでといたします。
Looker StudioはGoogleスプレッドシートを使って簡単な集計を行うこともできますが、やはりBigQueryのテーブルをデータリソースにするのが良いですね。データの更新が便利だったりなによりSQLでリレーション的なことができるので、BIツールとしての使い勝手が飛躍的に良くなります。
Looker StudioではなくLookerを使うための足がかりになる、ような気がします。Lookerのほうは使った事ありませんが！
あとよければ、プラグイン『機能拡張スタンダードAll-In』 の試用もしてみていただければと思います。
PDF出力とか細かい自動計算とかいろいろできるプラグインとなっております。
そんな感じでーす！