Retrieve only file name into a table in hive

Question

I need to retrieve the filename.txt from a linux path and insert filename into a table column in hive. Is it possible to retrieve only file name from a path and insert it into a hive table using virtual columns? please advice!

e.g. of path /home/usr/path/filename.txt and insert filename into a table. create table t( name string);

Thanks!

from where you need to get filename? add more info: filename with path (as an example) — Ronak Patel, Jul 25 '16 at 19:27
Possible duplicate of: http://stackoverflow.com/questions/965053/extract-filename-and-extension-in-bash — leftjoin, Jul 25 '16 at 20:04
Do not understand your scenario completely. If you just want to extract filename from full path, please see my previous comment. — leftjoin, Jul 25 '16 at 20:06
Thanks! can you please let me know how to insert file name into hive table via shell scripting? I'm new to linux. — Holmes, Jul 25 '16 at 20:13

score 2 · Accepted Answer · answered Jul 25 '16 at 20:10

if you want to run against HDFS - command - awk -F "/" '{print $NF}' will you just file name.

[cloudera@quickstart ~]$ hadoop fs -ls /user/cloudera/departments|awk -F "/" '{print $NF}'|egrep -v 'Found|_SUCCESS'
part-m-00000
part-m-00001
[cloudera@quickstart ~]$

if you want to run against local file system then - command - ls -1 will give you file name. you can also use awk -F "/" '{print $NF}'

you can create shell script as: (uncomment hive statements)

#!/bin/sh

files=`hadoop fs -ls /user/cloudera/departments|awk -F "/" '{print $NF}'|egrep -v 'Found|_SUCCESS'`

for file in $files
 do
   #hive -e "insert into table t(name) values (\"$file\");"
   echo "insert into table t(name) values (\"$file\");"
done

should instert in hive table:

[cloudera@quickstart ~]$ ./test.sh 
insert into table t(name) values ("part-m-00000");
insert into table t(name) values ("part-m-00001");

leftjoin · Answer 2 · 2019-04-30T06:38:12.473

For example you have a full file name in variable $filename:

 #!/bin/bash
        filename=$(basename "$fullfile")

        #pass variable to the hive script:
        hive -hiveconf filename=$filename -f your_script_name.hql

Inside the script:

        insert into your_table
        select some columns,  '${hiveconf:filename}' as filename  --use variable
        from some table... ;
        or just insert values...

Or just simply do:

hive -e "insert into t values '$filename'"

Retrieve only file name into a table in hive

2 Answers2