如何将HBase表导出到Mysql_从HBase(Hive)将数据导出到MySQL

在上一篇文章《用Sqoop进行MySQL和DHFS系统间的数据互导》中，提到sqoop可以让RDBMS和HDFS之间互导数据，并且也支持从mysql中导入

在上一篇文章《用Sqoop进行MySQL和DHFS系统间的数据互导》中，提到sqoop可以让RDBMS和HDFS之间互导数据，并且也支持从mysql中导入到HBase，但从HBase直接导入mysql则不是直接支持，而是间接支持。要么将HBase导出到HDFS平面文件，，要么将其导出到Hive中，再导出到mysql。本篇讲从hive中导出到mysql。

从hive将数据导出到mysql

一、创建mysql表

mysql> create table award (rowkey varchar(255), productid int, matchid varchar(255), rank varchar(255), tourneyid varchar(255), userid bigint, gameid int, gold int, loginid varchar(255), nick varchar(255), plat varchar(255));

Query OK, 0 rows affected (0.01 sec)

二、尝试用hive作为外部数据库连接hbase，导入mysql

hive> CREATE EXTERNAL TABLE hive_award(key string, productid int,matchid string, rank string, tourneyid string, userid bigint,gameid int,gold int,loginid string,nick string,plat string) STORED BY 'org.apache.Hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,info:MPID,info:MatchID,info:Rank,info:TourneyID,info:UserId,info:gameID,info:gold,info:loginId,info:nickName,info:platform") TBLPROPERTIES("hbase.table.name" = "award"); hive> desc hive_award;

key string from deserializer

productid int from deserializer

matchid string from deserializer

rank string from deserializer

tourneyid string from deserializer

userid bigint from deserializer

gameid int from deserializer

gold int from deserializer

loginid string from deserializer

nick string from deserializer

plat string from deserializer

[zhouhh@Hadoop46 ~]$ hadoop fs -ls /user/hive/warehouse/

Found 3 items

drwxr-xr-x - zhouhh supergroup 0 2012-07-16 14:08 /user/hive/warehouse/hive_award

drwxr-xr-x - zhouhh supergroup 0 2012-07-16 14:30 /user/hive/warehouse/nnnon

drwxr-xr-x - zhouhh supergroup 0 2012-07-16 13:53 /user/hive/warehouse/test222 [zhouhh@Hadoop46 ~]$ sqoop export --connect jdbc:mysql://Hadoop48/toplists -m 1 --table award --export-dir /user/hive/warehouse/hive_award --input-fields-terminated-by '\0001'

12/07/19 16:13:06 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset. 12/07/19 16:13:06 INFO tool.CodeGenTool: Beginning code generation

12/07/19 16:13:06 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `award` AS t LIMIT 1

12/07/19 16:13:06 INFO orm.CompilationManager: HADOOP_HOME is /home/zhouhh/hadoop-1.0.0/libexec/..

注: /tmp/sqoop-zhouhh/compile/4366149f0b6dd311c5b622594744fbb0/award.java使用或覆盖了已过时的 API。

注: 有关详细信息, 请使用 -Xlint:deprecation 重新编译。

12/07/19 16:13:08 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-zhouhh/compile/4366149f0b6dd311c5b622594744fbb0/award.jar

12/07/19 16:13:08 INFO mapreduce.ExportJobBase: Beginning export of award

12/07/19 16:13:09 WARN mapreduce.ExportJobBase: Input path hdfs://Hadoop46:9200/user/hive/warehouse/hive_award contains no files

12/07/19 16:13:11 INFO input.FileInputFormat: Total input paths to process : 0

12/07/19 16:13:11 INFO input.FileInputFormat: Total input paths to process : 0

12/07/19 16:13:13 INFO mapred.JobClient: Running job: job_201207191159_0059

12/07/19 16:13:14 INFO mapred.JobClient: map 0% reduce 0%

12/07/19 16:13:26 INFO mapred.JobClient: Job complete: job_201207191159_0059

12/07/19 16:13:26 INFO mapred.JobClient: Counters: 4

12/07/19 16:13:26 INFO mapred.JobClient: Job Counters

12/07/19 16:13:26 INFO mapred.JobClient: SLOTS_MILLIS_MAPS=7993

12/07/19 16:13:26 INFO mapred.JobClient: Total time spent by all reduces waiting after reserving slots (ms)=0

12/07/19 16:13:26 INFO mapred.JobClient: Total time spent by all maps waiting after reserving slots (ms)=0

12/07/19 16:13:26 INFO mapred.JobClient: SLOTS_MILLIS_REDUCES=0

12/07/19 16:13:26 INFO mapreduce.ExportJobBase: Transferred 0 bytes in 16.9678 seconds (0 bytes/sec)

12/07/19 16:13:26 INFO mapreduce.ExportJobBase: Exported 0 records. 直接导外部表不成功，Input path hdfs://Hadoop46:9200/user/hive/warehouse/hive_award contains no files