MySQL のテーブルのデータを CSV に出力

RDS のデータを BigQuery にインポートしている。

• RDS のデータをリストア
• RDS のデータを CSV で出力
  • MySQL で管理している全テーブルの名前とプライマリーキーを CSV に出力 - hene.dev
  • この記事: MySQL のテーブルのデータを CSV に出力 - hene.dev
• GCS に CSV をアップロード
• bq load コマンドを利用して BigQuery にインポート
  • BigQuery へのインポート時に利用する SCHEMA_FILE を生成 - hene.dev

今回やること

MySQL のデータを BigQuery に入れられるように整形して、CSV で出力する。

注意点

MySQL の接続先に合わせて、CSV の出力先を変更する。 CSV 生成時の MySQL の接続先が、2 種類ある。

• Amazon Aurora MySQL: RDS の Amazon Aurora MySQL に本番データをリストアしている
• MySQL Server: 本番データの dump ファイルを、MySQL にインポートしている

両方とも同じスクリプトでインポートを実行したい。

仕様

Amazon Aurora MySQL に接続している場合

• INTO OUTFILE S3 で、S3 に CSV をアップロード

S3 に CSV をアップロードしている理由

• INTO OUTFILE で、CSV を生成できない
• mysql -u ${USERNAME} -p${DB_PASSWORD} -h ${RDS_HOST_NAME} -P ${DB_PORT} > output.csv で、CSV を衛生できるが、BigQuery にインポートできる形式にエスケープするのが難しい

$ ./output_csv.sh "mysql -u ${USERNAME} -p${DB_PASSWORD}" $DATASET_NAME $TABLE_NAME

Amazon Aurora MySQL に接続していない場合

• /tmp/tables に CSV を生成

$ ./output_csv.sh "mysql -u ${USERNAME} -p${DB_PASSWORD} -h ${RDS_HOST_NAME} -P ${DB_PORT}" $DATASET_NAME $TABLE_NAME

実装

output_csv.sh

#!/bin/bash -xe

MYSQL=$1
DATASET_NAME=$2
TABLE_NAME=$3

AURORA_VERSION=$($MYSQL -N -B -e 'select @@aurora_version' 2> /dev/null || echo "null")

# Amazon Aurora MySQL に接続しているか判別
if [[ "${AURORA_VERSION}" != "null" ]]; then
    # Amazon Aurora MySQL に接続している場合は、S3 に CSV をアップロード
    INTO_OUTFILE="INTO OUTFILE S3"
    DIRECTORY_NAME="s3:/project_name/tables"
else
    # Amazon Aurora MySQL に接続していない場合は、/tmp/tables に CSV を生成
    INTO_OUTFILE="INTO OUTFILE"
    DIRECTORY_NAME="/tmp/tables"
fi

cat ./sql/output_csv.template.sql | \
    DATASET_NAME=$DATASET_NAME TABLE_NAME=$TABLE_NAME COLUMNS=$columns \
    INTO_OUTFILE=$INTO_OUTFILE DIRECTORY_NAME=$DIRECTORY_NAME envsubst | $MYSQL

output_csv.template.sql

SET SESSION group_concat_max_len = 1000000;

SELECT *
FROM ${DATASET_NAME}.${TABLE_NAME}
${INTO_OUTFILE} "${DIRECTORY_NAME}/${DATASET_NAME}_${TABLE_NAME}.csv"
  FIELDS
    TERMINATED BY ','
    ENCLOSED BY '"'
    ESCAPED BY '\\'
  LINES
    TERMINATED BY '\n';

参考

• Aurora(MySQL)のバージョンを確認する方法 – 株式会社 CONFRAGE IT ソリューション事業部