How to move Hive data table to MySql?

Question

I would like to know how I can move date from Hive to MySQL?

I have seen example on how to move hive data to Amazon DynamoDB but not for a RDBMS like MySQL. Here is the example that I saw with DynamoDB:

CREATE EXTERNAL TABLE tbl1 ( name string, location string )
STORED BY 'org.apache.hadoop.hive.dynamodb.DynamoDBStorageHandler'
TBLPROPERTIES ("dynamodb.table.name" = "table",
"dynamodb.column.mapping" = "name:name,location:location") ;

I would like to do the same but with MySQL instead. I wonder if I need to code my own StorageHandler? I also to do not want to use sqoop. I want to be able to do my query directly in my HiveQL script.

Answer 1

There are many options. You can download the files in hive as csv file and then try bulk insert into mysql tables. You can use Sqoop. Or you can use some of the popular ETL tools like Pentaho and many others.

Answer 2

Have you tried using Sqoop?. Its a good tool to do such kind of stuff.

Answer 3

You'd currently need a JDBC StorageHandler, which one has not been created just yet, but you could certain build your own.

There is currently an issue report for this which you can follow here:

https://issues.apache.org/jira/browse/HIVE-1555