Extended filesystem layout placeholders for destinations

[sultaniman]

Problem statement

There are already few requests to extend the supported variable placeholders for layout option, which has the following default value

[destination.filesystem]
layout="{table_name}/{load_id}.{file_id}.{ext}"

There are already couple of requests on GitHub 1, 2 and slack. This means users would love to have more fine grained control for layout formatting options using more placeholders.

Request #1

curr_year, curr_month, curr_day, curr_hour and curr_minute

Request #2

Partition config should support resource parameters, for example, if date (or year, month, date) is a parameter to a resource, that should be available in the partition layout.

Partition config should support resource metadata, such as object path or creation date, for example, in order to replicate the partitioning from the source to destination.

Request #3

For us we would also like to be able to partition based on date for which the job is running. Very similar to airflow's ds field

So we can have hive style partitions s3:///..../year={job_run.year}/month={job_run.month/day={job_run.day}/table/schema/...

It would also be nice to be able to pass in these kind of fields as parameters to the dlt operator on airflow.

TODO

We need to augment LoadFilesystemJob.make_destination_filename and expand the the available parameters with datetime fields for year, month, day, hour, minute, second, microsecond

Extending [SUPPORTED_PLACEHOLDERS](https://github.com/dlt-hub/dlt/blob/devel/dlt/destinations/path_utils.py#L10) with known set of variables should suffice the minimum use case needs currently it has the following values

SUPPORTED_PLACEHOLDERS = {"schema_name", "table_name", "load_id", "file_id", "ext", "curr_date"}

After the extension this will look like

SUPPORTED_PLACEHOLDERS = {"schema_name", "table_name", "load_id", "file_id", "ext", "curr_date", "year", "month", "day", "hour", "minute", "second", "microsecond"}

[rudolfix]

@Pipboyguy this and #759 are related. This is our best scope description: #555 (comment)

My take here

1. We should let users to create path names (and thus partition) on any kind of metadata they want
2. Possibly the best way would be for filesystem to accept a callback that creates a file name on top of existing format specifier
3. It could also allow people to define custom formatter where they can bind a formatter to a value or a function that will be resoled at run time
4. mind that certain order of formatter is required for replace write disposition in filesystem destination.
5. @Pipboyguy you are free to merge the tickets and create a scope + ping users that were involved there