https://cloud.google.com/bigquery/docs/routines?hl=ja
BigQuery では、ルーティンは以下の対象を含むリソースタイプです。
リモート関数を含むユーザー定義関数（UDF）
テーブル関数
ストアド プロシージャ
-- 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データセットを作成する
CREATE SCHEMA ds01;
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
order by schema_name
;
-- 4. ルーティンの作成
-- 4.1 ユーザー定義関数（UDF）を作成する
CREATE OR REPLACE FUNCTION ds01.func01(x INT64, y INT64)
RETURNS FLOAT64
AS (
(x + 1) / y
);
SELECT
val, ds01.func01(val, 2)
FROM
UNNEST([1,2,3,4]) AS val;
CREATE OR REPLACE FUNCTION ds01.func02(x ANY TYPE, y ANY TYPE)
AS (
(x + 1) / y
);
SELECT
ds01.func02(3, 4) AS integer_input,
ds01.func02(1.59, 3.14) AS floating_point_input;
CREATE OR REPLACE TABLE ds01.users
AS (
SELECT
1 AS id, 10 AS age
UNION ALL
SELECT
2 AS id, 30 AS age
UNION ALL
SELECT
3 AS id, 10 AS age
);
CREATE OR REPLACE FUNCTION ds01.func03(userAge INT64)
AS (
(SELECT COUNT(*) FROM ds01.users WHERE age = userAge)
);
SELECT
ds01.func03(10) AS count_user_age_10,
ds01.func03(20) AS count_user_age_20,
ds01.func03(30) AS count_user_age_30;
-- 4.2 テーブル関数を作成する
CREATE OR REPLACE TABLE ds01.tab1
(
col1 INT64
, col2 STRING
, col3 INT64
)
;
insert into ds01.tab1 values
(2021,'A',2)
,(2021,'A',9)
,(2021,'A',89)
,(2021,'B',1)
,(2023,'A',22)
,(2023,'A',12)
,(2023,'B',54)
,(2023,'C',6)
,(2023,'C',3)
,(2023,'C',22);
CREATE OR REPLACE TABLE FUNCTION ds01.func11(x INT64)
AS (
SELECT col1, col2, SUM(col3) AS col3
FROM ds01.tab1
WHERE col1 = x
GROUP BY col1, col2
);
SELECT * FROM ds01.func11(2021)
ORDER BY col3 desc
LIMIT 1
;
SELECT * FROM ds01.func11(2023)
ORDER BY col2
;
-- 4.3 ストアド プロシージャを作成する
CREATE OR REPLACE TABLE ds01.tab2
(
col1 STRING
, col2 STRING
)
;
CREATE OR REPLACE PROCEDURE ds01.proc21()
BEGIN
DECLARE col1 STRING;
SET col1 = GENERATE_UUID();
INSERT INTO ds01.tab2 (col1)
VALUES(col1);
SELECT FORMAT("Created customer %s", col1);
END
;
CALL ds01.proc21();
select * from ds01.tab2;
CREATE OR REPLACE PROCEDURE ds01.proc22(col2 STRING)
BEGIN
DECLARE col1 STRING;
SET col1 = GENERATE_UUID();
INSERT INTO ds01.tab2 (col1, col2)
VALUES(col1, col2);
SELECT FORMAT("Created customer %s (%s)", col1, col2);
END
;
CALL ds01.proc22('bob');
select * from ds01.tab2;
CREATE OR REPLACE PROCEDURE ds01.proc23(col2 STRING, OUT col1 STRING)
BEGIN
SET col1 = GENERATE_UUID();
INSERT INTO ds01.tab2 (col1, col2)
VALUES(col1, col2);
SELECT FORMAT("Created customer %s (%s)", col1, col2);
END
;
DECLARE vcol1 STRING;
CALL ds01.proc23("alice",vcol1);
SELECT * FROM ds01.tab2
WHERE col1 = vcol1;
-- 5. ルーティンの確認
SELECT * FROM ds01.INFORMATION_SCHEMA.ROUTINES
order by routine_name
;
-- 6. クリーンアップ
gcloud projects list
gcloud projects delete project01-9999999 \
--quiet
