- Hello, everyone. My name is Bhanu Pittampally.
I'm a Redshift specialist solution architect at AWS.
Today I'm gonna talk about and demonstrate
data warehouse migration to Amazon Redshift.
Specifically, how to use AWS Schema Conversion Tool
to migrate a data warehouse to Amazon Redshift.
The solution architecture for the demo is shown here.
On the left-hand side, we have on-premise data warehouse,
along with some ETL scripts
download in the proprietary language.
On the right-hand side
we have Amazon Redshift cloud data warehouse.
We're going to use AWS SCT to convert the SQL core objects
and database objects.
We're going to use AWS SCT data extraction agent
to migrate data to Redshift server,
and finally, we're going to use AWS SCT ETL converter
to migrate BTEQ script to RSQL script.
So let's start the demo now.
Can see, the SCT tool is already open on my machine,
which is a Windows system.
I downloaded, installed, and configured the SCT tool
as per the instructions found on the product page.
It's very easy, you can do it yourself.
The first thing I'm gonna do in this tool
is click on "File,"
and click on "New project."
And I wanna call my project as
teradata_migration.
As you can see, we have the main view,
where we have sources and targets,
which are empty.
Then I'm gonna add a source, which is Teradata.
I have this information prepopulated,
based on my configuration that I have done,
so now I'm gonna simply enter my password,
and name my connection.
It's onprem_teradata_edw.
I'm gonna click on "Store password," and connect.
I'm gonna accept the risk and continue
of not encrypting the data,
since this is a demo.
Give it some time here,
while it connects to Teradata machine.
I have successful connection to the source.
Now let's add target.
Target is going to be Amazon Redshift.
Click on "Next," again, I got this information prepopulated,
I'm just gonna enter my password,
and call my connection, give it a name,
cloud_redshift_edw.
Store password, I'm gonna uncheck Glue here,
'cause we're not using that for this demo purpose,
so click on "Connect."
And now I have my cloud_redshift_edw
populated on the target side.
Next thing we want to do is create the mapping.
Mapping establishes the path for the objects
to flow from source to target.
So I'm gonna go ahead,
click on the schema that I want to use for this demo,
I want to use the td_migration_demo
to demonstrate the conversion process,
so I'm gonna select on that.
And create mapping, I click on "Create mapping."
Once I do that,
the mapping source to target path is established.
Then I'm gonna go back to main view.
Once I do that, you can see
the information is again populated.
I'm gonna uncheck all schemas,
since we're gonna use only one schema.
As you can see, we have 10 tables,
four views, and two macros.
Go ahead and expand that,
we have some fact tables, dimension tables,
we have some views, and then two macros.
So what we're gonna do next
is we're gonna select the schema,
and click on "Converge schema."
Click "Yes," it's warning if objects already exist,
it's gonna replace, which is fine, click on "Continue."
Then now it's working on the conversion process.
So, conversion process is where SCT is going to convert
or rewrite your tables, or views, or macros,
to a format compatible with Amazon Redshift.
It is also going to give us an assessment report
where you can download and review that
to understand how the conversion process went through.
To access the assessment report,
you can go to "Main view,"
click on "Assessment report view."
And here you can see, we worked on one schema,
10 tables, and four views,
or four indexes.
And when it comes to database code objects,
we worked on four views and two macros.
The light green is for the objects
which are automatically converted,
the darker green for the objects with simple actions,
the light blue for the objects
with medium complexity actions,
and the darker blue with the objects with complex actions.
So today we have 10% of the tables
with the medium complexity actions,
and the rest went through without any issues.
Let's review what those are,
and then we will convert these objects to Redshift side.
We can see the issues that have been encountered
during the conversion process.
I'll go ahead, click on the first issue here.
It's about loa_duration column.
So once I click on that,
we can see that it's coming from the loa_durations table.
It's about the interval data type.
Our SCT is clever enough
to convert that into character varying (indistinct),
since Redshift doesn't have it yet.
Another issue that is reported here is
about the view top_five_months,
it's about the qualifier rank,
and we can see that Redshift is able to mimic the logic
with a subquery,
and the filtering that is happening with the qualifier rank
less than or equal to five
is now being handled by the (indistinct),
and the view works the same way as the Teradata side,
and you get the consistent results.
And we can also see that SCT is able to convert
some of the macros to store procedures,
and you can witness that here.
It is also able to apply the case-specific
or case not specific logic in the where filter.
So here in the where clause,
you can see that the string_par is complete to ci column
with the not casespecific.
On the Redshift side, the same thing is happening.
Collate function is used
where we're applying case_insensitive type,
so both the columns are matched irrespective of the case.
So there are some ways where AWS SCT really helps you
automate your conversion process
(indistinct) interventions for you,
and makes your migration experience very easy.
The next step for us is to apply these converted objects
to Redshift side.
On my target side, on the right-hand side,
this is my schema td_migration_demo.
It's ready with the converted script, the objects.
I'm gonna right-click and select "Apply to database,"
and click okay, click "Yes."
Now, it is in the process of applying those objects
onto my target system.
While that is happening,
I will show you...
This is my client tool SQL Workbench/J,
I have a connection with my target system.
These are our current schemas on my Redshift cluster.
I don't see the td_migration yet,
because it's not created yet,
so if I...
Query tried to select one of the fact table,
says doesn't exist.
So the process is still, there it is,
the process is done now,
and you can check the status by going to,
on the target side, on the main view,
you can check the status,
accessing the applied status.
So as you can see, the schema, tables, views, procedures,
all have been successfully created on the Redshift side.
Now let's go back to my client tool,
and refresh the dev.
The object explorer.
Right-click "Schemas," reload it,
now I see my td_migration_demo schema now,
and I see my tables here, my views.
Now when I try to run this query now,
it should run successfully,
but you don't get any data,
because we haven't loaded any data yet.
Now we move to the next step in the demo,
it's about loading the data into Redshift cluster
from Teradata warehouse.
(indistinct) we're gonna do is, while in the mapping view,
so we get to the mapping view here,
so while in the mapping view, right-click on the schema.
Actually, on the set of tables here.
Right-click there.
And you want to first, step is register agent.
The registering agent is actually registering
the data extraction agent that SCT comes with.
So you want to give it a name,
so I'm gonna call it as a td_agent,
host name, the agent is installed on the local machine,
so I'm gonna call it localhost.
And the port is 8192.
This information, you provide this information
when you're installing the product, so,
that's why I remembered these,
so I'm using them here.
And I will uncheck SSL.
I'm not using that,
and I will go ahead and register the agent.
So accept the risk and continue.
Now, I'm in data migration view, the agent is registered,
its status is active.
And once agent is successfully registered,
I right-click on my tables,
and then I will create a local task.
The local task is actually the agent,
now you're creating the data movement task,
so I'm gonna name it as td_migration_demo_schema_load,
and then I wanna select "Extract, upload and copy."
So the data extraction agent gives you three option,
where you can extract data to local system,
extract and upload data to Amazon S3 or data lake,
extract, upload and copy data to Amazon Redshift.
So in this demo,
I'm gonna demonstrate how to do all three in one go.
So I wanna select the third option.
I don't have LOBs in this case,
so I'm gonna uncheck that,
and then we're gonna go ahead and create the task.
So task has been created,
so this is my new task, Teradata migration schema load.
I have three phases in this task,
extract, upload, and copy.
So I'm gonna go ahead, select the start,
now as you can see, start has initiated extraction process,
and then the uploading to S3,
and then also running copy commands,
which you know, loads data to Redshift,
also being in action.
So we have completed for many tables,
the fact sales table is also finished.
Now if we click on that, it extracted 60,000 rows
from source,
uploaded 60,398 to S3 and 60,398 to Redshift side.
So if we go back to my client tool,
if I run this query now,
you can see that I have data return for my query.
That's how easy it is to move data from Teradata tables
to Redshift tables.
Now let's move on to the next step in the process,
which is about converting ETL scripts like BTEQ
to Amazon RSQL.
(indistinct) now our demonstration is converting ELT script
to Amazon Redshift RSQL.
In this demo, I'm gonna show you how to convert BTEQ script.
On the left-hand side, on the source side,
at the bottom, there is a section called "Scripts."
Under it you can see that there is a BTEQ option
for BTEQ Shell,
FastLoad, FastExport and MultiLoad.
These are some scripts you can convert
by using AWS SCT.
So let me load up BTEQ script.
So they're in this folder.
The script is gonna look like this.
It has a merge statement,
it has a logic based on the row count,
if the row count exists.
If the rows exist in product stage,
then it's gonna run this merge statement,
otherwise it's gonna exit.
So let's see how AWS SCT gonna convert that script.
Let's select the folder.
It's asking if there are any variables,
I don't have any variables, so I'm gonna click OK.
Then, you can see, the scripts are loaded.
This is my BTEQ script here.
Now, let's get into main view here.
And then if you go to the script view here,
you can see the BTEQ script loaded.
In order to script, before you convert the script,
we need to set the schema.
So current schema I'm working on is td_migration_demo,
and then apply.
Then I go to the script here,
and right-click on the left side,
I right-click on the script and convert it to RSQL.
Click okay.
So it identified the merge statement,
it highlighted that,
and it converted the merge statement to update or insert.
And other parts are equally converted
to RSQL compatible commands.
So it's doing row count,
and using if logic,
if activities go to zero, then go to no data,
otherwise go and run this command.
And it also converted labels, remark, everything,
as compatible with the RSQL.
You can save the SQL.
I'm gonna save it to same folder.
It's gonna ask me to replace, select yes,
and this is how the converted script's gonna look like.
It has update, and equate, and same as the BTEQ side.
In today's demo,
we have seen how to connect to Teradata system,
we have seen how to convert tables, views, objects,
from Teradata to Redshift.
We have seen how to handle some of the proprietary features
from Teradata to Redshift.
We have seen how to load the data from Teradata tables
to Redshift side,
and then finally we have seen how to convert
a BTEQ script to Amazon Redshift RSQL script.