{GCP BigQuery}ビューの作成

https://cloud.google.com/bigquery/docs/views?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
)
;

CREATE TABLE ds01.tab2
(
  col1 NUMERIC
, col2 STRING
)
;

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(1,'あ');
insert into ds01.tab1 values(2,'い');
insert into ds01.tab1 values(3,'う');
insert into ds01.tab1 values(1,'え');
insert into ds01.tab1 values(2,'お');
insert into ds01.tab1 values(3,'か');

insert into ds01.tab2 values(1,'X');
insert into ds01.tab2 values(2,'Y');

select * from ds01.tab1;
select * from ds01.tab2;

-- 5. ビューを作成する

CREATE VIEW ds01.view1(col1, col2) AS (
  SELECT
      t1.col1
    , t1.col2
  FROM ds01.tab1 t1
  INNER JOIN ds01.tab2 t2
  ON t1.col1 = t2.col1
  WHERE t2.col2 = 'X'
  ORDER BY t1.col2 DESC
);

SELECT * FROM ds01.INFORMATION_SCHEMA.VIEWS
order by table_name;

select * from ds01.view1;

-- 6. クリーンアップ

gcloud projects list

gcloud projects delete project01-9999999 \
--quiet