一。使用hive向mysql插入数据.
这个是比较简单的,因为hive提供了一个udf。所以咱们自己不用开发代码。
具体的步骤为:
(1)add jar /home/hUser/apache-hive-0.13.1-bin/lib/hive-contrib-0.13.1.jar;
(2)add jar /home/hUser/apache-hive-0.13.1-bin/lib/mysql-connector-java-5.1.13.jar;(需要提前将jar包粘贴到这,hive本身是没有的)
(3)CREATE TEMPORARY FUNCTION dboutput AS 'org.apache.hadoop.hive.contrib.genericudf.example.GenericUDFDBOutput';
(4)select dboutput('jdbc:mysql://datanode2:3306/hivetest','hive','123456','INSERT INTO nginxLog(ip,url) VALUES(?, ?) ', ipaddress,referer) from userInfoTest;
说明下:
1. 前两步是将需要到的jar包加载到当前的classpath中。
2. 第3步是创建一个临时的方法,就跟数据库函数的max()啥的都一样,创建完之后你就可以使用这个函数。
3. dboutput输入意思是:
1.jdbcurl 2.数据库用户名 3 密码 4 sql 5之后的就是刚才sql用到的预查询用到的参数。
4. dboutput输出有3个,0:成功 1 sql报错 2 数据库连接错误。 5. 鉴于一般每次重启hive的cli 都需要重新执行前3步,比较麻烦,有2个办法可以解决
(1)修改hive源码,FunctionRegistry.java 添加这个UDF(这个比较麻烦,我没有试验过)
(2)在启动hive的时候加载文件,使用hive -i xxx ,xxx是你的配置文件,你可以把前三步的命令都写进去,那他每次启动的时候就自动加载了。
二。使用hive向sqlserver中导入数据。
这个其实跟mysql也是一样的,正常来说也可以用上面的函数,但是由于jdbc url中带有个";",所以导致一直报错,所以一气之下决定修改GenericUDFDBOutput这个类。思路就是多加了一个参数。附上源码
package com.hive.udf.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.serde.serdeConstants;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.PrimitiveObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.StringObjectInspector;
import org.apache.hadoop.io.IntWritable;
public class GenericSQLServerDBOutput extends GenericUDF {
private static final Log LOG = LogFactory.getLog(
GenericSQLServerDBOutput.class.getName());
private transient ObjectInspector[] argumentOI;//参数
private transient Connection connection = null;
private String url;//jdbc url
private String user;//用户名
private String pass;//密码
private String dbName;//数据库名称
private final IntWritable result = new IntWritable(-1);//返回参数
//初始化方法,确定返回类型
@Override
public ObjectInspector initialize(ObjectInspector[] arguments)
throws UDFArgumentTypeException {
argumentOI = arguments;
for (int i = 0; i < 4; i++) {
if (arguments[i].getCategory() == ObjectInspector.Category.PRIMITIVE) {
PrimitiveObjectInspector poi = ((PrimitiveObjectInspector) arguments[i]);
if (!(poi.getPrimitiveCategory() ==
PrimitiveObjectInspector.PrimitiveCategory.STRING)) {
throw new UDFArgumentTypeException(i,
"The argument of function should be \""
+ serdeConstants.STRING_TYPE_NAME
+ "\", but \"" + arguments[i].getTypeName()
+ "\" is found");
}
}
}
for (int i = 4; i < arguments.length; i++) {
if (arguments[i].getCategory() != ObjectInspector.Category.PRIMITIVE) {
throw new UDFArgumentTypeException(i,
"The argument of function should be primative"
+ ", but \"" + arguments[i].getTypeName()
+ "\" is found");
}
}
return PrimitiveObjectInspectorFactory.writableIntObjectInspector;
}
@Override
public Object evaluate(DeferredObject[] arguments) throws HiveException {
url = ((StringObjectInspector) argumentOI[0])
.getPrimitiveJavaObject(arguments[0].get());
user = ((StringObjectInspector) argumentOI[1])
.getPrimitiveJavaObject(arguments[1].get());
pass = ((StringObjectInspector) argumentOI[2])
.getPrimitiveJavaObject(arguments[2].get());
dbName = ((StringObjectInspector) argumentOI[3])
.getPrimitiveJavaObject(arguments[3].get());
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
connection = DriverManager.getConnection(url + ";DatabaseName=" + dbName,
user, pass);
} catch (SQLException ex) {
LOG.error("Driver loading or connection issue", ex);
result.set(2);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
if (connection != null) {
try {
PreparedStatement ps =
connection.prepareStatement(((StringObjectInspector) argumentOI[4])
.getPrimitiveJavaObject(arguments[4].get()));
for (int i = 5; i < arguments.length; ++i) {
PrimitiveObjectInspector poi =
((PrimitiveObjectInspector) argumentOI[i]);
ps.setObject(i - 4, poi.getPrimitiveJavaObject(arguments[i].get()));
}
ps.execute();
ps.close();
result.set(0);
} catch (SQLException e) {
LOG.error("Underlying SQL exception", e);
result.set(1);
} finally {
try {
connection.close();
} catch (Exception ex) {
LOG.error("Underlying SQL exception during close", ex);
}
}
}
return result;
}
@Override
//其用于Hadoop task内部,在使用到这个函数时来展示调试信息
public String getDisplayString(String[] children) {
StringBuilder sb = new StringBuilder();
sb.append("dboutput(");
if (children.length > 0) {
sb.append(children[0]);
for (int i = 1; i < children.length; i++) {
sb.append(",");
sb.append(children[i]);
}
}
sb.append(")");
return sb.toString();
}
}
然后步骤就跟上面一样了,只不过在调用函数的时候在password之后加上一个数据库的名称就OK了。
(1)add jar /home/hUser/apache-hive-0.13.1-bin/lib/hive-contrib-0.13.1.jar;
(2)add jar /home/hUser/apache-hive-0.13.1-bin/lib/sqljdbc.jar;(需要提前将jar包粘贴到这,hive本身是没有的)
(3)CREATE TEMPORARY FUNCTION sqlserver AS 'com.hive.udf.test.GenericSQLServerDBOutput';
(4)from (
select * from urlvisitinfo ul
) a select sqlserver('jdbc:sqlserver://192.168.181.6:1433','sa','cailiqiang','jml_301_140324','INSERT INTO urlFromInfo(url,cishu,createDate) VALUES( ?,?,getDate()) ', a.url,a.cishu);
这个就不说明了,跟上面的完全一样。
三。hive向hbase插入数据。
在下篇里继续写:http://cdelliqi.blog.51cto.com/9028667/1538614
转载于:https://blog.51cto.com/cdelliqi/1435849