2015年3月30日月曜日

HAProxyを監視するMuninプラグイン

Danny Choo Follow Sato Dai  Ergo Proxy - another title that Sato-san worked on https://www.flickr.com/photos/dannychoo/8147019784
HAProxyを監視するMuninプラグインです。

contrib/plugins/haproxy at master · munin-monitoring/contrib
https://github.com/munin-monitoring/contrib/tree/master/plugins/haproxy


アクティブ・非アクティブなサーバ、トラフィック、セッション、エラーなど広範囲なステータスをメトリクス化できます。すべての監視項目を有効化すると31項目です。

以下、セットアップ方法です。

HAProxyのステータスを取得できるようにしておきます。
今回は、バックエンドにMySQLを置く設定です。
# cat /etc/haproxy/haproxy.cfg
#---------------------------------------------------------------------
# round robin balancing between the various backends
#---------------------------------------------------------------------
listen mysql
    bind 0.0.0.0:3307
    mode tcp
    option mysql-check user haproxy
    balance roundrobin
    server mysql1 127.0.0.1:3306 check port 3306 inter 10s rise 2 fall 5
    server mysql2 127.0.0.1:3306 check port 3306 inter 10s rise 2 fall 5

#---------------------------------------------------------------------
# stats
#---------------------------------------------------------------------
listen stats 127.0.0.1:1919
    mode http
    stats uri /haproxy-status
ソースをgit cloneで取得
# git clone https://github.com/munin-monitoring/contrib.git
Muninプラグインのディレクトリにコピー
# cp contrib/plugins/haproxy/* /usr/share/munin/plugins/
設定ファイルを記述
env.backendと、env.frontendには、HAProxyで設定しているlistenの値を入れます
# emacs /etc/munin/plugin-conf.d/haproxy
[haproxy*]
user root
env.backend mysql
env.frontend mysql
env.url http://127.0.0.1:1919/haproxy-status?stats;csv
利用可能なHAProxyプラグインを表示
# munin-node-configure -suggest | grep haproxy
HAProxyプラグインのリンクを貼る
# munin-node-configure --shell  | grep haproxy | sh
munin-nodeをリスタート
# service munin-node restart

うまくグラフが表示されたでしょうか?

2015年3月27日金曜日

絶対にロックしたくないMySQLマスターからレプリケーション構築 innobackupex編

brett jordan Follow Slave or Free?  BLAZE 'Slave or Free?' Houseparty, 1993
mysqldumpのダンプデータを使って、レプリケーション用のダンプファイルを作るとします。このとき、mysqldump に --master-data を付与すると "FLUSH TABLES WITH READ LOCK" が発行され、一瞬のあいだ、全テーブルがロックします。
mysqldump -uroot -pxxxxxxxx --single-transaction --master-data=2 --all-databases | gzip >  20150327.sql.gz

2095627 Query FLUSH TABLES WITH READ LOCK
2095627 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2095627 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2095627 Query SHOW MASTER STATUS
2095627 Query UNLOCK TABLES
上記のような状況を回避して、MySQLマスターをロックせずレプリケーションを構築するためにXtraBackupを利用します。 
通常、innobackupexを使ってバックアップをするとロックを発行するのですが、 --no-lockで回避します。
 --no-lock オプションを付与すると、binlogのポジションが記述されるはずの xtrabackup_binlog_info ファイルが作成されません。
しかし、--apply-logコマンドでリカバリ用のファイルを作成すると、ログにbinlogのポジションが出力されます。これを利用してレプリケーションを構築できます。 

以下、作業ログです。

MySQLマスターサーバ

XtraBackupをインストール
# rpm -Uhv http://www.percona.com/redir/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
# yum -y install xtrabackup
バックアップディレクトリ作成
# mkdir -p /tmp/xtrabackup/
バックアップ実行 --no-lockオプションを付与する
# time innobackupex --user root --password xxxxxxxx /tmp/xtrabackup/ --no-lock
フォルダをスレーブサーバへscp(転送制限を5MB/sに設定)
# scp -r -l 40000 /tmp/xtrabackup/2015-03-25_09-31-50 username@100.100.100.100:/tmp/


MySQLスレーブサーバ

XtraBackupをインストール
# rpm -Uhv http://www.percona.com/redir/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
# yum -y install xtrabackup
MySQLリストア用にデータディレクトリを空にしておく
# /etc/init.d/mysqld stop
# mv /var/lib/mysql /var/lib/mysql.old
# mkdir /var/lib/mysql
バックアップの準備とポジションの確認
$ innobackupex --apply-log /tmp/2015-03-25_09-31-50
[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 431897511, file name mysql-bin.000009
binlogのポジションは、431897511
binlogファイルは、mysql-bin.000009

リストア実行
$ innobackupex --copy-back /tmp/2015-03-25_09-31-50
$ chown -R mysql:mysql /var/lib/mysql
# /etc/init.d/mysqld start 

あとは、 先ほど確認したbinlogのポジションを利用して "CHANGE MASTER TO" コマンドでレプリケーションをスタートさせればOKです。
ちゃんと、レプリケーションされているか確認してみてください。

参考資料)
Percona xtrabackup - MySQL Meetup @ Mumbai
http://www.slideshare.net/nilnandan/percona-xtrabackup-mysql-meetup-mumbai/48
Working with Binary Logs — Percona XtraBackup Documentation
http://www.percona.com/doc/percona-xtrabackup/2.1/xtrabackup_bin/working_with_binary_logs.html
漢(オトコ)のコンピュータ道: MySQLバックアップ頂上決戦!! LVMスナップショット vs InnoDB Hot Backup
http://nippondanji.blogspot.jp/2009/12/mysql-lvm-vs-innodb-hot-backup.html

2015年3月23日月曜日

Cyberduckを使ってSFTPでサーバに接続する Mac編

MiNe MiNe-5DII_103-9570UG https://www.flickr.com/photos/sfmine79/10468588284

たまに、「FTPアカウントをください」といった依頼を受けることがあるのですが。さすがにFTPは使ってほしくないので、SFTPで接続していただいています。
SSH鍵の生成などは慣れていると簡単ですが。初めての人には若干ハードルが高かったりするので。そのときに、このドキュメントを渡して理解してくれたらいいなと考えて書きました。

FTPではなくて、SFTP?

FTPは危険です。FTPはそのプロトコルの仕組みから、クライアントとサーバが通信する際にパスワードが暗号化されません。
つまり、通信の盗聴にとても弱いと言えます。たとえば、
『スターバックスで Free Wi-Fi に繋ごうとしたら、似たような名前の Wi-Fi があったので接続した。
しかし、それは、盗聴用の Wi-Fi で通信が盗聴され、Webサイトが改ざんされた』
といったように、公衆無線LANのSSID詐称で大きな被害を受けたり、リスクが色々考えられます。
そのため、SSH鍵交換方式による認証を利用したSFTP(SSH File Transfer Protocol)を使って通信内容を暗号化することが必須となります。

SFTPとは?
http://ja.wikipedia.org/wiki/SSH_File_Transfer_Protocol
SSH File Transfer Protocol(略称:SFTP)とは、SSHの仕組みを使用しコンピューター間でファイルを安全に転送するプロトコルである。
sftpは、このプロトコルを利用するコマンドの名前である。
以下、SSH公開鍵と秘密鍵を作成して、SFTPをサポートしたソフト Cyberduck を使ってサーバに接続してみます。

SSH公開鍵と秘密鍵を作成

ターミナルを開きます。
Launchpad -> Other -> Terminal

ターミナルを開いたら、コマンドを入力してSSH公開鍵を作成します。
いくつか設定入力が求められますが、何も入力せずEnterを3回叩くだけでもOKです。
ポリシーによっては、パスフレーズを入力するとよいでしょう。
$ ssh-keygen
Enter file in which to save the key (/Users/yako/.ssh/id_rsa): ファイルを作成する場所は?
Enter passphrase (empty for no passphrase): パスフレーズは?
Enter same passphrase again: 同じパスフレーズを入力

このような記号入りの図が表示されたら、SSH公開鍵の作成が完了しています。
[ RSA 2048]となっているのを確認します。
これは、SSH鍵の種類がRSAで2048bit鍵であることを表しています。もし、1024bitだったら鍵の長さが十分ではありません。鍵の長さを指定して作成しなおしましょう。

SSH公開鍵の内容を確認します。
$ cat $HOME/.ssh/id_rsa.pub
ずらずらっとテキスト文字列が出てきます。
次のコマンドでSSH公開鍵のテキストがクリップボードにコピーされます。
$ pbcopy < ~/.ssh/id_rsa.pub
このテキスト情報をサーバ管理者に渡してサーバアカウントを作ってもらいましょう。
※ 同時に秘密鍵($HOME/.ssh/id_rsa)が作成されています。このファイルは絶対に誰かに渡してはダメです。

Cyberduckでサーバに接続

サーバアカウントができたら、接続してみましょう。
https://cyberduck.io/
上記サイトからCyberduckをダウンロードします。
最新バージョンは、4.6.5でした。

ソフトを起動したら。左上の [Open Connection] を開きます。
サーバへの接続情報を、入力していきます。
プルダウンから [SFTP (SSH File Transfer Protocol)] を選択
Server: サーバのIPアドレス
Username: ユーザ名
Password: パスフレーズを入力(SSH公開鍵を作成したときに、パスフレーズを設定しなかった時は空のまま)
一番下の [Use Public Key Authentication] をチェックして、SSH秘密鍵を選択 [Choose] します。
[Connect]を選択してサーバに接続します。
最初の接続では、fingerprint (SSHサーバーの公開鍵の指紋)が正しいか聞かれます。
[Allow] をクリックして接続します。
ちゃんと接続できたでしょうか?

セキュアにサーバへ接続する事ができるようになりました。

2015年3月18日水曜日

動画変換ソフトFFmpegをCentOS6にインストール

動画や音声をエンコードしてくれるソフトである FFmpeg(https://www.ffmpeg.org/)をCentOS6で扱えるようにします。
ついでに、ImageMagickも使ってアニメーションGIFを作ってみます。

この動画を。

アニメーションGIFに変換します。



動画ファイル -> 1コマづつのPNGファイル生成 -> アニメーションGIF
という流れです。以下、作業ログです。

yumライブラリをインストール
# yum -y install autoconf automake make gcc gcc-c++ pkgconfig wget libtool zlib-devel
# yum -y install git
# yum -y install http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
# yum -y install --enablerepo=epel yasm
x264 をインストール
# cd /usr/local/src
# git clone git://git.videolan.org/x264
# cd x264
# ./configure --enable-shared
# make
# make install
fdk-aac をインストール
# cd /usr/local/src
# git clone --depth 1 git://github.com/mstorsjo/fdk-aac.git
# cd fdk-aac
# autoreconf -fiv
# ./configure
# make
# make install
ライブラリパスを設定
$ export LD_LIBRARY_PATH=/usr/local/lib/
$ echo /usr/local/lib >> /etc/ld.so.conf.d/custom-libs.conf
$ ldconfig
ffmpeg のインストール
# cd /usr/local/src
# git clone git://source.ffmpeg.org/ffmpeg.git ffmpeg
# cd ffmpeg
# ./configure --enable-gpl --enable-nonfree --enable-libfdk_aac --enable-libx264 --enable-shared --arch=x86_64 --enable-pthreads
# make
# make install
# ffmpeg -version
ffmpeg version N-70803-g0f16dfd Copyright (c) 2000-2015 the FFmpeg developers
built with gcc 4.4.7 (GCC) 20120313 (Red Hat 4.4.7-11)
configuration: --enable-gpl --enable-nonfree --enable-libfdk_aac --enable-libx264 --enable-shared --arch=x86_64 --enable-pthreads
libavutil      54. 20.100 / 54. 20.100
libavcodec     56. 28.100 / 56. 28.100
libavformat    56. 25.101 / 56. 25.101
libavdevice    56.  4.100 / 56.  4.100
libavfilter     5. 12.100 /  5. 12.100
libswscale      3.  1.101 /  3.  1.101
libswresample   1.  1.100 /  1.  1.100
libpostproc    53.  3.100 / 53.  3.100
ffmpegの動作確認として、動画サイズを変更するようにエンコードを実行してみます。
# ffmpeg -i /tmp/sample.mp4 -s 200x150 \
  -vcodec libx264 -profile:v baseline -preset:v slower \
  -b:v 300k -maxrate 300k -minrate 300k -bufsize 1000k \
  -ac 2 -c:a libfdk_aac -b:a 64k /tmp/encoded_sample.mp4
ffmpegがエンコードしたファイルを確認してみてください。指定したとおりにエンコードされているでしょうか?

ImageMagickをインストール
# yum -y install ImageMagick ImageMagick-devel

やっと準備が出来ました。
アニメーションGIFを作ってみましょう。

動画ファイルをPNGファイルに変換
# ffmpeg -i /tmp/sample.mp4 -an -r 15 -s 160x90 /tmp/%05d.png
PNGファイルをアニメーションGIFに変換
# convert /tmp/*.png sample.gif
うまく変換できたでしょうか?
FFmpeg + ImageMagickさえインストールしてしまえば、2コマンドで処理が済みました。かなりお手軽な感じがしていいですね。

FFmpegは、ビルド済みのバイナリファイルが公開されているので、こちらを使っても良いとおもいます。
FFmpeg Static Builds
https://johnvansickle.com/ffmpeg/

参考資料

CompilationGuide/Centos – FFmpeg
https://trac.ffmpeg.org/wiki/CompilationGuide/Centos
Encode/H.264 – FFmpeg
https://trac.ffmpeg.org/wiki/Encode/H.264
CentOS6.xにFFmpegをインストールする - Code Life
http://blog.code-life.net/blog/2013/04/14/how-to-install-ffmpeg-on-centos6-x86-64/
どーもくん ギターを弾きまくる 白バック素材:素材をさがす:NHKクリエイティブ・ライブラリー
http://www1.nhk.or.jp/creative/material/dd/D0002040236_00000.html

Top image from "NASA's James Webb Space Telescope Light Fantastic: Laser at Inner Harbor Beams Hubble's Heartbeat".

2015年3月16日月曜日

ネストされたJSONデータをFluentdでGoogle BigQueryに投入してクエリを実行する


ネストされたJSONデータ(https://cloud.google.com/bigquery/docs/personsData.json)を、fluentdを使ってGoogle BigQueryに投入します。
BigQueryは、こういった構造化されたデータに対応する WITHIN、FLATTEN といったSQL関数が準備されています。
これらを利用したSQLクエリを構築してデータを取り出してみます。



プロジェクトを作成
https://console.developers.google.com/project
Google Developers Consoleを開き、プロジェクトを作成して、『プロジェクト ID』『メールアドレス』をメモしておいてください。
また、P12キーファイルを保存しておいてください。
認証で必要になります。

サーバ
AMI: CentOS 6 (x86_64) - with Updates HVM
Amazon Linuxは、td-agentが非サポートのため。CentOSを選びました。
Treasure AgentとAmazon Linux(http://qiita.com/repeatedly/items/a07f34c1be7f7309b521
追記:td-agent - Treasure Agent 2.2.0のリリース - Qiita『Amazon Linuxのサポート』(http://qiita.com/repeatedly/items/5904e81e1856e0671684

以下、サーバでの作業です。

Google Cloud SDKをインストール
Google Cloud SDKのために、Pythonのバージョンを2.7にアップしておきます。
# yum -y install gcc zlib-devel openssl-devel
# curl -O https://www.python.org/ftp/python/2.7.9/Python-2.7.9.tgz
# tar zxvf Python-2.7.9.tgz
# cd Python-2.7.9
# ./configure --prefix=/usr/local
# make
# make install
# /usr/local/bin/python --version
Python 2.7.9
Google Cloud SDKをインストール。
$ curl https://sdk.cloud.google.com | bash
$ source ~/.bashrc
$ gcloud components list
$ gcloud auth login --no-launch-browser
Go to the following link in your browser:

    https://accounts.google.com/o/oauth2/auth?xxxxxx

Enter verification code:
URLが表示されるので、それを手元のブラウザで開く。
許可リクエストを承認すると、認証用コードが表示されるので、ターミナルに貼り付けて認証します。

認証を行ったアカウントを確認。
$ gcloud auth list
BigQuery用のコマンドが有効になっているか確認。
$ bq
既存プロジェクトを確認。プロジェクト名は、my-project-id です。
$ bq ls
       projectId          friendlyName
 --------------------- ------------------
  my-project-id   My First Project

データセットを作成
データセットとは、MySQLに例えると、データベースのようなものです。
BigQueryコンソール(https://bigquery.cloud.google.com/)から作成できます。My First Project -> プルダウン -> Create new dataset
コマンドラインからデータセットを作成する場合は以下のようにします。
データセット名は、”my_dataset”です。
# bq mk your-project-id:my_dataset

テーブルを作成
テーブルのスキーマファイルを用意します。
こちらのサンプルを利用します。
Nested and Repeated Data(https://cloud.google.com/bigquery/docs/data#nested
# curl -o /tmp/personsDataSchema.json  https://cloud.google.com/bigquery/docs/personsDataSchema.json
テーブルを作成。テーブル名は、 my_table です。
$ bq mk -t my-project-id:my_dataset.my_table ./personsDataSchema.json
Table 'my-project-id:my_dataset.my_table' successfully created.
テーブルの情報を表示。
$ bq --project_id my-project-id show my_dataset.my_table
Table my-project-id:my_dataset.my_table

   Last modified                   Schema                  Total Rows   Total Bytes   Expiration
 ----------------- -------------------------------------- ------------ ------------- ------------
  16 Mar 14:33:01   |- kind: string
                    |- fullName: string (required)
                    |- age: integer
                    |- gender: string
                    +- phoneNumber: record
                    |  |- areaCode: integer
                    |  |- number: integer
                    +- children: record (repeated)
                    |  |- name: string
                    |  |- gender: string
                    |  |- age: integer
                    +- citiesLived: record (repeated)
                    |  |- place: string
                    |  |- yearsLived: integer (repeated)
BigQueryコンソールから確認してみましょう。


fluentdをインストール
td-agent2をインストール。
# curl -L http://toolbelt.treasuredata.com/sh/install-redhat-td-agent2.sh | sh
fluent-plugin-bigqueryプラグインをインストール。
# td-agent-gem install fluent-plugin-bigquery
td-agentの設定ファイルを編集します。
データセット名、テーブル名には、先ほど作った名前を設定します。
Google Developers Console(https://console.developers.google.com/project)に認証情報があるので、それをコピーして利用します。
プロジェクトを選択 -> APIと認証 -> 認証情報
emailにメールアドレスを、private_key_pathにダウンロードしたP12キーファイルのパスを設定します。
# emacs /etc/td-agent/td-agent.conf
<source>
  type tail
  format json
  path /tmp/fluentd_data_source.json
  tag tail.json
</source>

<match tail.json>
  type bigquery
  method insert
  auth_method private_key
  email メールアドレス
  private_key_path P12キーファイルのパス
  project my-project-id
  dataset my_dataset
  table my_table
  time_format %s
  time_field time
  schema_path /tmp/personsDataSchema.json
  flush_interval 1s
  try_flush_interval 0.05
  queued_chunk_flush_interval 0.01
  buffer_chunk_limit 512k
  buffer_chunk_records_limit 250
  buffer_queue_limit 1024
  retry_limit 5
  retry_wait 1s
  num_threads 10
</match>

td-agentをスタート。
# /etc/init.d/td-agent start
# cat /var/log/td-agent/td-agent.log

サンプルデータをBigQueryに投入
サンプルデータをダウンロード。
# curl -o /tmp/personsData.json  https://cloud.google.com/bigquery/docs/personsData.json
サンプルデータをfluentdに投入。fluentdは、すぐにデータをBigQueryに転送するはずです。
# cat personsData.json >> /tmp/fluentd_data_source.json

BigQueryにデータは投入されている?
データは投入されているでしょうか?
『Details』タブから確認できます。


データが投入されていることがわかりました。

SQLを発行してデータを取得
SQLを発行してデータを取得してみます。
SELECT * FROM [my_dataset.my_table] LIMIT 1000
Query Failed
Error: Cannot output multiple independently repeated fields at the same time. Found children_age and citiesLived_place
ワイルドカードを使うと、複数の繰り返しのあるフィールドにアクセスすることになるため。エラーになってしまいます。 代わりに、以下ようにフィールドを指定してクエリを実行します。
SELECT
  fullName AS name,
  age,
  gender,
  citiesLived.place,
  citiesLived.yearsLived
FROM [my_dataset.my_table]


コマンドラインからも同じ結果を取得できます。
# bq query --project_id my-project-id "SELECT fullName AS name, age, gender, citiesLived.place, citiesLived.yearsLived FROM [my_dataset.my_table]"
自動的にネストされたデータがフラット化されて取得できたことがわかります。

FLATTENについて
明示的に、複数の繰り返しフィールドを扱うときには、FLATTENを使う必要があります。
例えば、次のクエリを実行します。
SELECT fullName, age
FROM [my_dataset.my_table]
WHERE
  (citiesLived.yearsLived > 1995 ) AND
  (children.age > 3)

BigQuery error in query operation: Error processing job 'xxxx:bqjob_xxxxx': Cannot query the
cross product of repeated fields children.age and citiesLived.yearsLived.
エラーになります。
複数の繰り返しフィールド全体に問い合せるには、フィールドのいずれかをフラット化する必要があります。
この例では、childrenをフラットにしています。
SELECT
  fullName,
  age,
  gender,
  citiesLived.place
FROM (FLATTEN([my_dataset.my_table], children))
WHERE
  (citiesLived.yearsLived > 1995) AND
  (children.age > 3)
GROUP BY fullName, age, gender, citiesLived.place
+------------+-----+--------+-------------------+
|  fullName  | age | gender | citiesLived_place |
+------------+-----+--------+-------------------+
| John Doe   |  22 | Male   | Stockholm         |
| Mike Jones |  35 | Male   | Los Angeles       |
| Mike Jones |  35 | Male   | Washington DC     |
| Mike Jones |  35 | Male   | Portland          |
| Mike Jones |  35 | Male   | Austin            |
+------------+-----+--------+-------------------+

ほしい情報を出力することが出来ました。

WITHINについて
WITHINは、ネストされたフィールド内の繰り返しフィールドを集計するときに役立ちます。
以下の2つの使用方法があります。
WITHIN RECORD: レコード内の繰り返しの集合体データ。
WITHIN node_name: レコード内の親ノードで指定した、繰り返しの集合体データ。

例えば、それぞれの子供の数をカウントする場合には、WITHIN RECORD使って、レコード内の繰り返しの集合体データを計算します。
SELECT
  fullName,
  COUNT(children.name) WITHIN RECORD AS numberOfChildren
FROM [my_dataset.my_table]
+---------------+------------------+
|   fullName    | numberOfChildren |
+---------------+------------------+
| Anna Karenina |                0 |
| John Doe      |                2 |
| Mike Jones    |                3 |
+---------------+------------------+
全ての子供のリストです。上記の結果が間違いないことがわかります。
SELECT
  fullName, children.name
FROM [my_dataset.my_table]
+---------------+---------------+
|   fullName    | children_name |
+---------------+---------------+
| Anna Karenina | NULL          |
| John Doe      | Jane          |
| John Doe      | John          |
| Mike Jones    | Earl          |
| Mike Jones    | Sam           |
| Mike Jones    | Kit           |
+---------------+---------------+

別のSQLを発行してみましょう。
citiesLived.place対してWITHIN RECORDを実行して、それぞれの人が住んでいる街の数をカウントします。
citiesLived.yearsLived対してWITHINを実行して、それぞれの人がにその街に住んでいた年をカウント(citiesLivedの数)します。WITHINで親ノードを指定して、繰り返しの集合体データを計算しています。
SELECT
  fullName,
  COUNT(citiesLived.place) WITHIN RECORD AS numberOfPlacesLived,
  citiesLived.place,
  COUNT(citiesLived.yearsLived) WITHIN citiesLived AS numberOfTimesInEachCity,
FROM [my_dataset.my_table]
+---------------+---------------------+-------------------+-------------------------+
|   fullName    | numberOfPlacesLived | citiesLived_place | numberOfTimesInEachCity |
+---------------+---------------------+-------------------+-------------------------+
| Anna Karenina |                   3 | Stockholm         |                       4 |
| Anna Karenina |                   3 | Russia            |                       3 |
| Anna Karenina |                   3 | Austin            |                       2 |
| John Doe      |                   2 | Seattle           |                       1 |
| John Doe      |                   2 | Stockholm         |                       1 |
| Mike Jones    |                   4 | Los Angeles       |                       4 |
| Mike Jones    |                   4 | Washington DC     |                       4 |
| Mike Jones    |                   4 | Portland          |                       4 |
| Mike Jones    |                   4 | Austin            |                       4 |
+---------------+---------------------+-------------------+-------------------------+

まとめ。

ネストされたJSONデータをBigQueryにインポートして、データを取得するようにしました。
また、FLATTEN関数、WITHIN関数を利用してシンプルに計算をすることができました。

繰り返しやネストされたJSONデータが扱えるようになったことで、データのや計算方法の幅が広くなったと思います。
しかしながら、こういったデータを扱うとすれば、BigQueryは、RDBとは別物であることを念頭にデータの持ち方を考える必要があるので、慎重な設計と十分にテストをする必要があるでしょう。
Query Reference(https://cloud.google.com/bigquery/query-reference
ほかにも、使えそうな関数がありますので、試してみたいところです。

Fluentdのように連続してデータをBigQueryにインポートする場合は、リトライ時に重複データが投入されることがあります。それを防ぐためにインポートするデータに insertId を付けることを推奨されています。fluent-plugin-bigqueryでも insertId がサポートされているので導入は難しくありません。(https://github.com/kaizenplatform/fluent-plugin-bigquery#specifying-insertid-property
ほかにも、いくつか制限がありますのでドキュメントを熟読すると良いでしょう。

Streaming Data Into BigQuery - Google BigQuery — Google Cloud Platform(https://cloud.google.com/bigquery/streaming-data-into-bigquery