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.
Export Data with Integer Parameters
The Export Data with Integer Parameters action will take data produced by an SQL Query and export it to one or more destinations, including an S3 Bucket, an FTP server, an SFTP server or an FTPS server. This action requires that the source data be sequentially sorted on a integer stamp key. Whenever your Task runs, it will execute your SQL Query against the database specified in your Target Database Connection in accordance with the parameters that you have specified. The result data will be formatted and stored in a file in the location or locations that you have specified.
This Task requires you to provide a remote database Connection, an SQL Query to perform on the database, parameters for narrowing the range of data processed based on integer information and one or more export result destination Connections.
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 Query
Type in an SQL Query that will generate the data that you want to export. In your query, 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 Query will pull the values from columns named column_1, column_2 and column_3 in a table named yourtable in your database for the rows where the value in your_integer_column is between range_start_point and range_end_point:
SELECT column_1, column_2, column_3
FROM yourtable
WHERE your_integer_column >= {{ range_start_point }}
AND your_integer_column < {{ range_end_point }}
The example above assumes that you are processing your data in ascending order. If you are processing your data in descending order, adjust the comparison operators in your SQL Query accordingly. For example:
SELECT column_1, column_2, column_3
FROM yourtable
WHERE your_integer_column <= {{ range_start_point }}
AND your_integer_column > {{ range_end_point }}
For the Export Data with Integer Parameters action, your SQL Query is restricted to a single statement.
If you have previously saved your SQL Query 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 Query contains parameters, use this field to specify the parameter values.
For example, yourvalue is a parameter in this SQL Query:
SELECT column_1, column_2, column_3
FROM yourtable
WHERE column_1 = {{ yourvalue }}
AND your_integer_column >= {{ range_start_point }}
AND your_integer_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 integer 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 integer 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 last row's value plus one if you are processing your data in ascending order or minus one if you are processing your data in descending order. This field can be modified between Task runs, if desired.
When a Task starts, the Next Run Start Point is used to automatically set the initial value of the range_start_point variable used in your SQL Query. After the beginning of the Task run, the value of the range_start_point variable is then updated 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 Query
Type in an SQL Query that returns the integer value where the processing of your sequentially sorted data should stop. Once a row with a greater integer stamp, if you are processing your data in ascending order, or a lesser integer stamp, if you are processing your data in descending order, is encountered, no more rows will be processed. For example, this SQL Query will set the End Point value to the maximum value in your_integer_column:
SELECT MAX(your_integer_column)
FROM yourtable
Using a MAX or MIN function in your End Point Query is typical for exporting the latest information from 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 integer stamp of the next row after the last successfully processed row in the increment. Each increment will generate a different output file, however, so ensure that files that you are creating match their usage needs. The value in this field should be positive if you are processing your data in ascending order or negative if you are processing your data in descending order. Note that, for negative values, a smaller number is a larger process increment. For example, -100 is a larger process increment than -50.
When a Task starts, the Process Increment value is combined with the range_start_point value and the result is compared to the result of the End Point Query. If you are processing your data in ascending order, the lesser of the two results becomes the value of the range_end_point variable used in your SQL Query. If you are processing your data in descending order, the greater of the two results becomes the value of the range_end_point variable used in your SQL Query. 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 combined with the new range_start_point value and the result is compared to the result of the End Point Query. If you are processing your data in ascending order, the lesser of the two results becomes the new value of the range_end_point variable. If you are processing your data in descending order, the greater of the two results becomes the new value of the range_end_point variable. If the range_start_point 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:
S3 Bucket Connection
S3 Bucket access information is stored in an S3 Connection on your Nominode.
This link contains more information about creating a Connection on a Nominode:
The S3 Connection has seven fields.
- Set the Alias field to something meaningful like "YourS3BucketFolder as YourUser".
- Set the Bucket field to the name of the S3 bucket.
- For non-AWS hosted buckets, set the S3 Endpoint URL field to the S3 URL used to access the bucket.
- Set the Folder Path Prefix field to the full path to the folder within the S3 bucket that the Connection will point to.
- Set the S3 Temporary Path field to the full path to a folder within the S3 bucket that can be used for temporary file storage. A folder named "temp" off the root of the S3 bucket will be created, if it doesn't exist, and used if this field is left blank.
- Set the Access Key ID field to the first part of your AWS access key.
- Set the Secret Access Key field to the second part of your AWS access key.
This link contains more information about AWS access keys:
Once the Connection is created, you can select its Alias from the drop down list for the Target Database Connection field on this Task.
S3 Folder Path
Type in the path from the starting folder in the S3 bucket to the folder where you would like the task to generate the export file. This path is appended to the Folder Path Prefix in the S3 Connection document, so do not include that part of the path in this field value. Folders in the path that do not exist will be created. Folder paths can include formatted datetime variables. These variables are available:
- now_datetime (datetime in your Nominode's time zone when the processing of the current Process Increment ended)
- now_iso_datetime (datetime in UTC format when the processing of the current Process Increment ended)
- now_date (date only portion of the datetime in your Nominode's time when the processing of the current Process Increment ended)
- now_iso_date (date only portion of the datetime in UTC format when the processing of the current Process Increment ended)
For example:
/ParentFolder/{now_iso_datetime:%Y%m%d%H%M%S}_YourFolderName
Variables defined and set within your SQL Query can also be used. For example:
/ParentFolder/{{YourDateVariable}:%Y%m%d%H%M%S}_{YourFolderNameVariable}
Datetime format syntax follows Python formatting syntax:
%Y Year
%m Month (as number)
%d Day
%H Hour
%M Minute
%S Second
The table in this link lists more formatting options:
If you have previously saved your S3 Folder Path 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:
FTP Connection
FTP access information is stored in an FTP Connection on your Nominode.
This link contains more information about creating a Connection on a Nominode:
The FTP Connection has seven fields.
- Set the Alias field to something meaningful like "YourFTPServer as YourUser".
- Set the FTP Type field to the FTP, FTPS (FTP with TLS) or SFTP (FTP via SSH).
- Set the Hostname field to the DNS hostname of the FTP, FTPS or SFTP server.
- Set the Username field to the username for authentication with the FTP, FTPS or SFTP server.
- Set the Password field to the password for the username ,if you are connecting to an FTP server, an FTPS server or an SFTP server that allows password based authentication, which is uncommon. If you are connecting to an SFTP server that uses key based authentication, leave this field blank.
- Set the SSH Private Key field to the private key to use to authenticate and encrypt traffic, you are connecting to an SFTP server. This field is required even if the SFTP server allows password based authentication. If you are connecting to an FTP or FTPS server, leave this field blank.
- Set the FTP Port field to the port number that the FTP server is listening on. 21 is the typical port for FTP and explicitly negotiated FTPS. 990 is the typical port for implicit FTPS. 22 is the typical port for SFTP.
These links contain more information about FTPS and SFTP:
Once the Connection is created, you can select its Alias from the drop down list for the FTP Connection field on this Task.
FTP Folder Path
Type in the path from the root of the FTP server to the folder where you would like the task to generate the export file. Folders in the path that do not exist will be created. Folder paths can include formatted datetime variables. These variables are available:
- now_datetime (datetime in your Nominode's time zone when the processing of the current Process Increment ended)
- now_iso_datetime (datetime in UTC format when the processing of the current Process Increment ended)
- now_date (date only portion of the datetime in your Nominode's time when the processing of the current Process Increment ended)
- now_iso_date (date only portion of the datetime in UTC format when the processing of the current Process Increment ended)
For example:
/ParentFolder/{now_iso_datetime:%Y%m%d%H%M%S}_YourFolderName
Variables defined and set within your SQL Query can also be used. For example:
/ParentFolder/{{YourDateVariable}:%Y%m%d%H%M%S}_{YourFolderNameVariable}
Datetime format syntax follows Python formatting syntax:
%Y Year
%m Month (as number)
%d Day
%H Hour
%M Minute
%S Second
The table in this link lists more formatting options:
If you have previously saved your FTP Folder Path 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:
File Name
Type in the name of the file to create containing the exported data. File names can include formatted datetime variables. These variables are available:
- now_datetime (datetime in your Nominode's time zone when the processing of the current Process Increment ended)
- now_iso_datetime (datetime in UTC format when the processing of the current Process Increment ended)
- now_date (date only portion of the datetime in your Nominode's time when the processing of the current Process Increment ended)
- now_iso_date (date only portion of the datetime in UTC format when the processing of the current Process Increment ended)
For example:
{now_iso_datetime:%Y%m%d%H%M%S}_YourFileName
User Defined Parameters can also be used. For example:
{{YourDateVariable}:%Y%m%d%H%M%S}_{YourFileNameVariable}
Datetime format syntax follows Python formatting syntax:
%Y Year
%m Month (as number)
%d Day
%H Hour
%M Minute
%S Second
The table in this link lists more formatting parameters:
Allow Overwrite
This field determines what this Task will do if a file exists in the Folder Path with the same name as the value of the File Name field. If this field is set to Enabled, that file will be overwritten. If this field is set to Disabled, the Task run will fail.
Select the format, JSON or Delimited, for the exported data.
Delimiter
If you have selected delimited as the Format Type, type in the delimiter to use to separate columns of data in your output file.
To use a character escape sequence, refer to the table in this link:
By default, a tab character will be used.
Escape Character
Type in the character that precedes character escape sequences in your source data so that they will be properly recognized during processing. By default, a backslash will be treated as the start of a character escape sequence.
Null Value
Type in the value that you want to be placed in your exported data to represent a null value encountered in your source data. By default, nothing will be written in your export data when a null is encountered.
Add Quotes
If you have selected delimited as the Format Type, enable this field to wrap any string values encounters in your source data in a pair of double quotes in your exported data. Disable this field otherwise. Set this field to enabled is recommended when the string values in your source data contain the character you have chosen as the delimiter in exported data. For example, if you have chosen a comma character as your delimiter and the string values in your source data contain commas.