Reading using proto_loader for MonetDB

Reading table data from another MonetDB server is possible via the proto_loader('uri_str') functionality. The uri_str expects a character string literal that complies to the following syntax:

monetdb://[<host>[:<port>]]/<database>/<schema>/<table>

or

monetdbs://[<host>[:<port>]]/<database>/<schema>/<table>

or

mapi://[<host>[:<port>]]/<database>/<schema>/<table>

The host name or IP address and port number are optional. Default host is local_host or 127.0.0.1 and default port is 50000.

The database name is mandatory and must be the same as the database name of the remote server.

The schema name is mandatory and must exist in the database of the remote server.

The table name is mandatory and must exist in the database and schema of the remote server. It may also refer to a view name.

Examples:

SELECT * FROM proto_loader('monetdb://127.0.0.1:50000/demo_db/sys/tables');

SELECT table_schema, table_name, table_type
  FROM proto_loader('monetdb://193.12.8.16:52000/dwh_db/information_schema/tables') as tbls
 WHERE NOT table_type LIKE 'SYSTEM %'
 ORDER BY table_schema, table_name;

Note that the proto_loader('monetdb:...') retrieves all rows and all columns of the specified table. For big tables this may take some time.

Tip If you need more control over which data to download (specifying columns, expressions, where clause, group by, order by, limit, etc), consider to use the proto_loader('odbc:DSN=MonetDB;QUERY=...') functionality.

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('monetdb://193.12.8.16:52000/dwh_db/information_schema/tables')
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('monetdb://193.12.8.16:52000/dwh_db/information_schema/tables')
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('monetdb://193.12.8.16:52000/dwh_db/information_schema/tables');

SELECT * FROM mytbl LIMIT 10;