SQL Runner
SQL Runner is used to execute an SQL Query or Script against a database and optionally store the results in a desired location.
Run SQL Script with Datetime Parameters
The Run SQL with Datetime Parameters action will run an SQL Script on a datetime bounded range of data. This action requires that the source data be sequentially sorted on a datetime stamp key. Whenever your Task runs, it will execute your SQL Script against the database specified in your Target Database Connection in accordance with the parameters that you have specified.
This Task requires you to provide a remote database Connection, an SQL Script to perform on the database and parameters for narrowing the range of data processed based on datetime information.
Alias
All Tasks contain this field. Set an Alias name that is meaningful and memorable as it will appear in the list of Tasks on your Nominode.
Target Database Connection
SQL Database access information is stored in a Generic:Database Connection on your Nominode.
This link contains more information about creating a Connection on a Nominode:
The Generic:Database Connection has seven fields.
- Set the Alias field to something meaningful like "YourDatabase on YourSQLServer".
- Leave the Engine Type field set to the default value of "mysql".
- Set the Hostname field to the DNS name of the server running the software hosting your database.
- Set the Database Name field to the name of your database.
- Set the Username field to the name of the SQL user that has the desired access to your database.
- Set the Password field to the SQL user's password.
- Set the Port field to the port number that your database server is listening on. MySQL servers listen on port 3306 by default.
Once the Connection is created, you can select its Alias from the drop down list for the Target Database Connection field on this Task.
SQL Script
Type in an SQL Script to execute on the data that you want to process. In your script, use the variables range_start_point and range_end_point which are automatically calculated and populated based on the values in the Range section of this Task. For example, this SQL Script will change the values in column_1 to Strawberry in a table named yourtable in your database for the rows where the value in your_datetime_column is between range_start_point and range_end_point:
UPDATE yourtable
SET column_1 = 'Strawberry'
WHERE your_datetime_column >= {{ range_start_point }}
AND your_datetime_column < {{ range_end_point }}
If you have previously saved your SQL Script as a Shared Object on your Nominode, you can select the Shared Object to populate this field.
This link contains more information about creating a Shared Object on a Nominode:
User Defined Parameters
If your SQL Script contains parameters, use this field to specify the parameter values. For example, yourvalue is a parameter in this SQL Script:
UPDATE yourtable
SET column_1 = {{ yourvalue }}
WHERE your_datetime_column >= {{ range_start_point }}
AND your_datetime_column < {{ range_end_point }}
To specify the value "Strawberry" for the yourvalue parameter when this Task runs, set this field to:
{"yourvalue": "Strawberry"}
If you have previously saved your User Defined Parameters as a Shared Object on your Nominode, you can select the Shared Object to populate this field.
This link contains more information about creating a Shared Object on a Nominode:
Next Run Start Point
Type in the datetime stamp where the processing of your sequentially sorted data should begin. Each time an increment of data is successfully processed, this field value will automatically be updated to the datetime stamp of the next row after the last row processed to allow the next increment of processing or the next run of the Task to begin at that point. If there is no next row, then this field value will be automatically set to the current datetime. This field can be modified between Task runs, if desired.
If you have previously saved your Range parameters as a Shared Object on your Nominode, you can select the Shared Object to populate this field.
This link contains more information about creating a Shared Object on a Nominode:
Start Point Buffer
Type in a value that adjusts the actual starting point a fixed amount of time prior to the value in the Next Run Start Point field. This pre-start buffer can be used to ensure that any new data created during the previous Task run is not missed. It can also be used if your data, though sequentially datetime sorted, is not actually created in a sequential manner matching the datetimes. For example, if the previous run of this Task ended its run processing a row with a timestamp of 11 am, but a new row with a timestamp of 10 am is later added to your database after the Task run completed, an appropriately sized buffer will ensure that the row is included in the next run of your Task, even though the Next Run Start Point was automatically set to 11 am. Typical values for this field range from zero to a day or two depending on the volatility of your row creation, how often your Task runs and length of time your Task takes to run. Using a Start Point Buffer will result in previously processed data within the buffer span getting processed again. Be sure to account for this if you set a value other than zero in this field.
Value format is a number followed by a letter, based on this table:
M Month
D Day
H Hour
T Minute
S Second
For example, the value 1D would indicate a buffer of one day.
When a Task starts, the Start Point Buffer value is subtracted from the Next Run Start Point value to automatically set the initial value of the range_start_point variable used in your SQL Script. After the initial adjustment for the beginning of the Task run, the value of the range_start_point variable is then updated normally without the buffer after each data increment is successfully processed during the Task run.
If you have previously saved your Range parameters as a Shared Object on your Nominode, you can select the Shared Object to populate this field.
This link contains more information about creating a Shared Object on a Nominode:
End Point
Type in the datetime stamp where the processing of your sequentially sorted data should stop. Once a row with a later datetime stamp is encountered, no more rows will be processed. If this field is left blank, all rows with a datetime stamp between the starting point and the current datetime will be processed. The 'current datetime' is the time on the Nominode server when the processing of the most recent increment of data concludes, not the time when the Task began. Leaving this field blank is typical for processing the latest information in a database on an ongoing basis.
If you have previously saved your Range parameters as a Shared Object on your Nominode, you can select the Shared Object to populate this field.
This link contains more information about creating a Shared Object on a Nominode:
Process Increment
This Task will process your data in increments. Set a value in this field to determine the range of each increment. Choosing a smaller process increment will allow your Task to use less memory and temporary disk space on your Nominode. Smaller process increments also improve the recoverability of your Task if it is stopped, intentionally or otherwise, prior to completion, because the Next Run Start Point is automatically updated to the datetime of the next row after the last successfully processed row in the increment.
Value format is a number followed by a letter, based on this table:
M Month
D Day
H Hour
T Minute
S Second
For example, the value 1H would indicate a increment of one hour.
When a Task starts, the Process Increment value is added to the range_start_point value and the result is compared to the End Point field value and the current datetime on the Nominode server. The lesser of the three values becomes the value of the range_end_point variable used in your SQL Script. During the Task run, after each data increment is successfully processed, the range_start_point value is updated to the current range_end_point value. Then the Process Increment value is added to the new range_start_point value and the result is compared to the End Point field value and the current datetime on the Nominode server. The lesser of the three values becomes the new value of the range_end_point variable. If the range_start_point value and the range_end_point values match, the task ends.
If you have previously saved your Range parameters as a Shared Object on your Nominode, you can select the Shared Object to populate this field.
This link contains more information about creating a Shared Object on a Nominode: