You can add user-defined functions (UDF) to extend the built-in functions provided by Big SQL. By using a Big SQL UDF, your application logic can process the columns during a Big SQL query. A UDF can be an aggregate function, which returns a single value; a row function, which returns one row; or a table function, which returns a table.

The following steps describe how to create a Java™ user-defined table function and using it from a select query.

1. Create the Java user-defined table function:

a. Create a Java project in Eclipse.

JavaProject in Eclipse

b. Create a Java class getAllMembersUDTF. See the last section in this blog for the complete Java code.

createjavaclass

c. Create a Java jar (/opt/bigSQLUDF/BigSQLUdf.jar) from the Java project.

create javajar

2. Install the jar file in Big SQL

InstalljarfileinBigSQL

3. Register the user-defined external table function

Registeringuserdefinedfunction

4. Provide proper execute privileges to the UDF

executionprivileges

5. Invoke the UDF from a query

invokeudf

Complete Java code

[code language=”java”]package com.testudf;

import java.util.List;

import java.util.ArrayList;

import java.util.Iterator;

import COM.ibm.db2.app.UDF;

public class getAllMembersUDTF extends UDF {

        List<String> keys = new ArrayList<String>();

        List<String> values = new ArrayList<String>();

        Iterator<String> valuesIter;

        Iterator<String> keysIter;

        public void exec(String document, String outKey, String outValue)

throws Exception {

//getCallType(): This method returns the type of call that is currently being made.

switch (getCallType()) {

//These states correspond with the possible states that a SQL cursor can be.

//OPEN Table Call

case UDF.SQLUDF_TF_OPEN:

if (document == null) {

return;

}

//We will have place holder (variable keys & values) to store the extracted items

if (document.indexOf(",") > 0) {

String[] tokens = document.trim().split(",");

for (String token : tokens) {

if (token.indexOf("=") > 0) {

keys.add(token.substring(0, token.indexOf("=")));

values.add(token.substring(token.indexOf("=") + 1));

}

}

} else {

keys.add("NULL");

values.add("NULL");

}

keysIter = keys.iterator();

valuesIter = values.iterator();

break;

// This call type is the actual fetch where we start sending rows to client

case UDF.SQLUDF_TF_FETCH:

if (keysIter == null || !keysIter.hasNext()) {

setSQLstate("02000");

return;

}

if (keysIter.hasNext()) {

// set function used to set values for each column.

set(2, keysIter.next());

set(3, valuesIter.next());

}

break;

default:

break;

}

}

}[/code]

 

Big SQL query

[code language=”bash”]$JSQSH_HOME/bin/jsqsh bigsql -Ubigsql -Pbigsql
 
\install-jar –sqlj=/opt/bigSQLUDF/BigSQLUdf.jar –id=My_Sample
 [/code]

 

[code language=”sql”] CREATE OR REPLACE FUNCTION HIVE.GETALLMEMBERS (
 
jsonDocument varchar(32672)
 
)
 
RETURNS TABLE (
 
key VARCHAR(128),
 
value VARCHAR(128)
 
)
 
SPECIFIC GETALLMEMBERS
 
EXTERNAL NAME ‘My_Sample:com.testudf.getAllMembersUDTF!exec’
 
LANGUAGE JAVA
 
PARAMETER STYLE DB2GENERAL
 
FENCED
 
NO SQL
 
DISALLOW PARALLEL
 
FINAL CALL
 
NO EXTERNAL ACTION
 
GRANT EXECUTE ON FUNCTION HIVE.getAllMembers TO PUBLIC
 
select * from table(HIVE.getAllMembers(‘name=Nisanth,phone=123 456789’))[/code]

Join The Discussion

Your email address will not be published. Required fields are marked *