Support Questions

Find answers, ask questions, and share your expertise

Filename extract and insert into column values hive

 
1 REPLY 1

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

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.