Schema Copy

Schema Copy Component

Copy data from one or more tables from one schema to another. The source schema defaults to the one defined in the currently selected environment. Target tables can be new or existing, and can be appended to or replaced.

This component is potentially destructive. It may drop and recreate a target table marked as 'replace' without warning.

You should consider running schema copy inside a transaction to ensure all changes are visible in the target schema, or none of them are. Use the Begin/Commit/Rollback components to do this.

Note:External schemas cannot be copied through this component.

Properties

Property Setting Description
Name Text The descriptive name for the component.
Source Schema Select Select the table schema. The special value, [Environment Default] will use the schema defined in the environment. For more information on using multiple schemas, see this article.
Target Schema Select A list of the available schemas on your Redshift instance. (Must be different from the source schema)
Note: Since you may run the same job against multiple environments, it may be a good idea to define a variable for the target schema so the value can be changed dynamically depending upon the environment the job is run in.
Tables to copy Table Name Select an existing table from the environments current schema.
Copy Method Replace: Replace any like-named table in the target schema.
Append: Appends records to the target table. If the target table does not exist, it is created. If the target table does exists, its structure must be identical to the source table.

Example

In this example we have a selection of tables relating to flights data in our test schema (default) and we'd like to move them to a sales schema to be transformed along with sales data. Using the Schema Copy component, multiple tables can be copied in a single transaction.

In the Schema Copy component the properties, the source and target schemas are set from dropdown menus that will automatically present available schemas.

We can select multiple tables from the source schema using the 'Tables to copy' property as shown below. The tables are set to 'Replace' tables of the same name if they already exist in the target schema.

Running this job will complete the copy. If we move to a Transformation job, we can use a Table Input component (set to the sales schema) to find our copied tables and confirm the job's success.