Blogs

Migrating data from SharePoint to Redshift using AWS infrastructure

Purpose of the article: Created a Python-based automated technique for gathering data from SharePoint locations and sending it to the Redshift data warehouse. This process is automated by leveraging AWS infrastructure such as S3 Bucket, ECS, Code pipeline, Cloud Watch, and Redshift.

Intended Audience: AWS and Python

Tools and Technology: AWS, Python

Keywords: SharePoint, S3, Redshift, Bitbucket, Code pipeline

Architecture:

Migrating data from SharePoint to Redshift using AWS infrastructure1

 

Introduction:

Some time back, individuals  would  download data from SharePoint servers manually. In the near future, Python will be used to automate  processes such as downloading or editing SharePoint lists, views, and folders, as well as performing transformations on data before it is loaded into the target.

Technology upgradation has ensured a Python-based automated technique for gathering data from SharePoint locations and sending it to the Redshift data warehouse. This process is automated by leveraging AWS infrastructures such as S3 Bucket, ECS, Code pipeline, Cloud Watch, and Redshift


 

To begin, we must register the SharePoint site and produce a Client Id and Client Secret for the SharePoint site, which will be used for authentication.

A glimpse of the Share Point File location:

Migrating data from SharePoint to Redshift using AWS infrastructure2

Preview of a data file:

Step 1: Requisite libraries:

Step 2: We fetch  data from SharePoint. The following Python script will download the SharePoint file.

 

Step 3: Required credentials are stored in Amazon Web Services’ secret manager.

Credentials are obtained via AWS Secret Manager.

Step 4: Redshift warehouse is linked using python script

Step 5: Copy command is as follows: Copy converted data from S3 bucket to Redshift table.

Schema name, Table name, Bucket Name, Bucket Folder, File Name, and Redshift IAM Role are all required parameters.

Step 6: Migration of data to Redshift using Python script is complete.  are.

Step 7: Given below is a snapshot of data that has been migrated in a Redshift table.

Step 8: Monitoring logs in Amazon Web Services Cloud Watch.

Step 9: If the job fails, Microsoft Teams  gets a failure notification.

Contact us for further details.

    Leave A Comment

    Related Post

    Python-Stand-Alone Application

    Purpose of the Article: Execute python code without having Python installed in the system, which will be used for NON-TECHNICAL Persons. Intended Audience: Python Developers

    Read More »

    WordPress REST APIs

    Purpose of the Article: To Learn about WordPress Rest APIs Intended Audience: PHP Developers Tools and Technology: WordPress Keywords: WordPress Rest API What Is API?

    Read More »