CODEWITHSUNDEEP
hive
Surender Raja
Surender Raja

Reputation: 3599

Hive query insertion into other directories

I have one table called balance in warehouse directory that has following data.

Surender,HDFC,60000,CTS
Raja,AXIS,80000,TCS
Raj,HDFC,70000,TCS
Kumar,AXIS,70000,CTS
Remya,AXIS,40000,CTS
Arun,SBI,30000,TCS

I created a Internal table called balance and loaded the above file into balance table using.

LOAD data local inpath '/home/cloudera/bal.txt' into table balance

Now I just wanted to have all those rows in balance table into a HDFS directory.hence I used the below query.

Insert overwrite directory '/user/cloudera/surenhive' select * from balance;

when I run this query all the data also loaded into above mentioned directory in HDFS. If i navigate to /user/cloudera/surenhive then i can see the data ,but there are some junk characters between data . Why is it junk characters appearing? How to remove those.

but the below query gives me result without any issues.

Insert overwrite local  directory '/home/cloudera/surenhive' select * from balance;

if I load the file from local and store the output into HDFS directory creates any problem for that junk characters.

Upvotes: 1

Views: 597

Answers (1)

Joe K
Joe K

Reputation: 18424

First of all, if you've loaded the data into a hive table, then it is already in HDFS. Do "describe formatted balance" and you will see the hdfs location of the hive table; the files are there.

But to answer your question more specifically, the default delimiter that hive uses is ^A. That's probably the You can change that by specifying a different delimiter when you do the insert:

insert overwrite directory '/user/cloudera/surenhive'
row format delimited fields terminated by ','
select * from balance;

Alternatively, since it seems you're using an older version of Hive, you could do a "create-table-as-select" with the correct file format, then make the table external and drop it. This will leave you with just the files on hdfs:

create table tmp
row format delimited fields terminated by ','
location '/user/cloudera/surenhive'
as select * from balance;

alter table tmp set tblproperties('EXTERNAL'='TRUE');

drop table tmp;

Upvotes: 1

Related Questions