BigQuery：Big Queryでデータセットのデータの総容量・総レコード数を出す方法
今回はBig Queryネタです。
Google Cloud Platformの主要サービスのうちの一つ「Big Query」で今回は遊んでみようと思います。
最強のビッグデータ集計基盤BigQueryですが、ぶん回すうちに、
「データセットに合計でどれくらいの量のデータがあるんだろう」とよく思います。
ですので、今回はBigQuery上の「データセットの総容量と総レコード数を調べる方法」をご紹介します。
今回使うクエリ構文
今回使うクエリ元は以下の通りです。これらを少しいじっていきます。
①指定したテーブルの容量を出す
1 2 | SELECT SUM(size_bytes) / 1000000000 AS GBs FROM `Dataset.table` |
②指定されたテーブルの行数を出す
1 2 | SELECT SUM(row_count) AS Rows FROM `Dataset.table` |
以上の二つで簡単にテーブルの容量と行数が出ます。
データセット全体の容量と総レコード数を知りたい時は？？
正直、クエリを走らせなくてもテーブルプレビューでサイズを行数は確認できます。
ですが、もしデータセット全体(Datasetsに含まれるテーブル全て)のサイズと行数を知りたいときはどうするか。
これもやり方簡単です。「ワイルドカード」を使用します。
Dataset.table1、Dataset.table2、Dataset.table3、Dataset.table4・・・とテーブルが大量に存在するとき「*」を使うことで全てのテーブル指定できます。つまり、
Dataset.* = Dataset.table1、Dataset.table2、Dataset.table3、Dataset.table4・・・ となります。なので、
①指定したDatasetの容量を出す
1 2 | SELECT SUM(size_bytes) / 1000000000 AS GBs FROM `Dataset.*` |
②指定されたDatasetの行数を出す
1 2 | SELECT SUM(row_count) AS Rows FROM `Dataset.*` |
となります。シンプルですね。ワイルドカード便利。
DatasetにViewが含まれている時は注意！！
先ほどのワイルドカードを使用したクエリ、実は弱点があります。それは、
「DatasetにViweが含まれる場合は、クエリはエラーを返す」です。
Google Cloudの公式にもあるように、
制限事項
ワイルドカード テーブルクエリには、次の制限が課されます。
ワイルドカード テーブルの機能はビューをサポートしません。ワイルドカード テーブルがデータセットのビューと一致すると、クエリはエラーを返します。クエリの
WHERE句に_TABLE_SUFFIX擬似列を使用してフィルタを除外していても同様です。
どういう事でしょう。実験します。
試しにやってみた
今回はこの「Tableau」というデータセットで実験します。
二つのテーブルと二つのViewが入っています。
先ほどの記載の通りクエリを走らせようとすると・・・
走りませんね。エラーの通り、「*はViewを含む、テータセットでは使えません」とあります。
「View1」と「View2」が邪魔しているみたいです。
残念ながら、Viewを含むDatasetを調べる際には他の方法が必要そうです。
メタテーブルを使ったクエリで解決
ワイルドカードを使わない方法も勿論あります。さすがBig Query大先生。
それは「メタデータ」を使用するクエリです。こちらもGoogle Cloudのヘルプにあるように、
メタテーブルを使用する
BigQuery では、内容がメタデータ（テーブルの名前など）を表す特別なテーブルがいくつか提供されます。メタテーブルは読み取り専用です。通常、メタテーブルを使用するには
SELECTステートメントでメタテーブルを参照します。メタテーブルは、クエリジョブだけでなく、
tables.getやtabledata.listなどの他の API オペレーションでも使用できます。メタテーブルはtables.insertをサポートせず、宛先テーブルとしては使用できません。また、テーブル デコレータもサポートしていません。 メタテーブルは、データセットのtables.listには表示されません。データセット内のテーブルに関するメタデータ
データセット内のテーブルに関するメタデータには、
__TABLES__または__TABLES_SUMMARY__メタテーブルを使用してアクセスできます。
つまり、
from Dataset.__TABLES__
とすることで、Dataset内にあるテーブルを全て指定することになります。
なので、
1 2 3 4 | SELECT SUM(size_bytes) / 1000000000 AS GBs, SUM(row_count) AS Records FROM `Dataset.__TABLES__` |
とすれば良さそうです。
実際にやってみた
実際に入力画面がこちら。エラー出ていませんね。
そして結果がこちら。
5.5GB、2億行。
ブログのための適当なデータセットだったんですが、結構ボリュームありましたw
今回の記事は「データ分析で使える！」というものではありませんが、
「自社がどれくらいのデータを扱っているか」を資料として出したい時などでお役に立つかもしれません。
