自分用BigQuery周りコマンド集

# プロジェクト切り替え
```
gcloud config set project project_id
```

# bq query
## クエリ結果をもとにテーブル作成(存在したら失敗)
```
bq --location=us query \
--destination_table project_id:dataset_id.table_id \
--use_legacy_sql=false ${sql}
```
※ ${sql} はSQLを記述したファイル読み込み結果を変数に格納している

## 追記
```
bq --location=us query \
--append_table \
--destination_table project_id:dataset_id.table_id \
--use_legacy_sql=false ${sql}
```

## 置換
```
bq --location=us query \
--replace \
--destination_table project_id:dataset_id.table_id \
--use_legacy_sql=false ${sql}
```

## 取り込み時間分割テーブル
```
bq --location=us query \
--time_partitioning_type=DAY \
--destination_table project_id:dataset_id.table_id \
--use_legacy_sql=false ${sql}
```

## パーティション分割テーブル
```
bq --location=us query \
--time_partitioning_field field_column \
--destination_table project_id:dataset_id.table_id \
--use_legacy_sql=false ${sql}
```

# bq query 応用
## 既存のテーブルをパーティション分割テーブルにする
パーティション分割テーブルの構文を用いて SELECT * FROM を実行する

```
bq --location=us query \
--time_partitioning_field field_column \
--destination_table project_id:dataset_id.table_id \
--use_legacy_sql=false 'SELECT * FROM `project_id.dataset_id.table_id_work`'
```

## シェルで実行する
日付を引数にする

```
$ sh example.sh YYYYMMDD
```

```example.sh
#!/bin/sh
YYYYMMDD=$1

sql=`sh ./sql_example.sh ${YYYYMMDD}`

bq --location=us query \
--destination_table project_id:dataset_id.dest_table_id \
--use_legacy_sql=false ${sql}
```

```sql_example.sh
#!/bin/sh

YYYYMMDD=$1

cat <<EOS
SELECT
  col1,
  col2,
  col3
FROM \`project_id.dataset_id.source_table_id\`
WHERE
  col4 = PARSE_DATE('%Y%m%d', '${YYYYMMDD}')
EOS
```

## 上記との組み合わせで日付ループしたい時
2020-08-26〜2020-09-04までループされる

```loop.sh
#!/bin/sh

YYYYMMDD="20200826"

while [ "$YYYYMMDD" != "20200905" ]; do
    sql=`sh ./sql_example.sh "${YYYYMMDD}"`
    echo ${sql}

    bq --location=us query \
    --append_table \
    --use_legacy_sql=false ${sql}
    
    YYYYMMDD=$(date -v+1d -j -f "%Y%m%d" "$YYYYMMDD" "+%Y%m%d")
done

```

# bq schema
## テーブル作成
```
bq mk \
--table \
project_id:dataset_id.table_id \
schema.json
```

## テーブル削除
`-f` は削除確認なしで削除する

```
bq rm -f project_id:dataset_id.table_id
```

## テーブルコピー

```
bq cp project_id:dataset_id.table_id project_id:dataset_id.table_id_backup
```

## schema取得
```
bq --format=prettyjson show project_id:dataset_id.table_id > schema.json
```

## schema更新
基本的にカラム削除はできない。追記するのみ

```
bq update dataset_id.table_id schema.json
```

## カラムを削除したい場合
1. テーブルをコピーする
2. 元テーブルを削除する
3. クエリ結果を利用してテーブル作成時に不要なカラムを除いてSELECTする

### 取り込み時間分割テーブルの場合
1. テーブルをコピーする
2. スキーマ取得
3. json修正
4. 元テーブルを削除する
5. 修正したjsonを利用してテーブルを作成する
6. クエリ結果を利用してテーブル作成時に不要なカラムを除いてSELECTする

```
INSERT INTO `project_id.dataset_id.table_id`
(
  _PARTITIONTIME,
  col1,
  col2,
  col3
)
SELECT
  _PARTITIONTIME as pt,
  col1,
  col2,
  col3
FROM `project_id.dataset_id.table_id_backup`
```

### パーティション分割テーブルの場合
1. テーブルをコピーする
2. 元テーブルを削除する
3. クエリ結果を利用してテーブル作成時に不要なカラムを除いてSELECTする

※ 取り込み時間テーブルのように配慮しなくて良い

# 構造化テーブル用のクエリ (firebase)
```
SELECT
    col1_ts,
    col2,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'key1') AS key1,
    (SELECT value.int_value    FROM UNNEST(event_params) WHERE key = 'key2') AS key2,
    (SELECT value.float_value  FROM UNNEST(event_params) WHERE key = 'key3') AS key3,
    (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'key4') AS key4
FROM `project_id.dataset_id.table_id`
WHERE
    DATE(col1_ts) = PARSE_DATE('%Y%m%d', '${YYYYMMDD}')
    AND event_name = 'event_name'
```

# データエンジニアリング関連
## スキャン量が知りたい時
※ 要するに犯人探しです

```
WITH
`param` AS (
  SELECT DATE('2020-09-01') AS target_date
)

SELECT
  TIMESTAMP_ADD(creation_time, INTERVAL 9 HOUR) AS ts,
  user_email,
  destination_table.dataset_id,
  destination_table.table_id,
  query,
  ROUND(total_bytes_processed / 1024 / 1024 / 1024, 1) AS total_gb_processed
FROM region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  project_id = 'project_id'
  AND job_type = "QUERY"
  AND DATE(TIMESTAMP_ADD(creation_time, INTERVAL 9 HOUR)) = (SELECT target_date FROM `param`)
```

## BigQueryデータ基盤のテーブル依存関係を管理する
https://qiita.com/munaita_/items/0114dad655f6ccf758bf

## BigQueryデータ基盤のテーブル参照回数を管理する
https://qiita.com/munaita_/items/fccde4eaa0929b9c2bf0