小ネタです。色々教えてもらったりしたので、記憶が蒸発しないようにメモしておきます。教えてもらった同僚に感謝。
- BigQueryのUI上の配列を展開する
- Data Studioでデータソースのプロジェクトと課金プロジェクトを別のものを使う
- データ基盤の相談に乗る前にどういう利用の仕方をしている人なのか監査ログからざっくり把握する
- 社内のBigQueryのリソースの枯渇状況をData Studioで可視化する
BigQueryのUI上の配列を展開する
BigQueryのWeb UIが最近変更され、プレビューやクエリ結果に配列が含まれている場合は閉じた状態で表示されるようになりました。閉じた状態で表示されることで、より多くの行や列を画面内に表示することができるようになっています。
一方で、配列の要素が展開された状態で見たい場合もあります。以下のエントリでは、各パーセンタイルの値を配列として簡単に計算できるようにしていましたが、ぽちぽちとボタンを押して配列を展開していくのは正直面倒です。
そういうときはブックマークレットを書くと便利です。以下のようなブックマークレットを用意しておくと、開閉の状態をtoggleしてくれます。詳細に見ていきたいときとがっと概要を見ていきたいときをさっと切り替えることができるため、とても便利です。
javascript:(function () { document.querySelectorAll('td[role="cell"] button').forEach(node => { node.click(); } ) })();
ちなみに、配列の中に配列が含まれている場合には再帰的には展開しないので、それは読者への宿題とします(誰か頼んだ)。
Data Studioでデータソースのプロジェクトと課金プロジェクトを別のものを使う
こういうことをしたいとき結構あると思います。例えばこういうとき。
- でかいデータの処理は課金額が心配なのでFlat-Rateで実行してテーブルを生成する。テーブルは集約された統計値のみでデータ量的に十分小さくなっているため、Data Studioでこのテーブルを見るときはオンデマンドのプロジェクトを使いたい
- viewがFlat-Rateのプロジェクトに置いてあるが、速度の面でData Studioで見るときは見るときはオンデマンドのプロジェクトを使いたい
Data Studioのことをよく知らなかったので、こんな感じでしのいでいました。
- 見たいデータ自体はFlat-RateのGCPプロジェクトAにある
- カスタムクエリでプロジェクトAにあるデータを指定し、課金プロジェクトはオンデマンドのプロジェクトBを指定する
いちいちカスタムクエリで指定しないといけなくて嫌な感じがしていましたが、こんなことをする必要はないことを教えてもらいました。「共有プロジェクト」という概念があるらしく、それを使うと、データソースのプロジェクトと課金プロジェクトを別のものを使うことができました。助かった。
データ基盤の相談に乗る前にどういう利用の仕方をしている人なのか監査ログからざっくり把握する
データ基盤やってると色んな人から相談受けたりするわけだけど、監査ログを集計して相談される方が普段どのプロジェクトやデータセットを参照される方かぱっと分かるようにしておくと便利ということに気付いた
— Yasuhisa Yoshida (@syou6162) 2022年3月24日
BigQuery普段使ってない人なら厚めに前提から話したほうがよさそうだし、関連するデータセットとかも見えれば聞いといたほうがいい質問とかも相談の前に見えてくることも結構ある
— Yasuhisa Yoshida (@syou6162) 2022年3月24日
具体的には監査ログのreferencedViewsとかをSQLで出して、Data Studioでピボットテーブルを作ってます。
エンジニアであってもBigQueryあんまり触ってない方なら基礎的な概念の説明は厚めにしたほうがいいなとか、エンジニアでない方でもゴリゴリやってる人なら説明すっ飛ばして本題にいきなり行っても大丈夫そうとか、そういうのが分かると少し安心してmtgに臨めます(コミュ障なので...)。
書いてて思ったけど、これはあれですね。インターネットの人と初めて会う場合にtwitterやらブログやらを予習してから対面で会う仕草とほとんど一緒ですね。
社内のBigQueryのリソースの枯渇状況をData Studioで可視化する
Flat-RateでBigQueryを実行していると、自分のクエリが捌けるまでの時間は他の人のスロットの利用状況によってかなり変わってきます。そこで
- 同じ内容のクエリを定期的に実行してCloud Monitoring Metricsに放り込む
- オンデマンドでも同じクエリを実行して、Cloud Monitoring Metricsに放り込む
- Flat-Rateでかかった時間 / オンデマンドでかかった時間の比を取る
ということをやっていて、こうすると理想状態との相対実行時間を知ることができて便利です。詳しくは会社のTech Blogに書いてあります。
こういうグラフが得られます。毎日、朝会でグループメンバーと一緒に見てます。
このグラフ、情報量が多くて便利なので社内の人にも広く公開したい。「今の時間はBigQuery混んでてちょっと遅いみたいだから、あとで分析しよう」*1とか「この時間帯はいつもBigQuery空いてるから重めのバッチを流しても大丈夫かな」といったことを社内のユーザーがセルフサービスで分かるようにしたい。
ただ、元のグラフはCloud Monitoringのダッシュボードなので、あまり広く権限を付与したくはない。困った。
ちょっとよく考えてみると、以下のような工夫をするだけでSQLで同等の情報を得ることでき、Data Studioでダッシュボードを公開できることが分かりました。助かった。
- 元々のメトリックはクエリの実行時間 + αの計算をしているだけなので、INFORMATION_SCHEMAで基本的には取れる
- そのままだと、それ以外のクエリの情報も入ってきてしまう
- BigQueryはジョブに対してもラベルを付与(ポイント!)できるので、monitoringのジョブであることを分かるようなラベルを付与する
- INFORMATION_SCHEMAでもラベルの情報は入っているので、よしなにフィルタしてあげればよい
![Google Cloudではじめる実践データエンジニアリング入門[業務で使えるデータ基盤構築]](https://m.media-amazon.com/images/I/51s2EB1vvoL._SL500_.jpg "Google Cloudではじめる実践データエンジニアリング入門[業務で使えるデータ基盤構築]")
*1:こういうことになるべくならないようにFlex Slotを適宜購入とかはやってます
