You can translate the document:

Introduction

In this document we will see how to use the Denodo Scheduler API to run Scheduler jobs from Denodo Virtual DataPort (VDP). Some rules will be used to determine which Scheduler jobs need to be executed, to implement this logic we will use a VQL stored procedure.

First steps

Scheduler jobs

In this example, we have a database for looking up historical orders and current orders. We have in this database two base views (bv_historical_orders and bv_current_orders), in order to identify them.

We have configured the following jobs in Denodo Scheduler jobs that collect some statistics on the order:

  • The first job (job4) creates a report on the historical orders.
  • The second job (job5) creates a report on the current orders.

Both jobs just export some results to CSV files that are later consumed via two Delimited File data sources and their respective  base views.

For more information about job creation and configuration, check the Denodo KB article Creating a Denodo Scheduler Job.

Scheduler API

In order to execute a job using the Scheduler API,  we have followed the section Denodo Scheduler REST API of the KB Denodo article Starting a Denodo Scheduler Job based on Kafka Events.

You can access the documentation of the Denodo Scheduler API under the following URL:

http://<host>:<port>/webadmin/denodo-scheduler-admin/swagger-ui/index.html

where you have to specify the host and the port where your Denodo Scheduler is running.

To execute the  jobs, we will use the following Scheduler API endpoint:

  • PUT /webadmin/denodo-scheduler-admin/public/api/projects/{projectID}/jobs/status

This endpoint allows to change the status of one or more jobs, e.g. starting multiple jobs at the same time.

Job Execution

Data Source

Once the jobs have been created, we can create a JSON Data Source in order to make a request to the Scheduler API endpoint that executes a job. To create the JSON data source, go to the Server Explorer and then to New > Data source > JSON.

The Tool will display the dialog to create the data source:

  1. Data route: HTTP client.
  2. HTTP method: PUT
  3. Base URL:

http://localhost:9090/webadmin/denodo-scheduler-admin/public/api/projects/<project_id>/jobs/status?uri=%2F%2Flocalhost%3A8000 

In this url we have to introduce the project ID and we should put the server’s uri, localhost to access a local Scheduler server and port 8000 for the default port, but this might change.

  1. Post Body: @{post_body}. We are introducing the post body as an interpolation variable. In the post body we will be entering the id of the job to be executed.
  2. In the Authentication section provide the authentication details of the Denodo user that will be running the jobs.

Base View

This Scheduler API endpoint does not return any data that would allow us to create a base view in the conventional graphical way.  In this case, using VQL, we have to create a JSON Wrapper with some dummy output schema like the following:

#Creates a wrapper with an interpolation variable:

CREATE OR REPLACE WRAPPER JSON <WRAPPER_NAME>

DATASOURCENAME=<DATA SOURCE NAME>

TUPLEROOT '/JSONFile'

ROUTE HTTP 'http.CommonsHttpClientConnection,120000' PUT ''

POSTBODY '@{post_body}'

OUTPUTSCHEMA (        post_body = 'post_body' : 'java.lang.String' (OBL) (DEFAULTVALUE='{"action": "<default_action>","IDs": "<default_IDs>"}') EXTERN

    );

Then, we have to create a base view using the Wrapper:

CREATE OR REPLACE TABLE <VIEW_NAME> I18N de_euro (

post_body:text (extern)

    )

    CACHE OFF

    TIMETOLIVEINCACHE DEFAULT

    ADD SEARCHMETHOD <WRAPPER_NAME>(

        I18N de_euro

        CONSTRAINTS (

               ADD post_body (=) OBL ONE  

        )

        WRAPPER (json <WRAPPER_NAME>)

    );

Derived views

After creating the base view, we have to create two selection views with a where condition to provide the job id for the job to be executed.

 post_body = '{"action":"start", "IDs": "<job_id>"}' 

Click on the base view created and go to New > Selection and for the where condition introduce the conditions for each job and view.

VQL Stored Procedure

We will be using a VQL stored procedure to decide which job needs to be executed based on some inputs.

To create a new stored procedure go to File > New > Stored Procedure, introduce the VQL code and save it.

(order_id IN INTEGER, date_placed1 IN TIMESTAMP, order_type OUT VARCHAR, job_executed OUT INTEGER) # We define two input variables (order_id and date_placed1) and two output variables (order_type and job_executed)

AS (

        CURSOR cursordata IS 'SELECT oid, status, date_placed FROM bv_current_orders where  oid = :order_id'; # we define a cursor for accessing the current jobs base view

        status VARCHAR;

        oid INTEGER;

          date_placed TIMESTAMP;

        CURSOR cursordata2 IS 'SELECT post_body FROM runschedulerjob4'; # we define a cursor to execute the view that runs the job 4

        CURSOR cursordata3 IS 'SELECT post_body FROM runschedulerjob5'; # we define a cursor to execute the view that runs the job 5

        order_type VARCHAR;

        job_executed INTEGER;

)

BEGIN

        OPEN cursordata PARAMETERS ( order_id ) VALUES ( order_id ); # open the cursor with parameter (order_id)

        LOOP

            FETCH cursordata INTO oid, status,date_placed; #we start retrieving the results obtained

            IF oid = order_id and date_placed = date_placed1 THEN #check if the order introduced and the date_placed belongs to a current order, execute the job 5. Else the order belongs to historical orders and execute the job 4

                OPEN cursordata3; # open the cursor                  

                CLOSE cursordata3;# we close the cursor

                job_executed:=5;

                order_type :='Current Order';            

            ELSE

                OPEN cursordata2; # open the cursor          

                CLOSE cursordata2;#we close the cursor

                job_executed:=4;  

                order_type :='Historical Order';

            END IF;

            EXIT WHEN cursordata%NOTFOUND;

        END LOOP;

        CLOSE cursordata; # we close the cursor

   RETURN ROW ( order_type, job_executed) VALUES(order_type, job_executed); # return the type of the order introduced and the ID of the job executed

END

In the VQL above we have implemented a stored procedure that has as input parameters an order ID and the date when the order is placed. The values of these parameters  are compared with the ones in the current orders view. If the condition is met, it executes the job for current orders and returns the type of the order and the job that has been executed.  Otherwise,  if the value does not meet the condition, it executes the job for historical orders and returns the type of the order and the job that has been executed.

Executing the stored procedure

Once the stored procedure is created it can be executed to start the Scheduler job needed depending on the input parameters being provided.

Conclusions

This document presents a simple example where we decide which Scheduler job needs to be executed based on some business rules implemented with a VQL stored procedure..

References

Starting a Denodo Scheduler Job based on Kafka Events

Creating a Denodo Scheduler Job

Developing VQL Stored Procedures

Disclaimer
The information provided in the Denodo Knowledge Base is intended to assist our users in advanced uses of Denodo. Please note that the results from the application of processes and configurations detailed in these documents may vary depending on your specific environment. Use them at your own discretion.
For an official guide of supported features, please refer to the User Manuals. For questions on critical systems or complex environments we recommend you to contact your Denodo Customer Success Manager.

Questions

Ask a question

You must sign in to ask a question. If you do not have an account, you can register here