4

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?

abaghel
  • 14,783
  • 2
  • 50
  • 66
Sumit Raghani
  • 51
  • 1
  • 1
  • 2
  • 1
    I assume you've seen that if you export a lot of data it will end up in many different files. What behavior are you expecting? Why not just export to a new directory? – Carter Shanklin Mar 09 '14 at 23:42

2 Answers2

3

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.

Mark Vickery
  • 1,927
  • 3
  • 22
  • 34
  • We are "NOT TRYING TO APPEND" the data of Emp table to an existing table in Local File System. What we are trying is to export data from a hive table (say, Emp)to an existing local file sysytem directory, without affecting the already existing contents of the directory. – Sumit Raghani Mar 09 '14 at 09:31
  • See my edit for the command to export the Emp query to a file, not append. – Mark Vickery Mar 09 '14 at 09:58
  • This is one way. Is there any other way to do the same thing? using the INSERT..DIRECTORY command without the overwrite word. – Sumit Raghani Mar 09 '14 at 10:55
  • have you tried your command "insert overwrite local directoy '/my/local/filesystem/directory/path' select * from Emp;". It should work, in your failed trials though you not implied you tried it. http://stackoverflow.com/questions/18129581/how-do-i-output-the-results-of-a-hiveql-query-to-csv – Mark Vickery Mar 09 '14 at 11:27
0

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

Lee
  • 1