Data Transfer Between Databases

Overview

It may be useful to move tables from one database or cluster to another, a common issue when it comes to backing up work or sharing it with other users. AWS offers no direct way of accomplishing this but, fortunately, we can create some fairly simple jobs within Matillion ETL that will do the work.

The general strategy is to Unload table data from a Redshift cluster to a file on an S3 Bucket, then Load the data from that file back into a different table in another database. Thus, the data is copied from one database to another, even if the two databases exist on different clusters or regions.

Unloading data to an S3 Bucket.

Note: We must have an environment set up for Redshift connection that houses the table to be copied and this environment must be currently active/selected. In this example, we are moving a data from a Test database to a Live database, each with their own environment.

The first step is to create a file on an S3 Bucket from the table we want to copy. This can be accomplished by an S3 Unload Component linked to a Start component. The S3 Unload Component is set up to take an existing table from this Environment's default schema (although any schema can be used) and place it into the specific S3 Bucket. The name of the file inside the S3 Bucket is given by the 'S3 Object Prefix' property.

Uploading to a different Redshift database.

Now switch environments so the active/selected environment is the one we wish to transfer the data to. This can be done by right-clicking the other environment and clicking 'Select Environment'.

Another Orchestration job is created and filled with three components: Start, Create/Replace Table and S3 Load.

Create/Replace table is simply used to create a blank table in the Redshift database with a name that must then also be used by S3 Load to load data into. In this case, we make another table called example_table so that our new table will, by the end of the process, be exactly identical to the original. S3 Load is set up to load all columns of data from the file we wrote to the S3 Bucket. The bucket is linked to in the 'S3 URL Location' property and the file itself in the 'S3 Object Prefix' property.

Note that the file must be loaded in the same way it was unloaded in regards to being delimited. If S3 Unload in the previous job was told to delimit using tabs, S3 Load must be given the same properties.

Using a Manifest file

Job synchronization using SQS

• The "source" instance sends an SQS message when the unload has successfully finished
• The "target" instance is listening to the same queue, and launches the COPY command when it receives the SQS message.

Example

• CX Data Export - the export job, which runs on the source cluster.
• CX Prep data for export - Called by CX Data Export to prepare sample data for export.
• CX Data Import - the import job, which runs on the target cluster.

CX Data Export

The components look like this:



First to execute is a call to "CX Prep data for export", which creates a new Redshift table named tmp_10240000, containing sample data to demonstrate the export.

Next, an S3 Unload component creates files in a nominated S3 staging bucket (named via an Environment Variable). Parallel is set to Yes so multiple files will be created with a common prefix.

After the unload has finished, an S3 Manifest Builder component generates a named Manifest file in the same location. This will be used by the importer to verify that all the files have been included.

In order to notify the target cluster that new data is available, an SQS Message component is used to send a JSON message onto a named queue. This will be picked up by a Matillion instance running on the target cluster.

Finally, the Delete Tables component removes the tmp_10240000 table from the source cluster.

CX Data Import

The components look like this:



First of all, the Python script just does some basic logging of the parameters which are going to be used.

Then, a Create/Replace Table component creates a table named replicated_table, ready to receive a copy of the source data.

A parameterised S3 Load then copies the source data from S3 into the target Redshift cluster using the named Manifest file. This component will fail if not all the files are present.

Finally the tidy up is performed by a Bash script, which deletes all the data files including the manifest itself