BigQuery において 一時的な TVFやモデルを作る:

[takegue]

BigQueryにおける一時的なテーブルやUDFの作成

create temp function や create temp table 構文を利用することで、一時的なテーブルやUDFを作ることができる.
ただしこの temp 指示子は、限定的で TVFやPROCEDURE、BigQueryMLの modelなどでは作成することができない。

この記事では temp指示子が サポートされていないリソース類について作成する方法について解説する。

解決方法: BigQueryにより作成される一時隠しデータセットを間借りする

create temp table 構文により作成されるリソースは、BigQueryのシステム上では
一時的な隠しデータセットを生成している。
これは上記を実行したジョブから、作成されたテーブルのメタデータから確認できる。

そしてこれはSQL上からは一時的に参照できるデータセットであり、次の特徴を持つ。

• GUI上では確認できない
• INFORMATION_SCHEMA のメタデータは 利用できない
• 時間経過と共に削除される
• 作成者にしか参照できない。

このデータセットにUDFやTVFなどを作成することで一時的なTVFの作成を達成することができる

PoCコード

次のコードが上記を達成するコードとなる

declare temp_dataset string;
execute immediate format("create or replace temporary table `%s` as (select 1 as a limit 0)", generate_uuid());

set temp_dataset = (
  select as value
    ifnull(destination_table.dataset_id, error('not found job')) 
  from `region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
  where job_id = @@last_job_id
    and creation_time >= current_timestamp() - interval 30 minute
  limit 1
)
;
set @@dataset_id = temp_dataset;


-- table の作成と利用
create table `temp_table_test1`
as select 1 as a;
select * from `temp_table_test1`;

-- TVF の作成と利用
execute immediate format("""
  create or replace table function `%s.hoge`()
  as select 1 as a;
"""
  , temp_dataset
);
execute immediate format("select * from `%s.hoge`()", temp_dataset)

Limitation

• hidden dataset配下の TVFの参照には @@dataset_idが効かないため、 execute immediate で動的なSQLを実行する必要がある