monetdb bulk copy | bulk copy with python

Am facing issue with file loading for bulk import in monetdb table. provided below are details for re creation of issue:

I have installed monetdb version 11.19.9 and python client for monetdb is 11.19.9 too.

Created dbform and it's working fine, I have connected to monetdb shell and through python api.

Now am trying to import dumped CSV file, content of the csv file are presented below:

44444444|ALI
55555555|JAFFRI

Table schema is defined as: * "Table already have 50 million rows, while csv have only two rows"

CREATE TABLE "voc"."test" (
    "id"   INTEGER,
    "data" VARCHAR(30)
);

username and database name both are "voc"

command used to import file is as listed below.

root@monetdb:~# mclient -u voc -d voc -s  "COPY INTO test FROM STDIN" - < test.csv
password:
NULL byte in input
root@monetdb:~# mclient -u voc -d voc -s  "COPY INTO test FROM STDIN NULL AS ''" - < test.csv
password:
NULL byte in input

Error "NULL byte in input" is thrown and nothing is added to table.

For Python I have tried using following command:

import monetdb.sql
connection = monetdb.sql.connect(username="voc", password="voc", hostname="localhost", database="voc")
cursor = connection.cursor()
cursor.execute("COPY INTO test FROM 'test.csv'")
connection.commit()

This code throw Perm Exception:

COPY INTO: insufficient privileges: COPY INTO from file(s) requires database administrator rights, use 'COPY INTO "test" FROM STDIN' instead

Now I didn't found any example for python code to use with STDIN, Monetdb cursor does not offer copy_from unlike PostgreSQL where you can Pass file separately. Is there any solution for python?

OS?

uname -a
Linux monetdb 3.13.0-43-generic #72-Ubuntu SMP Mon Dec 8 19:35:06 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux

*** Python example worked fine with user monetdb but for terminal "NULL byte in input" is still exist.

Solution

mclient -u voc -d voc -s  "COPY INTO test FROM STDIN" - < test.csv

works fine for me. The NULL byte in input error indicates that something is wrong with the file you are trying to load.

With regards to the other issue (calling COPY INTO from Python), the error message is quite clear as well. Try connecting as the monetdb user.

What also works is to inline the CSV data to be imported after the COPY INTO command. Example (Python):

cursor.execute("COPY 2 RECORDS INTO test FROM STDIN;\n44444444|AL\n55555555|JAFFRI")

The downside here is that you need to specify the number of records to be imported.