Krypton


To enhance your DataSync integration for Amazon Redshift, you can configure the meshlet to the directives listed below. To check out the general meshlet configurations, see General Meshlet Configurations for DataSync.

DirectiveDefault ValueDescription
username

Redshift database username. 

perspectium:
	redshift: 
		username: perspectium
password

Redshift database password. 

perspectium:
	redshift: 
		password: somePasswordHere

database


Redshift database name.

perspectium:
	redshift: 
		database: database_name
schema

Redshift schema name.

perspectium:
	redshift: 
		schema: schema_name
role
An IAM role set up in AWS that grants you permission to access Amazon S3, e.g. arn:partition:service:region:account-id:resource-type/resource-id.

The role must have permissions to LIST and GET objects from the Amazon S3 bucket being used. The IAM role ARN, or Amazon Resource Name, is a required directive to leverage the COPY command. 

For more information, see AWS credentials and access permissions.

perspectium:
	redshift: 
		role: arn:aws:iam:123456789123:role/myRedshiftRole
connectionUrl

The JDBC URL for your Amazon Redshift cluster connection, e.g. jdbc:redshift://endpoint:port/database.

perspectium:
	redshift: 
		connectionUrl: jdbc:redshift://examplecluster.abc123xyz789.us-west-2.redshift.amazonaws.com:5439/dev
postInterval5

Minutes to check dead periods. Check every x minutes to compare if the in memory collection is the same as the last x minutes. If so, write records to file and push to Amazon Redshift. 

perspectium:
	redshift: 
		postInterval: 5
truncateColumnstrue

SUPPORTED IN KRYPTON 8.0.3 AND NEWER

Related to the maximum column lengths in databaseConfig.json as described below, truncate data if it is longer than a column's maximum length. If false, the data will not be truncated and the record will attempted to be inserted as is. If an error occurs (the default behavior), it will be logged in the meshlet's log file. 

perspectium:
	redshift: 
		truncateColumns: true
maxFileSize

Required Configuration. This configuration specifies the max size for temporary files created as the meshlet pushes records to Amazon Redshift. 10000 will be used if input is over 10000 to prevent possible performance and memory issues. A suggested value is 5000.

perspectium:
	filesubscriber: 
		maxFileSize: 5000
customFileName$table-$randomid

Names file with format of table - random id. File names MUST be unique.

perspectium:
	filesubscriber: 
		customFileName: $table-$randomid
fileDirectory/files

Directory where the locally created files get made. (In respects to where application is running).

perspectium:
	filesubscriber: 
		fileDirectory: /files
deleteFilestrue

Indicates whether you want to keep or delete locally created CSV files. Will not have a noticeable performance hit.

perspectium:
	filesubscriber: 
		deleteFiles: true
fileCleanerInterval1

How often (in minutes) the file cleaner job will run to clean up local files created by the meshlet. This job is similar to the ServiceNow Data Cleaner Scheduled Job. For example, value of 4 will run the job every four minutes. 

perspectium:
	filesubscriber: 
		fileCleanerInterval:
fileMaxAge1

Any csv file in the filesDirectory older than fileMaxAge in minutes, will be automatically deleted. 

perspectium:
	filesubscriber: 
		fileMaxAge: 1
legacyDbViewIdfalse

Derivative to use the legacy version for database view tables of concatenating GUID values into a sys_id field. If false, meshlet will use the pre-constructed encoded sys_id created by ServiceNow.

perspectium:
	filesubscriber: 
		legacyDbViewId: false
accessKey

Authorized AWS user's access key to the S3 bucket. 

For more information, see AWS credentials and access permissions.

perspectium:
	s3: 
		accessKey: AKIAIOSFODNN7EXAMPLE
secretKey

Authorized AWS user's full secret access key to the S3 bucket.

For more information, see AWS credentials and access permissions.

perspectium:
	s3: 
		secretKey: wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY
destinationBucket

The name of the S3 bucket where data will be bulk loaded from.

perspectium:
	s3: 
		destinationBucket: redshift-bulk-loading
region

The region the S3 bucket is located in. 

perspectium:
	s3: 
		region: us-east-1

databaseConfig.json

databaseConfig.json is a configuration file that contains specific configurations to the target database the meshlet is connecting to, in this case Redshift. databaseConfig.json should be stored in the meshlet's static/config folder located where the meshlet jar resides. 

(info) NOTE: This configuration file is for advanced use and should not be modified unless discussed with Perspectium Support. Changes here may result in unintended behavior with Redshift.

Configuration SectionDescription
SQLSyntax

Format of specific SQL commands used by the meshlet with the database.

These are specific SQL commands the meshlet will always used as opposed to the dynamic commands it uses to insert/update records into tables.

tableDefinitionList of table definitions (such as the table's primary key field) the meshlet uses for creating tables.
typesThe translation of ServiceNow field types to Redshift's data types. For example, if the field is an integer in ServiceNow, it will be created as a BIGINT in Redshift.
maxLengthColumns

The maximum length supported for different data types in Redshift.

This relates to being able to support matching the length of the field in ServiceNow to the column in Redshift. Lengths can only be matched for the Redshift data types listed in this section up to the maximum length specified here, which is the maximum length supported by Redshift.

(info) NOTE: Changing these values to a value greater than Redshift's defined maximum length will cause errors.

The meshlet performs the column length logic as follows:

  • Columns will be created in Redshift to match the length in ServiceNow if the length is less than Redshift's maximum length.
  • For any ServiceNow fields that have a length greater than the maximum length allowed in Redshift, those will be created with the maximum size Redshift allows as described in this configuration and data will be truncated if the above truncateColumns meshlet configuration is true (the default setting). For example, if a String field is defined in ServiceNow with 1,000,000 characters, it will be created as a VARCHAR column in Redshift with a size of 65,535 characters and any characters above this will be truncated in order to fit into the column.
  • As the meshlet processes a batch of records, if it sees a record with a field that has content greater than the current Redshift table column length, it will alter the column to have the maximum length supported in Redshift. This approach is similar to what the DataSync Agent does when it encounters a record with a field bigger than the current column length, altering it to be the maximum length possible to try and avoid losing any of the data's content as much as possible. For example, if the short_description field in the incident table is defined as 4000 characters originally in ServiceNow and the column in Redshift is created as VARCHAR(4000), if a new record is received that has greater than 4000 characters (such as the field is changed in ServiceNow to be greater in length to 100,000 characters), than the column in Redshift will be modified to VARCHAR(65535). 
  • By default, based on the combination of types and maxLengthColumns defined in databaseConfig.json, this logic only applies to VARCHAR data types since VARCHAR is the default mapping for most ServiceNow field types and the other mappings for fields do not support altering their field lengths. This is similar to the DataSync Agent. Other maxLengthColumns fields are included if any types mapping are changed but care should be taken and reviewed with Perspectium Support prior to making changes. 
errorsList of common errors the meshlet may encounter when running SQL commands against the database.


An example of a databaseConfig.json is as follows:

{
   "SQLSyntax":{
      "showTables":"SELECT DISTINCT tablename FROM pg_table_def WHERE tablename=?;",
      "selectAll":"SELECT * FROM %s LIMIT 1",
      "addColumn":"ALTER TABLE %s ADD COLUMN",
      "primaryKey":"Primary Key(%s)",
      "createTable":"CREATE TABLE IF NOT EXISTS %s",
      "modifyColumn":"ALTER TABLE %s ALTER COLUMN %s TYPE"
   },
   "tableDefinition":{ 
      "primaryKeys":[
      "sys_id"
      ]
   },
   "types":{
      "integer":"BIGINT",
      "boolean":"BOOLEAN",
      "glide_date":"DATE",
      "glide_date_time":"TIMESTAMPTZ",
      "float":"FLOAT",
      "reference":"VARCHAR",
      "default":"VARCHAR"
   },
   "NOTE": "The 'maxLengthColumns' and 'errors' configurations are strictly related to the database. Please do not change them without first consulting the database documentation",
   "maxLengthColumns": {
      "CHAR": 4096,
      "VARCHAR": 65535,
      "BINARY VARYING": 1024000,
      "VARBINARY": 1024000,
      "VARBYTE": 1024000
   },
   "errors": ["String length exceeds DDL length", "too long", "size <= 1024000"]
}
  • No labels