Reading table data from any data source via an ODBC-driver is possible via the
proto_loader('uri_str') functionality,
provided the prerequisites (see below) are met.
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 name needs to be setup first on the OS where MonetDB server is running.
This setup needs to be done once and is typically done by an administrator.
See prerequisites below.
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 or needed connection parameters.
After the QUERY= token you need to specify the SQL query statement.
The SQL query statement will be sent to the external data source via the associated ODBC driver,
so it needs to comply with the SQL syntax of that ODBC driver and/or connected (R)DBMS.
Important: when using single quotes in the SQL query statement text (e.g. name = 'Mary'),
you will need to escape them by adding an extra single quote (e.g. name = ''Mary'').
Examples:
-- read specific data from a different 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$`;');
In the above first example we connect to a different MonetDB server via the MonetDB ODBC driver.
This is an alternative to proto_loader for MonetDB
which can read only whole (all columns and all rows) table/view data.
The proto_loader for ODBC allows you to specify any SELECT query,
so provides more control which data to read and transfer from the remote MonetDB server.
The SELECT query can include expressions, aggregations, joins, grouping, ordering, limit, WITH clause, etc.
On Linux and macOS you need to have the unixODBC
package installed, preferably via a package manager (e.g dnf install unixODBC).
FYI: on Windows the ODBC driver manager package is part of the OS, so no download or install is needed.
You will need an ODBC driver to connect to.
There are many ODBC drivers available, often free to download and install.
Check the website of your (R)DBMS or see list of ODBC drivers.
Download and install the required ODBC driver(s) on the OS which runs the MonetDB server.
You will need to setup/configure a data source.
This setup can be done via the ODBC administrator program (odbcinst on Linux/macOS, odbcad32.exe on Windows).
Every data source will have a unique name which is used in the odbc uri_str. DSN stands for Data Source Name.
During setup of the data source you can often also test the connectivity. Use it to verify the connection settings work.
By default the proto_loader for ODBC functionality is disabled on the MonetDB server for security and stability reasons.
You will have to enable it explicitly. This requires you to restart the
MonetDB server with startup argument --loadmodule=odbc_loader
or when you use monetdbd and monetdb
programs do a: monetdb set loadmodules=odbc_loader DBname shell command
and verify it is set via shell command: monetdb get loadmodules DBname.
If during restart of the MonetDB server you get an error message like could not load lib_odbc_loader.so or libodbc.so
than you probably haven't installed the unixODBC package
and/or the MonetDB odbc_loader. Make sure both are installed. Search for /usr/lib64/libodbc.so
and e.g. <MonetDB_install_dir>/lib64/monetdb5-11.55.7/lib_odbc_loader.so.
ODBC (Open Database Connectivity) is a C API that provides access to many different Database Management Systems (DBMSs).
The ODBC API consists of the Driver Manager (DM) and the ODBC drivers.
The Driver Manager is part of the system library, e.g., unixODBC,
which manages the communications between the user applications and the ODBC drivers.
Typically, applications are linked against the DM, which uses Data Source Name (DSN) to look up the correct ODBC driver.
The ODBC driver is a DBMS specific implementation of the ODBC API, which handles all the communication and internals of that DBMS.
The DM maps user application calls of ODBC functions to the correct ODBC driver that performs the specified function and returns the requested data.
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;
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;