CODEWITHSUNDEEP
hadoophive
Sumit Raghani
Sumit Raghani

Reputation: 51

Exporting Data from Hive table to Local Machine File System

Using following command:

insert overwrite local directory '/my/local/filesystem/directory/path'
select * from Emp;

overwrites the entire already existing data in /my/local/filesystem/directory/path with the data of Emp.

What i want is to just copy the data of Emp to /my/loca/filesystem/directory/path and not overwrite, how to do that?

Following are my failed trials:

hive> insert into local directory '/home/cloudera/Desktop/Sumit' select * from appdata;

FAILED: ParseException line 1:12 mismatched input 'local' expecting TABLE near 'into' in insert clause

hive> insert local directory '/home/cloudera/Desktop/Sumit' select * from appdata;

FAILED: ParseException line 1:0 cannot recognize input near 'insert' 'local' 'directory' in insert clause

Can u please tell me how can I get this solved?

Upvotes: 4

Views: 27349

Answers (2)

Lee
Lee

Reputation: 1

https://cwiki.apache.org/confluence/display/Hive/GettingStarted#GettingStarted-SQLOperations

When using 'LOCAL', 'OVERWRITE' is also needed in your hql.

For example: INSERT OVERWRITE LOCAL DIRECTORY '/tmp/out' SELECT * FROM test

Upvotes: 0

Mark Vickery
Mark Vickery

Reputation: 1955

To appened to a hive table you need to use INSERT INTO:

INSERT INTO will append to the table or partition keeping the existing data in tact. (Note: INSERT INTO syntax is only available starting in version 0.8)

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML#LanguageManualDML-InsertingdataintoHiveTablesfromqueries

But you can't use this to append to an existing local file so another option is to use a bash command.

If you have a file called 'export.hql' and in that file your code is:

select * from Emp;

Then your bash command can be:

hive -f 'export.hql' >> localfile.txt

The -f command executes the hive file and the >> append pipes the results to the text file.

EDIT:

The command:

hive -f 'export.hql' > localfile.txt

Will save the hive query to a new file, not append.

Upvotes: 3

Related Questions