Dynamic Migration of Cloud Database to Snowflake

Use Talend Cloud to migrate your existing cloud database to Snowflake faster than you can imagine.

See how easy it is to migrate an existing cloud database to Snowflake. This set of jobs will extract the data from your existing cloud-based database, dynamically generate new tables, and import the data in Snowflake. Once you’ve got the basics, you can build your own flow.

Prerequisites 

Setup

Snowflake Account Setup

You should already have a Snowflake Trial Account. Follow the steps below to configure your Snowflake Trial Database to integrate with the example jobs:

  1. Follow Snowflake guidelines to create a new warehouse named TRIAL_WH and grant full privileges to the PUBLIC Role
  2. Follow Snowflake guidelines to create a new database named MYSQL and grant full privileges to the PUBLIC Role.

.

Amazon AWS Account Setup

To utilize the features of this demo, you will need to have access to Amazon AWS S3 Storage. If you don’t already have an AWS Account, there is a Free Tier account provided by Amazon that will be sufficient.

    1. To create a Free Tier Amazon Web Services account, follow the below link and submit your information:
      https://aws.amazon.com/free/
    2. Log in to your Amazon AWS Account
    3. Navigate to S3 Storage.
    4. Create an S3 Bucket called “trial-snowflake” and accept all default settings.
    5. You will enter this Bucket Name when prompted in the Talend Cloud example jobs.

.

Talend Cloud Setup

Talend offers a FREE 30-day trial of Talend Cloud to start your cloud integrations. Included with the trial are examples of how you can use Talend Cloud to achieve your cloud integration strategies.

    1. Create Talend Cloud Trial Account for a FREE 30-day trial
    2. Log in to your Talend Cloud Account.

 

Execution

Simple Execution (Using Talend Cloud)

aws-load

aws load 2

.

Dynamic Migration Example

    1. Log in to Talend Cloud and locate the sample Salesforce Jobs in the Samples Pane.

        1. Advanced – Snowflake Trial – Dynamic Migration (Step 1 of 2)
        2. Advanced – Snowflake Trial – Dynamic Migration (Step 2 of 2)
          .
    2. Click on each example listed above to view more information about the job. When ready, import the sample job into your personal workspace by clicking on the Try It button.
      .
    3. When the jobs are imported into your workspace, you will be prompted to fill in the necessary connection information to connect to your account(s).
      .
      Connection Name Parameters
      Snowflake 

      Account: <YourSnowflakeAccount>

      UserName: <YourSnowflakeUserName>

      Password: <YourSnowflakePassword>

      Warehouse: TRIAL_WH

      Schema: PUBLIC

      Database: MYSQL
      AWS_S3

      AccessKey: <YourAWSS3AccessKey>

      SecretKey: <YourAWSS3SecretKey>

      AWS_MySQL

      Username: sample_user

      Password: snowflake

      Host: talend.cqdx7dv9ql89.us-east-1.rds.amazonaws.com

      Port: 3306

      Database: movie_ratings
      .
    4. If you are not prompted to or need to modify your connection information at any time, you can follow the steps below:

          1. Navigate to “Manage” and “Connections” under your personal workspace.
          2. In Talend Cloud, click on the individual connections listed below and edit the connection parameters with the appropriate information based on your individual account(s). Save the changes.


            .
    5. Navigate to Flows on the Talend Cloud menu. In the Personal Workspace, click on the Flow named Advanced – Snowflake Trial – Dynamic Migration (Step 1 of 2). From this view you can review the Flow Description Information, Run the flow, and see the Flow’s Run History.


      .
    6. Click on Builder. The Builder view will provide additional details, allowing you to change/update the Job that the flow executes. You will also see the Parameters required to execute the job.
      .
    7. Enter the S3 Bucket name that you created in the Amazon AWS Account Setup steps above.


      .
    8. Click on Go Live. The Go Live view will ask for connection details. Here, under the Snowflake Connection, specify Snowflake, under the AWS S3 Connection, specify AWS_S3 and under the AWS MySQL Connection, specify AWS_MySQL from the respective dropdowns.
      .
    9. From this view you can also select the Cloud Runtime and schedule the execution. Leave the default values and click Go.



      • This job will configure your Snowflake instance by dropping database tables used in this example (if they exist), creating a staging area in Snowflake and linking it to an S3 Storage location (See Image 1 above)
    10. When complete, check your Snowflake account to confirm that you now have Stages (MY_S3) and File Formats (MYCSVFORMAT) created in the MYSQL Database.
      .
    11. Now, back in Talend Cloud, navigate back to the Flows view and select the Flow named Advanced – Snowflake Trial – Dynamic Migration (Step 2 of 2). Again, review the Flow Description, Run Details and Run History.
      .
    12. Click on Builder and then Go Live.
      .
    13. Here you will again specify 3 Connections (the three connections you configured earlier)
      .
        1. Snowflake Connection: Salesforce
        2. AWS MySQL Connection: AWS_MySQL
        3. AWS S3: AWS_S3
      .
    14. Keep the default values in the Schedule Pane and click on Go.


      • This job will dynamically read an existing AWS MySQL Database and replicate the table structure within Snowflake while at the same time, extract the table data and save it to an Amazon S3 bucket. After all tables have been created in Snowflake and data extracted, the job will populate the data from S3 into the newly created Snowflake Tables (See Image 2 above).

| Last Updated: September 14th, 2018