Ph 23410 md5 key - [merged]

[jnanaswaroop]

In GitLab by @mjaskiewicz1 on Jun 11, 2024, 22:30

_Merges ph_23410_md5key -> master

To join between the data_prep.raw_ccx table, I need some kind of key since the CCX uses item_start_date, item_end_date, uom, manufacturer_part_number, contract_number, organization_eid, vendor. This kind of key is slow to join against so I want to add an MD5 hashed version of the columns in question.

The deployment was also broken given the switch to kaniko.

[jnanaswaroop]

In GitLab by @mjaskiewicz1 on Jun 11, 2024, 22:37

added 1 commit

• 488c5fb7 - ph_23410

Compare with previous version

[jnanaswaroop]

In GitLab by @mjaskiewicz1 on Jun 11, 2024, 22:57

added 1 commit

• dff95774 - ph_23410

Compare with previous version

[jnanaswaroop]

In GitLab by @mjaskiewicz1 on Jun 11, 2024, 22:59

added 1 commit

• c0fcdda7 - ph_23410

Compare with previous version

[jnanaswaroop]

In GitLab by @mjaskiewicz1 on Jun 11, 2024, 23:01

added 1 commit

• 7802bab0 - ph_23410

Compare with previous version

[jnanaswaroop]

In GitLab by @mjaskiewicz1 on Jun 11, 2024, 23:17

requested review from @onmay16 and @nkessler

[jnanaswaroop]

In GitLab by @nkessler on Jun 12, 2024, 00:52

is there a reason to hash the columns instead of just using a uid? it seems like the process to hash columns instead of just using the uid would be slower

[jnanaswaroop]

In GitLab by @mjaskiewicz1 on Jun 12, 2024, 21:33

The whole objective here is to join from my contract table to data_prep.raw_ccx.

The data_prep.raw_ccx table brings in all the raw data rows from all the files.

• The key for a contract line is composed of 7 columns.
• Each line is labeled with a character identifying what that line is: insert, update, or delete.
• A unique contract row may actually appear multiple times in the raw data because there is a history of updates included in that table. If I had a new raw update row that I wanted to label with a uid, I would have to search the table for previous instances of that key to find the uid, which I'm sure would be prohibitively slow. Hashing the key seemed to be a quick way to facilitate joining.

[jnanaswaroop]

In GitLab by @nkessler on Jun 12, 2024, 22:25

gotcha -- makes sense. just curious here if nulls in any of those field will throw a wrench into this at all. if not i'll approve

[jnanaswaroop]

In GitLab by @mjaskiewicz1 on Jun 13, 2024, 01:32

Those fields should never be null. I confirmed with CCX and in the contract table. We do have some nulls in data_prep.raw_ccx but they never made it out of that table. I'm confirming w/ CCX right now what's wrong.

[jnanaswaroop]

In GitLab by @mjaskiewicz1 on Jun 13, 2024, 19:08

Confirmed with CCX. The rows with nulls are mistakes on their end and are to be ignored.

[jnanaswaroop]

In GitLab by @mjaskiewicz1 on Jun 14, 2024, 00:58

enabled an automatic merge when all merge checks for 7802bab030d81f65ede42021acb0b5c0327999d9 pass

[jnanaswaroop]

In GitLab by @mjaskiewicz1 on Jun 14, 2024, 01:04

Nate, need an approval.

[jnanaswaroop]

In GitLab by @nkessler on Jun 14, 2024, 01:15

approved this merge request

[jnanaswaroop]

In GitLab by @mjaskiewicz1 on Jun 14, 2024, 01:15

mentioned in commit a7b034b30cf590c7e5fafc168859cd3d3ce5a582