{GCP BigQuery}Procedural language

https://qiita.com/KENTAROSZK/items/2773ec4407b14f5a80eb
https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language

-- 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-northeast1 --quiet
gcloud config set compute/zone asia-northeast1-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. テストテーブル作成

CREATE SCHEMA ds01;

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

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

select * from ds01.tab1;

-- 4. 動作確認

-- 4.1 動的SQL

DECLARE col1 STRING DEFAULT 'AAAA';
DECLARE col2 INT64 DEFAULT 1111;

EXECUTE IMMEDIATE
  "CREATE OR REPLACE TABLE ds01.tab2 (col1 STRING, col2 INT64)";

EXECUTE IMMEDIATE
  "INSERT INTO ds01.tab2 (col1, col2) VALUES('BBBB', 2222)";

EXECUTE IMMEDIATE
  "INSERT INTO ds01.tab2 (col1, col2) VALUES(?, ?)"
  USING col1, col2;

EXECUTE IMMEDIATE
  "INSERT INTO ds01.tab2 (col1, col2) VALUES(@col1, @col2)"
  USING "CCCC" as col1, 3333 as col2;

EXECUTE IMMEDIATE
  CONCAT(
    "INSERT INTO ds01.tab2 (col1, col2)", "VALUES('DDDD', 4444)"
  );

select * from ds01.tab2 order by col1;

-- 4.2 例外ハンドリング

CREATE OR REPLACE PROCEDURE ds01.proc1() BEGIN
  SELECT 1/0;
END;

CREATE OR REPLACE PROCEDURE ds01.proc2() BEGIN
  CALL ds01.proc1();
END;

BEGIN
  CALL ds01.proc2();
EXCEPTION WHEN ERROR THEN
  SELECT
    @@error.message,
    @@error.stack_trace,
    @@error.statement_text,
    @@error.formatted_stack_trace;
END;

-- 4.3 case句

DECLARE i INT64 DEFAULT 100;
SET i = 1111;
CASE
  WHEN
    EXISTS(SELECT 1 FROM ds01.tab1 WHERE col1 = i)
    THEN SELECT 'found col1 in tab1';
  WHEN
    EXISTS(SELECT 1 FROM ds01.tab2 WHERE col2 = i)
    THEN SELECT 'found col1 in tab2';
  ELSE
    SELECT 'not found';
END CASE;

-- 4.4 loop

DECLARE x INT64 DEFAULT 0;
LOOP
  SET x = x + 1;
  IF x >= 3 THEN
    BREAK;
  END IF;
END LOOP;
SELECT x;

DECLARE x INT64 DEFAULT 0;
WHILE x < 5 DO
  SET x = x + 1;
END WHILE;
SELECT x;

FOR r IN
  (SELECT col1, col2
   FROM ds01.tab2
   ORDER BY col1
   LIMIT 2)
DO
  SELECT r.col1, r.col2;
END FOR;

-- 5. クリーンアップ

gcloud projects list

gcloud projects delete project01-9999999 \
--quiet