sqoop数据导入hive及到处到mysql

条件准备

复制jar包到sqoop的lib目录下

#复制hive的jar包
cp /opt/software/hive110/lib/hive-common-1.1.0-cdh5.14.2.jar /opt/software/sqoop146/lib
cp /opt/software/hive110/lib/hive-shims* /opt/software/sqoop146/lib

mysql数据导入hive

sqoop import \
--connect jdbc:mysql://localhost:3306/retail_db  \
--table orders \
--username root  \
--password ok \
--hive-import  \
--create-hive-table \
--hive-database retail_db \
--hive-table orders \
--m 3

mysql导入hive分区表

sqoop import \
--connect jdbc:mysql://localhost:3306/retail_db  \
--driver com.mysql.jdbc.Driver \
--query "select order_id,order_status from orders where order_date>='2013-11-03' and order_date<'2013-11-04' and \$CONDITIONS" \
--username root  \
--password ok \
--delete-target-dir \
--target-dir /data/retail_db/orders \
--split-by order_status \
--hive-import  \
--create-hive-table \
--hive-database retail_db \
--hive-table orders \
--hive-partition-key "order_date" \
--hive-partition-value "2013-11-03" \
--m 3

hive导出数据到mysql

hdfs导出到MySQL(或者说hive数据导出到mysql,因为hive的表实际是一个目录,将该目录下数据导出到mysql即可,注意hive默认分割符是’\001’需要换成能够解析的分隔符)

#先在mysql创建一个空表
create table customers_demo as select * from customers where 1=2;
#创建目录,上传数据
hdfs dfs -mkdir /customerinput
hdfs dfs -put customers.csv /customerinput/
#导出到mysql
sqoop export \
--connect jdbc:mysql://localhost:3306/retail_db \
--username root \
--password ok \
--table customers_demo \
--export-dir /customerinput/ \
--fields-terminated-by ',' \
-m 1