(WIP) Cloud Node.js Clientを利用してBigQueryのクエリ結果を取得
0. 動機
- BigQueryの実行結果を整形・表示・チャート化しやすい環境をつくる
- ほぼ自分用
1. Nodeからクエリを実行
GitHub - GoogleCloudPlatform/google-cloud-node: Google Cloud Client Library for Node.js
準備
npm install --save @google-cloud/bigquery
index.js
const config = { projectId: 'foo', keyFilename: '/path/to/keyfile.json' }; const bigquery = require('@google-cloud/bigquery')(config); const query = ` SELECT id FROM [foo:bar] LIMIT 10 `; bigquery.query(query) .then(data => { const rows = data[0]; console.log(rows); }) .catch(err => { console.log(err); res.json(err); });
2. Express上で動かす
const express = require('express'); const router = express.Router(); const path = require('path'); const config = { projectId: 'foo', keyFilename: path.resolve('/path/to/keyfile.json') }; const bigquery = require('@google-cloud/bigquery')(config); router.get('/', (req, res, next) => { // クライアントからクエリを受け取る const query = req.query.query; bigquery.query(query) .then(data => { const rows = data[0]; res.json({ data: rows }) }) .catch(err => { res.json(err); }); }); module.exports = router;
自動再起動
- nodemon
CORS
app.use(function (req, res, next) {
res.setHeader('Access-Control-Allow-Origin', 'http://localhost:3000');
res.setHeader('Access-Control-Allow-Methods', 'GET, POST, OPTIONS, PUT, PATCH, DELETE');
res.setHeader('Access-Control-Allow-Headers', 'X-Requested-With, Content-Type');
res.setHeader('Access-Control-Allow-Credentials', false);
next();
});
3. クライアント側でデータの整形と表示
- ロジックテスト
- HighCharts
TypeScriptでJSONファイルをimportする
webpack2利用時。
custom-typings.d.ts
declare module '*.json' { const value: any; export default value; }
foo.ts
import * as data from './data.json'; // ...
通貨記号つきで金額を表示
便利。（最近まで知らなかった）
IEは11以上なら大丈夫。
console.log(number.toLocaleString('ja-JP', { style: 'currency', currency: 'JPY' })) // → ￥123,457 console.log(new Intl.NumberFormat('ja-JP', { style: 'currency', currency: 'JPY' }).format(number)); // → ￥123,457
When formatting large numbers of numbers, it is better to create a NumberFormat object and use the function provided by its NumberFormat.format property.
ng-tableさわる
準備
webpack×TypeScriptの場合
webpack.config.js
module.exports = { // ... module: { rules: [ { test: /\.tsx?$/, loader: 'ts-loader' }, { test: /\.html$/, use: [ { loader: 'ngtemplate-loader', options: { requireAngular: true, relativeTo: '/src/browser/', prefix: 'ng-table/' } }, { loader: 'html-loader' } ] } ] } // ... };
例
カラムが固定
ng-table="$ctrl.tableParams"を使う- テーブルヘッダの内容、各セルの調整（formatなど）、ソート設定などはテンプレート上で行える
import { NgTableParams } from 'ng-table'; class Controller { tableParams: any; constructor() { } $onInit() { const data = [ { name: "Moroni", age: 50 }, { name: "test", age: 10 }, { name: "Moroni", age: 50 } ]; this.tableParams = new NgTableParams({ // pagerの設定 (paginationを非表示にする) counts: data.length }, { dataset: data, // pagerの設定 counts: [] }); } } export const TableNormalComponent: ng.IComponentOptions = { controller: Controller, template: ` <table ng-table="$ctrl.tableParams" class="table" show-filter="true"> <!-- $dataでdatasetを参照 --> <tr ng-repeat="user in $data"> <td title="'Name'" filter="{ name: 'text'}" sortable="'name'"> {{user.name}}</td> <td title="'Age'" filter="{ age: 'number'}" sortable="'age'"> {{user.age}}</td> </tr> </table>`, };
カラムが動的に変わる
ng-table-dynamic="$ctrl.tableParams with $ctrl.cols"を使う- テーブルヘッダの内容、各セルの調整（formatなど）、ソート設定などはjs上で
import { NgTableParams } from 'ng-table'; class Controller { tableParams: any; cols: any; constructor(private NgTableParams) { } $onInit() { const data = [ { name: "Moroni", age: 50 }, { name: "test", age: 10 }, { name: "Moroni", age: 50 } ]; this.cols = [ { field: "name", title: "Name", sortable: "name", show: true }, { field: "age", title: "Age", sortable: "age", show: true }, { field: "money", title: "Money", show: true } ]; this.tableParams = new this.NgTableParams({ // initial sort order sorting: { name: "asc" }, }, { dataset: data, }); } } export const TableDynamicComponent: ng.IComponentOptions = { controller: Controller, template: ` test <table ng-table-dynamic="$ctrl.tableParams with $ctrl.cols" class="table"> <!-- $dataでdatasetを参照 --> <tr ng-repeat="row in $data"> <!-- $columnsでcolumn設定を参照 --> <td ng-repeat="col in $columns">{{row[col.field]}}</td> </tr> </table> `, };
その他
Nested Property
const data = [{ "name": "Martin", "surname": "Freeman", "details": { "country": "Uzbekistan", "personal": { "age": 55 } }, "job": { "money": 564 } } ]; // ng-table ` <tr ng-repeat="row in $data"> <td data-title="'Name'" filter="{name: 'text'}">{{row.name}}</td> <td data-title="'Age'" filter="{'details.personal.age': 'number'}">{{row.details.personal.age}}</td> <td data-title="'Money'">{{row.job.money}}</td> <td data-title="'Country'" filter="{ 'details.country': 'select'}" filter-data="demo.countries">{{row.details.country}}</td> </tr> ` // ng-table-dynamic this.cols = [ { valueExpr: "item.name", title: "Name", show: true }, { valueExpr: "item.details.personal.age", title: "Age", show: true }, { valueExpr: "item.job.money", title: "Money", show: true }, { valueExpr: "item.details.country", title: "Country", show: true } ]; ` <tr ng-repeat="row in $data"> <td title="test" ng-repeat="col in $columns">{{$eval(col.valueExpr, { item: row })}}</td> </tr> `
template-headerのデフォ
header.html
<ng-table-group-row></ng-table-group-row> <ng-table-sorter-row></ng-table-sorter-row> <ng-table-filter-row></ng-table-filter-row>
クライアント側でCSV生成してダウンロードさせる
追記:
Safariも10.1からdownload属性が利用できるにようになりました。
https://developer.mozilla.org/en-US/docs/Web/HTML/Element/a#Browser_compatibility
CSVの生成
const arr = [ ['ご利用年月日', 'ご利用"箇所', 'ご,利,用,額'], ['2017/01/29', '', '""345'], ['2017/02/01', '"ＡＭ"ＡＺＯＮ．ＣＯ．ＪＰ', '7,362'], ]; /** * 各フィールドの囲い -> ダブルクォーテーション * 各フィールドの区切り -> カンマ * 改行コード -> LF */ function arrToCSV(arr) { return arr .map(row => row.map(str => '"' + (str ? str.replace(/"/g, '""') : '') + '"') ) .map(row => row.join(',')) .join('\n'); } arrToCSV(arr); // -> ""ご利用年月日","ご利用""箇所","ご,利,用,額" // "2017/01/29","","""""345" // "2017/02/01","""ＡＭ""ＡＺＯＮ．ＣＯ．ＪＰ","7,362""
参考
ダウンロード
以下どれかを利用
- msSaveBlob method - Windows app development
- a 要素 - HTML | MDN のdownload属性
- Data URI scheme - Wikipedia
msSaveBlob
- IE (10以上) で動く
const data = arrToCSV(arr); function download(data, name) { if (window.navigator.msSaveBlob) { // utf8 const bom = '\uFEFF'; const blob = new Blob([bom, data], { type: 'text/csv' }); window.navigator.msSaveBlob(blob, name); } } download(data, 'filename.csv');
download属性
const data = arrToCSV(arr); function download(data, name) { const anchor: any = document.createElement('a'); if (window.URL && anchor.download !== undefined) { // utf8 const bom = '\uFEFF'; const blob = new Blob([bom, data], { type: 'text/csv' }); anchor.download = name; // window.URL.createObjectURLを利用 // https://developer.mozilla.org/ja/docs/Web/API/URL/createObjectURL anchor.href = window.URL.createObjectURL(blob); // これでも可 // anchor.href = 'data:text/csv;charset=utf-8,' + encodeURIComponent(bom + data); // firefoxでは一度addしないと動かない document.body.appendChild(anchor); anchor.click(); anchor.parentNode.removeChild(anchor); } } download(data, 'filename.csv');
Data URI scheme + data:attachment/…
- 主にSafari対応
- ファイル名を設定できない
- 動作確認あんまりできていない
const data = arrToCSV(arr); function download(data) { // utf8 const bom = '\uFEFF'; window.location.href = 'data:attachment/csv;charset=utf-8,' + encodeURIComponent(bom + data); } download(data);
以下の方法でもできる（メモ）
// string to base64 function download_base64(data) { const bom = '\uFEFF'; window.location.href = 'data:attachment/csv;charset=utf-8;base64,' + btoa(unescape(encodeURIComponent(bom + data))); } // blob to dataurl function download_filereader(data) { const bom = '\uFEFF'; const reader = new window.FileReader(); const blob = new Blob([bom, data], { type: 'attachment/csv' }); reader.readAsDataURL(blob); reader.onloadend = () => { window.location.href = reader.result; } }
