S-JIS[2012-05-07/2012-06-01] 変更履歴

Sqoop PostgreSQLサンプル

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

準備
import
export [/2012-06-01]
- 更新モード [2012-06-01]
PostgreSQL8.1のエラー [/2012-06-01]
direct import/export [2012-05-12]

準備

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

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

PostgreSQLでサンプル用のテーブルを作っておく。

$ psql mydb

mydb=# create table table1 (
         key1   char(4),
         data1  varchar(10),
         num1   int,
         primary key(key1)
       );
mydb=# \dt;
mydb=# \d table1;

mydb=# 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);
mydb=# select * from table1;
mydb=# commit;

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

import

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

$ sqoop import --connect jdbc:postgresql://localhost/mydb --username hishidama --table table1

以下のようなエラーが出たときは、PostgreSQL側で認証に失敗している。
ローカルホストのTCP/IP経由の接続を許可しておく必要がある。

12/05/07 21:50:50 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.RuntimeException: org.postgresql.util.PSQLException: FATAL: Ident authentication failed for user "hishidama"
java.lang.RuntimeException: org.postgresql.util.PSQLException: FATAL: Ident authentication failed for user "hishidama"
        at com.cloudera.sqoop.manager.CatalogQueryManager.getColumnNames(CatalogQueryManager.java:163)
        at com.cloudera.sqoop.orm.ClassWriter.getColumnNames(ClassWriter.java:1198)
        at com.cloudera.sqoop.orm.ClassWriter.generate(ClassWriter.java:1053)
～

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

$ ls
table1  table1.java

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

接続用のURL（jdbc:postgresql://～）が異なるだけで、他は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:postgresql://localhost/mydb --username 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

mydb=# select * from table1 ;
 A001 | test     |  123
 A002 | aa,bb"cc |  456
 A003 |          |     
 A004 | null     |     
 A005 |          |

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

なお、実行開始時にトランケートが実行され、対象テーブルのデータは消される。（これはMySQLの動作とは異なる）[2012-06-01]

更新モード

exportは基本的にINSERTだが、UPDATEを使うことも出来る。[2012-06-01]

$ sqoop export --connect jdbc:postgresql://localhost/mydb --username 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文でテーブルが更新される。
（カンマ区切りで複数のカラムを指定可能）

truncate table1
～
BEGIN
UPDATE table1 SET num1=$1 WHERE key1=$2 AND data1=$3
パラメータ: $1 = '123', $2 = 'A001', $3 = 'test'
UPDATE table1 SET num1=$1 WHERE key1=$2 AND data1=$3
パラメータ: $1 = '456', $2 = 'A002', $3 = 'aa,bb"cc'
UPDATE table1 SET num1=$1 WHERE key1=$2 AND data1=$3
パラメータ: $1 = NULL, $2 = 'A003', $3 = ''
UPDATE table1 SET num1=$1 WHERE key1=$2 AND data1=$3
パラメータ: $1 = NULL, $2 = 'A004', $3 = 'null'
UPDATE table1 SET num1=$1 WHERE key1=$2 AND data1=$3
パラメータ: $1 = NULL, $2 = 'A005', $3 = NULL
COMMIT

ところがだ、なぜか実行開始時にテーブルのトランケートが実行されているので、更新対象は1レコードも無く、テーブルが空っぽになってしまう(爆)
これはさすがにSqoop 1.3.0-cdh3u4のバグだろうなぁ。

こんな状態なので、対象テーブルにデータが無ければINSERTするオプションである「--update-mode allowinsert」は意味が無いが、
試しに指定してみたら「Mixed update/insert is not supported against the target database yet」（未対応）だそうだ。

PostgreSQL8.1のエラー

PostgreSQL8.1を対象にSqoopのexportを実行すると、以下のようなエラーが発生する。

12/05/07 22:09:38 WARN mapred.LocalJobRunner: job_local_0001
java.io.IOException: org.postgresql.util.PSQLException: ERROR: syntax error at or near ","
        at com.cloudera.sqoop.mapreduce.AsyncSqlRecordWriter.close(AsyncSqlRecordWriter.java:187)
        at org.apache.hadoop.mapred.MapTask$NewDirectOutputCollector.close(MapTask.java:540)
        at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:649)
        at org.apache.hadoop.mapred.MapTask.run(MapTask.java:323)
        at org.apache.hadoop.mapred.LocalJobRunner$Job.run(LocalJobRunner.java:210)
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near ","
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1525)
～
12/05/07 22:09:39 INFO mapred.JobClient: Counters: 0
12/05/07 22:09:39 INFO mapreduce.ExportJobBase: Transferred 0 bytes in 1.6291 seconds (0 bytes/sec)
12/05/07 22:09:39 INFO mapreduce.ExportJobBase: Exported 0 records.
12/05/07 22:09:39 ERROR tool.ExportTool: Error during export: Export job failed!

PostgreSQL側でSQL文の文法エラー（syntax error）が出ている。

/var/lib/pgsql/data/pg_log/postgresql-*.log：

（ログのファイル名は、デフォルトでは出力した曜日を表す文字が付く。月曜日の場合、postgresql-MON.log）

ERROR: syntax error at or near "," at character 59

デフォルトではこのメッセージだけしか出ていないので、よく分からない…。
実行したSQL文もログに出力するように設定すると、以下の様になっていた。

LOG:  statement: PREPARE  AS INSERT INTO table1 (key1, data1, num1) VALUES ($1, $2, $3), ($4, $5, $6), ($7, $8, $9), ($10, $11, $12), ($13, $14, $15)
ERROR:  syntax error at or near "," at character 59

ここでの「59」は、INSERT文の59桁目、つまり「($1, $2, $3)」の直後のカンマを指している。

PostgreSQL8.1では、INSERT～VALUESにおいて、値の組を複数指定することが出来ない。
したがって、エラーが発生したのだ。
PostgreSQL8.2以降（やMySQL）ではこの構文が使えるようになっている。

PostgreSQL 8.1.23 DocumentationのINSERT
PostgreSQL 8.2.23 DocumentationのINSERT

ただ、バッチモードにすると複数VALUES INSERTの構文は使われず、通常のINSERT文になるのでエラーにならない。[2012-06-01]

$ sqoop export --connect jdbc:postgresql://localhost/mydb --username 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 \
  --batch

バッチモードは、JDBCのPreparedStatementのaddBatchでSQL文（SqoopではINSERT文）をいくつか登録し、executeBatchでまとめて実行するもの。
SQL文を1個ずつ実行するよりは速い（はずだが、RDBMS（JDBCの実装）次第）。

direct import/export

import/exportにはdirectというオプションがあり、RDBMS固有の機能を使って汎用SQLよりも高速に処理できる（はず）。[2012-05-12]

$ sqoop import --connect jdbc:postgresql://localhost/mydb --username hishidama --direct --direct-split-size 64000000 \
  --table table1

Sqoop1.3.0のPostgreSQLのダイレクトモードは以下のような特徴・制約がある。

import
- PostgreSQLのcopy文を使ってデータを取り込む。（普通は単なるselect文）
- MapReduceは使用されない。（つまり分散されず、Sqoopを実行したクライアントマシン上で処理される）
  ファイル名は（「part-m-*」ではなく）「data-*」になる。
- --direct-split-sizeオプションで、ファイル当たりのサイズ（バイト数）を指定することが出来る。
  読み込んだデータがこのサイズを超えると新しいファイルになる。（つまりdata-00000・data-00001…とファイルが増えていく）
  このサイズ指定は厳密に適用されるのではなく、各ファイルの大きさは指定したサイズぴったりにはならない。ばらつきが出る。
  --direct-split-sizeを指定しない場合は1ファイル（data-00000）のみになる。
- SequenceFileには対応していない。-as-sequencefileを付けても、テキスト（CSV）ファイルになってしまう。
```
12/05/12 07:40:49 WARN manager.DirectPostgresqlManager: File import layoutSequenceFile is not supported by
12/05/12 07:40:49 WARN manager.DirectPostgresqlManager: Postgresql direct import; import will proceed as text files.
```
export
- ダイレクトモードに対応していない。（『HADOOP HACKS』p.58）
  対応していないというのは、エラーになるわけではなく、普通のモードと同じ動作をする。

Sqoop目次へ戻る / Hadoopへ行く / 技術メモへ戻る

メールの送信先：ひしだま