This document describes how to execute VQL statements on a Denodo Virtual DataPort (VDP) view using a JMS (Java Message Service) queue as query dispatcher.
The tutorial has been tested in the following environment:
Active MQ Server
- Active Mq Version: 5.15.12
To install Active MQ Server you can follow this tutorial.
- Denodo Server: 8.0
- Java Version: Denodo Internal JVM
Note: If you use Denodo 6 then you would need to change the JVM that Denodo uses. With this version of Active MQ you need a JVM version >=1.8 and Denodo 6 is shipped with JVM version 1.7. Instructions to change the VM are available here.
For more details about impact and compatibility of this operation, please see here.
To explain how to get data from a VDP view using JMS we are going to use Apache ActiveMQ (version 5.15.12) 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 is 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.
- Install the JMS software, in this example we will use Apache ActiveMQ (http://activemq.apache.org/download.html). Once installed, 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
- 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)
- Fill the queue name field and click on the “Create” button. Create a new queue named “jmstest_queue”.
- Repeat this step creating a queue for receiving the results, for the example call it “result_queue”
- Once the queues have been created, the queues list is updated showing the new queues.
- To be able 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.
- Copy them into <DENODO_HOME>/extensions/thirdparty/lib and restart the VDP server afterwards.
- From the VDP administration tool, go to File > New > JMS Listener menu or right-click on the Elements Tree and click on JMS Listener under the New menu. The VDP administration tool will display the wizard to create new JMS listeners.
- Enter the following values in the JMS Listener screen:
- Listener name: “jms_activeMQlistener”
- Listener status: “On”
- Output: “XML”
- Destination: “jmstest_queue”
- Destination type: “Queue”
- 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.
- After creating the listener, you can open the JMS Listener Status dialog (Tools > JMS Listeners menu) to see the list of existing JMS listeners.
- 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 the following fields:
- Destination: “jmstest_queue”
- Query or Topic: “Queue”
- Reply to: “result_queue”
- Message Body: SELECT * FROM bv_jmstest
And click “Send”
- Now, we can check if the message was received and processed correctly looking into the result_queue.
- Clicking in the result_queue name you can browse the pending message. Our message will appear in the message list.
- 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:
- Go to Tools > JMS Listeners.
- Click on the queue named “jms_activeMQlistener” to edit it.
- Check the box “JMS messages contain the value of the variable JMSEXPRESSION”
- Define the query to be executed, in this case:
SELECT * FROM bv_jmstest
WHERE field1 = '@JMSEXPRESSION'
- Click Save.
- 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.
- To select rows from the view, go to the ActiveMQ Message service (http://host:8161) and click on “Send to”.
- 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:
- 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 field1 = '1'
- Checking the result in the result_queue, it will return the rows of bv_jmstest that match the condition by field1.
Virtual DataPort Administration Guide: section JMS Listeners
Java versions supported by the Denodo Platform
Virtual Machine and Web Container Configuration