search

bcgov / GDX-Analytics-microservice

The collection of GDX-Analytics Python microservices used to load and process data between systems and services.
Apache License 2.0
2 stars 0 forks source link

Gdxdsd 5548 sdpr related data from sbc the q #169

Closed doughon closed 1 year ago

doughon commented 1 year ago

This PR does the following:

  1. Adds a SDPR daily incremental feed of data from SBCs TheQ, that has an additional 'update_flag' column that is 1 if a record has been updated on a day after the day it was created and a 0 otherwise. This data will be stored in https://s3.console.aws.amazon.com/s3/buckets/sp-ca-bc-gov-131565110619-12-microservices?region=ca-central-1&prefix=client/theq_sdprabi/sbc-sdpr_daily-incremental/v01-Mar_2023_incremental/&showversions=false
  2. Adds a SDPR historical feed of data from SBCs the Q, that has data from 2022-04-01 up to the day before the job is ran. This data will be stored in https://s3.console.aws.amazon.com/s3/buckets/sp-ca-bc-gov-131565110619-12-microservices?region=ca-central-1&prefix=client/theq_sdprabi/sbc-sdpr_historical/&showversions=false

Please note that some of the config file were modified so that the tests can run. The modified configs will be added to the ticket in a zip file.

Testing instructions:

  1. Review the DML files sbc-sdpr_historical.sql and sbc-sdpr_last_full_day_incremental.sql
  2. Review the config files sbc-sdpr_historical.json and sbc-sdpr_last_full_day_incremental.json
  3. Log into the ec2 instance through the following commands 
    awsmfa prod <AWS OTP>
microservice_ssm
cd /home/microservice/branch/GDXDSD-5548-SDPR-related-data-from-SBC-TheQ/redshift_to_s3
  4. Run the following command and compare its output to what's expected. (Please note that the sdpr_historical command will take minutes to complete) 
    pipenv run python redshift_to_s3.py -c config.d/sdpr_historical.json 
pipenv run python redshift_to_s3.py -c config.d/sbc-sdpr_historical.json
pipenv run python redshift_to_s3.py -c config.d/sdpr_last_full_day.json
pipenv run python redshift_to_s3.py -c config.d/sdpr_last_full_day_incremental.json
pipenv run python redshift_to_s3.py -c config.d/sbc-sdpr_last_full_day_incremental.json
pipenv run python redshift_to_s3.py -c config.d/sdpr_hourly.json
    
***The microservice ran successfully***

Report: redshift_to_s3.py

Config: config.d/sbc-sdpr_last_full_day_incremental.json

DML: sbc-sdpr_last_full_day_incremental.sql

Microservice started at: 2023-03-06 15:24:12-0800 (PST), ended at: 2023-03-06 15:24:15-0800 (PST), elapsing: 0:00:02.220160.

Objects loaded to S3 /batch: 1/1 Objects successfully loaded to S3 /batch: 1

List of objects successfully loaded to S3 /batch

  1. processed/batch/client/doug_test/GDXDSD-5548/theq_sdprabi/sbc-sdpr_daily-incremental/v01-Mar_2023_incremental/sbc-sdpr_daily-incremental_20230306T232412

Objects to store: 1 Objects stored to s3 /client: 1

List of objects stored to S3 /client: 1: client/doug_test/GDXDSD-5548/theq_sdprabi/sbc-sdpr_daily-incremental/v01-Mar_2023_incremental/sbc-sdpr_daily-incremental_20230306T232412_part000.csv

Objects to process: 1 Objects processed to s3 /good: 1

List of objects processed to S3 /good: 1: processed/good/client/doug_test/GDXDSD-5548/theq_sdprabi/sbc-sdpr_daily-incremental/v01-Mar_2023_incremental/sbc-sdpr_daily-incremental_20230306T232412_part000


5. Check to see if the file appear in the s3 processed batch bucket: 
- SDPR Historical: https://s3.console.aws.amazon.com/s3/buckets/sp-ca-bc-gov-131565110619-12-microservices?region=ca-central-1&prefix=processed/batch/client/doug_test/GDXDSD-5548/theq_sdpr/historical/&showversions=false
- SDPR Daily: https://s3.console.aws.amazon.com/s3/buckets/sp-ca-bc-gov-131565110619-12-microservices?region=ca-central-1&prefix=processed/batch/client/doug_test/GDXDSD-5548/theq_sdpr/daily/&showversions=false
- SDPR Daily Incremental: https://s3.console.aws.amazon.com/s3/buckets/sp-ca-bc-gov-131565110619-12-microservices?region=ca-central-1&prefix=processed/batch/client/doug_test/GDXDSD-5548/theq_sdpr/daily-incremental/&showversions=false
- SBC SDPR Historical: https://s3.console.aws.amazon.com/s3/buckets/sp-ca-bc-gov-131565110619-12-microservices?region=ca-central-1&prefix=processed/batch/client/doug_test/GDXDSD-5548/theq_sdprabi/sbc-sdpr_historical/v01-Mar_2023_incremental/&showversions=false
- SBC SDPR Daily Incremental: https://s3.console.aws.amazon.com/s3/buckets/sp-ca-bc-gov-131565110619-12-microservices?region=ca-central-1&prefix=processed/batch/client/doug_test/GDXDSD-5548/theq_sdprabi/sbc-sdpr_daily-incremental/v01-Mar_2023_incremental/&showversions=false
- SDPR Hourly: https://s3.console.aws.amazon.com/s3/buckets/sp-ca-bc-gov-131565110619-12-microservices?region=ca-central-1&prefix=processed/batch/client/doug_test/GDXDSD-5548/theq_sdprabi/sdpr_hourly/v01-Mar_2023_incremental/&showversions=false
6. Check to see if the file appear in the s3 client bucket: 
- SDPR Historical: https://s3.console.aws.amazon.com/s3/buckets/sp-ca-bc-gov-131565110619-12-microservices?region=ca-central-1&prefix=client/doug_test/GDXDSD-5548/theq_sdpr/historical/&showversions=false
- SDPR Daily: https://s3.console.aws.amazon.com/s3/buckets/sp-ca-bc-gov-131565110619-12-microservices?region=ca-central-1&prefix=client/doug_test/GDXDSD-5548/theq_sdpr/daily/&showversions=false
- SDPR Daily Incremental: https://s3.console.aws.amazon.com/s3/buckets/sp-ca-bc-gov-131565110619-12-microservices?region=ca-central-1&prefix=client/doug_test/GDXDSD-5548/theq_sdpr/daily-incremental/v01-Mar_2023_incremental/&showversions=false
- SBC SDPR Historical: https://s3.console.aws.amazon.com/s3/buckets/sp-ca-bc-gov-131565110619-12-microservices?region=ca-central-1&prefix=client/doug_test/GDXDSD-5548/theq_sdprabi/sbc-sdpr_historical/&showversions=false
- SBC SDPR Daily Incremental: https://s3.console.aws.amazon.com/s3/buckets/sp-ca-bc-gov-131565110619-12-microservices?region=ca-central-1&prefix=client/doug_test/GDXDSD-5548/theq_sdprabi/sbc-sdpr_daily-incremental/v01-Mar_2023_incremental/&showversions=false
- SDPR Hourly: https://s3.console.aws.amazon.com/s3/buckets/sp-ca-bc-gov-131565110619-12-microservices?region=ca-central-1&prefix=client/doug_test/GDXDSD-5548/theq_sdprabi/sdpr_hourly/v01-Mar_2023_incremental/&showversions=false
7. Check to see if the file appear in the s3 processed good bucket: 
- SDPR Historical: https://s3.console.aws.amazon.com/s3/buckets/sp-ca-bc-gov-131565110619-12-microservices?region=ca-central-1&prefix=processed/good/client/doug_test/GDXDSD-5548/theq_sdpr/historical/&showversions=false
- SDPR Daily: https://s3.console.aws.amazon.com/s3/buckets/sp-ca-bc-gov-131565110619-12-microservices?region=ca-central-1&prefix=processed/good/client/doug_test/GDXDSD-5548/theq_sdpr/daily/&showversions=false
- SDPR Daily Incremental: https://s3.console.aws.amazon.com/s3/buckets/sp-ca-bc-gov-131565110619-12-microservices?region=ca-central-1&prefix=processed/good/client/doug_test/GDXDSD-5548/theq_sdpr/daily-incremental/v01-Mar_2023_incremental/&showversions=false
- SBC SDPR Historical: https://s3.console.aws.amazon.com/s3/buckets/sp-ca-bc-gov-131565110619-12-microservices?region=ca-central-1&prefix=processed/good/client/doug_test/GDXDSD-5548/theq_sdprabi/sbc-sdpr_historical/v01-Mar_2023_incremental/&showversions=false
- SBC SDPR Daily Incremental: https://s3.console.aws.amazon.com/s3/buckets/sp-ca-bc-gov-131565110619-12-microservices?region=ca-central-1&prefix=processed/good/client/doug_test/GDXDSD-5548/theq_sdprabi/sbc-sdpr_daily-incremental/v01-Mar_2023_incremental/&showversions=false
- SDPR Hourly: https://s3.console.aws.amazon.com/s3/buckets/sp-ca-bc-gov-131565110619-12-microservices?region=ca-central-1&prefix=processed/good/client/doug_test/GDXDSD-5548/theq_sdprabi/sdpr_hourly/v01-Mar_2023_incremental/&showversions=false