Copy Table To External Schema
The Copy Table to External Schema tool is available through the Components menu in any Transformation job. This tool facilitates any table to be written as an external table and can be utilised simply by dragging the tool onto the job canvas.
In this article we describe the tool and detail a typical use of the tool to copy a non-external table to an external schema. In the first screen of the tool, there are 5 main options:
- Source Schema: The schema the source table belongs to.
- Source Table: The name of the source table (input). A list of available tables is generated depending on the chosen Source Schema.
- Target Schema: The schema the target table belongs to.
- Target Table: The name of the target table (output). A list of available tables is generated depending on the chosen Target Schema.
- S3 Location: The S3 Bucket path where the external table data is held.
Below we have chosen a non-external schema and table as our source with an external schema as our target. We have also moved all columns into the Select panel and opted (completely optionally) to partition by 'resolutiondate' by moving that property into the appropriate panel.
If any field is invalid, press Back to return to the previous screen and reassess the configuration. Otherwise, press OK to finish the configuration and run the tool, which will create a small chain of pre-configured components to accomplish the copy.
Transformation Job
The resulting transformation job is shown below. These components are generated automatically by the tool. Documentation links for the components are given below:
The properties of this Transformation job are shown below. Note that the properties of the components have been autofilled by the tool according to the settings we gave it.
Running this job will recreate the input table as an external table of the same name. Below we can confirm its success by attempting to load the new table from the specified external schema.
| Snowflake | Delta Lake on Databricks | Amazon Redshift | Google BigQuery | Azure Synapse Analytics |
|---|---|---|---|---|
| ✅ | ✅ | ✅ | ✅ | ✅ |