Help us understand the problem. What is going on with this article?

BigQuery をスキーマレスっぽく運用する方法

More than 3 years have passed since last update.

3行でまとめ

  • 1つの列に JSON 文字列を突っ込む
  • JSON functions を使って、必要な値を取り出す
  • 要するに RDB の JSON 型みたいな感じで運用しようということ。

どういう時に使うの?

「1時間後からログ分析するから」とぶっこまれた時。当然、スキーマは決まっていない。あとは、使い捨てのアドホックな分析とか簡易ETLツールとして使うと便利だと思う。

なお、この方法はコストもかかるし、速くもない、実際は BigQuery なので速いけど、相対的には速くないので、甘えずにスキーマはちゃんと決めるようにしよう。

手順

スキーマを準備

$ bq mk test.json_test time:timestamp.message:string

スクリーンショット 2015-02-13 22.27.24.png

データを投入

こんな感じで、message 列に JSON を投入。データ投入スクリプトは記事の最後を参照。

スクリーンショット 2015-02-13 22.28.53.png

JSON functions を使って JSON を展開する

JSON functions は JSON path が使えるので、柔軟に展開できる。
書式は JSON_EXTRACT_SCALAR(json, json_path)

SELECT
  time,
  message,
  JSON_EXTRACT_SCALAR(message, "$.a") AS a,
  JSON_EXTRACT_SCALAR(message, "$.b") AS b,
  JSON_EXTRACT_SCALAR(message, "$.c") AS c
FROM
  [test.json_test]

スクリーンショット 2015-02-13 22.33.05.png

無事に展開できてますね。本格的に分析したくなってきたら、スキーマを決めて、それに合わせて展開したクエリ結果をテーブルに保存するだけでOK。簡易ETLツールとしても使えますね。

実験に使ったソース

ダミーデータ生成

generate.js

for (var i = 0; i < 10000; ++i) {
  var t = Math.ceil(Date.now() / 1000);
  var obj = {
    a: t,
    b: "val" + i
  }
  if (i % 5 == 0) {
    obj.c = "option" + i
  }
  var log = { time: t, message: JSON.stringify(obj) };
  console.log(JSON.stringify(log));
}

ロード用スクリプト

load.sh

#!/bin/bash -e

TABLE=test.json_test
INPUT_FILE=input.json
node generate.js > $INPUT_FILE

bq rm $TABLE
sleep 5s
bq mk $TABLE time:timestamp,message:string
bq insert $TABLE $INPUT_FILE
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away