AnVIL uses non-standard encoding for MD5 digests

[hannes-ucsc]

This is the underlying cause for #7153, which we'll address differently. Instead of the commonly used hexadecimal encoding, AnVIL appears to be using Base64 which not very common. This is blocking mirroring of AnVIL data.

[hannes-ucsc]

Spike to …

1. provide reproduction/evidence using a BQ query result for an example snapshot in anvildev and anvilprod

2. perform a survey in anvildev and anvilprod to understand if this is universally true or if there is a mix of encodings

[dsotirho-ucsc]

1. provide reproduction/evidence using a BQ query result for an example snapshot in anvildev and anvilprod

## Info about a random file from anvil9

curl -s 'https://service.explore.anvilproject.org/index/files/7a5babe7-b85c-4d2c-953f-21e2c84c7e61?catalog=anvil9' | jq '.files[] | .file_name, .file_size, .file_md5sum, .url'
"IBS.1.pgen"
3301775
"7o3p9iFlw0YdCNzfCTYgtw=="
"https://service.explore.anvilproject.org/repository/files/7a5babe7-b85c-4d2c-953f-21e2c84c7e61?catalog=anvil9&version=2022-06-01T00%3A00%3A00.000000Z"

## Confirm md5sum from Azul matches in BigQuery

> SELECT file_md5sum FROM `datarepo-b555b2f5.ANVIL_1000G_PRIMED_data_model_20240410_ANV5_202502211647.anvil_file` where file_name = 'IBS.1.pgen' and file_size = 3301775
7o3p9iFlw0YdCNzfCTYgtw==

## Download the file

$ curl -L -s -o 'IBS.1.pgen' 'https://service.explore.anvilproject.org/repository/files/7a5babe7-b85c-4d2c-953f-21e2c84c7e61?catalog=anvil9&version=2022-06-01T00%3A00%3A00.000000Z'

$ ls -l IBS.1.pgen
-rw-r--r--@ 1 daniel  staff  3301775 May 21 14:50 IBS.1.pgen

## Check its md5sum

$ md5sum IBS.1.pgen
ee8de9f62165c3461d08dcdf093620b7  IBS.1.pgen

## Confirm "ee8de9f6..." converts to "7o3p9iFl..."

$ python -c "import binascii; import base64; print(base64.b64encode(binascii.unhexlify('ee8de9f62165c3461d08dcdf093620b7')))"
b'7o3p9iFlw0YdCNzfCTYgtw=='

2. perform a survey in anvildev and anvilprod to understand if this is universally true or if there is a mix of encodings

All snapshots in anvildev and anvilprod, except for one in anvilprod, were found to use the base64 encoded version for the file_md5sum value.
The one snapshot that doesn't use base64 has 581 anvil_file rows, and each row has a file_md5sum value in a 32 character hex format. This snapshot was added in anvil9.

SELECT file_name, file_md5sum FROM `datarepo-ec623ac1.ANVIL_HudsonAlpha_LR_v1_GRU_20241018_ANV5_202410240036.anvil_file`

file_name                      file_md5sum
m64017_210722_212231.fastq.gz  0122a8f7aeebcfe4e472a1c9931ae61a
m64017_211005_050039.fastq.gz  e97d8c15ba80fb7d5855e9224656f8cd
m64017_211014_211634.fastq.gz  f7b66282e84b8550ac2e1b6fc7aec2a2
m64017_211016_081238.fastq.gz  d537a8520e9015e7a7a9b8059c64da6b
…

[hannes-ucsc]

Reached out to AnVIL leadership: https://ucsc-gi.slack.com/archives/C03TPJS54DC/p1748456430899149