如何在Databricks外部表中获取行来源文件信息及写入日期？

可以实现，下面是具体操作方法：

一、查询时直接获取源文件路径 #

Databricks内置input_file_name()函数，能直接返回当前行所属的完整S3文件路径，查询时追加该字段即可：

SELECT *, input_file_name() AS source_file_path
FROM your_external_table_name

如果只需要文件名而非完整路径，可结合正则提取：

SELECT *, 
       regexp_extract(input_file_name(), '.*/(.*)', 1) AS source_file_name
FROM your_external_table_name

二、提取文件写入/修改日期 #

要获取S3文件的写入时间（即对象的LastModified时间），有两种实用方式：

方法1：通过元数据查询关联 #

先查询外部表的文件元数据，获取每个文件的修改时间：

DESCRIBE DETAIL your_external_table_name

该查询的files字段会返回所有源文件的详细信息，包括path（文件路径）和modificationTime（文件最后修改时间）。

再将元数据与原表关联，匹配每行对应的文件时间：

WITH file_metadata AS (
  SELECT explode(files) AS file_info
  FROM DESCRIBE DETAIL your_external_table_name
)
SELECT t.*, f.file_info.modificationTime AS file_write_time
FROM your_external_table_name t
JOIN file_metadata f
ON input_file_name() = f.file_info.path

方法2：创建表时预定义元数据列（推荐） #

如果希望每次查询都能直接拿到这些信息，创建表时可定义生成列自动获取：

CREATE EXTERNAL TABLE your_external_table_name (
  -- 原表字段，根据实际情况修改
  col1 STRING,
  col2 INT,
  -- 自动生成源文件路径列
  source_file_path STRING GENERATED ALWAYS AS (input_file_name()),
  -- 自动生成文件修改时间列（需Databricks Runtime 10.0+）
  file_write_time TIMESTAMP GENERATED ALWAYS AS (
    to_timestamp(get_json_object(get_file_status(input_file_name()), '$.modificationTime'))
  )
)
LOCATION 's3://your-bucket/path/to/folder'
USING CSV
OPTIONS (header 'true', inferSchema 'true')

get_file_status()会返回文件的元数据JSON，从中提取修改时间并转为时间戳格式。

三、筛选最新写入的行 #

拿到file_write_time后，可轻松筛选最新数据：

-- 获取最新批次的所有行
SELECT *
FROM your_external_table_name
WHERE file_write_time = (SELECT MAX(file_write_time) FROM your_external_table_name)

-- 获取最近7天写入的数据
SELECT *
FROM your_external_table_name
WHERE file_write_time >= date_sub(current_date(), 7)

内容的提问来源于stack exchange，提问作者Chris Hunt