https://cloud.google.com/bigquery/docs/parameterized-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データセットを作成する
CREATE SCHEMA ds01;
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
order by schema_name
;
-- 4. テストテーブルの作成
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,'A',current_timestamp());
insert into ds01.tab1 values(300,'B',current_timestamp());
insert into ds01.tab1 values(400,'B',current_timestamp());
insert into ds01.tab1 values(500,'C',current_timestamp());
select * from ds01.tab1 order by col1;
-- 5. 動作確認
bq query \
--use_legacy_sql=false \
--parameter=col1:NUMERIC:150 \
--parameter=col2:STRING:A \
'SELECT col1,col2,col3
FROM ds01.tab1
WHERE col1 >= @col1
AND col2 = @col2
ORDER BY col1;'
bq query \
--use_legacy_sql=false \
--parameter='col2:ARRAY<STRING>:["B", "C"]' \
'SELECT col1,col2,col3
FROM ds01.tab1
WHERE col2 IN UNNEST(@col2)
ORDER BY col1;'
bq query \
--use_legacy_sql=false \
--parameter='col3:TIMESTAMP:2023-03-20 12:13:45' \
'SELECT col1,col2,col3
FROM ds01.tab1
WHERE col3 >= @col3
ORDER BY col1;'
-- 6. クリーンアップ
gcloud projects list
gcloud projects delete project01-9999999 \
--quiet
