Zenn
🦄

BigQueryの結果をファイルにエクスポートする方法

2022/02/23に公開
Bash
BigQuery
tech

はじめに

ローカルからBigQueryにSQLを実行し、その結果をローカルファイルに保存する方法になります。

目的

  • クエリの結果はB上に一時テーブルを作成して出力する
  • クエリ結果がギガ単位のサイズ想定・・・BQのテーブルのGCSエクスポートはサイズが大きいと分割する必要がある
  • 一時テーブルをGCSのバケットにエクスポートしてからダウンロードする・・・この時に複数に分割される場合がある
  • 一時テーブルは有効期限で自動で削除するようにする。
  • GCSからダウンロードしたファイルをローカルで一個ファイルに結合する

事前準備

  • ローカルからbqコマンドでBigQueryにクエリを実行できるようにする。
  • ローカルからgsutilを利用しGCSを利用できるようにする。

実行コマンド

test.sh
#!/bin/bash

echo '
select * from dataset1.table1
where id > 100
' | bash exec_bq.sh output_file.tsv

# bash test.sh
# head -1000 output_file.tsv

exec_bq.shの内容

exec_bq.sh
#!/bin/bash

EXTRACT_FILE=$1
NOW=`date +%Y%m%d%H%M%S`

LOCATION_NAME=asia-northeast1
DATASET_NAME=dataset1
BACKET_NAME=backet1
TMP_DIR=./tmp_files

# BQ上に作成する一時テーブルの名前
TARGET_TABLE=${DATASET_NAME}.tmp_${NOW}

# 標準入力のクエリを実行
cat | bq query --replace --use_legacy_sql=false --destination_table ${TARGET_TABLE} --allow_large_results

# 作成した一時テーブルの有効期限を1時間に設定
bq update --expiration 3600 ${TARGET_TABLE}

# テーブルをGCSにエクスポートする
bq --location=${LOCATION_NAME} extract --destination_format CSV --compression NONE --field_delimiter tab --print_header=true ${TARGET_TABLE} gs://${BACKET_NAME}/EXTRACT_TMP/tmp_extract-*.tsv

# ダウンロード
mkdir -p ${TMP_DIR}/
gsutil -m cp gs://${BACKET_NAME}/EXTRACT_TMP/tmp_extract-*.tsv ${TMP_DIR}/

# ダウンロードしたファイルの結合
head -1 ${TMP_DIR}/tmp_extract-000000000000.tsv > ${EXTRACT_FILE}
ls -1 ${TMP_DIR}/tmp_extract-*.tsv | xargs -n 1 tail -n +2 >> ${EXTRACT_FILE}

# GCSのエクスポートしたファイルの削除
gsutil -m rm gs://${BACKET_NAME}/EXTRACT_TMP/tmp_extract-*.tsv

# ローカル側の一時ファイルの削除
rm -f ${TMP_DIR}/tmp_extract-*.tsv

exit 0

補足A

以下の設定値は環境に合わせて変更が必須となります。

LOCATION_NAME=asia-northeast1
DATASET_NAME=dataset1
BACKET_NAME=backet1
TMP_DIR=./tmp_files

補足B

bqコマンドやgsutilが標準出力を利用するので、結果をファイルではなく標準出力にする場合は次のような修正を行う。

  • 各コマンドの結果を/dev/nullに出力するように変更する。
  • 一時ファイルに出力するようにして最後にcatする。
  • 一時ファイルはcat後に削除する。

補足C

サイズが大きい場合TMP_DIRの領域を意識する必要がある。

GCSのBacketの用意

権限を厳しくした専用のBacketを用意するべき。

Discussion