question Add filename to import in Hive in Archives of Support Questions (Read Only)

Add filename to import in Hive

Lucille — Tue, 21 Apr 2026 13:51:26 GMT

Hi guys,

I am new to Cloudera and have only used Impala and Hive through Hue thus far.

I have a folder of CSVs that I imported, but would like to add the filename as a column to each row that I import.

How do I do this?

I came across the function

INPUT__FILE__NAME

but I don't see it actually doing anything.

My import is very easy - hoping you can help me?

create external table hive_magnum
(
col1 string,
col2 string,
col3 string
)
row format delimited fields terminated by ","
location '/user/xxxxxx/magnum';

Re: Add filename to import in Hive

bbreak — Fri, 17 Jun 2016 21:57:39 GMT

Hi Lucille,

For the example you provided, you could get the file names with a query like this:

SELECT hive_magnum.col1, hive_magnum.col2, hive_magnum.col3, hive_magnum.INPUT__FILE__NAME
FROM
hive_magnum;

It will actually provide the full HDFS location, which includes the file name.

I hope this is helpful.

Re: Add filename to import in Hive

Lucille — Mon, 20 Jun 2016 08:58:19 GMT

thank you so much - this worked 🙂

I just have one more questions.. to get this into the another table - it doesn't seem to accept this.. what is wrong with it?

It also did not like the <pre> bit... If I take it out, it runs..

create table tester
(
SELECT hive_magnum.col1, hive_magnum.col2, hive_magnum.col3, hive_magnum.INPUT__FILE__NAME as file_name FROM hive_magnum)

Re: Add filename to import in Hive

bbreak — Mon, 20 Jun 2016 13:59:55 GMT

The

 bit was an attempt to format the post. It wasn't supposed to be
part of the query, sorry about that.

Try adding "as" after "create table tester" and before the nested select
query.