{GCP BigQuery}クエリのスケジューリング

GCP

 


https://cloud.google.com/bigquery/docs/scheduling-queries?hl=ja

 

-- 1. 前作業

gcloud init
gcloud auth list

gcloud --version

gcloud projects create project01-9999999 \
--name="project01"

gcloud config list
gcloud config set project project01-9999999
gcloud config set compute/region asia-southeast1 --quiet
gcloud config set compute/zone asia-southeast1-a --quiet

gcloud beta billing accounts list
gcloud beta billing projects link project01-9999999 --billing-account=111111-111111-111111

gcloud services enable compute.googleapis.com --project project01-9999999

gcloud components update

 


-- 2. BigQuery API有効化

gcloud services list --enabled


gcloud services enable bigquery.googleapis.com \
--project project01-9999999


-- 3. BigQuery Data Transfer Service有効化


gcloud services enable bigquerydatatransfer.googleapis.com \
--project project01-9999999

 

 

-- 4. BigQueryデータセットを作成する

CREATE SCHEMA ds01;

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
order by schema_name
;

 


-- 5. テストテーブルの作成

CREATE TABLE ds01.tab1
(
  col1 NUMERIC
, col2 STRING
, col3 TIMESTAMP
)
;

 

select * from ds01.INFORMATION_SCHEMA.TABLES
order by table_name
;

SELECT * FROM ds01.INFORMATION_SCHEMA.COLUMNS
order by table_name,column_name
;


insert into ds01.tab1 values(100,'A',current_timestamp());
insert into ds01.tab1 values(200,'B',current_timestamp());


select * from ds01.tab1;

 

 

-- 6. スケジュールされたクエリを設定する

 

bq query \
--use_legacy_sql=false \
--destination_table=ds01.tab2 \
--display_name='query01' \
--schedule='every 15 minutes' \
--append_table=true \
'select * from ds01.tab1'


※間隔の最小値は15分


bq ls \
--transfer_config \
--transfer_location=us


select * from ds01.INFORMATION_SCHEMA.TABLES
order by table_name
;

SELECT * FROM ds01.INFORMATION_SCHEMA.COLUMNS
order by table_name,column_name
;


select * from ds01.tab2;

 

-- 7. クリーンアップ

 

gcloud projects list

gcloud projects delete project01-9999999 \
--quiet