Reading using proto_loader for ODBC

Reading table data from any data source via an ODBC-driver is possible via the proto_loader('uri_str') functionality. The uri_str expects a character string literal that complies to the following syntax:

odbc:DSN=<data source name>;[<ODBC connection parameters>;]QUERY=<SQL query>

or

odbc:FILEDSN=<data source name>;[<ODBC connection parameters>;]QUERY=<SQL query>

or

odbc:DRIVER=<path_to_driver>;[<ODBC connection parameters>;]QUERY=<SQL query>

The data source needs to be setup first on the OS where MonetDB server is running. This setup can be done via the ODBC administrator program (odbcinst on linux/mac, odbcad32.exe on Windows). Every data source will have a unique name.

Some ODBC drivers also support usage of FILEDSN, where the data source configuration is stored in a file with suffix .dsn.

The ODBC connection parameters are optional. Normally all connection parameters are specified in the data source configuration. Only when using the DRIVER= format you may need to specify all required connection parameters.

After the QUERY= token you need to specify the SQL query statement. The SQL query will be sent to the external data source via the ODBC driver, so it needs to comply with the SQL syntax of that data source. Note when using single quotes in the SQL query statement text, you will need to escape them by adding an extra single quote.

Examples:

-- read specific data from an external MonetDB server. This assumes a Data Source Name `MonetDB-Test` has been configured.
SELECT * FROM proto_loader('odbc:DSN=MonetDB-Test;QUERY=SELECT table_schema, table_name, table_type FROM information_schema.tables WHERE NOT table_type LIKE ''SYSTEM %'' ');

-- reading tracks table data from SQLite chinook.db database
select * from proto_loader('odbc:DRIVER=/usr/lib64/libsqlite3odbc.so;Database=/home/usernm/sqlite3/chinook.db;QUERY=SELECT * FROM tracks');

-- read worksheet data from an MS Excel file on Windows. This assumes a Data Source Name `eksel` has been configured which points to a specific .xlsx file.
select * from proto_loader('odbc:DSN=eksel;QUERY=SELECT Id, Name, Date FROM `Sheet1$`;');

Create table and bulk insert

It is also possible to use the query result data to create a new table and populate it in one go.

Examples:

CREATE TABLE tbl_imp AS
SELECT table_schema, table_name, table_type
  FROM proto_loader('odbc:DSN=MonetDB-Test;QUERY=SELECT table_schema, table_name, table_type FROM information_schema.tables WHERE NOT table_type LIKE ''SYSTEM %'' ')
WITH DATA;

SELECT * FROM tbl_imp;


-- or use a local temp table (visible to the current session only and automatically removed at end of the session)
CREATE LOCAL TEMP TABLE tbl_tmp AS
SELECT table_schema, table_name, table_type
  FROM proto_loader('odbc:DSN=MonetDB-Test;QUERY=SELECT table_schema, table_name, table_type FROM information_schema.tables WHERE NOT table_type LIKE ''SYSTEM %'' ')
WITH DATA
ON COMMIT PRESERVE ROWS;

SELECT * FROM tmp.tbl_tmp;

Bulk insert

The query result data can also be used in an insert statement to bulk load the external data into an existing table:

-- the table needs to exist
CREATE TABLE IF NOT EXISTS mytbl (sch VARCHAR(1024) NOT NULL, tbl VARCHAR(1024) NOT NULL, typ VARCHAR(30) NOT NULL);

-- do the bulk insert
INSERT INTO mytbl (sch, tbl, typ)
SELECT table_schema, table_name, table_type
  FROM proto_loader('odbc:DSN=MonetDB-Test;QUERY=SELECT table_schema, table_name, table_type FROM information_schema.tables WHERE NOT table_type LIKE ''SYSTEM %'' ');

SELECT * FROM mytbl LIMIT 10;