MonetDB bulk load with auto increment

2
votes

I am trying to load csv files into monetdb table(s) with auto increment PK/Id columns using copy into <table> from file....

  1. table has auto increment PK column.
  2. table has more columns than incoming csv files.
  3. csv file doesnt have Pk/Id column.

Is there a way to specify columns while loading data? something like Copy into <tableName>( col2, col2,...) from file ...?

For (1), the work around I have found is to drop the auto increment pk/id column and alter table later. For (2) load file into a temp table and insert/update actual table later. Though its getting cumbersome, not to mention overhead of insert/update instead of bulk load.

Any help/pointer would be really appreciated.

Regards

2
csvauto-incrementmonetdbbulk-load

2 Answers

1
votes

You are right, the monetdb doc doesn't mention a way to specify in which columns to insert the data read from CSV file. However, to achieve this, you can use a temporary table followed by a 'INSERT INTO' with a select subquery :

  1. Import your data into a temporary table 'tmp'
  2. Run INSERT INTO <table>(<col1>, <col2>, ...) SELECT <col1>, <col2>, ... FROM tmp

The "insert into" from a subquery will be equivalent to a bulk insert, and should be quite fast.

1
votes

The solution depends mostly on the format of the csv file so let's take a look at an example.

The csv file contains a header line and is separated with commas (mind that the monetdb default field delimiter is '|').

# cat /path/to/file.csv
val1, val2, val3
asd,2,23
dsa,3,24

sql>CREATE TABLE example (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, val1 VARCHAR(30),val2 INT, val3 INT);
sql>COPY OFFSET 2 INTO example (val1, val2, val3) FROM '/path/to/file.csv' (val1, val2, val3) DELIMITERS ',';
  1. OFFSET 2 makes sure that the header is not copied to the table.
  2. example (val1, val2, val3) FROM '/path/to/file.csv' (val1, val2, val3) part is needed as we don't want to copy value for id column.
  3. DELIMITERS ',' is a must as the default field delimiter is '|'.

For further details see https://www.monetdb.org/Documentation/ServerAdministration/LoadingBulkData