Bug 2804 - binary "export": COPY <subquery> INTO <list_of_binary_files_one_per_column>
Summary: binary "export": COPY <subquery> INTO <list_of_binary_files_one_per_column>
Status: NEW
Alias: None
Product: SQL
Classification: Unclassified
Component: all (show other bugs)
Version: -- development
Hardware: All All
: Normal enhancement
Assignee: SQL devs
URL:
Keywords: NONEEDTOTEST
Depends on:
Blocks:
 
Reported: 2011-04-14 18:23 CEST by Stefan Manegold
Modified: 2020-06-03 09:20 CEST (History)
3 users (show)



Attachments
KONTOL (deleted)
2020-06-03 03:43 CEST, THE_N4R4NT
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Stefan Manegold cwiconfidential 2011-04-14 18:23:30 CEST
For fixed-width (numerical, non-string) data, we already have the

COPY [ <int_val> RECORDS ] INTO <table_name> FROM <list_of_filenames>

aka. "attach", that uses the given (properly formatted) binary files as-is as column representations in MonetDB.

This should be matched with a

COPY <subquery> INTO <list_of_filenames>

aka. "export", that stores the columns of the <subqueries>'s result as-is in binary format into the given files.
Comment 1 Sjoerd Mullender cwiconfidential 2012-08-24 14:55:55 CEST
Jul2012-SP1 has been released.
Comment 2 Ying Zhang cwiconfidential 2012-11-27 15:28:43 CET
No test for feature request
Comment 3 Clint Cummins 2015-02-26 09:23:56 CET
Sjoerd Mullender's Comment 1 in 2012 might suggest that this feature has been implemented and released in Jul2012-SP2.
I tried it on the Windows64 Jan2014-SP2 version, and it does not appear to be available.
COPY fd562 INTO ('path-to-col1', 'path-to=col2', ...);
yields
syntax error, unexpected IDENT , expecting BINARY or INTO in:  "COPY fd562"

I wasn't entirely surprised, since I was not sure how this syntax without the BINARY keyword would handle a single column table (would it be a binary file or a CSV?).

The reason I'm interested in such an enhancement would be for a
BINARY DUMP/RESTORE .
It seems that the frequent Feature Releases may require DUMP/RESTORE for existing databases.
Doing DUMP/RESTORE in BINARY would seem to speed up this process, which would be very helpful for the large databases which MonetDB works so well on.

Even better than Stefan Manegold's proposed syntax would be an option to specify a single path to an output FOLDER.
Then users would not have to specify all the output column names and prepend the folder path to each.
E.g.

COPY BINARY subquery INTO 'path-to-folder';

or

\D 'path-to-folder'

for a binary dump of all tables in database.

Similarly, COPY BINARY INTO tablename    could use a FOLDER and default all the filenames within it to the column names:

COPY BINARY INTO tablename FROM 'path-to-folder';
Comment 4 Sjoerd Mullender cwiconfidential 2015-02-26 10:22:31 CET
(In reply to comment #3)
> Sjoerd Mullender's Comment 1 in 2012 might suggest that this feature has
> been implemented and released in Jul2012-SP2.

If it did, then that was not the purpose of the comment.  I merely changed the applicable version number of the bug report.  As in, now that Jul20120-SP2 has been released and this wasn't in there, the report applies to the next version.
Comment 6 Sjoerd Mullender cwiconfidential 2020-06-03 09:20:49 CEST
The content of attachment 679 [details] has been deleted for the following reason:

spam