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 6357 dependabot urllib3 part2 #209

Closed doughon closed 9 months ago

doughon commented 9 months ago

This PR does the following:

  1. Updates the urllib3 python package to version 1.26.18 in the google-api and looker_dashboard_usage microservices

Please note that some of the files on ec2 has been modified for testing. All of these will be added to the ticket in Jira in a zip file

Testing instructions: We'll be testing to make sure that the microservices are still able to run after upgrading urllib3 to it's new version. A microservice that runs without any errors in it's report will show that this is the case.

  1. Log into the ec2 instance using the following commands: 
    awsmfa prod <AWS OTP>
microservice_ssm
  2. Navigate to the looker_dashboard_usage microservice: 
    cd /home/microservice/branch/GDXDSD-6357_dependabot_urllib3_part2/looker_dashboard_usage
  3. Run the following command to test the looker_dashboard_usage microservice: 
    pipenv run python looker_dashboard_usage.py GDXDSD-6357_looker_dashboard_usage.json 
    
Report: looker_dashboard_usage.py

Config: GDXDSD-6357_looker_dashboard_usage.json

This microservice ran: successful

Microservice started at: 2024-01-29 16:04:47-0800 (PST), ended at: 2024-01-29 16:04:49-0800 (PST), elapsing: 0:00:01.731819.

Objects to process: 4 Objects that failed to process: 0 Objects output to 'processed/good': 4 Objects output to 'processed/bad': 0

List of objects successfully fully ingested from S3, processed, loaded to S3 ('good'), and copied to Redshift:

  1. client/doug_test/GDXDSD-6357/looker_dashboards/dashboard.2024-01-29
  2. client/doug_test/GDXDSD-6357/looker_dashboards/history.2024-01-29
  3. client/doug_test/GDXDSD-6357/looker_dashboards/user.2024-01-29
  4. client/doug_test/GDXDSD-6357/looker_dashboards/looker_user_facts.2024-01-29
  5. Navigate to the google-api microservices: 
    /home/microservice/branch/GDXDSD-6357_dependabot_urllib3_part2/google-api
  6. Run the following command to test the google_mybusiness microservice: 
    pipenv run python google_mybusiness.py -o credentials_mybusiness.json -a credentials_mybusiness.dat -c GDXDSD-6357_config_mybusiness.json
    
Report: google_mybusiness.py

Config: GDXDSD-6357_config_mybusiness.json

Microservice started at: 2024-01-26 15:52:44-0800 (PST), ended at: 2024-01-26 15:56:45-0800 (PST), elapsing: 0:04:01.532733.

Locations to process: 64 Successful API calls: 64 Failed API calls: 0 Successful loads to RedShift: 64 Failed loads to RedShift: 0 Files loads to S3 /good: 64 Files loads to S3 /bad: 0 Sites failed due to hitting an error: 0

Objects loaded RedShift and to S3 /good: 1: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Kelowna_2024-01-20_2024-01-23.csv 2: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Chetwynd_2024-01-20_2024-01-23.csv 3: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Williams-Lake_2024-01-20_2024-01-23.csv 4: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Valemount_2024-01-20_2024-01-23.csv 5: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Oliver_2024-01-20_2024-01-23.csv 6: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Fort-St.-John_2024-01-20_2024-01-23.csv 7: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Port-Alberni_2024-01-20_2024-01-23.csv 8: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Prince-George-(ICBC-Driver-Licensing---no-Road-Tests)_2024-01-20_2024-01-23.csv 9: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Kamloops-(No-Driver-Services)_2024-01-20_2024-01-23.csv 10: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Atlin_2024-01-20_2024-01-23.csv 11: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Kaslo_2024-01-20_2024-01-23.csv 12: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Ashcroft_2024-01-20_2024-01-23.csv 13: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Vancouver-(Limited-Services)_2024-01-20_2024-01-23.csv 14: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Fort-Nelson_2024-01-20_2024-01-23.csv 15: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Cranbrook_2024-01-20_2024-01-23.csv 16: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Vernon_2024-01-20_2024-01-23.csv 17: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Creston_2024-01-20_2024-01-23.csv 18: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Powell-River_2024-01-20_2024-01-23.csv 19: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Salmon-Arm_2024-01-20_2024-01-23.csv 20: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Squamish_2024-01-20_2024-01-23.csv 21: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Bella-Coola_2024-01-20_2024-01-23.csv 22: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Princeton_2024-01-20_2024-01-23.csv 23: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Golden_2024-01-20_2024-01-23.csv 24: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Nakusp_2024-01-20_2024-01-23.csv 25: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Stewart_2024-01-20_2024-01-23.csv 26: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Victoria_2024-01-20_2024-01-23.csv 27: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Hazelton_2024-01-20_2024-01-23.csv 28: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Quesnel_2024-01-20_2024-01-23.csv 29: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Masset_2024-01-20_2024-01-23.csv 30: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Burns-Lake_2024-01-20_2024-01-23.csv 31: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Dease-Lake_2024-01-20_2024-01-23.csv 32: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Nelson_2024-01-20_2024-01-23.csv 33: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Ucluelet_2024-01-20_2024-01-23.csv 34: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Terrace_2024-01-20_2024-01-23.csv 35: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Sechelt_2024-01-20_2024-01-23.csv 36: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Surrey-(Limited-Services)_2024-01-20_2024-01-23.csv 37: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Fort-St.-James_2024-01-20_2024-01-23.csv 38: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Mackenzie_2024-01-20_2024-01-23.csv 39: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Penticton_2024-01-20_2024-01-23.csv 40: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Maple-Ridge_2024-01-20_2024-01-23.csv 41: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Lillooet_2024-01-20_2024-01-23.csv 42: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Trail_2024-01-20_2024-01-23.csv 43: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Chilliwack_2024-01-20_2024-01-23.csv 44: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Invermere_2024-01-20_2024-01-23.csv 45: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Courtenay_2024-01-20_2024-01-23.csv 46: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Fernie_2024-01-20_2024-01-23.csv 47: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Clinton_2024-01-20_2024-01-23.csv 48: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Duncan_2024-01-20_2024-01-23.csv 49: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Smithers_2024-01-20_2024-01-23.csv 50: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-100-Mile-House_2024-01-20_2024-01-23.csv 51: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Grand-Forks_2024-01-20_2024-01-23.csv 52: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Daajing-Giids_2024-01-20_2024-01-23.csv 53: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Prince-Rupert_2024-01-20_2024-01-23.csv 54: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Port-Hardy_2024-01-20_2024-01-23.csv 55: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Dawson-Creek_2024-01-20_2024-01-23.csv 56: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Burnaby-(Limited-Services)_2024-01-20_2024-01-23.csv 57: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Ganges_2024-01-20_2024-01-23.csv 58: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Vanderhoof_2024-01-20_2024-01-23.csv 59: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Merritt_2024-01-20_2024-01-23.csv 60: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Revelstoke_2024-01-20_2024-01-23.csv 61: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Sparwood_2024-01-20_2024-01-23.csv 62: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Houston_2024-01-20_2024-01-23.csv 63: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Kitimat_2024-01-20_2024-01-23.csv 64: processed/good/client/doug_test/GDXDSD-6357/google_mybusiness/servicebc/gmb_Service-BC-Centre-Campbell-River_2024-01-20_2024-01-23.csv 

6. Run the following command to test the google_mybusiness_servicebc_derived microservice:

pipenv run python google_mybusiness_servicebc_derived.py -c GDXDSD-6357_config_servicebc.json

Success: executed the transaction to prepare the google_mybusiness_servicebc_derived DT 

7. Run the following command to test the google_search microservice:

pipenv run python google_search.py -o credentials_search.json -a credentials_search.dat -c GDXDSD-6357_config_search.json 

8. Check the logs for the google_search microservice and confirm that the run was successful

tail -50 logs/google_search.log 

9. 

Cleanup steps:
As these tests are adding potentially large amounts of data into redshift, an additional cleanup step is needed. 

1. Connect to redshift using the following command:

microservice_rs

2. Run the following query to remove the data from the google_mybusiness microservice:

snowplow=> truncate table test.gdxdsd6357_google_mybusiness; TRUNCATE TABLE and COMMIT TRANSACTION 

3. Run the following query to drop the google_mybusiness_servicebc_derived microservice table:

snowplow=> drop table if exists test.gdxdsd6357_google_mybusiness_servicebc_derived; DROP TABLE 

4. Run the following query to remove the data from the google_search microservice:

snowplow=> truncate table test.gdxdsd6357_google_googlesearch; TRUNCATE TABLE and COMMIT TRANSACTION 


5.