Getting data from Denodo Virtual DataPort through JMS

Goal

This document describes how to execute VQL statements on a Denodo Virtual DataPort (VDP) view using a JMS (Java Message Service) queue as a query dispatcher.

Environment

This document has been tested in the following environment:

Active MQ Server

Please note the following on the ActiveMQ download page (as of testing):

The client JMS API Client used differs for the versions of ActiveMQ.

5.17.x (Javax JMS 1.1)

5.18.x (Javax JMS 1.1/Jakarta JMS 2)

6.x (Jakarta JMS 2/3.1)

Denodo 8.0 and older versions support JMS 1.1. Hence, a 5.17.x or older version of the ActiveMQ server would work with this.

The upgrade on libraries in Denodo 9.0, due to an upgrade to Java 17, removed old javax API support meaning it will not work with 5.17.x and older versions of ActiveMQ. However, it is compatible with JMS 2. So Denodo 9.0 will work with the 6.x version of ActiveMQ.

NOTE: We have mentioned the 5.18.x version of ActiveMQ because it supports both JMS 1.1 and JMS 2. However, this version lacks the drivers for compatibility with Denodo 8.0. In the case of Denodo 9.0, the drivers try to use Javax JMS which then leads to an error trying to start the JMS listener. So, our recommendation would be to avoid this version of ActiveMQ altogether.

You can download Active MQ Classic from here. For older versions, you can see the list of past releases. For installation and configuration of ActiveMQ Server, you can follow this tutorial.

Denodo Server

• Denodo Server: 8.0 / 9.0
• Java Version: Denodo Internal JVM

Content

To explain how to get data from a VDP view using JMS, we are going to use Apache ActiveMQ as the JMS broker.

For this example, we have created a table named “jmstest” in a relational database. A JDBC data source and the base view called bv_jmstest are imported into Virtual DataPort to integrate this source.

To perform a select over the view via JMS we need to create the JMS Listener first.

1. Install the Apache ActiveMQ. Once done, to start the JMS service execute the script activemq(.bat/.sh) located under the installation folder. To check the status of the JMS service you can view the logs, by default located under $APACHE_ACTIVEMQ_HOME/data 

2. Once installed, you may need to configure the default ports used by ActiveMQ:

• JNDI provider port: the default port for the provider URL is 8161. It is possible to change the default port by editing the  $APACHE_ACTIVEMQ_HOME/conf/jetty.xml file.
• Broker port: the default port for the broker URL is 61616, it can be modified by editing the $APACHE_ACTIVEMQ_HOME/conf/activemq.xml file.

Restart the service to apply the changes.

• Now, we will have to create a queue that will receive the messages. In order to create a queue, go to http://localhost:8161/admin/queues.jsp (Note that we are using the JNDI provider default port) or click on Queues.

3. Create a new queue named “jmstest_queue” by filling in the queue name field and clicking on the “Create” button.

4. Repeat this step creating a queue for receiving the results, for the example call it “result_queue”
5. Once the queues have been created, the queues list is updated showing the new queues.

6. To use Apache ActiveMQ from Virtual DataPort, the client libraries for the JMS server need to be added to the Virtual DataPort classpath. They are located in the lib folder of the apache-activemq installation.

For Denodo 8.0 and older:

• activemq-client-5.17.6.jar
• geronimo-j2ee-management_1.1_spec-1.0.1.jar
• geronimo-jms_1.1_spec-1.1.1.jar
• hawtbuf-1.11.jar
• slf4j-api-2.0.12.jar

        For Denodo 9.0:

• Activemq-broker-6.1.1.jar
• Activemq-client-6.1.1.jar
• Activemq-openwire-legacy-6.1.1.jar
• hawtbuf-1.11.jar

7. Copy them into <DENODO_HOME>/extensions/thirdparty/lib and restart the VDP server afterwards.

8. From the VDP Design Studio, go to File > New > Listener > JMS Listener menu or click on menu on the the Elements Tree and click on Listener > JMS Listener under the New menu. The Design Studio will display the wizard to create new JMS listeners.

9. Enter the following values in the JMS Listener screen and save it:

• Listener name: “jms_activeMQlistener”
• Listener status: “On”
• Output: “XML”
• Destination: “jmstest_queue”
• Destination type: “Queue”
• Ignore reply to: deselect this option.
• Reply to: the name of the second queue, which will receive the responses from the Virtual DataPort server. In this case, “result_queue”.
• User name: admin
• Password: admin
• JMS Vendor: Apache ActiveMQ
• JNDI provider url: “tcp://host:port”, the default port for the JNDI provider is 8161.
• Broker url: “tcp://host:port”, the default port for the broker is 61616.

10. After creating the listener, you can open the JMS Listener Status dialog (Tools > Listener > JMS Listeners menu) to see the list of existing JMS listeners.

11. In order to send queries to VDP, go to the ActiveMQ Message service (http://localhost:8161) and click on “Send to”.

The message that we are going to send is a SELECT command to select all the rows from a view.

To do it, fill in the following fields:

• Destination: “jmstest_queue”
• Query or Topic: “Queue”
• Reply to: “result_queue”
• Message Body: SELECT * FROM bv_jmstest

        And click “Send”

12. Now, we can check if the message was received and processed correctly by looking into the result_queue.

13. Clicking in the result_queue name you can browse the pending message. Our message will appear in the message list.

14. Clicking in the Message ID, information about the message and the response can be checked. The query result will appear as XML in the field Message Details.

We can decide to not use a full VQL sentence as the input for the queue. As an example, we may just need to provide the value to filter in the bv_jmstest view by field1.

It is possible to configure the JMS listener to execute a parameterized query using the interpolation variable @JMSEXPRESSION. Once the query has been defined, it will be executed using the values provided for the @JMSEXPRESSION variable.

Follow these steps to modify the existing JMS listener to filter data by field1 in the bv_jmstest table using an interpolation variable:

1. Go to Tools > JMS Listeners.
2. Click on the queue named “jms_activeMQlistener” to edit it.
3. Check the box “JMS messages contain the value of the variable JMSEXPRESSION”
4. Define the query to be executed, in this case:

                SELECT * FROM bv_jmstest

WHERE <field> = '@JMSEXPRESSION'

5. Click Save.

6. Now, the message to send from the JMS service will contain only the values to filter so the new input will not contain any VQL syntax.
7. To select rows from the view, go to the ActiveMQ Message service (http://host:8161) and click on “Send to”.
8. Now we can send a message for the new Listener. Note that it is only necessary to send a message containing the values to be included in the VQL sentence defined in the queue. The values of the Message body will replace the JMSEXPRESSION in the query. For instance:

9. The  @JMSEXPRESSION will be replaced by the Message body ‘1’. So this query will be executed in the VDP server:

SELECT * FROM bv_jmstest

WHERE name = 'works'

10. Checking the result in the result_queue, it will return the rows of bv_jmstest that match the condition by name.

References

Virtual DataPort Administration Guide: section JMS Listeners

Apache ActiveMQ

Java versions supported by the Denodo Platform

Virtual Machine and Web Container Configuration