CODEWITHSUNDEEP
sqlhadoophivehdfs
Sotark
Sotark

Reputation: 196

How to load a file from desktop into Hive

I am using an internal HIVE terminal console we use to interface with HIVE. I have a csv with a list of account numbers. I need to pull data from those account numbers form our tables in our database. Normally I would just say where acct_num = XXXXXX but I have a list of 800 that I need to pull for. I have tried creating a table and then loading the data using the infile but can not seem to get the hang of it. This file is on my desktop but I have used winscp to move the csv file to my "directory" where I create my tables to.

This is the code I am using. the account number is 15 digits and all numerical. I am not sure if saving it as a .csv messes with the formatting of the number, but I have tried using create table as sting and as int.

drop table acorn_data.cj_test_accounts_load;
create table acorn_data.cj_test_accounts_load
(acct_num int);
load data inpath 
'/axp/buanalytics/csgsn/dev/Akhilesh/acorn_data/Test_accounts.csv' 
into table acorn_data.cj_test_accounts_load

I would like to if possible load this file into at least a temporary table so I can join it to the tables in our database.

Upvotes: 1

Views: 1196

Answers (2)

Georgina Skibinski
Georgina Skibinski

Reputation: 13377

Depends where this "desktop" is and which exactly web tool you are using (hue? - I think you can't).

Then you have 2 options for loading data into hive from the file:

(1) Local - from the unix box on which hdfs is located (most likely not your "desktop")

(2) Not-Local - from hdfs (you can e.g. interact with webhdf - to dump file directly there: https://hadoop.apache.org/docs/r1.0.4/webhdfs.html, or do hadoop fs -put from the mentioned unix box)

Documentation REF: https://cwiki.apache.org/confluence/display/hive/languagemanual+dml#LanguageManualDML-Loadingfilesintotables

Upvotes: 1

dassum
dassum

Reputation: 5093

try the below code

create table acorn_data.cj_test_accounts_load
(acct_num int) row format delimited fields terminated by ',';

LOAD DATA LOCAL INPATH '/cuddle/prod/sales.csv' INTO TABLE acorn_data.cj_test_accounts_load;

Upvotes: 0

Related Questions