cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
cancel
Showing results for 
Search instead for 
Did you mean: 

Reference file name when using COPY INTO?

cnjrules
New Contributor III

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')

1 ACCEPTED SOLUTION

Accepted Solutions

cnjrules
New Contributor III
3 REPLIES 3

Anonymous
Not applicable

@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
New Contributor III

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
New Contributor III

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

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

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.