SOP to raise pull request for Data Updates in Production
Raise pull request in production.
Pre-requisite/checks:
- Vinesh approval is required before executing Data Manipulation Language DML queries (except email / phone validation issue)
- For ALL pull requests, if workflow shows “PENDING” then don't update that/those customer(s) during that time & educate user.
- For ‘workflow check’ use below query.
Query = Select * from mdm_smds.customer_wf_process_details where customer_cmd_code = '';
Workflow State Y means it is pending for ADS to action.
CMD Screen “The customer is not available for edit as the record is pending workflow approval.”
Code status description – cross check code status before performing DML’s in requests for code status/reason changes.
Query Select customer_cmd_code,customer_status_cd,reason_name from mdm_smds.customer_status_reasons where customer_cmd_code ='';
Similarly, you can cross check other values as per request before executing DML’s.
To check latest version in DBeaver
Query Select * from public.flyway_schema_history fsh
History can be downloaded.
Standards:
For data pipeline PR's lets follow simple standards, which will help to us to search/navigate the PRs easily.
- PR Title
- Labels
- Assignee
PR Title:
Format: <Incident/RITM Number> - <Short Description>
Examples
Labels:
Created below labels, so you can assign these labels directly to PR.
- PROD, CDT, PreProd - label based on to which ENV you are making database changes.
- Phone Validation, Email Validation, Customer Suspension - label based on the database change.
Examples
Assignee: Assign the PR to you in the Assignee section
Example:
Steps to update Telephone number using DML.
Phone '23569777555' to be validated using DML.
Precheck:
Query:
select * from mdm_smdsmd.phone_validation_tracker pvt where phone_num = '23569777555';
Validation code = 302.
Validation Status = NDC Not Found.
Is_validated = N
Iso_country_code = Null
dialing_code = Null
phone_type = Null
Query:
select * from mdm_smds.ref_international_dialing_prefix where dialing_code = '235';
It will give you dialing code, country name, country code etc details.
Open Visual Studio (VS) code
GITHUB commands are executed in VS code.
Navigation of Production path in VS -
smds-cmd-data-service src mainresourcesdbdataprod (DML are saved in a file)
In above screen it shows main branch.
* Every time create a new branch in GITHUB by clicking on Main & type feature/Ticketnumber as shows below and click Create. Took an example for incident #INC6608220.
GITHUB Link: https://github.com/Maersk-Global/smds-cmd-data-service.git
feature/INC6608220 please click on create in GitHub.
feature/INC6608220 got created.
2. git remote update
* Every time need to run command git remote update. Go to the New terminal as shown below & execute command ‘git remote update’ to update the Local
Command can be run by clicking drop down arrow next to powershell
It will showfeature/INC6608220 the branch you created.
3. git checkout feature/Ticketnumber
Command **git checkout feature/**INC6608220 (in bottom left of VS code it will show same feature/Ticketnumber. It is the feature you created.
4. Create a new file in Prod
Check latest version & then create next version.
New file V4_1_6__update_smdsmd_phone_validation_INC6608220.sql
Right click on Production folder and then click “New File”
Select New File
Name the file (latest version)
6.Use below sql for validating phone. In the file after you write/paste the sql than you need to save the sql (Control + S)
Query:
update mdm_smdsmd.phone_validation_tracker
set validation_code = '201', validation_status = 'Valid Number', is_validated = 'Y',
dialing_code = '235', iso_country_code = 'TD', phone_type = 'TEL'
where phone_num = '23569777555';
7. Command git add . (git space add space dot)
8. Command git status (to check status which changes you are promoting)
9. Command git commit -m "Ticketnumber Short description" within double inverted commas. You can pass commit message as per requirement for DML which you need to execute.
10. Command git push
In GITHUB follow below steps open https://github.com/Maersk-Global/smds-cmd-data-service.git it will ask for Compare and Pull request
Click compare and pull request.
In Reviewer add the approvers name(s) Yashwanth Kurru, Harshit Chouhan, Gopala Krishnan Rajendran. (In this PR added Yashwanth Kurru)
Add description (provide complete details like incident number and requirement)
Click Create pull request.
After approved (you get an email) click merge pull request
Pre-approval
Email approval
Post approval
Click Merge Pull request.
click confirm merge.
Merged message appears.
Go to Actions
Select ‘Data Migration Pipeline’
You need to select prod environment from dropdown & hit run workflow.
Click Run workflow.
Data migration pipeline is completed.
Cross check data updated.
select * from mdm_smdsmd.phone_validation_tracker pvt where phone_num = '23569777555';
*Auto sync job is not required for Phone/email validation. It is required for other data updates.
Knowledge: “Read Me” page by scrolling below for more details in https://github.com/Maersk-Global/smds-cmd-data-service
Tips – File Version examples,
If you see last version as V4_1_1__update_smdsmd_phone_validation_Ticketnumber.sql then next version will be V4_1_2__update_smdsmd_phone_validation_Ticketnumber.sql
In file name: V4_1_2__update_phone_validation_Ticketnumber.sql
There is __ double underscore after 2. Same format to be followed.
File name starts with V4 where 4 is for production. Add the latest file version to be created.
If the last version is V4_0_9 then the next version will be V4_1_0
Quick guide Replace Ticketnumber below by actual number.
1. Open GITHUB link: Maersk-Global/smds-cmd-data-service (github.com)
2. * Every time create a new branch in GITHUB by clicking on Main, type
feature/Ticketnumber
Click create.
3.* Every time need to run command
git remote update
4. git checkout feature/Ticketnumber
5. Create file in Prod after checking latest version in DB
Select * from public.flyway_schema_history fsh
6. File name: V4_x_x__update_xxx_xxx_Ticketnumber.sql
7. Paste SQL & Control (S) Save
8. git add .
9. git status
10. git commit -m "Ticketnumber Short description"
11. git push
12. Compare and Pull request.
13. Add reviewers, Label & Assignee to yourself.
14. Create pull request.
15. After above you get pull request number/link to be shared to reviewer
16. After PR is approved you get an email. Click merge pull request.
17. Confirm Merge.
18. Actions - Data migration pipeline - Run workflow - Select from drop down *Prod
19. Data migration pipeline is completed.
20 *Auto sync in postman & then check changes in DB. (It is not required for phone/email validation). It’s to be done for customer/contact/facility/concern etc. as per ticket.
Few file names for reference (Version is excluded)
update_smdsmd_phone_validation_Ticketnumber.sql
update_smdsmd_email_validation_Ticketnumber.sql
update_smds_customer_status_Ticketnumber.sql-
update_smds_customer_status_taxnull_Ticketnumber.sql
update_smds_customer_status_tax_Ticketnumber.sql
update_smds_contact_customer_relationship_Ticketnumber.sql
update_smds_contact_doc_type_and_comm_pref_Ticketnumber.sql
update_smds_contact_customer_relationship_Ticketnumber.sql
update_smds_customer_tax_email_validation_Ticketnumber.sql
update_smds_contact_information_Ticketnumber.sql