【BigQuery】外部テーブルを使いこなせ！ ファイルネームで絞ってスキャン量を減らす方法

やろうとしていること

BigQueryの外部テーブルに対してクエリをたたく際に、何も指定しないとレコードを全スキャンしてしまいます。
今回はFILENAME 疑似列を使って、スキャン量を減らしていきたいと思います。

そもそもFILENAME疑似列とは

GCSのような外部リソースを使った外部テーブルにおいて、ファイル名という疑似カラムが存在します。
そしてその疑似カラムをWHERE句等でフィルターすることで、BigQueryはそれに該当しないファイルを読み込むことをスキップします。

When the query has a filter predicate on the _FILE_NAME pseudo column, BigQuery attempts to skip reading files that do not satisfy the filter

早速クエリを例に見てみましょう。

クエリ

参考例として、以下のようなクエリを外部テーブルに対して叩けば、そのファイル名のavroファイルなりjsonファイルなりのレコードだけスキャンします。
このクエリの場合、特定のテーブルの2022年1月分のファイルレコードだけをフィルタリングしています。

filename.sql

    SELECT 
    id,
    _FILE_NAME as fn

    FROM `project_name.dataset_name.table_name`
    
    WHERE _FILE_NAME LIKE concat("gs://your_gcs_bucket_name/any_folder/", "your_table_name","/",
    "2022" ,"/" ,"01" , "/%")

結果

result.sql

行	id	 fn
 1 	1111 gs://project_id/table_1/2022/01/30/10/00/your_file1.avro 
 2 	1112 gs://project_id/table_1/2022/01/31/10/00/your_file2.avro 
...

上記の例のファイル名の構造は以下のような
project_id/table_1/year/month/day/hour/minute/your_file.avro
といった形です。
これはGCSのファイルの保存形式に依存するので、ご自身のGCSのフォルダ構造に合わせてください。

 フォルダ名(テーブル名)/year/month/day/hour/minute/file.avro
|-table_1 
|　　　  ┗2022
|　　　　　　┗01
|　　　　　　　┗31
|　　　　　　　　┗10
|　　　　　　　　　┗00
|　　　　　　　　　　┗filename_avro
|-table_2
|-table_3

まとめ(追記)

外部テーブルのスキャン量を減らす方法として紹介しましたが、
活用方法としては、連携されたデータGCSに置いてBigQueryにテーブルを作るときに

①直接外部テーブルを作る。
→　これだと外部テーブルに直クエリなのでパフォーマンスが悪い×

②（外部テーブルではない）BigQueryテーブルを作る。
→　月次ではなく毎日or毎時でテーブルを作り直したりするわけにはいかないので×

③　②の確定データテーブル（前月末日までのみ）　＋　ファイルネーム疑似カラムを使用して、今月１日～当日のデータだけ外部テーブルから取得してviewを作る
→　これなら前月までの確定データは既に作成されているかつ、今月分を取得する際に全データスキャンしないのでviewが重くなることもないのでＯＫ

以下は簡素ですが③のview作成クエリのイメージです。

sample.sql

SELECT * FROM 確定データテーブル --例　2022/03/31までのデータ
union all
SELECT * FROM 外部テーブル
WHERE _FILE_NAME LIKE concat("gs://your_gcs_bucket_name/any_folder/", "your_table_name","/",
    "2022" ,"/" ,"04" , "/%") --2022の４月分だけのデータ