[EPIC] A collection of support for metadata columns in ListingTable

[alamb]

Other systems support "metadata" columns when querying datasources. These metadata columns do not exist in the underlying data source but instead are related to the source

Common examples:

• Row number
• File name
• Row Group Number (parquet)

DataBricks / Spark

It appears DataBricks / spark represents this concept as a struct column _metadata column with multiple fields
https://docs.databricks.com/aws/en/ingestion/file-metadata-column

SELECT
    *
    ,_metadata
    ,_metadata.file_path
    ,_metadata.file_name
    ,_metadata.file_modification_time
FROM
    json.`/path/to/table/data`

It looks like maybe spark/databricks used to support the input_file_name() function, but has moved to _metadata: https://pawankumarshukla1979.medium.com/tips-use-metadata-instead-of-input-file-name-function-in-databricks-runtime-10-5-and-above-b32766b0296b

DuckDB

DuckDB seems to model this as additional parameters to the read_parquet function, specifically file_name and file_row_number:
https://duckdb.org/docs/stable/data/parquet/overview#parameters

Per @adriangb last year:

D select filename, sum(row_count) as row_count from read_parquet('/Users/adriangb/Downloads/data2/**/*_stats.parquet', filename=true) group by filename order by row_count desc limit 10;
Binder Error:
Option filename adds column "filename", but a column with this name is also in the file. Try setting a different name: filename='<filename column name>'

Related tickets for adding similar metadata features to DataFusion:

• metadata column support #13975
• Support metadata columns (location, size, last_modified) in ListingTableProvider #15173
• Add input_file_name built-in function #6051
• Add with_virtual_columns to ParquetSource for reading virtual columns #20132
• Return the "position" of rows in parquet files after performing a query. #13261
• Support extended partition cols for listing table. #18482

[AdamGS]

Vortex is also interested in this (mostly row numbers for now), would be happy to help here, even if just be testing it in our FileSource.

(also realizing I've been subscribed to so many related issues, happy to see the effort in one place!)

[alamb]

I think the first key decision is how to model this method / what is the API

I think @ethan-tyler / @adriangb were leaning towards some built in functions like

select filename() from my_table

And then rewriting the function reference in the datasource

One potential downside of doing it this way is that it is not clear how it would work for a multi-table query -- for example

SELECT ..., filename()    -- <---- Which relation does `filename()` get attached to?
FROM a JOIN b USING (id)

[adriangb]

I think the first key decision is how to model this method / what is the API

I think @ethan-tyler / @adriangb were leaning towards some built in functions like

select filename() from my_table

And then rewriting the function reference in the datasource

One potential downside of doing it this way is that it is not clear how it would work for a multi-table query -- for example

SELECT ..., filename() -- <---- Which relation does filename() get attached to?
FROM a JOIN b USING (id)

That's an interesting case. I think in general "which table did this row come from" is difficult to mix with joins.

I'm guessing in systems where this is a special column this is not a problem:

SELECT ..., a.filename
FROM a JOIN b USING (id)

Maybe something like filename(a.id) (really just any reference to a column in either table) would force the expression to be pushed down into that side and disambiguate by establishing a dependence of the expressions on that table.
And if you don't specify a table and the rows are from a join you get back null.

[alamb]

Maybe something like filename(a.id) (really just any reference to a column in either table) would force the expression to be

It looks to me like what spark / databricks did was to add a special column to each table named _metadata

And then that _metadata column has various fields like filename, row, etc

This might fit nicely into the expression pushdown / rewrite mechanism that you are working on 🤔

[adriangb]

Maybe something like filename(a.id) (really just any reference to a column in either table) would force the expression to be

It looks to me like what spark / databricks did was to add a special column to each table named _metadata

And then that _metadata column has various fields like filename, row, etc

This might fit nicely into the expression pushdown / rewrite mechanism that you are working on 🤔

The idea of a single struct containing all of the info is interesting.

If we make it a column we could use the rewrite stuff at the file level to rewrite any column with this particular name into the right data for that file but adding a column opens up a whole can of worms: #14362, #14057

[lukekim]

FWIW, we implemented a version of this in ListingTable spiceai#74