BigQuery + Googleスプレッドシートでほぼノーコード開発でデータマート作成したお話

こんにちは。日々の業績データのデータマート作成をいかにシンプルな構成で実現できるか思案していたところ、BigQueryの外部テーブル機能が便利だったのでそのお話です。
↓外部テーブル作成設定画面

BigQueryの外部テーブル機能を使う一番のメリットとして、データ取り込みの開発を省略して、BigQuery上でスプレッドシートのデータをテーブルとして扱える点でしょう。
実際にデータ分析業務で、RPAで業務システムから業績データのCSVファイルを自動でDL→スプレッドシートに出力→BigQueryのスケジューリングクエリ機能で、スプレッドシートに出力したデータをデータマートにInsertすることで日々のデータマートへのデータ蓄積を行っています。

※業績データをZIP形式やCSV形式でDLして、スプレッドシートに出力できない場合

GoogleAppScript(GAS)のスクリプトでスプレッドシートに出力しましょう。
↓zipファイルを解凍してスプレッドシートに出力するGASのコードです。

//zipファイルのフォルダ、ファイル名(例：yyyy-MM-dd_XXXX.zip)
var folderID = "XXXXX";
var date = new Date();
var file_name = Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy-MM-dd') + "_XXXX.zip"

//zipファイルの解凍先フォルダ、ファイル名
var unzipfolderID = "XXXXX"
var unzip_file_name = Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy-MM-dd') + "_XXXX.csv"

//出力するスプレッドシート、シート
var ss = SpreadsheetApp.openById('XXXXX');
var sheet = ss.getSheetByName('XXX');

//ZIP解凍
function unzip() {
 //解凍する対象のファイルを取得
 var zipblob = DriveApp.getFolderById(folderID).getFilesByName(file_name).next();

 //ファイル解凍の準備
 var unzipfile = Utilities.unzip(zipblob);

 //解凍先フォルダにunzipしたファイル群を入れる
 var unzipfolder = DriveApp.getFolderById(unzipfolderID)
 for (i = 0; i < unzipfile.length; i++) {
   unzipfolder.createFile(unzipfile[i].setName(unzip_file_name));
 }

　//解凍したCSVファイルをパース
 var file = DriveApp.getFilesByName(unzip_file_name).next();
 var csvData = Utilities.parseCsv(file.getBlob().getDataAsString('shift-jis'));

　//シートを一旦クリアして、CSVデータを貼り付け
 sheet.clearContents();
 sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}

zipではなくCSVファイルをスプレッドシートに出力したいだけであれば、
//解凍したいCSVファイルをパース　以降のコードを参照ください。

外部テーブル機能はスプレッドシート上の参照するシート、列を選択できるので、スプレッドシートの関数で整形できるのも大変便利です。実際に、
・QUERY関数
・FILTER関数
・ARRAYFORMULA関数
この３つの関数は、特定の列だけ抽出したり、あらかじめBigQueryに連携するデータをフィルタリングする目的で使っています。

いかがでしょうか、まとめるとBigQueryの外部テーブル機能、スケジューリングクエリとGoogleスプレッドシートだけで、ほぼノーコード、ノーアプリケーションでデータマートの作成が可能だったので、感動的でした。笑