架設Sqoop

匯入或匯出資料

安裝Sqoop

下載Sqoop 1.4.7到hadoop1 Server的hadoop User家目錄

$ wget http://apache.stu.edu.tw/sqoop/1.4.7/sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz
$ tar sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz 
$ tar -zxvf sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz 
$ mv sqoop-1.4.7.bin__hadoop-2.6.0 sqoop

設定必要環境變數

請在.bashrc新增以下環境變數內容

$ vi ~/.bashrc 

變數內容

export SQOOP_HOME=/home/hadoop/sqoop
export PATH=$SQOOP_HOME/bin:$PATH
export HIVE_HOME=/home/hadoop/hive
export PATH=$HIVE_HOME/bin:$PATH
export HIVE_CONF_DIR=${HIVE_HOME}/conf
export HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$HIVE_HOME/lib/*

使環境變數生效

$ source ~/.bashrc

下載Mysql JDB Connector

$ cd ~/sqoop/lib/
$ wget http://central.maven.org/maven2/mysql/mysql-connector-java/8.0.11/mysql-connector-java-8.0.11.jar
$ cd ~

已root身分修改java.policy

$ vi /usr/lib/jvm/jre/lib/security/java.policy 

新增這一行內容

permission javax.management.MBeanTrustPermission "register";

測試

匯入MySql資料到Hive

$ sqoop import --connect jdbc:mysql://${MysqlHost}:3306/${MySqlSchemaName}?zeroDateTimeBehavior=EXCEPTION --table ${MySqlTableName} --username ${MySqlAccount} --password ${MySqlPassword} --create-hive-table --hive-import --fields-terminated-by ',' --hive-table ${HiveDatabaseName.HiveTableName}

如果第二次匯入，可以把 --create-hive-table 拿掉

匯出Hive資料到MySql

$ sqoop export --connect jdbc:mysql://${MySqlHost}:3306/${MySqlSchema} --username ${MySqlAccount} --password ${MySqlPassword} --table ${MySqlTable} --export-dir /user/hive/warehouse/${HiveTableDir} --input-fields-terminated-by ','