{GCP BigQuery}テーブルデータのエクスポート (bqコマンドの場合)

https://cloud.google.com/bigquery/docs/exporting-data?hl=ja
https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-csv?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-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. BigQueryデータセットを作成する

bq mk ds01

bq ls

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

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

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('X',100);
insert into ds01.tab1 values('Y',200);
insert into ds01.tab1 values('Z',300);

select * from ds01.tab1;

-- 5. バケットの作成

gcloud storage buckets create gs://bucket123 \
--default-storage-class=Standard \
--no-enable-autoclass \
--location=asia-northeast1 \
--public-access-prevention \
--uniform-bucket-level-access

gcloud storage ls

-- 6. テーブルデータのエクスポート

bq extract \
--destination_format CSV \
--field_delimiter=',' \
'ds01.tab1' \
gs://bucket123/tab1.csv

gcloud storage ls --long gs://bucket123/*

-- 7. テーブルデータのインポート

bq load \
--autodetect \
--source_format=CSV \
--field_delimiter=',' \
'ds01.tab2' \
gs://bucket123/tab1.csv

select * from ds01.tab2;

-- 8. クリーンアップ
gcloud storage rm gs://bucket123 --recursive
gcloud storage ls

gcloud projects list

gcloud projects delete project01-9999999 \
--quiet