Importing and Exporting

Bulk data import/export is a common activity in data analytics. This section provide details on the various ways of moving data into and out of MonetDB.

Reading external data

SELECT * FROM file_loader('file_path')

Reading file data from a delimited text file

SELECT * FROM 'file_path'

Reading file data from a delimited text file.

Note this syntax is a short form of SELECT * FROM file_loader('file_path').

SELECT * FROM proto_loader('monetdb:...')

Reading table data from a MonetDB server

SELECT * FROM proto_loader('odbc:...')

Reading query data from any data source accessable via ODBC

Importing data into table

The best way to import data depends on its source and on the amount of data.

INSERT INTO table VALUES

Every SQL-based application understands INSERT INTO. For moderate amounts of data its performance is sufficient as long as you take care to avoid some pitfalls.

COPY INTO table FROM file

Many applications can export data as text files, for example with comma- or tab separated records or with fixed width records. Loading these using COPY INTO is significantly faster then the equivalent series of INSERTs. Generally this is the preferred way of bulk loading data into MonetDB.

COPY BINARY INTO table FROM files

Loading data from binary files is even faster but the binary files require more effort to create.

COPY LOADER INTO table FROM python_function

Data can also be generated on the fly using a Python program running inside the database server. This program can make up the data on the fly or extract it from an arbitrary external source.

INSERT INTO table SELECT * FROM file_loader('file_path')

Reading file data from a delimited text file and bulk insert it into an existing table

INSERT INTO table SELECT * FROM proto_loader('monetdb:...')

Reading table data from a MonetDB server and bulk insert it into an existing table

INSERT INTO table SELECT * FROM proto_loader('odbc:...')

Reading query data from any data source accessable via ODBC and bulk insert it into an existing table

Exporting data

COPY select_query INTO file

This statement can be used to export data to a text file

COPY select_query INTO BINARY files

This statement can be used to export data to binary data files.