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:

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:

​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.