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 Datetime Parameters
The Export Data with Datetime 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 datetime 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 datetime 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_datetime_column is between range_start_point and range_end_point:
SELECT column_1, column_2, column_3
FROM yourtable
WHERE your_datetime_column >= {{ range_start_point }}
AND your_datetime_column < {{ range_end_point }}
For the Export Data with Datetime 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_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 export duplication for previously processed data within the buffer span. 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 Query. 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 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 datetime 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.
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 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 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:
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.