3

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

Mr Lister
  • 45,515
  • 15
  • 108
  • 150
Azar
  • 61
  • 6

2 Answers2

2

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.

Nicolas Riousset
  • 3,447
  • 1
  • 22
  • 25
  • An advance and mature database engine doesn't allow to import auto increment columns? – ps0604 Aug 29 '16 at 09:16
  • You are right, an advanced and mature database engine should allow to import auto increment columns. But MoneyDB is more like a R&D tool. For production environment, HP Vertica or Amazon Redshift may be more appropriate (and also more costly). – Nicolas Riousset Aug 29 '16 at 10:22
2

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

szadam12
  • 21
  • 1