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