1：創(chuàng)建shell腳本

1 touch sqoop_options.sh2 chmod 777 sqoop_options.sh

編輯文件? 特地將執(zhí)行map的個數(shù)設置為變量? 測試 可以java代碼傳參數(shù) 同時也驗證sqoop的 options 屬性支持這種寫法

1 #!/bin/bash2 /opt/cdh-5.3.6/sqoop-1.4.5-cdh5.3.6/bin/sqoop --options-file /opt/cdh-5.3.6/sqoop-1.4.5-cdh5.3.6/sqoop-import-mysql.txt --num-mappers $1

3 if [ $? -eq 0];then4 echo "success"

5 else

6 echo "error"

7 fi

2：創(chuàng)建??sqoop-import-mysql.txt 文件并編輯

touch sqoop-import-mysql.txt

1 export2 --connect3 jdbc:mysql://172.16.71.27:3306/babasport

4 --username5 root6 --password7 root8 --table9 test_hive10 --export-dir11 /user/hive/warehouse/hive_bbs_product_snappy12 --input-fields-terminated-by13 '\t'

hive數(shù)據(jù)存在hdfs位置

3：開始寫java后臺代碼? ?目前只支持 window寫法 后期加上linux調用shell腳本的寫法

1 packagecom.liveyc.common.utils;2

3 importjava.util.Properties;4

5 importorg.apache.commons.logging.Log;6 importorg.apache.commons.logging.LogFactory;7

8 public classFileToHbase {9 /**

10 * shell腳本執(zhí)行成功標識11 */

12 public static int SHELL_EXIT_OK = 0;13 public static Log log = LogFactory.getLog(FileToHbase.class);14 public static String connIp = "172.16.71.120";15 public static String connUser = "root";16 public static String connPwd = "123456";17

18 public static void main(String[] args) throwsException {19 boolean result =export();20 System.out.println(result);21 }22

23 public static boolean export() throwsException {24 boolean result = false;25 //如果當前系統(tǒng)是window系統(tǒng)需要遠程ssh連接系統(tǒng)

26 if(isWinSystem()) {27 ConnectShell connectShell = new ConnectShell(connIp, connUser, connPwd, "utf-8");28 String url = "/opt/cdh-5.3.6/sqoop-1.4.5-cdh5.3.6/sqoop_options.sh" + " " +1;29 result =connectShell.excuteShellCommand(url);30 }31 returnresult;32 }33

34 /**

35 * 當前操作系統(tǒng)類型36 *37 *@returntrue 為windos系統(tǒng)，false為linux系統(tǒng)38 */

39 public static booleanisWinSystem() {40 //獲取當前操作系統(tǒng)類型

41 Properties prop =System.getProperties();42 String os = prop.getProperty("os.name");43 if (os.startsWith("win") || os.startsWith("Win")) {44 return true;45 } else{46 return false;47 }48 }49 }

1 packagecom.liveyc.common.utils;2

3 importjava.io.BufferedReader;4 importjava.io.IOException;5 importjava.io.InputStream;6 importjava.io.InputStreamReader;7 importjava.io.UnsupportedEncodingException;8 importjava.nio.charset.Charset;9

10 importorg.apache.commons.logging.Log;11 importorg.apache.commons.logging.LogFactory;12

13 importch.ethz.ssh2.ChannelCondition;14 importch.ethz.ssh2.Connection;15 importch.ethz.ssh2.Session;16 importch.ethz.ssh2.StreamGobbler;17

18 /**

19 *20 * ConnectShell21 *22 * @Description:連接Shell腳本所在服務器23 *@author:aitf24 * @date: 2016年3月31日25 *26 */

27 public classConnectShell {28 privateConnection conn;29 privateString ipAddr;30 privateString userName;31 privateString password;32 private String charset =Charset.defaultCharset().toString();33 private static final int TIME_OUT = 1000 * 5 * 60;34 public static Log log = LogFactory.getLog(ConnectShell.class);35

36 publicConnectShell(String ipAddr, String userName, String password, String charset) {37 this.ipAddr =ipAddr;38 this.userName =userName;39 this.password =password;40 if (charset != null) {41 this.charset =charset;42 }43 }44

45 public boolean login() throwsIOException {46 conn = newConnection(ipAddr);47 conn.connect();48 return conn.authenticateWithPassword(userName, password); //認證

49 }50

51 /**

52 *53 * @Title: excuteShellCommand54 * @Description: 執(zhí)行shell腳本命令55 *@paramshellpath56 *@return

57 */

58 public booleanexcuteShellCommand(String shellpath) {59 InputStream in = null;60 boolean result = false;61 String str = "";62 try{63 if (this.login()) {64 Session session =conn.openSession();65 //session.execCommand("cd /root");

66 session.execCommand(shellpath);67 in = newStreamGobbler(session.getStdout());68 //in = session.getStdout();

69 str = this.processStdout(in, charset);70 session.waitForCondition(ChannelCondition.EXIT_STATUS, TIME_OUT);71 session.close();72 conn.close();73 if (str.contains("success")) {74 result = true;75 }else{76 result = false;77 }78 }79 } catch(IOException e1) {80 e1.printStackTrace();81 }82 returnresult;83 }84

85 public String excuteShellCommand2(String shellpath) throwsException {86 InputStream in = null;87 String result = "";88 try{89 if (this.login()) {90 Process exec = Runtime.getRuntime().exec(shellpath);//ipconfig

91 in =exec.getInputStream();92 result = this.processStdout(in, this.charset);93 }94 } catch(IOException e1) {95 e1.printStackTrace();96 }97 returnresult;98 }99

100 /**

101 * 轉化結果102 *103 *@paramin104 *@paramcharset105 *@return

106 *@throwsUnsupportedEncodingException107 */

108 public String processStdout(InputStream in, String charset) throwsUnsupportedEncodingException {109 String line = null;110 BufferedReader brs = new BufferedReader(newInputStreamReader(in, charset));111 StringBuffer sb = newStringBuffer();112 try{113 while ((line = brs.readLine()) != null) {114 sb.append(line + "\n");115 }116 } catch(IOException e) {117 log.error("---轉化出現(xiàn)異常---");118 }119 returnsb.toString();120 }121

122 }

4：開始測試

在mysql創(chuàng)建一個表? hive中數(shù)據(jù)格式 是? int int String

1 CREATE TABLE test_hive(2 id INT,3 brand_id INT,4 NAME VARCHAR(200)5 )

執(zhí)行java main方法 開始測試

觀看8088端口 查看MapReduce的運行狀況 發(fā)現(xiàn)正在運行(開心)

執(zhí)行完畢

可以看到 只有1個 MapReduce任務 (默認的個數(shù)是4個 這樣看來第一步寫的shell腳本 參數(shù)是傳遞過來了 sqoop的 options?也支持這種直接指定參數(shù)的寫法)

現(xiàn)在轉過來看java代碼

返回值 ：

1 Warning: /opt/cdh-5.3.6/sqoop-1.4.5-cdh5.3.6/bin/../../hbase does not exist!HBase imports will fail.2 Please set $HBASE_HOME to the root of your HBase installation.3 Warning: /opt/cdh-5.3.6/sqoop-1.4.5-cdh5.3.6/bin/../../hcatalog does not exist!HCatalog jobs will fail.4 Please set $HCAT_HOME to the root of your HCatalog installation.5 Warning: /opt/cdh-5.3.6/sqoop-1.4.5-cdh5.3.6/bin/../../accumulo does not exist!Accumulo imports will fail.6 Please set $ACCUMULO_HOME to the root of your Accumulo installation.7 Warning: /opt/cdh-5.3.6/sqoop-1.4.5-cdh5.3.6/bin/../../zookeeper does not exist!Accumulo imports will fail.8 Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation.9 success

發(fā)現(xiàn)返回 success 說明shell腳本執(zhí)行成功了

一切執(zhí)行正常? ?看下mysql 數(shù)據(jù)庫表中有沒有數(shù)據(jù)

OK 一切正常 ， 后期把linux執(zhí)行shell腳本的語句也補充上 。

總結

以上是生活随笔為你收集整理的hive运行mysql脚本_用java代码调用shell脚本执行sqoop将hive表中数据导出到mysql的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。