AWS Athena: partition table for S3 bucket with non-standard file structure

Question

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.

Answer 1

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.