Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Filename extract and insert into column values hive

Highlighted

Filename extract and insert into column values hive

New Contributor
 
1 REPLY 1

Re: Filename extract and insert into column values hive

Super Guru

@Gayathri Devi

You can use INPUT__FILE__NAME(gives all input filenames of the table) virtual column and construct your query then store the results of your query to final table.

You need to create a temp table and keep your akolp9app1a_170905_0000.txt file in that table location.

Then use

hive> select INPUT__FILE__NAME from table; //this statement results your akolp9app1a_170905_0000.txt filename
+---------------------------------------------------------------------------------+--+
|                                input__file__name                                |
+---------------------------------------------------------------------------------+--+
| /apps/hive/warehouse/sales/akolp9app1a_170905_0000.txt  			|
+---------------------------------------------------------------------------------+--+

So then you can use all your string functions like substring on the input_file_name filed and keep your hostname,date fileds extracted from the input__file__name field.

hive> select substring(INPUT__FILE__NAME,20,30) hostname,substring(INPUT__FILE__NAME,40,50) `date` from table;

Then you can have final table that you can insert the above select statement hostname,date values.

hive> insert into finaltable select substring(INPUT__FILE__NAME,20,30) hostname,substring(INPUT__FILE__NAME,40,50) `date` from table;

For more references:-

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+VirtualColumns

Don't have an account?
Coming from Hortonworks? Activate your account here