2017/12/01

Oracle DatabaseのテーブルをCSV出力する6つの方法

本記事は JPOUG Advent Calendar 2017 の1日目です。

Oracle Database でテーブルのデータを外部に出力するには Data Pump を使うことが一般的ですが、外部システムとデータ連係する場合には CSV 形式のフォーマットで行うことが多いと思います。そこで 2017 年現在だったら CSV 形式の出力にどのような方法があるのか試したときのメモになります。

環境

今回は Oracle Database 12.1.0.2 のプラガブルデータベース(PDB)の SCOTT スキーマの EMP テーブルをエクスポートする場合を想定します。

スキーマは Database 付属のサンプルスキーマ作成スクリプト(utlsampl.sql)で作成します。

$ export TWO_TASK=localhost/pdb
$ sqlplus system '@?/rdbms/admin/utlsampl.sql'
utlsampl.sql が作成する SCOTT.EMP テーブルについては Live SQL に近いものが登録されていますので、そこでどのようなものかはある程度確認できます。(BONUS テーブルが足りないようですが。。。)

1. SQL*Plus 文字列連結

まず浮かぶのは SQL の実行結果として CSV を生成する方法です。

Oracle Database のコマンドライン・ユーザー・インターフェースの SQL*Plus で区切り文字(,)による文字列連結を行う SQL を実行して、その出力を CSV ファイルとして保存します。

$ sqlplus scott/tiger@localhost/pdb
SET PAGESIZE 0 LINESIZE 32767 TRIMSPOOL ON FEEDBACK OFF
SPOOL ./emp.csv

SELECT empno || ',' || ename || ',' || job || ',' || mgr || ',' ||
       hiredate || ',' || sal || ',' || comm || ',' || deptno
FROM emp;

SPOOL OFF
EXIT

なお、この例のように対話型で SQL*Plus を実行した場合には SPOOL による出力ファイルには先頭と末尾に CSV データとしては不要な文字列があるので別途削除する必要があります。

SQL>
SQL> SELECT empno || ',' || ename || ',' || job || ',' || mgr || ',' ||
  2         hiredate || ',' || sal || ',' || comm || ',' || deptno
  3  FROM emp;
7369,SMITH,CLERK,7902,17-DEC-80,800,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20
7900,JAMES,CLERK,7698,03-DEC-81,950,,30
7902,FORD,ANALYST,7566,03-DEC-81,3000,,20
7934,MILLER,CLERK,7782,23-JAN-82,1300,,10
SQL>
SQL> SPOOL OFF
$ grep -v -E '^(SQL>| +[[:digit:]]+) ' < emp.csv > emp2.csv

基本的にこのような処理を行う場合はあらかじめ用意した SQL ファイルをバッチ実行する事になると思います。その場合は削除処理は不要です。

$ sqlplus scott/tiger@localhost/pdb @csv_export.sql

また SPOOL によるファイル出力ではなく、SQL*Plus の SILENT オプションを使用してバナーやプロンプトなどの不要な文字列を表示しないようにしたコマンド出力をシェルのリダイレクトでファイルに保存する方法もあります。この場合も削除処理は不要です。

$ sqlplus -S scott/tiger@localhost/pdb <<'SQL' > emp.csv
SET PAGESIZE 0 LINESIZE 32767 TRIMSPOOL ON FEEDBACK OFF
SELECT empno || ',' || ename || ',' || job || ',' || mgr || ',' ||
       hiredate || ',' || sal || ',' || comm || ',' || deptno
FROM emp;
SQL

単一テーブルの内容を出力したい場合には SQL をシンプルに SELECT * FROM emp などとしたいところですが、SQL*Plus は問い合わせ結果を表形式で出力するレポーティングツールとしての役割もあるため、列幅が固定長で出力されてしまい、CSV 形式での出力には向きません。

とはいえ SQL で出力データを制御しますので、複数のテーブルを結合したり、コード値の変換など自由にできるこの方法は多くの現場で利用されているのではないかと思います。

2. SQL*Plus 12.2 によるCSV出力

引き続きまた SQL*Plus です。(オンプレミス版では)2017 年 3 月にリリースされた Oracle Database リリース 12.2 の新機能として SQL*Plus に CSV 形式で出力するためのオプションが用意されました。

今回の環境ではデータベースのバージョンは 12.1 ですが、SQL*Plus のようなクライアントツールはより新しいバージョンを使うことも出来ます。

とはいえそのためだけに 12.2 の環境を準備するのは手間かも知れません。そんな時はフットプリントが軽量でインストールが容易なうえ主要なプラットフォーム向けに提供されている Oracle Instant Client が便利です。

Oracle Instant Client はいくつかのパッケージに分かれて配布されていますが、今回は基本と SQL*Plus を使用します。配布形態として RPM 形式と ZIP 形式があります。ZIP 形式は展開して環境変数を設定するだけで使用できます。

$ unzip instantclient-basic-linux.x64-12.2.0.1.0.zip
Archive:  instantclient-basic-linux.x64-12.2.0.1.0.zip
  inflating: instantclient_12_2/adrci
  inflating: instantclient_12_2/BASIC_README
  inflating: instantclient_12_2/genezi
  inflating: instantclient_12_2/libclntshcore.so.12.1
  inflating: instantclient_12_2/libclntsh.so.12.1
  inflating: instantclient_12_2/libipc1.so
  inflating: instantclient_12_2/libmql1.so
  inflating: instantclient_12_2/libnnz12.so
  inflating: instantclient_12_2/libocci.so.12.1
  inflating: instantclient_12_2/libociei.so
  inflating: instantclient_12_2/libocijdbc12.so
  inflating: instantclient_12_2/libons.so
  inflating: instantclient_12_2/liboramysql12.so
  inflating: instantclient_12_2/ojdbc8.jar
  inflating: instantclient_12_2/uidrvci
  inflating: instantclient_12_2/xstreams.jar

$ unzip instantclient-sqlplus-linux.x64-12.2.0.1.0.zip
Archive:  instantclient-sqlplus-linux.x64-12.2.0.1.0.zip
  inflating: instantclient_12_2/glogin.sql
  inflating: instantclient_12_2/libsqlplusic.so
  inflating: instantclient_12_2/libsqlplus.so
  inflating: instantclient_12_2/sqlplus
  inflating: instantclient_12_2/SQLPLUS_README
$ cd instantclient_12_2

$ export LD_LIBRARY_PATH=$PWD

$ ./sqlplus scott/tiger@localhost/pdb
以下は MARKUP オプションを使用した CSV 出力の例です。
  • デフォルトでは非数値型は " でクォートされる
  • 区切り文字は変更できる
SQL> SET MARKUP CSV ON
SQL> SELECT * FROM emp FETCH FIRST 1 ROW ONLY;

"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,"SMITH","CLERK",7902,"17-DEC-80",800,,20

SQL> SET MARKUP CSV ON QUOTE OFF
SQL> /

EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
7369,SMITH,CLERK,7902,17-DEC-80,800,,20

SQL> SET MARKUP CSV ON DELIMITER |
SQL> /

EMPNO|ENAME|JOB|MGR|HIREDATE|SAL|COMM|DEPTNO
7369|SMITH|CLERK|7902|17-DEC-80|800||20

SQL> SET MARKUP CSV ON DELIMITER | QUOTE ON
SQL> /

"EMPNO"|"ENAME"|"JOB"|"MGR"|"HIREDATE"|"SAL"|"COMM"|"DEPTNO"
7369|"SMITH"|"CLERK"|7902|"17-DEC-80"|800||20

他にも次のような特徴があります。
-- 従来の MARKUP CSV を使用しない場合、LINESIZE(80) で折り返され、PAGESIZE(14) ごとに列ヘッダが出力される
SQL> SELECT '"' AS "quote", ',' AS "delimiter", 'a' || CHR(10) || 'b' AS "newline",
            RPAD('very l', 1000, 'o') || 'ng' AS "long" from dual;

q d new
- - ---
long
--------------------------------------------------------------------------------
" , a
    b
very loooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo
oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo
oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo
oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo
oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo
oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo
oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo

q d new
- - ---
long
--------------------------------------------------------------------------------
oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo
oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo
oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo
oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo
oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo
oooooooooooooooooooooooooooooooooooooooong


SQL> SET MARKUP CSV ON
SQL> /

"quote","delimiter","newline","long"
"""",",","a
b","very loooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooong"

SQL> SET MARKUP CSV ON QUOTE OFF
SQL> /

quote,delimiter,newline,long
",,,a
b,very loooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooong

少し気になる点として、いままでの出力結果でお気づきかも知れませんが、 MARKUP CSV ON した場合には必ず1行目が改行されてから列名が出力されるようです。

そのため前項と同じくデータ行のみを出力したい場合には、PAGESIZETRIMSPOOL の指定は不要になりますが、HEADING OFF とあわせて使うことになるかと思います。ちなみに MARKUP は SQL*Plus の SET システム変数として以外にコマンドラインオプションとしても指定できるようです。

$ ./sqlplus -S -M 'csv on quote off' scott/tiger@localhost/pdb <<'SQL' > emp.csv
SET HEADING OFF FEEDBACK OFF
SELECT * FROM emp;
SQL

3. SQL Developer のデータベース・エクスポート(データのアンロード)

SQL Developer は Oracle が提供しているデータベース開発ツールです。Oracle Database のテーブルやインデックスなどオブジェクトの閲覧・作成、データファイルの追加やバックアップジョブ作成などの管理作業、AWR レポートやリアルタイム SQL 監視などによる診断・チューニング、PL/SQL による開発・テスト、データモデリング、データベースマイグレーションなど、データベースに関する幅広い用途に使用できます。

Oracle Database はもちろん、ドライバを用意すれば MySQL や TimesTen などの Oracle 製のデータベースをはじめ、Amazon Redshift、Teradata、PostgreSQL などにも接続できます。

このツールは Java アプリケーションですので Java ランタイムが動作する環境であれば使用できます。ここでは macOS で SQL Developer 17.3 を使ったデータベース・エクスポート(データのアンロード)の手順を紹介します。

GUI よるウィザード形式で手軽に出来ます。データベースに接続して左側のテーブル一覧から該当テーブルを右クリックして「Export」を選ぶか、テーブルオブジェクトを右側に表示したときに表示される「Actions」メニューで「Export」を選んでウィザードを起動します。

CSV 形式のデータ出力ですので「Export DDL」のチェックを外して「Export Data」の「Format」で「csv」を選択します。

次の画面でエクスポート対象オブジェクトの選択や列の指定、エクスポート対象全体にフィルタリングとして指定する WHERE 句が指定できます。今回は個別のテーブルの全てのデータを出力しますので何も指定しません。

最後に処理前の確認です。「Finish」を押すとエクスポート処理が開始されます。

今回は出力フォーマットとして「csv」を選んだので区切り文字(Delimiter)は , 固定でしたが、出力フォーマットとして「delimited」を選ぶと区切り文字が選べるようになります。この選択に使用するプルダウン項目は入力可能ですので、あらかじめ決められている値以外に任意の文字列を指定できます。ちなみに手元の環境では「space」は半角スペース 1 個、「whitespace」は半角スペース 2 個でした。

エクスポートウィザードで選択できる出力フォーマットは「csv」「delimited」以外に次のようなものがありますので用途によって使い分けると手間が省けるかも知れません。

  • excel 2003+ (xlsx)
  • excel 95-2003 (xls)
  • excel.xml
  • fixed
  • html
  • insert ※対象データを INSERT するための DML
  • json
  • loader ※対象データを SQL*Loader でロードするための制御ファイルとデータファイル
  • pdf
  • text
  • xml

また、テーブル単体ごとではなく問い合わせ結果をエクスポートしたい場合には SQL Worksheet に SELECT 文を入力して Run Statement した結果(Query Result)を右クリックしてエクスポートできます。

4. SQL Developer のスクリプト出力

SQL Developer の Worksheet では、入力(あるいは選択)した SQL の実行(Run Statement)だけではなく、入力全体を PL/SQL 文および幾つかの SQL*Plus の変数やコマンドとして実行(Run Script)し、その結果の出力を Script Output で確認できます。

ここで実行する SELECT 文に SQL Develper が独自に解釈するヒント句のような SQL コメントを付与すると、出力フォーマットを CSV など様々な形式で出力できます。

  • SQL Worksheet "Hints" for Formatting Output - SQL Developer Concepts and Usage
    SELECT /*ansiconsole*/ * FROM books; -- Best appearance for ANSI terminal display
    SELECT /*csv*/ * FROM books; -- Comma-separated values
    SELECT /*delimited*/ * FROM books; -- (same as csv)
    SELECT /*fixed*/ * FROM books; -- Fixed-width fields with trailing blanks
    SELECT /*html*/ * FROM books; -- Marked-up HTML table
    SELECT /*insert*/ * FROM books; -- SQL INSERT statements
    SELECT /*json*/ * FROM books; -- JSON object format
    SELECT /*loader*/ * FROM books; -- Pipe-delimited format suitable for SQL*Loader
    SELECT /*text*/ * FROM books; -- Plain text
    SELECT /*xml*/ * FROM books; -- Tagged XML

この方法はコメントとは言え SQL 文本体に手を入れるため多少使いづらいところがあります。その後 SQL Developer 4.1 で出力フォーマットの指定が SQLFORMAT システム変数としても実装されましたので、Run Script の結果を用いる場合にはこちらの方が使い勝手はよいかも知れません。

SQL Developer の Worksheet を用いた CSV 出力についての動画による解説は以下のページで確認できます。

5. SQL Developer Command Line (SQLcl) によるCSV出力

引き続き SQL Developer 関連です。SQL Developer はとても便利なツールですが、多機能がゆえに起動に時間がかかったり、GUI なので実行する環境が限られる、など気軽に実行できない場合もあります。そんな要望に応えるべく?機能の一部を切り出して SQL*Plus の上位互換となるようなコマンドラインツールとして提供されたのが SQLcl です。

SQLcl は Oracle Database リリース 12.2 に同梱されていますが、SQL Developer がそうであったように製品版に同梱されるものは単体で提供されているものに比べるとどうしてもバージョンが古く(同梱版のバージョンは 12.2.0.1.0 RC) 、また SQLcl は頻繁にバージョンアップされるため、使用する場合は最新バージョンをダウンロードすることをオススメします。

このツールも Java アプリケーションですので Java 8 のランタイムが動作する環境であれば使用できます(現時点では Java 9 は公式サポート外)。ここでは macOS で SQLcl 17.3 を使った CSV 形式での出力手順を紹介します。

SQLcl は配布されている ZIP ファイルを展開するだけですぐに使用できます。展開したファイルの中に JDBC ドライバや必要なライブラリ、コマンドを実行するためのシェルスクリプトやバッチファイルなどが含まれています。

% unzip sqlcl-17.3.0.271.1943-no-jre.zip
Archive:  sqlcl-17.3.0.271.1943-no-jre.zip
  inflating: sqlcl/bin/sql
  inflating: sqlcl/bin/sql.bat
  inflating: sqlcl/bin/sql.exe
  inflating: sqlcl/lib/antlr-runtime.jar
  inflating: sqlcl/lib/commons-codec.jar
  inflating: sqlcl/lib/commons-logging.jar
  inflating: sqlcl/lib/httpclient.jar
  inflating: sqlcl/lib/httpcore.jar
  inflating: sqlcl/lib/httpmime.jar
  inflating: sqlcl/lib/jackson-annotations.jar
  inflating: sqlcl/lib/jackson-core.jar
  inflating: sqlcl/lib/jackson-databind.jar
  inflating: sqlcl/lib/javax.json.jar
  inflating: sqlcl/lib/jline.jar
  inflating: sqlcl/lib/jsch.jar
  inflating: sqlcl/lib/ojdbc8.jar
  inflating: sqlcl/lib/oracle.dbtools-common.jar
  inflating: sqlcl/lib/oracle.dbtools.http.jar
  inflating: sqlcl/lib/oracle.dbtools.jdbcrest.jar
  inflating: sqlcl/lib/oracle.sqldeveloper.sqlcl.jar
  inflating: sqlcl/lib/oraclepki.jar
  inflating: sqlcl/lib/orai18n-collation.jar
  inflating: sqlcl/lib/orai18n-mapping.jar
  inflating: sqlcl/lib/orai18n-servlet.jar
  inflating: sqlcl/lib/orai18n-utility.jar
  inflating: sqlcl/lib/orai18n.jar
  inflating: sqlcl/lib/orajsoda.jar
  inflating: sqlcl/lib/osdt_cert.jar
  inflating: sqlcl/lib/osdt_core.jar
  inflating: sqlcl/lib/stringtemplate.jar
  inflating: sqlcl/lib/xdb6.jar
  inflating: sqlcl/lib/xmlparserv2.jar
展開した先の sqlcl/bin ディレクトリにある sql ファイルが SQLcl を実行するスクリプトになります。

SQLcl の基本的な使用方法は SQL*Plus と同じで、SQL Developer の Worksheet に入力できる文と同じものが使用できます。

% sqlcl/bin/sql -silent scott/tiger@ol64.vbox/pdb <<'SQL' > emp.csv
SET SQLFORMAT CSV
SET HEADING OFF FEEDBACK OFF
SELECT * FROM emp;
SQL
今回試して気づきましたが、シェルによる出力のリダイレクトだと HEADING OFF しているにも関わらず1行目には空行が出力されます。それが嫌な場合は SPOOL でファイル出力するとスッキリします。
% sqlcl/bin/sql -silent scott/tiger@ol64.vbox/pdb <<'SQL'
SET SQLFORMAT CSV
SET HEADING OFF FEEDBACK OFF
SPOOL ./emp.csv
SELECT * FROM emp;
SQL

6. EmbulkによるCSV出力

ここまで Oracle 製のツールを紹介してきましたが、最後にオープンソースのデータ転送ツール Embulk を使用した例を紹介します。

このツールは Java アプリケーションですので Java のランタイムが必要です。ここでは macOS で Embulk 0.8.38 を使った CSV 出力の手順を紹介します。

インストールそのものはドキュメントの手順にあるとおり、ダウンロードしたファイルに実行権限を付与してコマンド実行パスを設定するだけです。

% curl --create-dirs -o ~/.embulk/bin/embulk -L "https://dl.embulk.org/embulk-latest.jar"
% chmod +x ~/.embulk/bin/embulk
% export PATH=$HOME/.embulk/bin:$PATH
Embulk はプラグインにより柔軟に入力と出力を組み合わせられます。今回は入力を Oracle Database に適した Java によるデータベース接続(JDBC)にしますので embulk-input-oracle プラグインをインストールします。CSV 形式でのファイル出力はデフォルトで対応されています。
% embulk gem install embulk-input-oracle
Oracle Database への接続に使用する JDBC ドライバは Oracle Instant Client の形式でも配布されていますが、今回は単体の JDBC ドライバ(ojdbc8.jar)をダウンロードしてカレントディレクトリに配置します。 そして次のような Embulk の設定ファイル(csv_export.yml)を用意します。それぞれの項目についてはドキュメントをご確認ください。
exec:
  min_output_tasks: 1

in:
  type: oracle
  driver_path: ./ojdbc8.jar
  url: jdbc:oracle:thin:@//ol64.vbox/pdb
  user: scott
  password: tiger
  table: emp

out:
  type: file
  path_prefix: ./emp
  sequence_format: '.'
  file_ext: csv
  formatter:
    type: csv
まずは設定が正しいかプレビュー(preview)します。
% embulk preview ./csv_export.yml
2017-12-01 01:06:23.875 +0900: Embulk v0.8.38

********************************** INFORMATION **********************************
  Join us! Embulk-announce mailing list is up for IMPORTANT annoucement such as
    compatibility-breaking changes and key feature updates.
  https://groups.google.com/forum/#!forum/embulk-announce
*********************************************************************************

2017-12-01 01:06:27.393 +0900 [INFO] (0001:preview): Loaded plugin embulk-input-oracle (0.8.6)
2017-12-01 01:06:27.873 +0900 [INFO] (0001:preview): Using JDBC Driver 12.2.0.1.0
2017-12-01 01:06:28.058 +0900 [INFO] (0001:preview): SQL: SELECT * FROM "EMP"
2017-12-01 01:06:28.061 +0900 [INFO] (0001:preview): > 0.00 seconds
+--------------+--------------+------------+------------+-------------------------+------------+-------------+---------------+
| EMPNO:double | ENAME:string | JOB:string | MGR:double |      HIREDATE:timestamp | SAL:double | COMM:double | DEPTNO:double |
+--------------+--------------+------------+------------+-------------------------+------------+-------------+---------------+
|       7369.0 |        SMITH |      CLERK |     7902.0 | 1980-12-16 15:00:00 UTC |      800.0 |             |          20.0 |
|       7499.0 |        ALLEN |   SALESMAN |     7698.0 | 1981-02-19 15:00:00 UTC |     1600.0 |       300.0 |          30.0 |
|       7521.0 |         WARD |   SALESMAN |     7698.0 | 1981-02-21 15:00:00 UTC |     1250.0 |       500.0 |          30.0 |
|       7566.0 |        JONES |    MANAGER |     7839.0 | 1981-04-01 15:00:00 UTC |     2975.0 |             |          20.0 |
|       7654.0 |       MARTIN |   SALESMAN |     7698.0 | 1981-09-27 15:00:00 UTC |     1250.0 |      1400.0 |          30.0 |
|       7698.0 |        BLAKE |    MANAGER |     7839.0 | 1981-04-30 15:00:00 UTC |     2850.0 |             |          30.0 |
|       7782.0 |        CLARK |    MANAGER |     7839.0 | 1981-06-08 15:00:00 UTC |     2450.0 |             |          10.0 |
|       7788.0 |        SCOTT |    ANALYST |     7566.0 | 1987-04-18 15:00:00 UTC |     3000.0 |             |          20.0 |
|       7839.0 |         KING |  PRESIDENT |            | 1981-11-16 15:00:00 UTC |     5000.0 |             |          10.0 |
|       7844.0 |       TURNER |   SALESMAN |     7698.0 | 1981-09-07 15:00:00 UTC |     1500.0 |         0.0 |          30.0 |
|       7876.0 |        ADAMS |      CLERK |     7788.0 | 1987-05-22 15:00:00 UTC |     1100.0 |             |          20.0 |
|       7900.0 |        JAMES |      CLERK |     7698.0 | 1981-12-02 15:00:00 UTC |      950.0 |             |          30.0 |
|       7902.0 |         FORD |    ANALYST |     7566.0 | 1981-12-02 15:00:00 UTC |     3000.0 |             |          20.0 |
|       7934.0 |       MILLER |      CLERK |     7782.0 | 1982-01-22 15:00:00 UTC |     1300.0 |             |          10.0 |
+--------------+--------------+------------+------------+-------------------------+------------+-------------+---------------+
データが表示されることから、データベースへの接続には問題無さそうです。EMP テーブルだと件数が少ないのでプレビューで全て表示されています。設定ファイルに問題は無さそうなので実行(run)します。
% embulk run ./csv_export.yml
2017-12-01 01:13:52.821 +0900: Embulk v0.8.38

********************************** INFORMATION **********************************
  Join us! Embulk-announce mailing list is up for IMPORTANT annoucement such as
    compatibility-breaking changes and key feature updates.
  https://groups.google.com/forum/#!forum/embulk-announce
*********************************************************************************

2017-12-01 01:13:57.529 +0900 [INFO] (0001:transaction): Loaded plugin embulk-input-oracle (0.8.6)
2017-12-01 01:13:58.208 +0900 [INFO] (0001:transaction): Using JDBC Driver 12.2.0.1.0
2017-12-01 01:13:58.300 +0900 [INFO] (0001:transaction): Using local thread executor with max_threads=16 / tasks=1
2017-12-01 01:13:58.328 +0900 [INFO] (0001:transaction): {done:  0 / 1, running: 0}
2017-12-01 01:13:58.375 +0900 [INFO] (0019:task-0000): Writing local file './emp.csv'
2017-12-01 01:13:58.442 +0900 [INFO] (0019:task-0000): SQL: SELECT * FROM "EMP"
2017-12-01 01:13:58.448 +0900 [INFO] (0019:task-0000): > 0.00 seconds
2017-12-01 01:13:58.456 +0900 [INFO] (0001:transaction): {done:  1 / 1, running: 0}
2017-12-01 01:13:58.462 +0900 [INFO] (main): Committed.
2017-12-01 01:13:58.463 +0900 [INFO] (main): Next config diff: {"in":{},"out":{}}
プレビューの出力でお気づきかも知れませんが、この手順で出力した場合は数値や日時などは JDBC の型に合わせたフォーマットで出力されており、SQL*Plus などで出力したものとは異なっています。
% head -n 2 emp.csv
EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
7369.0,SMITH,CLERK,7902.0,1980-12-16 15:00:00.000000 +0000,800.0,,20.0
出力フォーマットを揃えるには、入力と出力のそれぞれに column_options を設定する必要があります。
exec:
  min_output_tasks: 1

in:
  type: oracle
  driver_path: ./ojdbc8.jar
  url: jdbc:oracle:thin:@//ol64.vbox/pdb
  user: scott
  password: tiger
  table: emp
  column_options:
    empno: { type: long }
    mgr: { type: long }
    sal: { type: long }
    comm: { type: long }
    deptno: { type: long }

out:
  type: file
  path_prefix: ./emp
  sequence_format: '.'
  file_ext: csv
  formatter:
    type: csv
    header_line: false
    column_options:
      HIREDATE: { format: '%d-%^b-%y', timezone: 'Asia/Tokyo' }
% head -n 2 emp.csv
7369,SMITH,CLERK,7902,17-DEC-80,800,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30

おわりに

Oracle Database では CSV 形式の出力は意外と手間ですが、プログラミング言語によるコーディングをせずに、ツール(と設定)で行える方法を 6 つほど紹介させていただきました。

個人的にはカジュアルに出力するなら SQL*Plus 12.2、テーブル単体ではなくクエリで色々試しながらだと SQL Developer、決定したクエリを定型ジョブなどで実行するなら Embulk かなと思いました。

2日目は charade_oo4o さんです。

0 件のコメント:

コメントを投稿