Thoughts around new usage "modes" (?)

[aaelony]

Thoughts around new usage "modes" (?)

[aaelony]

Current thoughts on a new specification for manifest and config files to support the two modes of operation above. This is a spec before I code this up (the code in my repo doesn't necessarily support this yet).

Modes of Operation

---

Normal Mode

• Watches S3 buckets with a Manifest file that match the key pattern and data pattern.
• Performs upsert on new data when found.
• Deletes S3 files from bucket that were upserted into Redshift.
• Keeps watching the S3 bucket as per the Manifest file in the bucket.

Example config file (Normal mode)

{:s3 {:credentials   {:access-key "***"
                      :secret-key "***"}
      :bucket        "your-bucket"
      :key-pattern   ".*"
      :poll-interval {:seconds 30}
      :server-side-encryption "AES256"    ;; optional
      }
 :telemetry {:reporters [uswitch.blueshift.telemetry/log-metrics-reporter]}
 :redshift-connections [{:dw1 {:jdbc-url "jdbc:postgresql://foobar...."}}
                        {:dw2 {:jdbc-url "jdbc:postgresql://blahblah.fake.com..."}}
                        ]
 }

Example manifest.edn file (Normal mode)

{:table "mydata_fact"
 :pk-columns ["id" "blah"]
 :columns ["id" "blah" "timestamp" ]
 :database :dw1    ;; must match the config to resolve the jdbc-url
 :options      ["DELIMITER '\\t'" "IGNOREHEADER 1" "GZIP" "TRIMBLANKS" "TRUNCATECOLUMNS"]
 :data-pattern ".*blah.*.gz$"
 :keep-data-pattern-files-on-import false
 :keep-manifest-upon-import true
 }

---

Alternate Mode

• Watches S3 buckets with a Manifest file that match the key pattern and data pattern.
• Performs upsert on new data when found.
• Does not delete S3 file data.
• If the data loads successfully, deletes the Blueshift manifest file in the S3 bucket to stop watching the bucket.

Example config file (Alternate mode)

{:s3 {:credentials   {:access-key "***"
                      :secret-key "***"}
      :bucket        "your-bucket"
      :key-pattern   ".*"
      :poll-interval {:seconds 30}
      :server-side-encryption "AES256"    ;; optional
      }
 :telemetry {:reporters [uswitch.blueshift.telemetry/log-metrics-reporter]}
 :redshift-connections [{:dw1 {:jdbc-url "jdbc:postgresql://foobar...."}}
                        {:dw2 {:jdbc-url "jdbc:postgresql://blahblah.fake.com..."}}
                        ]
 }

Example manifest.edn file (Alternate mode)

{:table "mydata_fact"
 :pk-columns ["id" "blah"]
 :columns ["id" "blah" "timestamp" ]
 :database :dw1    ;; must match the config to resolve the jdbc-url
 :options      ["DELIMITER '\\t'" "IGNOREHEADER 1" "GZIP" "TRIMBLANKS" "TRUNCATECOLUMNS"]
 :data-pattern ".*blah.*.gz$"
 :keep-data-pattern-files-on-import true
 :keep-manifest-upon-import false
 }

Would be great to get uswitch thoughts on this.
-A

[nullpointerexcept]

Did you implement this? I don't see it in your fork.

[aaelony]

Hello, yes I have this implemented and I've used it for at least a year. I will definitely make it available once I get the chance to look into it (it's been a while) :)

[aaelony]

update: found the code, will carve out time to create a branch (hopefully today)...

[aaelony]

Okay, it's merged to master branch now. Lmk how it goes... ;)

[nullpointerexcept]

Thank you! Have you looked into a mode that doesn't upsert?
If the data doesn't have a primary key, I'd like Blueshift to just do a simple insert.