shell - Filename extract and insert into column values hive



I have a file called : akolp9app1a_170905_0000.txt

I need to split the values

hostname= akolp9app1a

date=170905 (convert into proper data format)

Now create a table in hive with 2 columns hostname and date and insert this values into the table.

any suggestion


1 Answer: 

You can use virtual columns, for example INPUT__FILE__NAME. It gives the input file's name.

Then you can use split (or) substring (or) regexp_extract string functions on the input__file__name field and create hostname,date values.

Example:- the below select query gives date field value as 170905, like this way build your query using string functions to extract hostname

 hive> select split(INPUT__FILE__NAME,'[\_]')[1] `date` from tablename;

Store them to separate table by using insert statement.