Using COPY TO for CSV

CrateDB
1

Hi,

After looking through docs on the COPY TO/FROM it seems that the COPY TO only does json… ?

image
image1135×474 62 KB

What I’m trying to do is dump one table to CSV, so that I can specify the columns to import using COPY FROM into another table ( which has an extra generated column ).
JSON does not seem to allow for extra columns … ?
( I should note the other table is on another instance elsewhere, otherwise I’d use INSERT INTO () SELECT * FROM blah )

I’ve read the various other tutorials but most seem to assume the CSV is generated elsewhere.
I’ve tried CSV export within DBeaver, but this blows up with memory issues ( 9M+ rows in the table to export ).

Any help will be appreciated.

Many thanks
David.

3

Indeed COPY TO currently is only supported for JSON files

What do you mean by that?

cr> CREATE TABLE old_table (val integer);                                                                                                                        
CREATE OK, 1 row affected  (1.694 sec)

cr> INSERT INTO old_table (val) VALUES (1),(2),(3);                                                                                                              
INSERT OK, 3 rows affected  (0.197 sec)

cr> COPY old_table TO DIRECTORY '/Users/Sandbox/crate-5.0.0/export';                                                                                      
COPY OK, 3 rows affected  (0.027 sec)

cr> CREATE TABLE new_table (val integer, val_g GENERATED ALWAYS AS val+1);                                                                                       
CREATE OK, 1 row affected  (1.556 sec)

cr> COPY new_table FROM '/Users/Sandbox/crate-5.0.0/export/*';                                                                                            
COPY OK, 3 rows affected  (0.134 sec)

cr> SELECT * FROM new_table;                                                                                                                                     
+-----+-------+
| val | val_g |
+-----+-------+
|   1 |     2 |
|   2 |     3 |
|   3 |     4 |
+-----+-------+
SELECT 3 rows in set (0.014 sec)
1 Like
4

Yes right, COPY TO only supports JSON.
There is also a feature request at GH (export to csv · Issue #8551 · crate/crate · GitHub), but it lacks a bit the use-case or why post-processing JSON to CSV isn’t an option.

Converting the exported JSONL to CSV could be done e.g. using jq:
jq -r -s '(map(keys) | add | unique) as $cols | map(. as $row | $cols | map($row[.])) as $rows | $cols, $rows[] | @csv' <YOUR_FILE_NAME>.json

1 Like
5

Oh that’s odd - it was not doing anything on import, no matter what I tried and so I assumed that it was due to not specifying the columns and/or the table schema being different to the import.

I’ll have to try again, as it must be something else.

Thanks.

6

OK - it was a mistake from my end.

So with the COPY FROM I was specifying the files individually

tableX_1.json.gz
tableX_2.json.gz
…

which did not do anything ( so therefore I was thinking it was needing to specify columns i.e. CSV ).

So now I tried with a wildcard (*) in the and its working. Sorry!

Thanks for all the help though.

Best regards
David.

2 Likes