AWS Schema Conversion Tool

1 AWS Schema Conversion ToolUser GuideVersion Schema Conversion tool User GuideAWS Schema Conversion tool : User GuideCopyright 2018 Amazon Web Services, Inc. and/or its affiliates. All rights 's trademarks and trade dress may not be used in connection with any product or service that is not Amazon's, in any mannerthat is likely to cause confusion among customers, or in any manner that disparages or discredits Amazon. All other trademarks notowned by Amazon are the property of their respective owners, who may or may not be affiliated with, connected to, or sponsored Schema Conversion tool User GuideTable of ContentsWhat Is the AWS Schema Conversion tool ? .. 1 Schema Conversion Overview.. 2 Feedback .. 2 Installing, Verifying, and Updating .. 4 Installing the AWS SCT .. 4 Installing Previous Versions .. 5 Verifying the AWS SCT File Download .. 5 Verifying the Checksum of the AWS SCT File.

2 6 Verifying the AWS SCT RPM Files on Fedora .. 6 Verifying the AWS SCT DEB Files on Ubuntu .. 7 Verifying the AWS SCT MSI File on Microsoft Windows .. 7 Verifying the AWS SCT Application on Mac OS .. 7 Installing the Required Database Drivers .. 8 Installing JDBC Drivers on Linux .. 9 Storing Driver Paths in the Global Settings .. 10 Updating the AWS SCT .. 11 Using the AWS SCT User Interface .. 12 The AWS SCT Project Window.. 12 Storing AWS Profiles .. 13 Storing AWS Credentials .. 14 Setting the Default Profile for a Project .. 16 Storing Database Passwords .. 16 Using the Union All View for Projects with Partitioned Tables .. 16 AWS SCT Tree Filters .. 16.. 17 Importing a File List for the Tree Filter .. 18 Hiding Schemas.. 19 Keyboard Shortcuts .. 19 The Database Migration Assessment Report .. 20 Starting SCT.

3 24 Creating a Project .. 25 Converting Your Schema .. 26 Applying the Converted Schema to Your Target DB Instance .. 27 Getting Started .. 29 Source Databases for AWS SCT .. 30 Using Oracle as a Source for AWS Schema Conversion tool .. 30 Permissions Needed for Oracle as a Source .. 31 Connecting to Oracle as a Source .. 31 Oracle to PostgreSQL .. 33 Oracle to MySQL .. 36 Oracle to Amazon RDS Oracle .. 40 Using Microsoft SQL Server as a Source for AWS Schema Conversion tool .. 43 Source: Microsoft SQL Server .. 43 Source: Windows Authentication with Microsoft SQL Server .. 43 Connecting to SQL Server as a Source .. 45 SQL Server to MySQL .. 46 SQL Server to PostgreSQL .. 48 SQL Server to Amazon RDS SQL Server .. 51 Using MySQL as a Source for AWS Schema Conversion tool .. 52 Source: MySQL .. 52 Connecting to MySQL as a Source.

4 52 Using PostgreSQL as a Source for AWS Schema Conversion tool .. 54 Source: PostgreSQL .. 54 Connecting to PostgreSQL as a Source .. 55 Version Schema Conversion tool User GuideUsing Db2 LUW as a Source for AWS Schema Conversion tool .. 56 Source: Db2 LUW.. 56 Connecting to Db2 LUW as a Source .. 58DB2 LUW to PostgreSQL .. 60 Using Amazon Redshift as a Source for AWS Schema Conversion tool .. 61 Source: Amazon Redshift .. 61 Connecting to Redshift as a Source .. 61 Using Oracle DW as a Source for AWS Schema Conversion tool .. 63 Source: Oracle Data Warehouse .. 63 Connecting to OracleDW as a Source .. 63 Using Teradata as a Source for AWS Schema Conversion tool .. 66 Source: Teradata .. 66 Connecting to Teradata as a Source .. 66 Using Netezza as a Source for AWS Schema Conversion tool .. 69 Source: Netezza .. 69 Connecting to Netezza as a Source.

5 69 Using Greenplum as a Source for AWS Schema Conversion tool .. 71 Source: Greenplum.. 71 Connecting to Greenplum as a Source .. 71 Using Vertica as a Source for AWS Schema Conversion tool .. 72 Source: Vertica .. 73 Connecting to Vertica as a Source .. 73 Using Microsoft SQL Server DW as a Source for AWS Schema Conversion tool .. 74 Source: Microsoft SQL Server Data Warehouse .. 75 Connecting to SQLS erverDW as a Source .. 75 AWS SCT Assessment Report .. 77 Creating a Database Migration Assessment Report .. 77 Summary .. 78 Action Items .. 79 Saving the Assessment Report .. 80 Converting Database Schemas .. 82 Creating Mapping Rules .. 83 Creating Mapping Rules .. 84 Creating Mapping Rules for Objects .. 85 Exporting Mapping Rules .. 85 Converting Your Schema .. 85 Converting Schema .. 85 Editing Converted Schema .

6 87 Clearing a Converted Schema .. 88 Handling Manual Conversions .. 89 Modifying Your Source Schema .. 89 Modifying Your Target Schema .. 90 Updating and Refreshing Your Converted Schema .. 90 Saving and Applying Your Schema .. 91 Saving Your Converted Schema to a File .. 91 Applying Your Converted Schema .. 92 The Extension Pack Schema .. 93 Comparing Schemas.. 94 Related Transformed Objects .. 95 Converting Data Warehouse Schemas to Amazon Redshift .. 96 Choosing Optimization Strategies and Rules.. 97 Collecting or Uploading Statistics .. 98 Creating Mapping Rules .. 99 Creating Mapping Rules .. 100 Viewing Mapping Rules for Objects .. 101 Exporting Mapping Rules .. 101 Converting Your Schema .. 101 Version Schema Conversion tool User GuideConverting Schema .. 102 Editing Converted Schema .. 103 Clearing a Converted Schema .

7 104 Managing and Customizing Keys .. 105 Handling Manual Conversions .. 106 Modifying Your Source Schema .. 106 Modifying Your Target Schema .. 107 Updating and Refreshing Your Converted Schema .. 107 Saving and Applying Your Schema .. 108 Saving Your Converted Schema to a File .. 108 Applying Your Converted Schema .. 109 The Extension Pack Schema .. 110 Python Libraries .. 111 Optimizing Amazon Redshift .. 111 Optimizing Your Amazon Redshift Database .. 111 Using the AWS Schema Conversion tool with the AWS Database Migration Service .. 113 Using an AWS SCT Replication Agent with AWS DMS .. 113 Using an AWS SCT Data Extraction Agent with AWS DMS .. 113 Using Data Extraction Agents .. 114 Migrating Data From an On-Premises Data Warehouse to Amazon Redshift .. 114 Prerequisite Settings .. 116 Installing Agents .. 117 Registering Agents.

8 120 Hiding and Recovering Information for an AWS SCT Agent .. 121 Creating Data Extraction Filters .. 122 Sorting Data .. 123 Creating, Running, and Monitoring an AWS SCT Task .. 124 Data Extraction Using a AWS Snowball Device .. 126 Data Extraction Task Output .. 132 Using Virtual Partitioning .. 133 Working with LOBs .. 136 Best Practices and Troubleshooting .. 137 Migrating Data From Apache Cassandra to Amazon DynamoDB .. 138 Prerequisites for Migrating From Cassandra to DynamoDB .. 140 Create a New AWS SCT Project .. 141 Create a Clone Datacenter .. 144 Install, Configure and Run the Data Extraction Agent .. 151 Migrate Data From the Clone Datacenter to Amazon DynamoDB .. 154 Post-Migration Activities .. 158 Converting Application SQL .. 159 Overview of Converting Application SQL .. 159 Creating Application Conversion Projects .. 159 Analyzing and Converting Your SQL Code.

9 162 Creating and Using the Assessment Report .. 163 Creating an Application Assessment Report .. 163 Editing and Saving Your Converted SQL Code .. 164 Using the Extension Pack .. 165 Using the Extension Pack DB Schema .. 165 The Custom Python Library for the Extension Pack .. 166 Using AWS Services to Upload Custom Python Libraries .. 166 Applying the Extension Pack .. 166 The Lambda Functions from the Extension Pack .. 167 Using AWS Services to Emulate Database Functionality .. 167 Applying the Extension Pack .. 168 Best Practices .. 170 General Memory Management and Performance Options .. 170 Configuring Additional Memory .. 170 Version Schema Conversion tool User GuideTroubleshooting .. 171 Cannot load objects from an Oracle source database .. 171 Release Notes .. 172 Release Notes .. 172 Release Notes .. 173 Release Notes.

10 175 Release Notes .. 177 Release Notes .. 179 Release Notes .. 181 Release Notes .. 183 Release Notes .. 185 Release Notes .. 187 Document History .. 189 Earlier Updates.. 190 Version Schema Conversion tool User GuideWhat Is the AWS Schema ConversionTool?You can use the AWS Schema Conversion tool (AWS SCT) to convert your existing database Schema fromone database engine to another. You can convert relational OLTP Schema , or data warehouse converted Schema is suitable for an Amazon Relational Database Service (Amazon RDS) MySQL DBinstance, an Amazon Aurora DB cluster, an Amazon RDS PostgreSQL DB instance, or an Amazon Redshiftcluster. The converted Schema can also be used with a database on an Amazon EC2 instance or stored asdata on an Amazon S3 SCT supports several industry standards, including Federal Information Processing Standards (FIPS)when connecting to an Amazon S3 bucket or another AWS resource.