データベースのテーブル名で大文字と小文字は区別される？
embulk-output-oracleを使っている方から、テーブルがあるのにエラーになってしまう、という問い合わせがあった。
調べてみると、テーブル名は大文字なのに、設定ファイルは小文字で書いてある。
普通にOracleでSQLを書くと大文字／小文字は区別されないが、embulk-output-jdbcではテーブル名を引用符で囲んだSQLを構築する。
Oracleでは引用符で囲むと大文字／小文字が区別されてしまうのだ。
うーん、各DBMSで大文字／小文字が区別されるかどうか、まとめておいた方がよさそうだ。
Oracle
9. 引用符のない識別子は、大/小文字が区別されません。 引用符のない識別子は大文字として解析されます。 引用識別子では、大文字と小文字が区別されます。
実際にやってみた。
SQL> CREATE TABLE TEST(ID CHAR(2)); 表が作成されました。 SQL> SELECT * FROM TEST; レコードが選択されませんでした。 SQL> SELECT * FROM test; レコードが選択されませんでした。 SQL> SELECT * FROM "TEST"; レコードが選択されませんでした。 SQL> SELECT * FROM "test"; SELECT * FROM "test" * 行1でエラーが発生しました。: ORA-00942: 表またはビューが存在しません。
MySQL
MySQL :: MySQL 5.6 リファレンスマニュアル :: 9.2.2 識別子の大文字と小文字の区別
データベース内の各テーブルも、...ファイルに対応しています。 ... この結果、基になるオペレーティングシステムで大文字と小文字が区別されるかどうかが、 データベース名、テーブル名、およびトリガー名で大文字と小文字が区別されるかどうかに影響します。 これは、Windows ではこれらの名前は大文字と小文字が区別されませんが、 多くの Unix では大文字と小文字が区別されることを意味します。
なるほど。OSによって違うのか。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 9.2 スキーマオブジェクト名
引用符と大文字／小文字の区別については書かれていなかった。
実際Windowsで試してみると、区別されなかった。
mysql> create table xyz (id char(2)); Query OK, 0 rows affected (0.06 sec) mysql> select * from xyz; Empty set (0.00 sec) mysql> select * from XYZ; Empty set (0.00 sec) mysql> select * from `xyz`; Empty set (0.00 sec) mysql> select * from `XYZ`; Empty set (0.00 sec)
Linuxだと大文字と小文字は区別される。
mysql> create table xyz (id char(2)); Query OK, 0 rows affected (0.02 sec) mysql> select * from xyz; Empty set (0.00 sec) mysql> select * from XYZ; ERROR 1146 (42S02): Table 'test.XYZ' doesn't exist mysql> select * from `xyz`; Empty set (0.00 sec) mysql> select * from `XYZ`; ERROR 1146 (42S02): Table 'test.XYZ' doesn't exist
PostgreSQL
キーワードと引用符付きでない(クォートされていない)識別子は大文字と小文字を区別しません。
なるほど、逆に、引用符を付けると大文字／小文字が区別されるのかな。
postgres=# create table xyz(id char(2)); CREATE TABLE postgres=# select * from xyz; id ---- (0 行) postgres=# select * from XYZ; id ---- (0 行) postgres=# select * from "xyz"; id ---- (0 行) postgres=# select * from "XYZ"; ERROR: リレーション"XYZ"は存在しません 行 1: select * from "XYZ"; ^
確かに区別された。
SQL Server
テーブル名、ビュー名、列名など、データベース内のオブジェクトの識別子には、 データベースの既定の照合順序が指定されます。 たとえば、大文字と小文字を区別する照合順序が指定されたデータベースでは、 同じ名前で大文字と小文字のみが異なる 2 つのテーブルを作成できますが、 大文字と小文字を区別しない照合順序が指定されたデータベースでは作成できません。
SQL Serverでは、照合順序の設定により、どちらでも選べるようだ。
例えば、"Japanese_CI_AS"のように"CI"を含む場合は区別しない。"CS"を含む場合は区別する。
引用符（""や[]）と大文字／小文字の区別については書かれていない。
"Japanese_CI_AS"で以下を実行したら、正常に実行できた。
CREATE TABLE XYZ(ID CHAR(2)); SELECT * FROM XYZ; SELECT * FROM xyz; SELECT * FROM "XYZ"; SELECT * FROM "xyz"; SELECT * FROM [XYZ]; SELECT * FROM [xyz];
まとめ
まとめると、こんな感じか。
| DBMS | 大文字と小文字の区別 |
|---|---|
| Oracle | 引用符で囲むと区別される |
| MySQL | 大文字と小文字のファイル名を区別するOSでは区別される |
| PostgreSQL | 引用符で囲むと区別される |
| SQL Server | 照合順序の設定によっては区別される |
embulk-output-jdbcではテーブル名を引用符で囲むので、OracleとPostgreSQLでは要注意だ。
| Oracle | 12c |
| MySQL | 5.6 |
| PostgreSQL | 9.4 |
| SQL Server | 2012 |
embulk-output-redshiftのパフォーマンスを計測してみた
embulk-output-redshiftを使えばテキストファイルを簡単にAmazon Redshiftにインポートできる。
だが、やはり気になるのはパフォーマンスだ。
という訳で、embulk-output-redshiftのパフォーマンスを計測してみた。
計測環境
AWSのリージョンはTokyoを使用した。
Redshiftのインスタンスはdc1.large。クライアントのインスタンスはm1.xlarge（4コア）だ。
インポート先のテーブルは、以下を用意した。
create table example (
id integer,
num decimal(12,0),
value1 varchar(60),
value2 varchar(60),
value3 varchar(60),
value4 varchar(60),
value5 varchar(60),
value6 varchar(60),
value7 varchar(60),
value8 varchar(60),
value9 varchar(60),
value10 varchar(60),
primary key(id)
);
テストデータは、10,000,000件（約4.2GB）のCSVファイルを用意した。
手動でインポートしてみる
まずは、指標とするため、embulkを使わず手でインポートしてみる。
まずはChromeからS3 Management Consoleでアップロードしたところ、約3.3分掛かった。
そこからCOPYでインポートしたところ、約2.6分掛かった。合計約5.9分だ。
COPY example FROM 's3://xxx/temp/test1.csv' CREDENTIALS 'aws_access_key_id=XXXXXXXX;aws_secret_access_key=XXXXXXXXXXXXXXXX' DELIMITER ','
embulk-output-redshiftでインポートする
以下のようなymlを用意し、embulk-output-redshiftでインポートしてみる。
in: type: file path_prefix: '/temp/test1.csv' parser: charset: UTF-8 newline: CRLF type: csv delimiter: ',' columns: - {name: id, type: long} - {name: num, type: string} - {name: value1, type: string} - {name: value2, type: string} - {name: value3, type: string} - {name: value4, type: string} - {name: value5, type: string} - {name: value6, type: string} - {name: value7, type: string} - {name: value8, type: string} - {name: value9, type: string} - {name: value10, type: string} out: type: redshift host: xxxxxxxx.ap-northeast-1.redshift.amazonaws.com database: dev user: xxxxxxxx password: XXXXXXXX table: example mode: insert_direct iam_user_name: redshift access_key_id: XXXXXXXX secret_access_key: XXXXXXXXXXXXXXXX s3_bucket: xxxxxxxx s3_key_prefix: temp
約35.6分掛かった。
...結構掛かるな。。
embulk-output-redshiftでは、一定サイズ毎に、GZIP圧縮された一時ファイルを作成→S3にアップロード→COPY を繰り返す。
ログを集計したところ、この処理の回数は204回。
合計アップロード時間は12.2分、合計COPY時間は3.6分だった。
COPYはともかく、アップロードにかなり時間が掛かっているな。。
なお、CSVファイルを丸ごとGZIP圧縮したら、3.2GBくらいだった。
AmazonS3ClientのputObjectを計測する
アップロードが遅いのが気になったので、AWS SDKのAmazonS3ClientクラスのputObjectメソッドを使って、アップロードだけする簡単なプログラムを書いてみた。
すると、4.2GBのファイルのアップロードに17分くらい掛かった。
やはり、S3 Management Consoleに比べるとかなり時間が掛かっている。
なぜかは…、まだ分かっていない。。
batch_sizeを大きくしてみる
上で「一定サイズ毎に、GZIP圧縮された一時ファイルを作成→S3にアップロード→COPY を繰り返す」と書いたが、このサイズは大きくすることができる。
これを大きくすれば、アップロードやCOPYの回数が減るため、オーバーヘッドの分速くなるかもしれない。
ymlを以下のように修正した。デフォルトの10倍だ。
...
out:
type: redshift
batch_size: 167772160
...
結果は、33.7分で、若干速くなった。
アップロード©の回数は21回、合計アップロード時間は11.4分、合計COPY時間は2.6分だった。
embulk-input-filesplitを使う
embulk-input-filesplitは、入力ファイルを分割して読み込み、マルチスレッド処理してくれる。
これを使えば高速化するかもしれない！
という訳で試してみた（batch_sizeは10倍）。
in: type: filesplit path: '/temp/test1.csv' parser: ...
結果は約8.9分で、かなり速くなった！
アップロードとCOPYをバックグラウンド化する
現状、アップロードとCOPYは同期処理になっている。
つまり、アップロードとCOPYが終わってから、次のレコードを読みに行くということだ。
アップロードとCOPYをバックグラウンドで行うようにすれば、速くなるかもしれない。
という訳で、実装して実行してみた（batch_sizeは10倍）。
結果は、約20.9分。
バックグラウンド化する前（33.7分）と比べると、だいぶ速くなった。
バックグラウンド化＋embulk-input-filesplit
これに更にembulk-input-filesplitも組み合わせてみる。
embulk-input-filesplitですでにマルチスレッド化されているので、それほど効果は期待できないが…。
結果は、約7.7分。
若干速くなったようだ。
まとめ
現状のembulk-output-redshiftは少々遅いので、embulk-input-filesplitと組み合わせるのがお勧め。
embulk-output-redshift自体もマルチスレッド化などにより、高速化を目指したい。
| embulk | 0.6.16 |
| embulk-output-redshift | 0.4.1 |
| embulk-input-filesplit | 0.1.2 |
jnr-ffiでJavaからCを呼び出す (Windows)
jnr-ffiとは？
JavaからCを呼び出す、と言えばJNIだが、C側にJNI固有の処理を書いたりするので結構めんどい。
jnr-ffiというフレームワークを使うと、C側の関数をそのままJavaにマッピングして呼べるらしい。
具体的には、C側に
int length(char *s)
のような関数があると、Java側から
int length(String s)
というほぼそのままのメソッドで呼び出せてしまう！
という訳で、今回はこのjnr-ffiを実際に試してみる。
jnr-ffiをダウンロードする
jnr-ffiを使うには、jffiやasmも必要だ。
mavenなどを使わず手動でダウンロードする場合は、以下からダウンロードできる。
http://search.maven.org/#artifactdetails|com.github.jnr|jnr-ffi|2.0.3|jar
http://search.maven.org/#artifactdetails|com.github.jnr|jffi|1.2.9|jar
http://forge.ow2.org/project/showfiles.php?group_id=23&release_id=5660
jnr-ffi-2.0.3.jar jffi-1.2.9-native.jar jffi-1.2.9.jar asm-5.0.3.jar asm-analysis-5.0.3.jar asm-commons-5.0.3.jar asm-tree-5.0.3.jar asm-util-5.0.3.jar asm-xml-5.0.3.jar
C側を実装する
Visual Studioで、クラスライブラリ（Visual C++）のプロジェクトを作成する。
自分の環境は64bitなので、ビルド構成も64bitにする。
プリコンパイル済みヘッダなども取っ払ってしまって、cppファイル1つだけにした。
#include <stdio.h>
#include <string.h>
extern "C" __declspec(dllexport) int int_test(int n) {
return n * 2;
}
extern "C" __declspec(dllexport) int string_in_test(char* s) {
printf("#string_in_test s = ");
for (int i = 0; s[i] != '\0'; i++) {
printf("%x:", (s[i] & 0xFF));
}
printf("\n");
return strlen(s);
}
extern "C" __declspec(dllexport) void string_out_test(char* s, int n) {
strncpy(s, "abcdefg", n);
}
extern "C" __declspec(dllexport) void byte_array_test(unsigned char* p, int n) {
for (int i = 0; i < n; i++) {
p[i] = p[i] + 1;
}
}
なお、「extern "C"」を付けておかないと、関数名が修飾されてしまい（C++のオーバーロードの解決用らしい）、Java側とうまくリンクできない。
Java側を実装する
public class JnrFfiTest {
public static interface TestC {
int int_test(int n);
int string_in_test(String s);
void string_out_test(StringBuilder s, int n);
void byte_array_test(byte[] p, int n);
}
public static void main(String[] args) {
TestC testC = LibraryLoader.create(TestC.class).load("jnr_ffi_test");
System.out.println("int_test(5) = " + testC.int_test(5));
System.out.println("string_in_test(\"abcあいう\") = " + testC.string_in_test("abcあいう"));
StringBuilder builder = new StringBuilder(4);
testC.string_out_test(builder, 4);
System.out.println("string_out_test(s, 4) = " + builder.toString());
byte[] bytes = "ABC".getBytes();
testC.byte_array_test(bytes, bytes.length);
System.out.println("byte_array_test(p, 3) = " + new String(bytes));
}
}
まず、C側の関数に合わせたインターフェイスを用意する（TestC）。
そして、LibraryLoader.createによりインスタンスを取得する。
loadの引数の"jnr_ffi_test"はDLL名（から拡張子を除いたもの）。
DLLは、環境変数PATHおよびJava VM引数 -Djava.library.path で指定したパスから検索される。
実行結果はこんな感じ。
int_test(5) = 10
#string_in_test s = 61:62:63:82:a0:82:a2:82:a4:
string_in_test("abcあいう") = 9
string_out_test(s, 4) = abcd
byte_array_test(p, 3) = BCD
文字のエンコーディングは？
上の実行結果を見ると、文字列はシフトJISのバイト列に変換されてC側に渡されている。
試しに -Dfile.encoding=UTF8 を指定したところ、以下のようにUTF8になった。
#string_in_test s = 61:62:63:e3:81:82:e3:81:84:e3:81:86: string_in_test(...) = 12
つまり、Javaのデフォルトエンコーディングが使われるようだ。
更に調べたところ、jnr.ffi.annotations.Encodingアノテーションを付けることにより、パラメータごとにエンコーディングを指定することもできるようだ。
StringBuilderに対応するバッファのサイズは？
Java側からStringBuilderを引数で渡すと、バッファが確保され、そのポインタがC側に渡される。
このときのバッファのサイズは何バイトなんだろうか？
ソースを調べてみたら、jnr.ffi.provider.converters.StringBuilderParameterConverterというそれらしいクラスがあった。
中を見ると、以下のような処理がある。
ByteBuffer byteBuffer = ByteBuffer.wrap(new byte[parameter.capacity() * (int) Math.ceil(encoder.maxBytesPerChar()) + 4]);
なるほど。
StringBuilderのcapacity × 1文字の最大バイト数 + 4 だから、長さcapacityの文字列を設定するには十分、ということだ。
まとめ
JNIからCの関数を呼ぶには、JNIに合わせた専用の関数を作る必要がある。
jnr-ffiを使えば、Cの関数をそのまま呼べるので、楽だし構成もきれいになる。
こいつはなかなか良さそうだ。
| OS | Windows 7 Enterprise (64bit) |
| Java | 8 |
| jnr-ffi | 2.0.3 |
| jffi | 1.2.9 |
| asm | 5.0.3 |
embulk-input-jdbc 0.5.0では型を指定できるようになった
以前embulk-input-jdbcをOracleで試したときは、いろいろと問題があった。
- 整数なのに小数点以下が出力される
- 大きな数値が指数表記で出力される
- DATE型なのに時分秒が出力される
- TIMESTAMPの秒未満が出力されない
しかし、embulk-input-jdbc 0.5.0 (および embulk-input-oracle 0.5.0）では、これらが全て解決できた！
テーブルの準備
この前と同じく、テーブルを準備する。
CREATE TABLE INPUT_TEST (
ID NUMBER(8,0),
NUM NUMBER(12,2),
STR CHAR(8),
VARSTR VARCHAR2(8),
DT DATE,
TIME0 TIMESTAMP(0),
TIME6 TIMESTAMP,
TIME9 TIMESTAMP(9),
PRIMARY KEY(ID)
);
データの準備
これもこの前と同じく、CSVファイルを準備する。
1,,,,,,, 2,123.4,chr1,varchr1,2015-04-24,2015-04-24 01:02:03,2015-04-24 01:02:03.12345,2015-04-24 01:02:03.12345678 3,1234567890.12,chr12345,varchr12,2015-12-31,2015-12-31 23:59:59,2015-12-31 23:59:59.123456,2015-12-31 23:59:59.123456789
で、embulk-output-oracleで流し込む。
in: type: file path_prefix: 'data/output-oracle.csv' parser: charset: UTF-8 newline: CRLF type: csv delimiter: ',' header_line: false columns: - {name: ID, type: long} - {name: NUM, type: string} - {name: STR, type: string} - {name: VARSTR, type: string} - {name: DT, type: timestamp, format: '%Y-%m-%d'} - {name: TIME0, type: timestamp, format: '%Y-%m-%d %H:%M:%S'} - {name: TIME6, type: timestamp, format: '%Y-%m-%d %H:%M:%S.%N'} - {name: TIME9, type: string} out: type: oracle host: localhost database: TESTDB user: TEST_USER password: test_pw table: INPUT_TEST mode: insert_direct insert_method: normal driver_path: 'driver/ojdbc7.jar' column_options: TIME9: {value_type: pass}
こんなymlファイルを用意して、
embulk run output-oracle.yml
を実行すればOK。
以前は「mode: insert」だったが、「mode: insert_direct」に変わっている。
また、Embulkのtimestamp型を使うとマイクロ秒未満が切り捨てられてしまうため、TIME9列はstringのまま挿入するようにしている。
embulk-input-oracleでCSVを出力する
embulk-input-jdbc 0.5.0から、各列の型を明示的に指定できるようになった。
これを利用すれば、各列の値を正確に出力することができる。
ymlファイルはこんな感じ。
in: type: jdbc driver_path: driver/ojdbc7.jar host: localhost database: TESTDB user: TEST_USER password: test_pw table: INPUT_TEST select: "*" column_options: ID: {type: long} NUM: {type: string} DT: {type: timestamp, timestamp_format: '%Y-%m-%d'} TIME6: {type: timestamp, timestamp_format: '%Y-%m-%d %H:%M:%S.%6N'} TIME9: {type: timestamp, timestamp_format: '%Y-%m-%d %H:%M:%S.%9N'} out: type: file path_prefix: input-oracle file_ext: .csv formatter: type: csv
ID列はデフォルトだとdoubleになり、小数点以下が出力されてしまうので、明示的にlongを指定している。
NUM列もデフォルトだとdoubleで、大きな数が指数表記になってしまうので、stringを指定している。
DT列、TIME6列、TIME9列は、明示的にフォーマットを指定している。
embulk run input-oracle.yml
を実行すると…、
ID,NUM,STR,VARSTR,DT,TIME0,TIME6,TIME9 1,,,,,,, 2,123.4,chr1 ,varchr1,2015-04-24,2015-04-24 01:02:03,2015-04-24 01:02:03.123450,2015-04-23 16:02:03.123456780 3,1234567890.12,chr12345,varchr12,2015-12-31,2015-12-31 23:59:59,2015-12-31 23:59:59.123456,2015-12-31 14:59:59.123456789
正しいCSVが出力された！
CHARの後ろのスペースとか、TIMESTAMPの秒未満の後ろの0とかは元のCSVと違うが、問題無いでしょう。
| embulk | 0.6.12 |
| embulk-output-oracle | 0.4.0 |
| embulk-input-oracle | 0.5.0 |
| Oracle | 12c |
embulk-output-redshiftのタイムゾーンについて
embulk-output-redshiftでタイムゾーンがどう扱われるかを確認してみた。
Redshift側のタイムゾーン
UTC固定らしい。
検証の準備
テーブル
create table test1 (
id char(4),
varchar_item varchar(20),
integer_item int,
numeric_item numeric(10,2),
date_item date,
timestamp_item timestamp,
primary key (id)
);
テストデータ
P001,あいうえお,123,123456.78,2015-05-20,2015-05-20 12:01:02+0900 P002,12345678901234567890,123456789,12345678.12,2015-12-31,2015-12-31 23:59:59+0900 P003,,,,,
yml
in: type: file path_prefix: 'data/data.csv' parser: charset: UTF-8 newline: CRLF type: csv delimiter: ',' columns: - {name: id, type: string} - {name: varchar_item, type: string} - {name: integer_item, type: long} - {name: NUMERIC_ITEM, type: string} - {name: date_item, type: timestamp, format: '%Y-%m-%d'} - {name: timestamp_item, type: timestamp, format: '%Y-%m-%d %H:%M:%S%z'} out: type: redshift host: xxx.redshift.amazonaws.com database: dev user: xxxxxxxx password: xxxxxxxx table: test1 mode: insert_direct iam_user_name: redshift access_key_id: XXXXXXXX secret_access_key: XXXXXXXXXXXXXXXX s3_bucket: xxxxxxxx
JSTで入れてみる
まず、クライアント側のタイムゾーンをJSTにして入れてみる。
embulk "-J-Duser.timezone=Asia/Tokyo" run redshift.yml
※Windowsの場合は""がいるが、Linuxの場合はいらないかも。
実行後にSELECTした結果（SQLクライアントのタイムゾーンはUTC）。
| id | varchar_item | integer_item | numeric_item | date_item | timestamp_item |
|---|---|---|---|---|---|
| P001 | あいうえお | 123 | <null> | 2015-05-20 | 2015-05-20 12:01:02.0 |
| P002 | 12345678901234567890 | 123456789 | <null> | 2015-12-31 | 2015-12-31 23:59:59.0 |
| P003 | <null> | <null> | <null> | <null> | <null> |
UTCで入れてみる
次に、クライアント側のタイムゾーンをUTCにして入れてみる。
embulk "-J-Duser.timezone=UTC" run redshift.yml
実行後にSELECTした結果（SQLクライアントのタイムゾーンはUTC）。
| id | varchar_item | integer_item | numeric_item | date_item | timestamp_item |
|---|---|---|---|---|---|
| P001 | あいうえお | 123 | <null> | 2015-05-20 | 2015-05-20 03:01:02.0 |
| P002 | 12345678901234567890 | 123456789 | <null> | 2015-12-31 | 2015-12-31 14:59:59.0 |
| P003 | <null> | <null> | <null> | <null> | <null> |
考察
Embulk内部では、時刻データをUTCで持っている。
embulk-output-redshiftは、入力データをS3上にファイル出力した後、COPYでRedshiftにロードする。
S3上の一時ファイルを確認したところ、クライアント側のタイムゾーンになっていた。
まとめると、こんな感じ。
| クライアントのタイムゾーン | JST | UTC |
|---|---|---|
| CSVファイル | 23:59:59+0900 | 23:59:59+0900 |
| Embulk内部 | 14:59:59 | 14:59:59 |
| S3上の一時ファイル | 23:59:59 | 14:59:59 |
| Redshift | 23:59:59 | 14:59:59 |
どうも、S3上の一時ファイルを出力する際に、クライアント側のタイムゾーンが反映されているようだ。
ソースを追ってみると、ファイルに書き出しているのはorg.embulk.output.postgresql.AbstractPostgreSQLCopyBatchInsertクラスのsetSqlTimestampメソッドだ。
public void setSqlTimestamp(Timestamp v, int sqlType) throws IOException
{
appendDelimiter();
writer.write(v.toString());
}
ここのTimestampは、java.sql.Timestamp型だ。
TimestampのtoStringを追ってみると、どうもクライアント側のタイムゾーンに応じてフォーマットされるようだ。
結論としては、クライアント側のタイムゾーンでの値が格納されるということだ。
| embulk | 0.6.11 |
| embulk-output-redshift | 0.3.0 |
embulk-output-jdbcの型についてまとめた
embulk-output-jdbcではいろいろな型が出てくる。
ややこしいので、現在の実装に基づいてまとめてみた。
embulk-output-jdbcに出てくる型の種類
- 入力の型
- column_optionsのvalue_type
- column_optionsのtype
- 出力先テーブルの列の型
入力の型
embulk内部の型であり、boolean/long/double/string/timestampのいずれかである。timestampにはフォーマット指定もあり、日付のみや時刻のみといいう指定もできる。
入力の型は、例えばCSV入力の場合はymlファイルで定義する。
embulk-input-jdbcを使う場合は入力元テーブルの列の型により自動的に決まる。
| テーブルの列の型(java.sql.Types) | 入力の型 |
|---|---|
| BOOLEAN BIT |
boolean |
| TINYINT SMALLINT INT BIGINT |
long |
| DOUBLE FLOAT REAL NUMERIC DECIMAL |
double |
| CHAR VARCHAR LONGVARCHAR CLOB NCHAR NVARCHAR LONGNVARCHAR |
string |
| DATE | timestamp (%Y-%m-%d) |
| TIME | timestamp (%H:%M:%S) |
| TIMESTAMP | timestamp (%Y-%m-%d %H:%M:%S) |
column_optionsのtype
必要に応じてymlファイルで定義する。
embulk-output-jdbcにより出力先のテーブルが生成される際の列の型を表す。
未指定の場合は、入力の型により自動的に決まる。
| 入力型 | テーブルの列の型 |
|---|---|
| boolean | BOOLEAN |
| long | BIGINT |
| double | DOUBLE PRECISION |
| string | CLOB |
| timestamp | TIMESTAMP |
column_optionsのvalue_type
必要に応じてymlファイルで定義する。
DBに出力する際の型を表す。
基本的には、java.sql.PreparedStatementのsetXXXメソッドに対応する。
| value_type | 動作 | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| boolean | setBoolean | ||||||||||||
| byte | setByte | ||||||||||||
| short | setShort | ||||||||||||
| int | setInt | ||||||||||||
| long | setLong | ||||||||||||
| float | setFloat | ||||||||||||
| double | setDouble | ||||||||||||
| decimal | setBigDecimal | ||||||||||||
| string | setString | ||||||||||||
| nstring | setNString | ||||||||||||
| date | setObject(java.sql.Date) | ||||||||||||
| time | setObject(java.sql.Time) | ||||||||||||
| timestamp | setObject(java.sql.Timestamp) | ||||||||||||
| null | setNull | ||||||||||||
| pass | 入力型による
|
||||||||||||
| coalesce | 未設定の場合と同じ |
未指定の場合は、出力先DBの型に応じて自動的に決まる。
| テーブルの列の型(java.sql.Types) | 動作 |
|---|---|
| BOOLEAN BIT |
setBoolean |
| TINYINT | setByte |
| SMALLINT | setShort |
| INTEGER | setInt |
| BIGINT | setLong |
| FLOAT DOUBLE |
setDouble |
| REAL | setFloat |
| NUMERIC DECIMAL |
setBigDecimal |
| CHAR VARCHAR LONGVARCHAR CLOB |
setString |
| NCHAR NVARCHAR LONGNVARCHAR |
setNString |
| DATE | setObject(java.sql.Date) |
| TIME | setObject(java.sql.Time) |
| TIMESTAMP | setObject(java.sql.Timestamp) |
例えば、CSVファイルを入力してDBに出力するとき、入力の型がlong、column_optionsのvalue_typeがdouble、テーブルの列の型がDECIMALであれば、以下のように変換されることになる。
文字列(CSVファイル) ↓ CSVパーサ long値 （入力の型） ↓ embulk-output-jdbcのColumnSetter double値 （column_optionsのvalue_type） ↓ DB側 DECIMAL値
結局どう設定すればよいのか？
基本的には、なるべく余計な変換はしたくない。
エラーが起きたり、情報が落ちたり、パフォーマンスが劣化したりする恐れがあるからである。
例えばテキストファイルを入力してDBに出力する場合は、以下の2パターンがありそうだ。
- 入力の型をDB側の型に合わせる
- 入力の型をstringにしてそのままDBに出力する（column_optionsのvalue_typeをpassに）
前者の方がきれいだが、パフォーマンスは後者の方が速いかも。
桁数の大きい数値型（DECIMAL(18,3)とか）では、後者にする必要がある。
Embulk内部の数値型はlongとdoubleだが、これらに変換すると誤差が出てしまうからだ。
なお、DBMSやJDBCドライバによっては変なjava.sql.Typesの値を返す場合があるので、このような場合もcolumn_optionsのvalue_typeを設定する必要がある。
| Embulk | 0.6.8 |
| embulk-output-jdbc | 0.3.0 |
| embulk-input-jdbc | 0.4.0 |
EmbulkでRedshiftにデータをロードしてみる
今回は、EmbulkでRedshiftにデータをロードするのを試してみたい。
マシンの準備
まずはRedshiftのインスタンスを準備する。
とりあえずは動作確認なので、一番安いdw2.largeにした。
うちの社内からデータをロードしようとすると、ユーザ認証が必要なプロキシを越えなきゃいけないので、めんどい。
そこで、EC2のインスタンスを立ち上げ、そこからロードすることにした。
テーブルの準備
JDBCドライバをダウンロードし、Redshiftのセキュリティ設定をして、SQLクライアントから接続する。
そこそこいろいろな型があるテーブルを作ってみた。
create table test1 (
id char(4),
varchar_item varchar(20),
integer_item int,
numeric_item numeric(10,2),
date_item date,
timestamp_item timestamp,
primary key (id)
);
なお、テーブル名とかカラム名は小文字にするのがよいみたいだ。
というか、大文字にしても自動的に小文字に変換されてしまう。
ので、大文字でEmbulkを実行すると、テーブルのメタデータが取得できなくて失敗してしまう。
Embulkを使わないデータのロード
まずは普通にINSERT文を実行してみる。
insert into test1 values('A001', 'あいうえお', 123, 123456.78, '2015-05-20', '2015-05-20 12:01:02');
insert into test1 values('A002', '12345678901234567890', 123456789, 12345678.12, '2015-12-31', '2015-12-31 23:59:59');
insert into test1 values('A003', null, null, null, null, null);
うん、無事成功した。
また、Redshiftでは、copyコマンドによりS3上のファイルをロードできるらしい。
このようなファイルをS3に上げて、
A001,あいうえお,123,123456.78,2015-05-20,2015-05-20 12:01:02 A002,12345678901234567890,123456789,12345678.12,2015-12-31,2015-12-31 23:59:59 A003,,,,,
SQLクライアントでcopyを実行する。
copy test1 from 's3://xxx/data/data1.csv' credentials 'aws_access_key_id=XXXXXXXX;aws_secret_access_key=XXXXXXXXXXXXXXXX' csv;
これも無事成功した。
embulk-output-redshiftでロードしてみる
Embulkには、embulk-output-redshiftというRedshift用のプラグインがあるので、これを使ってみる。
embulk-output-redshiftは、一旦S3にファイルをアップロードしてから、copyでRedshiftにロードする仕組みらしい。
ので、ymlファイルにはAWSのキーも記述する必要がある。
in: type: file path_prefix: 'data/data1.csv' parser: charset: UTF-8 newline: CRLF type: csv delimiter: ',' columns: - {name: id, type: string} - {name: varchar_item, type: string} - {name: integer_item, type: long} - {name: NUMERIC_ITEM, type: string} - {name: date_item, type: timestamp, format: '%Y-%m-%d'} - {name: timestamp_item, type: timestamp, format: '%Y-%m-%d %H:%M:%S'} out: type: redshift host: xxxxxxxx.us-east-1.redshift.amazonaws.com database: dev user: xxxxxxxx password: xxxxxxxx table: test1 mode: insert_direct access_key_id: XXXXXXXX secret_access_key: XXXXXXXXXXXXXXXX s3_bucket: xxx
で、実行してみたのだが、エラーだ。。
iam_user_nameは必須のようだ。
そこで、IAMユーザを準備する。
よく分からないが、とりあえずS3とRedshiftのアクセス権限は必要だろう。
in: type: file path_prefix: 'data/data1.csv' parser: charset: UTF-8 newline: CRLF type: csv delimiter: ',' columns: - {name: id, type: string} - {name: varchar_item, type: string} - {name: integer_item, type: long} - {name: NUMERIC_ITEM, type: string} - {name: date_item, type: timestamp, format: '%Y-%m-%d'} - {name: timestamp_item, type: timestamp, format: '%Y-%m-%d %H:%M:%S'} out: type: redshift host: xxxxxxxx.us-east-1.redshift.amazonaws.com database: dev user: xxxxxxxx password: xxxxxxxx table: test1 mode: insert_direct iam_user_name: redshift access_key_id: XXXXXXXX secret_access_key: XXXXXXXXXXXXXXXX s3_bucket: xxx
で、もう一度実行してみたのだが、またエラーだ。。
com.amazonaws.AmazonServiceException: User: arn:aws:iam::xxxxxxxx:user/redshift is not authorized to perform: sts:GetFederationToken on resource: arn:aws:sts::xxxxxxxx:federated-user/redshift (Service: AWSSecurityTokenService; Status Code: 403; Error Code: AccessDenied; Request ID: xxxxxxxx)
どうやらIAMユーザの権限が足りないようだ。
いろいろ調べた結果、実はRedshiftの権限は不要（AWSのAPIは使わずJDBCドライバでアクセスするだけなので）だったりして、結局以下のようなPolicyを定義した。
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"s3:GetObject",
"s3:PutObject",
"s3:ListBucket",
"sts:GetFederationToken"
],
"Resource": "*"
}
]
}
再度実行したら…、今度は成功だ！
embulk-output-jdbcでロードしてみる
embulk-output-redshiftはcopyを使うが、普通にinsertでもロードできるのではなかろうか？
と思って、embulk-output-jdbcも試してみることにした。
embulk-output-jdbcの場合は、JDBCドライバのパスやドライバクラス名、URLを全て記述する必要がある。
in: type: file path_prefix: 'data/data1.csv' parser: charset: UTF-8 newline: CRLF type: csv delimiter: ',' columns: - {name: id, type: string} - {name: varchar_item, type: string} - {name: integer_item, type: long} - {name: numeric_item, type: string} - {name: date_item, type: timestamp, format: '%Y-%m-%d'} - {name: timestamp_item, type: timestamp, format: '%Y-%m-%d %H:%M:%S'} out: type: jdbc driver_path: driver/RedshiftJDBC41.jar driver_class: com.amazon.redshift.jdbc41.Driver url: jdbc:redshift://xxxxxxxx.us-east-1.redshift.amazonaws.com:5439/dev database: dev user: xxxxxxxx password: xxxxxxxx table: test1 mode: insert_direct
で、実行してみると…、
2015-05-21 02:30:27.421 +0000 [INFO] (task-0000): Prepared SQL: INSERT INTO "test1" ("id", "varchar_item", "integer_item", "numeric_item", "date_item", "timesta mp_item") VALUES (?, ?, ?, ?, ?, ?) 2015-05-21 02:30:27.733 +0000 [INFO] (task-0000): Loading 3 rows
…止まってるな…。
いろいろ調べたところ、どうもデータに日本語が含まれてると止まるようだ。
また、PreparedStatement#addBatchを使わずにPreparedStatement#executeUpdateで1件ずつ更新しようとすると、以下のようなエラーが出た。
Caused by: java.sql.SQLException: [Amazon](500310) Invalid operation: String contains invalid or unsupported UTF8 codepoints. Bad UTF8 hex sequence: 82 (error 3); at com.amazon.redshift.client.messages.inbound.ErrorResponse.toErrorException(Unknown Source) ... at com.amazon.jdbc.common.SPreparedStatement.executeUpdate(Unknown Source)
82？
"あ"はUTF8だとE38182だが…、
シフトJISだと82A0だ。
ひょっとして、UTF8ではなく、WindowsデフォルトのシフトJISでエンコーディングされている？
試しに、エンコーディングにUTF8を指定して実行してみるか。
embulk -J-Dfile.encoding=UTF-8 run jdbc-redshift.yml
のようにすればよいはずだが、Windows版はまだ-Jオプションに対応していないようだ。
仕方ないので、以下のようにしてみた。
java -Dfile.encoding=UTF-8 -jar embulk.bat run jdbc-redshift.yml
ようやく成功した！
※なんかRedshiftのJDBCドライバのバグっぽい気がするなあ。もう少し調べるか…
embulk-output-postgresqlではロードできなかった
RedshiftはPostgreSQLベースで作られているらしいので、embulk-output-postgresqlも試してみることにした。
PostgreSQLのデフォルトポート番号は5432だが、Redshiftは5439なので、ポート番号は明示的に指定する必要がある。
in: type: file path_prefix: 'data/data1.csv' parser: charset: UTF-8 newline: CRLF type: csv delimiter: ',' columns: - {name: id, type: string} - {name: varchar_item, type: string} - {name: integer_item, type: long} - {name: NUMERIC_ITEM, type: string} - {name: date_item, type: timestamp, format: '%Y-%m-%d'} - {name: timestamp_item, type: timestamp, format: '%Y-%m-%d %H:%M:%S'} out: type: postgresql host: xxxxxxxx.us-east-1.redshift.amazonaws.com port: 5439 database: dev user: xxxxxxxx password: xxxxxxxx table: test1 mode: insert_direct
で、実行してみると、エラーだ…。
2015-05-28 05:59:49.431 +0000 [INFO] (task-0000): Copy SQL: COPY "test1" ("id", "varchar_item", "integer_item", "date_item", "timestamp_item") FROM STDIN 2015-05-28 05:59:49.556 +0000 [INFO] (task-0000): Loading 3 rows (144 bytes) 2015-05-28 05:59:50.711 +0000 [INFO] (transaction): {done: 1 / 1, running: 0} 2015-05-28 05:59:50.726 +0000 [INFO] (main): Transaction partially failed. Cleaning up the intermediate data. Use -r option to make it resumable. org.embulk.exec.PartialExecutionException: java.lang.RuntimeException: org.postgresql.util.PSQLException: ERROR: LOAD source is not supported. (Hint: only S3 or DynamoDB or EMR based load is allowed) at org.embulk.exec.BulkLoader$LoaderState.buildPartialExecuteException(org/embulk/exec/BulkLoader.java:331) at org.embulk.exec.BulkLoader.doRun(org/embulk/exec/BulkLoader.java:526) ...
embulk-output-postgresqlではCOPY ... FROM STDINによりデータをロードするが、どうやらRedshiftはサポートしていないようだ。
残念。
まとめ
Redshiftには、embulk-output-redshiftはもちろんのこと、embulk-output-jdbcでもロードすることができた。
次回はこれらのパフォーマンス等を比較してみたい。
| OS | Windows Server 2008 R2 |
| Embulk | 0.6.8 |
| embulk-output-jdbc | 0.3.0 |
| embulk-output-redshift | 0.3.0 |
| embulk-output-postgresql | 0.3.0 |
2015/5/28: embulk-output-postgresqlでの実行結果を追記しました。
