Reference file name when using COPY INTO?

[cnjrules]

When using the COPY INTO statement is it possible to reference the current file name in the select staement? A generic example is shown below, hoping I can log the file name in the target table.

COPY INTO my_table

FROM (SELECT key, index, textData, 'constant_value', ?FILE_NAME?

FROM 'abfss://container@storageAccount.dfs.core.windows.net/base/path')

FILEFORMAT = CSV

PATTERN = 'folder1/file_[a-g].csv'

FORMAT_OPTIONS('header' = 'true')

[Anonymous]

@Michael L​ 

Yes, it is possible to reference the current file name in the SELECT statement when using the COPY INTO statement. The placeholder for the current file name is @1. You can use this placeholder in your SELECT statement to reference the current file name. Here is an example of how to use it:

COPY INTO my_table
FROM (
  SELECT key, index, textData, 'constant_value', @1 AS file_name
  FROM 'abfss://container@storageAccount.dfs.core.windows.net/base/path'
)
FILEFORMAT = CSV
PATTERN = 'folder1/file_[a-g].csv'
FORMAT_OPTIONS('header' = 'true')

In this example, the @1 placeholder is used to reference the current file name and it is aliased as file_name in the SELECT statement. This value will be inserted into the my_table target table as a column.

[cnjrules]

Thanks for the response. I tried that but received a PARSE_SYNTAX_ERROR on the @1 reference. Any chance you know of any documentation on that placeholder?

[cnjrules · Accepted solution]

Found the info I was looking for on the page below:

https://docs.databricks.com/ingestion/file-metadata-column.html