Bulk upload using csv file to remote MonetDB server

Question

I have a remote MonetDB server running and I want to bulk upload a csv file as it is much faster.

Based on the params in MonetDB.R, there is a csvdump=TRUE option but I don't think it works when you are trying to do this against a remote server. The server has to be local.

https://rdrr.io/github/MonetDB/monetdb-r/man/dbWriteTable.html

First, am I correct that I can't do this and if not, is there a workaround? I have a dataframe with +5M rows so it takes a long time with insert statements rather than using COPY INTO.

When I try using csvdump=TRUE against the remote server, it can't find the csv file because it is local to computer that called the dbWriteTable command.

Answer 1

I think you are right. As a workaround either use explicit COPY INTO ON CLIENT SQL statements or first use some file transfer tool to copy the file to the remote server before calling dbWriteTable.

It reads from MonetDB's documentation on COPY INTO:

FROM files ON SERVER

With ON SERVER, which is the default, the file name must be an absolute path on the system on which the database server (mserver5) is running. ...

Interestingly enough pymonetdb, the Python driver for MonetDB, uses ON CLIENT for bulk loads. From the pymonetdb's doc:

File Uploads and Downloads

Classes related to file transfer requests as used by COPY INTO ON CLIENT.

You might want to file an issue for the MonetDB R-driver project to have similar behavior as pymonetdb.