|
ここでは、SqoopでMySQLにアクセスする。
HadoopとSqoopとMySQLを同一マシン（CentOS）上で稼動させる。
Hadoopはスタンドアローン環境。
MySQLのJDBCドライバーをSqoopのlibに配置しておく必要がある。
MySQLでサンプル用のDBとユーザーとテーブルを作っておく。
$ mysql -u root mysql> create database test1 character set utf8; mysql> show databases; mysql> create user hishidama identified by 'hishidamal'; ←パスワード mysql> grant all privileges on test1.* to hishidama@localhost identified by 'hishidamac'; ←接続用パスワード mysql> flush privileges; mysql> exit;
$ mysql -u hishidama -phishidamac test1
mysql> create table table1 (
key1 char(4),
data1 varchar(10),
num1 int,
primary key(key1)
);
mysql> show tables;
mysql> desc table1;
mysql> 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);
mysql> select * from table1;
mysql> commit;
テストデータの中にカンマとダブルクォーテーションおよびnullを含めてみた。
インポート（DB→HDFS）を実行してみる。
$ sqoop import --connect jdbc:mysql://localhost/test1 --username hishidama --password hishidamac --table table1 $ ls table1 table1.java $ ls table1 _SUCCESS part-m-00000 $ cat table1/part-m-00000 A001,test,123 A002,aa,bb"cc,456 A003,,null A004,null,null
テーブル名のディレクトリーと、テーブル名のJavaファイルが出来ている。
（→Javaソースを生成させない方法）
ディレクトリー内にテーブルから取得されたデータが入っている。
が、データ文字列内のカンマがそのまま出力されているので、これを読み込んでも正しく反映できない。
Sqoopでimportする際には区切り文字等を指定することが出来る。
$ sqoop import --connect jdbc:mysql://localhost/test1 --username hishidama --password hishidamac --table table1 \ --fields-terminated-by ',' --lines-terminated-by '\n' --escaped-by '\\' --enclosed-by '\"' \ --target-dir table1csv $ ls table1 table1.java table1csv $ cat table1csv/part-m-00000 "A001","test","123" "A002","aa,bb\"cc","456" "A003","","null" "A004","null","null"
ついでに--target-dirを指定しているので、その名前のディレクトリーが作られている（指定しないとテーブル名のディレクトリーになる）。
| パラメーター | 説明 | 指定例 | 出力例 |
|---|---|---|---|
| --lines-terminated-by | 行区切り文字。 デフォルトは改行なので、特に指定する必要は無いだろう。 |
'\n' |
|
| --fields-terminated-by | フィールド区切り文字。 | ',' |
|
| --enclosed-by | データを囲む文字。 全データがこの文字で囲まれる。 |
'\"' |
"A001","test","123" |
| --optionally-enclosed-by | データを囲む文字。 フィールド区切り文字がデータに含まれている場合はこの文字で囲まれる。 |
'\"' |
A005,"aa,bb,cc",456 |
| --escaped-by | エスケープ文字。 データを囲む文字と同じ文字がデータに含まれている場合、この文字が前に付く。 |
'\\' |
A006,aa\"bb'cc,456 |
| --null-string | 文字列データがNULLのときに出力される文字列。デフォルトは「null」。 文字列でないデータには影響しない。 |
'NULL!!!' |
A004,NULL!!!,null |
テキストファイルだけでなく、HadoopのSequenceFileとして出力することも出来る。
$ sqoop import --connect jdbc:mysql://localhost/test1 --username hishidama --password hishidamac --table table1 -as-sequencefile
$ ls
table1 table1.java
$ od -tx1a table1/part-m-00000
0000000 53 45 51 06 21 6f 72 67 2e 61 70 61 63 68 65 2e
S E Q ack ! o r g . a p a c h e .
0000020 68 61 64 6f 6f 70 2e 69 6f 2e 4c 6f 6e 67 57 72
h a d o o p . i o . L o n g W r
0000040 69 74 61 62 6c 65 06 74 61 62 6c 65 31 00 00 00
i t a b l e ack t a b l e 1 nul nul nul
〜
値のクラスはテーブル名と同じになっている。
データと一緒に生成されているJavaソースファイルがこのクラスのソース。
クラス名は--class-nameで指定できる。
$ sqoop import --connect jdbc:mysql://localhost/test1 --username hishidama --password hishidamac --table table1 \ -as-sequencefile --class-name com.example.Table1Writable \ --target-dir table1sf $ ls com table1 table1.java table1sf $ tree com com `-- example `-- Table1Writable.java $ od -tx1a table1sf/part-m-00000 0000000 53 45 51 06 21 6f 72 67 2e 61 70 61 63 68 65 2e S E Q ack ! o r g . a p a c h e . 0000020 68 61 64 6f 6f 70 2e 69 6f 2e 4c 6f 6e 67 57 72 h a d o o p . i o . L o n g W r 0000040 69 74 61 62 6c 65 1a 63 6f 6d 2e 65 78 61 6d 70 i t a b l e sub c o m . e x a m p 0000060 6c 65 2e 54 61 62 6c 65 31 57 72 69 74 61 62 6c l e . T a b l e 1 W r i t a b l 0000100 65 00 00 00 00 00 00 06 bf e8 b5 9b c7 55 d4 08 e nul nul nul nul nul nul ack ? h 5 esc G U T bs 〜
生成されるソースもパッケージのディレクトリー階層下に作られる。
エクスポート（HDFS→DB）を実行してみる。
（export実行時も、importと全く同じJavaソースが生成される。→Javaソースを生成させない方法）
exportのCSVに関するオプションは、インポートのときのオプションの前に「input」を付けたもの。
$ cat dir1csv/data.txt A001,"test",123 A002,"aa,bb\"cc",456 A003,"",null A004,null,null A005,NULL!!!,null $ sqoop export --connect jdbc:mysql://localhost/test1 --username hishidama --password hishidamac --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
mysql> select * from table1; +------+----------+------+ | key1 | data1 | num1 | +------+----------+------+ | A001 | test | 123 | | A002 | aa,bb"cc | 456 | | A003 | | NULL | | A004 | null | NULL | | A005 | NULL | NULL | +------+----------+------+ 5 rows in set (0.00 sec)
SequenfeFileに出力するときは-as-sequencefileで明示的にSequenceFileであることを指定したが、エクスポートのときはファイルの種類を自動的に判別してくれるようだ。
$ sqoop export --connect jdbc:mysql://localhost/test1 --username hishidama --password hishidamac --table table1 \ --export-dir dir1sf
exportは基本的にINSERTだが、UPDATEを使うことも出来る。[2012-06-01]
$ sqoop export --connect jdbc:mysql://localhost/test1 --username hishidama --password hishidamac --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 table1 SET data1='test', num1=123 WHERE key1='A001' UPDATE table1 SET data1='aa,bb"cc', num1=456 WHERE key1='A002' UPDATE table1 SET data1='', num1=null WHERE key1='A003' UPDATE table1 SET data1=null, num1=null WHERE key1='A004' UPDATE table1 SET data1='NULL!!!', num1=null WHERE key1='A005' UPDATE table1 SET data1='かきく', num1=789 WHERE key1='A006'
対象テーブルにデータがあれば更新される。
対象テーブルにデータが無ければ（WHERE条件に合致するレコードが無いので）何も更新されない。
「--update-mode allowinsert」を指定すると、対象テーブルにデータが無いときはINSERTしてくれる。
なのだが、MySQLは（まだ）対応していないようだ。（デフォルトは「--update-mode updateonly」）
12/06/01 23:12:19 ERROR tool.ExportTool: Error during export: Mixed update/insert is not supported against the target database yet
exportにはステージング機能がある。[2012-06-01]
ステージングテーブルを経由して目的のテーブルにデータを転送するもの。
$ sqoop export --connect jdbc:mysql://localhost/test1 --username hishidama --password hishidamac \ --input-fields-terminated-by ',' --input-lines-terminated-by '\n' --input-escaped-by '\\' --input-optionally-enclosed-by '\"' \ --table table1 \ --staging-table table1_stage --clear-staging-table \ --export-dir dir1csv
内部的な動作は以下の様になる。
DELETE FROM table1_stage commit
INSERT INTO table1_stage (key1, data1, num1) VALUES ('A001', 'test', 123), …
…
commitINSERT INTO table1 ( SELECT * FROM table1_stage ) DELETE FROM table1_stage commit
Sqoopは複数のノードから並列でインサートするので、コミットタイミングはばらばら。目的のテーブルに直接インサートすると、中途半端な状態が出来てしまう。
それが嫌な場合はステージングテーブルを使うと、目的のテーブルは一気に更新されることになる。
importやexportを実行すると、（デフォルトでは）テーブル名と同名のJavaソースファイルが生成される。
テキストファイル（CSVファイル）ではこのクラスのパーサーメソッドが使われると思われる。
シーケンスファイルでは値（Writable）としてこのクラスが使われる。
importやexportの際には、このクラスを用いてデータのシリアライズ/デシリアライズを行うようだ。
このクラスの1インスタンスが1レコード分のデータを保持する。
このクラスはHadoopのWritableとJDBCアクセスの為のDBWritableというインターフェースを実装し、その他にテキスト（CSV等）の為のパーサーとtoString()メソッド等が入っている。
| メソッド | 備考 |
|---|---|
| get_フィールド() set_フィールド(値) with_フィールド(値) |
フィールド（データ）のセッター・ゲッターメソッド。 （withは、自分自身のインスタンスを返すセッターメソッド） |
| equals() | |
| readFields(ResultSet) | DBから読み込んだResultSetを元に自分自身のフィールドに値をセットする。 |
| write(PreparedStatement) | 自分自身の値をPreperedStatementにセットする。（DBへの書き込み用） |
| readFields(DataInput) | HadoopのWritableの読込用メソッド。自分自身のフィールドに値をセットする。 |
| write(DataOutput) | HadoopのWritableの出力用メソッド。 |
| toString() | 文字列として出力する。（CSVとして整形する） |
| parse(CharSequence) | 文字列を（CSVとして）パースし、自分自身のフィールドに値をセットする。 |
| clone() | |
| getFieldMap() | フィールド名と値のMapを返す。 |
| setField(名前, 値) | フィールド名を指定して値をセットする。 |
Writableのメソッドでは、値がnullかどうかに応じてbooleanの値も入出力している。
オプションでnullを扱うかどうか切り替えられそうな気はする。
importやexportを実行する度にJavaソースが生成されるのは鬱陶しい。
codegenによって事前に一度だけ生成し、importやexportでそのクラスを使うことが出来る。
$ sqoop codegen --connect jdbc:mysql://localhost/test1 --username hishidama --password hishidamac --table table1 \ --fields-terminated-by ',' --lines-terminated-by '\n' --escaped-by '\\' --optionally-enclosed-by '\"' \ --class-name com.sample.Table1 \ --outdir src $ ls src $ tree src src `-- com `-- sample `-- Table1.java
$ mkdir classes $ javac -cp classes:/usr/lib/hadoop/hadoop-core.jar:/usr/lib/sqoop/sqoop-1.3.0-cdh3u3.jar \ -d classes -sourcepath src \ src/com/sample/Table1.java $ tree classes classes/ `-- com `-- sample `-- Table1.class
$ jar cf sqoop1.jar -C classes .
$ jar tf sqoop1.jar ←中身確認
META-INF/
META-INF/MANIFEST.MF
com/
com/sample/
com/sample/Table1.classCSVファイルへのインポート $ sqoop import --connect jdbc:mysql://localhost/test1 --username hishidama --password hishidamac --table table1 \ --jar-file sqoop1.jar --class-name com.sample.Table1 \ --target-dir table1csv
シーケンスファイルへのインポート $ sqoop import --connect jdbc:mysql://localhost/test1 --username hishidama --password hishidamac --table table1 \ --jar-file sqoop1.jar --class-name com.sample.Table1 \ --target-dir table1sf -as-sequencefile
エクスポート $ sqoop export --connect jdbc:mysql://localhost/test1 --username hishidama --password hishidamac --table table1 \ --jar-file sqoop1.jar --class-name com.sample.Table1 \ --export-dir dir
importやexport時に--jar-fileを指定すると、Javaソースが自動生成されなくなる。
その代わりに（jarファイルの中に入っている）実行時に使用するクラスを--class-nameで指定する必要がある。
import/exportにはdirectというオプションがあり、RDBMS固有の機能を使って 汎用SQLよりも高速に処理できる（はず）。[2012-05-12]
$ sqoop import --connect jdbc:mysql://localhost/test1 --username hishidama --password hishidamac --direct --table table1 \ --target-dir table1csv
$ sqoop export --connect jdbc:mysql://localhost/test1 --username hishidama --password hishidamac --direct --table table1 \ --export-dir dir1csv
Sqoop1.3.0のMySQLのダイレクトモードは以下のような特徴・制約がある。
MySQL direct export currently supports only text output format.Parameters --as-sequencefile and --as-avrodatafile are not supported with --direct params in MySQL case.