(upbeat music)
- [Instructor] You can use the AWS Schema Conversion Tool
to convert BigQuery database objects
to a format compatible with Amazon Redshift.
Then you can apply the converted code
to your target database and migrate data.
This video shows how to get started
with BigQuery to Amazon Redshift migrations
and covers the key steps of this process.
AWS SCT uses a service account to connect
to your BigQuery data warehouse.
You can create a service account
in the Google Cloud management console,
but first make sure that you turned on the BigQuery API,
then create a service account.
(upbeat music)
And grant the following privileges.
AWS SCT uses the BigQuery admin role
to load your BigQuery metadata in the migration project.
To extract data from a BigQuery data warehouse,
AWS SCT uses the Google Cloud storage bucket.
To access this bucket,
you grant the storage admin role to your service account.
(upbeat music)
Then you create a new JSON key
and use it in AWS SCT to connect to BigQuery.
(upbeat music)
Check your downloads folder
to find a service account key file.
Before we launch AWS SCT,
let us check our source and target databases.
To demonstrate data migration,
we can use the line order table.
This table includes some data in the source database
and it doesn't exist in the target database.
We can use AWS SCT to create it.
Now we start at AWS SCT and create a new project.
To connect to BigQuery,
we use the service account key file that we created
in the Google Cloud management console.
(upbeat music)
Then we use database credentials
from AWS Secrets Manager to connect to Amazon Redshift.
(upbeat music)
You can add multiple source
and target databases in a single project.
We create mapping rules to describe the source target pair
for our BigQuery to Amazon Redshift migration.
We switch to the main view and start the schema conversion.
Our source data set includes 33 tables, 278 views,
82 procedures, and 14 functions.
To assess the migration complexity,
we create an assessment report for this data set.
You can see that AWS SCT converted most
of the database storage objects.
These objects include data sets,
tables, and materialized views.
Also, AWS SCT converted such database code objects
as views, SQL functions, JavaScript functions,
table functions, and stored procedures.
However, some of the database objects
require manual conversion.
AWS SCT highlights these objects
in blue in the conversion statistics diagram.
In the action items tab, you can see the recommended actions
for each conversion issue.
For example,
AWS SCT converts the BigQuery stream data type
to the character varying data type in Amazon Redshift.
Because this data type is a shorter length,
you need to make sure that you avoid data loss
during migration.
AWS SCT has several limitations
when using BigQuery as a source, for example,
AWS SCT can't convert sub queries and analytic functions
as well as geography, statistical aggregate,
or some of the string functions.
Find the full list
of limitations in the AWS SCT user guide.
We plan to address these limitations
in the future releases of AWS SCT.
Despite these limitations,
you can use AWS SCT to automatically convert more
than 80% of objects in your BigQuery data sets.
Now let us convert the data set that includes data
for migration.
Here's the table that we plan to migrate.
(upbeat music)
We convert the data set.
During conversion,
AWS SCT automatically generates the assessment report.
You can see the converted code of the line order table.
AWS SCT converted string columns
to character varying columns and added an action item.
Now we apply the converted code to the target database.
To convert SQL statements and queries,
make sure that you include them in the stored procedures
in your BigQuery project.
AWS SCT converts all BigQuery metadata objects.
For migrations from BigQuery,
you can't use AWS SCT to convert external SQL scripts
or additional files.
Let us check our target database.
After we refresh the database, you can see the new schema
that includes the line order table.
You can see that the line order table now exists
in the Amazon Redshift database.
It doesn't include any data.
AWS SCT uses data extraction agents to migrate data
from data warehouses to Amazon Redshift.
The zip file that you downloaded to install AWS SCT
includes the extraction agent installer file.
Run the installer file
and then configure the data extraction agent.
(upbeat music)
For a source vendor enter no,
because BigQuery doesn't require a JDBC driver.
For target vendor,
enter the path to your Amazon Redshift JDBC driver.
The data extraction agent stores temporary data
in the working folder.
You can use the project folder
as a working folder for the data extraction agent.
After you configure the data extraction agent,
you can review the file that includes all settings.
Then switch back to AWS SCT, choose the data migration view,
and register your agent.
Because our data extraction agent is running
on the same computer as AWS SCT,
we enter the local host IP address as the host name.
(upbeat music)
You can see that AWS SCT can connect
to the data extraction agent.
This completes the installation and registration
of the data extraction agent.
To manage the migration of large tables,
you can create virtual partitions in AWS SCT.
Our source table is relatively small
and doesn't require partitions.
However, let us show you how to create virtual partitions.
We split the source table
depending on the values of the third column.
We use range partitioning
and create a separate partition for null values.
Now we create a local migration task.
This task uses the data extraction agent to migrate data.
For each task, you choose the migration mode.
The available options include extract source data
and store it on a local PC where the agent runs,
extract data and uploaded on an Amazon S3 bucket,
extract, upload, and copy data from your S3 bucket
to Amazon Redshift.
We choose this option.
Also, we enter the Google Cloud storage bucket
where the data extraction agent can store the source data.
You can create this bucket
in the Google Cloud management console.
Finally, we set up the folder in our Amazon S3 bucket
where the data extraction agent can upload the data.
AWS SCT creates a data migration task
and a subtask for each virtual partition.
You can manage data migration for each partition.
Now we start the data migration task.
The data extraction agent extracts data from BigQuery,
then the agent uploads data to Amazon S3
and launches the copy command to move it to Amazon Redshift.
AWS SCT migrated data
from the source BigQuery table to Amazon Redshift table.
Let us check this table in the target database.
You can see
that the data migration task completed successfully
and the line order table includes data.
Now, you know how to use AWS SCT
to convert BigQuery projects to Amazon Redshift
and migrate data.
Thanks for watching this video.