Exporting data from duckdb query composing filename with strftime

Question

Trying to export a bunch of tables as csv from duckdb database I get an error on COPY when composing the filename with the timestamp this way:

COPY (SELECT * FROM main.mytable) TO CONCAT('C:\\Users\\rdata\\mytable_', strftime(NOW(), '%Y%m%d%H%M'), '.csv') (FORMAT 'csv', HEADER, DELIMITER '|')

Error Caused by: java.sql.SQLException: Parser Error: syntax error at or near "CONCAT"

Tried to compose this way too (to no avail):

COPY (SELECT * FROM main.mytable) TO 'C:\\Users\\rdata\\mytable_' || strftime(NOW(), '%Y%m%d%H%M') || '.csv' (FORMAT 'csv', HEADER, DELIMITER '|')

hawkfish · Accepted Answer · 2024-04-10 16:18:13Z

1

If you can't generate the string outside SQL (probably a better idea), you could try using hive partitioning and date formatting to generate something close:

COPY (
    SELECT *, now() as mytable 
    FROM main.mytable
) 
TO 'C:\\Users\\rdata\\') (
    FORMAT 'csv', 
    HEADER, 
    DELIMITER '|', 
    PARTITION_BY("mytable"),
    FILENAME_PATTERN 'mytable.csv'
    DATEFORMAT '%Y%m%d%H%M'
)

This should create a directory called mytable=<now> containing mytable.csv. Play around with it to see what works best for your use case.

answered Apr 10, 2024 at 16:18

hawkfish

3811 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Exporting data from duckdb query composing filename with strftime

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related