mysql sqoop 到hive,使用Sqoop从MySQL导入到Hive

I have to import > 400 million rows from a MySQL table(having a composite primary key) into a PARTITIONED Hive table Hive via Sqoop. The table has data for two years with a column departure date ranging from 20120605 to 20140605 and thousands of records for one day. I need to partition the data based on the departure date.

The versions :

Apache Hadoop - 1.0.4

Apache Hive - 0.9.0

Apache Sqoop - sqoop-1.4.2.bin__hadoop-1.0.0

As per my knowledge, there are 3 approaches:

MySQL -> Non-partitioned Hive table -> INSERT from Non-partitioned Hive table into Partitioned Hive table

MySQL -> Partitioned Hive table

MySQL -> Non-partitioned Hive table -> ALTER Non-partitioned Hive table to add PARTITION

is the current painful one that I’m following

I read that the support for this is added in later(?) versions of Hive and Sqoop but was unable to find an example

The syntax dictates to specify partitions as key value pairs – not feasible in case of millions of records where one cannot think of all the partition key-value pairs

3.

Can anyone provide inputs for approaches 2 and 3?

解决方案

If this is still something people wanted to understand, they can use

sqoop import --driver --connect --username -P --table employee --num-mappers --warehouse-dir --hive-import --hive-table table_name --hive-partition-key departure_date --hive-partition-value $departure_date

Notes from the patch:

sqoop import [all other normal command line options] --hive-partition-key ds --hive-partition-value "value"

Some limitations:

It only allows for one partition key/value

hardcoded the type for the partition key to be a string

With auto partitioning in hive 0.7 we may want to adjust this to just have one command line option for the key name and use that column from the db table to partition.