Migrating data from on-premises data warehouse to Amazon Redshift with AWS Schema Conversion Tool

To configure IAM roles to access your Amazon S3 bucket

1. Create a new IAM user. For user credentials, choose Programmatic access type.

2. Configure the host environment so that your data extraction agent can assume the role that AWS SCT provides. Make sure that the user that you configured in the previous step enables data extraction agents to use the credential provider chain. For more information, see Using credentials in the AWS SDK for Java Developer Guide.

3. Create a new IAM role that has access to your Amazon S3 bucket.

4. Modify the trust section of this role to trust the user that you created before to assume the role. In the following example, replace 111122223333:user/DataExtractionAgentName with the name of your user.

   {
       "Effect": "Allow",
       "Principal": {
           "AWS": "arn:aws:iam::111122223333:user/DataExtractionAgentName"
       },
       "Action": "sts:AssumeRole"
   }

5. Modify the trust section of this role to trust redshift.amazonaws.com to assume the role.

   {
       "Effect": "Allow",
       "Principal": {
           "Service": [
               "redshift.amazonaws.com"
           ]
       },
       "Action": "sts:AssumeRole"
   }

6. Attach this role to your Amazon Redshift cluster.

To set up secure communication with your extraction agent

1. Start the AWS Schema Conversion Tool.

2. Open the Settings menu, and then choose Global settings. The Global settings dialog box appears.

3. Choose Security.

4. Choose Generate trust and key store, or choose Select existing trust store.

   If you choose Generate trust and key store, you then specify the name and password for the trust and key stores, and the path to the location for the generated files. You use these files in later steps.

   If you choose Select existing trust store, you then specify the password and file name for the trust and key stores. You use these files in later steps.

5. After you have specified the trust store and key store, choose OK to close the Global settings dialog box.

To install an extraction agent

1. If you have not already downloaded the AWS SCT installer file, follow the instructions at Installing and Configuring AWS Schema Conversion Tool to download it. The .zip file that contains the AWS SCT installer file also contains the extraction agent installer file.

2. Download and install the latest version of Amazon Corretto 11. For more information, see Downloads for Amazon Corretto 11 in the Amazon Corretto 11 User Guide.

3. Locate the installer file for your extraction agent in a subfolder named agents. For each computer operating system, the correct file to install the extraction agent is shown following.

   Operating system	File name
   Microsoft Windows	aws-schema-conversion-tool-extractor-2.0.1.build-number.msi
   RHEL	aws-schema-conversion-tool-extractor-2.0.1.build-number.x86_64.rpm
   Ubuntu Linux	aws-schema-conversion-tool-extractor-2.0.1.build-number.deb

4. Install the extraction agent on a separate computer by copying the installer file to the new computer.

5. Run the installer file. Use the instructions for your operating system, shown following.

   Operating system	Installation instructions
   Microsoft Windows	Double-click the file to run the installer.
   RHEL	Run the following commands in the folder that you downloaded or moved the file to. sudo rpm -ivh aws-schema-conversion-tool-extractor-2.0.1.build-number.x86_64.rpm sudo ./sct-extractor-setup.sh --config
   Ubuntu Linux	Run the following commands in the folder that you downloaded or moved the file to. sudo dpkg -i aws-schema-conversion-tool-extractor-2.0.1.build-number.deb sudo ./sct-extractor-setup.sh --config

6. Choose Next, accept the license agreement, and choose Next.

7. Enter the path to install the AWS SCT data extraction agent, and choose Next.

8. Choose Install to install your data extraction agent.

   AWS SCT installs your data extraction agent. To complete the installation, configure your data extraction agent. AWS SCT automatically launches the configuration setup program. For more information, see Configuring extraction agents.

9. Choose Finish to close the installation wizard after you configure your data extraction agent.

To configure your extraction agent

1. Launch the configuration setup program:

   • In Windows, AWS SCT launches the configuration setup program automatically during the installation of a data extraction agent.

     As needed, you can launch the setup program manually. To do so, run the ConfigAgent.bat file in Windows. You can find this file in the folder where you installed the agent.

   • In RHEL and Ubuntu, run the sct-extractor-setup.sh file from the location where you installed the agent.

   The setup program prompts you for information. For each prompt, a default value appears.

2. Accept the default value at each prompt, or enter a new value.

   Specify the following information:

   • For Listening port, enter the port number the agent listens on.

   • For Add a source vendor, enter yes, and then enter your source data warehouse platform.

   • For JDBC driver, enter the location where you installed the JDBC drivers.

   • For Working folder, enter the path where the AWS SCT data extraction agent will store the extracted data. The working folder can be on a different computer from the agent, and a single working folder can be shared by multiple agents on different computers.

   • For Enable SSL communication, enter yes.

   • For Key store, enter the location of the key store file.

   • For Key store password, enter the password for the key store.

   • For Enable client SSL authentication, enter yes.

   • For Trust store, enter the location of the trust store file.

   • For Trust store password, enter the password for the trust store.

To install and configure extraction agents and a dedicated copying agent

1. Make sure that the working directory of all extracting agents uses the same folder on shared storage.

2. Install extractor agents by following the steps in Installing extraction agents.

3. Configure extraction agents by following the steps in Configuring extraction agents, but specify only the source JDBC driver.

4. Configure a dedicated copying agent by following the steps in Configuring extraction agents, but specify only an Amazon Redshift JDBC driver.

To start your extraction agent

• On the computer that has the extraction agent installed, run the command listed following for your operating system.

  Operating system	Start command
  Microsoft Windows	Double-click the StartAgent.bat batch file.
  RHEL	Run the following command in the path to the folder that you installed the agent: sudo initctl start sct-extractor
  Ubuntu Linux	Run the following command in the path to the folder that you installed the agent. Use the command appropriate for your version of Ubuntu. Ubuntu 14.04: sudo initctl start sct-extractor Ubuntu 15.04 and higher: sudo systemctl start sct-extractor

To register an extraction agent

1. Start the AWS Schema Conversion Tool, and open a project.

2. Open the View menu, and then choose Data Migration view (other). The Agents tab appears. If you have previously registered agents, AWS SCT displays them in a grid at the top of the tab.

3. Choose Register.

   After you register an agent with an AWS SCT project, you can't register the same agent with a different project. If you're no longer using an agent in an AWS SCT project, you can unregister it. You can then register it with a different project.

4. Choose Redshift data agent, and then choose OK.

5. Enter your information on the Connection tab of the dialog box:

   1. For Description, enter a description of the agent.

   2. For Host Name, enter the host name or IP address of the computer of the agent.

   3. For Port, enter the port number that the agent is listening on.

   4. Choose Register to register the agent with your AWS SCT project.

6. Repeat the previous steps to register multiple agents with your AWS SCT project.

To reconfigure the agent and restore passwords for accessing SSL certificates

1. Install a new AWS SCT agent and run configuration.

2. Change the agent.name property in the instance.properties file to the name of the agent the storage was created for, to have the new agent work with existing agent storage.

   The instance.properties file is stored in the agent's private folder, which is named using the following convention: {output.folder}\dmt\{hostName}_{portNumber}\.

3. Change the name of {output.folder} to that of the previous agent's output folder.

   At this point, AWS SCT is still trying to access the old extractor at the old host and port. As a result, the inaccessible extractor gets the status FAILED. You can then change the host and port.

4. Modify the host, port, or both for the old agent by using the Modify command to redirect the request flow to the new agent.

To create data migration rules

1. Open the View menu, and then choose Data Migration view (other).

2. Choose Data migration rules, and then choose Add new rule.

3. Configure your data migration rule:

   1. For Name, enter a name for your data migration rule.

   2. For Where schema name is like, enter a filter to apply to schemas. In this filter, a WHERE clause is evaluated by using a LIKE clause. To choose one schema, enter an exact schema name. To choose multiple schemas, use the “%” character as a wildcard to match any number of characters in the schema name.

   3. For table name like, enter a filter to apply to tables. In this filter, a WHERE clause is evaluated by using a LIKE clause. To choose one table, enter an exact name. To choose multiple tables, use the “%” character as a wildcard to match any number of characters in the table name.

   4. For Where clause, enter a WHERE clause to filter data.

4. After you have configured your filter, choose Save to save your filter, or Cancel to cancel your changes.

5. After you are done adding, editing, and deleting filters, choose Save all to save all your changes.

To sort data before migrating using AWS SCT

1. Open an AWS SCT project.

2. Open the context (right-click) menu for the object, and then choose Create Local task.

3. Choose the Advanced tab, and for Sorting strategy, choose an option:

   • Never use sorting – The extraction agent doesn't use the DENSE_RANK analytic function and restarts from the beginning if a failure occurs.

   • Use sorting if possible – The extraction agent uses DENSE_RANK if the table has a primary key or a unique constraint.

   • Use sorting after first fail (recommended) – The extraction agent first tries to get the data without using DENSE_RANK. If the first attempt fails, the extraction agent rebuilds the query using DENSE_RANK and preserves its location in case of failure.

   

4. Set additional parameters as described following, and then choose Create to create your data extraction task.

To assign tasks to agents and migrate data

1. In the AWS Schema Conversion Tool, after you have converted your schema, choose one or more tables from the left panel of your project.

   You can choose all tables, but we recommend against that for performance reasons. We recommend that you create multiple tasks for multiple tables based on the size of the tables in your data warehouse.

2. Open the context (right-click) menu for each table, and then choose Create task. The Create Local task dialog box opens.

3. For Task name, enter a name for the task.

4. For Migration mode, choose one of the following:

   • Extract only – Extract your data, and save the data to your local working folders.

   • Extract and upload – Extract your data, and upload your data to Amazon S3.

   • Extract, upload and copy – Extract your data, upload your data to Amazon S3, and copy it into your Amazon Redshift data warehouse.

5. For Encryption type, choose one of the following:

   • NONE – Turn off data encryption for the entire data migration process.

   • CSE_SK – Use client-side encryption with a symmetric key to migrate data. AWS SCT automatically generates encryption keys and transmits them to data extraction agents using Secure Sockets Layer (SSL). AWS SCT doesn't encrypt large objects (LOBs) during data migration.

6. Choose Extract LOBs to extract large objects. If you don't need to extract large objects, you can clear the check box. Doing this reduces the amount of data that you extract.

7. To see detailed information about a task, choose Enable task logging. You can use the task log to debug problems.

   If you enable task logging, choose the level of detail that you want to see. The levels are the following, with each level including all messages from the previous level:

   • ERROR – The smallest amount of detail.

   • WARNING

   • INFO

   • DEBUG

   • TRACE – The largest amount of detail.

8. To export data from BigQuery, AWS SCT uses the Google Cloud Storage bucket folder. In this folder, data extraction agents store your source data.

   To enter the path to your Google Cloud Storage bucket folder, choose Advanced. For Google CS bucket folder, enter the bucket name and the folder name.

9. To assume a role for your data extraction agent user, choose Amazon S3 settings. For IAM role, enter the name of the role to use. For Region, choose the AWS Region for this role.

10. Choose Test task to verify that you can connect to your working folder, Amazon S3 bucket, and Amazon Redshift data warehouse. The verification depends on the migration mode you chose.

11. Choose Create to create the task.

12. Repeat the previous steps to create tasks for all the data that you want to migrate.

To run and monitor tasks

1. For View, choose Data Migration view. The Agents tab appears.

2. Choose the Tasks tab. Your tasks appear in the grid at the top as shown following. You can see the status of a task in the top grid, and the status of its subtasks in the bottom grid.

   

3. Choose a task in the top grid and expand it. Depending on the migration mode you chose, you see the task divided into Extract, Upload, and Copy.

4. Choose Start for a task to start that task. You can monitor the status of your tasks while they work. The subtasks run in parallel. The extract, upload, and copy also run in parallel.

5. If you enabled logging when you set up the task, you can view the log:

   1. Choose Download log. A message appears with the name of the folder that contains the log file. Dismiss the message.

   2. A link appears in the Task details tab. Choose the link to open the folder that contains the log file.

To export and import a data extraction task

1. For View, choose Data Migration view. The Agents tab appears.

2. Choose the Tasks tab. Your tasks are listed in the grid that appears.

3. Choose the three vertically aligned dots (ellipsis icon) located at the lower right corner under the list of tasks.

4. Choose Export task from the pop-up menu.

5. Choose the folder where you want AWS SCT to place the task export .xml file.

   AWS SCT creates the task export file with a file name format of TASK-DESCRIPTION_TASK-ID.xml.

6. Choose the three vertically aligned dots (ellipsis icon) at lower right under the list of tasks.

7. Choose Import task from the pop-up menu.

   You can import an extraction task to a project connected to the source database, and the project has at least one active registered extraction agent.

8. Select the .xml file for the extraction task you exported.

   AWS SCT gets the extraction task's parameters from the file, creates the task, and adds the task to the extracting agents.

9. Repeat these steps to export and import additional data extraction tasks.

To create a new project in AWS SCT

1. Start the AWS Schema Conversion Tool. On the File menu, choose New project. The New project dialog box appears.

2. Enter a name for your project, which is stored locally on your computer.

3. Enter the location for your local project file.

4. Choose OK to create your AWS SCT project.

5. Choose Add source to add a new source database to your AWS SCT project.

6. Choose Add target to add a new target platform in your AWS SCT project.

7. Choose the source database schema in the left panel.

8. In the right panel, specify the target database platform for the selected source schema.

9. Choose Create mapping. This button becomes active after you choose the source database schema and the target database platform.

To import your AWS Snowball job

1. Open the Settings menu, and then choose Global settings. The Global settings dialog box appears.

2. Choose AWS service profiles, and then choose Import job.

3. Choose your AWS Snowball job.

4. Enter your AWS Snowball IP. For more information, see Changing Your IP Address in the AWS Snowball User Guide.

5. Enter your AWS Snowball Port. For more information, see Ports Required to Use AWS Services on an AWS Snowball Edge Device in the AWS Snowball Edge Developer Guide.

6. Enter your AWS Snowball access key and AWS Snowball secret key. For more information, see Authorization and Access Control in AWS Snowball in the AWS Snowball User Guide.

7. Choose Apply, and then choose OK.

To register a data extraction agent

1. On the View menu, choose Data migration view (other), and then choose Register.

2. For Description, enter a name for your data extraction agent.

3. For Host name, enter the IP address of the computer where you run your data extraction agent.

4. For Port, enter the listening port that you configured.

5. Choose Register.

To create the migration task

1. On the View menu, and then choose Data migration view (other).

2. In the left panel that displays the schema from your source database, choose a schema object to migrate. Open the context (right-click) menu for the object, and then choose Create local task.

3. For Task name, enter a descriptive name for your data migration task.

4. For Migration mode, choose Extract, upload, and copy.

5. Choose Amazon S3 settings.

6. Select Use Snowball.

7. Enter folders and subfolders in your Amazon S3 bucket where the data extraction agent can store data.

8. Choose Create to create the task.

To create a RANGE virtual partition

1. Open AWS SCT.

2. Choose Data Migration view (other) mode.

3. Choose the table where you want to set up virtual partitioning. Open the context (right-click) menu for the table, and choose Add virtual partitioning.

4. In the Add virtual partitioning dialog box, enter the information as follows.

   Option	Action
   Partition type	Choose RANGE. The dialog box UI changes depending on the type you choose.
   Column name	Choose the column that you want to partition.
   Column type	Choose the data type for the values in the column.
   Values	Add new values by typing each value in the New Value box, then choosing the plus sign to add the value.
   Load from file	(Optional) Enter the name of a .csv file that contains the partition values.

5. Choose OK.

To create a LIST virtual partition

1. Open AWS SCT.

2. Choose Data Migration view (other) mode.

3. Choose the table where you want to set up virtual partitioning. Open the context (right-click) menu for the table, and choose Add virtual partitioning.

4. In the Add virtual partitioning dialog box, enter the information as follows.

   Option	Action
   Partition type	Choose LIST. The dialog box UI changes depending on the type you choose.
   Column name	Choose the column that you want to partition.
   New value	Type a value here to add it to the set of partitioning values.
   Include other values	Choose this option to create a default partition where all values that don’t meet the partitioning criteria are stored.
   Load from file	(Optional) Enter the name of a .csv file that contains the partition values.

5. Choose OK.

To create a DATE AUTO SPLIT virtual partition

1. Open AWS SCT.

2. Choose Data Migration view (other) mode.

3. Choose the table where you want to set up virtual partitioning. Open the context (right-click) menu for the table, and choose Add virtual partitioning.

4. In the Add virtual partitioning dialog box, enter information as follows.

   Option	Action
   Partition type	Choose DATE AUTO SPLIT. The dialog box UI changes depending on the type you choose.
   Column name	Choose the column that you want to partition.
   Start date	Type a start date.
   End date	Type an end date.
   Interval	Enter the interval unit, and choose the value for that unit.

5. Choose OK.

To use native partitioning

1. Open AWS SCT, and choose New project for File. The New project dialog box appears.

2. Create a new project, add your source and target servers, and create mapping rules. For more information, see Starting and managing Projects in AWS SCT.

3. Choose View, and then choose Main view.

4. For Project settings, choose the Data migration tab. Choose Use automatic partitioning. For Greenplum and Netezza source databases, enter the minimal size of supported tables in megabytes (for example, 100). AWS SCT automatically creates separate migration subtasks for each native partition that isn't empty. For Oracle to Amazon Redshift migrations, AWS SCT creates subtasks for all partitioned tables.

5. In the left panel that displays the schema from your source database, choose a schema. Open the context (right-click) menu for the object, and chose Collect statistics. For data migration from Oracle to Amazon Redshift, you can skip this step.

6. Choose all tables to migrate.

7. Register the required number of agents. For more information, see Registering extraction agents with the AWS Schema Conversion Tool.

8. Create a data extraction task for the selected tables. For more information, see Creating, running, and monitoring an AWS SCT data extraction task.

   Check if large tables are split into subtasks, and that each subtask matches the dataset that presents a part of the table located on one slice in your source data warehouse.

9. Start and monitor the migration process until AWS SCT data extraction agents complete the migration of data from your source tables.

To migrate LOBs to Amazon Redshift

1. Open an AWS SCT project.

2. Connect to the source and target databases. Refresh metadata from the target database, and make sure that the converted tables exist there.

3. For Actions, choose Create local task.

4. For Migration mode, choose one of the following:

   • Extract and upload to extract your data, and upload your data to Amazon S3.

   • Extract, upload and copy to extract your data, upload your data to Amazon S3, and copy it into your Amazon Redshift data warehouse.

5. Choose Amazon S3 settings.

6. For Amazon S3 bucket LOBs folder, enter the name of the folder in an Amazon S3 bucket where you want the LOBs stored.

   If you use AWS service profile, this field is optional. AWS SCT can use the default settings from your profile. To use another Amazon S3 bucket, enter the path here.

7. Turn on the Use proxy option to use a proxy server to upload data to Amazon S3. Then choose the data transfer protocol, enter the host name, port, user name, and password.

8. For Endpoint type, choose FIPS to use the Federal Information Processing Standard (FIPS) endpoint. Choose VPCE to use the virtual private cloud (VPC) endpoint. Then for VPC endpoint, enter the Domain Name System (DNS) of your VPC endpoint.

9. Turn on the Keep files on Amazon S3 after copying to Amazon Redshift option to keep extracted files on Amazon S3 after copying these files to Amazon Redshift.

10. Choose Create to create the task.