Oracleでembulk-input-jdbcを試してみた - 今日もプログラミング

※2016/6/23: この記事は古いので、こちらをご参照ください。

embulk-output-oracleもだいぶ良くなってきたので、そろそろinputの方も試してみたい。

どうやらembulk-input-oracleは無いようなので、汎用的なembulk-input-jdbcを使ってみることにした。

準備

まず、テーブルを準備する。

とりあえずは、比較的よく使われそうな型を用意した。

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)
);

で、テストデータを用意して、

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: timestamp, format: '%Y-%m-%d %H:%M:%S.%N'}
out:
    type: oracle
    host: localhost
    database: TESTDB
    user: TEST_USER
    password: test_pw
    table: INPUT_TEST
    mode: insert
    insert_method: normal
    driver_path: 'driver/ojdbc7.jar'

一応SELECTして確認してみると…、あれ？TIME9のマイクロ秒未満が切り捨てられている？

org.embulk.spi.time.TimestampParserのソースを見た感じだと、マイクロ秒未満には対応していないようだ。

かといって、org.embulk.output.jdbc.setter.SqlTimestampColumnSetterを見ると、stringの値をTIMESTAMP列に設定するのにも対応していない。

仕方ないので、とりあえずは直接UPDATEしおくか。

UPDATE INPUT_TEST SET TIME9='2015-04-24 01:02:03.12345678' WHERE ID=2;
UPDATE INPUT_TEST SET TIME9='2015-12-31 23:59:59.123456789' WHERE ID=3;
COMMIT;

embulk-input-jdbcでデータを取り出す

embulk-input-jdbcのドキュメントを見ると、csv parserのように列を定義しなくていいみたいだ。

楽ちん。

in:
  type: jdbc
  driver_path: driver/ojdbc7.jar
  driver_class: oracle.jdbc.driver.OracleDriver
  url: jdbc:oracle:thin:@localhost:1521:TESTDB
  user: TEST_USER
  password: test_pw
  table: INPUT_TEST
  select: "*"

out:
  type: file
  path_prefix: input-oracle
  file_ext: csv
  formatter:
    type: csv

で、実行してみた結果がこれ。

ID,NUM,STR,VARSTR,DT,TIME0,TIME6,TIME9
1.0,,,,,,,
2.0,123.4,chr1    ,varchr1,2015-04-24 00:00:00,2015-04-24 01:02:03,2015-04-24 01:02:03,2015-04-23 16:02:03
3.0,1.23456789012E9,chr12345,varchr12,2015-12-31 00:00:00,2015-12-31 23:59:59,2015-12-31 23:59:59,2015-12-31 14:59:59

気になる点が幾つかあるなあ。

気になった点

整数なのに小数点以下が出力されている

NUM列はNUMBER(8,0)型なので整数だが、NUMBERはdoubleにマッピングされている。

doubleは小数点以下が0でも、toStringすると.0が付いてしまう。

doubleは有効数字の問題もあるので、本当はBigDecimalにマッピングしたいんだけど、embulkにはBigDecimal型が無いので、とりあえずはStringにマッピングしておくのが一番正確か。

大きな数値が指数表記で出力されている

3行目のNUMは、元は1234567890.12なんだが、1.23456789012E9のように指数表記で出力された。

数値はdoubleで扱われているので、大きな数値や小さな数値は指数表記になってしまう。

DATE型なのに時分秒が出力されている

のだが…、ちょっとこの問題はややこしい。

OracleのDATE型は実は時分秒まで持っている。

例えば、

INSERT INTO INPUT_TEST(ID, DT) VALUES(11, TO_DATE('2015-04-24 11:11:11', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO INPUT_TEST(ID, DT) VALUES(22, TO_DATE('2015-04-24 22:22:22', 'YYYY-MM-DD HH24:MI:SS'));

のように挿入して、単純にSELECTすると、

SELECT ID, DT FROM INPUT_TEST;
        ID DT
---------- --------
        11 15-04-24
        22 15-04-24

日付までしか表示されないのだが、TO_CHARを使うと、

SELECT ID, TO_CHAR(DT, 'YYYY-MM-DD HH24:MI:SS') FROM INPUT_TEST;
        ID DT
---------- --------
        11 2015-04-24 11:11:11
        22 2015-04-24 22:22:22

ちゃんと時分秒まで格納されているのが分かる。

デフォルトでは日付まで出力して、設定により秒まで出力する、とかできるといいのだが…。

なお、DATE型のデフォルトのフォーマットは、NLS_DATE_FORMATというパラメータで定義されるらしい。

TIMESTAMPの秒未満値が出力されない

org.embulk.standards.CsvFormatterPluginのソースを見ると、schemaの中にフォーマットの情報が格納されているようだ。

    TimestampType tt = (TimestampType) column.getType();
    builder.put(column.getIndex(), new TimestampFormatter(tt.getFormat(), task));

で、schemaを作っているのは、org.embulk.input.jdbc.AbstractJdbcInputPlugin#setupTaskあたり。

ここで、TimestampColumnGetter#getToTypeが呼ばれ、TimestampTypeが返される。

TimestampColumnGetter(org.embulk.input.jdbc.getter.ColumnGettersの内部クラス)のソースを見ると…、

    return Types.TIMESTAMP.withFormat("%Y-%m-%d %H:%M:%S")

となっており、固定で秒までのフォーマットを返していた。

列のメタデータから桁数をとって、それに応じたフォーマットを返すようにできないかな。

型の定義とかJDBCで取れるメタデータはDBMS毎に異なるので、やはりOracle用のembulk-input-oracleを作った方がよいかもしれない。

embulk	0.6.5
embulk-input-jdbc	0.4.0

2015/4/27: 「大きな数値が指数表記で出力されている」を追記しました。