Convert Hive Table to MySQL

Question

I have a large Hive table which I would like to transfer from the server, to my local MySQL database. Are there any standard ways of doing this?

I downloaded the Hive file from the metastore and it appears to be in an unusual formate where it is difficult to parse to a database (The columns dont seem to even be seperated)

e.g. of file from hive:

"1980""007"59610.0016778523489932886"actors""007"59610.0016778523489932886"7d""007"59620.003355704697986577"abu""007"59610.0016778523489932886

Edit: Heres my table

DESCRIBE phil_overall_cooc

0   tag_a   string  
1   tag_b   string  
2   bcount  bigint  
3   cooc    bigint  
4   prob    double

Pierre-Luc Bertrand · Accepted Answer

Sqoop is what you are looking for ! I'm using it to send my data into MySQL. The only trick is that you must use TEXTFILE without partitions from

file_format:
  : SEQUENCEFILE
  | TEXTFILE
  | RCFILE     (Note: only available starting with 0.6.0)
  | ORC        (Note: only available starting with 0.11.0)
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-Create%2FDrop%2FTruncateTable

If it's not in TEXTFILE, you can do a

INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML#LanguageManualDML-InsertingdataintoHiveTablesfromqueries

Now that you have your export table, you may use sqoop as follow:

sqoop export 
  --connect ${YOUR_CONNECTION_STRING}
  --username ${YOUR_USERNAME} 
  --password ${YOUR_PASSWORD}
  --table ${MYSQL_TABLE_NAME}
  --update-key ${FIELD1[,FIELD2]*}
  --update-mode allowinsert 
  --export-dir ${PATH_TO_YOUR_HIVE_TABLE_ON_HDFS} 
  --input-fields-terminated-by \001 
  --num-mappers 1 
  --batch 
  --verbose

http://sqoop.apache.org/docs/1.4.4/SqoopUserGuide.html#_syntax_3

Good luck !

Convert Hive Table to MySQL

Answers (2)

Related Questions