File metadata column
You can get metadata information for input files with the _metadata
column. The _metadata
column is a hidden column, and is available for all input file formats. To include the _metadata
column in the returned DataFrame, you must explicitly reference it in your query.
If the data source contains a column named _metadata
, queries return the column from the data source, and not the file metadata.
Warning
New fields might be added to the _metadata
column in future releases. To prevent schema evolution errors if the _metadata
column is updated, Databricks recommends selecting specific fields from the column in your queries. See examples.
Supported metadata
The _metadata
column is a STRUCT
containing the following fields:
Name |
Type |
Description |
Example |
Minimum Databricks Runtime release |
---|---|---|---|---|
file_path |
|
File path of the input file. |
|
10.5 |
file_name |
|
Name of the input file along with its extension. |
|
10.5 |
file_size |
|
Length of the input file, in bytes. |
628 |
10.5 |
file_modification_time |
|
Last modification timestamp of the input file. |
|
10.5 |
file_block_start |
|
Start offset of the block being read, in bytes. |
0 |
13.0 |
file_block_length |
|
Length of the block being read, in bytes. |
628 |
13.0 |
Examples
Use in a basic file-based data source reader
df = spark.read \
.format("csv") \
.schema(schema) \
.load("dbfs:/tmp/*") \
.select("*", "_metadata")
display(df)
'''
Result:
+---------+-----+----------------------------------------------------+
| name | age | _metadata |
+=========+=====+====================================================+
| | | { |
| | | "file_path": "dbfs:/tmp/f0.csv", |
| Debbie | 18 | "file_name": "f0.csv", |
| | | "file_size": 12, |
| | | "file_block_start": 0, |
| | | "file_block_length": 12, |
| | | "file_modification_time": "2021-07-02 01:05:21" |
| | | } |
+---------+-----+----------------------------------------------------+
| | | { |
| | | "file_path": "dbfs:/tmp/f1.csv", |
| Frank | 24 | "file_name": "f1.csv", |
| | | "file_size": 12, |
| | | "file_block_start": 0, |
| | | "file_block_length": 12, |
| | | "file_modification_time": "2021-12-20 02:06:21" |
| | | } |
+---------+-----+----------------------------------------------------+
'''
val df = spark.read
.format("csv")
.schema(schema)
.load("dbfs:/tmp/*")
.select("*", "_metadata")
display(df_population)
/* Result:
+---------+-----+----------------------------------------------------+
| name | age | _metadata |
+=========+=====+====================================================+
| | | { |
| | | "file_path": "dbfs:/tmp/f0.csv", |
| Debbie | 18 | "file_name": "f0.csv", |
| | | "file_size": 12, |
| | | "file_block_start": 0, |
| | | "file_block_length": 12, |
| | | "file_modification_time": "2021-07-02 01:05:21" |
| | | } |
+---------+-----+----------------------------------------------------+
| | | { |
| | | "file_path": "dbfs:/tmp/f1.csv", |
| Frank | 24 | "file_name": "f1.csv", |
| | | "file_size": 10, |
| | | "file_block_start": 0, |
| | | "file_block_length": 12, |
| | | "file_modification_time": "2021-12-20 02:06:21" |
| | | } |
+---------+-----+----------------------------------------------------+
*/
Select specific fields
spark.read \
.format("csv") \
.schema(schema) \
.load("dbfs:/tmp/*") \
.select("_metadata.file_name", "_metadata.file_size")
spark.read
.format("csv")
.schema(schema)
.load("dbfs:/tmp/*")
.select("_metadata.file_name", "_metadata.file_size")
Use in filters
spark.read \
.format("csv") \
.schema(schema) \
.load("dbfs:/tmp/*") \
.select("*") \
.filter(col("_metadata.file_name") == lit("test.csv"))
spark.read
.format("csv")
.schema(schema)
.load("dbfs:/tmp/*")
.select("*")
.filter(col("_metadata.file_name") === lit("test.csv"))
Use in COPY INTO
COPY INTO my_delta_table
FROM (
SELECT *, _metadata FROM 'gs://my-bucket/csvData'
)
FILEFORMAT = CSV
Use in Auto Loader
Note
When writing the _metadata
column, we rename it to source_metadata
. Writing it as _metadata
would make it impossible to access the metadata column in the target table, because if the data source contains a column named _metadata
, queries will return the column from the data source, and not the file metadata.
spark.readStream \
.format("cloudFiles") \
.option("cloudFiles.format", "csv") \
.schema(schema) \
.load("gs://my-bucket/csvData") \
.selectExpr("*", "_metadata as source_metadata") \
.writeStream \
.option("checkpointLocation", checkpointLocation) \
.start(targetTable)
spark.readStream
.format("cloudFiles")
.option("cloudFiles.format", "csv")
.schema(schema)
.load("gs://my-bucket/csvData")
.selectExpr("*", "_metadata as source_metadata")
.writeStream
.option("checkpointLocation", checkpointLocation)
.start(targetTable)