The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Execute embedded sql in scalar function

Hello All,

I am creating an external scalar function on Java. Is it possible to execute inline sql inside this function? What connection parameters should I use in this case?

Comments

  • Hi Roman,

    I believe it is possible, but then it is not recommended.

    You need to open a jdbc connection to the database and UDF executes parallel on the nodes in a cluster, it will open as many threads/processes on the nodes.

    A UDF is meant for transforming data and not retrieving data.

    Hope this helps
  • Hello Navin,

     I Just try to find how I can execute select into statements in procedure or function. Procedure is better. But in Vertica we have only external procedures that should be wrote only on script language and Java is not suit in this case.

    So I decide to write UDF and place into this select into statement.

    If you can advice better solution for me it will be great.

    Thank you 
  • if your requirement is to only execute select statement / insert statement then external procedure is the best way.
    If your requirement is like creating and using prepared statement to load database then go for writing programs in JAVA which can open a connection to vertica and execute your statement and close the connection. 

    For this you can refer to this blog: http://tomgendron.wordpress.com/2011/08/24/connecting-java-to-vertica/

    Hope this helps
  • Can I somehow use Java to create external procedure?
  • External procedure are only allowed to be written in shell script for now.
  • Thank you very much for your help, Navin.
  • For what it's worth:  External procedures can, I believe, technically be any executable command.  So you could write a standalone Java program and use that.  (Or use a shell script to invoke a Java program; etc.)
  • Hello, All!
    You are recommended this blog for connecting Java to Vertica, but I have problem with vertica driver at the moment. I use Java UDTf to refer to the database.
     http://tomgendron.wordpress.com/2011/08/24/connecting-java-to-vertica/

    My java class:
    package com.mycompany.example;
    import java.sql.*;
    import java.util.Properties;

    import com.vertica.sdk.*;

    public class TokenFactory extends TransformFunctionFactory{
        @Override
        public void getPrototype(ServerInterface srvInterface, ColumnTypes argTypes,ColumnTypes returnType){
            argTypes.addVarchar();
            returnType.addVarchar();
        }
        @Override ;
        public void getReturnType(ServerInterface srvInterface, SizedColumnTypes inputTypes,SizedColumnTypes outputTypes){
            outputTypes.addVarchar(inputTypes.getColumnType(0).getStringLength(),"Company");
            }
        @Override
        public TransformFunction createTransformFunction(ServerInterface srvInterface) {
            return new TokenizeString();
        }
        public class TokenizeString extends TransformFunction {
            @Override
            public void processPartition(ServerInterface srvInterface,PartitionReader inputReader,PartitionWriter outputWriter) throws UdfException,DestroyInvocation {
                Connection mConn;
                ResultSet rs=null;
                String Comp =inputReader.getString(0);
                Integer NUM =null;
                 Properties myPop=new Properties();
                  myPop.put("user", "dbadmin");
                  myPop.put("password", "vertica1");
                try {
                    mConn=DriverManager.getConnection("jdbc:vertica://localhost:5433/vertica1",myPop);
                    //mConn=DriverManager.getConnection("jdbc:vertica://192.168.2.140:5433/vertica1",myPop);
                    mConn.setAutoCommit(false);
                    PreparedStatement pstmt=mConn.prepareStatement("SELECT COMPANY FROM allcustomers  WHERE allcustomers.CUST_NUM = ?");
                    pstmt.setLong(1,NUM);
                    rs = pstmt.executeQuery();
                     if(rs.next()){
                         Comp=rs.getString(0);
                     }

                     
                }
                catch(SQLException se){
                    throw new UdfException(33,se.getMessage());
                }
                
                outputWriter.setString(NUM, Comp);    
                 
                 try {
                    mConn.commit();
                     mConn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
                
                                
        }
        
    }
        
    }

    Command line:
    dbadmin=> create library  token1  as '/home/dbadmin/java/select1.jar'dbadmin-> language 'Java';
    CREATE LIBRARY
    dbadmin=> create transform function to1  as language 'Java' name
    dbadmin-> 'com.mycompany.example.TokenFactory' library token1;
    CREATE TRANSFORM FUNCTION
    dbadmin=> select CUST_NUM,to1(COMPANY) over(partition by CUST_NUM) from allcustomers;

    ERROR 3399:  Failure in UDx RPC call InvokeProcessPartition(): Error in User Defined Object [to1], error code: 33com.vertica.sdk.UdfException: com.vertica.Driver
            at com.mycompany.example.TokenFactory$TokenizeString.processPartition(TokenFactory.java:36)
            at com.vertica.udxfence.UDxExecContext.processPartition(UDxExecContext.java:1432)
            at com.vertica.udxfence.UDxExecContext.run(UDxExecContext.java:233)
            at java.lang.Thread.run(Thread.java:744)

    Thank you for any help.
  • Java class with driver:
    package com.mycompany.example;
    import java.sql.*;
    import java.util.Properties;

    import com.vertica.sdk.*;

    public class TokenFactory extends TransformFunctionFactory{
        @Override
        public void getPrototype(ServerInterface srvInterface, ColumnTypes argTypes,ColumnTypes returnType){
            argTypes.addVarchar();
            returnType.addVarchar();
        }
        @Override ;
        public void getReturnType(ServerInterface srvInterface, SizedColumnTypes inputTypes,SizedColumnTypes outputTypes){
            outputTypes.addVarchar(inputTypes.getColumnType(0).getStringLength(),"Company");
            }
        @Override
        public TransformFunction createTransformFunction(ServerInterface srvInterface) {
            return new TokenizeString();
        }
        public class TokenizeString extends TransformFunction {
            @Override
            public void processPartition(ServerInterface srvInterface,PartitionReader inputReader,PartitionWriter outputWriter) throws UdfException,DestroyInvocation {
                Connection mConn;
                ResultSet rs=null;
                String Comp =inputReader.getString(0);
                Integer NUM =null;
                 Properties myPop=new Properties();
                  myPop.put("user", "dbadmin");
                  myPop.put("password", "vertica1");
                  try
                     {
                     Class.forName("com.vertica.Driver");
                     } catch (ClassNotFoundException e)
                        {
                         throw new UdfException(33,e.getMessage());
                      
                        }
                try {
                    mConn=DriverManager.getConnection("jdbc:vertica://localhost:5433/vertica1",myPop);
                    //mConn=DriverManager.getConnection("jdbc:vertica://192.168.2.140:5433/vertica1",myPop);
                    mConn.setAutoCommit(false);
                    PreparedStatement pstmt=mConn.prepareStatement("SELECT COMPANY FROM allcustomers  WHERE allcustomers.CUST_NUM = ?");
                    pstmt.setLong(1,NUM);
                    rs = pstmt.executeQuery();
                     if(rs.next()){
                         Comp=rs.getString(0);
                     }

                     
                }
                catch(SQLException se){
                    throw new UdfException(33,se.getMessage());
                }
                
                outputWriter.setString(NUM, Comp);    
                 
                 try {
                    mConn.commit();
                     mConn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
                
                                
        }
        
    }
        
    }

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.