HowTo

Tips and Tricks

Start an Ad-Hoc Batch via Webservice

Objective: execute individual / multiple actions / sheets / folders directly without using the datasqill GUI

Assume the sheet with ID 45 and the action with ID 73 are to be executed. Create the following file run.json:

{
   "command":"RunElements",
   "variableList":[{"variableName":"environment", "variableType":"String", "variableValue":"DEV"}],
   "payload":{
      "batchExecution":{
         "batchName":"Test run",
         "user":"me",
         "elementList":[
            {
               "elementType":"S",
               "elementId":47
            },
            {
               "elementType":"A",
               "elementId":73
            }
         ]
      }
   }
}

On the datasqill host, execute the following command:

curl -d @run.json -H "Content-Type:application/json" http://localhost:17491/datasqill-server/service

changing the port 17491 if necessary. As a response you receive JSON with the batchInstanceId. This can be monitored to see when the batch has finished (or has failed):

{"requestId":4297280,"payload":{"batchInstanceId":4812038}}

The variableList section is optional. In this example, the variable environment is set to the value "DEV". It can be used in the transformations.

Time Change and Night Batches

It may be necessary to disable night batches during the time change. This process can be automated with datasqill using these steps:

1. Create a new function is_dst_change_today in the H2 repository.
2. Create a connection to the datasqill repository in datasqill.
3. Store the credentials for this connection in the keyfile.
4. Create a sheet "Reschedule" and therein an action with source and target and fill everything.
5. Create a new batch "Reschedule" that runs once every day.

is_dst_change_tonight

First, define a function is_dst_change_tonight in H2 using Java. This function determines whether a time change occurs in the coming night:

CREATE ALIAS IF NOT EXISTS is_dst_change_tonight AS $$
boolean isDstChangeTonight() {
    java.time.ZoneId zone = java.time.ZoneId.of("Europe/Berlin");
    java.time.ZonedDateTime now = java.time.ZonedDateTime.now(zone);
    java.time.ZonedDateTime nightEnd = now.plusDays(1).withHour(6).withMinute(0).withSecond(0).withNano(0);
    java.time.zone.ZoneRules rules = zone.getRules();
    java.time.Instant nowInstant = now.toInstant();
    java.time.zone.ZoneOffsetTransition nextTransition = rules.nextTransition(nowInstant);
    if (nextTransition != null) {
        java.time.ZonedDateTime transitionTime = nextTransition.getDateTimeBefore().atZone(zone);
        return !transitionTime.isBefore(now) && !transitionTime.isAfter(nightEnd);
    }
    return false;
}
$$;

Create Connection

Then create a connection to the datasqill repository in datasqill. Connections can be found under Connections, in the tree on the left under Settings:

With Add, add a new connection "datasqill Repository" and a schema "DATASQILL_REPOSITORY".

Store Credentials

For the new connection, credentials must be stored in the keyfile. The easiest way is to copy the existing SQTS_DB_0 entry and make it the entry for the new connection number.

Create Sheet and Action with Source and Target

On a new sheet, create an action with source and target:

The objects for source and target are:

• Type: Table or View
• Connection: datasqill Repository
• Schema: DATASQILL_REPOSITORY
• Table: datasqill_batch_next_run

The action gets a name, type Upsert and as SQL this query:

SELECT batch_id
     , start_dt + interval '1' DAY AS start_dt
  FROM datasqill_batch_next_run
 WHERE batch_id IN (6790)
   AND is_dst_change_tonight()
   AND start_dt <= CURRENT_TIMESTAMP + interval '14' hour

This sets the start date for the batches in the list (IN clause) one day further. In the example this is the batch with ID 6790.

Furthermore, the condition is checked that a time change actually occurs in the next night.

And finally, only batches that run within the next 14 hours are considered. This ensures that with multiple executions of this action, the planned execution time is only shifted once.

Create Batch "Reschedule"

Finally, create a new batch "Reschedule" that executes the sheet daily. The execution time should be before all other batches that this batch shifts during time change.

In the example, batch 6790 runs every night at 22:00, so the Reschedule batch was scheduled for 19:00:

Manage JDBC Drivers

To simplify the management of installed JDBC drivers, the jdbcManager program can be used. As of version 4.1.4 it is located in the "$HOME/bin" directory with the other datasqill scripts.

After startup, jdbcManager shows the installed JDBC drivers. With ADD, new drivers can be added; with DELETE, existing drivers can be removed:

On first start, no JDBC drivers are shown. With ADD, drivers can be added.

Then a selection of JDBC drivers appears, from which one can be chosen for download and installation.

A progress bar appears and after download completion a confirmation dialog.

Then the driver is installed in datasqill and appears in the list in the main menu.

Further drivers can be installed incrementally in this way.

The available drivers and their download URLs are managed via the configuration file "jdbcList", which is in the same directory as the script itself. It looks like this in principle:

# JDBC Driver Configuration
# Format: <name>:<description>:<maven-repo-url>

# datasqill CSV JDBC Driver
DATASQILL-CSV:datasqill CSV Driver:https://download.softquadrat.de/master/jdbc/csvjdbc.jar

# datasqill Excel JDBC Driver
DATASQILL-EXCEL:datasqill Excel Driver:https://download.softquadrat.de/master/jdbc/exceljdbc.jar

# datasqill Json JDBC Driver
DATASQILL-JSON:datasqill Json Driver:https://download.softquadrat.de/master/jdbc/jsonjdbc.jar

# datasqill Salesforce JDBC Driver
DATASQILL-SALESFORCE:datasqill Salesforce Driver:https://download.softquadrat.de/master/jdbc/sfjdbc.jar

...

If needed, simply add further drivers with their name and download URL.

Valid URLs begin with "http:", "https:" or "file:". The latter format allows installation of files that already exist on the server, for example on a mounted fileshare.

# my jdbc driver
MY-DRIVER:My Jdbc Driver:file:///home/myuser/mydriver.jar