Lab 006

實作六

Sqoop (6) : 將 MySQL 資料表匯入 HBase

執行記錄匯入

1. 請輸入如下指令並將「帳號」處替換成您的帳號：

user@master ~ $ export DBID=帳號
user@master ~ $ sqoop import --connect "jdbc:mysql://sql.3du.me/$DBID" --table mysql_data --username $DBID -P --hbase-row-key id --hbase
-create-table --column-family data --hbase-table mysql_data
Enter password: 輸入密碼

1. 若資料表匯入有正常執行，您將看到以下的類似訊息：

13/12/22 16:09:37 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
13/12/22 16:09:37 INFO tool.CodeGenTool: Beginning code generation
13/12/22 16:09:38 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `mysql_data` AS t LIMIT 1
13/12/22 16:09:38 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `mysql_data` AS t LIMIT 1
13/12/22 16:09:38 INFO orm.CompilationManager: HADOOP_HOME is /opt/hadoopmr
Note: /tmp/sqoop-user/compile/0caa2db5c9f9ebce85eda6c97028fc0f/mysql_data.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
13/12/22 16:09:41 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-user/compile/0caa2db5c9f9ebce85eda6c97028fc0f/mysql_data.jar
13/12/22 16:09:41 WARN manager.MySQLManager: It looks like you are importing from mysql.
13/12/22 16:09:41 WARN manager.MySQLManager: This transfer can be faster! Use the --direct
13/12/22 16:09:41 WARN manager.MySQLManager: option to exercise a MySQL-specific fast path.
13/12/22 16:09:41 INFO manager.MySQLManager: Setting zero DATETIME behavior to convertToNull (mysql)
13/12/22 16:09:41 INFO mapreduce.ImportJobBase: Beginning import of mysql_data
... 略 ...
13/12/22 16:11:11 INFO mapreduce.ImportJobBase: Transferred 0 bytes in 78.9235 seconds (0 bytes/sec)
13/12/22 16:11:11 INFO mapreduce.ImportJobBase: Retrieved 2 records.

檢查匯入結果

1. 轉換之結果，可於 HBase 儲存於 HDFS 的路徑查到。

user@master ~ $ hadoop fs -ls /hbase/mysql_data
No encryption was performed by peer.
Found 3 items
-rw-------   3 etu supergroup        559 2013-12-22 16:09 /hbase/mysql_data/.tableinfo.0000000001
drwxr-xr-x   - etu supergroup          0 2013-12-22 16:09 /hbase/mysql_data/.tmp
drwx------   - etu supergroup          0 2013-12-22 16:09 /hbase/mysql_data/595da9f76c44dcd9cb22ab6a4c8cdc61

1. 您也可以透過 Etu Appliance 的管理介面觀察 HBase 資料表的狀態。
2. 當然也可以透過 hbase shell 指令查詢

user@master ~ $ hbase shell
HBase Shell; enter 'help' for list of supported commands.
Type "exit" to leave the HBase Shell
Version 0.92.1-cdh4.0.1, r, Thu Jun 28 18:04:29 PDT 2012
hbase(main):001:0> list
TABLE
MSSQL_DATA
_acl_
mysql_data
3 row(s) in 2.0240 seconds

參考資料

1. "Sqoop: Big data conduit between NoSQL and RDBMS", Surajit Paul, Advisory Consultant, IBM, 23 Jul 2013