GCSのファイルを、BigQueryへ前日分を自動的に取込

4166 ワード
取込み SQL GCS BigQuery SQL テキストリンク

SQLの機能を利用して定期取込み自動化

1 特に下記新機能を使ってみたい

1. CREATE OR REPLACE EXTERNAL TABLE　（外部ストレージの読み取りと書き込み）
2. EXECUTE IMMEDIATE　（スクリプティングと SQL の改善）
↓半年前っぽいが最近気づいた、他にも参考になりました

2 やりたい事

毎日スケジュールでGCSのtable_yyyymmdd.csvを、BQに取込みtable_yyyymmddテーブルを作成したい

3 利用データ

GCS：table_20200609.csv 日付はyyyymmdd形式　/ 前日日付で、毎日作成される
テーブル構成
列名 型
TABLE_SCHEMA STRING
TABLE_NAME STRING
TABLE_ROWS INT64
TABLE_COMMENT STRING

4 実行SQL

transfer.sql
/*1 日付変数を定義*/
DECLARE yyyymmdd INT64 DEFAULT 20210601;
SET yyyymmdd = (SELECT CAST(FORMAT_DATE("%G%m%d",CURRENT_DATE() -1) as INT64)) ;
/*2 GCSとの外部接続テーブル作成*/
EXECUTE IMMEDIATE format("""
    CREATE OR REPLACE EXTERNAL TABLE <外部接続テーブル名>(TABLE_SCHEMA STRING,TABLE_NAME STRING,TABLE_ROWS INT64,TABLE_COMMENT STRING) 
    OPTIONS 
        (uris=["gs://<パケット名>/<フォルダ名>/table_%d.csv"], skip_leading_rows=1,format=CSV)
""", yyyymmdd);
/*3 外部接続テーブルから、実テーブル作成*/
EXECUTE IMMEDIATE format("""
    CREATE OR REPLACE TABLE `<project名>`.test.table_%d
    as (select * from ＜外部接続テーブル名＞)
""", yyyymmdd);
1.SETで前日日付をセット
2.EXECUTE IMMEDIATE でtable_yyyymmdd.csvとの外部接続テーブル（EXTERNAL TABLE）を作成
　(列名はskip_leading_rowsで指定)
3.EXECUTE IMMEDIATE でtable_yyyymmddを作成

5 スケジュールに登録し、実行

テーブルも出来ているようです。

6 まとめ

１日１回取込みぐらいだとSQL機能のみで簡単便利かと