I'm very new to Athena and I'm having a little bit of hard time understanding how partitioning works and if it can work for me.
I have files in S3 in the following format:
path/to/files/YYYYmmDDTHHMMSSZ_<id>.json
For example:
path/to/files/20191208T130435Z_265901.json
path/to/files/20191212T132019Z_266406.json
path/to/files/20191216T102909Z_266975.json
path/to/files/20191226T095326Z_268789.json
path/to/files/20191226T103749Z_268798.json
path/to/files/20191226T110113Z_268802.json
path/to/files/20191229T182902Z_269391.json
path/to/files/20191230T041315Z_269481.json
path/to/files/20200101T200007Z_269935.json
...
I understand that this does not follow the guidelines for correct hive-style partitioning, but I'm not the owner of the data, so this cannot change.
My question is: Is there any way to specify a monthly partition to such a structure?
I'm reading this guide but I'm not sure if and how it applies to my case.
Thanks a lot in advance.
CodePudding user response：
You can pre-process your data, and rename them, to have proper directory structure.
For ex:
- Object uploaded
- Send S3 PUT event to lambda (setup it carefully to not interfere with step 3)
- Copy delete this object to correct directory structure.
CodePudding user response：
So, after reading the documentation more carefully, I think the answer is "no". If I had a delimiter ("/") after "YYYYmmDD", then this would be possible by partition projection, but since there is not, this cannot happen either.
It seems there is no other way as the workaround @Alex has suggested.