sqoop1: import to hive partitioned table

mysql table user

 

hive metastore table

 

sqoop import scripts

 

import --verbose --connect jdbc:mysql://192.168.122.1:3306/sample 
--username zhaohj --password 123456 
--table user 
--split-by user_id 
-m 2 
--hive-import 
--hive-database user 
--hive-partition-key first_letter
--create-hive-table

 

Imported Failed: Partition key first_letter cannot be a column to import.

 

 

 --------------------

 

 

import --verbose --connect jdbc:mysql://192.168.122.1:3306/inok 
--username zhaohj --password 123456 
--table user --split-by user_id 
-m 2 
--hive-database user 
--hive-table user 
--hive-import

 

 

 Need to specify partition columns because the destination table is partitioned

 

 －－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

dynamic partition

1. set hive-site.xml

hive.exec.dynamic.partition=true

hive.exec.dynamic.partition.mode=nonstrict

 

2.create partition table in hive

 

 

3.import data from mysql to hive dynamic partition table

 

 

 

References

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

https://issues.apache.org/jira/browse/SQOOP-312

http://www.2cto.com/kf/201207/144807.html

https://cwiki.apache.org/confluence/display/Hive/DynamicPartitions

https://cwiki.apache.org/confluence/display/Hive/Tutorial#Tutorial-DynamicpartitionInsert

http://docs.hortonworks.com/HDPDocuments/HDP1/HDP-1.3.2/bk_dataintegration/content/ch_using-sqoop-hcat-integration.html

 

blogs

http://www.qubole.com/5-tips-for-efficient-hive-queries/

https://gist.github.com/bugcy013/7188933