本サイトからスピンオフした姉妹サイトです。テーブルワイルドカード関数とテーブルデコレーターを説明する。BigQueryを使ったクエリの実行について説明した。今回は前回説明しきれなかった、テーブルワイルドカード関数およびテーブルデコレーターについて説明したい。データスキャン量を抑えるための機能テーブルワイルドカード関数やテーブルデコレーターといった機能はBigQuery独特の機能であるが、この機能が活用される背景には、BigQueryのテーブルはレコードの追記のみが可能で、更新や削除ができないことがある。日々レコードが追加されていくテーブルを、同じテーブルのまま利用していくと仮定しよう。レコード数が増えるため、同じクエリであってもデータスキャン量が増える。そのため、クエリの課金額も増える、という問題が生じるだろう。古くなったデータを削除することになっても、レコードの削除ができない。新規に同じスキーマの別名テーブルに対し、必要なレコードを抽出するWHERE条件を付けてSELECTした結果データを新テーブル側に挿入し、元のテーブルを削除して、別名テーブルを元のテーブルにリネームする、といった運用もできなくはないが、非常に煩雑である。そこで一般的に活用されているのが、ポストフィクスを付けたテーブルである。ポストフィクスは日付を利用することが多い。アクセスログを保存する場合、AccessLogという名前の後ろに日付をポストフィクスとして追加する。AccessLog_20151101、11/2のレコードあればAccessLog_20151102といった具合である。1日おきにテーブルを作る必要はなく、データ量に応じて月ごとや年ごとにテーブルを作ればいいだろう。このような運用をすれば、テーブルのサイズが増え続けることもなく、古いレコードを削除したい場合はテーブルごと削除すればよい。この運用をすると、複数の日にまたがったレコードをクエリの対象とする場合、複数のテーブルをクエリの対象にする必要がある。テーブルワイルドカード関数を使うことによって、あらかじめ指定した条件に一致するテーブルのみを対象とすることができる。テーブルデコレーターは常にレコードが挿入されているようなテーブルで、通常のクエリではスキャン量が多くなってしまうような場合に、レコードの挿入時刻でスキャン対象をフィルターする機能である。どちらも、データスキャン量を抑えることのできる機能である。テーブルワイルドカード関数TABLE_DATE_RANGE今回は日付付きのポストフィクステーブルをクエリの対象とするため、githubarchive:day.events_YYYYMMDD(YYYYMMDDは日付で、執筆時点では2015年1月1日以降毎日作成されている)に公開されているテーブルを使用する。BigQueryのクエリを使って5日前~3日前のテーブルを対象にしたクエリを紹介する。5日前~3日前のテーブルを対象にtypeごとのカウントを取得するBigQueryのクエリリスト1のように、FROM句にTABLE_DATE_RANGE関数の引数には、テーブルのポストフィクスを除いた共通の部分と、TIMESTAMPを指定する。BigQueryで書いてみよう。Studioでのプロジェクトの作り方、NuGetでのプロジェクトの追加、認証しBigQueryContextを生成する処理などは、前回の記事を参考にしてほしい。テーブルに対応したC#クラスの定義で、TablePrefix属性に変更し、日付を除いた部分を属性の引数に指定する(リスト2)。テーブルスキーマに対応するクラスクエリを実行するコードはリスト3となる。TABLE_DATE_RANGE関数を使ったクエリを実行するコード(FromDateRangeメソッド使用)なお、このコードで実行されるクエリ内容はリスト1と同じである。Fromメソッドの代わりにFromDateRangeメソッドを使い、その引数にDateTimeOffset(=リスト3ではDateTimeオブジェクトを指定し、DateTimeOffset型への暗黙的な変換を利用している)を指定し、開始日と終了日を指定している。FromDateRangeメソッドにはいくつかオーバーロードが用意されているが、引数を指定せずに今日(=DateTime.Today)のテーブルを対象にするものや、開始日のみを指定して開始日から今日までのテーブルを対象にするものをよく使うだろう。TABLE_DATE_RANGE_STRICTさて、TABLE_DATE_RANGE関数は指定期間に欠けている日付がある場合、その日付以外のテーブルをクエリの対象とする。2日前~2日後の日付を対象にしてリスト2と同様のコードを実行すると、2日前~今日もしくは1日前の日付を対象にした結果と変わらないはずだ(※今日までか1日前までかのどちらになるかは、その日のテーブルが作成されるタイミングによる)。それに対し、テーブルが存在しない日付を対象にした場合エラーを返すのが、TABLE_DATE_RANGE_STRICT関数である。BigQueryではリスト4のようにFromDataRangeStrictメソッドに対応する。TABLE_DATE_RANGE_STRICT関数を使ったクエリを実行するコード(FromDateRangeStrictメソッド使用)なお、このコードは実行時エラーになる。BigQueryでは、指定した範囲の中に存在しないテーブルが存在する場合、実行時エラーとなる。API関連が原因の例外は、全てこの型になる。このため、より詳細な例外処理をするためには、メッセージに特定の文字列が含まれているか、などで処理する必要があるだろう。Table」と後続のテーブル名が手掛かりになるだろう。メッセージの形式は仕様として定まっていないため、変更される可能性があるので注意されたい。過去実際に、メッセージの大文字・小文字が変更されたこともある。TABLE_QUERYここまでのテーブルワイルドカード関数は、毎日テーブルが作られることを前提として、開始日と終了日の範囲指定で対象のテーブルを指定できた。汎用(はんよう)的に、テーブル名を文字列として扱い、条件式に一致するテーブルをクエリ対象にできるのが、TABLE_QUERY関数である。正規表現を使って各月下旬(=20日以降)のテーブルを対象にするクエリは、リスト5のように書ける。TABLE_QUERY関数を使ったクエリを実行するコード(FromTableQueryメソッドと正規表現を使用)なお、このクエリは800MBytes以上スキャンするため、実行するときは課金額に注意してほしい。正規表現に限らず、BigQueryの式で記述できれば利用できるため、リスト6のようにテーブル名の先頭から「events_」の文字列を取り除いた日付部分をパースし、日にち部分を比較することでフィルタリングするテーブルクエリも実行可能である。TABLE_QUERY関数を使ったクエリを実行するコード(FromTableQueryメソッドを使用し、日付と比較)リスト5と同じくこのクエリは800MBytes以上スキャンするため、実行するときは課金額に注意してほしい。テーブルデコレーターテーブルワイルドカード関数が複数のテーブルにまたがってクエリを実行するための機能であるのに対し、テーブルデコレーターは1つのテーブルに対してデータスキャン量を削減するための機能である。ポストフィクス付きのテーブルを活用することで、日ごとのテーブルを作成している場合でも、アクセスログといった絶えずレコードが挿入されているテーブルでは全体のレコード量が多くなり、その結果クエリのデータスキャン量も増えがちである。そのようなテーブルに対し、「指定した時刻のスナップショット」もしくは「指定した時間帯に挿入されたレコード」をクエリの対象にできるのが、テーブルデコレーターという機能である。スナップショットデコレーターまず、指定した時間のスナップショットを取得する機能であるスナップショットデコレーターを使ってみよう。リスト7のように、From<T>メソッドを実行した後にWithSnapshotメソッドを呼び出す。現在時刻と比べた相対時間をTimeSpanで指定する、もしくは絶対時間をDateTimeOffsetで指定する2通りが使える。テーブルデコレーターは単一のテーブルに対して作用するため、Fromメソッドでリスト2のようにTablePrefix属性を付けたクラスを利用する場合、引数にテーブル名を指定する。前回記事のようにTableName属性を付けて引数にテーブル名を指定しない方法も利用できるが、同じスキーマの日付が違うテーブルを同時に利用することを考慮すると、テーブル名を引数で指定する方が利用しやすいと思う。テーブルデコレーターで指定する時刻は現在時刻より7日以内である必要がある。スナップショットデコレーター機能を使用したクエリを実行するコード(Fromメソッド+WithSnapshotメソッド使用)実際に利用する場合は、実行日に近いテーブルを指定し、データが存在している時間をスナップショットに指定しないと実行時エラーが発生する。リスト7で実行された実際のクエリを見てみるとリスト8のようになる。リスト7のコードを実行する際に、BigQueryで実行されるクエリテーブル名の後の@以降の数値に注目してほしい。どちらも単位はミリ秒で、相対時刻の場合は負の値、絶対時刻の場合は1970年1月1日からの経過時間を指定している。BigQueryを使えば、TimeSpanもしくはDateTimeOffsetという.NET標準のオブジェクトで指定できる点が便利である。最後に時間範囲を指定して、その時間中に挿入されたレコードのみをクエリの対象とするレンジデコレーターを紹介する。使い方はWithSnapshotの代わりにWithRangeを指定すればよい。レンジデコレーターの場合も、相対時間をTimeSpanで指定する方法と、絶対時間をDateTimeOffsetで指定する方法が使える。第2引数を省略すると、現在時刻までの時間範囲となる。BigQueryによるクエリの実行について説明してきた。次回はBigQueryにデータを投入してテーブルを作る方法について、C#からAPIを実行して処理する方法を説明したい。※以下では、本稿の前後を合わせて5回分(第2回~第6回)のみ表示しています。INDEX]を参照してください。.NETの使い方BigQueryをはじめ、GoogleのほとんどのサービスはAPIが提供されている。これを.NETから利用するためのライブラリの基本的な使用方法を解説する。Library)を用いたクエリ実行BigQueryをより便利に使うために、C#プログラムからAPIを利用する方法を紹介する。BigQueryによるクエリ実行C#プログラマーにおなじみのLINQでBigQueryのクエリを実行するライブラリを紹介。テーブルワイルドカード関数とテーブルデコレーターを説明する。C#のクライアントライブラリを使ってBigQueryへデータを挿入する方法を説明。Insertについても解説する。機械学習エンジニアを目指す人~初中級者のための技術情報サイトです。2023最適なビューへの切り替えを推奨します:スマートフォン用表示に。