Inserting local csv to a Hive table from Qubole

Question

I have a csv on my local machine, and I access Hive through Qubole web console. I am trying to upload the csv as a new table, but couldn't figure out. I have tried the following:

LOAD DATA LOCAL INPATH <path> INTO TABLE <table>;

I get the error saying No files matching path file

I am guessing that the csv has to be in some remote server where hive is actually running, and not on my local machine. The solutions I saw doesn't explain how to handle this issue. Can someone help me out reg. this?

Answer 1

Qubole allows you to define hive external/managed tables on the data sitting on your cloud storage ( s3 or azure storage ) - so LOAD from your local box wont work. you will have to upload this on your cloud storage and then define an external table against it -

CREATE External TABLE orc1ext(
  `itinid` string, itinid1 string)
stored as ORC
LOCATION
  's3n://mybucket/def.us.qubole.com/warehouse/testing.db/orc1';

INSERT INTO TABLE orc1ext SELECT itinid, itinid 
FROM default.default_qubole_airline_origin_destination LIMIT 5;

Answer 2

First, create a table on hive using the field names present in your csv file.syntax which you are using seems correct. Use below syntax for creating table

 CREATE TABLE foobar(key string, stats map<string, bigint>)
 ROW FORMAT DELIMITED
 FIELDS TERMINATED BY ','
 COLLECTION ITEMS TERMINATED BY '|'
 MAP KEYS TERMINATED BY ':' ;

and then load data using below format,then mention path name correctly

LOAD DATA LOCAL INPATH '/yourfilepath/foobar.csv' INTO TABLE foobar;