Zheng,
Thanks for the tips. I used your suggestions.
https://issues.apache.org/jira/browse/HIVE-645.
Zheng we are only outputting one query per row. The usage is to dump a table:
people
id name
1 Ed
2 Zheng
from people
select dboutput('jdbc:mysql', 'user','password', 'INSERT INTO people
(id,name) VALUES (?,?)', id, name);
@Frederick. There is no transaction here since we are making a
connection per row. A huge transactional dump would be tricky. Is
DBOutputFormat transactional.
The UDF returns 0 for success 1 for failure so you can do the status like:
from people
select id, dboutput('jdbc:mysql', 'user','password', 'INSERT INTO
people (id,name) VALUES (?,?)', id, name);
1,0
2,0
Edward
On Thu, Jul 16, 2009 at 8:23 PM, Zheng Shao<[email protected]> wrote:
> Hi Edward,
>
> We currently don't allow UDF/GenericUDF to output multiple rows with a
> single call to evaluate(...).
> Is that feature going to block you?
>
>
> Also, if you expect an argument to be a String, we should do:
> 1. Check the type of that argument in the intialize by doing:
> if (!(arguments[i] instanceof StringObjectInspector)) {
> throw new UDFArgumentTypeException(i, "..."));
> }
> 2. in evaluate function
> Use: ((StringObjectInspector)argumentOI[i]).getJavaObject(arguments[i].get())
> instead of arguments[i].get().toString()
>
> The reason for 2 is that the in-memory objects in Hive can have
> different format (which is encapsulated by ObjectInspector).
> This is for both efficiency and ease-of-integration with other systems.
>
> We have to always use the ObjectInspector to access the value of the
> parameters in GenericUDF.
>
>
> If the ObjectInspector stuff seems too complicated, you can try UDF
> which is much simpler.
>
>
> Zheng
>
> On Tue, Jul 14, 2009 at 7:57 AM, Edward Capriolo<[email protected]> wrote:
>> Hey all,
>>
>> I am working on a UDF that can be used with prepared statements ad a
>> technique to go from Hive->SQL. The usage would be something like
>> from src SELECT
>> dboutput("jdbc:mysql://localhost:20","user","pass","INSERT INTO A
>> (col1,col2) VALUES (?,?)", key,value)
>>
>> --Sniplet--
>> public class GenericUDFDBOutput extends GenericUDF {
>> private static Log LOG =
>> LogFactory.getLog(GenericUDFDBOutput.class.getName());
>>
>> ObjectInspector[] argumentOIs;
>> GenericUDFUtils.ReturnObjectInspectorResolver returnOIResolver;
>> Connection connection = null;
>> private String url;
>> private String user;
>> private String pass;
>> private IntWritable result = new IntWritable(-1);
>>
>> public ObjectInspector initialize(ObjectInspector[] arguments)
>> throws UDFArgumentTypeException {
>>
>> this.argumentOIs = arguments;
>>
>>
>>
>> public Object evaluate(DeferredObject[] arguments) throws HiveException {
>>
>> System.err.println("we are here");
>> url = arguments[0].get().toString()+";create=true";
>> url = "jdbc:derby:;databaseName=test_dboutput_db;create=true";
>> user = arguments[1].get().toString();
>> pass = arguments[2].get().toString();
>> try {
>> try {
>> Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
>> } catch (InstantiationException e) {
>> System.out.println(e);
>> } catch (IllegalAccessException e) {
>> System.out.println(e);
>> } catch (ClassNotFoundException e) {
>> System.out.println(e);
>> }
>> connection = DriverManager.getConnection(url, user, pass);
>> PreparedStatement ps = connection.prepareStatement(arguments[3].get()
>> .toString());
>> for (int i = 4; i < arguments.length; ++i) {
>> PrimitiveObjectInspector poi = ((PrimitiveObjectInspector)
>> arguments[i]);
>> ps.setObject(i - 3, poi.getPrimitiveJavaObject(arguments[i]));
>> }
>> ps.execute();
>> ps.close();
>>
>> --end snip--
>>
>> In my test case I am attempting to use the derby embedded driver since
>> I figured it would probably already be in the classpath.
>> org.apache.derby.jdbc.EmbeddedDriver. It is not. I took several stabs
>> and adding it to the ant files but I get the feeling that the
>> TestCliDriver is not inheriting those paths. Any suggestions?
>>
>> Thanks
>>
>
>
>
> --
> Yours,
> Zheng
>
