1

I am confused whether you can copy records into a new table in MonetDB.

The official documentation seems to talk only about copying into existing tables. I have quite a large universe of billions of records in dozens of tables, and I would much prefer a solution that inferred the type from the CSV itself, and the column names from the first rows.

A StackOverflow answer seems to work for SQL Server 2000 with OpenRowset, though even they are not concerned about data-type inferencing.

Another answer here on DBA recommends BCP before calling BULK INSERT in Server 2000, neither of which is discussed in the MonetDB documentation.

Other answers also suggest that this is impossible in MySQL without external scripts.

Disclaimer: I am an SQL and MonetDB newbie, but thanks for not letting me miss the obvious.

Improve this question

1 Answer 1

Reset to default
2

Indeed, the COPY INTO requires a CREATE TABLE beforehand. As of right now, there is no generic way to infer a schema. The main issue here is the massive diversity of CSV encodings.

That being said, the MonetDB.R connector does support schema "guessing" through the monetdb.read.csv function.

Also, I have been working on a Python script to automate the CSV loading task. The script (importer.py) is available from my CWI page. It works as follows:

./importer.py -h
usage: importer.py [-h] [--database DATABASE] [--port PORT] [--user USER]
                   [--password PASSWORD] [--header] [--yes]
                   files [files ...]

A "smarter" CSV loader for MonetDB, v.0.3, [email protected], 2014-05

positional arguments:
  files                One or many CSV files to be imported

optional arguments:
  -h, --help           show this help message and exit
  --database DATABASE  Database name to connect to
  --port PORT          MonetDB TCP port, defaults to 50000
  --user USER          MonetDB username, defaults to "monetdb"
  --password PASSWORD  MonetDB password, defaults to "monetdb"
  --header             set if given CSV file has a header in the first line
  --yes                if set, assume Yes on all questions

Usage example:

./importer.py --database acs ~/Desktop/adult.csv
Now probing '/ufs/hannes/Desktop/adult.csv':

OK, I am going to run the following commands on your DB acs:

CREATE TABLE adult5 (C1 DOUBLE, C2 STRING, C3 STRING, C4 STRING, C5 STRING, C6 STRING, C7 STRING, C8 STRING, C9 STRING, C10 STRING, C11 DOUBLE, C12 STRING, C13 BIGINT);
COPY 35469 OFFSET 1 RECORDS INTO adult5 FROM '/ufs/hannes/Desktop/adult.csv' USING DELIMITERS ',','\n','"' NULL AS '' LOCKED;

Proceed? [Y/n] y
imported 30162 rows from /ufs/hannes/Desktop/adult.csv in 3 second(s)

If there are any problems, please let me know.

Improve this answer
6
  • This is truly great. There is one other stupid thing. In many cases the files are simply similar tables but for different years. But the crucial 'year' column is not in the data, it can only be inferred from the file name (ending). How would you suggest I deal with this? (e.g. in a Python wrapper) Commented May 15, 2014 at 10:21
  • 2
    Say you have tables data_2005, data_2006 and so on, all with the same schema. I would use said Python script to import all of them. Then, ask the catalog for their names, e.g. SELECT name FROM tables WHERE name LIKE 'data_%';. Now, add a year column for each of them, e.g. ALTER TABLE data_2005 ADD COLUMN "year" INTEGER; UPDATE data_2005 SET "year" = 2005;. Finally, combine all of them with CREATE TABLE data AS SELECT * FROM data_2005 UNION ALL SELECT * FROM data_2006 UNION ALL [...] WITH DATA; Commented May 15, 2014 at 12:02
  • Thanks, this sounds great — but are you implying that there is no other way but to repeat the code for each year manually? That cannot be scripted? Thanks again, greatly appreciated. Commented May 15, 2014 at 21:36
  • For the Python script, I get a "too few arguments" error if I call it following your usage example. This is with Python 2.7.5 under Windows Server 2012. What am I missing? Thanks! Commented May 17, 2014 at 0:28
  • Actually, even importer.py -h raises error: too few arguments after detailing the usage. Is this easy to fix? Commented May 17, 2014 at 14:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.