Copy Table To External Schema

Copy Table To External

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.

Satisfied with the tool's configuration, we can click on OK to move to the second and final screen. In this screen, shown below, a summary of tests is given to ensure the configuration is valid. A brief summary of the output table's column data is also given.



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.

Table Input.

Rewrite External Table.

The properties of this Transformation job are shown below. Note that the properties of the components have been auto-filled 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.