[SOLVED] Not able to upload data in hive table

Not able to upload data in hive table

I have an hive table like below,

hive> describe eslg_transaction_01;
OK

    a1 string
    a2 date
    a3 string
    a4 string
    a5 string
    a6 bigint
    a7 double
    a8 double
    a9 double
    a10 bigint
    a11 bigint
    a12 bigint
    a13 bigint
    a14 bigint
    a15 bigint
    a16 bigint
    a17 string
    a18 string

    Time taken: 0.723 seconds, Fetched: 18 row(s)

I am trying to upload data into this table using,

hive> LOAD DATA INPATH '/user/hadoop/data/2502.txt' INTO TABLE eslg_transaction_01;

I am getting the following error:

FAILED: SemanticException Line 1:17 Invalid path ''/user/hadoop/data/2502.txt'': No files matching path hdfs://sandbox-hdp.hortonworks.com:8020/user/data/2502.txt

My data is present in the location and I am able to see it:

[root@sandbox-hdp ~]#  hadoop fs -cat /user/hadoop/data/2502.txt | head -5

-200879548|2018-02-18|1485|384672|1787329|1|8.69|0|50|0|0|0|1|0|0|0||NULL
-192188296|2018-02-07|508|321131|9713410|1|0.68|0|30|0|0|0|2|0|0|1|1|2018_303
-198424071|2018-02-15|93|404120|97223|1|2|0.89|0|0|0|1|0|0|0|1|1|2018_4
-185483553|2018-01-29|131|336347|1070990|1|1.3|0.88|0|0|0|0|0|1|0|1|1|2018_3
-205064252|2018-02-23|516|21118|2610945|1|0.89|0.6|0|0|0|0|0|1|0|1|1|2018_5

can somebody help. I am stuck here. I am new to hadoop/hive

Solution

You don't really need to use LOAD DATA if you instead define an EXTERNAL TABLE with a LOCATION pointing at the original HDFS directory.

CREATE EXTERNAL TABLE IF NOT EXISTS 
eslg_transaction_01
.... 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '|'
LOCATION '/user/hadoop/data/'

Then any file you place into that data directory will be immediately queryable by Hive