CUSTOM COMPONENTS

Denodo Virtual DataPort includes out-of-the-box several functions and stored procedures which can be used to implement specific operations.

At some point, you may find that you need a function or stored procedure which doesn't exist in Denodo Platform. For these situations, Denodo Platform provides an API (in Java) which allows developers to implement them in their Java code editor (we recommend Eclipse, in order to use the Denodo4E plugin) and import them later to the Denodo Platform.

At the end of this tutorial, you will learn about:

  • Install Denodo4E plugin
  • How to develop a Virtual DataPort Custom Function
  • How to implement a Stored Procedure

Of course, using the API you can develop more components! For example, custom data sources in Virtual DataPort, custom policies, Scheduler custom exporters and handlers. You can use this tutorial as starter point as the development of the rest of componets is very similar. Check the Virtual DataPort Developer Guide and Scheduler Guide for more information on this.

Before starting with this tutorial you will require to install the following resources in your workstation:

Application

Download links

Denodo Platform 8.0

Go to https://support.denodo.com/ and download the installer and your license.

Eclipse Neon (4.6) or Oxigen (4.7)

Go to https://www.eclipse.org/downloads/ to download and install it.

Denodo Platform includes an Eclipse plugin called Denodo4E, through which Java developers could create, deploy and debug custom components like functions, stored procedures, data sources, etc.

Plugin installation

First of all, let us see how to install the Denodo4E plugin:

  1. By default, the Denodo4E plugin files are located in the directory $DENODO_HOME/tools/denodo4e/updateSite of your Denodo 8.0 installation.
  2. For installing the plugin in Eclipse you have to launch Eclipse and click on Install New Software on the menu Help.
  3. We are going to use the plugin folder as local repository in Eclipse. For doing that, click the Add button and enter the name as "Denodo" in the Add Repository dialog.
  4. Click Local and browse to the updateSite folder of your Denodo Installation folder and click Add.

  1. The following dialog will be shown. Ensure you have enabled the check box Contact all update sites during install to find required software.

  1. Click Next > to review the Denodo license agreement and Click Finish.

Once the installation is completed, restart Eclipse and you could see new Denodo4E buttons available in the Eclipse Toolbar.

And also, when you navigate to the option File > New > Project, you could see a "Denodo4E" option available which would be used to create new custom components (you will learn about it in detail in next sections).

Next we are going to create our first custom component in Denodo!

Developing a Virtual DataPort Custom Function

As a next step, let's move on to developing a Virtual DataPort Custom function in which you can extend the set of functions available in Denodo Virtual DataPort.

Virtual DataPort Functions

In order to see the list of existing functions, you can open the dialog Help > Functions list in your Virtual DataPort Administration Tool:

Implementing new Virtual DataPort Functions

Custom functions are implemented as Java classes included in a Jar file that is added to Virtual DataPort as extension. There are some general rules to keep in mind before developing these functions, you can refer the section Developing Custom Functions for more details on it.

You could create a Custom function by using "annotations" or following some "name conventions". We will use the first approach as it is the recommended one!.

Now let's learn how to develop a StringConcatenate function which would join two or more strings together!

  • Launch Eclipse, create a new project by navigating to File> New > Project:

  • Create a new Denodo Extension project by choosing Denodo4E > Denodo Extension project and click on Next >.

  • Specify the project name as my_denodo_extensions and click Next >:

  • Select the path to your local Denodo Platform installation.
  • Choose Denodo VDP/ITP Server as Denodo application and Denodo VDP Custom Function as Extension configuration.
  • Enter the package, e.g. com.denodo.vdp.custom.functions, and the Java class name, e.g. StringConcatenate and Click Finish.

  • Now you could see that StringConcatenate.java file gets created in the Package Explorer and the needed dependencies get imported to the project classpath:

  • When you double-click on the StringConcatenate.java file, you will see a template to implement the Virtual DataPort Custom Function (it is automatically created). This template consists of a simple function returning the length of an input String.

As you can see the java class contains several annotations to indicate Virtual DataPort the following:

  1. @CustomElement(type=CustomElementType.VDPFUNCTION, name="STRINGCONCATENATE"): the Java class contains the code of a Virtual DataPort custom function called STRINGCONCATENATE.
  2. @CustomExecutor: method containing the code that Virtual DataPort will have to run when the custom function is invoked.
  3. @CustomParam(name = "s") String s: input parameters definition (Note: See the equivalency table between Java and Virtual DataPort data types).
  4. @CustomExecutorReturnType: method invoked by Virtual DataPort to obtain the return type of the custom function.
  • For implementing the new function you only have to modify the Java code of this function:

Deploy our Custom Function into Virtual DataPort

Ok, once the function is ready it's time to deploy it into Virtual DataPort (it has to be launched before you deploy the extension!). The Denodo4e plugin allows to deploy the extensions created in the Eclipse project. You only have to follow these steps:

  1. Right click on the project my_denodo_extensions and select Deploy Extensions:

  1. Provide the connection details to the Virtual DataPort server in the Denodo VDP/ITP server configuration section and the metadata information of the extension you are going to deploy: JAR name, description, version, target Denodo version and update and click Finish.

  1. From your Virtual DataPort Administration tool, first click on File > Refresh to load the changes. Then navigate to File > Extension Management, you will find a new extension!

  1. You could see the jar extension denodo-tutorial-extensions was added. Also you can check the dialog Help > Functions list to see the new function (with the syntax defined in the @CustomExecutor annotation!).

  1. Finally, you can test the new function, for example, from the VQL Shell:

I want to know more advanced techniques!

Is it possible to have different signatures for the same custom function?

Yes, you only have to add more methods having the @CustomExecutor annotation, for example:

@CustomExecutor

public Integer method1(Integer i) { ... }

@CustomExecutor

public Integer method2(Integer i, String s) { ... }

Could my custom function be delegated to a JDBC data source?

Yes, you only have to configure the @CustomExecutor annotation adding the delegation pattern, for example:

@CustomExecutor(implementation = true, delegationPatterns = {

@DelegationPattern(databaseName = "sqlserver",

pattern = "CONCAT($0[, $i]{1, n})") })

In that example Virtual DataPort will delegate the execution of the function to SQL Server (using the CONCAT() function of SQL Server), whenever it is possible. If a different data source in envolved in the query then Virtual DataPort will execute the code implemented in the custom function instead of delegating it. Find more examples here.

That's all! You have created your first custom fucntion.

In the previous section, you have learnt about developing Virtual DataPort custom functions. Denodo Platform also provides an API to develop stored procedures written in Java. In this section, we will learn how to create a new stored procedure in Denodo.

Introduction to VQL Stored Procedures

Starting from Denodo Platform 8.0 March 2023 Update, Virtual DataPort Server now allows users to create a Custom Stored Procedure using PL/VQL. The benefits of these procedures compared to Java stored procedures are:

  • The SQL developers do not need to know Java to develop stored procedures.
  • VQL procedures are easier to modify than Java procedures: you do not need to recompile a Java class, package it in a .jar file and import it again; you can modify the procedure from the Design Studio, the Administration Tool or any client application.
  • It is easier for other users to review your procedure because they do not need to download the .jar file with the extension, open it and extract the Java class in another editor.

Basic Syntax - Variable Declaration

A VQL Stored Procedure will have the following sections:

  • A header- that includes the declaration statement and the global variable declaration
  • A body - that includes the commands and the exceptions

The header of the VQL Stored Procedure, starts with declaration of the Stored Procedure name

contains INPUT and OUTPUT Variables and contains the AS () having global variables declared.

VQL Stored Procedure variables support Data Types such as Numeric, Text, Dates and other types.

The body of the Stored Procedure is where the declaration for all the calculations and all the operations that a Stored Procedure has to perform are placed. This starts with the BEGIN keyword and ends with an END keyword. Inside this body of the stored procedure is where you would be providing the Control Statements, calculations for variables, calling on CURSORs as well as statements for displaying the results.

VQL Stored Procedures also allow for declaring and call of Exceptions. The exceptions are called in the body of the stored procedure during execution. INSERT, UPDATE and DELETE operations over a view can also be performed in the body of the stored procedure without a need to declare a CURSOR.

Creating a New VQL Stored Procedure

Now let's see how we can create a new stored procedure from the Denodo Design Studio. Open Design Studio and navigate to File >New >Stored Procedure >VQL Stored Procedure option

In the structure of the stored procedure you can find the following:

  1. First, we can find the header of the Stored Procedure. Here you must define the parameters of the Stored Procedure. Those parameters can be input parameters defined by IN clause, output parameters defined by the OUT clause, or parameters that can be used as input or output, defined with the IN OUT clause.
  2. Then, in the AS section, you must define the different variables needed by the Stored Procedure. If you need to execute a query, you can create a cursor that processes a SELECT statement and stores in the cursor the results of that query.
  3. Finally, the body of the Stored Procedure is located between the BEGIN and END clauses where you must define the logic of your Stored Procedure. If needed you can also declare your own exceptions by using the EXCEPTION clause.

Now, we let's create a simple VQL stored procedure to find the greatest of 3 numbers. Paste the following code in the Configuration part of the Stored Procedure and name the procedure as VQL_SP_MAX_VALUE

(valuea IN INTEGER, valueb IN INTEGER, valuec IN INTEGER, valmax OUT INTEGER)

AS (

a INTEGER;

b INTEGER;

c INTEGER;

abcmax INTEGER;

)

BEGIN

a:=valuea;

b:=valueb;

c:=valuec;

IF a > b THEN

IF a > c THEN

abcmax:=a;

ELSE

abcmax:=c;

END IF;

ELSE

IF b > c THEN

abcmax:=b;

ELSE

abcmax:=c;

END IF;

END IF;

RETURN ROW ( valmax) VALUES (abcmax);

END;

Once done, click on Save. Now, you will notice that stored procedure is created successfully in Denodo

Now, let's execute the procedure by providing the input values.

  • Valuea = 10
  • Valueb = 40
  • Valuec = 20

Well done! Now you have successfully created your first Denodo VQL Stored procedure.

Conditions and Control Statements

The VQL Stored Procedure can use several conditions and control statements. Those commands can be included in the body of the VQL Stored Procedure, between the BEGIN and END keywords. These are the commands you can use in a VQL procedure:

  • Variable Declarations (Eg: variable_name := value )
  • IF/THEN/ELSE
  • CASE WHEN
  • LOOP Statements (LOOP...END LOOP, WHILE, FOR LOOP)

Now, let's create a VQL Stored Procedure that will execute a Loop statement and return the result. This stored procedure will return for us end dates between two dates

For this exercise, lets just open the VQL shell and paste the following code

CREATE OR REPLACE VQL PROCEDURE getmonthenddates

(startdate IN TIMESTAMP, enddate IN TIMESTAMP, monthenddate OUT TIMESTAMP)

AS (

processdate TIMESTAMP;

)

BEGIN

processdate:=startdate;

WHILE processdate < enddate LOOP

processdate:=lastdayofmonth(processdate);

RETURN ROW ( monthenddate) VALUES (processdate);

processdate:=addmonth(processdate, 1);

END LOOP;

END;

.

Click on Execute to create the Stored Procedure.

This Stored Procedure we created describes the following:

  • It has 2 input values (startdate and enddate) of type TIMESTAMP and one output value
  • In the Global variable declaration (AS () section) we have declared 2 variables that would be used to perform the calculations.
  • In the body, the Input value is parsed to months and the last date of the month function is used to fetch the last date of each month in each iteration

Now lets execute the procedure from the VQL shell. Execute the following command

SELECT * FROM getmonthenddates() WHERE startdate='2023-01-25' AND enddate='2023-07-14'

Great job! Now you have successfully created a Stored procedure using conditional and control statements.

Cursors

The VQL stored procedures also support cursors.

  • A cursor processes a SELECT statement and stores in the cursor the results of that query.
  • The Select statement can be over: Views Stored procedures, including predefined, java or VQL stored Procedures.
  • DDL commands like INSERT,DELETE,AND UPDATE do not require a cursor to be defined.

All the information related to PL/VQL Denodo Stored Procedures can be found in the document Developing VQL Stored Procedures

Excited to try more? Here is a simple exercise you could practice.

  1. Create a loop to get 5 consecutive integers : Provide a starting number as integer input and use FOR LOOP to get the next 5 consecutive integers

What is a Java Stored Procedure and Why sometimes they are needed

Until now, we have seen how you can create a stored procedure from Denodo. Now lets see how you can create a Java stored procedure. Basically, a stored procedure is a program containing a logic which is going to be used by several clients.

Typically is composed by a group of VQL queries. Let's see an example, imagine we have this requirement:

  1. Execute a query to get a result set from a final view.
  2. Execute some validation over those results based on the input parameters
  3. Execute an insert over a base view of a data source used for logging purposes (storing the result of the validation)
  4. Return a summary of the execution to the calling application along with the result set

Of course this logic could be executed by the client application. It could connect to Denodo Platform to execute those queries (getting the data from the first point and inserting in the third point) but... what happen if more applications have to use the same logic? Are you going to implement the same in ALL applications?

In these cases, it makes sense to define the logic inside of a stored procedure so external applications could execute it directly (the execution is centralized in a single point and we obtain other benefits, like for example, reducing the network traffic between the Denodo server and the client).

Implementing a new Stored Procedure

Now let's see the development process of a Denodo Stored Procedure. In this tutorial, you are going to create a simple stored procedure called GetClients to return all the Clients of type "Residential".

  1. First of all, ensure you have done the Basics Tutorial so you have available in your Virtual DataPort the base views client and client_type.
  2. Launch Eclipse, an open our existing Denodo Extension project: my_denodo_extensions.
  3. Right click over the project and select New > Other. In the dialog, select Denodo Extension and click on Next >:

  1. Choose Denodo VDP Stored Procedure as Extension and give a package (com.denodo.vdp.custom.sp) and class name (GetClients):

  1. Click Finish and you will see that GetClients.java file gets created in the Package Explorer.
  2. By double clicking on the GetClients.java, you could see that a template to implement the stored procedure was created. By default, the template includes a Java code for doubling a number passed as imput parameter:

  1. In this tutorial, we are going to modify only the getParameters and doCall methods for modifying the list of input/output paramenters and for implementing the logic.
  2. Parameters: No input paramenters / Output paramenters: name, surname and ssn of the clients:

  1. Logic: The stored procedure has to execute the VQL query
    SELECT name, surname, ssn FROM client
    WHERE client_type = '01';

    and return all the output rows:

  1. If any error occurs, they will be written to the Virtual DataPort log file (%DENODO_HOME%/logs/vdp/vdp.log) by invoking this.environment.log(LOG_ERROR, e.getMessage());.

Deploy our Stored Procedure into Virtual DataPort

Ok, once the procedure is ready, as you know, it has to be deployed into Virtual DataPort. You have to follow the same steps you did for deploying the custom function:

  1. Right click on the project my_denodo_extensions and select Deploy Extensions.
  2. In the dialog a new option for creating the Stored Procedure will appear (it is also a good idea to modify the value of the version of our extension, as we have added a new component). Finally, click Finish.

  1. From your Virtual DataPort Administration tool, first click on File > Refresh to load the changes. You will see a new Stored Procedure in the tutorial database!

  1. To execute the stored procedute you have to double-click and then click on Execute and click Ok.

  1. Stored procedure will be executed and query results would be displayed:

You have done an excellent work in completing the tutorial for Denodo Custom components.

Thanks!