Amazon Redshift is a cloud-based data warehouse service.

Tonic Structural can move data from one database to another within a single Amazon Redshift instance. Structural can also move data between Amazon Redshift instances.

In both cases, Structural uses Amazon S3 as an intermediate stage to host both the original data and the masked data.

When it uses Amazon Redshift, Tonic Structural orchestrates the creation, usage, and deletion of several AWS components.

The required permissions to do so are taken from the Instance Profile role of the machine that runs Structural's server. This role (EC2) needs the permissions listed below.

Note that these permissions are starting point. Based off your exact AWS setup, you might need to add additional permissions. For example, you might need to grant AWS Key Management Service (AWS KMS) access if you use AWS KMS on your S3 buckets. Go to the .

The above policy allows Structural to properly orchestrate jobs in your AWS infrastructure. It assumes that you use default names for objects in AWS, and that your source and destination S3 buckets begin with the "tonic-" prefix.

User permissions on the source database

The following is an example of how to create an Amazon Redshift user with the permissions needed to connect to Tonic Structural.

We recommend that you use a backup as your source database instead of connecting directly to your production environment.

User permissions on destination database

The destination database must exist before Structural can connect to it. The user provided to Structural for connecting to the destination database must be a superuser who holds ownership and privileges of all schemas and tables.

During workspace creation, under Connection Type, click Redshift.

Connecting to the source database

In the Source Settings section, provide the details about the source database:

Specifying the source connection details

To provide the connection details for the source database:

1. In the Server field, provide the server where the database is located.

2. In the Database field, provide the name of the database.

3. In the Port field, provide the port to use to connect to the database.

4. In the Username field, provide the username for the account to use to connect to the database.

5. In the Password field, provide the password for the specified username.

6. In the S3 Bucket Path field, specify the S3 bucket where Tonic Structural places temporary CSV files that it uses to load and unload Amazon Redshift tables. During data generation, CSV files containing the source data are copied to an input folder in the S3 bucket. After the generators are applied, CSV files containing the destination data are copied to an output folder in the S3 bucket.

7. To test the connection to the source database, click Test Source Connection.

Ensuring encryption of source database authentication

The Enable SSL/TLS setting indicates whether to encrypt source database authentication data.

By default, the toggle is in the on position. We strongly recommend that you do not turn off this setting.

Blocking data generation on all schema changes

By default, data generations are not blocked when schema changes do not conflict with your workspace configuration.

To block data generation when there are any schema changes, regardless of whether they conflict with your workspace configuration, toggle Block data generation on schema changes to the on position.

Determining ownership in the destination database

By default, the source database owner relationships, such as schema and table ownership, are preserved in the destination database. The Preserve source database owners in destination database toggle is in the on position.

To instead have the admin user for the destination database gain ownership of the schema and tables, toggle Preserve source database owners in destination database to the off position.

Limiting the included schemas

By default, the source database includes all of the schemas. To specify a list of specific schemas to either include or exclude:

1. Toggle Limit Schemas to the on position.

2. From the filter option dropdown list, select whether to include or exclude the listed schemas.

3. In the field, provide the list of schemas to either include or exclude. Use commas or semicolons to separate the schemas.

Do not exclude schemas that are referred to by included schemas, unless you create those schemas manually outside of Structural.

Connecting to the destination database

In the Destination Settings section, you specify the connection information for the destination database.

Copying the source connection details

To copy the connection details from the source database:

1. Click Copy Settings from Source.

2. In the Password field, provide the password.

3. To test the connection to the destination database, click Test Destination Connection.

Specifying the destination connection details

If you do not copy the source connection details, then to specify the connection information for the destination database:

1. In the Server field, provide the server where the database is located.

2. In the Database field, provide the name of the database.

3. In the Port field, provide the port to use to connect to the database.

4. In the Username field, provide the username for the account to use to connect to the database.

5. In the Password field, provide the password for the specified username.

6. To test the connection to the destination database, click Test Destination Connection.

Ensuring encryption of destination database authentication

The Enable SSL/TLS setting indicates whether to encrypt authentication data for the destination database.

By default, it is in the on position. We strongly recommend that you do not turn off this setting.

Table mode limitations

Amazon Redshift workspaces cannot use the following table modes:

• Scale

• Incremental

Generator limitations

Amazon Redshift workspaces cannot use the following generators:

• Algebraic

• Array JSON Mask

• Array Regex Mask

• Cross Table Sum

• Current Date

• Event Timestamps

• Geo

• Sequential Integer

No subsetting, but support for table filtering

Amazon Redshift workspaces do not support subsetting.

However, for tables that use the De-Identify table mode, you can provide a WHERE clause to filter the table. For details, go to Using table filtering for data warehouses and Spark-based data connectors.

No upsert

Amazon Redshift workspaces do not support upsert.

No output to container artifacts

For Amazon Redshift workspaces, you cannot write the destination data to container artifacts.

No output to an Ephemeral snapshot

For Amazon Redshift workspaces, you cannot write the destination data to an Ephemeral snapshot.

Tonic Structural allows you to set several Amazon Redshift-specific environment settings that make it easier to adapt our Amazon Redshift integration into your specific AWS environment. You configure these settings in the Structural worker container.

# No default value
# This setting is required to be set by user
# ARN of AWS Role to be assumed by Tonic's Lambda function
TONIC_LAMBDA_ROLE

# Default value of 30 secs
# Timeout of Lambda used to process data files
# Maximum allowed duration of Lambda function is 15 min
TONIC_LAMBDA_TIMEOUT

# Default value of 1024MB
# Memory limit of Lambda used to process data files
# Maximum allowed memory of Lambda function is 10240 MB
TONIC_LAMBDA_MEMORY_SIZE

# Default value of 30 secs
# Visibility of SQS which stores messages sent to Lambda
# Note that this value must be >= TONIC_LAMBDA_TIMEOUT
TONIC_LAMBDA_SQS_VISIBILITY_TIMEOUT

# No default value
# This setting is required to be set by user if using AWS KMS encryption
# AWS KMS Key ID for encrypting messages sent to Amazon SQS
TONIC_LAMBDA_KMS_MASTER_KEY

Creating the role

The AWS Lambda function that Tonic Structural sets up requires an AWS role. The name of this role is set by the following environment setting:

TONIC_LAMBDA_ROLE

The policy for this role should look like this:

{
	"Version": "2012-10-17",
	"Statement": [{
		"Sid": "VisualEditor0",
		"Effect": "Allow",
		"Action": [
			"s3:PutObject",
			"s3:GetObject",
			"s3:ListBucket",
			"sqs:ReceiveMessage",
			"sqs:GetQueueAttributes",
			"sqs:SendMessage",
			"sqs:DeleteMessage",
			"logs:CreateLogGroup",
			"logs:PutLogEvents"
			"logs:CreateLogStream",
		],
		"Resource": [
			"arn:aws:sqs:*:<aws account id>:tonic-*",
			"arn:aws:s3:::tonic-*",
			"arn:aws:logs:*:*:*"
		]
	}]
}

The above policy grants the Lambda function the required access to Amazon SQS, Amazon S3, and CloudWatch.

This policy assumes that the S3 buckets and Amazon SQS queues that are used begin with the tonic- prefix.

Enabling Lambda to assume the role

After you create the role, you must allow the Lambda service to assume the role.

For the role, the Trust relationships in the AWS IAM role should be configured to look like the following:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "Service": "lambda.amazonaws.com"
            },
            "Action": "sts:AssumeRole"
        }
    ]
}

The following high-level diagram describes how Tonic Structural orchestrates the processing and moving of data in Amazon Redshift.

This diagram is not the same as the Tonic architectural diagram.

Structural orchestrates the moving and transforming of data between Redshift databases. To do this, Structural uses the Amazon S3, Amazon SQS, and AWS Lambda services in AWS.

Structural manages the lifetimes of data and resources used in AWS. It only requires you to assign the necessary permissions to the IAM role that Structural uses.

At a high level, the process is:

1. Structural creates a Lambda function for your version of Structural. This step is performed once per version of Structural. The Lambda function is created when you run your first data generation job after you install or update Structural.

2. Structural creates an Amazon SQS queue and Amazon S3 event triggers. This is done once for each data generation job. The resource names are scoped to your specific generation job.

3. Structural copies the table data into Amazon S3 as CSV files. You specify the S3 bucket path in the Structural workspace configuration. Within the S3 bucket, the data files are copied into an input folder.

4. As files land in Amazon S3, Amazon S3 event notifications place messages in Amazon SQS. Messages in Amazon SQS trigger Lambda function invocations. By default, each file placed in Amazon S3 has a maximum file size of 50MB. Each Lambda invocation processes a single file. Lambda processes each file and then writes them back to Amazon S3 in an output folder in the S3 bucket.

5. After it processes all of the files for a table, Structural copies data back into Amazon Redshift, into the destination database.

6. After it processes all of the tables, Structural removes ephemeral AWS components such as Amazon SQS and Amazon S3 event notifications.

If you use AWS KMS for Amazon SQS encryption, make sure that you provided the correct key ID for the Tonic Structural environment setting TONIC_LAMBDA_KMS_MASTER_KEY. Also provide Amazon S3 access under your AWS KMS key policy:

{
    "Sid": "Allow access for Amazon S3 Event Notifications to Amazon SQS",
    "Effect": "Allow",
    "Principal": {
        "Service": "s3.amazonaws.com"
    },
    "Action": [
        "kms:Decrypt",
        "kms:GenerateDataKey"
    ],
    "Resource": "*"
}

Additional key permissions must be added to your Amazon EC2 and Lambda roles:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "kms:Decrypt",
                "kms:Encrypt",
                "kms:GenerateDataKey"
            ],
            "Resource": "<ARN to AWS KMS key>"
        }
    ]
}