自分用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データ基盤のテーブル依存関係を管理する

BigQueryデータ基盤のテーブル参照回数を管理する