Cloud StorageからJSONをBigQueryに注入する

#Overview

BigQueryを検索エンジンに採用し、JSONデータを投入した際のメモです。
BigQueryを知らない方向けなので、それ以外の方には不要な記事です。

#Target reader

- BigQueryにJSONデータ投入を検討している方
- 検索エンジンとしてBigQueryを検討している方

#Prerequisite

- Cloud Functions for Firebaseを使える
- Cloud Storageを使える

#Body
##BigQueryを採用した理由

検索エンジンを採用するにあたり、次のことを条件としました。
- テキストの部分一致検索が可能
- トランザクションは不要
- データは10万未満
- 料金は完全従量制。つまりリザーブドインスタンスではなくサーバーレス。

以下の公式ドキュメントを基に検討しました。
https://cloud.google.com/bigtable/docs/overview?hl=ja#other-storage-options
https://cloud.google.com/products/databases/?hl=ja

Cloud Firestoreという選択肢もありましたが、部分一致検索がサポートされていないためBigQueryにしました。
（よく聞くので使いたいという欲求も少しあり）
次に料金を見ておきます。
https://cloud.google.com/bigquery/pricing?hl=ja

1TBあたり$6は正直高い。
しかし、毎月1TBの無料枠があれば大丈夫だと思っていました…そう実際にクエリーを発行するまでは…:fearful:

>クエリ料金について次の点に注意してください。
>・料金は MB 単位のデータ処理容量（端数は四捨五入）で決まります。クエリが参照するテーブルあたりのデータ最小処理容量は 10 MB、クエリあたりのデータ最小処理容量は 10 MB とします。

そう、取得データがどんなに小さくとも10MBは消費してしまうのです。
つまり、無料枠1TBは最高でも10万回のリクエストまでです。
充分と思われるかもしれませんが、1日当たり3333回です。
1日1000人のユーザーが訪れるサイトでは、一人当たり3回までしか無料ではありません:sob:
ユーザー一人当たりいくら売り上げがあり、BigQueryがそれの何％に該当するかは計算しておいたほうがいいでしょう。

##データを投入するもJSONがJSONじゃない

公式ドキュメントに沿ってデータをとりあえず投入することにしました。
https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-json?hl=ja

…しかしエラーの連発:weary:
読み飛ばしていた制限事項を読んでみる。

>JSON データは改行区切りである必要があります。各 JSON オブジェクトはファイル内でそれぞれ別の行に配置されている必要があります。

何を言っているんだお前は？

>BigQuery では JSON のマップや辞書がサポートされません。たとえば、"product_categories": {"my_product": 40.0} は無効ですが "product_categories": {"column1": "my_product" , "column2": 40.0} は有効です。

何を言っているんだお前は？

さっぱりわからないorz

公式ドキュメントから[JSON Lines](http://jsonlines.org/examples/)の存在を知る。
そして、npmのパッケージから具体的なデータイメージを理解。
https://github.com/ndjson/ndjson-spec

>```json
> {"some":"thing"}
> {"foo":17,"bar":false,"quux":true}
> {"may":{"include":"nested","objects":["and","arrays"]}}
>```

このデータを見てBigQueryが行でデータを扱っていることを理解する。
どおりでCSVがドキュメントのインポート手段の最上位に来るわけだ。
つまり、JSONのようなデータ構造を行に変換しており、実は苦手なんだろう。
Firestoreにしようかと思ったが、扱うデータは単純なフラット構造なのでCSV変換可能、よってJSONで投入せずにCSVに変換する方針に変更。


##シンプルなJSONをCSVにする

CSVにするのはこのようなデータで、自分でもCSVに変換できなくはない。

```json
[
	{
		"id": "001",
		"text": "item1",
		"number": 1,
		"timestamp": 1576931549130
	},
	{
		"id": "002",
		"text": "item2",
		"number": 2,
		"timestamp": 1576931649130
	}
]
```

ただし、手間をかけたくないしバグも出したくないのでパッケージの採用を検討。
採用したのは[json2csv](https://www.npmjs.com/package/json2csv)。
採用理由は以下のようにコマンド実行できる点。
npxコマンドにより、ソース取り込む必要がなく、実行するたびに最新バージョンを取得するためアンマネージド！

```terminal

npx json2csv -i bigdata.json -o bigdata.csv
```

変換されたデータはこんな感じ。

```csv
"id","text","number","timestamp"
"001","item1",1,1576931549130
"002","item2",2,1576931649130
```

これを再度[公式ドキュメント](https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-csv?hl=ja)に従ってテーブルに読み込ませる。
（JSONを読み込ませる手順と特に変わりはない）

##クエリーの実行

Cloud ConsoleよりBigQueryを選択し、クエリーエディタに以下を入力する。
前提条件として、データセットをplayground、テーブルをitemsとしている。

```sql
select id, text, number, timestamp from playground.items
```

すると以下の結果として出力される。

```json
[
  {
    "id": "1",
    "text": "item1",
    "number": "1",
    "timestamp": "1576931549130"
  },
  {
    "id": "2",
    "text": "item2",
    "number": "2",
    "timestamp": "1576931649130"
  }
]
```

注意深く観察するとidが数値になってしまっているが、これは今は無視してOKとする。


##クライアントライブラリーよりクエリーを実行

クエリーが通ることを確認したので、今度はFunctionsよりクライアントライブラリを介してクエリーを実行する。
基本的には公式ドキュメントに沿えば問題なし。
https://cloud.google.com/bigquery/docs/quickstarts/quickstart-client-libraries?hl=ja#client-libraries-install-nodejs

ただし、心配性なあなたのために、私が作成したCloud Functions for Firebaseのくそソースを公開しておきます。
…いや、基本的にサンプルソースそのものなので前言撤回:sweat_smile:

```javascript:functions/index.js

const functions = require('firebase-functions');
// Import and initialize the Firebase Admin SDK.
const admin = require('firebase-admin');
admin.initializeApp();

// Import the Google Cloud client library using default credentials
const { BigQuery } = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

const regionTokyo = 'asia-northeast1';

exports.searchItems = functions.region(regionTokyo).https.onCall(async (data, context) => {
    const items = await query();
    return items;
});

async function query() {
    const query = `SELECT id, text, number timestamp FROM playground.items LIMIT 100`;

    // For all options, see https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query
    const options = {
        query,
        // Location must match that of the dataset(s) referenced in the query.
        location: "asia-northeast1",
        // maxResults: 100,
    };

    // Run the query as a job
    const [job] = await bigquery.createQueryJob(options);
    console.log(`Job ${job.id} started.`);

    // Wait for the query to finish
    const [rows] = await job.getQueryResults();

    // Print the results
    console.log('Rows:');
    rows.forEach(row => console.log(row));

    return rows;
}

```

プロジェクト関連を設定するコードがないが、これはFunctionsのデフォルト認証をつかうため。
当然FunctionsとBigQueryは同じプロジェクトの同じロケーションにあるものとする。
認証でわからない場合はこちら。
https://cloud.google.com/bigquery/docs/authentication/getting-started?hl=ja

#Conclusion

BigQueryを知らないまま進んでみたら、JSONを書き込むつもりがCSVに変換する羽目になりました。
また、当初クライアントから直接コールする予定でしたが、アクセストークンでの認証もメアドが出てしまうので、Functionsを介することにしました。
コストもサービスの収益力からすると割高といろいろありましたが、これでクライアントでデータの取得が可能になりました。

継続を考えると参照の項目に載せている「クラウドストレージの転送」が必要になるはず。
また、FirebaseのCloud Firestoreからデータをインポートするなら、Firebase Extensionsが用意されているようです。
https://firebase.google.com/products/extensions/firestore-bigquery-export/

最後で気が付いたのだけど、BigQueryはデータ分析用だからか、**同時処理件数が100件の模様**。
Databaseの比較表にいなかったのは、そういうことかと、分析する人間が同時に数千とかないもんね:joy:
勘違いなら…いやそれはないな…サイトが大きくなったら引っかかりそう…まずは大きくしてからにしようか:laughing:
https://cloud.google.com/bigquery/quotas?hl=ja#query_jobs

グダグダですがこれで締めます。
Have a great day!

#Appendices

#References

BigQueryの認証方法
https://cloud.google.com/bigquery/docs/authentication/?hl=ja
トークンはメアドも含まれていて、権限をReadOnlyにしたとしてもクライアントに出すには不向き
https://cloud.google.com/bigquery/docs/authorization?hl=ja


Cloud Functions For Firebasaeをローカルでテストする
https://firebase.google.com/docs/hosting/deploying?hl=ja

RealtimeDatabaseからBigQueryにデータをインポートするサンプルFunction
https://github.com/firebase/functions-samples/blob/master/bigquery-import/functions/index.js

Node.jsのBigQueryクライアントライブラリ
https://cloud.google.com/bigquery/docs/reference/libraries?hl=ja
https://github.com/googleapis/nodejs-bigquery


API リクエストの承認
https://cloud.google.com/bigquery/docs/authorization?hl=ja

Cloud Storage からのデータの読み込みの概要
https://cloud.google.com/bigquery/docs/loading-data-cloud-storage?hl=ja
Cloud Storage からの JSON データの読み込み
https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-json?hl=ja
クラウドストレージの転送
https://cloud.google.com/bigquery-transfer/docs/cloud-storage-transfer?hl=ja

Cloud Functios for FirebaseのCodelab
https://codelabs.developers.google.com/codelabs/firebase-cloud-functions/?hl=ja#0

コレクションをBigQueryにエクスポートする
https://firebase.google.com/products/extensions/firestore-bigquery-export/