Optionally forestall duplicate ingestion

[googlecloudplatform]

Optionally forestall duplicate ingestion

[mservidio]

I prefer the approach of taking an MD5 hash on the file content. However both have their merits. We could allow for both and make it configurable also. Default on file hash, but allow a user to configure it within the individual file configs. If we take a MD5 hash we'll also need a place to persist historical hashes. We could keep at a row level which would be redundant or maybe even store a dummy file named with the hash in a separate path in the bucket upon successfully processing a file. This way we could just check for the existence of a hash/file name within one storage call.

[salsferrazza]

The hash is available as file metadata for each GCS object - perhaps it makes sense to add this to the datashare_batch_id.

    Creation time:          Thu, 19 Apr 2018 18:50:40 GMT
    Update time:            Thu, 19 Apr 2018 18:50:40 GMT
    Storage class:          STANDARD
    Content-Length:         33685826
    Content-Type:           application/zip
    Hash (crc32c):          25iVgg==
    Hash (md5):             sEjyHtS53TO/t+wK9PFtog==
    ETag:                   CPzOweKAx9oCEAE=
    Generation:             1524163840862076
    Metageneration:         1
    ACL:                    [

[salsferrazza]

So hash could be stored in column separate from datashare_batch_id, then ingestion function can halt if a COUNT(*) WHERE datashare_hash = '<hash_of_new_file>' returns anything but 0.

[mservidio]

Problem is that using a single hash would require a join to all of the data. To minimize the joined set we could combine with a statement date or something depending on the type of data. In this case too, it may make sense to cluster on the date used.