本記事は JPOUG Advent Calendar 2017 の１日目です。
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 形式で出力するためのオプションが用意されました。
- SQL*Plusユーザーズ・ガイドおよびリファレンス リリース2 (12.2)
- このリリースにおけるSQL*Plusの変更点
CSV形式データのサポート。SET MARKUPコマンドには、データをCSV形式で出力するためのCSVオプションが用意されています。
- SET MARK[UP]
SET MARK[UP] CSV {ON|OFF} [DELIMI[TER] character] [QUOTE {ON|OFF}]
- このリリースにおけるSQL*Plusの変更点
今回の環境ではデータベースのバージョンは 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他にも次のような特徴があります。
- 文字列中の
"はエスケープ（""）される - 文字列中の区切り文字はエスケープされない
- 文字列中の改行は改行として出力される
LINESIZEを超えても改行されずに出力される- （参考）Standard Edition 2でも使えるOracle Database 12c Release 2オススメ新機能
-- 従来の 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 した場合には必ず１行目が改行されてから列名が出力されるようです。
そのため前項と同じくデータ行のみを出力したい場合には、PAGESIZE や TRIMSPOOL の指定は不要になりますが、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 でロードするための制御ファイルとデータファイル
- 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 は頻繁にバージョンアップされるため、使用する場合は最新バージョンをダウンロードすることをオススメします。
- Updated SQLcl: 2 New Commands, Bug Fixes, and 12cR2 Oh My! | ThatJeffSmith
- Version 17.3(.1!) is Now Available | ThatJeffSmith
このツールも 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 しているにも関わらず１行目には空行が出力されます。それが嫌な場合は 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:$PATHEmbulk はプラグインにより柔軟に入力と出力を組み合わせられます。今回は入力を Oracle Database に適した Java によるデータベース接続（JDBC）にしますので
embulk-input-oracle プラグインをインストールします。CSV 形式でのファイル出力はデフォルトで対応されています。
% embulk gem install embulk-input-oracleOracle Database への接続に使用する JDBC ドライバは Oracle Instant Client の形式でも配布されていますが、今回は単体の JDBC ドライバ（ojdbc8.jar）をダウンロードしてカレントディレクトリに配置します。 そして次のような Embulk の設定ファイル（csv_export.yml）を用意します。それぞれの項目についてはドキュメントをご確認ください。
- Local executor plugin - Configuration — Embulk 0.8 documentation
- embulk-input-jdbc/embulk-input-oracle at master · embulk/embulk-input-jdbc
- File output plugin - Configuration — Embulk 0.8 documentation
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 かなと思いました。
２日目は charade_oo4o さんです。
0 件のコメント:
コメントを投稿