how to get input file name as column within hive query

Question

I have a hive external tables that mapped to some directory. This directory includes a several files.

I want to run query like find file name where there is a user "abc"

 select file_name , usr from usrs_tables where usr = "abc" 

But of course the data doesn't includes file name inside.

In MapReduce I can do it by

FileSplit fileSplit = (FileSplit)context.getInputSplit(); String filename = fileSplit.getPath().getName(); System.out.println("File name "+filename); System.out.println("Directory and File name"+fileSplit.getPath().toString()); 

How can I do it in Hive?

Answer 1

Yes, you can retrieve the file the record was found in using the virtual column named INPUT__FILE__NAME, for example:

select INPUT__FILE__NAME, id, name from users where ...; 

yields something like:

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 

If necessary, use the provided string functions to trim the host and directories from the uri.

You can find the documentation on virtual columns here: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+VirtualColumns