You can translate the document:
CUSTOM COMPONENTS

The Virtual DataPort component of the Denodo Platform 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 the Denodo Platform. For these situations, Denodo Platform provides an API (in Java) which allows developers to implement Denodo custom components in their preferred Java code editor and import them later to the Denodo Platform.

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

  • 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 a starter point as the development of the rest of components is very similar. Check the Virtual DataPort Developer Guide and Scheduler Guide for more information on this.

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

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 Design Studio:

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 to the section Developing Custom Functions for more details on it.

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

  • Launch your preferred IDE and create a new Java project
  • Specify the project name as tutorial-custom-extensions for example
  • Add the library /lib/contrib/denodo-commons-custom.jar to the classpath of your project.
  • Copy this sample Java class and rename it to StringConcatenate.
  • 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. 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:

Ok, once the function is ready it's time to deploy it into Virtual DataPort. What you have to do is to export the project as a .jar file.

It is always a good idea to add to the MANIFEST.MF file of the jar, the meta information of our custom function. This is because the Design Studio will show this information when importing the extension. For example, we can add:

Now, let's open the Design Studio and click on File > Extension Management, and click on Import

Now, drag and drop the jar file you have generated:

Now you can see the jar extension tutorial-custom-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!).

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

Congratulations! You have created your first custom function.

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 is involved 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 function.

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.

Until now, we have seen how you can create a stored procedure from Denodo. Now let's 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 a stored procedure 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 the Denodo Platform to execute those queries (getting the data from the first point and inserting in the third point) but... what happens 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).

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 Denodo Basics tutorial so you have available in your Virtual DataPort the base views bv_crm_client and bv_crm_client_type.
  2. In your favorite IDE, add a new class to the project for our new Stored Procedure.
  3. For example, let's use this sample Java class and rename it to GetClients.java (and use the package com.denodo.vdp.custom.sp).
  4. Add the dependencies /lib/contrib/denodo-commons-custom.jar, /lib/contrib/denodo-commons-util.jar, /lib/vdp-server-core/denodo-vdp-server.jar, /lib/vdp-client-core/denodo-vdp-parser.jar to the project.

  1. By double clicking on the GetClients.java, you could see a template to implement the stored procedure. This template includes a Java code for doubling a number passed as input parameter:

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

  1. Logic: The stored procedure has to execute the VQL query
    SELECT name, surname, ssn FROM tutorial.bv_crm_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());.

Ok, once the stored 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. Export the project as a .jar file
  2. Now, let's open the Design Studio and click on File > Extension Management, and click on Import
  3. Drag and drop the new jar file and confirm the replacement of the extension.
  4. Now, the extension has two elements:
  5. Now click on the menu of the 8 - stored procedures folder and select + New > Stored procedure > Java stored procedure

  1. Enter the following:
    Name: sp_get_clients
    Class name    : com.denodo.vdp.custom.sp.GetClients
    Select jars: tutorial-custom-extensions
  2. Click on and you will see the schema of our stored procedure.

  1. To execute the stored procedure you have to click on and then click on Execute. The query results would be displayed:

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

Thanks!