テーブル定義/データインポート(Amazon Redshift)

前回mysqlからtsvファイルとしてダウンロードしたファイルをredshiftにデータをロードする。検証目的なので、batch処理は後日。

前提環境

Redshift(AWS)
MySql(5.7系)
macOS 10.13.5
PostgreSQL 9.6.2
前回の記事でtxtファイルをs3アップロードしている

Redshiftにクラスターを追加

ここは省いて、追加済みとして進めます。

redshiftにテーブルを作る。

# tables.sql
create table tables
(
  id BIGINT NOT NULL PRIMARY KEY,
  name VARCHAR(255),
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);

ソートキーは無視します。
mysql側に定義したテーブルのカラム順番とredshiftのカラムの順番を一致させている。
値を流し込む場合にredshiftの仕様により流し込みが失敗・エラーになるケースがあります。
- String length exceeds DDL length Mysqlで扱うVARCHARの仕様のズレによりRedshiftではsizeを変更。渡された数値を文字数として扱うかバイト数で扱うかの違い

postgresql clientからredshiftに接続して、テーブルを作ります。

コマンドを簡易化するためにshファイルを作っておく。

# redshift.sh
psql -Uusername poscalc --host=endpoint -p port
# redshiftのusernameに対応したpasswordを入力する

bash redshift.sh < tables.sql

データ投入

# import_to_redshift.sql
copy tables
from 's3://buckets/txts/tables.txt'
iam_role 'arn:aws:iam::000000000:role/RedshiftRoleForClient'
region 'ap-northeast-1'
blanksasnull
emptyasnull
csv;

bash redshift.sh < import_to_redshift.sql

データの確認

bash redshift.sh
select * from tables;

...=> 結果確認

以上になります。作るケースもありますが、redshiftと繋げるBIツールもありますのでユーザ(SQL必須)にはそちらを使って確認してもらうこともできます(tableau Desktop ... etc)

チューニングに関しては、まだ書く意欲があれば書きます。

次の課題

本稼働に向けて、batch処理化する必要がありますので、AWS DATAPIPEを利用することも検討しますし、記事内のスクリプトをcronに設定して、lambda(AWS)で流し込むことも可能です。

費用面と設計面でどちらを選択するか検討する。

回線速度の問題で、s3のアップロードで時間がかかるので、zip, gzipにしてからアップロードして、s3 + lambdaで解凍するとこの問題を迂回する。

参考

文字型 - Amazon Redshift

MySQL :: MySQL 5.7 Reference Manual :: 11.4.1 The CHAR and VARCHAR Types

kikeda1104's blog

備忘録・技術に関することを書いています。(webエンジニア)