Virtual column

You can include a virtual column in a standard SELECT statement, select INPUT__FILE__NAME from <tablename>, to retrieve the data file name that stores the actual row in a table.

List of use cases

You can use this virtual column INPUT__FILE__NAME in multiple ways.

To view the file names corresponding to each record.
To extract the metadata using virtual columns if external source files ingested to HDFS contain metadata like a timestamp or source system identifier, and if you prefer to use it in subsequent operations.
To extract the timestamp of the data derived from the fully qualified file name.
To forward the technical timeline in a data warehouse scenario (for example, the "LoadDateTS" in a hybrid Data Vault architecture).

Supported operations

You can perform ceratin operations on this virtual column. The following list contains some of the supported operations:

Invoke “Functions”
Filter rows
"GROUP BY" operation

Example

This example provides the file name corresponding to each record.

select INPUT__FILE__NAME, id, name from users where dt='20140210' group by
          INPUT__FILE__NAME;

Result

The above query returns results similar to the following

hdfs://localhost.localdomain:8020/user/hive/warehouse/users/users1.txt 2 user2

hdfs://localhost.localdomain:8020/user/hive/warehouse/users/users2.txt 42 john.doe

You can use the supported string functions to extract information from the URL.