BigQuery+Dataportalでインタラクティブフィルタを活用する

プライベートでブログのPVなどを集計していて

BigQueryにTwitterのエゴサやGoogleAnalyticsのPVなどを取り込んで、Dataportalから参照してグラフにして定期的に見るようにしている

記事についてのデータ(urlやタグ)はjsonで持っていてこちらもBigQueryに定期的に同期してそれを用いてこの記事がいつ書かれてPVはこんな感じ

といったようなことをさっと把握できるようにしていた

記事ごとに集計していたがせっかく「タグ」の情報も持っているのでタグごとにも集計結果を見れるようインタラクティブフィルタをうまく使いたいと思っていた

色々調べてみるとどうやらDataportalのフィルタの制約をクリアすれば実現できそうということでやってみた

イメージ

f:id:swfz:20210618092920g:plain

インタラクティブフィルタについて

インタラクティブフィルタは基本的には同じデータソース間で適用される

しかし次のドキュメントを読むと

データソース間でコントロールを使用する - データポータルのヘルプ

support.google.com

フィルタ処理は、データソース内に表示されているフィールド名ではなく、内部的なフィールド ID をもとに行われているためです

ということで、適当に名前を合わせたフィールドを作成してもフィルタは適用されない

が、内部的なフィールドIDが同じであればデータソースが異なっていてもインタラクティブフィルタが適用できる

共通するフィールドIDをarticle.idにして各種数値データはarticleとレポートデータを混合データにして扱うことで同じIDを扱えるようになり、異なるデータソース間でもインタラクティブフィルタを適用できるはず

f:id:swfz:20210618092934p:plain

ということで実践してみる

サンプルデータ投入

sample-report.json

{"dt":"2021-06-15","id":1,"title":"hoge","view":1}
{"dt":"2021-06-15","id":2,"title":"fuga","view":1}
{"dt":"2021-06-15","id":3,"title":"piyo","view":1}
{"dt":"2021-06-15","id":4,"title":"foo","view":1}
{"dt":"2021-06-15","id":5,"title":"bar","view":1}
{"dt":"2021-06-16","id":1,"title":"hoge","view":2}
{"dt":"2021-06-16","id":2,"title":"fuga","view":1}
{"dt":"2021-06-16","id":3,"title":"piyo","view":1}
{"dt":"2021-06-16","id":4,"title":"foo","view":1}
{"dt":"2021-06-16","id":5,"title":"bar","view":1}
{"dt":"2021-06-17","id":1,"title":"hoge","view":2}
{"dt":"2021-06-17","id":2,"title":"fuga","view":1}
{"dt":"2021-06-17","id":3,"title":"piyo","view":2}
{"dt":"2021-06-17","id":4,"title":"foo","view":1}
{"dt":"2021-06-17","id":5,"title":"bar","view":3}

sample-summary.json

{"id":1,"title":"hoge","view":5}
{"id":2,"title":"fuga","view":3}
{"id":3,"title":"piyo","view":4}
{"id":4,"title":"foo","view":3}
{"id":5,"title":"bar","view":5}

article.json

{"id":1, "title": "hoge", "tags": ["A","B"]}
{"id":2, "title": "fuga", "tags": ["C","D"]}
{"id":3, "title": "piyo", "tags": ["A"]}
{"id":4, "title": "foo",  "tags": ["C"]}
{"id":5, "title": "bar", "tags": ["E"]}

投入

bq load --autodetect --replace --source_format=NEWLINE_DELIMITED_JSON sample.report sample-report.json
bq load --autodetect --replace --source_format=NEWLINE_DELIMITED_JSON sample.summary sample-summary.json
bq load --autodetect --replace --source_format=NEWLINE_DELIMITED_JSON sample.article article.json

スキーマの確認

$ bq show --schema --format=json sample.article
[{"name":"tags","type":"STRING","mode":"REPEATED"},{"name":"title","type":"STRING","mode":"NULLABLE"},{"name":"id","type":"INTEGER","mode":"NULLABLE"}]
$ bq show --schema --format=json sample.summary
[{"name":"view","type":"INTEGER","mode":"NULLABLE"},{"name":"title","type":"STRING","mode":"NULLABLE"},{"name":"id","type":"INTEGER","mode":"NULLABLE"}]
$ bq show --schema --format=json sample.report
[{"name":"view","type":"INTEGER","mode":"NULLABLE"},{"name":"id","type":"INTEGER","mode":"NULLABLE"},{"name":"title","type":"STRING","mode":"NULLABLE"},{"name":"dt","type":"DATE","mode":"NULLABLE","description":"%E4Y-%m-%d"}]

それぞれidをもとにひも付けていく前提の中身となっている