Sqoop Oracleサンプル

SqoopでOracleを扱う例。
（基本的な使い方はMySQLを対象にする場合と同じであり、MySQLの方で詳しく試している）

準備

ここでは、SqoopでOracle11gにアクセスする。
HadoopとSqoopとOracle11gを同一マシン（CentOS）上で稼動させる。
Hadoopはスタンドアローン環境。

OracleのJDBCドライバーをSqoopのlibに配置しておく必要がある。

# cp -p $ORACLE_HOME/jdbc/lib/ojdbc6.jar /usr/lib/sqoop/lib/

Oracleでサンプル用のユーザー（DB）とテーブルを作っておく。

$ sqlplus sys as sysdba

SQL> create user hishidama identified by hishidama;

User created.

SQL> grant connect,resource to hishidama;

Grant succeeded.

※OraOopを使う場合は、dbaロールも加えておく方がよい。

$ sqlplus hishidama

SQL> create table table1 (
       key1   char(4),
       data1  varchar(10),
       num1   number(9),
       primary key(key1)
     );
SQL> desc table1

SQL> insert into table1 values('A001', 'test', 123);
     insert into table1 values('A002', 'aa,bb"cc', 456);
     insert into table1 values('A003', '', null);
     insert into table1 values('A004', null, null);
SQL> select * from table1;
SQL> commit;

テストデータの中にカンマとダブルクォーテーションおよびnullを含めてみた。

import

インポート（DB→HDFS）を実行してみる。

$ sqoop import --connect jdbc:oracle:thin:@localhost:1521:orcl --username hishidama --password hishidama --table table1
〜
12/06/18 22:50:30 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM table1 t WHERE 1=0
12/06/18 22:50:30 ERROR tool.ImportTool: Imported Failed: Attempted to generate class with no columns!

いきなりエラー。
『HADOOP HACKS』p.49によると、Sqoopでは、テーブル名の大文字小文字を区別するらしい。
普通のOracleのSQLでは、テーブル名（等の識別子）を小文字で書いても大文字に変換して認識してくれるのだが、Sqoopはそうではないらしい。
したがって、テーブル名を大文字で指定してやれば上手くいく。
ただし、テーブル名だけでは駄目で、ユーザー名も大文字にする必要がある。（パスワードはOracle11gでは最初から大文字小文字が区別されるので、そのままでよい）

$ sqoop import --connect jdbc:oracle:thin:@localhost:1521:orcl --username HISHIDAMA --password hishidama --table TABLE1

正常に終了すると、テーブル名のディレクトリーと、テーブル名のJavaファイルが出来ている。
（→Javaソースを生成させない方法）

$ ls
TABLE1  TABLE1.java

$ cat TABLE1/part-m-00000
A001,test,123
A002,aa,bb"cc,456
A003,null,null
A004,null,null

他のオプションについてはMySQLのimportと同じ。

export

エクスポート（HDFS→DB）を実行してみる。

$ cat dir1csv/data.txt 
A001,"test",123
A002,"aa,bb\"cc",456
A003,"",null
A004,null,null
A005,NULL!!!,null

$ sqoop export --connect jdbc:oracle:thin:@localhost:1521:orcl --username HISHIDAMA --password hishidama --table TABLE1 \
  --input-fields-terminated-by ',' --input-lines-terminated-by '\n' --input-escaped-by '\\' --input-optionally-enclosed-by '\"' \
  --input-null-string 'NULL!!!' \
  --export-dir dir1csv

SQL> select * from table1 ;

KEY1 DATA1            NUM1
---- ---------- ----------
A001 test              123
A002 aa,bb"cc          456
A003
A004 null
A005

export実行時も、importと全く同じJavaソースが生成される。→Javaソースを生成させない方法

exportでもテーブル名やユーザー名を小文字にすると、importと同じくエラーになる。

12/06/18 23:09:19 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM table1 t WHERE 1=0
12/06/18 23:09:19 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.IllegalArgumentException: Attempted to generate class with no columns!
java.lang.IllegalArgumentException: Attempted to generate class with no columns!
	at com.cloudera.sqoop.orm.ClassWriter.generateClassForColumns(ClassWriter.java:1241)
	…

バッチモード

「--batch」を指定するとバッチモードになる。[2012-06-19]

$ sqoop export --connect jdbc:oracle:thin:@localhost:1521:orcl --username HISHIDAMA --password hishidama --table TABLE1 \
  --input-fields-terminated-by ',' --input-lines-terminated-by '\n' --input-escaped-by '\\' --input-optionally-enclosed-by '\"' --input-null-string 'NULL!!!' \
  --batch \
  --export-dir dir1csv

デフォルトモードでは、Oracle以外では複数VALUES INSERTを使う。

INSERT INTO テーブル VALUES (値の組1), (値の組2), …, (値の組100);

しかしOracleには複数VALUESの文法は無いので、代わりにSELECT〜INSERTが使われる。

INSERT INTO テーブル
SELECT 値1-1, …, 値1-n FROM DUAL UNION ALL
SELECT 値2-1, …, 値2-n FROM DUAL UNION ALL
〜
SELECT 値100-1, …, 値100-n FROM DUAL;

バッチモードにすると、（MySQLやPostgreSQLのバッチモードと同じく）PreparedStatementのaddBatch()・executeBatch()を使った 普通のINSERT VALUESになる。

更新モード

exportは基本的にINSERTだが、UPDATEを使うことも出来る。

$ sqoop export --connect jdbc:oracle:thin:@localhost:1521:orcl --username HISHIDAMA --password hishidama --table TABLE1 \
  --input-fields-terminated-by ',' --input-lines-terminated-by '\n' --input-escaped-by '\\' --input-optionally-enclosed-by '\"' --input-null-string 'NULL!!!' \
  --update-key KEY1 \
  --export-dir dir1csv

--update-keyを指定すると、そのカラムをWHERE条件としたUPDATE文でテーブルが更新される。
（カンマ区切りで複数のカラムを指定可能）

--update-keyに指定するカラム名も大文字にする必要がある。小文字だと、NullPointerExceptionが起きる。

$ sqoop export --connect jdbc:oracle:thin:@localhost:1521:orcl --username HISHIDAMA --password hishidama --table TABLE1 \
  --input-fields-terminated-by ',' --input-lines-terminated-by '\n' --input-escaped-by '\\' --input-optionally-enclosed-by '\"' --input-null-string 'NULL!!!' \
  --update-key key1 \
  --export-dir dir1csv
〜
12/06/18 23:17:01 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.NullPointerException
java.lang.NullPointerException
	at com.cloudera.sqoop.orm.ClassWriter.generateDbWrite(ClassWriter.java:631)
	…

--verboseを付けると、もう少し詳しい情報が分かる。

$ sqoop export --verbose --connect jdbc:oracle:thin:@localhost:1521:orcl --username HISHIDAMA --password hishidama --table TABLE1 \
  --input-fields-terminated-by ',' --input-lines-terminated-by '\n' --input-escaped-by '\\' --input-optionally-enclosed-by '\"' --input-null-string 'NULL!!!' \
  --update-key key1 \
  --export-dir dir1csv
〜
12/06/18 23:19:52 DEBUG orm.ClassWriter: selected columns:
12/06/18 23:19:52 DEBUG orm.ClassWriter:   KEY1
12/06/18 23:19:52 DEBUG orm.ClassWriter:   DATA1
12/06/18 23:19:52 DEBUG orm.ClassWriter:   NUM1
12/06/18 23:19:52 DEBUG orm.ClassWriter: db write column order:
12/06/18 23:19:52 DEBUG orm.ClassWriter:   DATA1
12/06/18 23:19:52 DEBUG orm.ClassWriter:   NUM1
12/06/18 23:19:52 DEBUG orm.ClassWriter:   key1
12/06/18 23:19:52 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.NullPointerException

--update-mode allowinsertを付けると、転送先テーブルにデータの無いレコードの場合はINSERTしてくれる。

$ sqoop export --connect jdbc:oracle:thin:@localhost:1521:orcl --username HISHIDAMA --password hishidama --table TABLE1 \
  --input-fields-terminated-by ',' --input-lines-terminated-by '\n' --input-escaped-by '\\' --input-optionally-enclosed-by '\"' --input-null-string 'NULL!!!' \
  --update-key KEY1 --update-mode allowinsert \
  --export-dir dir1csv

これはMERGE文を使って実行されるようだ。

MERGE INTO TABLE1 USING dual ON ( KEY1 = :1 ) WHEN MATCHED THEN UPDATE SET DATA1 = :2 , NUM1 = :3 WHEN NOT MATCHED THEN INSERT ( KEY1, DATA1, NUM1 ) VALUES ( :4 , :5 , :6 )

direct import/export

import/exportにはdirectというオプションがあり、RDBMS固有の機能を使って 汎用SQLよりも高速に処理できる（はず）。
だが、Oracleは特に対応してないっぽい。（オプションは指定できるが、特別な処理は特にしない感じ）

$ sqoop import --connect jdbc:oracle:thin:@localhost:1521:orcl --username HISHIDAMA --password hishidama --table TABLE1 \
  --direct

$ sqoop export --connect jdbc:oracle:thin:@localhost:1521:orcl --username HISHIDAMA --password hishidama --table TABLE1 \
  --input-fields-terminated-by ',' --input-lines-terminated-by '\n' --input-escaped-by '\\' --input-optionally-enclosed-by '\"' --input-null-string 'NULL!!!' \
  --direct \
  --export-dir dir1csv

OraOop

OraOop(オラウープ)は、Quest Software社が提供しているSqoopのOracle用プラグイン。
（OraOopは古い名前で、今は「Quest Data connector for Oracle and Hadoop」というらしい）

通常のOracle用Sqoopでは、import/export時にテーブル名やユーザー名を大文字にしないといけないが、OraOopでは小文字でも大丈夫。

OraOopのインストール

CDHのサイトからOraOopのアーカイブをダウンロードして展開・インストールする。

1. ClouderaのDownloadsページのConnectorsセクションの「Quest Data connector for Oracle and Hadoop」をクリックする。
2. Clouderaにログインする。
   1. ユーザー登録していない場合は、ここでユーザー登録が必要。
      • 「State」はアメリカの州を選ぶものなので、日本の場合は「-- Outside United States --」を選択する。
      • 登録すると、記入したメールアドレス宛にメールが届く。パスワードはメールに書かれている。
   2. ログインする際のユーザー名は、記入したメールアドレス。
3. Quest Data connectorのページで「Quest Data Connector for Oracle and Hadoop」をクリックしてアーカイブファイル（oraoop-1.5.0.tgz）をダウンロードする。
4. アーカイブファイルを適当な場所に解凍する。

   # tar xf oraoop-1.5.0.tgz

5. 環境変数SQOOP_HOMEを定義しておく。

   # export SQOOP_HOME=/usr/lib/sqoop

6. アーカイブファイルを解凍して出来たディレクトリー内のインストールシェルを実行する。

   # cd oraoop-1.5.0
   # ./install.sh

   実行するとライセンスとかが表示される。最後に「y」を押す。

これで、$SQOOP_HOME/libにoraoop-1.5.0.jarが配置される。
また、$SQOOP_HOME/conf/managers.dの下にoraoopというファイルが作られ、この中にoraoop-1.5.0.jarの場所が記述されている。

OraOopによるimport

通常のSqoopでは、指定されたカラムに対してMapタスク数で分割した上限・下限の範囲をSELECTする。

select * from テーブル where 指定カラム >= 下限 and 指定カラム < 上限;

OraOopでは擬似列ROWIDを使って分割するらしい。
したがって、分割用カラムを指定する必要は無いし、インデックスも不要。

select * from テーブル where ROWID >= dbms_rowid.rowid_create(〜) and ROWID <= dbms_rowid.rowid_create(〜);

OraOopでimportを実行するときは、「-D oraoop.disabled=false」を指定する。

$ sqoop import -D oraoop.disabled=false --verbose \
  --connect jdbc:oracle:thin:@localhost:1521:orcl --username HISHIDAMA --password hishidama --table table1

ところが、なんかエラーが出たよ…orz
（--verboseを付けているとデバッグログも出るので、何のSQLを実行しようとしたか分かる）

12/06/19 01:16:20 DEBUG oraoop.OraOopOracleQueries: getTableColumns() : sql = 
SELECT column_name, data_type FROM dba_tab_columns WHERE owner = ? and table_name = ? and (DATA_TYPE IN ('BINARY_DOUBLE','BINARY_FLOAT','BLOB','CHAR','CLOB','DATE','FLOAT','LONG','NCHAR','NCLOB','NUMBER','NVARCHAR2','RAW','ROWID','URITYPE','VARCHAR2') OR DATA_TYPE LIKE 'INTERVAL YEAR(%) TO MONTH' OR DATA_TYPE LIKE 'INTERVAL DAY(%) TO SECOND(%)' OR DATA_TYPE LIKE 'TIMESTAMP(%)' OR DATA_TYPE LIKE 'TIMESTAMP(%) WITH TIME ZONE' OR DATA_TYPE LIKE 'TIMESTAMP(%) WITH LOCAL TIME ZONE') ORDER BY column_id
12/06/19 01:16:20 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.RuntimeException: java.sql.SQLSyntaxErrorException: ORA-00942: 表またはビューが存在しません。
java.lang.RuntimeException: java.sql.SQLSyntaxErrorException: ORA-00942: 表またはビューが存在しません。
	at com.quest.oraoop.OraOopConnManager.getColumnNamesInOracleTable(OraOopConnManager.java:117)
	…

dba_tab_columnsテーブルが見つからないということらしい。
実際、SQL*Plusから「select * from dba_tab_columns」を実行しても同じエラーになる。
そりゃ、対象ユーザーが管理者（DBA）ではないので、dba_tab_columnsにはアクセスできなくて当然(苦笑)
したがって、対象ユーザーに管理者ロールを付与してやればよい。

$ sqlplus sys as sysdba

SQL> grant dba to hishidama;

OraOopによるexport

OraOopでexportを実行するときは、「-D oraoop.disabled=false」を指定する。

$ sqoop export -D oraoop.disabled=false --verbose \
  --connect jdbc:oracle:thin:@localhost:1521:orcl --username hishidama --password hishidama --table table1 \
  --input-fields-terminated-by ',' --input-lines-terminated-by '\n' --input-escaped-by '\\' --input-optionally-enclosed-by '\"' --input-null-string 'NULL!!!' \
  --export-dir dir1csv

exportでも、対象ユーザーに管理者ロール（DBA）が付いていないとエラーになる。
（ただし、こちらは何のテーブルにアクセスしようとしてエラーになったか表示されないので分かりにくい）

12/06/19 01:42:08 DEBUG oraoop.OraOopUtilities: The Oracle table context has been derived from:
	oracleConnectionUserName = hishidama
	tableStr = table1
	as:
	owner : HISHIDAMA
	table : TABLE1
12/06/19 01:42:08 ERROR oraoop.OraOopManagerFactory: java.sql.SQLSyntaxErrorException: ORA-00942: 表またはビューが存在しません。
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
	…

12/06/19 01:42:08 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM table1 t WHERE 1=0
12/06/19 01:42:08 DEBUG manager.OracleManager$ConnCache: Caching released connection for jdbc:oracle:thin:@localhost:1521:orcl/hishidama
12/06/19 01:42:08 DEBUG orm.ClassWriter: selected columns:
12/06/19 01:42:08 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.IllegalArgumentException: Attempted to generate class with no columns!
java.lang.IllegalArgumentException: Attempted to generate class with no columns!
	at com.cloudera.sqoop.orm.ClassWriter.generateClassForColumns(ClassWriter.java:1241)
	…

OraOopのエラー（制約）

OraOopには様々な制約があり、知らないと不可解なエラーに悩まされる。[2012-06-22]

タスク数1

OraOop1.5.0では、タスク数に1を指定できないようだ。[2012-06-22]

$ sqoop import -D oraoop.disabled=false --connect jdbc:oracle:thin:@localhost:1521:orcl --username hishidama --password hishidama \
  -m 1 --table table1
〜
12/06/22 22:58:44 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM table1 t WHERE 1=0
12/06/22 22:58:44 ERROR tool.ImportTool: Imported Failed: Attempted to generate class with no columns!

$ sqoop export -D oraoop.disabled=false --connect jdbc:oracle:thin:@localhost:1521:orcl --username hishidama --password hishidama \
  -m 1 --table table1 --export-dir table1
〜
12/06/22 22:57:53 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM table1 t WHERE 1=0
12/06/22 22:57:53 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.IllegalArgumentException: Attempted to generate class with no columns!
java.lang.IllegalArgumentException: Attempted to generate class with no columns!
        at com.cloudera.sqoop.orm.ClassWriter.generateClassForColumns(ClassWriter.java:1241)
        at com.cloudera.sqoop.orm.ClassWriter.generate(ClassWriter.java:1129)
        at com.cloudera.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:84)
        at com.cloudera.sqoop.tool.ExportTool.exportTable(ExportTool.java:66)
        at com.cloudera.sqoop.tool.ExportTool.run(ExportTool.java:99)
        at com.cloudera.sqoop.Sqoop.run(Sqoop.java:146)
        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
        at com.cloudera.sqoop.Sqoop.runSqoop(Sqoop.java:182)
        at com.cloudera.sqoop.Sqoop.runTool(Sqoop.java:221)
        at com.cloudera.sqoop.Sqoop.runTool(Sqoop.java:230)
        at com.cloudera.sqoop.Sqoop.main(Sqoop.java:239)

codegen

OraOop1.5.0ではcodegen（Javaソースの生成）が出来ない。[2012-06-22]

$ sqoop codegen -D oraoop.disabled=false --connect jdbc:oracle:thin:@localhost:1521:orcl --username hishidama --password hishidama --table table1
〜
12/06/22 22:43:11 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM table1 t WHERE 1=0
12/06/22 22:43:11 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.IllegalArgumentException: Attempted to generate class with no columns!
java.lang.IllegalArgumentException: Attempted to generate class with no columns!
        at com.cloudera.sqoop.orm.ClassWriter.generateClassForColumns(ClassWriter.java:1241)
        at com.cloudera.sqoop.orm.ClassWriter.generate(ClassWriter.java:1129)
        at com.cloudera.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:84)
        at com.cloudera.sqoop.tool.CodeGenTool.run(CodeGenTool.java:101)
        at com.cloudera.sqoop.Sqoop.run(Sqoop.java:146)
        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
        at com.cloudera.sqoop.Sqoop.runSqoop(Sqoop.java:182)
        at com.cloudera.sqoop.Sqoop.runTool(Sqoop.java:221)
        at com.cloudera.sqoop.Sqoop.runTool(Sqoop.java:230)
        at com.cloudera.sqoop.Sqoop.main(Sqoop.java:239)

importやexportを実行するとJavaソースも生成されるので、それを別途コンパイルしておけば、jarファイル化して使うことは出来る。

生成されるソースの相違

OraOop1.5.0とデフォルト版Sqoopでは、生成するJavaソースに互換性が無い。[2012-06-22]

デフォルト版Sqoopでjarファイルを作りOraOopモードでそれを使おうとすると、エラーになる。

12/06/22 23:00:35 ERROR tool.ExportTool: Error during export: The column-types for the table are not known.

特に、Oracle（DB）側のカラムがTIMESTAMPやDATE型の場合、
デフォルト版Sqoopではどちらもjava.sql.Timestampになるが、OraOopではなんとStringになる。

例えばデフォルト版Sqoopでjarファイルを作ってSequenceFileを生成し、 OraOopで（そのjarファイルを使わずに）exportしようとすると、データ位置がずれる。
運が良ければエラー発生、運が悪ければ変なデータとなって正常終了すると思われる。

下記の例は、TIMESTAMPとNUMBERを使っているテーブルに対して実行してみたもの。
デフォルト版SqoopではTIMESTAMPにlong値を使って値を入れているのに、OraOopはTextとして取得する。その結果、データ位置がずれて、NUMBERであるBigDecimal（整数部にBigIntegerが使われている）を取得する際に変なデータ（空文字列）が入ってきて例外が発生している。

12/06/22 23:13:03 WARN mapred.LocalJobRunner: job_local_0001
java.lang.NumberFormatException: Zero length BigInteger
        at java.math.BigInteger.(BigInteger.java:276)
        at java.math.BigInteger.(BigInteger.java:451)
        at com.cloudera.sqoop.lib.BigDecimalSerializer.readFields(BigDecimalSerializer.java:80)
        at example.table2.readFields(table2.java:99)
        at org.apache.hadoop.io.serializer.WritableSerialization$WritableDeserializer.deserialize(WritableSerialization.java:67)
        at org.apache.hadoop.io.serializer.WritableSerialization$WritableDeserializer.deserialize(WritableSerialization.java:40)
        at org.apache.hadoop.io.SequenceFile$Reader.deserializeValue(SequenceFile.java:1888)
        at org.apache.hadoop.io.SequenceFile$Reader.getCurrentValue(SequenceFile.java:1861)
        at org.apache.hadoop.mapreduce.lib.input.SequenceFileRecordReader.nextKeyValue(SequenceFileRecordReader.java:74)
        at com.cloudera.sqoop.mapreduce.CombineShimRecordReader.nextKeyValue(CombineShimRecordReader.java:111)
        at org.apache.hadoop.mapreduce.lib.input.CombineFileRecordReader.nextKeyValue(CombineFileRecordReader.java:66)
        at org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.nextKeyValue(MapTask.java:456)
        at org.apache.hadoop.mapreduce.MapContext.nextKeyValue(MapContext.java:67)
        at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:143)
        at com.cloudera.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:189)
        at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:647)
        at org.apache.hadoop.mapred.MapTask.run(MapTask.java:323)
        at org.apache.hadoop.mapred.LocalJobRunner$Job.run(LocalJobRunner.java:210)